Appendix E: dbt Reference

Pinned to dbt-core 1.9.1 with dbt-duckdb 1.9.1 (Appendix A).

E.1 Project Layout

dbt_project.yml
profiles.yml                  # NOT in the repository
models/
  staging/                    # silver: one model per source table
    _sources.yml
    _staging__models.yml
    stg_orders.sql
  intermediate/               # optional; not consumed directly
    int_orders_joined.sql
  marts/                      # gold
    core/
      dim_customer.sql
      fct_order_line.sql
    finance/
      daily_revenue.sql
seeds/                        # small versioned CSVs
snapshots/                    # SCD Type 2
macros/
tests/                        # singular tests
analyses/

# dbt_project.yml
name: kestrel
version: "1.0.0"
profile: kestrel
model-paths: ["models"]

models:
  kestrel:
    staging:
      +materialized: view
      +schema: silver
      +tags: ["silver"]
    marts:
      +materialized: table
      +schema: gold
      +tags: ["gold"]
      finance:
        +materialized: incremental

Configs cascade by directory, most specific wins, and a {{ config() }} block in the model wins over all of them.

E.2 Materializations

	Writes	Rebuilds	Use for
`view`	nothing	every query	staging, cheap transforms
`table`	full table	every run	small marts, anything queried often
`incremental`	new rows	only new rows	large facts
`ephemeral`	nothing	inlined as a CTE	small shared logic
`materialized_view`	a MV	engine-managed	engine-dependent

{{ config(materialized='table', tags=['gold']) }}

Start with view for staging and table for marts. Go incremental when the full refresh becomes the problem, and not before — incremental is where the correctness bugs live (ch20).

E.3 Incremental Models

{{ config(
    materialized='incremental',
    unique_key=['order_line_id'],
    incremental_strategy='merge',
    on_schema_change='append_new_columns',
    partition_by={'field': 'event_date', 'data_type': 'date'}
) }}

SELECT *
  FROM {{ ref('stg_order_lines') }}
{% if is_incremental() %}
  -- the lookback is what catches late-arriving data (ch20)
  WHERE event_date >= (SELECT max(event_date) FROM {{ this }})
                      - INTERVAL '3 days'
{% endif %}

Strategies:

Strategy	How	Idempotent?
`append`	inserts	✗ — a rerun duplicates
`delete+insert`	deletes matching keys, inserts	✓
`merge`	upserts on `unique_key`	✓
`insert_overwrite`	replaces whole partitions	✓ — and the cleanest
`microbatch` (1.9+)	one bounded time window per batch	✓ by construction

append is the default on some adapters and it is not idempotent. State the strategy explicitly.

microbatch is the 1.9 addition worth knowing:

{{ config(
    materialized='incremental',
    incremental_strategy='microbatch',
    event_time='placed_at',
    batch_size='day',
    lookback=3,
    begin='2026-01-01'
) }}
SELECT * FROM {{ ref('stg_orders') }}

Backfills become parallel, and each batch is a separate idempotent write.

E.4 Sources, Freshness, and Exposures

# models/staging/_sources.yml
version: 2
sources:
  - name: kestrel_raw
    schema: bronze
    loaded_at_field: _ingested_at
    freshness:
      warn_after:  {count: 2, period: hour}
      error_after: {count: 6, period: hour}
    tables:
      - name: orders_raw
        columns:
          - name: order_id
            tests: [not_null]
      - name: supplier_feed
        freshness: null              # this one is weekly; opt out
        meta:
          external: true
          upstream_owner: "supplier-b, ops@supplier-b.example"
          upstream_controls: "none known; no change notification"

# exposures: who breaks if this breaks (ch30)
exposures:
  - name: executive_daily_revenue
    type: dashboard
    maturity: high
    url: https://bi.example/dash/17
    owner: {name: Finance, email: finance@example.com}
    depends_on: [ref('daily_revenue')]

dbt source freshness before the build is the assertion that stops you transforming stale data.

E.5 Tests

# generic tests: four lines, most of the value
models:
  - name: fct_order_line
    description: "One row per order line, after refunds. Grain: order_line_id."
    columns:
      - name: order_line_id
        tests: [unique, not_null]
      - name: customer_sk
        tests:
          - not_null
          - relationships:
              to: ref('dim_customer')
              field: customer_sk
      - name: status
        tests:
          - accepted_values:
              values: ['placed', 'shipped', 'refunded']
    tests:
      # the grain test (ch23): nine lines, catches every fan-out
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns: [order_id, line_number]

