Database Error: relation 'dbt_cloud_pr_4213__dbt_tmp' already exists

Not specified by the original author.

TL;DR

A previous run left its temporary relation behind, or two runs are sharing one schema, so creating the temp table fails with already exists. Drop the stale temp relation or let a clean rerun replace it, and give concurrent PR jobs separate schemas. Temp relations are scratch space, so clearing them is safe.

Database Error: relation 'dbt_cloud_pr_4213__dbt_tmp' already exists

Use this when

• dbt run in a PR or CI schema fails saying relation dbtcloudpr4213dbttmp already exists (number varies)
• the failing object name ends with _dbttmp or contains dbt_tmp
• a prior run was cancelled or killed mid-model

Not for this skill when

• the existing relation is a real incremental table you need, do not drop it; fix the model name or schema instead
• the error is about a permanent view or table owned by another team
• two production jobs genuinely need the same table name, coordinate schemas instead of dropping

Steps

1. Confirm the object is temporary: read the full name in the error. Names containing _dbttmp are dbt staging relations for incremental and table materializations. Note its schema. Verify: the relation is confirmed as a dbt temp relation in a PR or job schema.

2. Check for a still-running job using that schema in dbt Cloud or your orchestrator. If a run is active, wait for it or cancel it deliberately; do not drop relations under a live run. Verify: no active run owns the schema, or the stale run is cancelled.

3. Drop the stale temp relation in the warehouse (drop table if exists for that exact schema and name), or rerun with a fresh PR schema if your CI generates one per run. Verify: the temp relation is gone, confirmed by an information_schema query.

4. Rerun the failed dbt command for the affected models only. Verify: the run recreates its temp relation and completes.

Variant phrasings

recurs on every PR run

Your CI reuses one PR schema across runs. Either add a cleanup step that drops _dbttmp relations at job start, or suffix the schema per run id.

happens on incremental models in prod

A killed prod run can leave the temp table. The next run normally swaps it, but a locked table blocks the swap; clear the lock, then rerun.

Compatibility: dbt-core incremental and table materializations on Snowflake, BigQuery, Postgres, and Redshift; dbt Cloud PR schemas. Temp naming uses the _dbttmp suffix.

Why it happens

dbt builds table and incremental models by writing a temp relation first, then swapping or merging it into the target. If a run dies between create and swap, or two runs share a schema, the create step collides with the leftover. The warehouse reports already exists because, from its side, the name is simply taken.

Edge cases / pitfalls

• Never script a broad drop of everything ending in tmp in a shared schema; scope drops to the exact schema and run.
• A leftover temp relation can hold the only copy of a partially merged batch; check row counts before dropping in prod.
• Locking: on some adapters a long-running query holds the temp table; killing the query, not just the job, releases it.

Provenance

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

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

Thread query: Database Error: relation 'dbtcloudpr4213dbttmp' already exists

TL;DR: A previous run left its temporary relation behind, or two runs are sharing one schema, so creating the temp table fails with already exists. Drop the stale temp relation or let a clean rerun replace it, and give concurrent PR jobs separate schemas. Temp relations are scratch space, so clearing them is safe.

Not specified by the original author.

TL;DR

A previous failed run left a half-built table sitting in your target schema, or two models are writing to the same table name. Either way the database refuses to create a relation that is already there. Find the leftover object, drop it, rerun the model, then fix the root cause so it stops coming back. Nine times out of ten a single DROP and a rerun clears it for good, and the duplicate-name check below keeps it from recurring.

Database Error in model stg_orders (models/staging/stg_orders.sql)
  relation "stg_orders" already exists

Use this when

• dbt fails on CREATE TABLE right after an earlier run died partway through
• you spot __dbt_tmp leftovers or suspect two models share a name
• the same model fails repeatedly with the identical relation error

Not for this skill when

