duplicate rows - alter row issue

Question

duplicate rows - alter row issue

arkiboys 9,711

Hi,
I am using delta parquet files.
My dataflow seems to be doing just insert and not update.
Any suggestions?

As you see in screen-shot I am using alter row as well as sink delta with keys.

alterRow2 is set to updateIf --> true()
sinkUpdate is set to Allow Update
list of columns added in each drop down

alterRow1 is set to insertIf --> true()
sinkInsert is set to Allow Insert
list of columns added in each drop down

I have the following in both derivedcolumns:
LookupSourceKey
LookupTargetKey
sha1(toString(Quantity_DC)+toString(Receiving_plant)+toString(Material)+toString(Plant)+toString(Batch)+toString(Posting_Date))

Let me know if you would like me to send you any more screen-shots.

Thank you

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

HimanshuSinha 19,637 Microsoft Employee Moderator

Hello @arkiboys ,
Thanks for the question and using MS Q&A platform.
As we understand the ask here is why you are only seeing INSERT and not UPDATE , please do let us know if its not accurate.
I did tried out the a simple transformation to test my theory . The INSERT and UPDATE needs attention .
What I did After the Exist activity I added derieved column and added an a Operation column . The idea is if you are using EXIST that means the record exists and UPDATE and NOT exists means INSERT .

After this derived column we have the ALTER roq and I used the below expression
For UPDATE :
Update if -->iif(Operation == 'Update',true(),false())
For INSERT : iif(Operation == 'Insert',true(),false())

It worked for me and I was able to get both INSERT and UPDATe work on my side .

Please do let me if you have any queries.
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

arkiboys 9,711 Reputation points

2022-06-03T04:50:34.127+00:00

Hi,
Thanks for this which I am trying to replicate...
1-
do you have to have the Operation column == "update" for updating the delta rows and operation == "insert" for inserting into delta parquet?
At present I am doing as same as you but just before the sink I select all columns except Operation column.
Thanks
arkiboys 9,711 Reputation points

2022-06-03T07:54:46.607+00:00

Hi,
I did further work based on your suggestions...
As you see in my new screen-shot, there is sinkInsert - delta parquet and sinkUpdate delta parquet.
for each of the sink alter row I put the if statement as you see in screen-shot...
In addition, I added sink1 and sink2 which are csv files for me to see the loaded data...
The csv files look good so far, although I will do more tests...

1-
Not sure if you can answer this but the only way I know to read the data in delta parquet files in my sink daily folder is to use databricks. In this case reading the delta parquet files using databricks, not sure why I do not see the new rows added into the delta parquet files.
2-
Note that the sink csv files do show the inserted and updated rows but they seem to be only in the updated file which is the update operation and insert file is empty.

Any thoughts

Share via

duplicate rows - alter row issue

0 additional answers

Your answer