Chapter 19: dbt: The Transformation Framework That Changed Data Engineering

"Every shop before dbt had a dependency graph. It lived in a wiki page, and it was wrong."

Overview

Chapter 18 gave you the SQL. This chapter is about the eighty percent of transformation work that is not writing the SQL: knowing what order to run it in, knowing whether it is still correct, knowing who depends on it, and being able to change it without a two-day audit first.

dbt did not invent any of that. It packaged it, gave it a file layout, and made the packaged version the default — which turned out to matter more than any individual feature.

What you will be able to do: structure a dbt project that a team can work in; use ref() and source() correctly and explain why they are not conveniences; choose a materialization from evidence rather than habit; write tests that can fail; run a CI build that costs cents instead of dollars; and say precisely what dbt does not do.

Two of those matter more than the rest, and both are things dbt will not tell you. A hardcoded table name compiles, runs, and returns the right answer while silently deleting a graph edge — Case Study 1 is eleven weeks of that. And dbt is structurally blind to data that never arrived: every model builds and every test passes on a dead pipeline, which is Case Study 2. Neither is a bug in dbt. Both are consequences of what dbt is, and the chapter is largely about the assertions that cover them.

19.1 What dbt Actually Is

Strip away the marketing and dbt is three things wrapped around a SQL file.

One: a templating engine. Your model file is Jinja-templated SQL. dbt renders the template into plain SQL and sends it to your warehouse. The rendered output is on disk in target/compiled/, and looking at it is the single most useful debugging habit in this chapter.

Two: a dependency graph. Because you reference other models through a function — {{ ref('stg_orders') }} rather than analytics.stg_orders — dbt can read your project and know that this model depends on that one. From that it derives a DAG, and from the DAG it derives execution order, parallelism, and the blast radius of any change.

Three: a test runner. Assertions live beside the models as configuration, run in the same command, and stop downstream work when they fail.

That is the whole product. Everything else — documentation, lineage graphs, packages, the semantic layer — is built on those three, and understanding them as three separate things will save you from most of the confusion that follows.

What dbt is not, and each of these is a real mistake teams make:

• It is not an ingestion tool. dbt cannot get data into your warehouse. Everything in Part III — extraction, CDC, Kafka, API clients — happens before dbt runs and is dbt's problem only in the sense that dbt will faithfully transform whatever garbage arrives.
• It is not a compute engine. dbt sends SQL to Snowflake, BigQuery, DuckDB, Postgres, Databricks. Your warehouse does the work. dbt's own process is small enough to run on a laptop while the query it dispatched consumes a hundred nodes.
• It is not an orchestrator. dbt build runs a DAG of dbt models, once, when you invoke it. Something else has to decide when to invoke it, retry it, and coordinate it with the twelve things that are not dbt. That is Chapter 24.
• It is not a data quality platform, though it has tests. Chapter 23 is about the difference.

🎓 Interview Angle — "What does dbt do?"

A weak answer describes the features. A strong answer is three sentences:

"dbt compiles templated SQL, infers a dependency graph from the ref() calls, and runs the models in dependency order with their tests. It doesn't move data and it doesn't run compute — it sends SQL to a warehouse. The value is that the DAG and the tests are derived from the code rather than maintained alongside it."

That last clause is the one that separates people who have used dbt from people who have read about it. Every pre-dbt shop had a dependency graph; it lived in a wiki page, an Airflow DAG file, or somebody's head, and it was wrong. dbt's contribution is that the graph cannot drift from the code because it is the code.

19.2 The Model Is a SELECT Statement

A dbt model is a file containing one SELECT. Not a CREATE TABLE, not an INSERT, not a transaction — a SELECT.

-- models/staging/stg_orders.sql
SELECT
    order_id,
    customer_id,
    status,
    total_cents,                         -- integer cents. Chapter 6 §6.5.
    ordered_at
  FROM {{ source('kestrel_app', 'orders') }}
 WHERE _deleted_at IS NULL

Run dbt run --select stg_orders and dbt renders that template, wraps it in whatever DDL the materialization requires, and executes it. The wrapping is dbt's job and it changes with the materialization; the file you wrote and maintain stays a SELECT.

This is a bigger deal than it looks. The pre-dbt equivalent was a stored procedure or an ETL script containing a SELECT surrounded by fifty lines of drop-create-insert-swap-grant boilerplate, and the boilerplate is where the bugs lived: the swap that was not atomic, the grant that was forgotten on Tuesday's rebuild, the DROP that ran when the SELECT had failed.

Separating the query from its persistence is the core idea. You describe what the table should contain; dbt decides how to make the warehouse contain it.

The corollary is worth stating: if you find yourself wanting to write DDL in a model, something is wrong — either you want a different materialization, or you want a pre-hook, or you want an operation (dbt run-operation), or you want something that is not dbt's job.

19.3 ref() and source(): The Two Functions That Matter

Everything dbt does downstream of compilation depends on two Jinja functions, and they are the part of dbt people most often get casually wrong.