• the error is permission denied, that is a grants problem not a leftover problem
• the relation genuinely does not exist, that is a different error family entirely
• the failure happens at compile time, nothing ever reached the database

Steps

1. Check for duplicate model names first, since it is the most deterministic cause and the easiest to rule out:

dbt ls --resource-type model --output name | sort | uniq -d

Expected output: any duplicated model name printed once. Empty output means every model name is unique and you can move on.

2. Look for alias collisions too, because two files can map to one table name even when the file names differ:

grep -rn "alias" models/ --include="*.sql" | head -30

Expected output: the alias assignments across your project. Two models with the same alias in the same schema will collide on every single run.

3. Inspect the target schema for the leftover relation from the failed run, which is the usual culprit when names are unique:

SELECT tablename FROM pg_tables
WHERE schemaname = 'analytics'
  AND tablename LIKE '%stg_orders%';

Expected output: the half-built table, sometimes sitting next to a __dbt_tmp sibling. Swap in your warehouse catalog query if you are not on Postgres.

4. Drop the orphaned relation by hand, then verify it is really gone:

DROP TABLE analytics.stg_orders;

Expected output: the relation disappears. Rerun the catalog query from step 3 to confirm before you rebuild, since a stale drop leaves you exactly where you started.

5. Rerun just the one model and confirm it builds cleanly:

dbt run --select stg_orders

Expected output: dbt recreates the table from scratch and the run goes green. A single-model run keeps the feedback loop tight while you verify the fix.

6. If the error keeps recurring on incremental models, force one clean rebuild:

dbt run --select stg_orders --full-refresh

Expected output: dbt drops and rebuilds the relation instead of trying to create over the old one. If even this fails, something else is recreating the table, check for concurrent runs.

Variant phrasings

dbt table already exists on run

Same family, different wording. A killed transaction left the table behind and the adapter tries to create over it. Steps 3 through 5 clear it every time.

dbt _dbttmp relation already exists

Temp build artifacts from an interrupted run. Any __dbt_tmp relation is safe to drop by hand, they are transient by design and dbt recreates them as needed.

already exists error on snowflake or bigquery

Warehouses phrase it differently but the mechanics match: find the leftover object, drop it, rerun the model. The duplicate-name check in step 1 applies everywhere.

Why it happens

dbt builds a model by creating a temp relation and swapping it into place inside a transaction. If the run is killed or errors mid-swap, the half-built relation stays in the schema. The next run tries to create that name again and the database refuses. Duplicate aliases cause the same error on purpose-built runs because two models genuinely target one name, so the first one always wins and the second always fails.

Edge cases

• Two people or CI jobs running against the same target schema will collide with each other. Give every developer and CI their own target schema.
• Snowflake folds unquoted names to uppercase, so stg_orders and STG_ORDERS are one table. Keep alias casing consistent across the project.
• Dropping the table does not drop views built on it. Check dependents before you drop, and think twice about cascade.
• Custom materializations sometimes skip the drop-if-exists step. Pin the materialization version or patch the macro.

Provenance

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

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

Thread query: dbt "relation already exists" error fix

TL;DR: A previous failed run left a half-built table sitting in your target schema, or two models are writing to the same table name. Either way the database refuses to create a relation that is already there. Find the leftover object, drop it, rerun the model, then fix the root cause so it stops coming back. Nine times out of ten a single DROP and a rerun clears it for good, and the duplicate-name check below keeps it from recurring.

Not specified by the original author.

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 exists

Use 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

1. Identify the existing relation in the warehouse: when it was created and by what. Note the other writer. Verify: the collision source is known
2. Check for overlapping dbt runs: CI plus a manual run, or two schedules. Note the overlap. Verify: concurrency is confirmed or ruled out
3. 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
4. Serialize the writers: one schedule, or runs scoped to non-overlapping models. Note runs no longer overlap. Verify: the collision cannot recur
5. 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

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

Thread query: dbt run failed: Database Error: relation already exists

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.