[Python] Databricks merge issue with null values

I'm merging two delta tables with databricks and one struct in the target delta table isn't merged as expected.

On the source delta table I have data as follow:

{
id: '123',
artist: {
song: {
id: null,
name: null,
country: null
}
}
}


My goal is to extract the song, and set it directly as null if its id is null. To do so I do a select on the source table before merging:

self.spark.read.option("mode", "PERMISSIVE")
.json(pattern, schema=artistSchema)
.select(
col('id'),
when((col("artist.song").isNull() | col("artist.song.id").isNull()), lit(None)
.otherwise(col("artist.song")).alias("song")
)


When testing the select on its own, it gives me the expected data:

{
id: '123',
song: null
}


The issue arise when doing the merge afterwards using this (supposedly valid) data. Instead of {song: null} it merges with {song: {id: null, name: null, country: null}}

Here is how I do my merge:

deltaTable = DeltaTable.forPath(self.spark, deltaPath)
deltaTable.alias("existing") \
.merge(
dataDf.alias("updates"), # dataDf is the "selected" input
"existing.id = updates.id")
.whenMatchedUpdateAll("(updates.song IS NOT DISTINCT FROM existing.song) OR (updates.song IS NOT NULL AND updates.song.id != existing.song.id OR updates.song.name != existing.song.name OR updates.song.country != existing.song.country)")
.whenNotMatchedInsertAll()
.execute()


Since I can test the select on its own and it seems to provide valid data, I can only see an issue with the merge itself. What could cause this modification of the song struct ?

Continue reading...