Skip to content
DRE

Build and run reports

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/
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 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, on one database session, so a temp table made by one is there for the next. 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: [...]

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 sources: and delivery targets under destinations:. Each profile picks a default target (environment) from its named targets:

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

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:

sources:
  warehouse:
    target: dev
    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.