source() names data dbt did not create. The raw tables your ingestion pipeline landed. They are declared once, in YAML:

# models/staging/_sources.yml
sources:
  - name: kestrel_app
    schema: bronze
    tables:
      - name: orders
        loaded_at_field: _ingested_at
        freshness:
          warn_after:  {count: 90,  period: minute}
          error_after: {count: 180, period: minute}

ref() names another dbt model. And that is what builds the graph:

SELECT o.*, c.region
  FROM {{ ref('stg_orders') }}    o
  JOIN {{ ref('stg_customers') }} c USING (customer_id)

⚠️ Failure Mode — the hardcoded table name

This compiles, runs, and returns correct results:

sql SELECT * FROM analytics.stg_orders -- instead of {{ ref('stg_orders') }}

In development it is indistinguishable from the correct version. In production it is a silent defect with four separate consequences:

1. The DAG edge does not exist. dbt does not know this model depends on stg_orders, so it may run them in either order — and will run them in the wrong order eventually, when the graph changes shape enough to alter the topological sort. The failure is intermittent and looks like flakiness.

2. It points at the wrong environment. ref() resolves to the target schema — dev in dev, prod in prod. A hardcoded analytics.stg_orders points at production from your laptop, so your development build silently reads production data and, on a model with a pre-hook, can write to it.

3. Lineage is wrong. The docs site, the impact analysis, and every "what breaks if I change this?" question now have a missing edge.

4. Slim CI silently skips it. state:modified+ selects modified models and their descendants. A model connected by a hardcoded name is not a descendant, so CI does not test it. The one place you would have caught the problem is the place the problem disables.

The lint is three lines and every project should have it. manifest_audit.py --hardcoded in this chapter's code/ directory does it against the compiled manifest, which catches the cases a naive grep does not.

Why a function and not a naming convention? Because a convention can be violated by accident and a function cannot be resolved without registering the edge. This is the same principle as Chapter 17's schema registry: the enforcement is a side effect of the thing you had to do anyway.

19.4 Materializations: The Decision You Make Most Often

A materialization is the strategy dbt uses to persist a model. There are five that matter.

The mistake is choosing by habit rather than by traffic. A view is free to build and expensive to query; a table is the reverse. Which is correct depends entirely on the ratio of builds to queries, and that ratio is a fact about your dashboards, not about your taste.

💸 Cost Check — the view chain that cost $2,845 a month

Kestrel's gold.daily_revenue sat at the end of a four-model chain, all materialized as views because "views are free."

A view is free to build. Every query recomputes the whole chain. The compiled query behind the dashboard scans 42 GB, and the executive dashboard loads it 400 times a day.

On BigQuery's frozen basis of $6.25/TiB (Chapter 8 §8.4):

$$42\ \text{GB} = \frac{42 \times 10^9}{2^{40}} = 0.03819\ \text{TiB} > \quad\Rightarrow\quad 0.03819 \times \$6.25 = \$0.2387\ \text{per query}$$

$$400\ \text{queries/day} \times \$0.2387 = \$95.48/\text{day} = \$2{,}864/\text{month}$$

Materialized as a table, the chain is built once a night and the dashboard scans the 180 MB result:

$$\underbrace{\$0.2387}_{\text{nightly build}} + \underbrace{400 \times \$0.001023}_{\text{queries}} > = \$0.648/\text{day} = \$19.44/\text{month}$$

$2,845 a month, $34,146 a year, from one line of configuration.

The general shape: view optimizes the case where you build often and query rarely, which is almost never true of a serving model. Run code/manifest_audit.py --views to list every view in your project with its downstream reference count; anything with a dashboard behind it is a candidate.

Setting one is a line in the model or a block in dbt_project.yml:

{{ config(materialized='table') }}

# dbt_project.yml -- defaults by directory, overridden per model
models:
  kestrel:
    staging:      {+materialized: view}
    intermediate: {+materialized: ephemeral}
    marts:        {+materialized: table}

Directory-level defaults with per-model overrides is the layout that survives a growing team. Staging models are thin and rarely queried directly; marts are queried constantly. The defaults encode the reasoning once so nobody has to rediscover it per model.

📐 Design Decision — ephemeral is not free

ephemeral models compile into a CTE inside every model that references them. No object is created, nothing is stored, and the intermediate logic is named and testable. It reads like a pure win.

Two costs, and the second is the one that bites.

They do not exist, so you cannot query them. Debugging a mart whose logic lives in four ephemeral models means reading the compiled SQL, because there is nothing to SELECT from. This is a real productivity tax on exactly the models that are hardest to debug.

An ephemeral model referenced by six models is computed six times. dbt inlines the CTE into each caller; the warehouse does not deduplicate across queries. A cheap ephemeral used widely is fine; an expensive one is a hidden multiplier that no cost report attributes to it.

Rule of thumb: ephemeral is right for logic used by one or two models that you do not want to materialize. Beyond that, make it a table and let the six callers share the work.

