Chapter 20: Incremental Processing and Slowly Changing Dimensions

"An incremental model is a full refresh that has agreed to be wrong in exchange for being cheap. The engineering is in bounding the wrongness."

Overview

Chapter 19's materialization table has one row that needs a chapter of its own. incremental is where most of the difficulty in a transformation layer lives, and nearly all of the correctness bugs.

The reason is structural. A table materialization is stateless — it rebuilds from scratch, so its output is a pure function of its inputs and it is trivially correct. An incremental model is stateful: it reads its own previous output to decide what work to do, which means every failure, every retry, every late-arriving row, and every clock skew in the upstream system is now a question about correctness rather than a question about scheduling.

That statefulness buys a great deal. It also means the model can be wrong in ways that produce no error, no failed test, and no visible symptom for months — Case Study 2 is eight of them.

The second half of the chapter is slowly changing dimensions, which belong here because an SCD is an incremental model with a specific job: it maintains history for something whose current value is all the source will tell you. The two topics share their failure modes, their assertions, and their central discipline, which is stated once and applies to everything in the chapter:

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

20.1 Decide Whether To, Before Deciding How

Most writing on incremental processing skips this section, and it is the one that saves the most work.

Incremental processing has a real cost, paid in complexity rather than dollars: a watermark, a merge key, a lookback window, a backfill procedure, a class of bug that produces no symptom, and a model that no longer rebuilds itself if you are unsure whether it is right. You should only pay it where the savings justify it, and the savings vary by four orders of magnitude across models in the same project.

Two models from Kestrel, both plausible candidates.

silver.events — the clickstream. 14,000,000 rows a day (Chapter 1 §1.5), 341 GB of Parquet for the year (§1.6). A full rebuild on Spark, at the frozen $2.400/node-hour compute rate:

$$24\ \text{nodes} \times 2.4\ \text{h} \times \$2.400 = \$138.24\ \text{per night}$$

Incremental, processing one day:

$$24\ \text{nodes} \times 0.15\ \text{h} \times \$2.400 = \$8.64\ \text{per night}$$

$$\$129.60/\text{night} \times 365 = \mathbf{\$47{,}304\ \text{a year}},\ \text{a 16.0× reduction}$$

gold.fct_order_item — 6,483,117 rows, one year of order lines. Full rebuild on a Snowflake Medium warehouse (4 credits/hour at the frozen $2.00/credit) takes 14 minutes:

$$4 \times \tfrac{14}{60} \times \$2.00 = \$1.87\ \text{per night}$$

Incremental, 17,753 new lines, 40 seconds:

$$4 \times \tfrac{40}{3600} \times \$2.00 = \$0.09\ \text{per night}$$

$$\$1.78/\text{night} \times 365 = \mathbf{\$649\ \text{a year}}$$

📐 Design Decision — $47,304 is worth a watermark. $649 is not.

Same technique, same project, and the answer is opposite in the two cases.

silver.events should be incremental. $47,304 a year, and the full rebuild takes 2.4 hours, which does not fit inside the window before the 6am SLA (Chapter 1 §1.7) alongside everything else. Both arguments point the same way.

fct_order_item should not be, and Kestrel made it incremental anyway — which is why Chapter 19 Case Study 2's damage was permanent, and why this chapter's Case Study 2 happened at all. For $649 a year they bought a watermark, a lookback window they did not initially have, a backfill procedure, and two incidents.

The rule of thumb, and it is deliberately conservative: go incremental when the full refresh either costs more than about $5,000 a year or does not fit in the window. Below that, a table materialization is cheaper in total once you price the engineering.

Recheck it annually. The threshold moves as tables grow, and — more often — as warehouse pricing and engine performance improve. A model that needed to be incremental in 2022 may not in 2026, and nobody ever goes back to look.

There is a third case worth naming, because it is common and it is not about cost at all: a model must be incremental if the source no longer has the history. A CDC stream with a seven-day retention cannot be fully rebuilt from source at all, so the fact table is the system of record. That model is incremental by necessity, and the bar for its assertions is correspondingly higher.

20.2 The Three Questions

Every incremental model answers three questions. A model that has not answered all three explicitly has answered them by accident.

One: what is new? The predicate that selects rows to process. Usually a watermark against the model's own maximum timestamp, and Chapter 13 §13.4's four ways updated_at lies all apply here unchanged.

Two: how does new data combine with old? Append, replace by key, or replace by partition. This is the strategy, and it determines what happens to a row you have already loaded.

Three: what happens if it runs twice? The idempotency question. It is the one most often left unanswered, and it is the only one whose wrong answer is silent.

{{ config(
    materialized='incremental',
    unique_key=['order_id', 'line_number'],   -- Q2: replace by this key
    incremental_strategy='merge'
) }}

SELECT * FROM {{ ref('int_order_items_deduped') }}

{% if is_incremental() %}
  -- Q1: what is new. The 3-day lookback is Q1 and Q3 together --
  -- see §20.7 for why the lookback is useless without the merge above.
  WHERE updated_at >= (SELECT MAX(updated_at) - INTERVAL '3 days' FROM {{ this }})
{% endif %}

