All lessons

Module 5: Model it in SQL · Lesson 5.4 · about 40 min

dbt: SQL models with software discipline

Lay out a dbt project in staging, intermediate and marts; use ref, tests and docs; build incremental models and snapshots; run it on several warehouses.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

Module 5 · about 40 minutes, plus a long lab · read first: 5.1 to 5.3, 4.3. You’ll be able to lay out a dbt project, write staging and mart models with ref and source, add tests and docs, build an incremental fact with a look-back and an SCD2 snapshot, and point the same project at DuckDB, Postgres or Azure SQL.

dbt turns a folder of SQL files into software: each file is one table, dbt works out the order to build them, and every build runs the tests that prove the tables are still right.

1. Why this matters

Until now your SQL lived in scripts that ran in an order you remembered. dbt (the open-source dbt Core) adds what software teams take for granted: version control, dependencies, tests, documentation and separate environments. It appears in a large share of analytics engineering and BI job adverts, it is free, and it works with Azure SQL, Postgres and DuckDB through adapters.

A dbt project is like a recipe book where each recipe names the other recipes it needs (“use the stock from page 12”). The kitchen (dbt) reads all the recipes, works out what to cook first, and tastes each dish (tests) before it goes out.

2. Project shape

restaurant_dbt/
├── dbt_project.yml
├── profiles.yml            # connection targets; secrets come from env vars
├── models/
│   ├── staging/            # one model per source table: rename, cast, light cleaning   → views
│   │   ├── _sources.yml
│   │   └── stg_pos__order_lines.sql
│   ├── intermediate/       # joins and business rules                                 → ephemeral or views
│   └── marts/              # facts and dimensions for BI                              → tables or incremental
│       ├── fct_order_lines.sql
│       ├── dim_menu_item.sql
│       └── _marts.yml      # descriptions and tests
├── snapshots/              # SCD2 from sources that only give current state
├── seeds/                  # small CSVs: holidays, channel mapping
└── tests/                  # one-off SQL tests

Naming conventions make a project readable by a team: stg_<source>__<entity>, int_<verb>, fct_ and dim_; one staging model per source table; no business logic in staging; every mart model has a description, a grain sentence and a primary-key test.

3. The basics

-- models/staging/stg_pos__order_lines.sql
with src as (select * from {{ source('pos', 'order_lines') }})
select
  restaurant_id,
  order_id,
  cast(order_ts as timestamp)                                   as order_ts,
  {{ dbt.dateadd('hour', -5, 'cast(order_ts as timestamp)') }}  as business_ts,   -- 05:00 cut-off, works on any adapter
  channel, menu_item_id, qty, price,
  qty * price                                                   as line_revenue
from src
# models/marts/_marts.yml
models:
  - name: fct_order_lines
    description: "One row per till order line after removing re-sent batches. Business date uses the 05:00 cut-off."
    columns:
      - name: order_line_key
        data_tests: [unique, not_null]
      - name: menu_item_key
        data_tests:
          - relationships: { to: ref('dim_menu_item'), field: menu_item_key }
      - name: channel
        data_tests:
          - accepted_values: { values: ['counter', 'drive_thru', 'app', 'delivery'] }
  • {{ source('pos', 'order_lines') }} points at a raw table you declared in _sources.yml.
  • {{ ref('dim_menu_item') }} points at another model and records the dependency.
  • dbt build runs seeds, models, snapshots and tests in graph order, and skips everything downstream of a failure.

(Older dbt versions call data_tests: simply tests:. Check the docs for your version.)

Read the graph. dbt parses every ref and source: seed channels → stg_pos__order_lines → int_order_lines_deduped → fct_order_lines → fct_restaurant_daily.
Build in order. The seed loads, then staging builds as a view, then the intermediate model.
Test as it goes. After fct_order_lines builds, its tests run. unique(order_line_key) returns 2 rows: a re-sent batch slipped through.
Stop the damage. fct_restaurant_daily is skipped, so yesterday's correct table stays in place instead of being replaced by doubled numbers.
Fix and rerun. Fix the dedup rule, run dbt build --select fct_order_lines+ (the model and everything after it), and all tests pass.

4. Incremental models

Rebuilding years of order lines every morning wastes time and money. An incremental model only processes recent rows and merges them in.

{{ config(materialized='incremental', unique_key='order_line_key',
          incremental_strategy='merge', on_schema_change='append_new_columns') }}

select ... from {{ ref('int_order_lines_deduped') }}
{% if is_incremental() %}
where business_date >= (select {{ dbt.dateadd('day', -3, 'max(business_date)') }} from {{ this }})  -- 3-day look-back
{% endif %}

The look-back reprocesses the last few days on every run, so a late file for yesterday is picked up tomorrow. Strategies depend on the adapter (merge, delete+insert, append, microbatch). For “replace each business date I reload”, the pattern from Lesson 4.3, use delete+insert with the date as the key.

