We had developed the CDC flow for source tables in MS Sql server to target as snowflake db. we have an issue with how the user do operations on the source tables.
there is a scenario, that user will first update, and delete and at last insert. Here the primary key column is same in both in delete and insert operation.
For example: table :EMPTABLE, columns : EMPID(primary key),EMPNAME
original value in EMPTABLE is : 4, Nick
Please see the below sequence of operations performed on EMPTABLE.
Update: 4, John
Delete: the id with 4 is deleted
Insert: 4, Johnny
so when we are running the job, it is first inserting, updating, and deleting . so the record which is
"4, johnny'
which need to be inserted, is deleting in the end.
actually, if the user enters a new EMPID after deletion of the old, the whole CDC flow will work. but in real time, user will enter the the same id again for the new record.
we have 'TALEND_CDC_CREATION_DATE' and 'TALEND_CDC_TYPE_OPERATION' field for the table 'EMPTABLE
' (that is added to CDC).
is there any way if we can insert, delete, and update according to the 'TALEND_CDC_CREATION_DATE'.
if we figure out this, then i hope we can solve the problem. Any inputs will be helpful for me.
Can you share a screenshot of your job? I think you need to use a tFlowToIterate after tMSSQLCDC to iterate each operation, this will ensure the execution order of each operation you have done.