Nine lines, three decisions, and each is reversible only with a full refresh. Write the reasoning in a comment; you will not remember it, and the person who inherits the model will assume the numbers were chosen rather than defaulted.

20.3 Idempotency: Four Strategies, With Code

Chapter 4 §4.6 promised these. An operation is idempotent if running it twice produces the same result as running it once — which is what makes retries safe, and retries are not optional in a distributed system.

Kestrel's standing example is Chapter 1's duplicate-rows incident: a backfill written without a delete step, run nightly for 31 days, inflating revenue by 11.4% with every dashboard up and every DAG green. The append was the whole bug.

Strategy 1 — Overwrite the partition

-- Delete the target window, then insert it. The unit of work is a PARTITION.
DELETE FROM gold.fct_order_item
 WHERE order_date >= :start AND order_date < :end;

INSERT INTO gold.fct_order_item
SELECT ... FROM silver.order_items
 WHERE order_date >= :start AND order_date < :end;

Simplest to reason about, and the one to reach for first. Running it twice deletes and re-inserts the same window; the result is identical.

Requires two things. The write must align with a partition boundary — you cannot cheaply delete "the last three days" from a table partitioned by month. And the DELETE and INSERT must be atomic together, or a crash between them leaves a hole. On Delta, Iceberg, and Hudi this is a single transaction (Chapter 10 §10.4). On plain Parquet on S3 it is not, and that gap is the reason those formats exist.

Strategy 2 — Merge on a unique key

MERGE INTO gold.fct_order_item AS t
USING staged AS s
   ON t.order_id = s.order_id AND t.line_number = s.line_number
 WHEN MATCHED THEN UPDATE SET ...
 WHEN NOT MATCHED THEN INSERT ...;

The general strategy, and dbt's default for most adapters. Running it twice updates the same rows to the same values.

Requires a key that is genuinely unique in the source, which is the failure point. If staged contains two rows for the same key, most engines raise an error; some silently pick one. Deduplicate before the merge — Chapter 18 §18.7, with the tiebreaker — and assert the grain of the staged set rather than trusting it:

-- Run this against the staged set BEFORE the merge, not after.
SELECT order_id, line_number, COUNT(*)
  FROM staged GROUP BY 1,2 HAVING COUNT(*) > 1;   -- expect zero rows

Strategy 3 — Append with a deterministic key, deduplicate on read

-- Writes are append-only and therefore cheap and contention-free.
INSERT INTO bronze.order_events
SELECT md5(order_id || '|' || line_number || '|' || cdc_lsn) AS event_key, ...

-- The cost moves to read time, where a view enforces the grain.
CREATE VIEW silver.order_items AS
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id, line_number
                               ORDER BY cdc_lsn DESC) AS rn
    FROM bronze.order_events)
 WHERE rn = 1;

Right when writes are frequent and reads are not, and when you want the raw history preserved. The duplicate rows still exist; they are simply not visible through the interface. The cost is real and recurring — every read pays for the deduplication — so this is a bronze-layer pattern, not a serving-layer one.

Strategy 4 — Build aside and swap

CREATE OR REPLACE TABLE gold.fct_order_item__new AS SELECT ...;
-- ... run the assertions against __new here, BEFORE the swap ...
ALTER TABLE gold.fct_order_item        RENAME TO fct_order_item__old;
ALTER TABLE gold.fct_order_item__new   RENAME TO fct_order_item;
DROP TABLE gold.fct_order_item__old;

Full-refresh idempotency, and it is what a table materialization does for you. Its real value is the line in the middle: you can test the new table before it becomes the visible one, so a failed assertion means the old data stays live rather than the new bad data going out. That is Chapter 19's "stale beats wrong," implemented.

Two swaps is not atomic on every engine. Snowflake has ALTER TABLE ... SWAP WITH; Postgres can do both renames in one transaction; some engines leave a window where the table does not exist. Check yours, and prefer a single-statement swap where one exists.

🔁 Idempotency Check — the four, and how to choose

	Use when	Breaks when
Overwrite partition	writes align with partitions	the window is not a partition boundary; DELETE+INSERT is not atomic
Merge on key	a real unique key exists	the staged set has duplicates on that key
Append + dedup on read	writes frequent, reads rare	reads become frequent
Build aside and swap	full rebuild is affordable	the table is too large to build twice

The question that picks one, and it is not "which is best": what is the smallest unit I can rewrite completely? If it is a partition, overwrite it. If it is a row, merge. If it is the whole table, swap. If the answer is "I cannot rewrite anything completely," you do not have an incremental model — you have an append-only log, and it needs Strategy 3 whether you planned it or not.

And the test, which costs one line: run the model twice on the same input and diff the output.

```bash dbt run --select fct_order_item && dbt run --select fct_order_item

then: full-row EXCEPT in both directions. Ch. 18 CS1.

```

