SynapseToSQL_ADF - IncrementalExport_SQL

There are instances when the _cdc files have a DELETE operation followed by an INSERT operation on the identical RECID record. It appears to be when the Excel add-in and/or data management is used to extract and subsequently update records. The following is an example showing the contents of one such _cdc record along with the relevant fields.

$FileName | LastProcessedChange_DateTime | DataLakeModified_DateTime | Start_LSN | End_LSN | DML_Action | Seq_Val | Update_Mask | RECID -- | -- | -- | -- | -- | -- | -- | -- | -- 0X0001AC1800000690001C_1_0X0001AC18000006400024 | 2024-04-15 13:55:54.4900000 | 2024-04-15 13:56:55.9651752 | 0X0001AC1800000690001C | | DELETE | 0X0001AC18000006400024 | 0X0FFF | 5637152336 0X0001AC1800000690001C_1_0X0001AC18000006400024 | 2024-04-15 13:55:54.4900000 | 2024-04-15 13:56:55.9651864 | 0X0001AC1800000690001C | | INSERT | 0X0001AC18000006400024 | 0X0FFF | 5637152336

In these cases, when the CDCCopy activity is executed, it grabs the INSERT operation based on the 3 fields Start_LSN, Seq_Val and DataLakeModified_DataTime, since the INSERT was performed after the DELETE.

However, when the MergeData activity execute, the MERGE statement finds a match with the DML_Action = 'INSERT' which is not an option in the MERGE statement. Therefore nothing is performed and the target SQL database is now out of sync with the source.

Possible solution is to change the existing MERGE statement to delete the existing record and then an additional MERGE statement to handle the INSERT.

MERGE @TargetTable T USING @CDCTable S ON T.RECID = S.RECID WHEN MATCHED AND S.DML_Action = 'AFTER_UPDATE' THEN UPDATE SET @UpdateColumns WHEN NOT MATCHED BY TARGET AND S.DML_Action <> 'DELETE' THEN INSERT (@Columns) VALUES (@Columns) WHEN MATCHED AND S.DML_Action = 'DELETE' OR S.DML_Action = 'INSERT' THEN DELETE;

MERGE @TargetTable T USING @CDCTable S ON T.RECID = S.RECID WHEN NOT MATCHED BY TARGET AND S.DML_Action = 'INSERT' THEN INSERT (@Columns) VALUES (@Columns)

Is this a known issue or am I chasing something that should not be happening in the first place? The only other alternative is to perform a FullExport_SQL on all of the tables, but that takes time which I may not have.

Thanks for any input on this.

Seth

microsoft / Dynamics-365-FastTrack-Implementation-Assets

SynapseToSQL_ADF - IncrementalExport_SQL #324