dbt — Models, Tests, Docs, Warehouse-Native ELT

Review boundary — 10 October 2026: SQL and the incremental-state fixture below are bounded local teaching checks. The dbt/PostgreSQL project is a source-reviewed reference, not an executed warehouse deployment. dbt documentation is mutable; the evidence captures the exact pages used. No runtime or cost reduction is promised.

Learning goal: Build a staging → intermediate → mart project, explain its dependency and testing contracts, and choose ELT and materialisation from correctness and workload requirements.

Why this session exists

A warehouse can contain several incompatible definitions of revenue even when every query is valid SQL. Putting transformation code in version control, naming dependencies, reviewing changes, testing business invariants and publishing documentation makes those definitions inspectable. dbt supplies a build system around analytics code; it neither invented version-controlled SQL nor displaced every ETL system. Its SQL models run on the selected adapter's database. Python models and hosted orchestration exist too, but this lesson's project is specifically SQL dbt.

You will be able to explain ETL versus ELT; organise staging, intermediate and marts; write four generic data tests and a singular test; use macros, ref() and source(); and defend view, table, incremental or ephemeral materialisation.

Prerequisites

Bring SQL joins and aggregations, dimensional grain, and the distinction between scheduling a job and executing it. Sessions 045 and 050 cover modelling and orchestration. The prerequisite is the concept, not the old misnumbered session labels.

Optional resources for S051

Use these optional pointers for orientation, deeper exploration or a return visit alongside the project: introductory video pointer, second video pointer, and the same second pointer for a project-side revisit. Reading options are the projects page, dbt background essay, and best-practices index; the version-qualified references below remain the basis for technical claims.

These are optional destinations, not verified video titles, creators, content, viewing recommendations or durations. The former “35 min total” is not a completion estimate: the same URL had both 45- and 60-minute labels, alongside other estimates. Nor do the former “tightest” introduction or “save you a year of arguments” descriptions establish quality or a benefit. Choose what helps, skip the rest, and use the project and recall exercises to check understanding.

(a) Intuition · 5 min

Imagine loose SQL scripts on several analysts' laptops. One subtracts refunds, another counts only paid orders, and a third sums every amount. A shared repository does not decide the right definition, but it lets the team agree on one, record its inputs and prove it on examples. “Git for SQL” is an analogy for that workflow, not a claim that dbt replaces Git.

A SQL model is normally a SELECT in a .sql file. {{ ref('stg_orders') }} resolves another dbt resource and declares a graph edge; {{ source('raw', 'orders') }} resolves a declared external source. A literal database name may execute successfully while hiding the dependency from dbt. A macro is reusable Jinja code evaluated while constructing SQL, not a row-by-row Python UDF inside the database.

The four ideas that unlock everything

1. Model: the query defines a relation and its grain; materialisation defines how to maintain it.
2. Dependencies: ref and source expose lineage and support environment-specific relation naming. Dependencies hidden behind runtime Jinja branches need explicit handling.
3. Data tests: a default singular or generic test identifies violating rows. The default zero-failure rule can be configured with severity and thresholds; a passing run is not a database constraint.
4. Macros: centralise genuinely shared expressions without hiding business meaning. For example, a money conversion macro can take a column expression and expand to cast(amount_cents as numeric) / 100. Document its currency/unit assumptions and inspect the compiled result.

dbt compile constructs compiled SQL but can still require database access for introspection or macros; it is not an offline-security guarantee. dbt run executes models. dbt test executes tests. dbt build runs selected resources and tests in DAG order, using test failures to skip affected downstream nodes. Selection and indirect-test rules matter. None of these commands alone schedules an upstream ingestion system.

A quick history so you know why the world looks like this

ETL transforms before loading the analytical destination; ELT loads first and transforms there. Both predate dbt. Elastic analytical compute and inexpensive retained inputs made warehouse-native transformation attractive for many workloads, but there is no universal year when ETL ended. The useful progression is from separately managed scripts to versioned dependency graphs, tests and documented data products; multi-project governance and semantic definitions address coordination beyond a single graph. These are capabilities, not evidence for the original valuation, market-share or startup-adoption claims.

Deriving the ETL versus ELT decision