Almost nobody does this, and it finds the bug in Chapter 1's incident, this chapter's Case Study 2, and roughly half of the incremental models written under time pressure.

20.4 Backfills, and Why --full-refresh Is Sharper Than It Looks

Sooner or later a model is wrong and history must be rebuilt. The obvious command is the dangerous one.

dbt build --select fct_order_item --full-refresh

What that actually does is drop the table and rebuild it. Three consequences people meet the hard way:

The table does not exist while it rebuilds. For 14 minutes, every query against fct_order_item fails or returns nothing. If it runs at 05:50 you have missed the 6am SLA in the most confusing possible way.

If it fails halfway, you have neither the old table nor a new one. The old data is gone. Recovery is time travel (Chapter 10 §10.6) if you have it, and a restore from backup if you do not.

If the source no longer covers the full history, the rebuild is smaller than the table it replaced, and it will not tell you. A CDC source with seven-day retention rebuilds seven days over the top of two years.

The safer procedure, in four steps:

# 1. Build the replacement under a different name, live table untouched.
dbt run --select fct_order_item --vars '{target_suffix: "__rebuild"}'

# 2. Assert against it -- including a row count within tolerance of the
#    live table, which is what catches a truncated source.
dbt test --select fct_order_item__rebuild

# 3. Swap. One statement where the engine has one.
ALTER TABLE gold.fct_order_item SWAP WITH gold.fct_order_item__rebuild;

# 4. Keep the old table for a day. It costs storage and buys a rollback.

And a bounded backfill is usually better than a full one. If you know the affected range, rebuild only that range with Strategy 1, in chunks small enough that a failure loses one chunk:

for d in $(seq 0 30); do
  dbt run --select fct_order_item \
    --vars "{backfill_date: $(date -d "2026-04-01 + $d days" +%F)}"
done

⚠️ Failure Mode — the backfill that competes with the nightly run

Long backfills are started in the afternoon, take longer than expected, and are still running at 03:00 when the scheduled job begins.

Now two processes are writing the same table with different watermarks. With a merge strategy you get last-writer-wins on overlapping keys, which is usually survivable. With partition overwrite you can get a DELETE from one process landing between the other's DELETE and INSERT, and the outcome is a hole nobody will find for weeks.

Three defenses, in order of strength:

• A lock. An advisory lock or a scheduler-level mutex on the model. Chapter 24 §24.7's pools do this and are the right place for it.
• Pause the schedule. Explicit, obvious, and forgotten roughly one time in five.
• Run backfills in the window between the nightly job finishing and the business day, which is free and requires only that you know when that window is.

Chapter 1's incident is this failure with the lock left out and the delete step never written.

20.5 Incremental Strategies Across Engines

dbt exposes the engine's capability, so the names differ and the semantics differ more.

Two things to know before choosing.

append is the default on some adapters and it is not idempotent. A model with materialized='incremental' and no unique_key appends. Every retry duplicates. This is the single most common way the Chapter 1 incident is recreated, and it happens because the configuration that produces it is the one you get by not specifying anything.

insert_overwrite on BigQuery requires partition alignment, and silently overwrites whole partitions. If your model emits three days of a monthly-partitioned table, it will replace the entire month with three days of data. The partition_by in the config and the WHERE in the model must describe the same grain, and nothing checks that they do.

🧭 Version Note — what changed, and what old material assumes

• microbatch is new in dbt-core 1.9. Anything written before December 2024 describes backfills as one large statement.
• merge on Postgres requires PostgreSQL 15+. On 14 and earlier, dbt-postgres uses delete+insert, and the semantics differ on duplicate keys in the staged set.
• incremental_predicates (1.6+) let you add a predicate to the merge's join, which is how you stop the engine scanning the entire target table on every run. It is the single highest-value incremental setting most projects have never set — see §20.12.
• on_schema_change defaults to ignore, which means a new column in the source is silently dropped from an existing incremental model. append_new_columns is what most people expect. Chapter 17 §17.2's compatibility discussion, appearing where nobody looks for it.

20.6 Microbatch

microbatch (dbt 1.9) changes what a backfill is. Instead of one statement covering two years, dbt runs one statement per time window and tracks them independently.

{{ config(
    materialized='incremental',
    incremental_strategy='microbatch',
    event_time='ordered_at',        -- the column that defines the batch
    batch_size='day',
    lookback=3,                      -- §20.7, first-class
    begin='2025-01-01'
) }}

SELECT * FROM {{ ref('int_order_items_deduped') }}
-- No {% if is_incremental() %} filter. dbt injects the window.

Three things this buys, and the third is the one that matters:

Failure is bounded. A two-year backfill that dies on day 400 has completed 399 days. Re-running resumes rather than restarting.

Batches can run in parallel, where the engine and the data allow it.

The window filter is generated rather than written, which removes the most common source of incremental bugs: a hand-written WHERE clause that does not match the strategy's assumptions. In the insert_overwrite trap above, the mismatch between the config's grain and the model's WHERE is exactly what microbatch makes impossible.

