Skip to content
You're reading the docs for DRE 0.11 (v0.11.1). This page for 0.13

Build and run reports

Use reports for repeatable exports with named queries, outputs and delivery settings. Start with the first report tutorial; the complete monthly finance example adds a styled workbook and lookup data. The report reference lists keys, and the glossary defines reports, Sets, Bindings and outputs. Shared defaults belong in project configuration.

Terminal window
dre init # pick a source, enter its connection, optionally start a project
cd my_reports
dre validate # check the project and compile its SQL
dre validate -s monthly # ...and show where monthly's output would go
dre compile -s daily,monthly # render the SQL into target/compiled/ and list the files
dre run # run every report; output lands in target/run/ (one folder per run)
dre run monthly --preview 50 # sample 50 rows, never delivered
dre run -s tag:regulatory --set all # every regulatory report, for every Set
dre validate --live # check every statement against the database without running it
dre validate --all-targets # also check the settings of every profile entry, used or not
dre ls --schedule close_monthly # list the Bindings a schedule runs (--output json for tools)
dre clean # remove the target folder
dre system update # update DRE itself

Choose the command explicitly; the same report selectors, Sets and named Schedules apply:

Command Fresh report queries Attached data tests Report formatting and delivery
dre run Yes No Yes
dre test Yes Yes No
dre build Yes Yes Only for Bindings whose tests allow continuation

dre build --schedule close_monthly combines checks and delivery; DRE does not schedule the command itself. There is no individual-test selector. For a complete synthetic project, see the data-test example.

Attach SQL checks to a query with data_tests. Define checks as test blocks under macros/. By default, checks inspect saved results locally using core-owned embedded DuckDB. The DuckDB source plugin is not needed for local validation; source queries still use their configured plugins and execute once. Each local check receives an opaque SQL relation, never a spool path.

queries:
- query: invoices
data_tests:
- missing_identifier:
arguments: {column: invoice_id}
config: {execution: local, severity: error}
output: {format: csv}
-- macros/checks.sql
{% test missing_identifier(relation, column) %}
select * from {{ relation }} where {{ column }} is null
{% endtest %}
Terminal window
dre test monthly # fresh queries and attached checks, no report files or delivery
dre test -s tag:regulatory --set all
dre test --schedule close_monthly
dre run monthly # report files and delivery; declared checks are skipped
dre build monthly # checks first; format/deliver only allowed Bindings

Omit config for the defaults: local execution and error severity. Omit arguments when the macro needs only relation, or uses argument defaults. Ordinary project macros remain available as helpers. A Set’s query entry can replace data_tests; an empty list disables inherited checks for that Binding. Test arguments are template values; the macro remains responsible for quoting identifiers and constructing SQL for the selected execution engine. Tests may also use explicit test_<name> macros. Plain macros remain helpers, and duplicate definitions are errors. Installed Git and local macro packages use the existing dependency declarations and dre deps; call their checks as package.check_name. Project and package test blocks can call helpers through their package namespace, such as quality.predicate(column), including helpers in other macro files or packages. Tests can use argument defaults, helpers and dispatch(); local checks dispatch as DuckDB regardless of the query’s source. Configure tests in YAML; runtime config() calls are unsupported. dre validate resolves definitions and checks signatures and template syntax without running test SQL or introspection. A missing package or definition fails before source execution where detectable; run dre deps after adding or changing a package declaration. Familiar syntax does not make arbitrary dbt packages, adapter APIs or runtime functions compatible with DRE. Column names and other data-dependent SQL errors are checked only when dre test or dre build executes the check.

For database relations, select execution: source and supply arguments.relation explicitly:

queries:
- query: prepare_invoices
profile: warehouse
tab: false
data_tests:
- missing_identifier:
arguments: {relation: temp_invoices, column: invoice_id}
config: {execution: source}

Source checks run after the query’s last statement and before the next query, through that query’s actual connection and existing session. They can read temporary tables and views, including preparation queries with no returned dataset. The source plugin must support sessions. Macros dispatch for the source dialect and may use its native SQL functions. DRE neither infers a relation from preparation SQL nor uploads saved results to the source. Source checks validate the database state they read, not a separately returned dataset. They still consume database compute; a temporary view can re-evaluate its SQL. Normal connection permissions apply, and an error does not roll back earlier preparation side effects.

Hidden queries (tab: false) still provide their returned datasets to local checks, without adding report tabs. Empty returned datasets retain their schema and run checks before any on_empty output policy. A statement returning no dataset cannot be checked locally: use an explicit result-producing query, or a source test with an explicit relation.

For reconciliation, attach a local test to the later query and map macro arguments to earlier query names with query_results. These names must exist earlier in the same Binding; forward references and access to other Bindings are errors. Each mapped argument receives an opaque DuckDB relation over the saved rows. Original source SQL does not run again. query_results is separate from arguments and existing lookup references, and is unavailable for source tests. Tolerances remain ordinary macro arguments:

queries:
- {query: expected_totals, tab: false}
- query: actual_totals
data_tests:
- reconcile:
query_results: {expected: expected_totals}
arguments: {tolerance: 0.01}
{% test reconcile(relation, expected, tolerance) %}
select 1 where abs((select sum(amount) from {{ relation }})
- (select sum(amount) from {{ expected }})) > {{ tolerance }}
{% endtest %}

A test returns failure rows: zero passes; a positive count records bad data at the declared severity. DRE runs all checks attached to the current query when they merely find bad data. An error-severity failure then stops that Binding before its next query. severity: warn allows continuation; warning-only commands succeed. Broken SQL or a missing runtime column is always an execution error, even with warning severity, and stops the Binding immediately. Earlier source side effects may already exist. Other selected Bindings continue. Any failed Binding makes the command exit nonzero. run_results.json records the query, test, execution target, severity, status, duration and full failure count; an execution error records its cause instead of a count. By default, tests do not retain failure-row samples.

Local checks inspect unformatted Arrow values and never change the saved result. Data moves in batches into a disposable, disk-backed DuckDB workspace under the run’s spool directory, with a configured 512 MB DuckDB memory budget, one DuckDB thread and a 2 GB spill limit per check. The memory setting is not a hard process limit: Arrow batches and engine allocations can take memory beyond that budget. The spill limit covers temporary spill files, not the disk-backed imported tables. A check that exhausts its available resources returns an execution error. Normal completion, failure and cancellation remove the database and spill files. The first implementation accepts scalar integer, floating-point, boolean, string, binary, Date32, timezone-free timestamp and supported decimal results; unsupported Arrow types fail explicitly rather than being silently coerced. Decimals must use Decimal128 with precision at most 38 and a nonnegative scale. Timezone-bearing timestamps, nested types and Arrow extension types are rejected. Date32 and timezone-free timestamps retain their stored units and precision; result spools and report output values keep their original Arrow types.

The existing SQLite database remains the disposable per-row population/job index; embedded DuckDB serves local data tests independently. No delivery-history database or migration is introduced. A bounded 1,000-row Python comparison with one and four workers did not justify replacing the existing index: the direct DuckDB candidate took longer. It used Python SQLite 3.42 and DuckDB 1.4.5, not DRE’s Rust bindings or embedded DuckDB 1.5.6, and omitted Arrow, formatting and delivery. This is a keep decision, not an end-to-end performance claim.

dre validate checks declarations and test definitions without executing report or test SQL. Data-dependent checks run under dre test and dre build.

Diagnostics identify the Report, Set (when present), query, test declaration position, local/source execution, severity and elapsed time. PASS, WARN, FAIL, ERROR and SKIP remain readable without color. A failure count means rows returned by the check: a duplicate check can return one row per duplicated key rather than one per invalid input row. Execution errors show their cause without a fabricated failure count. Skipped checks have no failure count, and later queries are listed as skipped even when they declare no tests. The final Tests summary separates passing checks, warnings, data failures, execution errors and skipped checks across all selected Bindings.

