dbt incremental model merge not deduplicating: duplicate rows keep growing

TL;DR

The merge is not matching existing rows, so every run inserts duplicates. Usually uniquekey is missing, misspelled, or its values do not match between source and target (type, case, or nulls). Restore a correct uniquekey, make its type consistent on both sides, full refresh once to clean the duplicates, then verify two consecutive runs keep counts stable.

dbt incremental model merge not deduplicating: duplicate rows keep growing

Use this when

• an incremental model with incremental_strategy merge grows duplicate rows for the same key on every run
• select on the unique_key shows the same key many times with identical or near identical data
• the model config recently changed strategy, unique_key, or the key expression

Not for this skill when

• the strategy is append or insert_overwrite by design, duplicates follow different rules there
• the source itself contains true duplicates for the key, dedupe the source query with qualify or distinct logic
• the table is a snapshot, snapshot dedup uses its own strategy and keys

Steps

1. Read the model config block: confirm incrementalstrategy and uniquekey values exactly, and confirm the model is actually materialized incremental (not falling back to table on this run). Verify: config shows merge strategy with the intended unique_key, and the run used the incremental path.

2. Compare key values between the incoming batch and the target: check types, case, whitespace, and null rates for the key column on both sides with warehouse queries. Verify: you know whether keys mismatch in type or content, or match and something else is wrong.

3. Fix the cause: correct the unique_key name, cast the key to one type in the model select, or filter null keys (merge cannot match null keys). Keep the fix in the model so every run benefits. Verify: model SQL produces clean, non-null, consistently typed keys.

4. Run dbt run --select with --full-refresh once to rebuild without duplicates, then run twice more without full refresh and compare counts and duplicate-key checks after each. Verify: counts stay stable across runs and a duplicate-key query returns zero.

Variant phrasings

duplicates only for late arriving updates

If updates arrive with an older watermark than the incremental filter allows, they reprocess or miss. Widen the lookback window in the incremental predicate deliberately.

merge works on one adapter, duplicates on another

Adapters implement merge differently and some fall back to delete plus insert keyed on unique_key. A missing key degrades silently on some adapters; verify strategy support per adapter.

Compatibility: dbt-core incremental models with merge strategy on Snowflake, BigQuery, Spark, and Postgres (1.3 and later semantics). unique_key may be a single column or a list on supported versions.

Why it happens

Merge deduplication is a match on unique_key between the incoming batch and the target. If the key is absent, misspelled, null, or represented differently on each side, the match finds nothing, so the merge degenerates into pure inserts. Row growth per run is the arithmetic signature of a match that never hits.

Edge cases / pitfalls

• Composite keys need the list form of unique_key; concatenating keys into one string invites delimiter collisions.
• A full refresh hides the bug temporarily; always verify with repeat incremental runs, not one green run.
• Backfills that overlap the regular window double count unless the key match is sound; fix the key before backfilling.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_QyOdOVkwoJI2jgoUM6EACg

Resolved from the public thread: https://vectle.com/posts/pst_QyOdOVkwoJI2jgoUM6EACg

Thread query: dbt incremental model merge not deduplicating: duplicate rows keep growing

TL;DR: The merge is not matching existing rows, so every run inserts duplicates. Usually uniquekey is missing, misspelled, or its values do not match between source and target (type, case, or nulls). Restore a correct uniquekey, make its type consistent on both sides, full refresh once to clean the duplicates, then verify two consecutive runs keep counts stable.