# DRE practices | DRE

> The opinions DRE’s agent skills give, and the reasons behind them.

You're reading the docs for DRE 0.6 (v0.6.1). [This page for 0.11](https://getdre.com/docs/practices/)

# DRE practices

The opinions DRE’s agent skills give, and the reasons behind them. Every skill takes its advice from this file and cites rules by ID (`SEC-1`, `REP-2`, …), so you can look up the full reasoning. They’re good advice for people, too.

Each rule has a level:

-   **advise**: the guide says “I’d do X, because Y” once, then does what you decide.
-   **warn**: the guide explains the trade-off and needs your explicit “yes, do it anyway” before going ahead. Then it does what you decided, without arguing again.
-   **block**: the guide won’t do it, whatever you say, and offers the safe way instead. Only secrets are at this level.

## Secrets

Secrets are passwords, tokens, access keys, client secrets, connection strings with keys in them, private keys and their passphrases. The rules hold for every source and destination.

### SEC-1: Never ask for a secret, and say so up front

**Level:** block

Before any step that involves a sign-in, the guide says: “Never paste a password, token or key into this chat. I’ll never ask for one; you’ll set it yourself where I can’t see it.” It never asks for a secret’s value, in any form (“paste the token”, “what’s the password?”).

_Why:_ anything typed into a chat can end up in the agent’s logs, history and provider, outside your control. A secret that never enters the chat can’t leak from it.

_Example:_ setting up Postgres, the guide asks for the host, database and user, then says it’ll write `password: "{{ env_var('PG_PASSWORD') }}"` and how you set `PG_PASSWORD` yourself.

### SEC-2: Prefer a sign-in that stores no secret

**Level:** block (the order below is advice; storing a secret where a sign-in would do is not)

Recommend, in this order:

1.  **A platform sign-in that stores no secret in DRE’s files.**
    -   Databricks: `auth_type: oauth` (a browser sign-in; `dre` keeps the session in `~/.dre/oauth_sessions.json`, readable only by you), or a `databricks auth login` session or `~/.databrickscfg` profile, which the default `auth_type: auto` finds.
    -   S3: the AWS credential chain, e.g. `aws sso login` (leave the key fields out).
    -   GCS: application default credentials, `gcloud auth application-default login` (leave the key fields out).
    -   Azure Blob: `use_azure_cli: true` after `az login`, or `use_managed_identity: true` on Azure.
    -   SFTP, and a Postgres SSH bastion (`ssh:`): a key pair (`private_key_path`), with its passphrase in an environment variable if it has one. Where the key can’t be a file (a CI runner), its text from `env_var()` in `private_key` (as in item 2).
2.  **An environment variable read with `env_var()`**, set by you (SEC-3), for what has no sign-in: Postgres and FTP passwords, SMTP passwords, Slack bot tokens, a Databricks service principal’s secret for a scheduler.

_Why:_ a sign-in expires and can be revoked centrally, and there’s nothing to leak. A long-lived secret in a file is the most common way credentials escape.

### SEC-3: Secrets only through `env_var()`, set by you

**Level:** block

A profile field that holds a secret is always written as an `env_var()` reference, e.g. `token: "{{ env_var('SLACK_BOT_TOKEN') }}"`, never as the value. The guide tells you how to set the variable, and you set it: in your shell profile (`~/.zshrc`, `~/.bashrc`, PowerShell’s `$PROFILE`), a secrets manager your shell reads from (1Password CLI, `pass`, macOS Keychain), or your scheduler’s or CI’s secret store (GitHub Actions secrets, Databricks secret scopes, Airflow connections). A variable whose name starts with `DRE_SECRET_` is also masked as `*****` in DRE’s output and logs.

If you ask the guide to “just put the token in the YAML”, it refuses, says why, and writes the `env_var()` reference instead.

_Why:_ `profiles.yml` gets copied, backed up and committed. The same file with `env_var()` references is safe to share.

