Data Vault Mysteries… Effectivity Satellite & Driving Key

In today’s episode of Data Vault Mysteries, we discuss Driving Keys and Effectivity Satellites!

One of the most complex and often misunderstood Data Vault 2.0 artifacts is the beloved Effectivity Satellite. Some argue it doesn’t fit the Insert-Only architecture of Data Vault 2.0 but I can assure you, through a detailed example that an Effectivity Satellite does indeed fit the Data Vault 2.0 architecture and I will show you where and why you would even consider using Effectivity Satellite and why you would not.

Relationship Effectivity

Although Data Vault 2.0 is INSERT-ONLY why then do we have a start and end date in an Effectivity Satellite?

To understand this let’s first discuss relationship effectivity. Hypothetically if two business entities we are tracking have a relationship between each other, an account and a product (for instance) can be related in a one-to-one or one-to-many cardinality it stands to reason that if an account begins a relationship with a product the relationship itself will have a start date with no end-date value. To draw a parallel with slowly changing dimension (type 2) this means the end-date is assigned a high-date like ‘9999–12–31’ meaning that until the relationship changes, when we query this relationship it is the…