Compare the two versions of dataframes for Upsert and list out changes

Question

I am doing upsert operation in databricks. Now I want to check what is changed between two upsert operation.

My original df1 look like this>>

My upserted df2 look like this >>

I want Output like this>>

here id is my primary_key

Join df1 on df2. Action column if df1.id is not null => updated, else inserted. From there add the old_age and old_city in case of update. Then pivot the whole thing. — ferdyh, Jan 16 '23 at 20:11

score 1 · Answer 1 · answered Jan 24 '23 at 15:59

Simpler would be to enable Change Data Feed on the given Delta Table - then you can read all changes between - inserts/updates/deletes, and you can consume these changes either as batch or as stream:

all_changes = spark.read.format("delta") \
  .option("readChangeFeed", "true") \
  .option("startingVersion", 0) \
  .option("endingVersion", 10) \
  .table("myDeltaTable")
inserts = all_changes.filter("__change_type = 'insert'")
updates = all_changes.filter("__change_type = 'update_postimage'")
deletes = all_changes.filter("__change_type = 'delete'")

Compare the two versions of dataframes for Upsert and list out changes

1 Answers1