Data Vault Anti-pattern: Using Effectivity Satellites as SCD2


In Data Vault 2.0 Effectivity Satellites are artifacts that are exclusively used to Track the temporal relevance of a relationship based on a Driving Key. As such, they hang from a Link Table.


Effectivity Satellites are not same as the SCD2.

So what is an Effectivity Satellite?

For an Effectivity Satellite, a Driving Key needs to be defined. For e.g. let's say we have a Link for Opportunities and relations to Accounts. The Account on Opportunity can change overtime. A common scenario is that the Opportunity is Assigned to a Global Parent account (e.g. Seagate Technology), and may get re-assigned to the Account Subsidiary (e.g. Lyve Labs) and get re-assigned to the Global Parent (Seagate Technology) at a later date

This will be tracked in the LINK as following:

8913b4fac89470111e184de02Lyve Labs2023-02-21 14:43:05
8cee5f97c8947011192b3503Seagate Technology2023-02-21 14:53:57

Now the 3rd step, i.e. 111 switching back to Seagate Technology will not be captured in this LINK as a new row in this LINK

An Effectivity SAT can be used to Track effectivity (temporal relevance) of the aforementioned Opportunity to Account relationships.

8913b4fac89470111e184de02Lyve Labs2023-02-21 14:43:059999-12-31 0:00:002023-02-21 14:43:05
8cee5f97c8947011192b3503Seagate Technology2023-02-21 14:53:579999-12-31 0:00:002023-02-21 14:53:57
8913b4fac89470111e184de02Lyve Labs2023-02-21 14:43:052023-02-21 14:53:572023-02-21 14:53:57
8cee5f97c8947011192b3503Seagate Technology2023-02-21 14:53:572023-02-21 15:00:312023-02-21 15:00:31
8913b4fac89470111e184de02Lyve Labs2023-02-21 15:38:309999-12-31 0:00:002023-02-21 15:38:30
8cee5f97c8947011192b3503Seagate Technology2023-02-21 15:44:429999-12-31 0:00:002023-02-21 15:44:42
8913b4fac89470111e184de02Lyve Labs2023-02-21 15:38:302023-02-21 15:44:422023-02-21 15:44:42
  1. When the relationship is first recorded only 1 record is inserted (highlighted in green)
  2. When there is a change in a relationship it must be based on one of the participants of the relationship– the driver, we track its change vs the other keys in the relationship. To do that we end_date one record and insert a new "active record"; i.e. 2 records inserted. (highlighted in red)