The cost: your model must be a pure function of one time window. A model whose current-row calculation depends on rows outside its batch — a running total, a session that straddles a boundary (Chapter 18 §18.9) — needs the lookback set correctly, and a model that depends on all history cannot be a microbatch at all.

20.7 Late Arrivals and the Lookback Window

A watermark of updated_at > MAX(updated_at) assumes that once you have seen a timestamp, no earlier timestamp will ever arrive.

That assumption is false in every system that has more than one writer, for the reasons Chapter 13 §13.4 laid out: a transaction that started before your read and committed after it carries a timestamp below your watermark; replicas lag; clocks skew; and a batch loader may stamp rows with the time the batch started.

The fix is a lookback window — reprocess a period you have already processed:

{% if is_incremental() %}
  WHERE updated_at >= (SELECT MAX(updated_at) - INTERVAL '3 days' FROM {{ this }})
{% endif %}

⚠️ Failure Mode — a lookback without a merge is worse than neither

The lookback reprocesses three days of rows. What happens to the rows you already loaded?

With incremental_strategy='merge' and a correct unique_key, they are updated in place. This is what you wanted.

With append, you have just inserted three days of duplicates, every night. The lookback has converted a data-loss bug into a data-duplication bug that grows without bound, and it will look like Chapter 1's incident at three times the rate.

The lookback and the merge are one decision, not two. A code review that sees a lookback being added must check the strategy in the same breath, and an assertion is better than a review:

sql -- tests/assert_incremental_config_is_coherent.sql -- from the manifest. -- A model with a lookback and no unique_key is a duplication engine.

Sizing the window is a judgment about the upstream system, and you should write down the measurement rather than the guess. Kestrel measured the distribution of arrival lag over 90 days: p99 was 41 minutes, p99.99 was 19 hours, and the maximum was 2 days 4 hours — a batch load during a migration. They set 3 days. The maximum matters more than the percentile, because the rows you lose are precisely the ones in the tail.

The lookback costs something, and it is worth stating: three days of reprocessing on a merge strategy means the merge's USING set is three times larger every night, and — unless you set incremental_predicates — the engine may scan the whole target table to find matches. §20.12.

20.8 Slowly Changing Dimensions, Honestly

A customer moves from Ohio to Texas. The source system has one row and it now says Texas. Ohio is gone from the source and your warehouse is the only place it can survive.

Every order that customer placed last year was placed from Ohio. Whether your revenue-by-state report knows that is entirely a modelling decision, and the SCD types are the vocabulary for it.

The honest guidance, which most treatments bury:

You will build Type 1 and Type 2, and the decision between them is per column, not per dimension. A customer's email is almost always Type 1 — nobody analyses by historical email. Their region is almost always Type 2 — every geographic report depends on it. Putting both in one Type 2 dimension means every email correction creates a new version, which is Case Study 1.

Type 0 has one good use and it is worth knowing: an attribute that describes the entity at creation and must never move. original_acquisition_channel, signup_country, the credit score at underwriting. Model it as Type 0 and the question "how do customers acquired via paid search perform?" stays answerable forever.

Type 3 is a targeted tool, not a general one: exactly one previous value, for exactly one column, usually because a business process needs "the previous plan" and nothing more.

Types 4 and 6 are usually a sign that the dimension is doing too much. A "mini-dimension" (Type 4) exists because a handful of attributes change so fast they would explode a Type 2 — which is Case Study 1 described as a modelling pattern rather than a bug. Before building one, check whether those attributes belong in the fact instead.

20.9 SCD2 Mechanics, and Three Invariants

CREATE TABLE gold.dim_customer (
    customer_key   BIGINT,        -- surrogate. Unique per VERSION.
    customer_id    BIGINT,        -- natural. Same across versions.
    region         VARCHAR,
    segment        VARCHAR,
    valid_from     TIMESTAMP,
    valid_to       TIMESTAMP,     -- exclusive upper bound
    is_current     BOOLEAN
);

The surrogate key identifies a version, not a customer. This is the point most often missed, and it is what makes the fact join work: fct_order_item.customer_key points at the version current when the order was placed, so the order stays attached to Ohio forever.

Two ways to join, for two different questions:

-- "What is true NOW?"  -- current-state reporting.
JOIN dim_customer d ON d.customer_id = f.customer_id AND d.is_current

-- "What was true THEN?" -- point-in-time, and the reason SCD2 exists.
JOIN dim_customer d ON d.customer_id = f.customer_id
                   AND f.ordered_at >= d.valid_from
                   AND f.ordered_at <  d.valid_to

Three invariants, and the assertions that hold them. None of them is checked by any default test, and a dimension that violates the first will silently multiply your revenue.

One: no overlapping ranges for a natural key. Two versions valid at the same instant means a point-in-time join fans out.

-- tests/assert_dim_customer_no_overlap.sql
SELECT customer_id, valid_from
  FROM (SELECT customer_id, valid_from, valid_to,
               LEAD(valid_from) OVER (PARTITION BY customer_id
                                      ORDER BY valid_from) AS next_from
          FROM {{ ref('dim_customer') }})
 WHERE next_from IS NOT NULL AND next_from < valid_to;   -- expect zero

