Diagnose incremental duplicates and missed corrections

Understand the likely causes

• The candidate filter can exclude legitimate late changes, keys can be NULL or duplicated, and the chosen strategy can append or match differently from expectations. unique_key does not enforce source uniqueness or compare revisions automatically. Schema synchronization also does not backfill historical values.

Identify which population first diverges

Compare the authoritative source, selected candidate rows and existing target at a known checkpoint. A source correction with updated_seq98 cannot pass a filter starting at target maximum100. That is a candidate-selection gap, not necessarily a failed merge. Conversely, duplicate candidate keys can create a target problem even with a correct boundary. Keep raw source identity and payload revision so the team can distinguish repeated delivery, stale state and contradictory equal revisions. No database query or incremental command is performed by this guide.

Inspect key validity without assuming the config enforced it

The diagnostic query returns duplicate non-null candidate loan identifiers in a prepared relation. A separate requiredness check is necessary for NULL keys. Compare the same properties in the existing target as well. This is read-only illustrative SQL with ref, not a command that was run. If duplication originates in a branch-history join, repair the model grain before tuning the incremental strategy. Deleting arbitrary duplicate rows can discard the authoritative revision while leaving the source defect unchanged.

select loan_id, count(*) as candidate_rows
from {{ ref('stg_loan_changes') }}
where loan_id is not null
group by loan_id
having count(*) > 1

Write the desired state rule before deciding to replay

The local model keeps L1 revision2 when stale revision1 reappears and inserts L2 revision3. It is an explicit greatest-revision rule, not native dbt output or behavior supplied by unique_key. Validate how the actual adapter combines candidates with target rows, including equal-version conflicts and repeated batches. An additive update can double a measure on replay even if its key matches. A lost commit acknowledgment also leaves the outcome unknown, so reconcile durable operation identity rather than inferring rollback from a timeout.

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

Choose a bounded repair with historical reconciliation

Use a justified change-capture, lookback or reconciliation design for late changes, and define deletion handling explicitly. Check whether a schema or transformation edit requires historical backfill; on_schema_change alone does not compute old values. A full refresh can rebuild large target state and affect downstream consumers, while snapshots have different history semantics. Have the authorized owner choose and rehearse the recovery path after preserving source and target evidence. This guide runs no merge, full refresh, backfill or deletion and makes no claim about a real adapter’s transaction behavior.

Keep it from recurring

• Publish explicit source-arrival, key, revision, deletion and schema-evolution contracts. Keep source history and an operation ledger long enough for supported recovery, and review both branches of is_incremental with edge-case fixtures.