dbt run failed: Database Error: relation already exists
Fixes dbt runs failing because the target relation already exists: full-refresh collisions, concurrent runs, and leftover tables from failed runs. Use when dbt errors that the relation exists instead of replacing it. Not for permission errors creating relations.
TL;DR
dbt expects to own the target relation, so already-exists means something else created it: a concurrent run, a leftover from a crashed full-refresh, or a table built by hand with the same name. Find the other writer, then drop the orphaned relation or serialize the runs. Do not just add drop-if-exists into the model; fix the collision.
dbt run failed: Database Error: relation already existsUse this when
- dbt run fails with relation already exists on a model.
- Two runs overlap in time.
- A previous run crashed midway through a full-refresh.
Not for this skill when
- dbt cannot create the relation at all. That is permissions.
- The error is on a source. Sources are read, not created.
Steps
- Identify the existing relation in the warehouse: when it was created and by what. Note the other writer. Verify: the collision source is known
- Check for overlapping dbt runs: CI plus a manual run, or two schedules. Note the overlap. Verify: concurrency is confirmed or ruled out
- If the relation is an orphan from a crashed run, verify it is safe to drop, then drop it. Note the name is free again. Verify: the orphan is cleared
- Serialize the writers: one schedule, or runs scoped to non-overlapping models. Note runs no longer overlap. Verify: the collision cannot recur
- Re-run the model and confirm it builds. Note the run is green. Verify: the fix is verified
Variant phrasings
dbt relation already exists full refresh
The full-refresh phrasing.
dbt concurrent runs same model
The cause-shaped search.
Compatibility: dbt Core 1.x, all adapters. Materialization create/drop semantics are adapter-specific but the collision pattern is universal.
Why it happens
Most materializations build into a temp relation and swap, but the swap assumes dbt is the only writer. A crashed full-refresh can leave the temp or the target behind, and a second concurrent run tries to create the same name. The warehouse refuses, correctly, because two writers on one name is a data-corruption risk. The error is the warehouse protecting you; the fix is making sure only one writer exists.
Edge cases / pitfalls
- Dropping a relation that a concurrent run is writing to corrupts both runs. Coordinate before dropping.
- Some warehouses keep dropped tables in time travel. The name is free but the storage is not, until retention expires.
- Manual tables created for debugging with the same name as a model are a recurring cause. Name debug tables distinctly.
- Incremental models rarely hit this; when they do, suspect a crashed run left a temp relation behind.
Provenance
Resolved from the public thread: https://vectle.com/posts/pst_jT6co6Rg3z6C1fUIiqZhLA