I am using Data vault 2.0 model. Here we are not supposed to use UPDATE in a table. Normally in RDBMS, we implement SCD-2 using UPDATE & INSERT strategy. But in my case, I can use only INSER strategy. My Source is Kafka input and its loaded into Snowflake (as avro format) and I'm flattening and loading the same into another table in RDBMS format.
My question is: Can anyone please help me to implement SCD type-2 logic without using UPDATE. Only with INSERT strategy I have to do this.
I should keep the history also. For example, if I get rec-1 in interval-a and in interval-b, I got 2 updates for rec-1, then I need to load all the three records in my target pointing the last record came in will be the latest one.
Your satellite key needs to consist out of the Hash Key + the Load Date Timestamp. Each individual version will then have a new primary key and thus can be inserted into the satellite. By querying the latest timestamp, you will get the latest version.
You can find a sample script here: https://simplesqlbi.home.blog/2019/06/29/part-3-data-vault-for-beginners/