19.5 Project Layout, and Why the Boring One Wins

dbt does not enforce a directory structure. The community converged on one anyway, and the convergence is worth adopting even where you disagree with the details, because it makes every dbt project navigable by anyone who has seen another dbt project.

models/
  staging/          one model per source table. Rename, recast, nothing else.
    _sources.yml
    _staging.yml    tests + descriptions
    stg_kestrel__orders.sql
    stg_kestrel__customers.sql
  intermediate/     the joins and reshaping nobody wants in a mart
    int_order_items_joined.sql
  marts/
    finance/
      fct_order_item.sql
      dim_customer.sql
    marketing/
      fct_session.sql
macros/
tests/              singular tests -- one SQL file, one assertion
seeds/              small static CSVs under version control
snapshots/          SCD2 -- Chapter 20

Three conventions do most of the work.

One staging model per source table, and nothing but renaming, recasting, and light cleaning in it. No joins in staging. The rule sounds arbitrary until you need to answer "what does our system do with the raw orders table?" and the answer is one file.

The stg_<source>__<table> double underscore disambiguates when two systems both have a customers table, which they eventually will.

Marts are named fct_ or dim_ — Chapter 6's vocabulary, applied. A model whose name starts with neither is a signal to ask what grain it is at.

⚠️ Failure Mode — the two shapes a dbt project degenerates into

The monolith. One 700-line model with fourteen CTEs, because CTEs are free and splitting felt like ceremony. It cannot be tested at any intermediate point, a change anywhere requires understanding all of it, and two people cannot work on it at once.

The confetti. Four hundred models, each doing one join, because "small models are good." The DAG is unreadable, every change touches nine files, and dbt build spends more time on warehouse round trips than on work.

Both come from applying a rule instead of a judgment. The judgment: a model should be the largest unit you would be willing to test as a whole. If you can state one assertion that means "this model is correct," it is the right size. If you need five assertions about five different intermediate results, it is too big. If the assertion is trivially true because the model barely does anything, it is too small.

19.6 Jinja: Useful, and Then Suddenly Not

Jinja is what makes a dbt model a template rather than a file. In small doses it is the best thing in the project.

-- One line that changes behaviour between full and incremental runs.
{% if is_incremental() %}
  WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}

-- A loop that would otherwise be forty lines of copy-paste.
SELECT order_id,
{% for status in ['pending','paid','shipped','delivered','cancelled'] %}
    SUM(CASE WHEN status = '{{ status }}' THEN 1 ELSE 0 END) AS n_{{ status }}
    {%- if not loop.last %},{% endif %}
{% endfor %}
  FROM {{ ref('stg_orders') }}
 GROUP BY 1

And then it goes wrong. The failure is gradual and it has a recognizable signature: you can no longer read the model and know what SQL it produces.

⚠️ Failure Mode — the model you have to compile to read

Kestrel had a model with nested {% for %} loops over a macro that returned a list built from a dbt_utils.get_column_values() call against another model. It generated the right SQL. It was genuinely clever.

Nobody could modify it, including the person who wrote it, four months later.

The test: can a competent 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.

Three rules that keep it usable:

• dbt compile and read target/compiled/ whenever you write more than an if. If the compiled output surprises you, so will it surprise the next reader.
• A loop over a literal list is fine. A loop over a query result is a build-time dependency that runs during parsing, is invisible in the DAG, and fails in ways whose error messages point at the wrong file.
• Put anything reused into a macro with a docstring, so the complexity is in one named place rather than smeared across nine models.

Clever Jinja is the most common form of technical debt in a mature dbt project, and it is particularly insidious because it was written by your best engineer, on a Friday, and it worked.

19.7 Tests: The Feature That Justifies the Tool

A dbt test is a query that should return zero rows. That is the entire model, and its simplicity is why it gets used.

Generic tests are declared in YAML and apply to a column:

models:
  - name: stg_orders
    columns:
      - name: order_id
        tests: [unique, not_null]
      - name: status
        tests:
          - accepted_values:
              values: ['pending','paid','picked','shipped',
                       'delivered','cancelled','refunded']
      - name: customer_id
        tests:
          - relationships:
              to: ref('stg_customers')
              field: customer_id

Four built-ins — unique, not_null, accepted_values, relationships — cover a surprising fraction of real defects, and the accepted_values test is Chapter 17 §17.9's enum gap closed with five lines.

Singular tests are a SQL file that returns the offending rows:

-- tests/assert_no_future_orders.sql
SELECT order_id, ordered_at
  FROM {{ ref('stg_orders') }}
 WHERE ordered_at > CURRENT_TIMESTAMP + INTERVAL '1 hour'

Severity is configurable, and the option most teams never find is the threshold:

tests:
  - not_null:
      config:
        severity: warn                # or error
        error_if: ">100"              # error above 100 failing rows
        warn_if:  ">0"

That combination — warn on any, error above a threshold — is how you introduce a test to a codebase that does not yet pass it, without either ignoring it or blocking every deploy.

