Hello everyone, I have a situation where I need to delete and insert data from a delta table atomically, using spark. Is there a way that I could do this within a transaction? Please note that I can't use

replaceWhere

, because the replace condition is arbitrary and the delta version doesn't support it (and I can't upgrade it). Also, I can't use

merge

because there's no one-to-one mapping between the source data frame and the target delta table. Any advice is welcome. Thank you! PS: I need to wrap these commands (delete and insert) into a transaction, if possible

DeltaTable.forPath(deltaTableLocation).delete(dataCondition)
                spark.read
                  .load(sourceDataLocation)
                  .filter(dataCondition)
                  .write
                  .format("delta")
                  .option("mergeSchema", "true")
                  .mode(Overwrite)
                  .save(deltaTableLocation)

I think with the limitations you've described you could perhaps do it by doing the following: 1. read in your new source data, filtered by

dataCondition

, as

df_new

2. generate a unique key value for each row in

df_new

. Perhaps with

monotonically_increasing_id()

from

pyspark.sql.functions

, but you would need to start from the max value already in your target data. 3. read in your existing data, filtered by

dataCondition

, as

df_deletes

4. union

df_new

and

df_deletes

5. delta merge on the unioned df, matching on the generated key column. On match then delete, on no match then insert. I'm not nearly as experienced as most in here but at the least hopefully I can inspire you to come up with something more efficient.

i think this is a good ask, it's popping up more and more (see this convo for example, just yesterday) - would you mind creating an issue on the delta github?

Thank you @Jon Stockham! Will try this out...

well it's only easily solvable by an upgrade if such a feature were built! 😄 i will make the issue, no worries... also, one thing to watch out for in the solution proposed by @Jon Stockham (which I think may work but I haven't tested) is it is subject to dirty reads if you have concurrent writers

Yes, I can have concurrent writers. I'll dig deeper into this. Thank you for the heads-up, Nick!