How to differentiate Updated and Deleted data in Adf CDC?

Franklin John 25

Currently, we integrated Azure data factory CDC, Our source system is the SQL server destination is the storage account.

we updated and deleted data in the source system. In both cases, it created files in a storage account but both the files look similar,

so how to differentiate which one is deleted and which one is updated? Even if opened both look similar. Please advise as to how to handle this

User's image

2 answers

Sina Salam 11,206 Reputation points

2023-08-16T05:55:02.89+00:00

Hello @Franklin John ,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

To understand your question, you would like to know how to differentiate which data is deleted and which data is updated in your ADF.

The cause is on your second screenshot. "The CDC is not running!"

When you enable native change data capture CDC or auto incremental extraction options in ADF mapping data flow, you will be able to identify the differences between the deleted and updated data.

Native change data capture is always recommended as the simplest way for you to get change data.

You can read more about change data capture in the following link.:

Change data capture in Azure Data Factory and Azure Synapse Analytics.

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

Please remember to "Accept Answer" if answer helped, so that others in the community facing similar issues can easily find the solution.

Best Regards,

Sina Salam
Please sign in to rate this answer.
Franklin John 25 Reputation points

2023-08-16T06:52:22.78+00:00

Hi @Sina Salam

CDC was running properly. For taking a screenshot I stopped it.

The Url that you shared, We were using previously. Now we migrated to the Change data capture (preview) pipeline.

How to differentiate in this new preview technology?

Sina Salam 11,206 Reputation points

2023-08-16T19:31:40.48+00:00

Hi @Franklin John

Thank you for more explanations.

To differentiate deleted and updated data using Azure CDC preview, you can use the following methods:

If you use the Change Data Capture factory resource in Azure Data Factory, you can specify the operation type (insert, update, delete) in the source settings. This will filter the changed data based on the operation type. You can also use the column __$operation in the change table to identify the operation type. The values for this column are 1 for delete, 2 for insert, and 4 for update.

If you use the native change data capture in mapping data flow, you can use the column __$operation in the change table to identify the operation type. The values for this column are 1 for delete, 2 for insert, and 3 for update. You can also use the Alter Row transformation to add a new column that indicates the operation type based on the value of __$operation.

NOTICE THE VALUE.

Also, you can use the below links for a suitable configuration:

Configure and Query Change Data Capture (CDC) data for Azure SQL Database.

Get started with change data capture in the analytical store for Azure Cosmos DB (Preview).

Track and Record Data Changes with Change Data Capture (CDC) in Azure.

NOTE: The above should be all you need, and I strongly believe it will work, except you are using unrelated database as mentioned.

I hope this helps you understand how to differentiate deleted and updated data using Azure CDC preview.

Best Regards,

Sina Salam

Franklin John 25 Reputation points

2023-08-18T02:38:06.7766667+00:00

Hi @Sina Salam

We followed the same steps but the "__$operation" column is not Available in CDC preview mapping. But it's already available in Sql Database. ADF CDC preview shows only main columns not showing ____operation,____start_lsn,..etc columns. screenshot is attached

Franklin John 25 Reputation points

2023-08-29T04:17:20.54+00:00

Hi @Sina Salam

Any update?
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Daniel G Perico S 0 Reputation points

2024-04-09T00:55:35.5933333+00:00

Right now I'm having the same problem. Performing, INSERT or DELETE operations creates similar files on the blob storage so I can't know which operatios was performed on each row 🥲
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

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

Share via

How to differentiate Updated and Deleted data in Adf CDC?

2 answers

Your answer