### SEC-4: Check a variable is set without showing it

**Level:** block

To confirm a variable is set, the guide runs a check that prints only “set” or “missing”:

```bash
[ -n "$PG_PASSWORD" ] && echo "PG_PASSWORD is set" || echo "PG_PASSWORD is missing"
```

```powershell
if ($env:PG_PASSWORD) { "PG_PASSWORD is set" } else { "PG_PASSWORD is missing" }
```

It never runs `echo $PG_PASSWORD`, `env`, `printenv` or `set` without a filter, and never reads a secret from a file. If the variable is missing in the agent’s shell but set in yours, start the agent again from a shell that has it.

_Why:_ printing the value puts it in the chat, which SEC-1 exists to prevent.

### SEC-5: A pasted secret has leaked

**Level:** block

If a secret is pasted into the chat anyway, the guide:

1.  doesn’t use it, repeat it, or write it to any file or command line;
2.  says it must now be treated as leaked, and gives the revoke-and-rotate steps below for that platform;
3.  carries on with the safe setup (SEC-2, SEC-3) using the new secret, which you set yourself.

Revoke and rotate:

-   **Databricks personal access token**: User Settings → Developer → Access tokens → revoke it (or `databricks tokens delete <token-id>`). Prefer switching to `auth_type: oauth` instead of a new token. **Service principal secret**: in the account console (or workspace admin settings), open the service principal → Secrets, create a new secret, then delete the leaked one.
-   **Postgres password**: have the role’s password changed (`ALTER ROLE <role> PASSWORD '<new>'` by an admin, typed in your own terminal, not the chat), and check the server’s logs for unexpected sign-ins.
-   **AWS access key (S3)**: IAM → Users → Security credentials → make the key inactive, then delete it (`aws iam update-access-key --status Inactive`, then `aws iam delete-access-key`). Check CloudTrail for its use. Prefer `aws sso login` over a new key.
-   **Google Cloud service account key (GCS)**: IAM & Admin → Service accounts → Keys → delete it (`gcloud iam service-accounts keys delete <key-id> --iam-account <account>`). Prefer `gcloud auth application-default login` over a new key.
-   **Azure storage (connection string, access key or SAS token)**: storage account → Security + networking → Access keys → rotate the key it contains or was signed with (a SAS stays valid until the key that signed it is rotated, or its stored access policy is removed). Prefer `use_azure_cli: true`.
-   **SFTP or FTP password**: have it changed on the server. **SFTP private key**: remove its public key from the server’s `authorized_keys`, and make a new key pair.
-   **SMTP (email) password**: change it, or revoke the app password in your mail provider’s account security settings, and make a new one.
-   **Slack bot token**: in your app’s settings (api.slack.com/apps) → OAuth & Permissions → revoke the tokens, then reinstall the app to the workspace to get a new one.

_Why:_ once a secret has been in a chat, you can’t know where copies went. Rotating is the only way to be sure.

## Setup

### SET-1: Keep profiles in `~/.dre`

**Level:** advise

Connections go in `~/.dre/profiles.yml`, outside any project, where `dre init` writes them. Keep a `profiles.yml` inside a project only for CI, a container or a Databricks job, and then take every secret from `env_var()` (SEC-3), since the project is committed.

_Why:_ profiles are per person and per machine; reports are shared.

### SET-2: One profile per system, one target per environment

**Level:** advise

Give each system one connection profile, with an entry per environment (`dev`, `prod`). Run without flags locally, and set `DRE_TARGET=prod` (or pass `--target prod`) where reports run for real. To read production data locally, give the connection `target: prod` rather than passing `--target prod`. Give every destination a `dev` entry: a dev location, or `dev: {deliver: false}` when it should deliver only from production. Two Databricks workspaces are two profiles.

