dbt / Intermediate: history and controlled workflows / Lesson 8

Incremental correctness and late changes

Design candidate selection, stable keys and replay behavior before optimizing the amount of transformed data.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Explain is_incremental and unique_key without assuming enforcement.
Account for late updates, deletes, schema changes and rebuilds.

Incremental processing is a state-transition design

A current-loan table should reflect a returned_at correction without appending a second lifecycle row. An incremental model needs a rule for selecting candidate source rows and a supported strategy for combining them with existing target data. Those are separate contracts. unique_key identifies the intended matching grain for strategies that use it; it does not automatically validate uniqueness or create an enforced primary key. Some strategies operate on partitions rather than row keys. Confirm adapter support and source revision semantics before treating a convenient config as a complete change-processing algorithm.

Both initial and incremental SQL must be valid

is_incremental becomes true only under its documented conditions, including an existing target table, incremental materialization and no overriding full-refresh behavior. The fragment uses a synthetic numeric updated_seq candidate boundary. It assumes source sequence arrival is suitable for that boundary and is not a general late-data solution. The false branch must also compile to valid SQL for initial construction. this resolves the model’s target relation, not an arbitrary Python object. No target is inspected here, and the example does not select a universal incremental_strategy for every adapter.

jinja Illustrative technical example; logical results are reasoned, not engine-tested.

{{ config(materialized='incremental', unique_key='loan_id') }}
select loan_id, returned_at, updated_seq
from {{ ref('stg_loan_changes') }}
{% if is_incremental() %}
where updated_seq >= (select coalesce(max(updated_seq), 0) from {{ this }})
{% endif %}

Illustrative candidate filter under a strong source-arrival assumption. Adapter merge/replace semantics, duplicate handling and late records need separate design.

A high-water mark can miss an old arriving correction

If the target maximum is100 and a delayed legitimate change arrives with updated_seq98, the shown filter will miss it. A bounded lookback can help only under a justified lateness bound and repeatable matching behavior; it is not a universal cure. A durable change log, source ingestion sequence or explicit reconciliation can be required. Validate candidate-key uniqueness before a merge and define how conflicting equal revisions are resolved. NULL matching keys can also create duplicates or failed matches under the target strategy. Keep rejected and stale changes visible rather than silently dropping them from accounting.

State replacement and additive replay differ

The local state model keeps revision2 of loan L1 when revision1 reappears, while inserting L2 revision3. That is an explicitly chosen revision policy, not behavior guaranteed by unique_key alone. Replaying a source amount into an additive update could double a measure even if the key matches. Deletions need a documented tombstone, soft-delete or reconciliation path; an incremental query filtering only present rows cannot infer every removed source record. The JSON model is validated locally and has no warehouse endpoint. It is not a runnable implementation of a dbt adapter strategy.

json Illustrative technical example; logical results are reasoned, not engine-tested.

{"existing":{"L1":2},"incoming":[["L1",1],["L2",3]],"chosen_rule":"greatest source revision wins","expected":{"L1":2,"L2":3}}

A local revision-selection model only. Real payload conflict, transactional and adapter behavior are outside this arithmetic check.

Schema synchronization does not backfill old meaning

on_schema_change offers supported responses to top-level schema differences, but adding a column does not automatically compute its value for historical rows. A changed transformation can leave old and new rows with different meanings until a justified backfill or rebuild. Full refresh is a potentially expensive and destructive data operation with downstream and recovery consequences. Review target permissions, source retention and accepted changes before planning it. This course performs no incremental run, merge, full refresh or schema modification. Local checks cover only candidate selection and the stated revision model, not engine or database correctness.

Common errors and fixes

unique_key is treated as automatic deduplication

Why it happens: A matching hint was confused with a validated source invariant.
How to fix it: Test keys in both candidate and target populations.
Prevent it next time: Define duplicate and NULL-key handling before incremental execution.

Late returns never update old loan rows

Why it happens: The candidate high-water mark excluded legitimate late changes.
How to fix it: Review ingestion sequence, bounded lookback and reconciliation requirements.
Prevent it next time: Test changes arriving below the current target maximum.

A new column is assumed populated across all history

Why it happens: Schema handling was confused with historical data transformation.
How to fix it: Plan and verify an explicit backfill or supported rebuild.
Prevent it next time: Include old rows in schema-evolution acceptance checks.

Check your understanding

1. Will the example select updated_seq98 after the target maximum reaches100?

Show worked solution

No. It is below the predicate boundary. If it is a legitimate late correction, the design is incomplete for that arrival pattern. Choose a justified change-capture or lookback/reconciliation protocol and verify replay safety rather than assuming the incremental flag solves lateness.

2. Does the local greatest-revision model prove dbt merge behavior?