Chapter 19 — Key Takeaways (dbt)

The page to keep open while setting up a project, and during the first review of someone else's.

What it is

Three things wrapped around a SQL file: a templating engine · a dependency graph inferred from ref() · a test runner.

What it is not, and each is a real mistake:

The contribution in one sentence: every pre-dbt shop had a dependency graph — in a wiki, in an Airflow file, in someone's head — and it was wrong. dbt's graph cannot drift from the code because it is the code.

ref() and source()

A hardcoded table name compiles, runs, and returns correct results. It is not a syntax problem, and no test of the rows will find it.

Four consequences, and the fourth is the trap:

1. The DAG edge does not exist → intermittent wrong build order, appearing months later
2. It points at the wrong environment → dev reads (and can write) production
3. Lineage is wrong → impact analysis is missing an edge
4. Slim CI skips the model → the check that would catch it is the one it disables

Diagnose in one minute: dbt ls --select <upstream>+. Not in the list? Edge missing.

Lint the manifest, not the source. The case that occurs is a model with some ref()s and one hardcoded name; a grep for ref( finds nothing.

Materializations

💸 Kestrel's four-view chain: $2,864/month. The same models as tables: $19.44. $2,845/month, $34,146/year, from one line of config.

📐 Ephemeral is not free. It cannot be queried (debug by reading compiled SQL), and one referenced by six models is computed six times — a multiplier no cost report attributes to it.

Layout

staging/       one model per source table. Rename, recast. NO joins.
               stg_<source>__<table>
intermediate/  the joins nobody wants in a mart
marts/         fct_ and dim_ (Ch. 6's vocabulary)

Model size: the largest unit you would be willing to test as a whole. One assertion that means "this is correct" → right size. Five assertions about five intermediates → too big. A trivially true assertion → too small.

⚠️ Two degenerate shapes, both from applying a rule instead of a judgment: the 700-line monolith, and 400 models doing one join each.

Jinja

The line is: can a SQL developer who does not know Jinja read the file and predict the output? If not, you have written a program that writes SQL, and you now own two things.

• dbt compile and read target/compiled/ whenever you write more than an if
• A loop over a literal list is fine. A loop over a query result is a build-time dependency, invisible in the DAG, failing with messages that point at the wrong file
• Reused logic → a macro with a docstring

Clever Jinja is the most common technical debt in a mature dbt project — written by your best engineer, on a Friday, and it worked.

Tests

Generic (YAML, per column) · singular (a SQL file returning offenders) · unit (fixture in, expectation out — for the handful of models with real business logic).

config: {severity: warn, error_if: ">100", warn_if: ">0"}

↑ how you introduce a test to a codebase that does not yet pass it.

🔎 dbt build, never dbt run && dbt test. A failed test stops the graph, so the mart holds yesterday's correct data. Stale beats wrong, and it is not close — a stale dashboard prompts a question, a wrong one prompts a decision.

⚠️ A test that cannot fail is not a test. not_null on a NOT NULL source column · unique on a row_number() key · accepted_values generated from SELECT DISTINCT. For each test, describe the upstream change that breaks it. If you cannot, delete it — a 100% pass rate is being read as evidence.

Every standard dbt test describes rows that EXIST. unique, not_null, accepted_values, and relationships all pass on an empty table. Add a volume assertion — floor well below expected, because the aim is catching zero, not catching low. A test that fires on ordinary fluctuation gets turned off.

The blindness

dbt transforms what is in the warehouse. It cannot know what should have been. Ingestion dies at 02:00 → every model builds, every test passes, the DAG is green, and the dashboard shows yesterday's numbers as today's.

Three checks, all of them:

1. dbt source freshness — a separate command dbt build does not run. Schedule it before the build; a check inside a build that did not run has also not run.
2. A volume floor on each fact.
3. A freshness test on the mart, not only the source.

⚠️ A threshold that cannot fire (error_after: 7 days on an hourly source) is worse than a missing check — it appears on the audit as present.

The unknown member (Case Study 2)

COALESCE(customer_key, -1) is correct — nulls would silently drop rows from every aggregation — and it defeats the relationships test, because -1 is a real row.

Unknown-member volume is a health metric. A few a day is late arrivals. Two hundred a day is a broken load, and nothing else in the project will say so.

Every graceful-degradation mechanism moves a failure from loud to quiet, and therefore creates a monitoring obligation. Retries, defaults, COALESCE, fallbacks, circuit breakers. Add the mechanism, add the metric — or you made the system quieter, not more robust.

Selection, environments, CI

model+      what I broke        +model    rebuild its lineage
@model      the safe rebuild    state:modified+   the CI selector

⚠️ Comma is intersection, space is union. The wrong one in a scheduled job builds a subset and reports success. Run dbt ls --select <the selector> first and count.

profiles.yml: gitignore it. Per-developer schemas (dev_{{ env_var('USER') }}) are what make dbt usable by more than one person. Credentials via env_var() — this is the most commonly committed secret in the data world.

💸 Slim CI: --select state:modified+ --defer --state ./prod-manifest. Kestrel: 22.0 min and $2.93 → 3.1 min and $0.41. $624.96/month, $7,499.52/year — and the money is the less important half. CI slower than a developer's patience gets routed around, and a control that gets routed around provides no assurance while still appearing on the diagram.

The two case studies, compressed

One missing ref() in ninety models → intermittent stale mart for eleven weeks → filed as flakiness → a month closed $498,630.14 short. "Intermittent" is a decision to stop investigating, and a cheap workaround is what makes it stick. The audit found four more, two of them worse and symptomless.

Six days of green on a frozen dimension → 1,082 orders on the Unknown member, 2.7% of GMV → the load was fixed and the data was not, because the rows were already below an incremental watermark. "The pipeline is fixed" is not "the data is fixed." Make data impact a required field in the incident template, with a repair command or an explicit "none, because."

A monitoring path that has never been exercised end to end is a hypothesis. Kestrel's first alert fire drill found three of eleven routes dead.