# Insert Only Effectivity Satellites **URL:** \ **Category:** Data Vault 2.0\ **Created:** [July 11, 2023, 8:25am UTC](https://forum.data-community.org/t/insert-only-effectivity-satellites/902 "2023-07-11T08:25:03Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![jmamedov](https://avatars.discourse-cdn.com/v4/letter/j/e274bd/32.png) [@jmamedov](https://forum.data-community.org/u/jmamedov)\ **Post date:** [July 11, 2023, 8:25am UTC](https://forum.data-community.org/t/insert-only-effectivity-satellites/902/1 "2023-07-11T08:25:03Z")
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? ---
**Author:** ![faulknerp](https://avatars.discourse-cdn.com/v4/letter/f/7bcc69/32.png) [@faulknerp](https://forum.data-community.org/u/faulknerp)\ **Post date:** [July 11, 2023, 3:06pm UTC](https://forum.data-community.org/t/insert-only-effectivity-satellites/902/2 "2023-07-11T15:06:42Z")
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. ---
**Author:** ![patrickcuba](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.data-community.org/patrickcuba/32/19_2.png) [@patrickcuba](https://forum.data-community.org/u/patrickcuba)\ **Post date:** [July 11, 2023, 11:23pm UTC](https://forum.data-community.org/t/insert-only-effectivity-satellites/902/3 "2023-07-11T23:23:13Z")
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 ---
**Author:** ![jmamedov](https://avatars.discourse-cdn.com/v4/letter/j/e274bd/32.png) [@jmamedov](https://forum.data-community.org/u/jmamedov)\ **Post date:** [July 12, 2023, 1:44am UTC](https://forum.data-community.org/t/insert-only-effectivity-satellites/902/4 "2023-07-12T01:44:18Z")
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. ---
**Author:** ![patrickcuba](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.data-community.org/patrickcuba/32/19_2.png) [@patrickcuba](https://forum.data-community.org/u/patrickcuba)\ **Post date:** [July 12, 2023, 1:54am UTC](https://forum.data-community.org/t/insert-only-effectivity-satellites/902/5 "2023-07-12T01:54:56Z")
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. ---
**Author:** ![kerabanaga](https://avatars.discourse-cdn.com/v4/letter/k/919ad9/32.png) [@kerabanaga](https://forum.data-community.org/u/kerabanaga)\ **Post date:** [February 28, 2024, 9:25am UTC](https://forum.data-community.org/t/insert-only-effectivity-satellites/902/6 "2024-02-28T09:25:07Z")
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.