Is anyone using the DBT Effectivity SAT macro? I want to understand the pattern for capturing the START_DATE and END_DATE when the source doesn’t provide it. We have ORDER_HK as the driving key. And CUSTOMER_HK as the Secondary Foreign Key. The CUSTOMER assignment to an Order can change over time in the source system. The source system only provides the latest assignment, and not when it changed. What is the best way to calculate the the START_DATE and END_DATE?
Hello Saqib!
The reason for choosing a rts instead of a an effsat is that rts captures if a key or combinations of keys was delivered or not delivered between two datasets. An effsat requires that the source system tells you the effectivity of the key or combination of keys. Please note that the definitions of an effsat sometimes is defined just like an rts. My point is that you should use the satellite that is not dependent on a source system telling the dv about the start and end of a relation. So if you use the rts, it will be very esay to calculate start- and end date. Sorry if a made things fuzzier than it has to be. Kind regards.
@AHenning . Ah I see. I have not worked with Record Tracking Satellite. Is there a dbtvault macro for loading Record Tracking Satellite with relationships? Thanks.
You can use either EFS or RTS, EFS can be more complex and you can rely on the applied date to track EFS driver key changes between driver-key and non-driver key. There’s definitely code in the book for that, I just hope dbtvault’s interpretation was correct.
RTS I don’t think is implemented as a satellite explicitly, but it’s loading pattern is so easy maybe that is why they haven’t!
RTS will be based on applied date and capture each time you see a single key (if the RTS hangs off a hub) or a relationship (if it hangs off a link). Perhaps to define an RTS on dbtvault you simply define the applied date as the payload!
Either EFS or RTS will do, RTS won’t have an end-date and capture every instance of the relationship (in your case it would resemble a non-unique link table - except the applied date will be different each time) but EFS will only show the changes and includes a physical end-date
Hope that helps.
By the the load code for RTS IS in the book**** — I think it was chapter 7 where we look at loading patterns along with every other satellite table in data vault, section 7.3 Loading Data Vault look for “Loading Record Tracking Satellites”, might be page 325 — you’ll notice I used the keyword “sat loader” in brackets to denote the load code is the same, just the payload is different.
Hi. We do not yet have RTS or STS in dbtvault. We started developing them, however we quickly found conflicting standards and guidance between the DV 2.0 book, Patrick Cuba (@patrickcuba) and other sources.
We reached out to DVA for an up-to-date standard for this as they had noted they were planning to release the code as reference, but have not received a response yet.
We hope to have it included in dbtvault in time, but we do not have an ETA yet.
Following up for this @alex.higgs it seems that the bridge logic in automate_dv is dependent on having an eff_sat on each link. Is this true? And what’s the background behind the end_date logic as it seems a weird dependency,
