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.
dre init # pick a source, enter its connection, optionally start a projectcd my_reportsdre validate # check the project and compile its SQLdre validate -s monthly # ...and show where monthly's output would godre compile -s daily,monthly # render the SQL into target/compiled/ and list the filesdre run # run every report; output lands in target/run/ (one folder per run)dre run monthly --preview 50 # sample 50 rows, never delivereddre run -s tag:regulatory --set all # every regulatory report, for every Setdre validate --live # check every statement against the database without running itdre validate --all-targets # also check the settings of every profile entry, used or notdre ls --schedule close_monthly # list the Bindings a schedule runs (--output json for tools)dre clean # remove the target folderdre system update # update DRE itselfCheck returned data with dre test
Section titled “Check returned data with dre test”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 %}dre test monthly # fresh queries and attached checks, no report files or deliverydre test -s tag:regulatory --set alldre test --schedule close_monthlydre run monthly # report files and delivery; declared checks are skippeddre build monthly # checks first; format/deliver only allowed BindingsOmit 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.
Retain failure examples
Section titled “Retain failure examples”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. Declared
secret values (secret_columns, secret env_var()s) are masked as ***** in every
cell and header, even under an alias, unless mask_secrets: false. 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:
queries: - {query: setup_temp_accounts, tab: false} # CREATE TEMP TABLE: runs first, no tab - {query: summary, tab_name: Summary} - detailoutput: format: xlsx destination: profile: reports_s3 path: "s3://reports/{{ var('client') }}/monthly-{{ run.date.yyyymmdd }}.xlsx"sets: [client_a, client_b]default_set: client_aQueries 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
SELECTin a tab file is an error: give each tab its own.sqlfile. tab: falseruns 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:(wassources:). In 0.2.1 each profile has its own defaulttarget: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_prodPlugin packages and macro packages are declared in dependencies.yml:
plugins: - duckdb - xlsx - object_store # the s3, gcs and azure_blob destinationspackages: - git: https://github.com/acme/finance_macros.git revision: v2.3.0dre 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.
When there’s no data
Section titled “When there’s no data”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 |
|---|---|---|---|
skip (default) |
nothing is written or delivered; a “skipped” line names the queries | 0 | status: skipped, reason: no_data |
continue |
written and delivered empty, with a warning naming the queries | 0 | the output’s status, no_data: [queries] |
fail |
the run fails before anything is written or delivered | 1 | error_code: no-data |
Breaking in 0.12: the default was continue, so an empty result went out to its
recipients. Now nothing is sent: the output is recorded skipped with reason no_data. To send
empty files as before, set on_empty: continue (on default_output for the whole project).
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 taboutput: format: xlsx on_empty: skip # nothing at all: send nothingsets: - {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).
Inspect a selection before running
Section titled “Inspect a selection before running”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:
dre ls -s tag:finance --set all --output jsondre validate -s tag:finance --set domestic --strict --all-targetsdre 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. Both include a
schema_version for integrations.
Run the tutorial example from its project directory:
cd examples/tutorialdre validate --profiles-dir . --jsondre --log-format json --color never run --preview 5From 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.
Clear inherited output settings
Section titled “Clear inherited output settings”Since 0.12, destination: null, when: null and template: null clear the inherited value
from default_output, folder +output or the report’s output. Omitting a key keeps inheritance.
For example, output: {destination: null, when: null} keeps the file locally and removes an
inherited condition. A Set can use the same overrides.
Preview, backfill and accept an intentional schema change
Section titled “Preview, backfill and accept an intentional schema change”Since 0.12, dre compile replaces the removed dry-run flag on dre run.
dre compile monthly 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:
DRE_RUN_DATE=2026-09-01 dre run monthly --preview 10 --target-path target-backfillInspect 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. Since 0.12
--output-path refuses a run that isn’t exactly one Binding whose first output has one
destination and no for_each.
--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. Since
0.12 a --set name the report doesn’t declare is an error unless sets.yml declares it or
--profile/--var complete it, so a typo never runs the base data under a Set’s name. A --var
name that no project file mentions gets an unused-var warning.
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.
Fix a report before delivery
Section titled “Fix a report before delivery”unknown-query: matchqueriesto SQL file stems; query names are unique across the project.no-result-set: mark setup-only queriestab: false, or end a tab query with a result-producing statement.duplicate-output: give outputs uniquenamevalues 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 withdre validate --liveor 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.