🔎 Read the Plan — dbt build, not dbt run && dbt test

These look equivalent. They are not, and the difference is the whole value of the tests.

text dbt run && dbt test dbt build ───────────────────── ────────────────────────────── build ALL models build stg_orders then test all test stg_orders ← FAILS SKIP everything downstream

With run && test, a failed test on stg_orders is discovered after every downstream mart has already been built from the bad data and after the dashboard has already served it. You get an alert and a table full of wrong numbers.

With build, the failure stops the graph. The mart is not rebuilt; it holds yesterday's correct data; the dashboard is stale rather than wrong.

Stale beats wrong, and it is not close. A stale dashboard prompts a question. A wrong dashboard prompts a decision.

dbt build interleaves models, tests, snapshots, and seeds in DAG order. Use it. There is no remaining reason to run dbt run and dbt test separately in production, and the split survives mostly because it is what the tutorials showed in 2019.

⚠️ Failure Mode — the test that cannot fail

A dbt project with 400 tests and a 100% pass rate for eight months is not a well-tested project. It is an untested project with 400 lines of YAML.

Three ways a test becomes decorative:

not_null on a column that is NOT NULL in the source. The warehouse already enforces it. The test can never fail and adds runtime to every build.

unique on a surrogate key you generated with row_number(). It is unique by construction. The test asserts that your ROW_NUMBER implementation works.

accepted_values listing every value currently present, generated by someone running SELECT DISTINCT. It encodes the data rather than the contract, and it will fail on the first legitimate new value while never catching a wrong one.

The check: for each test, can you describe a plausible upstream change that makes it fail? If not, delete it — it is costing build time and, worse, contributing to a pass rate that is being read as evidence.

Unit tests (dbt 1.8+) are a different thing and worth knowing about: they run a model against fixture input and assert on the output, so they test the logic rather than the current data.

unit_tests:
  - name: test_net_revenue_excludes_cancelled
    model: fct_order_item
    given:
      - input: ref('stg_orders')
        rows:
          - {order_id: 1, status: 'paid',      total_cents: 1000}
          - {order_id: 2, status: 'cancelled', total_cents: 9999}
    expect:
      rows:
        - {order_id: 1, net_revenue_cents: 1000}

Use them for the handful of models with real business logic — revenue recognition, tier assignment, anything with a CASE expression somebody argued about. They are slow and verbose, and they are the only way to test a branch that your production data does not currently exercise.

19.8 Sources, Freshness, and the Thing dbt Cannot See

Declaring sources buys three things: ref()-like environment resolution, lineage that starts at the raw table, and freshness checks.

dbt source freshness

dbt runs SELECT MAX(loaded_at_field) against each source and compares it to your thresholds. It is a handful of cheap queries and it answers the question that dbt otherwise cannot: is the input to this whole DAG actually current?

⚠️ Failure Mode — dbt is structurally blind to the failure that matters most

dbt transforms what is in the warehouse. It cannot know what should have been.

If the ingestion pipeline fails at 02:00 and lands nothing, the 03:00 dbt build will run perfectly. Every model builds. Every test passes — unique passes on unchanged data, not_null passes, relationships passes. The DAG is green.

And the dashboard shows yesterday's numbers as though they were today's, which is Chapter 25's definition of the worst possible failure: silent, confident, and wrong.

dbt source freshness is the only thing in dbt that catches it, and it is the check most projects have not configured, because it lives in YAML nobody edits and produces no output when things are fine.

Two more, and you want all three:

• A row-count assertion on the fact. dbt_utils.expression_is_true with a plausible daily minimum. Kestrel's is 3,000 order lines — well under the 17,753 daily average (Chapter 6 §6.9), because the point is catching zero, not catching low.
• A freshness test on the mart itself, not only the source, so a stalled model mid-DAG is caught as well as a stalled load.

Run dbt source freshness as a separate scheduled job, not only as a build step — if the build does not run at all, a check inside it does not either. Chapter 24 §24.6.

19.9 Documentation and the DAG as an Artifact

dbt docs generate && dbt docs serve

This produces a browsable site with every model, its columns, its tests, its compiled SQL, and an interactive lineage graph. It costs one command, and the lineage graph alone changes conversations with analysts.

Descriptions live in the same YAML as the tests:

models:
  - name: fct_order_item
    description: >
      One row per order line. Grain: (order_id, line_number). Excludes
      cancelled orders. Revenue is NET of returns -- see Chapter 6 §6.7 for
      why gross and net are separate columns rather than one.
    columns:
      - name: net_revenue_cents
        description: "Integer cents. Gross minus returns, allocated per line."

The description that earns its keep states the grain and the exclusions. "One row per order line" and "excludes cancelled orders" between them prevent most of the questions an analyst would otherwise ask you, and both are facts a reader cannot recover from the SQL without reading all of it.

Exposures close the loop downstream:

