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

[hspuijbroek]

@Lakshay when will this be added? I still get this error in 2025.

[hendrik]

An approach that works well when using a Databricks SQL Warehouse is to use the replace_where strategy - I've just tested this. It also works with partitioned tables:

{{ config(
   materialized='incremental',
   incremental_strategy='replace_where',
   incremental_predicates=["date in (" ~ var('date', '1999-12-31') ~ ")"],
   partition_by=['date']
) }}

select * from {{ ref('table_upstream') }}
where date in ({{ var("date", "1999-12-31") }})

which is compiled into the following SQL

insert into `table_downstream`
  replace where
    date in ('2023-01-03', '2023-01-02')
  table `table_downstream__dbt_tmp`

and run it with the following filter predicates dynamically passed in via CLI:

dbt run --vars "{\"date\": \"'2023-01-03', '2023-01-02'\"}"

I'd recommend to avoid using insert_overwrite with DBSQL warehouses. Also, omitting partition_by=['date'] causes the entire table to be overwritten. While recoverable via time travel and RESTORE, it’s risky if not intended.