dbt wrote to the same table twice

[septponf]

We have hundreds of dbt models in production and have never experienced this before, until tonight.
A standard table model was created, and then the data was written to it twice.
We noticed this thanks to a unique data-test.

Checking the table history in Unity Catalog, I can se the replace operation followed by two writes.

Checking the query history, I can see the Replace operation followed by a CANCELED insert, then a FINISHED insert. Something has gone horrible wrong here, since both inserts has materialized.

Maybe this is not a dbt issue? But a transactional databricks sql issue?

Has anyone experienced this?
Has it been addressed in any dbt update or dependent library?
Any ways to mitigate this?

Since the dbt logs was generated on a temporary cluster, I do not have access to them and can not provide them here.

use_materialization_v2: true

"dbt_version": "1.10.8"
"dbt_databricks_version": "1.10.9"
"databricks_sql_connector_version": "4.2.4"
Databricks-Runtime/17.3.x-aarch64-photon-scala2.13

[BindusekharGorintla]

Hi @septponf , I have faced the same issue.

It is not a DBT configuration issue, its Databricks Delta Lake transactional isolation bug. I have resolved by using below steps:
Add the below spark settings in profiles.yml
session_properties:
spark.databricks.delta.schema.autoMerge.enabled: "false"
spark.databricks.delta.optimizeWrite.enabled: "false"
spark.sql.adaptive.enabled: "false"
spark.databricks.delta.retentionDurationCheck.enabled: "false"
If we implement:
Spark disables automatic optimizations that can cause concurrent write issues
Prevents schema evolution during writes

[0RafalFrydrych0]

Since February 1, same issue here.

It seems that every two/three days I get random double writes on some tables (not all of them).
The difference in my case is that both queries succeded.

What is more the queries are executed within ~60 seocnds from each other.
@septponf was this case for you as well?

"dbt_version": "1.10.13",
"dbt_databricks_version": "1.10.14",
"databricks_sql_connector_version": "4.0.5",
Databricks-Runtime/17.3.x-aarch64-photon-scala2.13

[septponf]

Since February 1, same issue here.

It seems that every two/three days I get random double writes on some tables (not all of them). The difference in my case is that both queries succeded.

What is more the queries are executed within ~60 seocnds from each other. @septponf was this case for you as well?

"dbt_version": "1.10.13", "dbt_databricks_version": "1.10.14", "databricks_sql_connector_version": "4.0.5", Databricks-Runtime/17.3.x-aarch64-photon-scala2.13

No the write statements are executed in tight succession and the first one with status canceled.
We run on Azure so I have also filed a ticket with Microsoft if it is due to dw serverless commit behavior/retry patterns.

[sd-db]

This should be resolved post #1363 if this was an issue due to client retrying when server has given the go ahead that it is safe to do so. We need to set the flag _respect_server_retry_after_header to true. One note is that server is more conservative on retries some some cases where retries were helpful earlier in resolving a transient issue would now fail. Hence this is behind a flag but I feel the tradeoff is worth it as correctness matters more.

[septponf]

This should be resolved post #1363 if this was an issue due to client retrying when server has given the go ahead that it is safe to do so. We need to set the flag _respect_server_retry_after_header to true. One note is that server is more conservative on retries some some cases where retries were helpful earlier in resolving a transient issue would now fail. Hence this is behind a flag but I feel the tradeoff is worth it as correctness matters more.

Thanks.
Since you mentioned flag. Just to be sure, is it a flag (in dtb_project.yml), or a connection parameter in profiles.yml?

[sd-db]

My bad @septponf on the confusion, It's a connection parameter in profiles.yml, set under connection_parameters. Example:

my_profile:
  outputs:
    dev:
      type: databricks
      host: ...
      http_path: ...
      token: ...
      connection_parameters:
        _respect_server_retry_after_header: true

Two prerequisites:

• dbt-databricks >= 1.11.7 (the version that bumped the connector upper bound to allow 4.1.5)
• databricks-sql-connector >= 4.1.5 installed (this is where the flag was introduced — see databricks/databricks-sql-python#756)