SQL Warehouse - Table does not support overwrite by expression:

[adriennn]

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;

[Kaniz · Accepted solution]

Hi @adriennn, When facing the "Table does not support overwrite by expression" error, consider these quick steps:

1. Check Delta table overwrite compatibility.
2. Verify schema alignment and column types.
3. Review and validate the replace where expression.
4. Ensure no reserved keywords in field names.
5. Debug incrementally; start with a simple SELECT * query.
6. Wrap your query in a try block for exception handling.

[adriennn]

Thank you for the checklist @Kaniz,

> 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.

[Kaniz]

Hi @adriennn, To resolve this, consider extracting only the date part from the timestamp.

[adriennn]

Thanks @Kaniz 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.