Chapter 20 — Key Takeaways (Incremental Processing and SCD)

The page to keep open when you are about to add materialized='incremental' to something.

The one discipline

Know exactly what your model will do when it runs twice.

A table materialization is stateless and therefore trivially correct. An incremental model reads its own previous output to decide what work to do, so every retry, late row, and clock skew is now a question about correctness rather than about scheduling.

Decide whether, before deciding how

📐 Same technique, opposite answers, one project. Go incremental above roughly $5,000/year, or when the full refresh does not fit the window. Below that, a table is cheaper in total once you price the engineering: a watermark, a lookback, a backfill procedure, and a class of bug with no symptom.

A third reason, unrelated to cost: the source no longer has the history. That model is incremental by necessity — and the bar for its assertions is higher, because every bug in it is permanent.

Recheck annually. The threshold moves and nobody goes back to look.

The three questions

1. What is new? — the watermark predicate
2. How does new combine with old? — the strategy
3. What happens if it runs twice? — ← the one whose wrong answer is silent

Write all three in a comment. The next engineer cannot tell a chosen number from a defaulted one.

Idempotency: four strategies

The question that picks one: what is the smallest unit I can rewrite completely? Partition → overwrite. Row → merge. Whole table → swap. Nothing → you have an append-only log, and it needs Strategy 3 whether you planned it or not.

Swap's real value is the line in the middle: assert against the new table before it becomes visible, so a failed assertion leaves the old data live. Chapter 19's "stale beats wrong," implemented.

🔁 The test is one line, and almost nobody runs it:

dbt run --select m && dbt run --select m   # then EXCEPT, both directions

Backfills

--full-refresh drops the table. It does not exist while it rebuilds; a mid-way failure leaves you with neither copy; and a source with short retention rebuilds less than it replaced, silently.

Build aside → assert (including a row count within tolerance of the live table) → swap → keep the old copy a day. Bounded, chunked backfills beat full ones.

⚠️ A backfill still running at 03:00 competes with the nightly job. Take a lock; do not rely on remembering to pause the schedule.

Strategies by engine

append is not idempotent and is what you get by specifying nothing. insert_overwrite replaces WHOLE partitions — nothing checks that your WHERE matches your partition_by. 🧭 on_schema_change defaults to ignore: a new source column is silently dropped. microbatch (dbt 1.9) generates the window filter rather than making you write it, which removes the most common incremental bug.

Late arrivals

A watermark of > MAX(updated_at) assumes commit order matches timestamp order. It does not. A transaction that began before your read and commits after it carries a timestamp below the watermark, and is lost — not delayed.

⚠️ A lookback without a merge is a nightly duplication engine. They are one decision.

Size it above the observed maximum, not a percentile — the rows you lose are in the tail by definition. Kestrel measured p99 = 41 min, max = 2d 4h, and set 3 days.

📐 Do not make it enormous. The lookback is what the merge scans, and a large window silently absorbs an upstream whose lag is degrading. Alert when a recovered row's lag exceeds half the window — that turns the window into a sensor.

SCD, honestly

You will build Type 1 and Type 2, and the choice is per COLUMN. email → Type 1. region → Type 2. Both in one Type 2 dimension means every email correction creates a version.

Type 0 for attributes-at-creation (signup_country, score at underwriting) — one genuinely good use. Types 4 and 6 usually mean the dimension is doing too much; check whether those fast-moving attributes belong in the fact.

SCD2 mechanics

The surrogate key identifies a VERSION, not an entity. That is what keeps an Ohio order attached to Ohio forever.

-- what is true NOW          AND d.is_current
-- what was true THEN        AND f.ordered_at >= d.valid_from
--                           AND f.ordered_at <  d.valid_to

Three invariants, none checked by default:

1. No overlaps → a violation fans out the point-in-time join and multiplies your revenue
2. No gaps → facts in the gap land on the unknown member
3. Exactly one current row per natural key

⚠️ Pick the valid_to sentinel; never NULL. x < NULL is not TRUE, so a NULL sentinel silently drops every fact belonging to a current version — usually most of your data — and returns a smaller result rather than an error. A sentinel fails visibly.

check_cols

⚠️ check_cols: all is the worst default in dbt. Any column change makes a version, and source tables carry heartbeats: last_login_at, session_count, last_seen_ip.

Case Study 1: dim_customer went 1,904,221 → 11,355,581 rows in five months. 9,394,233 transitions; 0.65% involved any analysed attribute. Every row was true. The dimension could no longer answer its own question.

Enumerate them. The list is the explicit statement of what your organization considers a change worth remembering.

Snapshots are append-only and cannot be rebuilt. Test the column list on a copy.

Late-arriving dimensions

Unknown member (default, plus the volume metric) · inferred member (right for genuinely late data) · rebind.

🔁 Resuming the dimension load does not repair the facts. The watermark has passed them, nothing about them changed, and fixing the dimension changes the dimension. Run the rebind nightly, so a class of permanent damage becomes self-healing and nobody has to remember.

Testing and tuning

1. Run-twice equivalence — a CI job, not a data test
2. Reconciliation against a full rebuild, weekly, into a scratch schema — an incremental model drifts from its full-refresh equivalent and the drift is invisible by construction
3. A volume floor per partition
4. The three SCD invariants

💸 incremental_predicates is the setting most projects have never set. Without it a MERGE scans the entire target: Kestrel's was 4 min and $0.53/night → 11 s and $0.02. An incremental model's cost is dominated by the target scan, not the source read — the opposite of most people's intuition, and invisible unless you read the profile.

The two case studies, compressed

A dimension grew 5.96× in five months on check_cols: all, and surfaced as a 3.4% revenue overstatement only when version density made a date-vs-timestamp precision mismatch fan out. A precision mismatch is harmless at low density and catastrophic at high, and the transition is invisible. Data whose meaning changed silently in a date range is a trap with a timestamp on it.

A watermark with no lookback lost 0.037% of orders for eight months — $44,815.53, comfortably under a 0.5% reconciliation tolerance — then lost 2.1% in one night, because the loss rate scales with write concurrency. A tolerance is a budget for permanent error: state it in currency. 0.5% of $182.0M is $910,000 a year nobody will look at.