Two: no gaps. A moment with no valid version means orders placed then join to nothing and land on the unknown member (Chapter 19 Case Study 2).

 WHERE next_from IS NOT NULL AND next_from > valid_to;   -- expect zero

Three: exactly one current row per natural key.

SELECT customer_id FROM {{ ref('dim_customer') }}
 WHERE is_current GROUP BY 1 HAVING COUNT(*) <> 1;       -- expect zero

⚠️ Failure Mode — the valid_to sentinel

The current row's valid_to is either NULL or a far-future sentinel like 9999-12-31. Both are defensible. Mixing them is not, and the mixture is what you will inherit.

The problem with NULL: the point-in-time predicate f.ordered_at < d.valid_to is NULL for the current row, which is not TRUE, so the join silently drops every fact belonging to a current version. In a dimension where most rows are current, that is most of your data — and it produces a smaller result rather than an error.

The problem with 9999-12-31: it is a real value that participates in MAX(), breaks date-difference calculations, and looks like data to anyone reading the table.

Pick the sentinel. This book uses 9999-12-31 00:00:00 for valid_to and never NULL, because the failure mode of a sentinel is visible and the failure mode of NULL is silent. dbt 1.9's dbt_valid_to_current config makes this explicit rather than implicit, which is the real improvement.

And whichever you pick, write it in a NOT NULL constraint, so the choice is enforced rather than remembered.

20.10 dbt Snapshots

dbt implements SCD2 as snapshots, which run against a source and produce a history table.

# snapshots/_snapshots.yml  -- the 1.9 YAML form
snapshots:
  - name: scd_customers
    relation: source('kestrel_app', 'customers')
    config:
      unique_key: customer_id
      strategy: check
      # EXPLICIT. Never 'all' -- Case Study 1 is what 'all' does.
      check_cols: [region, segment, tier, is_active]
      dbt_valid_to_current: "'9999-12-31'::timestamp"

Two strategies:

timestamp — trust an updated_at column on the source. Cheap, and correct only if that column is trustworthy, which Chapter 13 §13.4 says it usually is not.

check — compare the listed columns to the stored version and write a new row if any differ. More expensive, and correct regardless of what the source's timestamps do.

⚠️ Failure Mode — check_cols: all

It is offered, it is one word, and it seems safer than enumerating columns. It is the worst default in dbt.

all means any column change creates a new version. Source tables carry columns that change constantly and mean nothing to your dimension: last_login_at, session_count, last_seen_ip, updated_at itself. Each one turns your dimension into a change-data-capture log.

Case Study 1 is this: dim_customer grew from 1,904,221 rows to 11,355,581 in five months, and the dimension stopped being able to answer the question it exists to answer.

Enumerate the columns. It takes two minutes, and the list is a genuinely useful piece of documentation — it is the explicit statement of what your organization considers a change worth remembering, which is a modelling decision that deserves to be written down rather than defaulted.

A new column in the source will then not be tracked, which is correct: tracking it should be a decision, and Chapter 17's contract is where the notice comes from.

Snapshots are append-only and dbt will not rebuild them. This is deliberate — the history is irreplaceable — and it means a mistake in a snapshot is expensive, because you cannot re-derive what the source looked like last March. Test the check_cols list on a copy first.

20.11 Late-Arriving Dimensions

A fact arrives for a dimension member that does not exist yet. Chapter 19 Case Study 2 is six days of it; the general problem is permanent.

Three responses, in increasing order of effort:

Route to the unknown member. COALESCE(customer_key, -1). Correct default, and it creates the monitoring obligation from Chapter 19 Case Study 2 — unknown-member volume is a health metric, and nothing else will report it.

Create an inferred member. Insert a placeholder dimension row with the natural key and nulls elsewhere, flagged is_inferred. The fact joins correctly and the row is filled in when the real dimension data arrives. This is the right answer for a dimension that is genuinely late rather than broken — a customer record that lands minutes after their first order.

Rebind the facts. When the dimension arrives, go back and repoint the facts that landed on the unknown member.

🔁 Idempotency Check — resuming the load does not repair the facts

This is Chapter 19 Case Study 2's five-week tail, and it deserves stating as a rule.

A fact row written with customer_key = -1 on Tuesday keeps that value forever, because:

• the incremental watermark has moved past it, so it is never reprocessed;
• nothing about the row changed, so a merge would not touch it even if it were reprocessed;
• the dimension load being fixed changes the dimension, not the fact.

The repair is a separate, deliberate statement:

sql UPDATE gold.fct_order_item f SET customer_key = d.customer_key FROM gold.dim_customer d WHERE f.customer_key = -1 AND d.customer_id = f.customer_id AND d.is_current;

Run it on a schedule, not after an incident. A nightly rebind of unknown-member rows turns a class of permanent damage into a self-healing property, costs a few seconds, and — the part that matters — means nobody has to remember.

