The current table materialization strategy uses a rename-swap approach: it creates a new table with a temporary name, renames the existing table to a backup name, then renames the new table into place, and finally drops the backup.
On databases configured with Read Committed Snapshot Isolation (RCSI), this causes a problem. During the rename window there is a brief period where the target table name does not resolve to any object. Concurrent queries executing against the table during this window will fail with an object-not-found error, even under RCSI — because DDL operations are not covered by snapshot isolation in the same way DML is.
This is particularly impactful for tables that are queried frequently or in production-like environments where dbt runs happen while the database is under active read load.
Additionally, the rename-swap approach destroys indexes (including clustered columnstore indexes) on every run, since the table is replaced entirely. This means indexes must be recreated from scratch each run, adding overhead and leaving another window where the table exists without its expected indexes.
Proposed Solution (As in I've built and am using it to test it)
Introduce an alternative DML-based table refresh method, configurable via a table_refresh_method model config option:
table_refresh_method: dml or 'rename' (default, current behavior)
When table_refresh_method: dml is set and the target table already exists:
Build new data into a table (__dbt_refresh) via SELECT * INTO
Compare schemas — if columns have changed, fall back to the rename-swap approach for that run.
Swap data via DELETE + INSERT inside an explicit transaction — under RCSI, any concurrent reader whose statement begins before the COMMIT will see the pre-swap data via row versioning, and any reader whose statement begins after will see the new data.
Clean up the table
This approach:
Eliminates the name-resolution gap — the target table always exists and is queryable
Preserves indexes — clustered columnstore indexes and other indexes survive the DML refresh since the table structure is unchanged
Is RCSI-friendly — readers see consistent data for each statement via row versioning throughout the swap
Falls back gracefully — schema changes automatically trigger the existing rename-swap behavior
Is backwards compatible — the default remains rename, so no behavior change unless explicitly opted in
The current table materialization strategy uses a rename-swap approach: it creates a new table with a temporary name, renames the existing table to a backup name, then renames the new table into place, and finally drops the backup.
On databases configured with Read Committed Snapshot Isolation (RCSI), this causes a problem. During the rename window there is a brief period where the target table name does not resolve to any object. Concurrent queries executing against the table during this window will fail with an object-not-found error, even under RCSI — because DDL operations are not covered by snapshot isolation in the same way DML is.
This is particularly impactful for tables that are queried frequently or in production-like environments where dbt runs happen while the database is under active read load.
Additionally, the rename-swap approach destroys indexes (including clustered columnstore indexes) on every run, since the table is replaced entirely. This means indexes must be recreated from scratch each run, adding overhead and leaving another window where the table exists without its expected indexes.
Proposed Solution (As in I've built and am using it to test it)
Introduce an alternative DML-based table refresh method, configurable via a table_refresh_method model config option:
table_refresh_method: dml or 'rename' (default, current behavior)
When table_refresh_method: dml is set and the target table already exists:
Build new data into a table (__dbt_refresh) via SELECT * INTO
Compare schemas — if columns have changed, fall back to the rename-swap approach for that run.
Swap data via DELETE + INSERT inside an explicit transaction — under RCSI, any concurrent reader whose statement begins before the COMMIT will see the pre-swap data via row versioning, and any reader whose statement begins after will see the new data.
Clean up the table
This approach:
Eliminates the name-resolution gap — the target table always exists and is queryable
Preserves indexes — clustered columnstore indexes and other indexes survive the DML refresh since the table structure is unchanged
Is RCSI-friendly — readers see consistent data for each statement via row versioning throughout the swap
Falls back gracefully — schema changes automatically trigger the existing rename-swap behavior
Is backwards compatible — the default remains rename, so no behavior change unless explicitly opted in