title	Build and run reports
description	Commands, the report YAML, profiles, dependencies and what a run does.
section	build-reports
position	1

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

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.

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.

Previous: Concepts · Next: Templates

Navigation Menu

Repository navigation

building-reports.md

building-reports.md

Build and run reports

When there's no data

Inspect a selection before running

Preview, backfill and accept an intentional schema change

Fix a report before delivery

FilesExpand file tree

building-reports.md

Latest commit

History

building-reports.md

File metadata and controls

Build and run reports

When there's no data

Inspect a selection before running

Preview, backfill and accept an intentional schema change

Fix a report before delivery