Rebuilding a billion-row table every run is wasteful. Incremental models process only new rows; snapshots capture how records change over time — dbt’s built-in Slowly Changing Dimension Type 2.
For large, append-heavy tables (events, logs, orders), fully rebuilding on every run wastes hours and money re-processing data that has not changed. An incremental model builds the table once, then on later runs transforms and inserts only the new rows. It is the difference between a five-minute run and a five-hour one on big data.
A 2-billion-row events table, run hourly:
table materialization -> rebuild ALL 2B rows every hour (hours, $$$)
incremental -> build once, then only process the last hour's
new rows and append them (minutes, cheap)
Use incremental when: the table is large AND mostly append-only, and you
can identify "new" rows (a timestamp or increasing id).An incremental model uses the incremental materialization and an is_incremental() guard: on a full run it processes everything, but on incremental runs the guarded WHERE clause filters to rows newer than what is already in the table. The special {{ this }} refers to the model’s existing table, letting you find the current high-water mark.
{{ config(materialized='incremental', unique_key='event_id') }}
select
event_id, user_id, event_type, occurred_at
from {{ ref('stg_events') }}
{% if is_incremental() %}
-- only on incremental runs: rows newer than what's already loaded
where occurred_at > (select max(occurred_at) from {{ this }})
{% endif %}
-- first run: builds the whole table (the {% if %} block is skipped)
-- later runs: appends only new events. unique_key lets it MERGE (upsert)
-- so late-arriving updates replace existing rows instead of duplicating.Source systems overwrite records — a customer’s status or a product’s price changes in place, losing history. A dbt snapshot captures the state on each run and records changes as versioned rows with validity dates: exactly the Slowly Changing Dimension Type 2 pattern from the Data Modeling course, without hand-writing the expire-and-insert logic.
-- snapshots/customers_snapshot.sql
{% snapshot customers_snapshot %}
{{ config(
target_schema='snapshots',
unique_key='customer_id',
strategy='check',
check_cols=['status', 'plan', 'region']
) }}
select * from {{ source('jaffle_shop', 'customers') }}
{% endsnapshot %}
-- 'dbt snapshot' each run: if status/plan/region changed for a customer,
-- dbt expires the old row (dbt_valid_to = now) and inserts a new version.
-- You get dbt_valid_from / dbt_valid_to for free = SCD Type 2 history.Incremental models trade simplicity for speed, so they need care: a good unique_key to handle updates and late-arriving data, an occasional full-refresh when logic changes, and awareness that the WHERE filter defines correctness. Snapshots must run on a schedule against the mutable source — miss a run and you miss a change forever.
Incremental gotchas:
- set unique_key so updates MERGE instead of creating duplicates
- late-arriving data: filter on a small look-back window, not just max()
- changed the model's logic? 'dbt run --full-refresh' to rebuild from scratch
- the WHERE clause IS the correctness boundary — get it right
Snapshot gotchas:
- must run regularly (via the orchestrator) — a missed run = a lost change,
because the source overwrote it. Snapshots can't recover unseen history.