Begin with a fixed-capacity warehouse shared by users and transformations. Moving heavy preparation elsewhere can protect interactive queries. Now suppose compute can scale independently of storage: running the transformation near retained data may remove a copy, a serialisation boundary and an engine to operate. This reduces some operational work, not necessarily CPU-seconds or the bill; engines, query plans, network egress, concurrency and pricing differ.

Retaining raw inputs can make a corrected full rebuild possible, but retention needs privacy, residency, access and deletion rules. “Keep everything forever” is not a consequence of cheap storage. ETL remains appropriate for redacting prohibited fields before loading, specialised transformations or constrained destination capacity. ELT is attractive when the destination handles the required SQL efficiently and controlled raw retention supports reconciliation. Calculate total storage, movement, compute and operating cost, then verify the freshness budget. The architecture follows more than its pricing model.

The staging → intermediate → mart layer cake

Staging is usually one model per source relation, with explicit columns and controlled cleaning. Intermediate models express reusable joins or calculations; an unnecessary pass-through intermediate layer is not a requirement. Marts expose consumer-ready entities and documented grain. In this project dim_customers derives lifetime paid-order metrics from staging, not from fct_orders: it is a sibling, not downstream of the fact. Therefore a fact-only test failure does not automatically block this dimension. Grain and actual graph edges determine the blast radius.

Materialisations — what actually happens at build time

• View: stores a definition, normally not a second persisted result. Reads execute its logic, subject to engine optimisation/caching. It still has metadata and compute costs. Good for inexpensive staging transformations; a heavily reused expensive view can shift cost onto every consumer.
• Table: rebuilds a persisted relation on a selected run. A full rebuild is simple to reason about when the inputs form a coherent snapshot. It costs storage and work proportional to the chosen query, not a universal “few GB” cutoff.
• Incremental: maintains an existing target from a selected change set. Append, merge, delete-and-insert and other strategies are adapter/version dependent. unique_key is optional for append-only use and is not an enforced uniqueness constraint. is_incremental() is true only when the target exists as a table, the model is configured incremental, and full-refresh is not active.
• Ephemeral: dbt inlines the model as a CTE in consumers. There is no separately queryable relation. Reuse can duplicate compiled SQL and work; the database decides whether the CTE is inlined or materialised internally.

Test types you'll actually use

unique finds duplicate non-null key values; pair it with not_null. accepted_values tests a declared value domain; decide nullability separately. relationships checks non-null child values against the parent field and also needs a separate not-null test for mandatory foreign keys. Optional relationships may legitimately be null. A package test such as dbt_utils.expression_is_true requires the package and its pinned compatible version; it is not one of the built-ins.

A singular test is a SQL file returning bad rows. amount <= 0 alone misses NULL because SQL's unknown predicate does not pass WHERE. The example therefore uses amount is null or amount <= 0. Source, intermediate join and mart tests all matter: clean inputs do not prevent fan-out or wrong aggregation in the middle. A test detects an invariant breach; enforcement requires a publication gate that consumers cannot bypass.

dbt is a Makefile for SQL

The target/dependency analogy explains ordering and parallel runnable nodes. It does not mean every materialisation is a single CTAS: adapters may execute temporary relations, transactional statements, grants, hooks and merges. Inspect target/compiled for query logic and target/run plus run artifacts/database logs for executed materialisation details. Slow work may arise from SQL, warehouse capacity, thread/concurrency choices or compilation overhead; “dbt cannot be slow” is not a diagnosis.

Reference inspection commands, not executed here:

dbt compile --select fct_orders
cat target/compiled/my_dbt/models/marts/fct_orders.sql
# Requires existing staging views; use +fct_orders to include ancestors.
dbt run --select fct_orders
cat target/run/my_dbt/models/marts/fct_orders.sql

The inspection target is deliberately the table fct_orders. Its helper int_orders_enriched is ephemeral: selecting that helper for dbt run does not build an independent relation or supply a normal executed materialisation to inspect. Compile the consumer to see its injected CTE; run the consumer to inspect the adapter's table-building statements. The file paths assume this project's default target directory and name, not every dbt layout.