exposures:
  - name: executive_daily_revenue
    type: dashboard
    maturity: high
    url: https://bi.internal/dash/17
    owner: {name: Analytics, email: analytics@example.com}
    depends_on: [ref('fct_order_item'), ref('dim_date')]

An exposure is Chapter 17's consumer list, expressed in dbt. It puts the dashboard on the lineage graph, makes dbt build --select +exposure:executive_daily_revenue meaningful, and — the part that matters — means the answer to "what breaks if I change this?" includes things that are not dbt models.

🏭 From the Pipeline — the documentation that is true because it is executable

Every data team has written a data dictionary. Most have written several, because the previous one drifted out of date and was abandoned.

The reason dbt docs do not rot is not that dbt is better at documentation. It is that three quarters of the content is generated from things that must be correct for the build to run at all — model names, column names, types, lineage, tests. Only the prose descriptions can drift, and they are the part that changes least.

The generalizable lesson: documentation survives in proportion to how much of it is a by-product of something else. A wiki page describing your DAG will be wrong within a quarter. A DAG rendered from the code cannot be.

Which is also the argument for writing the description in the YAML next to the tests rather than in a Confluence page — not because YAML is pleasant, but because it is in the diff of the pull request that changes the model.

19.10 Packages, and the Four Worth Having

packages.yml plus dbt deps installs community macros as source, which is the same mechanism dbt uses for your own macros.

packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.3.0", "<2.0.0"]

dbt_utils is effectively part of dbt. generate_surrogate_key, date_spine, star, union_relations, expression_is_true, unique_combination_of_columns — that last one being the grain test Chapter 2 promised, in three lines:

tests:
  - dbt_utils.unique_combination_of_columns:
      combination_of_columns: [order_id, line_number]

dbt_expectations ports the Great Expectations vocabulary into dbt tests — distributions, row counts between bounds, regex matching. Chapter 23 covers when you want this versus Great Expectations itself.

codegen generates the boilerplate YAML for a new source or model. It is a time-saver and it is also how the "accepted_values listing every current value" antipattern gets created, so read what it produced.

audit_helper compares two relations column by column and reports the differences — which is Chapter 18 Case Study 1's EXCEPT-in-both-directions verification, packaged. It is the right tool for every refactor and every migration in Chapter 37.

🧭 Version Note — dbt-core 1.9.1

This book pins dbt-core 1.9.1 and dbt-duckdb 1.9.1 (requirements.txt). Behaviour that moved recently and that you will meet in older material:

• microbatch — an incremental strategy that processes a backfill as a series of bounded time windows rather than one enormous MERGE. New in 1.9, and directly relevant to Chapter 20 §20.6.
• Unit tests arrived in 1.8. Anything written before that describes only data tests.
• Snapshots configured in YAML rather than in a {% snapshot %} block, plus dbt_valid_to_current, landed in 1.9. Chapter 20 §20.8 uses the YAML form.
• dbt_utils.surrogate_key became generate_surrogate_key in dbt_utils 1.0, and the hashing changed. Do not mix the two in one project — the keys will not match, and nothing will tell you.
• Metrics left dbt-core for MetricFlow and the Semantic Layer in 1.6. Tutorials showing metrics: in a model YAML are pre-1.6.

When you read a dbt blog post, check the date first. The tool moved quickly between 2020 and 2024, and a lot of confidently-worded advice describes a version nobody runs.

19.11 dbt Core, dbt Cloud, and What You Are Actually Choosing

dbt Core is the open-source CLI. It is the whole transformation engine, it is Apache-licensed, and everything in this chapter runs on it.

dbt Cloud is a hosted product around it: a scheduler, an IDE, a hosted docs site, CI integration, and the semantic layer.

What you are choosing is not features, it is who operates the scheduler. dbt Core plus Airflow (Chapter 24) plus a CI pipeline (Chapter 27) gives you the same capability and costs you the operation of three systems. dbt Cloud gives you one vendor and a bill.

The honest guidance: a team of two without a platform engineer should use dbt Cloud or a similar managed option, because the alternative is that one of them becomes a part-time Airflow operator. A team that already runs Airflow for eleven other things should use dbt Core, because the scheduler is already paid for. Neither is a technical argument, and treating it as one is how this decision gets made badly.

19.12 Node Selection: The Syntax That Makes a Large Project Workable

A ninety-model project is unusable if every command runs everything. dbt's selection syntax is how you avoid that, and it is worth learning properly because most people learn --select model_name and stop.

dbt build --select fct_order_item          # just this model
dbt build --select fct_order_item+         # it and everything DOWNSTREAM
dbt build --select +fct_order_item         # it and everything UPSTREAM
dbt build --select +fct_order_item+        # both directions
dbt build --select @fct_order_item         # upstream, the model, and ALL
                                           #   descendants of those parents
dbt build --select 2+fct_order_item        # two generations upstream only

The four you will actually use, and what each is for:

model+ — you changed a model and want to know what you broke. This is the one you run before opening a pull request.

+model — a mart is wrong and you want to rebuild its whole lineage from source. This is the one you run at 03:00.