If you use inferred members, the same logic fills them in, and the is_inferred flag makes the backlog queryable: SELECT COUNT(*) FROM dim_customer WHERE is_inferred is a number that should trend to zero and does not.

20.12 Testing and Tuning an Incremental Model

The four assertions that matter, none of which is a default dbt test:

Run-twice equivalence. §20.3's 🔁 callout. Run it twice, diff the output. This is the highest-value test in the chapter and it can be a CI job rather than a data test.

Reconciliation against a full rebuild. Weekly, against a __shadow copy built with --full-refresh into a scratch schema, comparing counts and sums by day. An incremental model drifts from its full-refresh equivalent, and the drift is invisible by construction — Chapter 18 Case Study 2's "a measurement with one implementation has no error bar," applied to a table.

Volume floor per partition, so a run that processes nothing is caught (Chapter 19 Case Study 1).

The three SCD invariants from §20.9, if it is a dimension.

And one tuning setting worth more than the rest combined:

{{ config(
    incremental_strategy='merge',
    unique_key=['order_id','line_number'],
    incremental_predicates=[
      -- Without this, the MERGE scans the WHOLE target to find matches.
      -- With it, the engine prunes to the partitions that can match.
      "DBT_INTERNAL_DEST.order_date >= dateadd(day, -3, current_date)"
    ]
) }}

💸 Cost Check — the merge that scanned two years to update one day

Kestrel's fct_order_item merge processed 17,753 new rows a night and scanned all 6,483,117 to find matches, because a MERGE without a predicate on the target must consider every target row.

On a Snowflake Medium, that was 4 minutes and $0.53 a night for work that should take seconds. Not a large number — and that is exactly why it survived for two years. It is below the threshold at which anything gets investigated, in a project with ninety models each doing something similar.

With incremental_predicates limiting the target scan to the lookback window: 11 seconds, $0.02.

$$(\$0.53 - \$0.02) \times 365 = \$186\ \text{a year, on one model}$$

The lesson is not the $186. It is that an incremental model's cost is dominated by the target scan, not the source read, which is the opposite of most people's intuition and invisible unless you look at the query profile. Chapter 33 §33.4 finds the same shape across a whole warehouse, where the total is not $186.

Backfilling an incremental model without breaking it

A backfill is the operation that turns a well-behaved incremental model into an incident, and the reason is that a backfill and a scheduled run are the same code doing different things.

The four ways a backfill goes wrong

