Replicate header__timestamp Clarification

[NewbieQlik]

I am replicating a table from Oracle source onto snowflake target where I have enabled the store changes. As part of replicate task I have added a global transformation to get source oracle record commit timestamp using $AR_H_COMMIT_TIMESTAMP. When I tried to process few CDC changes where I have an INSERT followed by "UPDATE" and when that happened $AR_H_COMMIT_TIMESTAMP shows same value for both insert and update and for some reason header__timestamp shows 2022-04-20 20:30:04.000 for insert (EST time) and 2022-04-20 20:30:05.000 for update.

My understanding was replicate internally uses $AR_H_COMMIT_TIMESTAMP to populate header__timestamp column in __ct table, so in that case I should see the same value for both insert and update. Is this an expected behavior?

header__operation/header__timestamp/Source_Record_Commit_TS

INSERT/2022-04-20 20:30:04.000/2022-04-20 16:30:05.000
UPDATE/2022-04-20 20:30:05.000/2022-04-20 16:30:05.000

[shashi_holla · Accepted solution]

AR_H_COMMIT_TIMESTAMP holds the datetime value when the record was committed at the source. Since insert and update were in succession and not at the same time, it's normal we see that one second difference between the two. if you notice the value for Source_Record_Commit_TS it's consistent with when the record was last committed. Hope this makes sense.

Thank you, 

[Heinvandenheuvel · Accepted solution]

Hello  @NewbieQlik  (why hide, why not at least provide a first name to address you properly !?) 

You did not explicitly indicated whether the Insert and Update were part of a single transaction committed at the same time. Judging by the example they were.

>> My understanding was replicate internally uses $AR_H_COMMIT_TIMESTAMP to populate header__timestamp column in __ct table, 

What led you to believe that? The documentation explicitly indicates: "[header__]timestamp  - The original change UTC timestamp"

By contrast for "[header__]change_seq" it indicates: "The time part usually refers to the commit time of the transaction that includes the change record."

>> I have added a global transformation to get source oracle record commit timestamp using $AR_H_COMMIT_TIMESTAMP. 

That may well be the best approach, based on the exact application requirements.

Hein.

[NewbieQlik]

Thanks for the clarification