agent dropped unique_key from the incremental config: full table rescan

Not specified by the original author.

TL;DR

Without uniquekey, an incremental merge or delete-plus-insert has nothing to match on, so dbt either appends everything again or scans and rewrites far more than the batch. Restore uniquekey in the model config, verify the strategy uses it, and full refresh once to remove duplicates the broken runs created. Then watch the next two runs for stable counts.

agent dropped unique_key from the incremental config: full table rescan

Use this when

• an incremental model suddenly does full table scans or grows duplicates after a config edit
• the model config block no longer shows unique_key
• warehouse query history shows the merge or delete step missing its key predicate

Not for this skill when

• the model is append strategy by design, absence of unique_key is correct there
• the key column itself was dropped from the select, restore the column and the config together
• you are migrating keys, plan the migration with a full refresh and consumer notice instead of a silent restore

Steps

1. Diff the model config against version control to confirm uniquekey was removed and note the strategy and any onschema_change settings that interact with it. Verify: the removed key and its previous value are confirmed from history.

2. Restore uniquekey exactly as before (single column name or the list form for composite keys) in the model config block or YAML config. Verify: the model config again declares the original uniquekey.

3. Run dbt run --select with --full-refresh to rebuild the table cleanly, clearing duplicates accumulated while the key was missing. Verify: the rebuilt table passes a duplicate check on the key (group by key having count above one returns nothing).

4. Run two ordinary incremental cycles and compare row counts and query history: the merge should touch only batch rows, with no full scan. Verify: counts stay stable and the compiled incremental SQL contains the key predicate.

Variant phrasings

agent removed the key to fix a merge error

The original merge error was likely a key type or null problem. Fixing the key data is the real repair; removing the key trades an error for silent corruption.

key exists but strategy ignores it

Some strategies on some adapters do not use unique_key the same way. Confirm strategy support for your adapter and version before assuming the config is read.

Compatibility: dbt-core incremental models (merge and delete+insert strategies) on Snowflake, BigQuery, Postgres, and Spark adapters. unique_key accepts a column name or a list on supported versions.

Why it happens

Incremental correctness rests on matching incoming rows to existing rows. unique_key is that match definition. Remove it and the adapter has no safe match to perform, so depending on strategy it degrades to appends (duplicates) or broad rewrites (full scans). Both failure shapes come from the same missing line of config.

Edge cases / pitfalls

• While the key was missing, downstream tables may have ingested duplicates; check consumers, not just the repaired model.
• Guard the config with a model contract or a CI check on critical models so a dropped key fails review.
• Composite keys restored as a concatenated string instead of the list form change matching semantics; restore the exact original form.

Provenance

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

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

Thread query: agent dropped unique_key from the incremental config: full table rescan

TL;DR: Without uniquekey, an incremental merge or delete-plus-insert has nothing to match on, so dbt either appends everything again or scans and rewrites far more than the batch. Restore uniquekey in the model config, verify the strategy uses it, and full refresh once to remove duplicates the broken runs created. Then watch the next two runs for stable counts.

Not specified by the original author.

TL;DR

Incremental models duplicate keys when the uniquekey does not match the real grain or when late data bypasses the merge: inspect the duplicates the run created, confirm the uniquekey columns in the model configuration, and rebuild the affected window with a correct merge. A full refresh proves the logic; fixing only the symptom rows leaves the next run to duplicate again.

how to fix duplicate primary key failure error on incremental model run

Use this when

• The check or run fails with this symptom: the incremental run creates duplicate rows for the same primary key.
• You are working on an incremental dbt model producing duplicate primary keys and the failure repeats on the same data or configuration.
• You need the failing rows, rule, or stage isolated before changing thresholds or code.

Not for this skill when

• Duplicates in a full-refresh table with no incremental logic, which points at joins or sources instead.
• The error text names a different tool, stage, or root cause. Match the verbatim error first and use that skill.

Steps

1. List the duplicated keys created by the latest run and the timestamps of the duplicate rows. Verify: the duplicated keys and their run timing are known
2. Read the model's unique_key and incremental strategy in its configuration and compare them with the table's real grain. Verify: the configured key and strategy are explicit
3. Check the incremental filter for a lookback gap that lets corrected or late rows append instead of merge on some runs. Verify: the filter behaviour for late rows is understood
4. Correct the unique_key or strategy, then rebuild the affected date window with the incremental logic, not just a delete of bad rows. Verify: the rebuilt window holds one row per key
5. Run the model twice in a row and confirm the second run changes no row counts, proving idempotency. Verify: two consecutive runs produce stable counts