--log-format json emits a data_test event with report, set, binding and a structured test outcome. binding_end includes tests and skipped_queries; the final test_summary event counts each test status. Consumers can use those records without parsing text. run_results.json records the same test outcomes and skipped query names. ordinal is the one-based declaration position within its query, and query_ordinal identifies the query declaration position, so repeated query or test names remain distinct. skip_reason identifies the earlier data-test failure or execution error that blocked a check. Default diagnostics contain counts and causes, never failure-row samples.

Set config: {store_failures: true, failure_limit: 1000} on a test to retain CSV examples. Storage is off by default. failure_limit is an integer from 1 to 9223372036854775807 and caps only CSV rows; the check executes once and counts all failures. Both engines write under the configured Target path, isolated by run, Binding, query and declaration order. Diagnostics and run_results.json record failure_path, stored_failures and failures_truncated when a CSV exists. Passing tests create no CSV. Evidence survives failed or cancelled runs and disposable workspace cleanup. dre clean removes it with other Target artifacts. Evidence never joins report outputs, attachments or deliveries. Local CSV storage requires no source write permissions.

The evidence sink uses Arrow’s existing CSV writer: UTF-8 text, a header, comma separators, doubled quotes inside quoted fields, and LF line endings. Nulls become empty fields, losing the distinction from empty strings. Scalars become text without Arrow type or precision metadata; unsupported nested types cause a test execution error. Without SQL ordering, examples can differ between runs. Spreadsheets may infer dates, round identifiers or interpret values beginning with = as formulas. Import evidence as text when investigating untrusted values or exact identifiers.

Run a selection when another report is broken

Section titled “Run a selection when another report is broken”

dre run monthly loads project-wide configuration and dependencies, then parses and checks the selected reports. Errors discovered in other reports produce a stderr warning and appear as project_errors in each Binding’s run_results.json, with a path, code and message for each affected report. Selected reports and the SQL, templates, profiles, lookups and macros they need must still be valid. A broken project-wide file still stops the run. An unreadable YAML file whose role cannot be established also stops it; restore readable UTF-8 configuration before running a selection.

Discovery includes report YAML structure, references, cheap template preflight checks, profile names and plugin declarations where provider metadata is available. It does not execute other reports’ plugin option checks or database queries. source: selectors, and projects with source-based schedule selectors, need a full dependency parse to resolve the selection.

dre validate checks the whole project and fails for any report error, including with -s. Use it as the CI gate. dre run leaves the full manifest to dre compile and dre validate; it neither rebuilds nor replaces an existing manifest.

A report is a YAML file next to its .sql files:

reports/finance/monthly/monthly.yml
queries:
- {query: setup_temp_accounts, tab: false} # CREATE TEMP TABLE: runs first, no tab
- {query: summary, tab_name: Summary}
- detail
output:
format: xlsx
destination:
profile: reports_s3
path: "s3://reports/{{ var('client') }}/monthly-{{ run.date.yyyymmdd }}.xlsx"
sets: [client_a, client_b]
default_set: client_a

Queries run one after another in the order listed, each on its connection’s session (one per connection the report uses), so a temp table made by one is there for the next query on the same connection. A query can run on its own connection with profile:, or by reading a source that names one; see Connections and targets. Each .sql file makes one tab (a sheet in xlsx, or one file for csv, parquet and the other single-table formats), named by tab_name or else the file’s name, in the same order. The YAML decides the tabs, not the data:

  • A file can hold several statements; the last one is the tab and the earlier ones prepare data. A second SELECT in a tab file is an error: give each tab its own .sql file.
  • tab: false runs a file only for what it does (temp tables, SETs) and discards any result.
  • A tab whose query returns no rows still appears, with its column names.
  • A tab file whose last statement returns no result set at all is an error that points at tab: false.

The output file is named after the report (monthly.xlsx), or after the first destination’s path. extension: changes the extension for text formats that feed other systems, e.g. a bank file that must end in .aba, or drops it with extension: "":

output:
format: fixed_width
extension: aba # payments.aba instead of payments.txt; "" for no extension
columns: [...]

output: can also be a list: the queries run once and each output is made from the same results, from the queries its queries: names (all of them by default). A named output writes <name>.<ext>, and a Set changes one output by giving its name:, or replaces the whole list. File outputs are delivered first, then messages:

output:
- name: workbook
format: xlsx
queries: [summary, detail]
destination: {profile: reports_s3, path: "s3://reports/monthly.xlsx"}
- name: headline
format: message # a short headline built from the results; see Messages
queries: [summary]
destination: {profile: team_slack, channel: "#finance"}

--output-name and --output-path apply to the first output. See messages for message outputs, when: and attach:.

Changed in 0.2. Connections are under connections: (was sources:). In 0.2.1 each profile has its own default target: again, and a missing entry is an error. See Upgrading to 0.2.

Connections live in profiles.yml. DRE looks for it, in order, in --profiles-dir, DRE_PROFILES_DIR, the project directory (next to dre_project.yml), and ~/.dre, the same order as dbt; dre validate and dre run -v say which file they used. Database connections go under connections: and delivery targets under destinations:, each with one entry per target (environment):

connections:
warehouse:
targets:
dev: {type: duckdb, path: dev.duckdb}
prod: {type: postgres, host: db.internal, user: reports, password: "{{ env_var('PG_PASSWORD') }}"}
destinations:
reports_s3:
targets:
dev: {deliver: false}
prod: {type: s3, bucket: reports}

Each profile the run uses picks its entry: --target, else DRE_TARGET (either sets every profile), else the profile’s own target:, else dev. A used profile without that entry is an error before anything runs; dev: {deliver: false} says reports_s3 delivers nowhere on a dev run, and the output stays in the target folder. See Connections and targets.

dre init writes this file for you, in ~/.dre (never into a project). A profiles.yml kept in the project, e.g. for CI, a container or a Databricks job, should take every secret from env_var() so nothing secret is committed.

Targets can share settings with YAML anchors and merge keys, in profiles.yml and every other YAML file DRE reads; keys written out win over merged ones:

connections:
warehouse:
targets:
dev: &pg {type: postgres, host: db.internal, user: reports, database: shop}
prod:
<<: *pg
database: shop_prod

Plugin packages and macro packages are declared in dependencies.yml (or packages.yml, or both):

plugins:
- duckdb
- xlsx
- object_store # the s3, gcs and azure_blob destinations
packages:
- git: https://github.com/acme/finance_macros.git
revision: v2.3.0

dre run, dre validate and dre compile install what’s missing into the project’s dre_deps/ folder before they start, so there’s nothing to run first. dre deps installs everything and refreshes the lock on purpose, e.g. as a separate CI step. Exact versions and commits are pinned in dre.lock. Package macros are called through the package’s name ({{ dre_utils.star(ref('customers'), except=['ssn']) }}), and dispatch() lets a package offer per-database variants that a project can override. See the registry docs.

Some days a query returns nothing: no exceptions, no new members, a closed market. on_empty says what an output does then, so “the data was empty” is never mistaken for “DRE didn’t work”. New in 0.6.

on_empty When every query of the output returns no rows Exit code run_results.json
continue (default) written and delivered empty, with a warning naming the queries 0 the output’s status, no_data: [queries]
skip nothing is written or delivered; a “skipped” line names the queries 0 status: skipped, reason: no_data
fail the run fails before anything is written or delivered 1 error_code: no-data

Set it on an output, or on a query entry for that query alone. It’s inherited like other output keys (default_output, a folder’s +output, the report, a Set), so a Set can make one client’s statement strict:

queries:
- holdings
- {query: exceptions, on_empty: skip} # no exceptions: no Exceptions tab
output:
format: xlsx
on_empty: skip # nothing at all: send nothing
sets:
- {name: regulator, output: {on_empty: fail}}

Each empty query applies its own setting first: continue keeps its empty tab, skip leaves its tab (or its file, for a single-table format) out, fail fails the run even when other queries have data. Then, if every query of the output is empty or left out, the output’s setting decides. A query’s own setting wins on conflict, with a warning:

Output Queries Result
any some have rows; an empty one has no setting written; an info line names the empty one
any an empty one has skip its tab is left out; the rest is written
any an empty one has fail the run fails
continue all empty, every one skip skipped, with a warning (the queries win)
skip or fail all empty, one has continue written empty, with a warning (the query wins)

