Database Error: relation 'dbt_cloud_pr_4213__dbt_tmp' 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.

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.