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.

<style> </style>

$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

Start by tracing the CDCCopy and MergeData activities and reviewing how the MERGE statement handles DML_Action values for the shown RECID sequence. Compare the incremental path with FullExport_SQL and verify that a DELETE followed by an INSERT leaves the target SQL database synchronized with the source.