Configuration spark.sql.sources.partitionOverwriteMode is not available.

[Bram]

Dear,

In the current setup, we are using dbt as a modeling tool for our data lakehouse.

For a specific use case, we want to use the insert_overwrite strategy, where dbt will replace all data for a specific partition:

Databricks configurations | dbt Developer Hub (getdbt.com)

Hereby the specific dbt model configuration:

{{

    config(

        materialized='incremental',

        partition_by=['zcFiscalYearPeriod'],

        file_format='delta',  

        incremental_strategy='insert_overwrite',

        on_schema_change='sync_all_columns'

    )

}}

When dbt is execution the needed query’s, we retrieve the following message:

Qry:

/* {"app": "dbt", "dbt_version": "1.5.6", "dbt_databricks_version": "1.5.5", "databricks_sql_connector_version": "2.7.0", "profile_name": "user", "target_name": "default", "node_id": "model.data_platform.prep_InventoryStockDay_test"} */

set

  spark.sql.sources.partitionOverwriteMode = DYNAMIC

Msg:

Configuration spark.sql.sources.partitionOverwriteMode is not available.

Maybe important to note that we are connecting via a sql endpoint, to a unity-catalog enabled cluster.

We already contacted the DBT-team and everything looks good on dbt side, maybe it’s a setting in databricks we need to change?

[-werners-]

the sql endpoint could be the culprit.  dynamic partition overwrite is in public preview on workspace so it could be that the sql endpoints are not yet supported.

What happens if you use replaceWhere instead of insertoverwrite?

[Bram]

Hi Werners1,

thank you for the response, using the replaceWhere statement gives us the same message

[-werners-]

Weird, because replaceWhere does not require the parameter to be set.
Or do you still try to set it to true, because that is not a requirement for replaceWhere

[bbthorn]

Did you ever get a solution or answer on this error? Apparently partition overwrites are no longer a preview feature, they were released in version Databricks SQL version 2023.40 (see link below). My SQL warehouse is using 2023.40 but I am still getting the same "spark.sql.sources.partitionOverwriteMode is not available" error as you via dbt.

https://docs.databricks.com/en/sql/release-notes/index.html

• Block schema overwrite is available when using dynamic partition overwrites.
  
  

[Bram]

Hi, it seems that this is not supported via sql warehousing (ref. dbt code)

https://github.com/databricks/dbt-databricks/blob/main/dbt/include/databricks/macros/materialization...

[nad__]

Hi!
I have same issue with insert_overwrite on Databricks with SQL Warehouse. Do you have any solution or updates? Or is it still not supported by Databricks? 

[Lakshay]

Hi @nad__ , This is still not available in sql warehouse