Incremental Models and Backfills

The Mental Model

Incremental models process only new or changed data to reduce cost. The hard part is correctness when data arrives late or historical logic changes.

Instead of rewriting an entire notebook every day, you add only today's pages. But if yesterday's page was corrected, you need a way to update it.

Dataset reference: ecommerce tables and grain assumptions.

Catch a late update without duplicating an order

Order 101 has created_at = Monday and source_updated_at = Wednesday. A Wednesday load filtered only by created_at misses the update. The processing cursor and business event timestamp serve different purposes.

Existing target: order 101, amount 100, updated Monday
Incoming batch:  order 101, amount 80, updated Wednesday
Required target: order 101, amount 80, one row only

Choose a reliable source update timestamp, an overlap window, and an adapter-supported merge or replacement strategy keyed by order_id. The unique_key setting identifies matching rows; it is not a database uniqueness test. Handle deletes explicitly and run a backfill for corrections older than the overlap window. Exact incremental syntax depends on the warehouse and adapter; see dbt incremental models.

Acceptance check: process the same batch twice, then process an older correction. Verify one final row per order and reconcile against a full rebuild on the same source snapshot.

Interactive Check

Question: An order from Monday arrives in the source on Wednesday. What can go wrong in a naive incremental model?

Practice: Spot the Incremental Bug

Read a naive incremental filter and explain why it misses late-arriving data.

Use the guided lab below to record your result, assumptions, and the check that would catch an incorrect result.