State selection compares the current graph with a saved manifest: state:modified+ means modified nodes and their descendants. Supply --state with the prior artifact directory, separate it from the current target output, and use deferral only with an understood environment policy. Macros, variables, sources and indirect tests affect selection. A shorter selection is not proof of complete integration coverage or a guaranteed CI speedup.

Trace the graph into a compiled query

For the supplied models, raw.orders → stg_orders → int_orders_enriched → fct_orders is one path; stg_customers is also a parent of the intermediate. Both staging models feed dim_customers directly. The relationships test depends on the fact and dimension, but that does not add a model dependency from fct_orders to dim_customers. Do not confuse test edges, resource selection and materialised data flow.

The following is a hand-expanded illustration, not captured dbt output. Assume the profile resolves staging relations into warehouse.analytics (PostgreSQL SQL addresses these as analytics.stg_orders and analytics.stg_customers). A compiled consumer can have this query shape:

-- Hand-expanded query shape, not a dbt-generated artifact.
with __dbt__cte__int_orders_enriched as (
  select o.order_id, o.customer_id, o.amount, o.status, o.order_ts,
         c.city as customer_city
  from analytics.stg_orders o
  left join analytics.stg_customers c using (customer_id)
)
select order_id, customer_id, amount, status, order_ts, customer_city
from __dbt__cte__int_orders_enriched;

Jinja has disappeared and the ephemeral SQL is included in its consumer. This SELECT is not the entire execution: adapter materialisation can create intermediate tables, swap names, run hooks and apply grants. A macro such as cents-to-currency also expands into SQL before the database evaluates rows. Inspect its expansion for numeric casts and currency units; reuse alone cannot prove business meaning. On the seed data, this join produces orders 10 and 11 in London. Duplicate customer 1 and it produces four fact rows: source cleanliness and join-cardinality tests are required even when the compiled SQL is syntactically valid.

Full rebuild, incremental, or lookback plus merge

A full rebuild incorporates retained late rows, corrections and deletions visible in its input snapshot. It is a useful reference result, but a successful rebuild does not prove that input snapshot is complete. Incremental maintenance saves work when change processing is cheaper than recomputation; target lookup and merge costs can still grow with history.

An event-time cursor order_ts > max(order_ts) misses a newly ingested old event. It also misses updates to older orders, customer-city changes joined onto existing orders, source deletes and ties at the maximum timestamp. A seven-day lookback repairs only changes included by that contract. A CDC/change-sequence cursor can identify changed keys, including tombstones, but needs retained ordered changes, restart-safe checkpointing and a strategy for changed dimensions. No prior successful uniqueness test guarantees the next batch is unique.

Use a full table while it fits the budget, or a view where appropriate. Convert to incremental with measured need and explicit change semantics. Compare the entire incrementally maintained relation with a full reconstruction on adversarial fixtures. Schedule reconciliation/backfill based on retained history and risk, not an arbitrary weekly promise that drift is impossible.

(c) Hands-on · 25 min

A complete SQL dbt reference project

The following uses the classic dbt Core 1.8-era PostgreSQL YAML syntax (tests, source-level freshness). Mutable docs now describe newer configuration locations too; do not mix their new options into this version contract. It assumes a separately provisioned disposable PostgreSQL database named warehouse, dbt-postgres compatible with Core 1.8, and a securely supplied local credential. No PostgreSQL server, dbt command, package installation or docs server was run for this review.

Project layout:

my_dbt/
  dbt_project.yml
  models/sources.yml
  models/staging/stg_orders.sql
  models/staging/stg_customers.sql
  models/staging/_staging.yml
  models/intermediate/int_orders_enriched.sql
  models/marts/fct_orders.sql
  models/marts/dim_customers.sql
  models/marts/_marts.yml
  tests/assert_positive_amount.sql
~/.dbt/profiles.yml  (outside the project; never commit credentials)

Create the disposable inputs before dbt. Integer cents avoid floating-point currency ambiguity:

create schema raw;
create table raw.customers (
  customer_id bigint, email text, city text, signup_date date,
  _loaded_at timestamptz
);
create table raw.orders (
  order_id bigint, customer_id bigint, amount_cents bigint,
  status text, order_date timestamptz, _loaded_at timestamptz
);
insert into raw.customers values
 (1,'ALICE@example.test',' London ','2026-01-01','2026-10-08 12:00:00+00'),
 (2,'bob@example.test','Paris','2026-01-02','2026-10-08 12:00:00+00');
