Azure Data Factory Upsert not working properly

Clifford Gentiles 21

Hello,

I am using Copy Activity which will copy parquet file to SQL Datawarehouse. Sink is set to upsert with identified key columns, however I am still getting duplicate based on the concatenated key columns. Please see images below

Sink set to upsert with key columns

But duplicate is still there after running the pipeline, value for dup should only be '1' to confirm that there's no duplicate

Your response would be very much appreciated.

Thanks

Bhargava-MSFT 31,031 Reputation points Microsoft Employee

2023-01-05T22:42:27.867+00:00

Hello @Clifford Gentiles ,

Welcome to the MS Q&A platform.

Please correct me if my understanding is wrong. You are using "Synapse dedicated SQL pool" as your sink data source, and you are using upsert with an identity key column.

Are you using the identity column on the mapping tab? If not, please use the identity column on the mapping tab to see if it fixes the issue.

(The General upsert behavior is, We need all columns. We can't choose specific columns)

And could you please provide the detailed error message you are seeing on the synapse end?
Clifford Gentiles 21 Reputation points

2023-01-06T06:40:18.557+00:00

Hi @Bhargava-MSFT

Good day. Yes I am using Synapse dedicated SQL Pool as sink. I followed your advice to include key columns in mapping. However I am still getting duplicates in terms of concatenated key columns even though we are sure that's there no duplicates on the source parquet file. What am I missing here? Please take note that I don't get error message but Upsert is not functioning as expected. Please see images below. Thanks

Sink Mapping

Before Running Pipeline

After Running Pipeline
Bhargava-MSFT 31,031 Reputation points Microsoft Employee

2023-01-06T18:44:27.867+00:00

Hello @Clifford Gentiles ,

Thank you for providing more details. I am pretty sure your input file has duplicate records.

Upsert - insert new records and update old records.

Using upsert, if the input file has duplicate records that were not present in the table, those records will be inserted as duplicate records.

You can handle duplicate records using unique key constraint. Please check this video tutorial to handle duplicate records.

In case if you are still having the issue, please send us the parquet file(input file) and the table definition to troubleshoot the issue from our end.

I hope this helps! Let me know if you have any further questions.
Clifford Gentiles 21 Reputation points

2023-01-09T04:25:19.13+00:00

Good day @Bhargava-MSFT

As I have said, we are sure that there's no duplicate on the input parquet file. Please see input file validation and table definition below. What is your email to send specific details regarding this issue? Thanks

Parquet validation

Table Definition
IF NOT EXISTS (SELECT * FROM sys.objects O JOIN sys.schemas S ON O.schema_id = S.schema_id WHERE O.NAME = 'LinkedTransactions' AND O.TYPE = 'U' AND S.NAME = 'dw')
CREATE TABLE dw.LinkedTransactions
(
[Column1] nvarchar (50) NULL,
[Column2] datetime NULL,
[Column3] nvarchar (4000) NULL,
[RFQSK] nvarchar (1000) NULL,
[QOTSK] nvarchar (1000) NULL,
[ORDSK] nvarchar (1000) NULL,
[POCSK] nvarchar (1000) NULL,
[Column4] datetime2 NULL,
[Column5] nvarchar (4000) NULL
WITH
(
DISTRIBUTION = ROUND_ROBIN,
CLUSTERED COLUMNSTORE INDEX
)
GO
Bhargava-MSFT 31,031 Reputation points Microsoft Employee

2023-01-09T22:54:34.89+00:00

Hello @Clifford Gentiles ,
Thank you for the details. You can send an email to AzCommunity@microsoft.com. Also, please include the below details.
Email subject: <Attn - Bhargava : Microsoft Q&A Thread title>
Thread URL: <Microsoft Q&A Thread>
Looking forward to your reply.

Regards,
BhargavaGunnam-MSFT
Clifford Gentiles 21 Reputation points

2023-01-11T06:25:15.92+00:00

Hi @Bhargava-MSFT

Good day, I've just sent an email for better conversation. Thanks
Tom Janmaat 0 Reputation points

2023-01-13T15:25:11+00:00

Is there more information on the resolution of this issue?

I think I'm also seeing upsert acting as an insert in my Data Flow, so am interested to hear what was the exact issue in this case.
Bhargava-MSFT 31,031 Reputation points Microsoft Employee

2023-01-14T00:28:36.92+00:00

Hello @Tom Janmaat ,

Thanks for the reply. Currently, I am waiting for the input parquet file from Clifford to repro the issue from my end before taking this with the PG team. If you see the same behavior, I would suggest opening a new thread by providing more details so that someone from my team will look into it.

Have a great weekend!
Ali Keçe 0 Reputation points

2024-03-26T10:59:42.7866667+00:00

Hi,

I have exactly same issue and i have only 1 Key column (cant be null). Wait a minute, we cant say if your source data have duplicate data u will have in synapse. This is not good :)

We are using upsert to avoid duplicate records, abovusly. Thats why upsert exsist :)

So what do u suggest? Stage?

1 answer

Rejas, Jose 20 Reputation points

2023-02-28T15:20:02.89+00:00

Hi everyone.

I think I know what's going on here.

I have the same problem in my project, and I'm going crazy, but I have a clue:

When I have duplicates at the end, but NOT at the source, it is because of the key Columns: one of them may be null, so the Copy Data activity does not concatenate the Key columns correctly. This could be very common, for example, in spark sql.

Please, let me know if this is a valid solution for you
Please sign in to rate this answer.

1 person found this answer helpful.
Bhargava-MSFT 31,031 Reputation points Microsoft Employee

2023-02-28T18:45:14.18+00:00

Hi Rejas, Jose

Thank you for sharing the resolution, as it helps the community members who are looking for answers to similar questions.

Rejas, Jose 20 Reputation points

2023-03-01T07:27:31.15+00:00

You are welcome, @Bhargava-MSFT

I can confirm that's the problem. If you have multiple columns in Key Column, you have to ensure none of them is null because the upsert method doesn't work in this case.

I can't share snapshot of this test by privacy. But I followed the next steps:

(transact sql) create table sch_anl.t1(c1 varchar(10) NULL, c2 varchar(10) NULL, c3 varchar(10) NULL,c4 varchar(10)) WITH (DISTRIBUTION = ROUND_ROBIN)

(transact sql) insert into sch_anl.t1(c1,c2,c3,c4) values ('row1','row1','row1','row1')

(transact sql) insert into sch_anl.t1(c1,c2,c3,c4) values (null,'row1','row1','row1')

Now I configure my Copy Data with upsert, key columns: c1,c2.

After that I insert by Copy Data the row ('row1','row1','row1','row1'): no duplicates generated

But, if I insert the row (null,'row1','row1','row1') again by copy data, it generates duplicates because of null value in field c1.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Azure Data Factory Upsert not working properly

1 answer

Your answer