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.

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