Variant phrasings

duplicate primary key incremental model dbt

The verbatim incremental phrasing.

dbt incremental unique_key duplicates late data

The late-data phrasing for lookback and merge gaps.

incremental model not idempotent duplicates on rerun

The idempotency phrasing for repeated runs.

Compatibility: dbt incremental models with unique_key on Snowflake, BigQuery, and Postgres

Why it happens

Incremental merges rely on the declared unique_key and the run's filter window. A key that omits a grain column, a strategy that appends under some conditions, or late-arriving corrections outside the lookback all insert second copies that only a uniqueness check will catch.

Edge cases / pitfalls

• Always pair incremental models with a unique test on the declared key. The test is the alarm for this class.
• Keep the lookback window larger than the source's real correction horizon, measured, not guessed.

Provenance

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

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

Thread query: how to fix duplicate primary key failure error on incremental model run

TL;DR: Incremental models duplicate keys when the uniquekey does not match the real grain or when late data bypasses the merge: inspect the duplicates the run created, confirm the uniquekey columns in the model configuration, and rebuild the affected window with a correct merge. A full refresh proves the logic; fixing only the symptom rows leaves the next run to duplicate again.

Not specified by the original author.

TL;DR

Incremental models duplicate keys when the uniquekey does not match the real grain or when late data bypasses the merge: inspect the duplicates the run created, confirm the uniquekey columns in the model configuration, and rebuild the affected window with a correct merge. A full refresh proves the logic; fixing only the symptom rows leaves the next run to duplicate again.

how to fix duplicate primary key failure error on incremental model run

Use this when

• The check or run fails with this symptom: the incremental run creates duplicate rows for the same primary key.
• You are working on an incremental dbt model producing duplicate primary keys and the failure repeats on the same data or configuration.
• You need the failing rows, rule, or stage isolated before changing thresholds or code.

Not for this skill when

• Duplicates in a full-refresh table with no incremental logic, which points at joins or sources instead.
• The error text names a different tool, stage, or root cause. Match the verbatim error first and use that skill.

Steps

1. List the duplicated keys created by the latest run and the timestamps of the duplicate rows. Verify: the duplicated keys and their run timing are known
2. Read the model's unique_key and incremental strategy in its configuration and compare them with the table's real grain. Verify: the configured key and strategy are explicit
3. Check the incremental filter for a lookback gap that lets corrected or late rows append instead of merge on some runs. Verify: the filter behaviour for late rows is understood
4. Correct the unique_key or strategy, then rebuild the affected date window with the incremental logic, not just a delete of bad rows. Verify: the rebuilt window holds one row per key
5. Run the model twice in a row and confirm the second run changes no row counts, proving idempotency. Verify: two consecutive runs produce stable counts

Variant phrasings

duplicate primary key incremental model dbt

The verbatim incremental phrasing.

dbt incremental unique_key duplicates late data

The late-data phrasing for lookback and merge gaps.

incremental model not idempotent duplicates on rerun

The idempotency phrasing for repeated runs.

Compatibility: dbt incremental models with unique_key on Snowflake, BigQuery, and Postgres

Why it happens

Incremental merges rely on the declared unique_key and the run's filter window. A key that omits a grain column, a strategy that appends under some conditions, or late-arriving corrections outside the lookback all insert second copies that only a uniqueness check will catch.

Edge cases / pitfalls

• Always pair incremental models with a unique test on the declared key. The test is the alarm for this class.
• Keep the lookback window larger than the source's real correction horizon, measured, not guessed.

Provenance

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

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

Thread query: how to fix duplicate primary key failure error on incremental model run

TL;DR: Incremental models duplicate keys when the uniquekey does not match the real grain or when late data bypasses the merge: inspect the duplicates the run created, confirm the uniquekey columns in the model configuration, and rebuild the affected window with a correct merge. A full refresh proves the logic; fixing only the symptom rows leaves the next run to duplicate again.