```yaml
connections:
  warehouse:
    targets:
      dev: {type: postgres, host: localhost, user: me, database: shop, password: "{{ env_var('PG_DEV_PASSWORD') }}"}
      prod: {type: postgres, host: db.internal, user: reports, database: shop, password: "{{ env_var('PG_PROD_PASSWORD') }}"}
destinations:
  finance_mail:
    targets:
      dev: {deliver: false}
      prod: {type: email, host: smtp.internal, from: reports@example.com}
```

_Why:_ the same report runs against dev and prod unchanged, a plain `dre run` delivers nowhere it shouldn’t, and a mistyped `--target` fails instead of quietly delivering nothing.

### SET-3: Start from a validated project

**Level:** advise

Create the project with `dre new` (or `dre init`), then run `dre validate` before writing any report. Commit `dre.lock` with the project.

_Why:_ a known-good base means the first error you see is about your report, not the setup. `dre.lock` pins plugin versions so every machine runs the same ones.

## Reports

### REP-1: One tab per SQL file, set in the YAML

**Level:** warn

Each tab (a sheet in xlsx, a file in csv) comes from its own `.sql` file, listed in the report’s YAML in the order the tabs should appear. The YAML decides the tabs, never the data: don’t try to split one query’s rows into tabs, or make tabs from a column’s values. Name tabs with `tab_name`.

```yaml
queries:
  - {query: summary, tab_name: Summary}
  - {query: detail, tab_name: Detail}
```

_Why:_ the workbook’s shape stays the same every run, whatever the data holds, so the people and systems reading it can rely on it. DRE refuses a second `SELECT` in a tab file for the same reason.

_Warned when:_ you ask for one SQL file to produce several tabs, or for tabs per value in the data.

### REP-2: Variables, not hardcoded dates or values

**Level:** warn

Use `run.date` (and its navigation, e.g. `run.date.prev_month.start.date`) for dates, and `var('name')` with defaults in the YAML for anything that changes between runs, clients or environments. Write `'{{ run.date.prev_month.start.date }}'`, not `'2026-08-01'`.

_Why:_ a hardcoded date or client name means editing SQL every run, and a report that silently repeats last month’s numbers when someone forgets. Variables also let `--var` and schedules change a run without touching the files.

_Warned when:_ you ask for a literal date, month or client value in SQL or a path.

### REP-3: `tab: false` for setup statements

**Level:** advise

A file that only prepares data (temp tables, `SET`s) is listed with `tab: false`, before the tabs that use it.

_Why:_ it runs on the same session as the tabs, and its result doesn’t become an empty tab.

### REP-4: Sets for variants, not copies

**Level:** advise

When the same report runs for several clients, regions or departments, declare Sets with their own vars instead of copying the report.

_Why:_ one copy of the SQL to fix and review.

### REP-5: Format in the output, not in SQL

**Level:** advise

Keep numbers and dates typed in SQL and format them with the format’s options (xlsx `columns:` formats, `date_format`; fixed-width `decimals`, `date_format`). Don’t turn them into text with `to_char` or string concatenation.

_Why:_ typed values stay sortable and summable in Excel, and one format change doesn’t mean editing every query.

### REP-6: Share SQL with `ref()` and lookups

**Level:** advise

Reuse a query through `ref('file')`, and keep mapping tables you maintain by hand as lookups (`lookups/`) rather than as `CASE` expressions or tables someone has to load.

_Why:_ one definition, used everywhere.

### REP-7: Declare the tables you read as sources

**Level:** advise

Declare every table reports read under `sources:` (in `sources/`), and write `{{ source('sales', 'orders') }}` in SQL rather than the table’s name. Put `profile:` on a source that lives on one system, and declare `columns` for tables whose shape other reports rely on.

_Why:_ table names live in one place, `dre run -s source:sales.orders` finds every report that reads a table that changed, `dre ls --resource-type source` shows what’s unused, and `dre validate --live` catches upstream drift before a report runs.

### REP-8: Name the connection where it’s true

