#49. Azure Data Factory - Implement Upsert logic in Mapping data flow.

[@monibodhuluru6267]

What if we don't have id / unique columns? How can we achieve using composite key (adding multiple columns in alter row?

[@sreeg9662]

what is the stage that you have used to copy xls ?. beging step is missing .

[@thesoftwarefarmer]

Alter Row transformation only works with excel files and RDBMS but what if we have to deal with a CSV or TSV files?

[@RodrigoZenga]

This is Awesome! tks for teaching us What about if my data source is a Paginated API JSON File ?

[@ksowjanya4488]

Howto implement this with dataverse dataflow?

[@anudipray4492]

Why you removed the space fron sink there is no space

[@Lihka080]

Hi Mam, suppose I have 1 lakh records in the initial load, later I have added 2000 records to source data and no changes happened to 1 lakh, so what will be the result of your pipeline. In the second run, will it perform insert/update operation for 1 lakh records or will it ignore and perform insert operation for the latest 2000 records

[@vrukshalikapdekar4627]

Superb 👍

[@AllAboutBI]

Thanks 🙏

[@codeworld8981]

Can i implement scd1 and scd2 in same dataflow? Pls let me know

Please, if you find the solution, let me know

[@AllAboutBI]

I don't think it's possible in one data flow

[@codeworld8981]

@@AllAboutBI If we implement these in separate dataflow pls let me know how to do it.

[@sivaramkodali8282]

Can we pass sno as parameter? I want to upsert multiple files with parameters.

[@muhammadehsanullah]

Thanks!

[@anudipray4492]

Simple matching will update and not match then insert on keycolum

[@RajeevKumar-zf8ox]

Nice with SCD Type -1 Implementation. Could you please upload the video for SCD Type-2 implementation. How to maintain the history.

[@AllAboutBI]

Sure.

[@RajeevKumar-zf8ox]

In case of incremental load, if there are suppose 70 columns , how will you compare and find the change. When I answered in the interview, that with the help of where clause comparing each columns to detect the change, the interviewer was not satisfied. If you could please help how to handle such scenarios?

[@AllAboutBI]

Hashing could be the expectation in that case. Hash function can find out if the columns have changed or not

[@vivekk9564]

I am trying to create parameterized data flow where Source & Target are dynamic and I achieved with simple Allow Insert and Recreate table options in Target Settings. But I need to implement Upsert logic for the same and I believe it requires Key Columns also to be parameterized which I am unable to do. Can you please suggest?

[@rodrigonicolastamarizcasti5279]

Can you find an answer to this?

[@tipsandhacksbygaurav]

Have you used dynamic join fields, exactly same manner. Refer this one - kzbin.info/www/bejne/eX6ygYOfmp6Vjq8

[@pamilad5473]

Assume there is no primary key column in source data...then how should perform upsert logic?

[@overlord7096]

You need make composite key which will be concatenation of 2 or 3 columns.

[@srikanthbachina7764]

Hi instead of role based mapping we can use auto mapping for drifting columns right

[@AllAboutBI]

Yes. Rule based mapping helps for type casting.

[@edwinraj2652]

Hi, Thanks for the Video. Is this Upsert logic, works for Cosmos DB. When choosing Cosmos in the Sink, Key columns property is not available for Cosmos.

[@AllAboutBI]

It does support Cosmos db as sink. Haven't you set any key while creating the Cosmos db container/table?

[@edwinraj2652]

@@AllAboutBI Yeah, it is working. previously I dont have an id field defined. Now it's working fine. Can you post a video about implementing SCD type 2 in cosmos?

[@balamuralipati8604]

@@edwinraj2652 where is the option for defining key ? i can see only unique key. But i want to upsert based on composite key?

[@edwinraj2652]

@@balamuralipati8604 Upserts in Cosmos works based on 'id' property. It's the default behaviour , we dont need to choose the 'id' property in adf sink.

[@balamuralipati8604]

@@edwinraj2652 how would data factory know which rows to update.. ? If i am trying to upsert an excel file, it has two columns col1,col2 and cosmos has id, col1, col2, then how does the update happen?

[@harikrishna-el7so]

hi its worth watching ,,pls can u share m the azure synapse analytics videos

[@AllAboutBI]

sure, I will do it once the ADF is done.

[@overlord7096]

In sink diectly you are doing upsert rhen why you are using alter row activity?

[@AllAboutBI]

It's not possible to implement without alter row transformation if we got to update something

[@preetijaiswal9089]

Hi, I need to implement upsert in sql server using ADF, How to implement that either by copy activity or Data flow. In mapping data flow sql server is not supported. so how to do this?

[@Mayank612722]

If your source and sink are SQL, why don't you just implement using SQL Query?

[@preetijaiswal9089]

Yeah i can surely do that.. but i need to automate the process and migtate around 70-80 tables and that too using adf so that's why

[@Mayank612722]

@@preetijaiswal9089 If you're talking about moving data from on prem to cloud SQL, you can use a lookup to get all the names of tables in the database and then on its output you can run SELECT * INTO. I'm pretty sure this might not be the best way though.

[@preetijaiswal9089]

@@Mayank612722 this is for migration i need to do Update or Upsert.

[@Mayank612722]

@@preetijaiswal9089 Oops, I read mitigate as migrate.

[@Myachnik]

Thanks a lot! You help me :)

[@raghunandan3068]

Thanks for this Video! As per my understanding from this video, when doing an UPDATE, it will update all the data point in the record though it is not changed. Is there a way not to UPDATE the unchanged data points. Thanks..

[@AllAboutBI]

Yes, correct👍

[@raghunandan3068]

Could you pls, put up a video for this. Thanks in advance. when there is record are less it may not impact, however if is million of record updating the same value again, may be too costly. Request you to post a video, on how to overcome this.

[@ash3rr]

count how many times: ohhh k.

[@AllAboutBI]

Ok 😀

[@mohammadfahim2002]

that's annoying