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

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

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.

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.

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, SETs) 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.

RUN-10: Send a password by another route than the file

Level: warn

A password-protected PDF or zip and its password never travel together: the password goes to another address (a second email, a phone number on file), or not at all because the recipient already knows it (a date of birth, a member number). A file → password list goes to a destination the client controls, never the one that receives the files.

Why: a password that arrives with the file protects nothing from whoever intercepts or misdirects that delivery.

Warned when: a password message goes to the same to: as the file it unlocks, or a password list goes to the same destination profile as the files.

RUN-11: Mark password columns secret

Level: warn

Every column a password: or zip.password reads is listed in its query’s secret_columns:, and passwords are at least 8 characters.

Why: without the mark, the passwords show in the console, dre.log, run_results.json, the saved results and dre-mcp’s answers, which are kept and copied far more widely than the files.

Warned when: dre validate reports a password column that isn’t secret, a password written in the YAML, or one read through var().

RUN-12: Prefer AES-256 zips

Level: advise

Use the default encryption: aes256 and tell recipients to open the zip with 7-Zip, WinRAR, Keka or similar. Choose zipcrypto only for recipients who can’t install anything, and only for data that can bear the risk.

Why: ZipCrypto can be cracked in minutes with known-plaintext attacks; AES-256 can’t.

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.