Lesson 08 · Slide 1

Lesson 08 — dbt Intro

Models, refs, tests, materializations — the SQL-pipeline framework that ate the warehouse.

Data Engineering Module 3 SQL Foundations

Lesson 08 · Slide 2

The problem dbt solves

Hundreds of .sql files in a folder with no dependency graph — "did anyone rerun the daily aggregate after I updated the source table?"
No tests — bad data ships silently until a dashboard breaks.
No materialization strategy — the same expensive query runs 50 times a day.
No version control discipline — SQL lives in the warehouse UI, not a repo.

Lesson 08 · Slide 3

What dbt is

dbt (data build tool) is a SQL-pipeline framework. You write SQL in .sql files; dbt turns them into a DAG, runs them in dependency order against your warehouse, runs your tests, and tracks lineage.

One CLI tool. Zero new query languages. Your existing SQL knowledge, with build-system discipline layered on top.

Lesson 08 · Slide 4

What dbt isn't

Not a database — you bring your own (Snowflake, BigQuery, Redshift, DuckDB, Postgres, Databricks).
Not an orchestrator — Airflow / Dagster / Prefect still run dbt build on a schedule.
Not an extraction tool — that's L01–L03's job. dbt is the T in ELT.
Not Python — dbt is SQL + Jinja templating + YAML config.

Lesson 08 · Slide 5

Three primitives

Models

.sql files in models/. Each one is a single SELECT. dbt wraps it in CREATE TABLE / CREATE VIEW / INSERT INTO based on its materialization.

`ref()`

The magic function that turns {{ ref('stg_events') }} into the right table name AND builds the dependency graph automatically.

Tests

Schema tests (unique, not_null, accepted_values, relationships) declared in YAML, plus singular tests (any SQL that should return zero rows).

Lesson 08 · Slide 6

A first model

-- models/staging/stg_events.sql
SELECT
  type,
  actor,
  repo,
  created_at,
  date AS event_date
FROM {{ source('raw', 'events_raw') }}

One SELECT, one file, one model. source() references your external raw table; dbt knows where it lives from sources.yml. The {{ }} is Jinja — dbt renders it before sending SQL to the warehouse.

Lesson 08 · Slide 7

The `ref()` graph

-- models/marts/mart_daily_event_counts.sql
SELECT
  event_date,
  type,
  COUNT(*) AS n_events,
  COUNT(DISTINCT actor) AS n_actors
FROM {{ ref('stg_events') }}
GROUP BY event_date, type
ORDER BY event_date, type

ref('stg_events') says "depends on the model named stg_events." dbt resolves the name to the right schema/table AND records the edge in the lineage graph. No hard-coded table names anywhere.

Lesson 08 · Slide 8

`dbt build` — the one command

$ dbt build
12:34:01  Running with dbt=1.8.x
12:34:01  Found 4 models, 9 tests, 1 source
12:34:01
12:34:02  1 of 13 START sql view model main.stg_events .......... [RUN]
12:34:02  1 of 13 OK created view stg_events ..................... [OK in 0.4s]
12:34:02  2 of 13 START test not_null_stg_events_type ........... [RUN]
12:34:03  2 of 13 PASS .......................................... [PASS in 0.1s]
...
12:34:08  Done. PASS=13 WARN=0 ERROR=0 SKIP=0 TOTAL=13

dbt build is the headline command: build every model in dependency order, run every test after the model it tests, fail fast on the first error. One command, one log, one exit code.

Lesson 08 · Slide 9

Schema tests in YAML

# models/staging/schema.yml
version: 2
models:
  - name: stg_events
    columns:
      - name: type
        tests:
          - not_null
          - accepted_values:
              values: ['PushEvent', 'CreateEvent', 'IssueCommentEvent',
                       'PullRequestEvent']
              quote: true
      - name: actor
        tests:
          - not_null

Four built-in tests cover 80% of data-quality needs: unique, not_null, accepted_values, relationships. Tests live next to the model they test.

Lesson 08 · Slide 10

Materializations — view, table, incremental

Materialization	Built as	Use when
`view`	`CREATE VIEW`	Cheap to recompute, no storage cost. Default.
`table`	`CREATE TABLE`	Expensive query, queried often, storage is cheap.
`incremental`	`INSERT INTO` new rows	Huge table, full rebuild too slow, only new rows arrive.
`ephemeral`	Inlined as CTE	Reusable SQL that shouldn't be its own object.

Lesson 08 · Slide 11

`incremental` in 6 lines

{{ config(materialized='incremental', unique_key='event_date') }}

SELECT event_date, type, COUNT(*) AS n_events
FROM {{ source('raw', 'events_raw') }}
{% if is_incremental() %}
WHERE event_date > (SELECT MAX(event_date) FROM {{ this }})
{% endif %}
GROUP BY event_date, type

{{ this }} is the model's own table. On first run, is_incremental() is false and dbt builds the full table; on subsequent runs, only new rows are inserted. unique_key controls how dbt merges/upserts.

Lesson 08 · Slide 12

Lineage you get for free

$ dbt docs generate
$ dbt docs serve

dbt docs serve opens a browser with the full lineage graph, every model's SQL, every test, every column description. No extra config — dbt assembles it from your ref() calls and YAML.

The lineage graph shows: raw.events_raw → stg_events → stg_actors, and stg_events → mart_daily_event_counts → dashboards.

Lesson 08 · Slide 13

Where dbt fits in the stack

Inputs (E + L)

Raw tables loaded by L01–L06: Python ETL + Airflow + Parquet output, loaded into DuckDB / Snowflake.

Outputs

Clean, tested mart tables consumed by BI (Looker, Mode, Hex, Metabase) and downstream systems.

dbt (the T in ELT)

Reads sources, writes models, runs tests. Same dbt_project.yml runs against DuckDB locally and Snowflake in production — only profiles.yml changes.

Lesson 08 · Slide 14

Recap & what's next

dbt = models + refs + tests + materializations — one CLI command runs them all in DAG order.
Lineage is free — every ref() call becomes an edge in the graph; dbt docs serve visualises it.
Same project, two warehouses — DuckDB locally today, Snowflake in Lesson 11 with a 5-line profiles.yml change.

Coming up: Module 4 — Lesson 09, DuckDB locally as the development warehouse the rest of the course builds on.