**Level:** advise

Let the data decide the connection: a source’s `profile:` for tables on one system, a query’s `profile:` only for a tab with no source, and the report’s `profile:` (or `default_profile`) for the rest. Keep a report’s `tab: false` setup queries on the same connection as the tabs that read what they make.

_Why:_ one rule (explicit beats inherited, and they must agree) then explains every tab’s connection, and a setup query can’t silently prepare a session the tabs never use.

### REP-9: Aggregate in SQL, keep messages short

**Level:** advise

Give a `message` output its own small query that computes the headline numbers (one row, or a handful), name it in the output’s `queries:`, and keep the text to a few lines: the number, the change, and a link to the file for detail. Don’t point a message at a detail query and count or sum rows in Jinja.

_Why:_ the database does the arithmetic on every row, a message only reads the first 1,000 rows of each query (`max_rows`), and a long message is cut short in chat while people stop reading a noisy channel.

### REP-10: Aggregate in SQL, not in a document template

**Level:** advise

Give an html or pdf document’s headline numbers their own small query (a total, a balance, a count), and pass detail tables to `table()` or a loop as they are. Don’t sum, count or group rows in Jinja.

_Why:_ the database does the arithmetic on every row, exactly, with the types it knows; a template that adds up rows is slow, easy to get subtly wrong, and checked by nobody.

### REP-11: Keep legal text in files, not vars

**Level:** advise

Put terms, disclaimers and other legal wording in their own template files (`terms.html`, `terms_acme.html`) and include them, choosing the file per Set or per client. Use vars for short values (a client’s name, a fee rate), not paragraphs.

_Why:_ wording in a file is reviewed, versioned and diffed like code; a paragraph in a var hides in YAML, is easy to change by accident, and can’t be checked by `dre validate`.

### REP-12: An opaque key for per-row outputs

**Level:** warn

Use an opaque id (a member or account number) as a `for_each` key, never an email address, a name or a phone number. Aggregate each member’s figures in SQL, by that key.

_Why:_ the key names each row’s files and appears in logs and `run_results.json` unmasked, so it must not be personal data; DRE masks values rendered from `each.*` in destinations, but never the key.

## Running and delivering

### RUN-1: Test before you schedule

**Level:** warn

Before a report goes on a schedule or into production: `dre validate -s <report>` passes, a `dre run <report> --preview` looks right, and a full run to the local target folder has been checked.

_Why:_ a scheduled report that fails, or delivers wrong numbers, is found by the people who receive it.

_Warned when:_ you ask to schedule a report, or run it with `--target prod`, before it has been tested this way.

### RUN-2: Confirm before production and real recipients

**Level:** warn

Before a run that reads from a production target or delivers anywhere but the local target folder (email, Slack, object storage, SFTP), the guide shows what will happen (`dre validate -s <report>` lists the source, target and every destination) and asks. Use `--preview` to try a report: it limits rows and never delivers.

_Why:_ an email or Slack post can’t be recalled.

### RUN-3: Pass the scheduled time

**Level:** advise

A scheduler runs a schedule with `dre run --schedule <name>` and passes the instant it was scheduled for with `DRE_RUN_AT` (RFC 3339), or at least the run’s logical date with `DRE_RUN_DATE=YYYY-MM-DD`. Each occurrence from `dre schedule ls` carries both in its command.

_Why:_ a rerun of last Monday’s job renders last Monday’s dates, not today’s, and a rerun of the 18:00 firing renders as 18:00 even when it starts at 21:00.

### RUN-4: A lasting target path on ephemeral runners

**Level:** advise

On a job cluster, a CI runner or a container, point `--target-path` (or `DRE_TARGET_PATH`) at a folder that outlives the run, e.g. a Databricks Volume.

_Why:_ schema-drift detection compares each run with the last successful one, whose snapshot lives in the target path.

### RUN-5: Look before accepting a schema change