@model — the safest possible rebuild. It includes the parents, the model, and everything that descends from any of those parents, which is the set that could be affected by a shared upstream change. Slower, and correct when you are unsure.

state:modified+ — the CI selector. §19.14.

Selection also takes tags, paths, resource types, and set operations:

dbt build --select tag:nightly
dbt build --select path:models/marts/finance
dbt build --select resource_type:test
dbt build --select "tag:finance,+fct_order_item"     # comma = INTERSECTION
dbt build --select tag:finance +fct_order_item       # space = UNION
dbt build --select tag:nightly --exclude tag:slow

⚠️ Failure Mode — comma is intersection, space is union

This is the single most misread piece of dbt syntax, and it fails quietly in the direction that hurts.

bash dbt build --select "tag:finance,tag:hourly" # models with BOTH tags dbt build --select tag:finance tag:hourly # models with EITHER tag

A scheduled job written with a comma when a space was meant builds a small subset and reports success. Nothing errors. The models you thought were running have not run for a month, and the first symptom is a stale mart that nobody connects to a scheduling change made in the spring.

The habit that prevents it: whenever you write a selector into a scheduled job, run dbt ls --select <the same selector> first and count the result. dbt ls is the dry run for selection, it is instant, and almost nobody uses it.

19.13 Environments, Targets, and the File You Must Not Commit

profiles.yml maps a target name to a set of connection details. A target is an environment.

kestrel:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: "{{ env_var('KESTREL_DUCKDB', 'kestrel.duckdb') }}"
      schema: "dev_{{ env_var('USER', 'local') }}"     # per-developer schema
      threads: 4
    prod:
      type: snowflake
      account:   "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
      user:      "{{ env_var('SNOWFLAKE_USER') }}"
      # Never a literal. Chapter 28 §28.7 covers where this comes from.
      password:  "{{ env_var('SNOWFLAKE_PASSWORD') }}"
      warehouse: TRANSFORM_WH
      schema:    analytics
      threads:   12

Three things this file is doing, and each is load-bearing.

Per-developer schemas. dev_alice, dev_bob. Every engineer builds into their own schema against the same warehouse, ref() resolves within it, and nobody's dbt run overwrites anybody's work. This is the feature that makes dbt usable by more than one person, and it is four words of configuration.

threads is dbt's parallelism: how many models it will run concurrently where the DAG allows. More threads is faster until it is not — the ceiling is your warehouse's concurrency, and past it you are queuing rather than parallelizing. Twelve is a reasonable production starting point; measure before raising it.

env_var() everywhere a credential would otherwise be. profiles.yml is the most commonly committed secret in the data world, and it is committed by well-meaning people because it lives next to the project and looks like configuration. Put it in .gitignore and put the credentials in the environment, which is where Chapter 28's secret manager will deliver them.

🔐 Privacy & Governance — the target you build into is a permission boundary

A developer running dbt build against production data in their own schema is doing something with two separate risks, and teams routinely address only the first.

The obvious one: they might write somewhere they should not. Per-developer schemas and a read-only role on the source solve it.

The one that gets missed: dev_alice.fct_order_item is now a full copy of production data, including every column of PII, sitting in a schema with development-grade access controls — often readable by the whole analytics role, retained indefinitely, and invisible to whatever inventory Chapter 30 maintains.

Three controls, in order of how much they buy:

• A drop policy on dev schemas — 14 days, enforced by a scheduled task, not by intention.
• Masking policies applied by role, so the dev role sees email hashed and nothing changes in the SQL. Snowflake and BigQuery both support this; it is Chapter 31 §31.6.
• A sampled dev source. dbt build --vars '{sample_days: 7}' with a WHERE in the staging models. Faster, cheaper, and most development does not need eighteen months of PII.

The one that does not work: telling people to be careful. Chapter 17 §17.9 again — a notice is not a control.

19.14 Slim CI, and What It Costs Not to Have It

The naive CI pipeline for a dbt project builds everything on every pull request. It is correct and it is unaffordable in both money and patience.

Slim CI builds only what changed and what depends on it, by comparing your branch against a stored manifest from the last production run.

# In CI. --state points at the production manifest.json.
dbt build --select state:modified+ --defer --state ./prod-manifest

Two flags, doing two different things:

state:modified+ selects models whose definition differs from the production manifest, plus their descendants. Three changed models with nine descendants is twelve models instead of ninety.

--defer is the part that makes it possible. Those twelve models reference upstream models that CI did not build. With --defer, any ref() to a model not in the current run resolves to the production relation instead. You build twelve models against real production upstreams, without building the other seventy-eight.

💸 Cost Check — Kestrel's CI, before and after

Kestrel's project is 90 models. A full build on a Snowflake Medium warehouse — 4 credits/hour at the frozen $2.00/credit (Chapter 3) — takes 22.0 minutes:

$$4 \times \tfrac{22.0}{60} = 1.467\ \text{credits} = \$2.93\ \text{per run}$$