-- tests/assert_revenue_reconciles.sql -- a singular test returns FAILING rows
SELECT g.revenue_date, g.revenue_cents, s.revenue_cents AS source_cents
  FROM {{ ref('daily_revenue') }} g
  JOIN {{ source('kestrel_raw', 'source_daily_totals') }} s USING (revenue_date)
 WHERE g.revenue_cents <> s.revenue_cents

Severity and thresholds:

      - name: refund_cents
        tests:
          - dbt_utils.accepted_range:
              min_value: 0
              config:
                severity: warn        # or error
                error_if: ">100"      # fail only above 100 failing rows
                warn_if: ">0"

Unit tests (1.8+) — for the gnarly CASE expression, not for everything:

unit_tests:
  - name: test_revenue_excludes_gift_cards
    model: fct_order_line
    given:
      - input: ref('stg_order_lines')
        rows:
          - {order_id: 1, sku: 'KS-1',  cents: 2500}
          - {order_id: 1, sku: 'GC-10', cents: 5000}
    expect:
      rows:
        - {order_id: 1, revenue_cents: 2500}

E.6 Jinja and Macros

-- macros/cents_to_dollars.sql
{% macro cents_to_dollars(column, decimals=2) -%}
    round(({{ column }} / 100.0)::numeric, {{ decimals }})
{%- endmacro %}

-- usage
SELECT {{ cents_to_dollars('net_revenue_cents') }} AS net_revenue FROM ...

The whitespace control (-%}, {%-) matters for readable compiled SQL, which you will read.

Useful built-ins:

{{ ref('stg_orders') }}                      -- builds the DAG. Never write a table name.
{{ source('kestrel_raw', 'orders_raw') }}
{{ this }}                                   -- the current model's relation
{{ var('start_date', '2026-01-01') }}        -- with a default
{{ env_var('DBT_WAREHOUSE') }}               -- credentials come from here, never inline
{{ target.name }}                            -- dev / prod
{{ run_started_at }}
{% if execute %}                             -- guard anything querying during parse

{% if execute %} matters because dbt parses every model twice, and a run_query at parse time fails or is slow.

Debugging: dbt compile --select my_model, then read target/compiled/.../my_model.sql. The compiled SQL is the truth, and reading it resolves most Jinja confusion in a minute.

E.7 Node Selection

dbt build --select stg_orders                # one model
dbt build --select stg_orders+               # and everything downstream
dbt build --select +fct_order_line           # and everything upstream
dbt build --select +fct_order_line+          # both
dbt build --select tag:gold
dbt build --select path:models/marts/finance
dbt build --select stg_orders+ --exclude tag:slow
dbt build --select state:modified+ --state ./prod-manifest   # slim CI
dbt build --select source_status:fresher+ --state ./prod-manifest
dbt build --select "@fct_order_line"         # the model, its parents, and their children

state:modified+ with a stored production manifest is what makes CI fast (ch19, ch27) — build what changed and what depends on it, not the project.

E.8 Snapshots

{% snapshot dim_customer_snapshot %}
{{ config(
    target_schema='snapshots',
    unique_key='customer_id',
    strategy='timestamp',
    updated_at='updated_at',
    invalidate_hard_deletes=True
) }}
SELECT * FROM {{ source('kestrel_raw', 'customers') }}
{% endsnapshot %}

This is SCD Type 2, and it is a point-in-time-correct feature table (ch32) — a fact most dbt users have not connected.

strategy='check' with check_cols when the source has no reliable updated_at, which per Chapter 13 is often.

invalidate_hard_deletes=True is off by default and is usually what you want.

E.9 Commands

dbt deps                    # install packages
dbt debug                   # connection and profile check -- run this first
dbt seed
dbt run
dbt test
dbt build                   # run + test + seed + snapshot, in DAG order
dbt snapshot
dbt source freshness
dbt docs generate && dbt docs serve
dbt clean
dbt ls --select tag:gold --resource-type model
dbt run-operation my_macro --args '{"day": "2026-11-14"}'
dbt parse                   # produces manifest.json without running anything

Prefer dbt build to dbt run + dbt test. It interleaves them in DAG order, so a failing test stops the models downstream of it rather than after everything has been built.

E.10 Practices Worth Adopting

One staging model per source table, doing only renaming, casting, and deduplication (§34.3's silver rule).

ref() always. A hardcoded table name is a model outside the DAG, and Chapter 38 Case Study 2 shows what that does to a rebuild.

Document the grain in description. It is §30.2's most valuable field, and dbt puts it in the docs for free.

Store the production manifest.json as a CI artifact. Everything in §E.7 depends on it.

Credentials from env_var() only. validate.py fails the build on a literal.

And test the sources, not only the models. A not_null on a source column fails where the problem is, rather than three models later.