snowflake table swap failed during dbt run

Not specified by the original author.

TL;DR

dbt builds the new table under a temp name and swaps it into place, so a swap failure means the build succeeded but the rename was refused: usually missing permissions, a lock on the target table, or a leftover temp table from a crashed run. Check the exact swap error, clear the blocker, and re-run. The data is built; only the rename failed.

snowflake table swap failed during dbt run

Use this when

• A dbt model on Snowflake fails with a swap error.
• The temp table exists but the target was not replaced.
• It follows a crashed or killed dbt run.

Not for this skill when

• The model build itself failed. Fix the build first.
• You swap tables manually outside dbt. Check your own swap script.

Steps

1. Read the dbt error for the swap statement and Snowflake's refusal reason. Note the exact error. Verify: the refusal is named
2. Check permissions: the dbt role needs ownership or the right grants on both tables. Note the grants. Verify: permissions are verified
3. Look for locks on the target table from concurrent queries or another dbt run. Note the locker. Verify: locks are found or ruled out
4. Drop leftover temp tables from crashed runs that collide with the new temp name. Note the namespace is clean. Verify: orphans are cleared
5. Re-run the model and confirm the swap completes. Note the target table is fresh. Verify: the swap succeeds

Variant phrasings

dbt snowflake swap table permission

The grants-shaped search.

snowflake alter table swap failed

The SQL-shaped phrasing.

Compatibility: dbt Core with the Snowflake adapter. Swap is used by table and incremental materializations.

Why it happens

The swap pattern exists so readers never see a half-built table: build privately, then atomically rename. The rename is a metadata operation that needs ownership-level rights and an unlocked target, which makes it the step where permission and concurrency problems surface even though the heavy work already succeeded. A crashed run leaves the temp table behind, and the next run's temp name can collide with it, failing the swap before it starts.

Edge cases / pitfalls

• Swapping does not move grants automatically in all cases. Verify grants on the new table after the swap.
• Concurrent BI queries hold locks that block the swap. Schedule swaps outside peak query windows or use short lock timeouts.
• Cloned or shared tables have ownership subtleties. Confirm the dbt role owns what it swaps.
• A swap that half-completes (rare) can leave the temp and target swapped unexpectedly. Verify table contents, not just names.

Provenance

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

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

Thread query: snowflake table swap failed during dbt run

TL;DR: dbt builds the new table under a temp name and swaps it into place, so a swap failure means the build succeeded but the rename was refused: usually missing permissions, a lock on the target table, or a leftover temp table from a crashed run. Check the exact swap error, clear the blocker, and re-run. The data is built; only the rename failed.

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

The run succeeded because the SQL ran, but its predicates or joins filtered out every row: common culprits are an incremental filter against the wrong max timestamp, a var or target-dependent filter, or an inner join to an empty upstream. Compile the model, run its SQL manually in Snowflake with filters relaxed step by step, and fix the predicate that eats all rows.

dbt run succeeds but the snowflake table is empty

Use this when

• dbt run reports success but select count from the Snowflake table returns 0
• the source tables visibly contain matching data
• the problem started after adding an incremental filter, a join, or an environment-specific var

Not for this skill when

• the table is a view on an empty upstream, fix the upstream data load
• zero rows is correct for the period (new table, no data yet), confirm with the data owner before changing SQL
• the run actually targeted a different schema and you are querying the wrong table copy

Steps

1. Confirm identity first: check the run target schema and database in the logs, and count rows in the exact relation dbt built, plus counts of each direct upstream. Verify: you know the built relation is empty while upstreams are not (or you found you were querying the wrong copy).

2. Open target/compiled SQL for the model and run it in Snowflake as a select, without the incremental wrapper. If it returns rows, the incremental logic is at fault; if not, bisect the where clauses and joins by removing one at a time. Verify: you have isolated the clause or join that drops all rows.

3. Fix the culprit: for incremental models, correct the watermark comparison (for example comparing a timestamp column to max of the same column with timezone or truncation mismatch), for var filters set the var for this target, for joins consider left join if the side table is optional. Verify: the compiled select returns the expected row count in Snowflake.

4. Rerun dbt run --select for the model (with --full-refresh if incremental state is poisoned) and recount the table. Verify: the table holds the expected rows and a second incremental run adds only new rows.

Variant phrasings

empty only in prod

Target-dependent code (target.name checks, prod-only vars, prod schemas with no data yet) explains most environment splits. Diff compiled SQL between dev and prod targets.

empty after first incremental run

A watermark initialized from an empty target can lock the model at zero. Full refresh once with corrected logic, then resume incremental runs.

Compatibility: dbt-core models on Snowflake; compiled SQL under target/compiled. The same bisection method works on other adapters, but timezone and timestamp semantics are Snowflake specific here.

Why it happens

dbt success only means the statements executed, not that they matched data. A filter comparing timestamps across timezones, an equality join on keys with different casing or padding, or an incremental predicate anchored to a max that is already ahead of the source will all legitimately produce zero rows with no error anywhere.

Edge cases / pitfalls

• Snowflake string comparisons and join keys are sensitive to case and trailing spaces from some loaders; profile the join keys, do not assume.
• An empty table that downstream models join against propagates emptiness silently; add a row-count test or an accepted range check on critical models.
• Check dbt run_results and the Snowflake query history to confirm the merge or insert statement actually ran against the schema you are inspecting.

Provenance

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

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

Thread query: dbt run succeeds but the snowflake table is empty

TL;DR: The run succeeded because the SQL ran, but its predicates or joins filtered out every row: common culprits are an incremental filter against the wrong max timestamp, a var or target-dependent filter, or an inner join to an empty upstream. Compile the model, run its SQL manually in Snowflake with filters relaxed step by step, and fix the predicate that eats all rows.