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 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

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}
  - 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.

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`
`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).

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 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:

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

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:

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.

Fix a report before delivery

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.