I cannot find any refence about how you can close (end effectivity) of a previous record without updating it in effectivity satellite table. I could not find any example or explanation in Patrick Cuba’s book “The Data Vault Guru” eighter.
Are we talking about virtual end date?
1 Like
I first evaluated and then decided to use this method for effectivity SATs in my first DV. If this method is flawed then hopefully someone will tell me here! However so far it works very well. Here is my raw vault table rv.SAT_ACCOUNTS:
HUB_ACCOUNT_KEY
SAT_Load_DTS
SAT_Rec_SRC
RECORD_HASH (I dislike the name HASH_DIFF as something is not a difference until you compare it)
:::
::: my data columns
:::
EFFECTIVE_FROM (date)
I only ever append to this table, and I ensure that EFFECTIVE_FROM is never repeated for the same value of HUB_ACCOUNT_KEY (unique on HUB_ACCOUNT_KEY, EFFECTIVE_FROM). In some tables EFFECTIVE_FROM can be a timestamp if there are multiple changes per day, but not in this example.
Here is my business vault view, I call it bv.SAT_ACCOUNTS_VW
::: all the above columns, plus,
coalesce(lead(EFFECTIVE_FROM)
over (partition by HUB_ACCOUNT_KEY
order by EFFECTIVE_FROM),
cast(‘9999-12-31’ as DATE))
as EFFECTIVE_TO
Whenever joining to this view I will include:
where …
and REQUIRED_DATE >= EFFECTIVE_FROM
and REQUIRED_DATE < EFFECTIVE_TO
Note: I cannot use <= as it will cause rows to be selected twice. I have had no performance issues at all with tables of >100M rows. Note: this is in BigQuery.
Updates to the SAT are never required, and historical rows (earlier EFFECTIVE_FROM) can also easily be added.
1 Like
Look harder
6.3 Effectivity Satellites
7.3 Loading patterns — turn to the page where I load effectivity satellites
10.3 How to query Effectivity satellites
All in the book you claimed to have bought
1 Like
The answer I am looking for is how “insert only DV 2.0” claim backed up by example of loading effectivity satellites.
I am looking at page 654 (Kindle edition) "Loading EFFECTIVITY SATELLITES (efs staging, sat loader).
I cannot find my answer. In this section you are talking about efs staging).
Pseudo code 1. where dv_rank = 1 and dv_ends=‘9999-12-31’. I am guessing the reason you are fetching rank = 1 of dv_loadts desc is because you will have multiple versions of open-ended records for a given relationship. So, I think you are not closing end date but, creating another one. With this approach insert only possible. If I am correct, then it would be helpful to explain it clearly in your book. Because, it is unconventional approach for temporal data management therefore deserve attention. By the way I like you book I do recommend to anyone whom serios about DV 2,0 education.
Thank you.
2 Likes
Well part of EFS is the second-level staging — why couldn’t you manipulate how you stage to solve your issue?
i.e. if the EFS is about driver-key and non-driver-key relationship then why couldn’t you “generate” a closing record by utilising the Zero key concept?
The book covers the majority of use cases — not all — that’s not practical.
1 Like
Your solution is a valid method for hub’s context history. But the question asked is about the activity of the link.
Since the link hashkey has changed by time, it seems that using only the EFFECTIVE_FROM field will not work in the link table. We also need information about when the relevant hashkey is closed.