dbt incremental model broken: re-running full refresh every time

Not specified by the original author.

TL;DR

Something forces a full refresh on every run: a --full-refresh flag in the job command, a missing unique_key, or the target table being dropped between runs. Check the model config, the job command, and the table's existence, then run twice and confirm the second run is incremental.

Error

dbt incremental model broken: re-running full refresh every time

Steps

1. Check the model config: it needs materialized='incremental' and, for merge-style increments, a unique_key. Expected: both are set correctly.
2. Check the job or command that runs it for a --full-refresh flag (common in dbt Cloud job settings or CI scripts). Expected: the flag is absent for normal runs.
3. Verify the target table still exists in the warehouse between runs; some pipelines drop it. Expected: the table persists across runs.
4. Inspect the is_incremental() branches in the model SQL: the incremental filter must actually restrict rows when true. Expected: the incremental branch is reachable and correct.
5. Run the model twice in a row and compare run times and the warehouse query history. Expected: the second run processes only new rows.

When to use

• An incremental model takes full-refresh time on every run.
• Run logs show "full refresh" behavior (drop and recreate) unexpectedly.

When not to use

• The model is configured as materialized='table' (full refresh is correct).
• You explicitly want full refreshes (then the behavior is intended).

Tool compatibility

• dbt Core 1.0 and later, all adapters. Incremental strategies (merge, delete+insert, append) vary by adapter.

Variant phrasings

is_incremental() never true

The usual symptom: check unique_key, the existing table, and the --full-refresh flag.

Incremental model slow as a full table build

Same problem; the incremental path is not being taken.

Why it happens

dbt chooses full refresh when told to (--full-refresh), when it cannot merge (no unique_key on adapters that need one), or when the target relation is missing. Any of these silently turns every run into a full rebuild.

Edge cases

• on_schema_change settings can trigger full refreshes when columns change; that is by design.
• dbt Cloud "run on schedule" jobs sometimes inherit a full-refresh checkbox someone ticked months ago.
• A failing incremental branch that falls back to full SQL still looks like a full refresh in timing; read the compiled SQL.

Provenance

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

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

Thread query: dbt incremental model broken: re-running full refresh every time

TL;DR: Something forces a full refresh on every run: a --full-refresh flag in the job command, a missing unique_key, or the target table being dropped between runs. Check the model config, the job command, and the table's existence, then run twice and confirm the second run is incremental.

Not specified by the original author.

TL;DR

A full refresh rebuilds the table from the model SQL with no existing rows to merge into, so a unique key collision during full refresh means the model output itself has duplicate values of the unique_key. The source gained duplicates, a join fanned out, or the key was never unique at the grain you assumed. Profile the model output grouped by the key, fix the grain or the join, and only then rerun the full refresh.

dbt incremental model full refresh failed: unique key collision

Use this when

• dbt build with the full refresh flag fails on a duplicate or unique constraint for an incremental model.
• A normal incremental run appeared fine but the rebuild exposes collisions.
• A recent join or union was added to the model.

Not for this skill when

• The collision happens only on incremental merges against existing rows. That points at merge logic or late arriving updates.
• The unique key is fine and the error is a warehouse primary key on a different table. Read which relation the error names.

Steps

1. Run the compiled model SQL with a grouping check on the unique_key and list keys with more than one row. Note example duplicate keys. Verify: duplication in the model output is proven with examples
2. Trace one duplicate key back through the model: check each join for a one to many leg that fans rows out, and each union for overlapping sources. Note the step where the row count doubles. Verify: the fanout or overlap is located
3. Decide the true grain: either the key needs more columns to be unique, or the model must aggregate or dedupe to one row per key with an explicit rule such as latest updated timestamp wins. Note the rule in the model. Verify: the grain decision is explicit, not accidental
4. Apply the fix, add a dbt unique test on the key plus a not_null test, and run the model on a dev target. Note the tests pass on the rebuilt table. Verify: uniqueness is enforced by a test, not by hope
5. Run the full refresh in the target environment during a window where downstream models can rebuild after it, then run the downstream tests. Note the full build is green. Verify: the refreshed table and its consumers agree

Variant phrasings

dbt full refresh duplicate key incremental model

The short form of the same failure.

dbt unique_key not unique in model output

The grain problem phrasing.

Compatibility: dbt Core and dbt Cloud incremental models on warehouses that enforce uniqueness on merge or on table creation. Exact error text varies by adapter; the duplicate in the output is the common cause.

Why it happens

