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?

Please advise.


I suggest you use a rts instead of a effsat. Then you can also extract multiple start- and enddates for a bk.

Hi @AHenning . I am not following you. Why would Record Tracking Satellite be better for this?

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.

Sry, I dont know if dbtvault builds rts, I guess it does.

@AHenning . No worries. Do you have SQL loading pattern for a RTS for tracking relationships? I couldn’t find any in @patrickcuba 's book.

Sure, i will post it when I am back at the computer, probbaly tomorrow.

1 Like

Thanks @AHenning .

@alex.higgs, do you know if there is dbtvault macro for RTS?

Hello Saqib, been a while

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.

1 Like

Thanks @patrickcuba . I will look for the code in the book. The Kindle search functionality is not that good :face_with_diagonal_mouth:

1 Like

I have sent you a sample on LinkedIn.

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.

EDIT: We do have XTS though, which is an implementation of Patrick’s take on RTS, write up at the following link: Data Vault’s XTS pattern on Snowflake | by Patrick Cuba | Snowflake | Medium

Well… I didn’t discard/replace RTS — I just made a new one, to deal with out-of-sequence loads described here:

1 Like

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,

Bridges and PITs in AutomateDV need a re-work. The versions in release at the moment were never supposed to be the long-term implementation.

There’s lots of improvements we want to make, removing some of the restrictions being one of them.

If you have any thoughts, ideas or requests for changes, please submit them on our GitHub or reach out to me on Slack :slight_smile:

Some people on other threads mention forking this code already - (Patrick and Frank at least) - did you reach out to these?

I haven’t gotten around to creating my own Bridge macro yet, unfortunately. Too much time and effort spent on other stuff.

I really want to, because my Information Mart queries are now way more complex than they have to be. If only there were 120 hours in a work week ^^