ADF Copy Data Upsert creates two rows having same composite key

Question

ADF Copy Data Upsert creates two rows having same composite key

Dan Barbary 36

I have a Copy Data task with "Upsert" enabled.

Something very strange is happening where when a row that is found in the destination data set is also in the DELTA file which is to be loaded. ADF is not UPDATING the row, and is adding a second row

This however is only happening with a delta parquet source file which is coming from a different server. I believe the columns I declared (cora_acct_id,hostitemid,server_ip_addr) would create a composite key to find the data within the destination - and update if it is there... well that is not happening, but strangely only for my US data and not my Canadian data.

Why in the world is it writing a second row for this and other examples. (377 examples I can find)

AnnuKumari-MSFT 34,566 Reputation points Microsoft Employee Moderator

2022-07-28T14:21:19.707+00:00

Hi @Dan Barbary ,
Just checking in to see if the below answer helped. Please do consider clicking Accept Answer as accepted answers help community as well.

1 answer

Your answer

AnnuKumari-MSFT 34,566 Reputation points Microsoft Employee Moderator

2022-07-28T14:21:19.707+00:00

Hi @Dan Barbary ,
Just checking in to see if the below answer helped. Please do consider clicking Accept Answer as accepted answers help community as well.

Answer 1

AnnuKumari-MSFT 34,566 Microsoft Employee Moderator

Hi @Dan Barbary ,
Thankyou for using Microsoft Q&A platform and thanks for posting your question.
As I understand your query, you are trying to perform upsert inside Copy data activity in ADF, however it's not working as expected as it is creating a duplicate. Please let me know if my understanding is incorrect.

What is happening in your case is certainly not expected. As upsert merges the data if the keyColumns matches and then perform update or insert accordingly. There shouldn't be duplicates coming in.

Kindly check if there is any trailing space in any of the column values - cora_acct_id, hostitemid, server_ip_addr which is leading it to treat the two rows as separate entries.

Also, kindly make sure that the source data doesn't have duplicate entries.

Please let us know if any of these is the case . Looking forward to your response. Thanks !

Dan Barbary 36 Reputation points

2022-07-15T21:06:38.617+00:00

Hello, I did check that there was no trailing spaces or strange hidden characters in the key fields. They are all correct.
AnnuKumari-MSFT 34,566 Reputation points Microsoft Employee Moderator

2022-07-19T09:17:03.823+00:00
Hi @Dan Barbary ,
This is not an expected behavior . This might have been caused due to following reasons:

Rerun of the pipeline due to retry enabled after incomplete transaction . For Example lets say 1L records there - it is writing in 10 batches and per batch 10K records - internally activity fails and it will rerun again automatically. So, the failed batch when it reruns automatically , it inserts the duplicates.

Source data itself has duplicate records.

To check the exact cause of the error, Would it be possible to add a new column during the runtime with value as utcdate() which would depict when that particular record got inserted or modified so that for duplicate record ,we would be able to identify in what frequency it inserts the record and deep dive into the cause of it.

Please let us know if it helps! Thanks
AnnuKumari-MSFT 34,566 Reputation points Microsoft Employee Moderator

2022-07-26T08:42:31.877+00:00

Hi @Dan Barbary ,
Just checking in to see if you got a chance to see previous response. If the suggested response helped you, please click Accept Answer and kindly upvote the same.
Dan Barbary 36 Reputation points

2022-07-26T14:51:33.73+00:00

Sorry no time yet to check on this, adding the column is a good idea however just need time to try it out.
AnnuKumari-MSFT 34,566 Reputation points Microsoft Employee Moderator

2022-08-01T05:45:02.337+00:00

Hi @Dan Barbary ,
Just checking in to see if you got a chance to try the above approach. If the suggested response helped you, please click Accept Answer as the accepted answer helps community as well.
Dan Barbary 36 Reputation points

2022-08-08T15:21:54.513+00:00

I was away for a week, today I aim to look into the solutions.
Dan Barbary 36 Reputation points

2022-11-02T19:49:54.793+00:00

It seems the duplicate records only appear when a scheduled trigger is run. Running the pipeline manually in debug does not cause the error as well as running the pipeline trigger manually "Trigger now" also does not produce duplicate records. I have another pipeline now that is producing errors in the same manner. I have also checked to see if the other settings such as "Write Batch Size", "Max concurrent Connections" and in the settings tab "Degree of Copy Parallelism" have any affect. No matter how I seem to try and debug the process, I end up with no duplicate records. YET when the process is allowed to run on its overnight scheduled trigger, This is where I get the duplicate records showing up.

Very confusing behavior. I'm stumped
AnnuKumari-MSFT 34,566 Reputation points Microsoft Employee Moderator

2022-11-28T05:31:32.177+00:00

Hi @Dan Barbary ,
Would recommend you to please file a support ticket for deeper investigation and in case if you don't have a support plan, do let us know here so that we can check on other options to unblock you.
Samriddhi Gupta 20 Reputation points

2023-03-22T15:26:36.44+00:00

Hello @AnnuKumari-MSFT ,

I am facing the same issue. In my source, the duplicates are removed, as seen in first query result. However, in my destination, there are two entries, second query result. Also, there are 3 such records out of ~1100 records.

In my case, the date and time is also the same. I am wondering on why all activities are happening at exactly the same timestamp.

Please advise on what is going wrong and how to remove the duplicates
Dan Barbary 36 Reputation points

2023-03-22T16:30:02.91+00:00
As far as the ADF Upsert is concerned. I believe it does work as designed. In the data that was causing the issues for us was coming from a source that in fact did have duplicate rows of data for what ever reason. I believe because those rows live in different rows ( and potentially different uploaded batches ) ADF sees them as net new when updating.

I'd suggest to try :

Ensure you have no duplicate rows, I believe ADF looks at the entire row to consider it for UPSERT, not just the keys you provide to identify the row.

Add the row to the destination manually first with all the same data that would be inserted. Then try the pipeline. to see if the row would be updated/ inserted or both.

Pray, there is a bit of mystery surrounding this - though overall, with the number of records we have discovered as being duplicated in our instance was the fault/result of the source data also containing a duplicate record. Bad data in bad data out.

Hopefully some words here would trigger you to find your solution. best of luck.

Share via

ADF Copy Data Upsert creates two rows having same composite key

1 answer

Your answer