**Level:** warn

When a run stops because the output’s columns changed, find out why before re-running with `--accept-schema-change`.

_Why:_ the people or systems reading the file may depend on its columns; the check exists to stop an unexpected change from reaching them.

### RUN-6: Preview a message before it’s sent

**Level:** warn

Before a report with a `message` output delivers to a real channel or inbox, run `dre run <report> --preview` and read the message it prints (title, text, length against each destination’s limit, and whether `when:` passed). On the dev target, give chat and email profiles `{deliver: false}` so a test run can’t post.

_Why:_ a post to a channel can’t be taken back, and a template that reads the wrong column still renders.

_Warned when:_ you ask to run a report with a `message` output against a real destination before it has been previewed.

### RUN-7: A date or period in delivery paths

**Level:** advise

Put the period a file covers in its delivery path (`monthly-{{ run.date.yyyymm }}.xlsx`), and set `if_exists: error` where a file must never be replaced (a regulated drop) or `number` to keep every copy.

_Why:_ a delivery replaces a file already at its path by default. With the period in the name, only a rerun of the same period replaces its file, which is usually what’s wanted after a fix; without it, every run overwrites the last.

### RUN-8: A `{deliver: false}` dev target for per-row sends

**Level:** warn

Before running a per-row output that emails or messages people, give its destination profiles a `dev: {deliver: false}` entry, and try it with `--preview`, `--only-keys` or `--limit-rows` before a production run.

_Why:_ a per-row output sends one delivery per row, so a test run against real addresses can email thousands of people; a dev entry that delivers nowhere renders every row and sends nothing.

### RUN-9: Hold regulatory sends for review

**Level:** advise

Put `hold: true` on the destinations of a regulatory or client-facing batch (or run with `--hold`), review the rendered files, then deliver them with `dre release`. Keep the run in the target path for the review window (`keep_runs`, a shared target path).

_Why:_ a maker/checker step catches a wrong figure before anyone receives it, and `dre release` sends exactly the files that were reviewed, refusing any that changed.

## Schedules

### SCH-1: Give every schedule a timezone

**Level:** advise

Set `timezone:` on each schedule, or on the shared timing it uses, rather than relying on the project’s or UTC.

_Why:_ a schedule without one fires in the project’s timezone (or UTC) but runs in its report’s, so “06:00 on the 1st” can render as the 31st. One timezone on the schedule keeps firing and run date together, and `dre validate` warns when they differ.

### SCH-2: Share timings in `timings.yml`

**Level:** advise

When two or more schedules fire at the same time, name the timing once in `timings.yml` and use `timing: <name>` in each, keeping their own report, Set and vars.

_Why:_ “the 1st of the month, 06:00 Sydney” changes in one place, for every client, and the change shows as one diff.

### SCH-3: Check a timing with `dre schedule ls`

**Level:** advise

After writing or changing a timing, run `dre schedule ls --schedule <name>` and read the dates before relying on it. Give `every` schedules and rules with `INTERVAL` or `COUNT` a `starting` date, and every timing a time of day.

_Why:_ cron and recurrence rules are easy to get subtly wrong (both cron day fields, `BYSETPOS`, DST), and a rule without an anchor fires on dates that depend on when you look.

## Porting reports from other tools

For moving reports from SSRS, Power BI paginated, Crystal Reports or SQL scripts (the `dre-port` skill).

### PORT-1: Port the logic to SQL, not to templates

**Level:** advise

Turn the old report’s expressions and formulas (`IIf`, `Switch`, `RunningValue`, Crystal `{@formulas}` and running totals) into SQL columns and window functions, and its number and date formats into the output’s column formats. Keep a document template for layout only.

_Why:_ the database computes every row the same way, with the types it knows, and the logic can be read and tested in one place; expressions spread over text boxes and template code are how old reports drifted from what anyone could check.

### PORT-2: Prove parity before switching over

