Azure Mapping Data Flow why CDC does not work with composite key?

Question

Hello,

Description:

As a source I have SAP CDC dataset, as a target - Azure Dedicated SQL Pool.

Created mapping data flow should take data from SAP BW and merge changes to the table in Dedicated SQL Pool.

Loading approach - fullAndIncrementalLoad.

Initial run works fine, all data is loaded as expected, but with the next run the problem appears: from SAP BW is taken delta that has to be merged but in fact it is appended to the table in Dedicated SQL Pool. It causes wrong check sum and duplicates on composite key level in the target table.

Assumption: one attribute in composite key contain nulls

Questions:

1. What are the reasons for the described problem?
2. If my assumption is correct, how to create an expression in order to override null problem in the composite key?

For better understanding, please, find below screenshots:

• target (sink) settings:
  
• parameter $key_columns is provided via ADF pipeline and looks as following:
  ["column1", "column2", "column3", "column4","column5"]
• "column4" and "column5" might contain null values

Thank you very much in advance!

Answer 1

Hi Tamashevich, Tatsiana

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

The problem you are experiencing with the delta load not being merged correctly into the target table in Azure Dedicated SQL Pool could be caused by a number of factors, including:

Incorrect mapping of the source and target columns in the data flow. Make sure that the columns in the source and target tables are correctly mapped in the data flow.

Incorrect configuration of the delta load settings in the data flow. Make sure that the delta load settings are correctly configured to identify the changes in the source data and merge them into the target table.

Issues with the composite key in the target table. If one of the attributes in the composite key contains null values, it could cause duplicates in the target table.

If your assumption may correct and the problem may caused by null values in the composite key, you can create an expression in the data flow to override the null values. Here is an example expression that you can use:

iif(isNull(column1), 'default_value', column1) + iif(isNull(column2), 'default_value', column2) + iif(isNull(column3), 'default_value', column3)

Please see if above helps. Thank you.