5. Snapshots: SCD2 for free

When a source only gives you the current state (a menu table that is simply overwritten), a snapshot keeps history for you:

# snapshots/menu_snapshot.yml   (newer dbt versions; older ones use a .sql snapshot block)
snapshots:
  - name: menu_snapshot
    relation: source('ref', 'menu_current')
    config: { unique_key: menu_item_id, strategy: check, check_cols: [price, category] }

Each dbt snapshot compares the current table with the last snapshot and closes and opens versions in dbt_valid_from and dbt_valid_to: the SCD2 of Lesson 5.2, maintained by dbt. Run it on a schedule; changes between two runs are invisible to it.

6. Adapters

Target Adapter Notes
DuckDB dbt-duckdb Fastest for local work; can read Parquet files directly as sources
Postgres dbt-postgres Maintained by dbt Labs
Azure SQL or SQL Server dbt-sqlserver (community) Check which incremental strategies it supports
Microsoft Fabric warehouse dbt-fabric For Fabric shops

Macros like {{ dbt.dateadd(...) }} and the dbt_utils package hide dialect differences, so one project can target several warehouses. Check each adapter’s docs for what it supports today.

Maths and principle

Principle: a test is a statement that a set is empty: the set of rows that break the rule.

Equation: unique(c) holds when no two rows share a value of c; relationships(c → D.k) holds when the fact’s keys minus the dimension’s keys is empty:

πc(F)∖πk(D)=⌀

Here πc(F) means “the set of values of column c in table F”.

Analogy: proving “no counterexample exists” by searching for one and finding nothing.

Where it breaks: a test only checks the data you have today. It can pass for 60 days and fail on day 61, which is why tests run on every build.

Try it: write “every order line’s revenue is at least 0” as a query that returns the violations. (select * from {{ ref('fct_order_lines') }} where line_revenue < 0; the test passes when it returns nothing.)

Model
One SQL SELECT in one file; dbt turns it into a view or a table.
ref / source
Pointers to other models or raw tables; they build the dependency graph.
Materialisation
How a model is stored: view, table, incremental or ephemeral (inlined into the next model).
Data test
A query that returns rule-breaking rows; zero rows means pass.
Snapshot
dbt's way of keeping SCD2 history for a source that overwrites itself.
Physical meaning
A re-sent till batch at 05:40 would have doubled one restaurant's sales on the 08:00 dashboard. Instead the unique test failed, dbt skipped the daily table, and managers saw yesterday's correct numbers with a "late" banner while you fixed the rule.

“dbt moves data.” It does not extract or load anything. dbt sends SQL to a warehouse that already holds the data and the warehouse does the work. Getting data in is the job of your Module 1 to 4 pipeline; running dbt on a schedule is the orchestrator’s job (next lesson).

7. Try it

  1. uv add dbt-core dbt-duckdb, then dbt init restaurant_dbt inside your restaurant-dbt repo.
  2. Declare your Module 2 staging Parquet files (or the DuckDB warehouse) as sources.
  3. Build stg_pos__order_lines, int_order_lines_deduped, fct_order_lines (incremental), fct_orders, fct_restaurant_daily, dim_restaurant, dim_menu_item (from a snapshot), dim_date (a model, or dbt_utils.date_spine) and dim_channel (a seed).
  4. Tests: unique and not-null primary keys, relationships, accepted values, and a one-off test in tests/ that returns the dates where revenue in staging differs from revenue in the fact.
  5. dbt docs generate then dbt docs serve: take a screenshot of the lineage graph.
  6. Add a Postgres target, then Azure SQL. Fix any dialect errors with macros, not copies of the model.

What to check: dbt build is green on DuckDB; injecting a duplicate row makes the unique test fail and skips the models downstream; the same project builds on a second target. Commit as feat(dbt): staging, intermediate, marts, snapshots, tests, docs.

New words: model, ref, source, materialisation, incremental, look-back, snapshot, adapter, seed, macro.

  • dbt is SQL files plus a graph: ref and source tell it the build order, and dbt build runs models and tests in that order.
  • Layers: staging (one per source, no business logic), intermediate (joins and rules), marts (facts and dimensions with descriptions and tests).
  • A test returns the rows that break a rule; zero rows means pass, and a failure skips everything downstream.
  • Incremental models with a look-back save work and still catch late files; snapshots keep SCD2 history for overwritten sources.

Check yourself

1What does {{ ref('dim_menu_item') }} do in a dbt model?
2A dbt data test passes when its query returns...
3Which incremental strategy matches "replace each business date that is reloaded"?

Sources

Your ticks stay in this browser. Save them as a file on the course home.

Updated 2026-11-22. All company data in the examples is synthetic. Tool versions and prices change: check the official docs before you rely on them.