Incremental models are only safe when change detection is explicit

An incremental model is safe only when it can construct the complete set of output keys affected by upstream change.

A watermark on the main source rarely proves that. It sees rows whose own timestamp advanced. It may miss late children, deletes, corrected dimensions, replayed events, and changes in another source that alter an existing aggregate.

The affected set defines the incremental contract. The MERGE only applies it.

Define change from the output grain

Start with one target row and ask what can make its current value different.

If the target is one order, changes may arrive through the order, its lines, payments, shipments, customer classification, exchange rate, or status history. Each dependency must provide a route back to order_id.

changed order       → order_id
changed line        → order_id
changed payment     → order_id
changed customer    → all affected order_id values
changed FX rate     → orders in affected currency and date
source deletion     → previously emitted order_id

This map is more useful than “process records updated since the last run.” It states how source events propagate into analytical state.

A model with unclear grain can’t build this map reliably. Identity comes first.

Use explicit change signals

The best source signal depends on the system:

• immutable change events or CDC positions
• reliable updated_at values
• ingestion timestamps plus source business keys
• partition manifests or extraction batches
• hashes comparing current and previous source state
• upstream affected-key tables
• bounded lookbacks derived from observed lateness

Record the last successful boundary transactionally with the output or in durable run metadata. A scheduler timestamp isn’t a safe watermark when extraction and transformation can fail independently.

Use half-open ranges and overlap where source precision or clock behavior requires it. Deduplicate by stable source identity so overlap repairs missed edges without creating duplicate facts.

Propagate changes through dependencies

A downstream aggregate can’t watch only its direct fact table if dimensions or child tables alter its output.

Build small affected-key models that union changes from every dependency and resolve them to the target grain. These sets are reviewable, reusable, and measurable. They also make cost visible: the team can compare the number of changed source records, affected target keys, and output partitions rewritten.

For high-fan-out changes, key propagation may become more expensive than rebuilding a partition or bounded slice. That’s a legitimate switch in correction unit, not a reason to hide the fan-out.

Detect deletions separately

Deleted rows often leave no current source record with a new timestamp.

Use CDC tombstones, source snapshots compared by key, extraction manifests, soft-delete fields, or a periodic anti-join against previously emitted keys. Decide whether deletion removes the target row or changes it to an inactive state.

A model that handles inserts and updates but can’t explain deletion isn’t a current-state incremental. It’s an accumulating archive with selective correction.

Treat lookbacks as measured insurance

A lookback window covers late arrival only when the lateness distribution is known and bounded enough for the business requirement.

Measure source event time against arrival or extraction time. Choose a window with an explicit tolerated miss rate, and alert on events outside it. For unbounded corrections, add a changed-key or reconciliation path rather than increasing the routine window forever.

The cost of the window should also be observable. If the model rereads thirty days to catch a handful of late records, another signal has probably earned implementation.

Keep first runs, retries, and backfills semantically identical

A full refresh and an incremental run should produce the same result at the same source state. A retry of the same boundary should be idempotent. A backfill should widen the affected set, not switch to a second definition of the model.

Persist enough run metadata to explain which source boundaries and keys were processed. If a run fails after writing output but before advancing its watermark, rerunning the range must converge safely.

Verify the contract continuously

Test known transitions: late child, corrected parent, deleted source, replayed batch, changed dimension, and boundary timestamp. Compare incremental output with a full rebuild for representative history.

In production, reconcile key counts, hashes, and aggregates over sampled or bounded periods. Measure changes that arrived outside the expected window. A successful pipeline run only proves the SQL executed. It doesn’t prove the affected set was complete.

When incrementality fits

Choose incremental materialization when a complete affected set is cheaper than rebuilding the table and the platform can validate that assumption.

Use a full or partition rebuild when change propagation is broad, source signals are unreliable, or the cost of missing corrections is higher than the saved compute. “Incremental” isn’t inherently more mature. It’s a performance optimization constrained by a correctness proof.

A unique key tells the model where to apply change. Explicit change detection tells it which keys need change. Without both, the model saves compute by preserving old answers.