Forum Discussion

D4zk's avatar
D4zk
Frequent Visitor
3 months ago
Solved

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

 

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.

data engineering
  • v-saisrao-msft's avatar
    v-saisrao-msft
    3 months ago

    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

5 Replies

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Community Support
    3 months ago

    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.

     

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Community Support
    3 months ago

    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's avatar
      D4zk
      Frequent Visitor
      3 months ago

      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's avatar
        v-saisrao-msft
        Community Support
        3 months ago

        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's avatar
    v-saisrao-msft
    Community Support
    3 months ago

    Hi D4zk,

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

     

    Thank you.