The team opens about 62 pull requests a month and pushes to each roughly four times, so CI runs 248 times:

$$248 \times \$2.93 = \$727.47/\text{month}$$

With slim CI, the median run builds 12 models and takes 3.1 minutes:

$$4 \times \tfrac{3.1}{60} = 0.207\ \text{credits} = \$0.41 > \quad\Rightarrow\quad 248 \times \$0.41 = \$102.51/\text{month}$$

$624.96 a month. $7,499.52 a year.

And the money is the less important half. A 22-minute CI run is one that people stop waiting for; they push, switch tasks, and come back after lunch. A 3.1-minute run is one they watch. CI that is slower than a developer's patience gets routed around — merged on red, re-run until green, or disabled for "urgent" changes — and a control that gets routed around provides no assurance while still appearing on the architecture diagram.

Two requirements people miss. You need somewhere to keep the production manifest.json — an S3 bucket written by the production job and read by CI, which is four lines of Chapter 27's pipeline. And --defer means CI reads production data, so CI's role needs production read access, which is a governance decision to make deliberately rather than discover.

📏 Scale Note — what breaks as a dbt project grows

A dbt project is comfortable to a few hundred models and then several things degrade at once, none of which is the warehouse.

``text models parse time what starts hurting ───────────────────────────────────────────────────────────────────────── < 50 < 2 s nothing 50-200 2-10 s a full build is too slow for CI -> slim CI (§19.14) 200-500 10-30 s parse time is felt on EVERY command, includingdbt ls; macros and packages dominate it 500-1500 30-90 s the DAG is unreadable; ownership is unclear;state:modified+` selects half the project because everything depends on one staging model

1500 minutes one project is the wrong unit. Split, and accept cross-project refs or a mesh (ch 35) ```

Two of those are worth acting on early because the fix is cheap then and expensive later.

Parse time is a tax on every command, not only on builds. It is dominated by macro complexity and by packages, and a project that reaches thirty seconds has made every developer's inner loop thirty seconds longer, forever. Exercise 19.18's test — "could a SQL developer predict the compiled output?" — is also a parse-time discipline.

And the state:modified+ explosion is a modelling problem wearing a tooling costume. If every model descends from one wide staging model, then changing that model selects everything and slim CI stops being slim. The fix is narrower staging models, one per source table (§19.5), which is the layout advice given for a different reason.

Kestrel's project is 42 models, comfortably in the second row, and the thing that keeps it there is not restraint about model count — it is that intermediate models are deleted when the mart they served is deleted, which almost never happens by default.

The number to watch is not the model count. It is the median number of models selected by state:modified+ over your last fifty pull requests. If it is climbing, your DAG is getting wider rather than your project getting bigger, and those need different responses.

🧪 Try It — read the compiled SQL, once, deliberately

The single most useful debugging habit in this chapter, and most people never do it.

bash cd platform/transform/kestrel_dbt dbt compile --select fct_order_line cat target/compiled/kestrel/models/marts/core/fct_order_line.sql

Read the whole file, slowly, and answer five questions:

text 1. What did every ref() render to? -> the SCHEMA is the answer to "am I reading prod or dev?" 2. What did the is_incremental() block render to on THIS run? -> on a first run it renders to nothing. That is why a first run and a second run are different queries. 3. Are there blank lines where a {% for %} was? -> whitespace control (§19.6). Cosmetic until the model is 200 lines and you are reading it at 3 a.m. 4. Is there a subquery you did not write? -> an ephemeral model, inlined. It is inlined into every consumer (§19.4), and this is where you can see the cost. 5. Would a SQL developer who has never seen Jinja be able to predict this output from the source file? -> §19.6's test. If no, the model is too clever.

Then do the thing that makes it stick: run the compiled SQL directly against the warehouse. Paste it into a query console. It runs, unmodified, because it is just SQL — and that realisation is what makes dbt stop feeling like a framework and start feeling like a templating engine, which is what §19.1 says it is.

Two follow-ups worth doing once:

Compile the same model with --target dev and with --target prod and diff the two. The only difference should be schema names. If anything else differs, you have environment-dependent logic, which is a class of bug that is invisible until it is a production incident.

And compile a model after deliberately breaking a ref() into a literal table name. The compiled output is nearly identical — which is exactly why the defect in Chapter 19's Case Study 1 survived four months.

🔁 Idempotency Check — dbt build twice should be a no-op

Run the whole project twice against unchanged sources, and the warehouse should be identical. It frequently is not, and the reasons are worth enumerating because each is a different bug.

text what differs on the second run why ───────────────────────────────────────────────────────────────────────── nothing correct row counts grow an `append` incremental strategy values change a dedup with no unique tiebreak, or a model reading now() a snapshot gains rows check_cols is too wide, or the source has a churning column a table is rebuilt from scratch a `table` materialization -- fine, and expensive

The CI job is five lines and it is Exercise 20.23(e):