**Level:** warn

A port is done when its parity check against an export of the old report, for the same parameters, matches (or every remaining difference is explained and accepted), and the owner has compared the layout. Keep the old report and its subscriptions running until one full cycle of both has matched.

_Why:_ a port that “looks right” can still drop rows at a period’s edge, round differently or read another time zone; readers notice weeks later, when the old report is gone and nothing is left to compare with.

### PORT-3: One report at a time, simplest first

**Level:** advise

Port an estate one report at a time, starting with the simplest, and finish each one, parity check included, before the next. Near-duplicates (a copy per region or client) become one report with Sets (REP-4).

_Why:_ the first, simple port settles the connection, folders and conventions every later one uses, and a finished report is worth more than ten half-ported ones.

### PORT-4: First deliveries held or on dev

**Level:** warn

Give a ported report’s destinations a `{deliver: false}` entry on `dev` and `hold: true` on the real ones until the owner signs off on its first runs.

_Why:_ while the old report still runs, an unreviewed new one sends recipients a second copy, or a wrong one; holding the first runs costs a review, and a recalled email can’t be.

### PORT-5: Record where a report came from

**Level:** advise

Put the old report’s path or name in the ported report’s `meta` (`ported_from:`), give it a `description`, and name columns for what they hold (`region_name`, not `Textbox12`).

_Why:_ during a migration people ask “which DRE report replaced /Finance/Invoice summary?”, and the answer should be in the project, not in someone’s memory.

## AI context and owner decisions

### AI-1: Describe the reviewed business answer

**Level:** advise

Record grain, units, period rules, exclusions, real example questions and declared input constraints, from existing evidence or the user’s answers. Ask for missing business facts; keep report exceptions near the report, in inherited context.

_Why:_ the model answers from this text; a guessed rule becomes a confident wrong answer.

### AI-2: Use pipeline documentation and report logic

**Level:** advise

Table and column comments belong to dbt or another pipeline. DRE reads them, and the compiled report’s joins, predicates, filters and grouping. A relationship one report uses is evidence for that report, not a universal join rule or a check of a newly generated answer.

_Why:_ one source of truth for column meaning; a second copy in DRE drifts.

### AI-3: Owners choose exposure

**Level:** block

Draft context without setting `meta.ai.expose: true`, and keep the owner’s existing settings. Explain that exposed reports include their tables by default, with opt-out flags and exclusions; owners review extra allowlists.

_Why:_ exposing a report lets an AI run it with the user’s database access; that’s the owner’s decision, not the author’s.

### AI-4: Preserve provenance

**Level:** block

A verified run follows the reviewed report definition. Derivation transforms saved verified rows; exploration is a new, unverified query. Keep the source run, operations and SQL available for review, and never present either as verified by wording.

_Why:_ “verified” only means something if it’s never stretched.

### AI-5: Leave companion settings to their owners

**Level:** block

Settings in an `extensions:` entry beyond `name` and `version` belong to the companion and its owner (for example a licence acceptance). Context-authoring agents leave them unchanged unless the owner explicitly asks, and never accept terms on anyone’s behalf. Changes go through the repository’s normal review.

_Why:_ a CLI can’t prove that a person read terms or that a PR was approved; the review can.

### AI-6: Constrain every variable a model may set

**Level:** block

Give every variable an AI client may set a type that can’t carry SQL (`date`, `integer`, `number`, `boolean`), an `allowed` list, or, for a string, a `pattern` (and a `max_length`). Free-text strings, arrays and objects without `allowed` keep a report from being exposed; give them a default and leave them out of what the model may set, or constrain them.

_Why:_ variables are rendered into the SQL as text. An unconstrained one lets the model rewrite the reviewed query while the answer still looks verified.

[Previous  
First-party plugins](https://getdre.com/docs/v0.6/plugins/)[Next  
Project configuration](https://getdre.com/docs/v0.6/project-configuration/)