We have a source table that links a member to an address, the table has it’s own effective dates and also has a type code as a member could have different addresses for correspondence, work, billing etc. We have created a link table and an effectivity satellite to track the history as well as a standard sat to keep all of the attributes.
We have the data already staged in Snowflake, however, it was all bulk loaded at the same time so all has the same load date so my question is how can we bulk load the effectivity sat on day 0 and maintain the correct order of the historical addresses that a member has had?
I know we are not supposed to use source effectivity dates as effectivity dates in the sat so I’m struggling to see how we can maintain the correct order…unless…our implementation of ‘applied date’ is incorrect. We have used the staging load date as the applied date and the load date for our DV objects is CURRENT_TIMESTAMP.
For clarity, we have used the pattern from Patrick Cuba’s book (with a coupld of tweaks)