An empty file keeps its shape, so a downstream reader doesn’t break on a quiet day: a csv has its header row, an xlsx tab its column names (a template keeps its layout), a parquet file its full schema, a fixed-width file its header record with header: true. An html or pdf document renders with results.<query>.row_count == 0, so the template can show an empty state:

{% if results.transactions.row_count == 0 %}<p>No transactions this period.</p>
{% else %}{{ table(results.transactions) }}{% endif %}

A query’s skip can’t leave a section out of an html, pdf or message output (the template decides what to show), so dre validate warns that it has no effect there; its fail still applies.

For a per-row output, the output’s on_empty decides what an empty driver does, and the query and output settings apply to each row’s slice: a skipped row is skipped with reason no_data, a failing one is a failed row.

count(*) is never empty. select count(*) from exceptions returns one row holding 0, so on_empty doesn’t fire. Return the rows themselves, or use when: on the count (when: results.exceptions.value > 0).

Use dre ls to see what selectors match; listing defaults to every declared Binding, whereas a plain run uses each report’s default Set. --set all makes the run use every variant:

Terminal window
dre ls -s tag:finance --set all --output json
dre validate -s tag:finance --set domestic --strict --all-targets
dre compile -s tag:finance --set all --var region=AU --timezone Australia/Sydney

--var overrides every YAML level. Quote lists or maps for the shell, and quote a YAML string inside the shell argument when it resembles a boolean: --var flag='"false"' keeps text. --timezone overrides report timezones. --all-targets checks even unused profile entries; --strict also fails on warnings. --json on validation gives tools structured problems, while --log-format json gives a stream of diagnostic events.

Run the tutorial example from its project directory:

Terminal window
cd examples/tutorial
dre validate --profiles-dir . --json
dre --log-format json --color never run --preview 5

From another directory, use an absolute --project-dir path. --profiles-dir names the directory containing profiles.yml. --target picks an environment; --target-path chooses artifact storage. A misspelled environment fails with missing-target-entry. Use -v or --verbose for each step (--log-level debug is equivalent), -q or --quiet for only errors and the final summary, or --log-level info for normal output. --color always keeps ANSI color for a terminal recorder; --color never keeps saved logs plain.

Preview, backfill and accept an intentional schema change

Section titled “Preview, backfill and accept an intentional schema change”

dre run monthly --dry-run renders SQL without executing report queries. Templates calling run_query() or columns() can still connect while rendering. Use --preview (100 rows by default) or --preview 10 to execute a sample and inspect files without delivery.

For a backfill, pin the date rather than changing SQL. Keep its artifacts separate while checking:

Terminal window
DRE_RUN_DATE=2026-09-01 dre run monthly --preview 10 --target-path target-backfill

Inspect the query columns against the previous successful output before accepting drift. --accept-schema-change permits delivery and saves the new schema; use it only after the recipient accepts the intentional column change. A new target path has no previous snapshot, so use the normal persistent target path for the accepted run.

--output-name corrected.xlsx changes the first output’s filename; --output-path replaces its delivery path. With multiple outputs these flags do not rename every output. --profile alternate replaces only the inherited connection: an explicit query/source profile still wins. For an ad hoc variant, pass --set trial --profile warehouse --var region=AU.

Bound unattended runs with --timeout 30m, retain their artifacts with --keep-runs 7, and use --threads 2 to run up to two Bindings at once. Queries within a Binding remain ordered. See parallel Bindings and run history.

  • unknown-query: match queries to SQL file stems; query names are unique across the project.
  • no-result-set: mark setup-only queries tab: false, or end a tab query with a result-producing statement.
  • duplicate-output: give outputs unique name values so filenames do not collide.
  • connection-conflict: split a query reading sources on two connections into separate queries.
  • compile-needs-run: a template needs database results; check with dre validate --live or a preview run.

These error codes are stable even when the detailed message names different files or columns. dre explain unknown-query gives the matching repair instructions.