materialized='table' causes brief service interruption (empty table window)

[pandabrun]

Describe the bug

When using the default table materialization in a dbt project on Databricks (Delta Lake), the process of replacing the table results in a brief, but critical, service interruption or "empty table" window for consumers.

This downtime occurs during the execution of the final SQL step, which is equivalent to a form of CREATE OR REPLACE TABLE AS SELECT ....

While Databricks/Delta Lake documentation suggests this operation is atomic and provides Snapshot Isolation (protecting long-running queries), we observe that new, short-lived queries (e.g., from dashboards, reports, or subsequent dbt models) that start immediately after the transaction commits can either:

1. Fail due to the table being in a transient state.

2. Return zero records, perceiving the table as momentarily empty before the new snapshot is fully available/indexed.

This behavior prevents true zero-downtime deployment for models that require a full refresh, which is a major constraint for production data pipelines.

Steps To Reproduce

In as much detail as possible, please provide steps to reproduce the issue. Sample data that triggers the issue, example model code, etc is all very helpful here.

Expected behavior

The table materialization should support a true zero-downtime swap, ensuring that consumers always have access to a consistent, non-empty snapshot of the data.

System information

dbt --version:*

[2025-11-17 00:59:31,575] INFO:root:00:59:31  Running with dbt=1.9.4
[2025-11-17 00:59:34,932] INFO:root:00:59:34  Registered adapter: databricks=1.10.2
[2025-11-17 00:59:35,093] INFO:root:00:59:35  Unable to do partial parsing because saved manifest not found. Starting full parse.

**The output of python --version:Python 3.11.11

Additional context

Add any other context about the problem here.

[benc-db]

Are you using 'use_materialization_v2'? It not, I'm genuinely surprised, as you should be getting atomic create or replace. If you are setting use_materialization_v2 to true, there we are trading atomicity for correctness due to a hole in the Databricks SQL api that we are working to resolve internally. When set to true, you can use this model config use_safer_relation_operations: true to get close to zero downtime swapping, but at the cost of UC losing history (because it tracks history by object identifier, and the swapped in table is a new object). These various issues are why we are not flipping this flag to true by default, but instead or trying to get CREATE OR REPLACE to work with a broader range of config, to retain atomicity and correctness (i.e. masks set at create time, constraints enforced before the data enters the table).