topic Re: Configuration spark.sql.sources.partitionOverwriteMode is not available. in Data Engineering

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

Bram — Tue, 05 Sep 2023 13:53:03 GMT

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?

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

-werners- — Tue, 05 Sep 2023 14:14:03 GMT

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?

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

Bram — Wed, 06 Sep 2023 08:14:11 GMT

Hi Werners1,

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

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

-werners- — Wed, 06 Sep 2023 08:31:35 GMT

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

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

bbthorn — Fri, 13 Oct 2023 11:02:41 GMT

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.

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

Bram — Mon, 16 Oct 2023 07:58:21 GMT

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/materializations/incremental/incremental.sql#L20

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

nad__ — Wed, 24 Jan 2024 12:09:17 GMT

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?

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

Lakshay — Wed, 24 Jan 2024 13:19:21 GMT

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

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

hspuijbroek — Wed, 19 Mar 2025 10:20:29 GMT

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

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

hendrik — Thu, 29 May 2025 01:30:40 GMT

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.