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.

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.

1. 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.

1. 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.

1. 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.

1. 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.

1. 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