Day 32. dbt, SQL models with software discipline

⏱ 4h · learn 70m · lab 140m · ship 15m · review 15m🏷 restaurantsBefore: Day 31

By the end of today you can

Structure a dbt project into sources, staging, intermediate and marts
Use ref, source, tests, docs and materialisations
Build incremental models and snapshots (SCD2)
Run the same project on DuckDB, Postgres and Azure SQL through adapters

1. Why this matters

dbt turned SQL transformations into software: version control, dependencies, tests, documentation and environments. "dbt" appears in a large share of analytics-engineering and BI job ads, and it's free and open source (dbt Core). It works with Azure SQL via adapters, so it fits the core path.

2. Project shape

restaurant_dbt/
├── dbt_project.yml
├── profiles.yml            # connection targets (keep secrets in env vars)
├── models/
│   ├── staging/            # 1:1 with sources: rename, cast, light cleaning  → views
│   │   ├── _sources.yml
│   │   └── stg_pos__order_lines.sql
│   ├── intermediate/       # joins and business rules                       → ephemeral/views
│   └── marts/              # facts and dimensions for BI                    → tables / incremental
│       ├── fct_order_lines.sql
│       ├── dim_menu_item.sql
│       └── _marts.yml      # docs + tests
├── snapshots/              # SCD2 from sources that only give current state
├── seeds/                  # small CSVs: holidays, channel mapping
└── tests/                  # singular SQL tests

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, dialect-safe
  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 POS 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'] }

{{ ref('dim_menu_item') }} builds the dependency graph; dbt runs models in order. dbt build = run + test + snapshot + seed, in DAG order, stopping downstream of failures.

4. Incremental models

{{ 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 %}

Strategies vary by adapter (merge, delete+insert, append, microbatch). For partition-style replace use delete+insert with the date as key, the Day 24 Pattern 2.

5. Snapshots (SCD2 for free)

# snapshots/menu_snapshot.yml  (newer dbt versions; older 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, item_name] }

Each dbt snapshot compares the current table with the last snapshot and closes/opens versions with dbt_valid_from / dbt_valid_to.

6. Adapters

Target	Adapter	Notes
DuckDB	`dbt-duckdb`	Fastest local dev; can read Parquet/S3 directly as sources
Postgres	`dbt-postgres`	Official
Azure SQL / SQL Server	`dbt-sqlserver` (community)	Supports Entra auth; check incremental strategies supported
Fabric Warehouse	`dbt-fabric` (Microsoft)	For Fabric

Macros ({{ dbt.dateadd(...) }}, dbt_utils) hide dialect differences so one project can target several warehouses.

Maths and principle

Principle: tests are assertions on sets; a dbt test passes when its query returns zero rows.

Equation: unique(c) ⇔ \(|\{r : \exists r' \neq r, c(r)=c(r')\}| = 0\). relationships(c → D.k) ⇔ \(\pi_c(F) \setminus \pi_k(D) = \emptyset\) (no orphans). Write any rule as "the set of violations is empty".

Analogy: proof by showing no counterexample exists in the data. Where it breaks: tests only check the data you have. A test that passes on 60 days can fail on day 61; that's why they run every build.

Try it: express "every order line's revenue ≥ 0" as a violation query. (select * from fct_order_lines where line_revenue < 0: test passes if empty.)

The 5-year lens

Conventions are what make dbt projects maintainable by teams: stg_<source>__<entity>, int_<verb>, fct_/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. Add dbt-project-evaluator or sqlfluff in CI to enforce them. In interviews, describing your conventions is more convincing than listing features.

Lab

uv add dbt-core dbt-duckdb (then later dbt-postgres, dbt-sqlserver). dbt init restaurant_dbt.
Sources: point at your staging Parquet (dbt-duckdb external_location or read_parquet) or the DuckDB warehouse.
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 (from a model or dbt_utils.date_spine), dim_channel (seed).
Tests: PKs unique/not null, relationships, accepted values, a singular test for "revenue in = revenue out" between staging and fact.
dbt docs generate && dbt docs serve: screenshot the lineage graph.
Switch target to Postgres, then Azure SQL. Fix any dialect issues with macros.

Ship

feat(dbt): staging, intermediate, marts, snapshots, tests, docs on three targets.