Fix dbt-trino snapshot failing to create __dbt_tmp after first run

TL;DR

Remove the location property from your snapshot config and rerun; the temp table goes into a subdirectory under the schema location and snapshots work.

Steps

1. dbt snapshots on Iceberg catalogs worked once, then failed because the snapshot tried to create the _dbttmp table in the same storage location as the final snapshot table, which Trino forbids. The workaround confirmed by a maintainer is to omit the location table property on snapshot models so content lands in a subdirectory of the schema location. Since dbt-trino v1.7.1 the adapter raises an explicit error for this unsupported combination, so upgrade past 1.7.1 to get the clear message instead of the cryptic failure.
2. My snapshot ran once and then every rerun failed creating __dbt_tmp. A dbt-trino maintainer confirmed the cause: with an explicit location property, the temp table targets the same location as the snapshot table, which Trino rejects. Remove the location property from your snapshot config and rerun; the temp table goes into a subdirectory under the schema location and snapshots work. Note snapshots with an explicit location are simply unsupported in dbt-trino (v1.7.1+ raises a clear error saying so), so do not try to force it. Source: https://github.com/starburstdata/dbt-trino/issues/346

Expected: A dbt-trino maintainer confirmed the cause: with an explicit location property, the temp table targets the same location as the snapshot table, which Trino rejects.

When to use

You are seeing this: The workaround confirmed by a maintainer is to omit the location table property on snapshot models so content lands in a subdirectory of the schema location. Use this skill when you run into "Fix dbt-trino snapshot failing to create _dbttmp after first run".

When not to use

If your error message or symptom does not match what is described above, this is probably not your fix. Search for your exact error text instead of forcing this one to fit.

Versions

Versions mentioned in the source: dbt-trino v1.7.1, past 1.7.1. If you are on something much newer or older, the details may have shifted.

Why this happens

The original report does not dig into a root cause. It documents the symptom and the fix that resolved it.

Edge cases

• Note snapshots with an explicit location are simply unsupported in dbt-trino (v1.7.1+ raises a clear error saying so), so do not try to force it.

Sources

• https://github.com/starburstdata/dbt-trino/issues/346

Question asked in the thread: dbt snapshots on Iceberg catalogs worked once, then failed because the snapshot tried to create the _dbttmp table in the same storage location as the final snapshot table, which Trino forbids. The workaround confirmed by a maintainer is to omit the location table property on snapshot models so content lands in a subdirectory of the schema location. Since dbt-trino v1.7.1 the adapter raises an explicit error for this unsupported combination, so upgrade past 1.7.1 to get the clear message instead of the cryptic failure.

Verified answer: My snapshot ran once and then every rerun failed creating __dbt_tmp. A dbt-trino maintainer confirmed the cause: with an explicit location property, the temp table targets the same location as the snapshot table, which Trino rejects. Remove the location property from your snapshot config and rerun; the temp table goes into a subdirectory under the schema location and snapshots work. Note snapshots with an explicit location are simply unsupported in dbt-trino (v1.7.1+ raises a clear error saying so), so do not try to force it. Source: https://github.com/starburstdata/dbt-trino/issues/346

Thread: https://vectle.com/threads/thr_bXgaf1vMag9KnGr63ukjtQ