🔀 Pipelines & Orchestration · Chapter 6

dbt: models, refs, tests, incremental strategies

dbt's whole idea fits in one line: every table is a SELECT statement in a file, and ref() turns a folder of those files into a dependency graph you can build, test and re-run.

Study this lesson free in the app →

The basics

dbt (data build tool) is the standard way to do the model stage of a pipeline inside the warehouse. You write each table as a model — a .sql file containing one SELECT — and dbt creates it as a view or table, in the right order, then runs tests on the result.

The problem it solves: before dbt, warehouse transformations were hundreds of hand-ordered CREATE TABLE AS scripts, no tests, no docs, and nobody knew what broke what. dbt brings software practice to SQL: version control, dependency order, testing, environments, documentation.

The mental model: a spreadsheet where each sheet's formula references other sheets. Change one sheet and you know exactly which ones depend on it. In dbt, {{ ref('stg_rides') }} is that reference — it tells dbt "build stg_rides first" and resolves to the right schema for dev or prod.

Explained

Meghalaya Cabs has 140 dbt models between raw rides and the finance dashboard. When stg_rides changes, dbt build --select stg_rides+ rebuilds exactly the 23 models downstream and runs their 180 tests — in order, stopping anything that depends on a failure.

That sentence is why dbt won.

The recipe book with cross-references

A restaurant's recipe book says "Jadoh: use 2 cups of stock (see page 14)". The cook doesn't need to know where stock fits in the day — the cross-reference tells him it must be made first.

Each recipe ends with a taste check: "must not be oversalted". If the stock fails its check, nobody makes the jadoh with it.

And for the big daily dal, nobody cooks the full pot from scratch every hour — they add today's batch to what's already there. That's an incremental model.

Recipes = models. Cross-references = ref(). Taste checks = tests. Topping up the pot = incremental.

Models, refs and materializations

Model — models/marts/fct_rides.sql, one SELECT. dbt wraps it in the DDL.

{{ ref('stg_rides') }} — builds the DAG edge and resolves to the environment's schema (dev_raj.stg_rides in dev, analytics.stg_rides in prod). Never hard-code schema names.

{{ source('app', 'rides') }} — declares raw tables in YAML; dbt source freshness alerts when loaded_at_field is too old.

Materializations:

• view (default) — cheap to build, computed on every query
• table — rebuilt fully every run
• incremental — first run builds the table, later runs only process new rows
• ephemeral — inlined as a CTE, never created
• snapshots (separate feature) — SCD Type 2 history of a mutable source

Layers: staging (1:1 with sources, rename/cast) → intermediate → marts (facts and dims).

Tests

Generic data tests declared in YAML:

models:
  - name: fct_rides
    columns:
      - name: ride_id
        data_tests: [unique, not_null]
      - name: status
        data_tests:
          - accepted_values:
              values: ['completed', 'cancelled', 'no_show']
      - name: driver_id
        data_tests:
          - relationships: {to: ref('dim_driver'), field: driver_id}

Singular tests — a .sql file in tests/ returning rows that violate a rule (select * from {{ ref('fct_rides') }} where status = 'completed' and fare_paise <= 0). Zero rows = pass.

severity: warn for soft rules. dbt build runs models and tests in DAG order and skips everything downstream of a failed test. (dbt 1.8 renamed tests: to data_tests: and added unit tests that check logic on mocked inputs.)

Incremental strategies, precisely

• append — insert new rows only. No dedupe: re-runs and overlaps duplicate. Fine for immutable events with exact windows
• merge — MERGE on unique_key: update matches, insert the rest. Default on Snowflake, BigQuery and Databricks. Cost grows with target size unless the merge can prune (e.g. incremental_predicates to limit the target scan)
• delete+insert — delete target rows whose key is in the new batch, then insert. Useful where MERGE is slow or unavailable
• insert_overwrite — replace whole partitions touched by the batch (BigQuery, Spark/Databricks). Great for date-partitioned facts: idempotent per partition, no row-by-row matching
• microbatch (dbt 1.9+) — dbt splits the run into time batches by event_time (batch_size='day', lookback), each idempotent; backfill with --event-time-start/--event-time-end

on_schema_change: ignore (default) · append_new_columns · sync_all_columns · fail.

At scale

Slim CI. With 1,000+ models, CI runs dbt build --select state:modified+ --defer --state prod-artifacts: only changed models and their children, reading unchanged parents from prod.

Incremental merges get slow. A MERGE against a 40-crore-row target scans it to match keys. Partition/cluster the target by date and add incremental_predicates (e.g. only match target rows from the last 7 days), or use insert_overwrite / microbatch on date partitions.

Lookback is a trade-off. Longer lookback catches later corrections but costs more per run. Pick it from data — "99.9% of fare corrections happen within 72 hours" — and schedule a periodic full refresh for the rest.

Tests cost compute too. unique on a 40-crore table every hour is expensive; test incrementally (dbt where config on tests) or at lower frequency.

Gotchas

• Hard-coding analytics.stg_rides instead of ref() — no DAG edge, wrong schema in dev, broken lineage
• append strategy on data that can be re-read — duplicates on every retry or lookback
• merge without a truly unique key — duplicate keys in the batch make MERGE fail (or update nondeterministically on some warehouses); dedupe in the model
• No lookback with > max(updated_at) — late-arriving rows are skipped forever
• on_schema_change='ignore' — new source columns silently never appear
• Changing incremental logic without --full-refresh — history keeps the old logic, new rows get the new one
• Tests only on marts — a bad join upstream is found three layers late; test primary keys on every model

A model is a SELECT. ref() is the graph. Tests are queries that should return nothing. Incremental = is_incremental() filter + a strategy that's safe to re-run.

For mutable rows: merge on a unique key with a lookback. For date-partitioned facts: insert_overwrite or microbatch.

Words to know

In two minutes

• A dbt model is one SELECT in a file; dbt wraps it in DDL using a materialization: view, table, incremental or ephemeral.
• ref() builds the dependency graph and resolves environment schemas; source() declares raw tables and enables freshness checks.
• Tests are queries returning failing rows: generic (unique, not_null, accepted_values, relationships) and singular SQL tests; dbt build skips downstream of failures.
• Incremental models use is_incremental() to filter new rows; first runs and --full-refresh build everything.
• Strategies: append (no dedupe), merge on unique_key, delete+insert, insert_overwrite by partition, microbatch by event_time.
• Use a lookback window for late updates, and make the strategy idempotent so overlaps and re-runs are safe.
• At scale: slim CI with state:modified+, incremental_predicates or partition overwrite to keep merges fast.

Test yourself

There are 10 questions, 3 puzzles and 5 recall cards for this lesson in the app — the recall cards come back on later days, just before you would forget.