topic Re: SQL Warehouse - Table does not support overwrite by expression: in Data Engineering

SQL Warehouse - Table does not support overwrite by expression:

adriennn — Wed, 22 Nov 2023 14:18:12 GMT

I'm copying data from a foreign catalog using a replace where logic in the target table, this work fine for two other tables. But for a specific one, I keep getting this error:

Table does not support overwrite by expression: DeltaTableV2(org.apache.spark.sql.SparkSession@...,abfss://...@....dfs.core.windows.net/__unitystorage/catalogs/.../tables/...,Some(CatalogTable( Catalog: ...Database: ...Table: ...Owner: c... Created Time: ... Last Access: UNKNOWN Created By: Spark Type: MANAGED Provider: delta Table Properties: [delta.checkpoint.writeStatsAsJson=false, delta.checkpoint.writeStatsAsStruct=true, delta.columnMapping.maxColumnId=91, delta.columnMapping.mode=name, delta.enableChangeDataFeed=false, delta.lastCommitTimestamp=1700647538000, delta.lastUpdateVersion=0, delta.minReaderVersion=2, delta.minWriterVersion=5]

Anybody has seen this before? I tried deleting the table, creating using another name, nothing works. Seems there either is some issue with field names or maybe something in the data? The table copies fine if I don't use the replace where logic in the query.

fails

insert into mycatalog.mydb.mytable

replace where myfield >= {{ pipeline param }}

select * from myforeigncatalog.mydb.mytable;

works

insert into mycatalog.mydb.mytable

select * from myforeigncatalog.mydb.mytable;

Re: SQL Warehouse - Table does not support overwrite by expression:

adriennn — Thu, 23 Nov 2023 13:02:37 GMT

Thank you for the checklist @Retired_mod,

> Review and validate the replace where expression.

I was using dateadd() with a pipeline parameter, dateadd() returns a timestamp, which was being compared against a date column which threw the error.

Re: SQL Warehouse - Table does not support overwrite by expression:

adriennn — Fri, 24 Nov 2023 07:24:09 GMT

Thanks @Retired_mod of course this is what I did.

But I think the bottom line of this issue is the unclear error message, which clearly states that the target delta table is incompatible, when the error is clearly on the source side.

A.

Re: SQL Warehouse - Table does not support overwrite by expression:

aakashnand-kt — Thu, 27 Nov 2025 05:36:47 GMT

Thank you @adriennn I encountered same issue and your post helped me resolved this. I agree that the error message given by databricks is not so helpful and I wasted my time in investigating table properties a lot before I found your post