CODEWITHSUNDEEP
azureazure-data-factory
UnskilledCoder
UnskilledCoder

Reputation: 312

Upsert Option in ADF Copy Activity

With the "upsert option" , should I expect to see "0" as "Rows Written" in a copy activity result summary? My situation is this: The source and sink table columns are not exactly the same but the Key columns to tell it how to know the write behavior are correct. I have tested and made sure that it does actually do insert or update based on the data I give to it BUT what I don't understand is if I make ZERO changes and just keep running the pipeline , why does it not show "zero" in the Rows Written summary?

Upvotes: 1

Views: 17041

Answers (3)

Nykolai Peterson
Nykolai Peterson

Reputation: 35

Marked solution is misleading. For the situation described in the question, a write action is indeed performed on each record, even if there is no difference between the source and the target. The only difference seems to be whether that write action is a create or an update, i.e. which plugins will trigger.

Upvotes: 0

user3588007
user3588007

Reputation: 267

Upsert only updates the record if it is present in both source and target tables and inserts if not present in the target and present in source.

Not sure why in the above answer it was mentioned that "it is modifying all records irrespective of the changes in data." and it is not true.

Upvotes: 4

Saideep Arikontham
Saideep Arikontham

Reputation: 6114

The main reason why rowsWritten is not shown as 0 even when the source and destination have same data is:

  • Upsert inserts data when a key column value is absent in target table and updates the values of other rows whenever the key column is found in target table.
  • Hence, it is modifying all records irrespective of the changes in data. As in SQL Merge, there is no way to tell copy activity that if an entire row already exists in target table, then ignore that case.
  • So, even when key_column matches, it is going to update the values for rest of the columns and hence counted as row written. The following is an example of 2 cases

The rows of source and sink are same:

  • The rows present:
id,gname
1,Ana
2,Ceb
3,Topias
4,Jerax
6,Miracle

enter image description here

When inserting completely new rows:

  • The rows present in source are (where sink data is as above):
id,gname
8,Sumail
9,ATF

enter image description here

Upvotes: 2

Related Questions