It runs concurrently with itself. Thirty intervals scheduled at once, all merging into the same table, and their delete-insert windows interleave (Chapter 24 §24.9's max_active_runs). The result is gaps, and the gaps are in whichever intervals lost the race.

It runs concurrently with the nightly build. The backfill holds the table while the scheduled run tries to merge into it; one of them waits, and if the waiting one is the nightly build you have missed the 6am SLA to repair a month from 2024.

It uses --full-refresh on a model with a snapshot upstream. Chapter 19's 🔁: the snapshot is a stateful object and a full refresh rebuilds the dimension from current state, destroying history.

And it re-derives history with today's code. This is the subtle one. A backfill applies the current definition to old data, so a rule that changed in March produces a table where January now looks like March's rules — which may be what you want, and is a restatement rather than a repair, and nobody has said which.

The procedure that avoids all four

1.  A SEPARATE PROGRAM.  Not the scheduled DAG with different dates.
    It has its own name, its own pool, and its own log.

2.  RATE LIMITED, deliberately low.  A `backfill` pool of 4 slots out
    of 32 (Exercise 24.16) means the backfill can never starve the
    scheduled work, whatever it is asked to do.

3.  CHUNKED AND RESUMABLE.  One interval at a time, with a record of
    which intervals completed.  A backfill that must restart from the
    beginning will not be restarted.

4.  IDEMPOTENT PER CHUNK.  Delete-insert or merge on the interval, so
    re-running one chunk is safe -- because you will.

5.  INTO A SCRATCH TARGET FIRST, for anything large.
    Build, test, reconcile against the current table, THEN swap.
    Chapter 20 §20.5's four steps.

6.  AND A DECISION, RECORDED, about whether this is a repair or a
    restatement.  One sentence.  It is the only step that is not
    mechanical and the only one anybody will ask about later.

Step 6 is the one people skip and the one that generates the awkward meeting. "Why did January's revenue change?" has two acceptable answers — "it was wrong and we fixed it" and "the definition changed and we restated" — and an unrecorded backfill cannot produce either.

The guard

# backfill.py -- refuse to run from a scheduler (Exercise 13.23)
if os.environ.get("AIRFLOW_CTX_DAG_ID"):
    sys.exit("REFUSED: a backfill is a manual, reviewed operation. "
             "It ran nightly for 31 days in the 2025-03 incident because "
             "it was scheduled, and nothing stopped it. "
             "See docs/runbooks/backfill.md.")

Chapter 1's incident was a backfill that had been scheduled, and the guard is four lines. It is the highest ratio of consequence-prevented to code-written anywhere in this book.

🎓 Interview Angle — "how do you handle slowly changing dimensions?"

Nearly every data engineering loop asks this, and the expected answer is "Type 1, Type 2, Type 3," which is recall.

The strong answer starts from the question the history is for:

"It depends what the business asks. If nobody ever asks 'what was their region at the time of the order', Type 1 is right and Type 2 is a cost with no benefit. The test I use is that every Type 2 attribute needs one sentence naming the question that requires its history — and anything I can't write that sentence for is Type 1. I've seen a dimension go to nine million rows because check_cols was set to all and it picked up last_login_at; every row was a true record of a real change and the dimension was useless."

Three things that answer does. It names the decision rule rather than the taxonomy. It gives a concrete failure with a number. And it concedes that Type 2 has a cost, which most answers do not.

The follow-ups, in the order they come:

"How do you implement Type 2?" — valid_from, valid_to, is_current, a surrogate key. And the three invariants (§20.9): one current row per key, no overlaps, no gaps.

"What breaks if the invariants are violated?" — this is the question. Overlap fans out a point-in-time join and multiplies revenue; a gap silently drops facts; two current rows is the loud one and the least dangerous. A candidate who can distinguish the three symptoms has debugged one.

"How would you test it?" — the three invariants as assertions, plus a point-in-time join with a fan-out check. "We test that the surrogate key is unique" is the answer that shows the gap, because uniqueness of the key is exactly the property that is not broken when the intervals overlap.

And the one that separates senior candidates: "when would you not use a dimension at all?" When the attribute changes with every event and belongs on the fact; when the history is never queried; and when the "dimension" is really a slowly changing measure, which is a modelling error people reach Type 2 to solve.

🔐 Privacy & Governance — Type 2 history is a privacy liability with a business justification

A Type 2 dimension is a record of how a person's attributes changed over time, which is more revealing than any single version of them.

text dim_customer, one natural key, four versions 2023-04 postal_code 94110, segment 'new' 2024-01 postal_code 30303, segment 'active' <- they moved 2025-06 postal_code 30303, segment 'lapsed' 2026-02 postal_code 11201, segment 'active' <- they moved again

Four rows, and they are a residential history with dates. Nobody set out to build that; it is what SCD Type 2 is, applied to an attribute that happened to be an address.

Three consequences that follow directly from §20.9's mechanics.

Erasure must reach every version, not the current one. An erasure job filtering WHERE is_current leaves the person's 2023 address in the table. This is the single most common SCD erasure bug and it looks like a fix.

The valid_from and valid_to columns must survive erasure, because facts point at the surrogate key and a point-in-time join needs an interval. So the row stays, the interval stays, and the attributes are nulled — which is Exercise 6.17's procedure, and it is worth being explicit that deleting the row is the wrong answer.

And the check_cols decision is a privacy decision. Every attribute you make Type 2 is an attribute whose history you are now retaining. Exercise 20.24's rule — one sentence naming the business question that requires the history — is a data-minimisation test wearing a modelling costume, and applying it removes attributes for both reasons at once.

The practical addition to Exercise 20.23(b): for each Type 2 attribute, record the retention of its history. Not the table's retention — the attribute's. "We keep segment history for seven years and address history for two" is implementable with a periodic null-out and it is the kind of thing nobody thinks to ask for until an audit.

📏 Scale Note — when each of these stops working

Every technique in this chapter has a size at which it is the wrong one, and the boundaries are further out than people assume.

text technique comfortable to the wall ───────────────────────────────────────────────────────────────────────── full refresh the build window when it no longer fits (§20.1) delete + insert ~a partition when the delete's WHERE cannot prune -- then it scans the table merge on a key ~100M target rows the target-side scan, unless you set incremental_predicates insert_overwrite any size nothing; it is the cleanest, and it needs partitioned data SCD2 snapshot ~10M natural keys the snapshot query compares the WHOLE source to the WHOLE current version, every run

The snapshot row is the one that bites unexpectedly, because its cost scales with the dimension rather than with the change rate. 1.9 million customers compared in full every night is a fixed cost that does not shrink when nothing changes — and it is the reason a snapshot on a large, slow-changing dimension is often better run weekly than nightly, with the current state served from the source in between.

And the merge row is the one with a free fix that nobody applies. Without incremental_ predicates, a merge considers every row of the destination as a potential match; with them, it considers a window. Exercise 20.18 measures 412 million rows scanned against 1.2 million — a 333× difference from three lines of config.

The transferable rule: an incremental technique's cost is either proportional to the change or to the target, and you want the first. Delete-insert, insert-overwrite, and a predicated merge are proportional to the change. An unpredicated merge and a snapshot are proportional to the target, and both look incremental from the outside.

🔎 Read the Plan — the destination side of a merge is the half nobody looks at

A MERGE has two inputs and every discussion of it is about the source.

text MergeIntoCommand ├── source: 17,753 rows (the incremental window -- filtered, small) └── target: 412,006,004 rows scanned <- THIS 412,006,004 rows scanned <- and it is not in your SQL

The source side is restricted by your is_incremental() filter. Nothing restricts the target side unless you say so, so the engine considers every destination row as a potential match.

Three things to look for, in order:

Target rows scanned against target rows total. Equal means no pruning. This single comparison is the whole callout, and on a partitioned target it should be a tiny fraction.

A partition filter on the destination alias. In a Snowflake profile it appears on the target scan; in Spark it is a PartitionFilters entry on the target relation. incremental_predicates puts it there and nothing else will.

And the join strategy. A broadcast of the source into the target's scan is what you want; a sort-merge join of a 17,753-row source against 412 million rows is the engine telling you it could not prune.

The correctness trap that comes with the fix (Exercise 20.18): the predicate must be at least as wide as the source's window. A model that looks back three days with a predicate that looks back one will not match a row arriving two days late — so the merge inserts it, as a duplicate, and the uniqueness test catches it only if you have one.

Derive both from one variable. Two separate strings in two separate config keys is exactly the kind of drift Chapter 38's audit is built to find.

20.13 The Kestrel Platform

🧱 Kestrel Platform — Increment 20: incremental facts and a real dimension

text platform/transform/kestrel_dbt/ models/marts/finance/ fct_order_item.sql ← merge, 3-day lookback, incremental_predicates dim_customer.sql ← built from the snapshot below snapshots/ _snapshots.yml ← check strategy, FOUR named check_cols models/marts/finance/ _finance.yml ← the three SCD invariants as tests tests/ assert_fct_order_item_run_twice.sql assert_no_unknown_member_backlog.sql macros/ rebind_unknown_members.sql ← §20.11, run nightly as a post-hook

Six things this increment must get right:

1. silver.events is incremental; fct_order_item is too — and the ADR records that the second one is not justified by cost. §20.1. Write the honest reason: the source's CDC retention is seven days, so a full rebuild is impossible. That is a necessity argument, not an economic one, and conflating them is how the $649 model gets defended with the $47,304 model's reasoning.
2. Every incremental model states all three answers from §20.2 in a comment, including the lookback's measured basis.
3. check_cols is enumerated. Four columns. all fails review.
4. The three SCD invariants are tests, and the overlap one runs on every build.
5. rebind_unknown_members runs nightly, and assert_no_unknown_member_backlog fails above 50.
6. Run-twice equivalence is a CI job. Build, build again, EXCEPT both directions.

The exercise that matters is 20.24: set check_cols: all on a copy of the snapshot, run it for a simulated fortnight against a source with a last_login_at, and count the rows. Then explain to yourself why the rows it produced are all true and the dimension is nonetheless useless.

20.14 Summary

Decide whether before deciding how. silver.events saves $47,304 a year by being incremental; fct_order_item saves $649 and costs a watermark, a lookback, a backfill procedure, and two incidents. Go incremental above roughly $5,000 a year or when the full refresh does not fit the window — or when the source has no history and you have no choice.

Three questions: what is new, how does it combine, what happens if it runs twice. The third is the one whose wrong answer is silent.

Four idempotency strategies — overwrite the partition, merge on a key, append and deduplicate on read, build aside and swap. The question that picks one is "what is the smallest unit I can rewrite completely?" And the test is one line: run it twice, diff the output.

--full-refresh drops the table. Build aside, assert, swap, keep the old copy for a day. A bounded backfill in chunks beats a full one; a backfill competing with the nightly job needs a lock.

append is not idempotent and is what you get by not specifying a unique_key. insert_overwrite replaces whole partitions and nothing checks that your WHERE matches your partition_by.

A lookback window without a merge is a duplication engine. They are one decision. Size the window from the maximum observed lag, not the p99 — the rows you lose are in the tail.

SCD: you will build Type 1 and Type 2, and the choice is per column. Type 0 for attributes-at-creation. Types 4 and 6 usually mean the dimension is doing too much.

The surrogate key identifies a version, not an entity. Three invariants — no overlaps, no gaps, exactly one current row — none of them checked by default, and the first one multiplies your revenue.

Pick the valid_to sentinel and never NULL, because a sentinel fails visibly and NULL drops every current-version fact from a point-in-time join.

check_cols: all is the worst default in dbt. Enumerate them; the list is the explicit statement of what your organization considers a change worth remembering.

Resuming a broken dimension load does not repair the facts. Rebind on a schedule so nobody has to remember.

An incremental model's cost is dominated by the target scan. incremental_predicates is the setting most projects have never set.

Chapter 21 moves from SQL to Spark, where the same incremental logic runs against data too large for a warehouse, and where the partition alignment §20.5 mentioned in passing becomes the dominant concern.

Key terms: incremental model · watermark · lookback window · idempotency · merge · upsert · insert_overwrite · microbatch · full refresh · backfill · slowly changing dimension · Type 1 · Type 2 · valid_from · valid_to · is_current · surrogate key · snapshot · check strategy · hash diff · late-arriving dimension · inferred member · partition alignment