topic Re: SQL - Dynamic overwrite + overwrite schema in Data Engineering

SQL - Dynamic overwrite + overwrite schema

oneill — Sun, 18 May 2025 16:11:28 GMT

Hello,

Let say we have an empty table S that represents the schema we want to keep

We have another table T partionned by column A with a schema that depends on the file we have load into. Say :

A	B	C	F
1	b1	c1	f1
2	b2	c2	f2

Now to make T having the same schema as S I do :

SET spark.sql.sources.partitionOverwriteMode=dynamic; CREATE OR REPLACE TABLE T PARTITIONED BY (A) as SELECT * FROM S WHERE false;

and the result is, as I wish :

A	B	C	D	E
1	b1	c1	null	null
2	b2	c2	null	null

Good. But the fact is I didn't see we can do something like that in databricks doc. Even worse it is said that overwrite schema option (pyspark option I guess because I don't succeed to use this option with SQL) and dynamic partition don't work together.

So here's the question : is the behavior described above a bug or a feature ?

Ps : runtime si 16.3

Re: SQL - Dynamic overwrite + overwrite schema

nikhilj0421 — Fri, 23 May 2025 06:00:34 GMT

Hi @oneill, please check this if it helps for your use case: https://docs.databricks.com/aws/en/delta/selective-overwrite#dynamic-partition-overwrites

Re: SQL - Dynamic overwrite + overwrite schema

oneill — Fri, 30 May 2025 09:54:32 GMT

Hi, thanks for the reply. I've already looked at the documentation on this point, which actually states that dynamic overwrite doesn't work with schema overwrite, while the instructions described above seem to indicate the opposite.

Re: SQL - Dynamic overwrite + overwrite schema

nikhilj0421 — Fri, 06 Jun 2025 17:42:07 GMT

Let me check on this.