# Build and run reports | DRE

> Commands, the report YAML, profiles, dependencies and what a run does.

You're reading the docs for DRE 0.12 (v0.12.2). [This page for 0.13](https://getdre.com/docs/building-reports/)

# Build and run reports

Use reports for repeatable exports with named queries, outputs and delivery settings. Start with the [first report tutorial](https://getdre.com/docs/v0.12/first-report/); the complete [monthly finance example](https://github.com/get-dre/dre/blob/v0.12.2/examples/monthly-finance/) adds a styled workbook and lookup data. The [report reference](https://getdre.com/docs/v0.12/reference-report/) lists keys, and the [glossary](https://getdre.com/docs/v0.12/glossary/) defines reports, Sets, Bindings and outputs. Shared defaults belong in [project configuration](https://getdre.com/docs/v0.12/project-configuration/).

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

## 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](https://github.com/get-dre/dre/blob/v0.12.2/examples/data-tests/).

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.

```yaml
queries:
  - query: invoices
    data_tests:
      - missing_identifier:
          arguments: {column: invoice_id}
          config: {execution: local, severity: error}
output: {format: csv}
```

```sql
-- macros/checks.sql
{% test missing_identifier(relation, column) %}
select * from {{ relation }} where {{ column }} is null
{% endtest %}
```

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

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

```yaml
queries:
  - {query: expected_totals, tab: false}
  - query: actual_totals
    data_tests:
      - reconcile:
          query_results: {expected: expected_totals}
          arguments: {tolerance: 0.01}
```

```sql
{% 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

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](https://github.com/get-dre/dre/blob/v0.12.2/crates/dre-core/src/run/data_tests.rs) 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

`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`](https://getdre.com/docs/v0.12/manifest/#run_resultsjson), 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

```yaml
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](https://getdre.com/docs/v0.12/sources/) that names one; see [Connections and targets](https://getdre.com/docs/v0.12/connections/). 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, `SET`s) 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: ""`:

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

```yaml
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](https://getdre.com/docs/v0.12/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](https://getdre.com/docs/v0.12/migrating-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):

```yaml
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](https://getdre.com/docs/v0.12/connections/).

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

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

```yaml
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](https://getdre.com/docs/v0.12/registry/).

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

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

```html
{% 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](https://getdre.com/docs/v0.12/per-row-outputs/#no-data-and-orphan-rows), 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:`](https://getdre.com/docs/v0.12/messages/#sending-only-when-it-matters) 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:

```bash
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. Both include a [`schema_version`](https://getdre.com/docs/v0.12/manifest/#json-validation-and-log-events) for integrations.

Run the tutorial example from its project directory:

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

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

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:

```bash
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. 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](https://getdre.com/docs/v0.12/connections/#running-bindings-at-once) and [run history](https://getdre.com/docs/v0.12/target-path/#runs-and-runs-that-overlap).

## 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](https://getdre.com/docs/v0.12/reference-error-codes/) are stable even when the detailed message names different files or columns. `dre explain unknown-query` gives the matching repair instructions.

[Previous  
Building from source](https://getdre.com/docs/v0.12/building-from-source/)[Next  
CLI reference](https://getdre.com/docs/v0.12/cli-reference/)
