Hi Team,
Can anyone please help me in the following issue which I'm facing with "Snowflake - SCD2" snap.
As per my requirement, I have to implement SCD Type2, to achieve this I'm using "Snowflake - SCD2" snap in SnapLogic pipeline. In this snap I'm using Timestamp field (LAST_UPDT_DATE and datatype used TIMESTAMP_NTZ(9)) for "Auto Historization Query" section.
I'm using the following derivation for Timestamp field (LAST_UPDT_DATE): "Date.now().toLocaleDateTimeString({"timeZone":"US/Eastern","format":"yyyy-MM-dd hh:mm:ss"})"
Here is how I set the columns in SCD2 snap.
For Initial (or Full) load, when I set the sort order to "Ascending Order" for this Auto Historization field, the data loaded properly (its working fine for "Descending Order" too for initial load).
Now, If I go for the incremental load in the same day (the same day when initial load happened), its not loading the data properly if we set the sort order to "Ascending" where as this scenario working fine if we set the sort order to "Descending".
If I go for the next day incremental run with the sort order to "Descending", then its not loading the data properly. If we set the sort order to "Ascending", the data load is happening properly.
The other observation is, instead of going with the Timestamp field for Auto Historization Query, If I use the Date field, the same day data will be overwritten with the latest run data instead of having all the entries, we will have only one record entry for that Auto Historization date.
Can anyone please help me in understanding this and resolving the issue which I'm facing?
Thanks in advance.