Incremental merges can hide a grain problem for months: when duplicate keys arrive in one batch, many adapters take one row or fail intermittently, and when they match existing rows the merge overwrites instead of erroring. A full refresh removes the hiding place. With no target table to merge into, duplicates in the result have nowhere to go, and the collision becomes deterministic. That is why a model with no recent code change can suddenly fail a refresh the day the source starts sending a second row per key.

Edge cases / pitfalls

• Do not solve this by dropping the unique test or picking an arbitrary row with a limit. The business meaning of the duplicate matters.
• A dedupe rule based on load time makes reruns non deterministic. Prefer a source timestamp or version column.
• Full refresh on a large model is expensive and downstream snapshots may capture the intermediate state. Schedule it and pause consumers if needed.
• If the source key duplicates are themselves a bug upstream, file it there too. The model dedupe is a guardrail, not absolution.

Provenance

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

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

Thread query: dbt incremental model full refresh failed: unique key collision

TL;DR: A full refresh rebuilds the table from the model SQL with no existing rows to merge into, so a unique key collision during full refresh means the model output itself has duplicate values of the unique_key. The source gained duplicates, a join fanned out, or the key was never unique at the grain you assumed. Profile the model output grouped by the key, fix the grain or the join, and only then rerun the full refresh.

Not specified by the original author.

TL;DR

A prod full refresh drops and rebuilds the incremental table, which is recoverable for pure models but destroys accumulated state the model cannot recompute (late corrections merged over time, first-seen timestamps). Assess what the rebuild lost by comparing against backups or downstream aggregates, restore from warehouse time travel or backup where possible, and then remove the agent ability to pass --full-refresh to prod at all.

agent ran --full-refresh on prod: wiped the incremental history

Use this when

• an agent command included --full-refresh (or full_refresh config) and ran against the prod target
• the prod incremental table row history changed unexpectedly, or its built timestamp reset
• downstream metrics shifted for historical periods after the run

Not for this skill when

• the full refresh ran in dev, no incident; still add the guardrail while it is cheap
• the model is a plain table materialization rebuilt every run anyway, full refresh is a no-op concept there
• a planned migration full refresh changed history deliberately, document it rather than treating it as an incident

Steps

1. Scope the damage: identify the refreshed models from the run logs, then compare current prod tables with the warehouse time travel snapshot or backup from just before the run (row counts, key ranges, historical aggregates). Verify: lost or altered history is quantified per model.

2. Restore where possible: use time travel or backup clones to recover the pre-refresh table, then replay incremental runs from the refresh point forward if source data allows. Validate restored aggregates against downstream reports. Verify: prod tables match pre-incident values or an accepted, documented delta.

3. Remove the capability: strip --full-refresh from agent command templates for prod targets, and enforce with warehouse or job config (separate prod credentials the agent does not hold). Verify: the agent cannot execute a prod full refresh through its normal paths.

4. Add a detective control: alert on prod runs whose run_results show full refresh semantics or on sudden historical aggregate changes after automated runs. Verify: a future occurrence pages a human within one run cycle.

Variant phrasings

snapshots affected too

If the build included snapshots, history tables may have closed records incorrectly. Snapshot recovery is harder; restore snapshot tables from backup rather than rerunning snapshot logic.

agent used config instead of the flag

fullrefresh can be set in model config. Grep configs for fullrefresh true as part of the sweep, not just command history.

Compatibility: dbt-core --full-refresh behavior for incremental models on all adapters; warehouse recovery via Snowflake Time Travel or BigQuery time travel and table snapshots where enabled.

Why it happens

Incremental tables are state, not just code output: months of merges encode information (deduplication decisions, late data handling) that a rebuild from current sources may not reproduce, especially if sources themselves were restated or purged. Agents reach for full refresh because it fixes many local errors, which makes it the highest blast radius flag an agent can pass in prod.

Edge cases / pitfalls

• Time travel windows are finite; assess damage the same day, before the recovery window closes.
• A rebuild that reproduces identical aggregates can still differ in physical columns like first loaded timestamps; check what consumers actually read.
• Guardrails belong in credentials and job definitions, not in agent instructions alone.

Provenance

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

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

Thread query: agent ran --full-refresh on prod: wiped the incremental history

TL;DR: A prod full refresh drops and rebuilds the incremental table, which is recoverable for pure models but destroys accumulated state the model cannot recompute (late corrections merged over time, first-seen timestamps). Assess what the rebuild lost by comparing against backups or downstream aggregates, restore from warehouse time travel or backup where possible, and then remove the agent ability to pass --full-refresh to prod at all.