How to update and delete a row using Azure Data Factory Change Data Capture

jigsm 236 Reputation points
2024-04-28T17:40:36.5966667+00:00

I am exploring Azure Data Factory CDC feature, and I am trying to perform the CDC from one SQL table to another SQL table, also the SQL table in the source has a primary key.

Whenever a new row is added to the source table, it gets added to the destination SQL table.
However, when an existing row is modified/updated in the source table, it adds a new row in the destination SQL table, instead of updating it.

Is there a way to achieve update and delete with Azure Data Factory CDC?
In the answer section of this question (https://stackoverflow.com/questions/75233423/how-are-changes-count-calculated-in-azure-data-factory-change-data-capture), the author says to specify a Primary Key, however in the current ADF CDC UI, I am unable to see the Keys column.

Please let me know if the update and delete is possible using Azure Data Factory CDC.
User's image

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,196 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Amira Bedhiafi 20,176 Reputation points
    2024-04-28T19:12:17.93+00:00

    If your SQL source doesn't have SQL Server CDC with net_changed enabled or doesn't have any time-based incremental columns, then maybe the columns the tables in your source will be unavailable for selection.


  2. ShaikMaheer-MSFT 38,401 Reputation points Microsoft Employee
    2024-04-29T16:55:59.4433333+00:00

    Hi jigsm,

    Thank you for posting query in Microsoft Q&A Platform.

    Kindly check my video on CDC and fellow same steps. That helps to avoid this error. Below is the video link. CDC (change data capture) Resource in Azure Data Factory

    Hope this helps. Please let me know how it goes.


    Please consider hitting Accept Answer button. Accepted answers help community as well.


  3. Pinaki Ghatak 3,265 Reputation points Microsoft Employee
    2024-06-03T09:01:35.7833333+00:00

    Hello @jigsm

    Yes, it is possible to achieve update and delete with Azure Data Factory CDC. To update the existing row in the destination SQL table, you need to specify the primary key of the table in the column mapping page of the copy activity. To specify the primary key, you can follow these steps:

    1. In the copy activity, go to the column mapping page.
    2. Select the checkbox for the primary key column(s) in the sink table.
    3. In the Mapping type column, select Update Key for the primary key column(s). By doing this, when an existing row is updated in the source table, it will update the corresponding row in the destination SQL table instead of adding a new row.

    Regarding the missing Keys column in the ADF CDC UI, it is possible that the UI has been updated since the answer was posted on Stack Overflow. However, you can still specify the primary key as mentioned above.

    For delete operations, the CDC feature in Azure Data Factory currently supports delete operations for Azure SQL Database and Delta Lake sink types.

    To achieve this, in the column mapping page, you need to select the keys column that can be used to determine if a row from the source matches a row from the sink.


    I hope that this response has addressed your query and helped you overcome your challenges. If so, please mark this response as Answered. This will not only acknowledge our efforts, but also assist other community members who may be looking for similar solutions.

    0 comments No comments