insert into raw.orders values
 (10,1,4200,'paid','2026-10-07 10:00:00+00','2026-10-08 12:00:00+00'),
 (11,1,1000,'pending','2026-10-07 11:00:00+00','2026-10-08 12:00:00+00');

dbt_project.yml:

name: my_dbt
version: '1.0.0'
config-version: 2
profile: my_dbt
model-paths: [models]
test-paths: [tests]
models:
  my_dbt:
    staging:
      +materialized: view
    intermediate:
      +materialized: ephemeral
    marts:
      +materialized: table

~/.dbt/profiles.yml (the environment variable is a placeholder for the operator's supported credential setup, not a literal password):

my_dbt:
  target: dev
  outputs:
    dev:
      type: postgres
      host: 127.0.0.1
      user: dbt_demo
      password: "{{ env_var('DBT_DEMO_PASSWORD') }}"
      port: 5432
      dbname: warehouse
      schema: analytics
      threads: 2

models/sources.yml:

version: 2
sources:
  - name: raw
    database: warehouse
    schema: raw
    loaded_at_field: _loaded_at
    freshness:
      warn_after: {count: 12, period: hour}
      error_after: {count: 24, period: hour}
    tables:
      - name: orders
        columns:
          - name: order_id
            tests: [unique, not_null]
      - name: customers
        columns:
          - name: customer_id
            tests: [unique, not_null]

stg_orders.sql and stg_customers.sql, respectively:

select order_id::bigint as order_id,
       customer_id::bigint as customer_id,
       amount_cents::numeric / 100 as amount,
       status::text as status, order_date as order_ts
from {{ source('raw', 'orders') }}

select customer_id::bigint as customer_id,
       lower(email) as email, trim(city) as city,
       signup_date::date as signup_date
from {{ source('raw', 'customers') }}

Do not silently filter null keys away and then claim the input passed validation. Quarantine, if required, must have its own rejected-row counts and reconciliation.

models/staging/_staging.yml:

version: 2
models:
  - name: stg_orders
    columns:
      - name: order_id
        tests: [unique, not_null]
      - name: customer_id
        tests: [not_null]
      - name: status
        tests:
          - not_null
          - accepted_values:
              values: [pending, paid, refunded, cancelled]
  - name: stg_customers
    columns:
      - name: customer_id
        tests: [unique, not_null]

int_orders_enriched.sql, then fct_orders.sql:

select o.order_id, o.customer_id, o.amount, o.status, o.order_ts,
       c.city as customer_city
from {{ ref('stg_orders') }} o
left join {{ ref('stg_customers') }} c using (customer_id)

select order_id, customer_id, amount, status, order_ts, customer_city
from {{ ref('int_orders_enriched') }}

dim_customers.sql preserves customers without paid orders and counts only matched paid orders:

select c.customer_id, c.email, c.city, c.signup_date,
       count(o.order_id) as lifetime_orders,
       coalesce(sum(o.amount), 0) as lifetime_revenue
from {{ ref('stg_customers') }} c
left join {{ ref('stg_orders') }} o
  on o.customer_id = c.customer_id and o.status = 'paid'
group by c.customer_id, c.email, c.city, c.signup_date

models/marts/_marts.yml:

version: 2
models:
  - name: fct_orders
    description: One row per order, including pending orders.
    columns:
      - name: order_id
        tests: [unique, not_null]
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id
  - name: dim_customers
    description: One row per customer with paid-order aggregates.
    columns:
      - name: customer_id
        tests: [unique, not_null]

tests/assert_positive_amount.sql:

select order_id, amount
from {{ ref('fct_orders') }}
where status = 'paid' and (amount is null or amount <= 0)

What each block does

Project defaults choose persistence, source YAML declares input identity/freshness, staging cleans without hiding rejects, the intermediate join centralises enrichment, and the marts define two distinct grains. ref is the graph contract; tests are observations and gates, not primary/foreign-key DDL. Explicit descriptions and dbt docs generate produce documentation artifacts; a serving/UI step is separate and unperformed.

On the seed fixture the fact has two rows. Customer 1 has one paid order and revenue 42; customer 2 has zero paid orders and revenue 0. dbt build should construct selected resources and run applicable tests. Run dbt source freshness separately: fixed fixture timestamps eventually become stale, deliberately illustrating wall-clock freshness rather than promising evergreen success. Freshness queries cost compute and alert delivery must be integrated explicitly.

Break a test and inspect the actual DAG

In a disposable input, duplicate order 10 with negative paid amount. A full selection including source/staging tests should detect duplicate keys before building affected descendants. A selection of only fct_orders+ does not automatically rebuild every ancestor. A merge may reject multiple matches before any fact uniqueness test runs. The singular test can detect the negative amount if its input was built. dim_customers is not a descendant of fct_orders in this project. Ordinary test severity: error versus warn determines test outcomes; --warn-error does not turn failing errors into warnings. Inspect run results rather than predicting one universal skip sequence.

Incremental is a correctness policy, not merely a speed flag

This exact standard-library program maintains a change-log projection. It is not a dbt adapter simulation. Sequence order, explicit tombstones and atomic checkpoint/effect handling are its declared contract. It proves why event time alone is insufficient.

# exact-program: dbt_changes
import sqlite3
 
CHANGES = [
    (1, 10, '2026-10-07', 4200, 'upsert'),
    (2, 11, '2026-10-08', 1000, 'upsert'),
    (3, 12, '2026-10-01', 700, 'upsert'),  # late arrival
    (4, 10, '2026-10-07', 4500, 'upsert'), # historical correction
    (5, 11, '2026-10-08', None, 'delete'),
]
 
def apply(db, changes, fail=False):
    with db:
        cursor = db.execute('select n from checkpoint').fetchone()[0]
        for seq, key, event_day, cents, op in sorted(changes):
            if seq <= cursor:
                continue
            if op == 'delete':
                db.execute('delete from target where id=?', (key,))
            elif op == 'upsert':
                db.execute('insert into target values (?,?,?) on conflict(id) '
                           'do update set day=excluded.day,cents=excluded.cents',
                           (key, event_day, cents))
            else:
                raise ValueError('unknown operation')
            db.execute('update checkpoint set n=?', (seq,))
        if fail:
            raise RuntimeError('crash before commit')
 
if __name__ == '__main__':
    db = sqlite3.connect(':memory:')
    db.executescript('create table target(id integer primary key,day text,cents integer);'
                     'create table checkpoint(n integer);insert into checkpoint values(0);')
    apply(db, CHANGES[:2])
    try:
        apply(db, CHANGES[2:], fail=True)
    except RuntimeError:
        pass
    assert db.execute('select n from checkpoint').fetchone()[0] == 2
    apply(db, CHANGES[2:]); apply(db, CHANGES)
    got = db.execute('select * from target order by id').fetchall()
    assert got == [(10, '2026-10-07', 4500), (12, '2026-10-01', 700)]
    print(got)
    db.close()

The input contract requires each sequence to name one immutable change; duplicates with conflicting content must be rejected upstream, and unavailable/missing history requires reconciliation. A source deletion is an event, not an absent row you can infer from an incomplete batch. The independent test reconstructs latest state by key, compares all prefixes and retries, and injects the rollback failure.

An incremental dbt model with an explicit input contract

For a separate append-only change-history model, declare raw.order_changes in sources.yml. Its input schema is change_seq, order_id, event_day, amount_cents, operation; the sequence is globally unique, immutable and monotonically exposed at a complete committed frontier. Late business events receive new sequence numbers, deletes are retained tombstones, and no earlier sequence may appear after a later frontier is exposed. Test change_seq with unique/not-null, validate operation values and audit sequence completeness. If the source cannot guarantee this, a max-sequence filter is insufficient too.

-- dbt/PostgreSQL reference, not executed through a dbt adapter.
{{ config(materialized='incremental', unique_key='change_seq') }}
select change_seq, order_id, event_day, amount_cents, operation
from {{ source('raw', 'order_changes') }}
{% if is_incremental() %}
where change_seq > (select coalesce(max(change_seq), 0) from {{ this }})
{% endif %}

Both branches are valid SQL after Jinja compilation. unique_key describes identity for the supported strategy; it does not enforce the source contract. This model stores the immutable changes, not the current fact. A downstream relation ranks by change_seq desc per order, keeps the latest change, then removes keys whose latest operation is delete. Apply that ordering before filtering tombstones, otherwise deleted orders can resurrect. The exact local change program demonstrates that current-state projection and rollback behaviour; it does not certify adapter transaction semantics. Customer-city changes need their own maintained dimension/join policy, and history retention must support the intended full reconciliation.

Schema changes need a historical-data plan

Suppose an incremental model adds net_amount. append_new_columns can add the target column; sync_all_columns also handles removed columns and supported type changes. Neither fills the old rows' values. fail stops on divergence; ignore is not a general compatibility guarantee, and removing a selected column can still fail. The captured official documentation describes top-level changes, not automatic nested-field reconciliation; actual DDL and cost remain adapter-specific.

For 1,000 old orders and 10 new ones, a successful schema update can leave 1,000 historical net_amount values unpopulated while the 10 new rows are populated. Define the historical expression and units, determine whether retained raw inputs can reproduce it, then choose an authorised full refresh or keyed backfill with reconciliation. A rename is not evidence that old values moved safely; test the new schema and the historical population separately. A logic change without a column change has the same old/new semantic-split risk.

Worked failure: merging order 10's corrected amount does not update its old customer city unless the selected change set revisits that order or the dimension is joined at read time. A changed-dimension policy must choose current attributes versus historically valid attributes; neither a unique key nor schema synchronisation decides that business question.

(d) Production reality · 15 min

Three hypothetical failure analyses, not attributed company incidents:

• Unowned revenue graph: many models reuse competing revenue definitions. Assign domain owners, document grain and tests, and agree on semantic definitions. Multi-project tooling helps organise dependencies but cannot decide the definition for the business.
• Rebuild exceeds budget: measure expensive nodes and target scans, compare table/view/incremental plans, and prove late/update/delete correctness before changing materialisation. There is no defensible 8×/60% result without measurements.
• Schema drift: an added field can leak through select *; renamed/type-changed fields can break compilation or meaning. Enumerate columns, review contracts and test values. on_schema_change: sync_all_columns changes the target schema; it is not an alerting policy and does not backfill historical values. fail is an alternative when an unexpected change must stop the run.

Where this shows up in the rest of the plan

Lakehouse formats provide transactional tables; DQ adds population/distribution checks; governance attaches ownership, access and retention. CDC may feed raw change records; feature engineering may consume marts. Adapters do not erase database differences, and a feature store still needs point-in-time correctness. These connections preserve the original route's concepts without its incorrect session numbers or “every BI system uses dbt” assertion.

(e) Recall + stretch · 10 min

Failure exercise and worked answer

A late order has an old event timestamp but a new ingestion sequence. The change sequence can find it if retained and complete; an event-time maximum cannot. Test that row, a corrected amount, a customer attribute change, a delete and a conflicting duplicate against a full reconstruction.

Explain-out-loud test

Explain the model/ref/test/DAG workflow, name each layer's responsibility, then defend the four persistence choices with a failure case rather than a size slogan.

Series route and pacing for S051

Revisit S050 · Orchestration — Airflow, or continue to S052 · Lakehouse — Delta / Iceberg / Hudi. These are the numbered-series navigation pointers; the hub just below supplies the broader route, not evidence that a linked lesson has been completed or reviewed here.

Use the series' intuition → visual explanation → hands-on → operational caveats → recall sequence as a study route. The original 130-session framing describes the planned series, not a completion claim. Its 90-minute duration and the retained section-time labels are suggested pacing, not measured reading or execution times; optional preparation and setup may take additional sessions. Operational examples are teaching material, not verified production war stories or proof of deployment readiness.

What comes next

The adjacent topic is table-format snapshots and retention, followed by quality and governance. Hub: The 6-Month Learning Plan.

Sources and review boundary

Official sources: incremental models, data tests, build semantics, materialisations, Jinja and macros, project structure, state caveats, freshness. October 8 captures were re-extracted and their governing passages reread on October 10; the current review adds graph/query and schema-backfill checks in foundations-batch-33. Local SQLite semantics do not establish PostgreSQL/dbt integration success.