upsert, soft delete the sink Delta parquet

Question

Hello,
How is it possible to have the sink Delta parquet file to upsert (update/insert) as well as a soft delete.
For example,
1- source parquet file has 10 rows --> sink Delta parquet gets 10 rows --> this is insert
2- source parquet file had 10 rows initially, but now it has 8 rows --> sink Delta parquet has 10 rows still but the Deleted column to be set to 1 for the two deleted rows --> this is insert/update

by soft delete I mean the Deleted column is set to 1 for the deleted rows whereas all the other rows have Deleted set to 1

Thank you

Answer 1

Hello @arkiboys ,
Thanks for the ask and using Microsoft Q&A platform .
Looking at the threads which you have created , it looks like your source is paraquet and sink is delta . I did implemented the something similar here .

SQL - > Paraquet - Delta .
I am using the EXIST transform here whcih I had issues before , I think I know the issue now .

Insert

You select the exists or not "doesn't exist " will depend on the left and right stream , so please make a note of this . What ever we do in INSERT , for DELETEION we will do the opposite .

The ALTER ROW should have the below dynamic expression

iif(Deleted==0, true(), false())

Deletion

Please do let me know how it goes .
Thanks
Himanshu

-------------------------------------------------------------------------------------------------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators