Understanding Effectivity Satellites in Data Vault
Effectivity satellites play a crucial role in Data Vault modeling by tracking changes and deletions in source systems. In this article, we’ll explore when to use an effectivity satellite, how it differs from a regular satellite, and the best practices for implementing it.
What is an Effectivity Satellite?
An effectivity satellite is essentially a standard satellite used to capture business time attributes such as valid-from and valid-to dates, contract start and end dates, and deletion timestamps. The key distinction is that it tracks soft deletions from source systems, ensuring that historical data integrity is maintained in the Data Vault.
When Should You Use an Effectivity Satellite?
Effectivity satellites should be used when you need to track historical changes in relationships and entities, especially deletions. Common use cases include:
- Tracking contract start and end dates
- Monitoring employee and corporate car assignments
- Managing customer records and their deletion status
Difference Between Effectivity and Regular Satellites
Regular satellites store descriptive attributes like names and addresses, whereas effectivity satellites focus on time-based attributes and deletion markers. While regular satellites track changes in data, effectivity satellites specifically manage record deletions and validity periods.
Choosing Between Effectivity and Link Satellites
Link satellites capture changes in relationships between entities, whereas effectivity satellites track the validity of those relationships. You should choose an effectivity satellite when:
- Deletions need to be recorded without physically removing data
- You need to track when a relationship was created and ended
- Historical relationship integrity must be preserved
Example: Employee and Corporate Car Assignment
Consider an employee assigned to a corporate car. When the assignment changes, a new link entry is created between the employee and the new car. The effectivity satellite records the deletion timestamp for the old relationship and maintains the history of assignments.
Handling Deletions in Effectivity Satellites
One of the main challenges in effectivity satellites is detecting and handling deletions. Different data loading methods impact how deletions are recorded:
- Full Loads: Compare current and previous loads to identify missing records.
- Change Data Capture (CDC): Uses system flags to detect deletions.
- Delta Loads: Requires additional logic to identify removed records.
Effectivity Satellites in Business Vault
In a Business Vault, effectivity satellites can be used to implement business rules for tracking deletions. For instance, a customer may be considered deleted only if removed from all source systems, which requires a business-driven deletion logic.
Conclusion
Effectivity satellites are essential in Data Vault modeling for tracking deletions and historical changes. Understanding their role and choosing the right satellite type ensures accurate data lineage and integrity.
Watch the Video
Meet the Speaker
Michael Olschimke
Michael has more than 15 years of experience in Information Technology. During the last eight years he has specialized in Business Intelligence topics such as OLAP, Dimensional Modelling, and Data Mining. Challenge him with your questions!
