upsert, soft delete the sink Delta parquet

arkiboys 9,686 Reputation points
2021-10-07T12:28:48.233+00:00

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

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,104 questions
{count} votes

Accepted answer
  1. HimanshuSinha-msft 19,381 Reputation points Microsoft Employee
    2021-10-08T19:11:27.36+00:00

    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 .

    138929-image.png

    The ALTER ROW should have the below dynamic expression

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

    138954-image.png

    Deletion

    138920-image.png

    139011-image.png

    Please do let me know how it goes .
    Thanks
    Himanshu

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

    • Please don't forget to click on 130616-image.png or upvote 130671-image.png 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

0 additional answers

Sort by: Most helpful