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

Module M05: Data Engineering · Session 51 of 130 · Track: DE 🧪

What you'll be able to do after this session

• Explain dbt to a friend in one minute, out loud, no notes.
• Recognise it when you see it in real code / systems / papers.
• Complete the hands-on exercise at the end without looking up help.

Prerequisites

If you can't answer these in 30 seconds each, redo the linked session first:

• S047 — Spark — RDD, DataFrame, Jobs/Stages/Shuffles

Pre-read (skim before the session)

• 🎥 Intuition (5–15 min) · What, exactly, is dbt? — dbt Labs — 6-minute plain-English pitch straight from dbt Labs; you'll get the "why" before touching code.
• 🎥 Deep dive (30–60 min) · dbt Fundamentals — Full Course (dbt Labs) — official long-form walkthrough of models, sources, tests, and docs end-to-end.
• 🎥 Hands-on demo (10–20 min) · Build your first dbt project in 15 minutes — click-along starter that gets you from dbt init to a rendered lineage graph.
• 📖 Canonical article · dbt Docs — About dbt projects — the source of truth for project layout, dbt_project.yml, and materialization types.
• 📖 Book chapter / tutorial · dbt Learn — Fundamentals course — free structured tutorial; skim the "Models" and "Tests" modules.
• 📖 Engineering blog · How GitLab uses dbt — a real, public dbt handbook showing style, folder layout, and CI.

(a) Intuition · 5 min

The one-paragraph mental model. Why this concept exists, what problem it solves, and the analogy that makes it stick.

In one sentence: Version-controlled SQL that scales.

Imagine you're baking a wedding cake. You have raw ingredients (flour, eggs), then batter, then baked layers, then a frosted cake. Each stage depends on the previous one, and if you burn the layers, everything downstream is ruined. Old-school data teams did all this in one giant handwritten SQL script that nobody dared touch. dbt (data build tool) is the recipe book plus the timer plus the taste-test: you write one small SQL file per "layer" of your data, dbt figures out the order, runs them inside your warehouse (Snowflake / BigQuery / Databricks / Postgres), and then runs quick assertions ("this column is never null", "this id is unique") to make sure nothing burnt.

The key mental shift: dbt does the T in ELT. You already loaded raw data into the warehouse (that's E and L). dbt lets you transform it inside the warehouse using pure SQL, with git-style version control, code review, tests, and auto-generated documentation. No Python cluster required, no Spark cluster required — the warehouse itself is the compute.

Forever gotcha: dbt does not move data between systems. It sits on top of your warehouse and issues CREATE TABLE AS SELECT (or CREATE VIEW) statements. If your warehouse is down, dbt is a paperweight. If your warehouse is slow, dbt is slow.

(b) Visual walkthrough · 15 min

Diagrams, worked examples, step-by-step visuals.

A dbt project is a DAG of SQL SELECT statements. You never write CREATE TABLE; you write SELECT ... and dbt wraps it. The magic glue is the {{ ref('model_name') }} function — that's how one model points at another, and that's how dbt builds the dependency graph.

Worked example — building mart_revenue_by_customer:

1. Raw data already loaded by Fivetran / Airbyte into raw.orders and raw.customers.
2. models/staging/stg_orders.sql cleans column names, casts types:

   copy

   select id as order_id, customer_id, amount_cents / 100.0 as amount_usd, created_at
   from {{ source('raw', 'orders') }}

3. models/marts/mart_revenue_by_customer.sql joins and aggregates:

   copy

   select c.customer_id, c.name, sum(o.amount_usd) as lifetime_revenue
   from {{ ref('dim_customers') }} c
   left join {{ ref('fct_orders') }} o using (customer_id)
   group by 1, 2

4. You run dbt run — dbt topologically sorts, generates CREATE TABLE analytics.mart_revenue_by_customer AS SELECT ..., and executes in the correct order.
5. You run dbt test — dbt checks every declared test (unique, not_null, relationships, custom SQL).

Materializations at a glance:

The schema.yml file next to your models is where you declare tests and documentation — that's what powers the auto-generated docs site (dbt docs generate && dbt docs serve).

(c) Hands-on · 20 min

Code you type and run. Not pseudo-code — real, runnable, copy-pasteable.

Run against a free local DuckDB — no cloud warehouse needed.

# 1. Setup
python -m venv .venv && source .venv/bin/activate
pip install dbt-duckdb==1.8.0
mkdir dbt_demo && cd dbt_demo
dbt init jaffle --skip-profile-setup
 
# 2. Configure profile (~/.dbt/profiles.yml)
mkdir -p ~/.dbt
cat > ~/.dbt/profiles.yml <<'EOF'
jaffle:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: /tmp/jaffle.duckdb
      threads: 4
EOF
 
cd jaffle
# 3. Seed some raw CSV data
mkdir -p seeds
cat > seeds/raw_customers.csv <<'EOF'
id,name,email
1,Alice,alice@x.com
2,Bob,bob@x.com
3,Carol,carol@x.com
EOF
cat > seeds/raw_orders.csv <<'EOF'
order_id,customer_id,amount_cents
101,1,2500
102,1,1500
103,2,4200
EOF
 
# 4. Write a staging + mart model
cat > models/stg_customers.sql <<'EOF'
select id as customer_id, name, email from {{ ref('raw_customers') }}
EOF
cat > models/mart_revenue.sql <<'EOF'
select c.customer_id, c.name,
       coalesce(sum(o.amount_cents) / 100.0, 0) as lifetime_usd,
       count(o.order_id) as orders
from {{ ref('stg_customers') }} c
left join {{ ref('raw_orders') }} o on o.customer_id = c.customer_id
group by 1, 2
EOF
 
# 5. Declare tests
cat > models/schema.yml <<'EOF'
version: 2
models:
  - name: stg_customers
    columns:
      - name: customer_id
        tests: [unique, not_null]
  - name: mart_revenue
    columns:
      - name: customer_id
        tests: [unique, not_null]
EOF
 
# 6. Run it
dbt seed && dbt run && dbt test
dbt docs generate && dbt docs serve  # opens http://localhost:8080

What to observe:

1. dbt seed loads the CSVs as tables.
2. dbt run prints the execution order — staging first, then mart.
3. dbt test runs select count(*) from ... where customer_id is null under the hood.
4. Open target/manifest.json — that's dbt's DAG in JSON.
5. The docs site shows lineage: click mart_revenue → see its ancestors.

Try this modification: change mart_revenue from view (default) to incremental and add where created_at > (select max(created_at) from {{ this }}). Re-run twice with new rows in between and watch dbt only MERGE the new ones.

(d) Production reality · 10 min

How this shows up in real systems, gotchas, war stories, common bugs.

The single biggest production win is that dbt turns your SQL into a git repo. Pull requests, code review, CI that runs dbt build --select state:modified+ against a staging schema — the same discipline software engineers get, applied to analytics SQL. GitLab, HubSpot, JetBlue, and Wise all publish their dbt style guides publicly; go read GitLab's if you want to see what "mature" looks like.

War stories:

• The select * incident. A staging model did select * from source. Someone added a PII column to the source. Overnight, PII propagated into 40 downstream marts and hit Looker. Fix: never select * in staging; enumerate columns explicitly, or use dbt's dbt_utils.star() with exclusions.
• Incremental drift. Someone renamed a column in an incremental model. dbt doesn't ALTER — it appends. Result: NULLs in the new column for old rows, silently. Fix: use on_schema_change: sync_all_columns or do a full-refresh (dbt run --full-refresh) after schema changes.
• The 4-hour rebuild. Every model defaulted to table. Nightly job grew from 20 min to 4 hours as data grew. Fix: convert big fact tables to incremental with a unique_key and a merge predicate on updated_at.
• Test explosion. 800 tests, 30-minute test suite, everyone ignores failures. Fix: use severity: warn for advisory tests, error only for contract-breaking ones. Prune duplicates.

How top teams handle it: slim CI (test only changed models + descendants using state:modified+), separate dev/staging/prod schemas via target, use dbt Cloud or Airflow/Dagster/Prefect to schedule, and pair dbt with a data-quality tool (Elementary, Monte Carlo, or dbt-expectations) for freshness/volume alerts beyond dbt's built-in tests.

Gotcha to memorise: dbt tests run after the model builds. If bad data lands, it lands. dbt tells you it's bad — it doesn't stop it. For pre-flight checks, use dbt build (which stops descendants when a test fails) instead of dbt run && dbt test.

(e) Quiz + exercise · 10 min

1. What SQL statement does dbt issue when you run dbt run on a model with materialized='table'?
2. What does {{ ref('stg_orders') }} compile to at runtime, and why is that better than hard-coding the table name?
3. Name the four built-in generic tests dbt ships with.
4. What's the difference between dbt run && dbt test and dbt build?
5. When would you choose incremental over table, and what field on the model config makes MERGE work?

Stretch (connects to S050 — Airflow): Sketch an Airflow DAG that runs dbt build --select tag:daily every morning, then triggers a downstream dbt build --select tag:hourly every hour. How do you make sure the hourly run doesn't start while the daily is still running? (Hint: sensors, pools, or the Cosmos library.)

Explain-out-loud test

If you can't teach these 3 things to a friend without notes, redo the session:

1. What is dbt? (one sentence, no jargon)
2. When would you use it? (one concrete example)
3. When would you NOT use it? (one anti-pattern)

What comes next

• Next session (S052): Lakehouse — Delta / Iceberg / Hudi, ACID on Files
• Previous (S050): Orchestration — Airflow, DAGs, Retries, Backfills
• Hub: The 6-Month Learning Plan

Part of a 130-session evergreen learning series. Session structure: (a) intuition · (b) visual walkthrough · (c) hands-on · (d) production reality · (e) quiz + exercise. Duration: 60 minutes.