Forum Discussion

afernand0's avatar
afernand0
Frequent Visitor
11 months ago

Cannot merge data from a CDC enabled table

Hi,

 

I'm trying to upsert data from a CDC enabled table in an Azure SQL database table to a Fabric database using a copy job. As you can see in the screenshot below, I have selected Merge as the update method. However, the column mapping is disabled when CDC is enabled - which is expected. 

There's a primary key in the dbo.customer table in the source. However, copy job isn't recognizing it nor allowiing me to specify it. Is this a known limitation or a bug?

 

Copy job
Need Help

7 Replies

  • spaceman127's avatar
    spaceman127
    Super User
    10 months ago

    afernand0 ,

    The problems should have been fixed by now. I just checked again quickly, and it works.
    Please try again.

     

    tayloramy,

    Merge is now supported for Fabric Data Warehouse, in case you didn't know. However, it is still in preview mode, but this has only been the case for a short time.

     

    Best regards 

    • tayloramy's avatar
      tayloramy
      Super User
      10 months ago

      Thanks spaceman127, I saw that on the september fabric update. I am quite excited at the new features that are coming out.

      If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, please mark this post as the solution.

       

  • tayloramy's avatar
    tayloramy
    Super User
    11 months ago

    Hi afernand0, 

     

    It’s expected behavior in CDC Copy Job that the column mapping UI is disabled. CDC auto-derives the schema and keys. Per the docs: when CDC tables are selected, "column mapping can’t be configured", and the Merge update method "matches the required key columns to the primary key defined in the source by default". Also note a key limitation: composite primary keys aren’t supported for CDC merge yet (Microsoft Learn: CDC in Copy job (Preview)).

    So if your dbo.customer has:

    • a single-column primary key: CDC should auto-recognize it.
    • a composite PK (multi-column): CDC Merge won’t work today (limitation).
    • only a unique index (no actual PRIMARY KEY constraint): CDC won’t detect it as the key.

    Quick check on the source table:

    SELECT k.name AS pk_name, c.name AS pk_column FROM sys.key_constraints k JOIN sys.index_columns ic ON ic.object_id = k.parent_object_id AND ic.index_id = k.unique_index_id JOIN sys.columns c ON c.object_id = k.parent_object_id AND c.column_id = ic.column_id WHERE k.parent_object_id = OBJECT_ID('dbo.customer') AND k.type = 'PK';

    If you do have a single-column PK and it still isn’t detected, that’s likely a product issue-capture the Job/Run IDs and open a ticket via the Fabric Help pane (Create a support ticket).

     

    Workarounds today:

    • Switch this table to watermark-based incremental (not CDC). You'll be able to pick the incremental column and configure mapping/keys (Copy job overview).
    • Or stage to a destination and run your own upsert logic (e.g., stored proc or PySpark). Be aware that T-SQL MERGE isn’t supported in Fabric Warehouse yet (T-SQL surface area limitations).

    If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, please mark this post as the solution.

    • afernand0's avatar
      afernand0
      Frequent Visitor
      10 months ago

      Thanks for the reply. The query above returns the following. I have opened a support ticket as you suggested.

       

      pk_namepk_column
      PK_Customers customer_id
      • v-prasare's avatar
        v-prasare
        Community Support
        10 months ago

        Thanks for your response. Please do keep posted updates here, this will useful for community members with similar issues.

  • v-prasare's avatar
    v-prasare
    Community Support
    11 months ago

    Hi afernand0,

    We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.


    tayloramy,Thanks for your prompt response

     

     

    Thank you for your patience and look forward to hearing from you.
    Best Regards,
    Prashanth Are
    MS Fabric community support

  • v-prasare's avatar
    v-prasare
    Community Support
    10 months ago


    We are following up once again regarding your query. Could you please confirm if the issue has been resolved through the support ticket with Microsoft?
    If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community. If we don’t hear back, we’ll go ahead and close this thread.
    Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

    Thank you for your understanding and participation.