We are replicating data from Oracle db to S3 then to snowflake, facing an issue that is when updates occurs in the db, sometimes the result .avrocf file is returning a consolidated row with the io_type parameter with the value "UNIFIED_UPDATE_VALUE" and a certain timestamp; but when another updates occurs, gg sends two records, one Delete and one Insert, the delete record is identical to the previous state of the record and the insert has the new updated values, but the problem is that this two records have the same timestamp of operation.
In the log trail in gg, a previous update record has the operation type as "UNIFIED_UPDATE_VAL" (this i think should be configurable, because basically is what we need) whereas the new update has two records with operation type as "DELETE" and the other as "INSERT"
In snowflake to deal with duplicated PK, we have a ROW_NUMBER after the pipe inserts the records on a staging table:
ROW_NUMBER() OVER(PARTITION BY [PK] ORDER BY [timestamp col] DESC)
The problem is, considering that both rows of the Delete/Insert couple have the same timestamp, the row number in occasions, is giving us the wrong result.
We could add another order by condition with something like a case in which the Insert is always the selected one but i would love to have the chance to set this in the goldengate itself and not in Snowflake,
Any thoughts?
Thanks