Microsoft Fabric Copy Job – SCD Type 2 (SCD2) Issues

[D4zk]

During source data changes, the Copy Job does not manage correctly the SCD2 control colums:

copyjob_valid_from, copyjob_valid_to, copyjob_is_current. As shown in the results, the first two rows correspond to the initial full load. After updating the source data and running the load again, the following two rows were generated. In the delete scenario, the Copy Job does not update the copyjob_is_current column to false after execution.

After the second run, the job encounters the issue described below. This appears to be caused by one primary key being associated with two rows:

Have you experienced this issue, and is there any known solution or workaround to resolve it?

One additional point regarding SCD Type 2: the “Edit mapping” option should not be used. If it is selected, the destination table will not be created with the three required SCD2 columns. And of course, the second run fails due to the missing SCD2 columns.
Thank you.

[v-saisrao-msft]

Hi @D4zk,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

Thank you.

[v-saisrao-msft]

Hi @D4zk,

Have you had a chance to review the solution we shared earlier? If the issue persists, feel free to reply so we can help further.

Thank you.

[D4zk]

Thank you for your response. I have verified all configurations and everything is correct. The issue is not related to edit mapping. When I stated that it should not be used, it was because I already understand what is happening. If I use MERGE instead of SCD Type 2, the flow executes successfully. This indicates that the root cause is related to the SCD Type 2 feature.

[v-saisrao-msft · Accepted solution]

Hi @D4zk,

Since MERGE (SCD Type 1) is functioning while SCD Type 2 is not, this highlights the documented difference: SCD2 uses CDC-based historical tracking, including expiring rows and soft deletes, whereas MERGE updates only current data. SCD2 depends on CDC to handle inserts, updates, and deletes during incremental runs, so its behavior varies based on how those changes are applied. As your configuration has been validated, you can keep using MERGE for stability or consider submitting a support request for SCD2-specific issues.

Change data capture (CDC) in Copy Job - Microsoft Fabric | Microsoft Learn

[v-saisrao-msft]

Hi @D4zk,

SCD Type 2 in Copy Job is currently available as a preview feature and depends on supported configurations. Make sure that Edit mapping is not enabled, because SCD Type 2 does not work when the destination schema is already created. Also, check that Change Data Capture is set up correctly, since inserts, updates, and deletes are handled through CDC during incremental runs. Because Copy Job uses these changes for incremental replication, any configuration issues can affect how updates and deletes are processed.

Change data capture (CDC) in Copy Job - Microsoft Fabric | Microsoft Learn

Connectors for Copy Job - Microsoft Fabric | Microsoft Learn

Slowly changing dimension type 2 - Microsoft Fabric | Microsoft Learn

Thank you.