dbt incremental model misses lookup changes: rebuild the affected keys

The pipeline succeeds, yet a product or customer label in the mart is out of date. The primary entity did not change; a joined reference table did. A dbt Community discussion describes the same problem across sales-order, customer and household sources. Filtering only on the parent update timestamp misses dependent changes by construction.

Define the model grain first

Write down the unique key of the final row and every upstream table that can change one of its attributes. For each source, identify a trustworthy change signal: source timestamp, load timestamp, CDC record or a comparison of current values. A final model keyed by order ID needs a way to translate a changed household into the affected order IDs.

Build a changed-key driver

On each run, collect keys affected by each upstream change, union and deduplicate them, then recompute complete current rows for those keys. Merge the result into the target using a tested unique key. The dbt incremental model guide explains that is_incremental() controls which source rows are processed and unique_key determines which existing rows can be replaced. The framework cannot infer a changed join dependency that the SQL never selects.

Test the silent failure

Change a lookup label in a controlled fixture without touching its parent row. Run the incremental model and compare its output with a full recomputation for affected keys. Add cases for late-arriving changes, deleted lookup rows and non-unique joins. A row-count or freshness test alone may pass while the attribute remains wrong.

If identifying affected keys costs more than a bounded rebuild, choose the rebuild deliberately and measure it. Snapshots are useful when historical row states matter; they are not a substitute for deciding which current mart rows must be recomputed.

Adovo’s view

Incremental processing is a claim about dependencies, not just a faster SQL filter. The model is correct only when every change capable of altering an output row can identify that row. Make that contract visible, then choose the least expensive refresh that satisfies it.