bash dbt build --select tag:gold dbt run-operation snapshot_gold_to scratch_run1 dbt build --select tag:gold dbt run-operation snapshot_gold_to scratch_run2 dbt run-operation assert_identical --args '{a: scratch_run1, b: scratch_run2}'

The assertion must diff in both directions (Chapter 20 §20.12), and it must compare values rather than counts — a dedup that picks a different row on the second run keeps the count identical.

Two dbt-specific causes worth knowing about.

run_started_at and dbt_utils.current_timestamp() in a model make it non-idempotent by construction. They are legitimate in an audit column and illegitimate in anything a downstream model filters on. Chapter 27's purity check greps for exactly this.

And a snapshot is a stateful object that a full-refresh destroys. dbt build --full-refresh on a project containing snapshots rebuilds the dimension from the source's current state and throws away every historical version. It is idempotent in the sense that running it twice gives the same result; it is catastrophic in the sense that the result is not the one you had. Exclude snapshots from --full-refresh, explicitly, in the command.

19.15 The Kestrel dbt Project

🧱 Kestrel Platform — Increment 19: kestrel_dbt

The transformation layer becomes a real dbt project. Chapter 18's SQL files move into it largely unchanged, which is the point — dbt adopts your SQL rather than replacing it.

text platform/transform/kestrel_dbt/ dbt_project.yml profiles.yml ← DuckDB local; env_var() for warehouse targets packages.yml ← dbt_utils, audit_helper models/ staging/ _sources.yml ← 12 app tables + clickstream, with freshness _staging.yml ← tests and descriptions stg_kestrel__orders.sql stg_kestrel__order_items.sql stg_kestrel__customers.sql stg_kestrel__products.sql stg_clickstream__events.sql intermediate/ int_order_items_deduped.sql ← Ch. 18 §18.7, with the tiebreaker int_sessions.sql ← Ch. 18 §18.9, with the overlap marts/ finance/ fct_order_item.sql dim_customer.sql dim_product.sql marketing/ fct_session.sql tests/ assert_no_future_orders.sql assert_revenue_reconciles_to_source.sql macros/ cents_to_dollars.sql assert_overlap_exceeds_gap.sql ← Ch. 18 CS2, as a build-time guard

Five things this increment must get right, each of which is a section of this chapter applied:

1. Every reference is ref() or source(). manifest_audit.py --hardcoded returns clean, and it runs in CI.
2. Freshness is configured on all thirteen sources, and dbt source freshness runs as its own scheduled job.
3. Materializations are chosen and recorded. Staging views, intermediate ephemeral, marts tables. Each mart's config block carries a one-line comment saying why.
4. Every mart has a grain test — dbt_utils.unique_combination_of_columns — and every test can fail. Run manifest_audit.py --decorative and justify anything it flags.
5. dbt build, never dbt run then dbt test. In the Makefile, in CI, in the runbook.

The exercise that matters is 19.24: take the four staging models, remove one ref() in favour of a hardcoded name, and observe that everything still passes. Then run the audit and watch it fail. That gap — between "works" and "correct" — is what this chapter is about.

19.16 Summary

dbt is three things around a SQL file: a templating engine, a dependency graph inferred from ref(), and a test runner. It does not move data, does not run compute, and is not an orchestrator.

A model is a SELECT. Separating the query from its persistence is the core idea; the boilerplate dbt replaced is where the bugs used to live.

ref() and source() are not conveniences. A hardcoded table name compiles, runs, returns correct results — and deletes a DAG edge, points at the wrong environment, breaks lineage, and makes slim CI skip the model. Lint for it.

Materialization is a traffic decision, not a taste decision. A view is free to build and recomputes on every query; Kestrel's four-view chain cost $2,845 a month more than the same models as tables. Ephemeral models are inlined into every caller, so a widely-used one is computed many times.

Layout: one staging model per source table, stg_<source>__<table>, marts named fct_/dim_. The right model size is the largest unit you would test as a whole.

Jinja stops being useful at the point where you must compile the model to read it. Loops over literal lists are fine; loops over query results are invisible build-time dependencies.

Use dbt build, not dbt run && dbt test — a failed test stops the graph, so the mart holds yesterday's correct data instead of today's wrong data. Stale beats wrong.

A test that cannot fail is not a test. For each one, describe the upstream change that would break it; if you cannot, delete it, because a 100% pass rate is being read as evidence.

dbt is structurally blind to data that never arrived. Every model builds and every test passes on a stalled pipeline. dbt source freshness, a minimum row-count assertion, and a freshness test on the mart — all three, and schedule the freshness check separately from the build.

Documentation survives in proportion to how much of it is a by-product of the build. That is why dbt docs do not rot and wiki pages do.

Chapter 20 takes the materialization table's third row — incremental — and spends a chapter on it, because it is where most of the difficulty and nearly all of the correctness bugs live.

Key terms: materialization · ref() · source() · staging model · mart · ephemeral model · generic test · singular test · unit test · source freshness · exposure · slim CI · manifest · dbt package · seed