Joining SCD2 Tables Using Data Vault

Joining SCD2 Tables

When working with Data Vault 2.0, one of the most common challenges is how to handle Slowly Changing Dimension type 2 (SCD2) tables when loading Link Satellites. Imagine a scenario where you need to join three SCD2 tables, each with valid_from and valid_to dates, and bring them together into a single Satellite hanging from a Link. At first, this might sound straightforward, but the details matter — and depending on your approach, you could make your warehouse harder to maintain, less performant, or less flexible.

In this article, we’ll walk through the best practices for handling this situation, based on Data Vault principles. We’ll discuss why you shouldn’t rush into joining multiple SCD2 tables into one Satellite, how to handle business timelines, the role of PIT and T-PIT tables, and strategies for keeping your design scalable and future-proof.

Understanding SCD2 in the Context of Data Vault

Slowly Changing Dimensions type 2 are a way to track historical changes in dimensional data. Each record typically has valid_from and valid_to dates that describe its period of effect. In a Data Vault model, however, the focus isn’t on interpreting those business dates upfront. Instead, the Raw Data Vault stage is all about capturing what the source gives you, as-is.

That means when we first bring data into the Raw Vault, the valid_from and valid_to fields should be stored as descriptive attributes — not as part of the Satellite’s primary key. Trying to interpret and align them too early will only create unnecessary complexity.

Why Not Load One Satellite From Three SCD2 Tables?

At first glance, it may seem attractive to combine all three SCD2 tables directly into one Satellite. But this goes against Data Vault best practices:

• Each Satellite should usually source from a single table or source system.
• Denormalizing multiple sources into one Satellite complicates your Raw Vault and makes it harder to maintain.
• You risk having to undo the denormalization later when new requirements come in.

The recommended approach is to create at least three Satellites — one per SCD2 table. You may even need more Satellites if you have to split them based on privacy, rate of change, or security rules. This simplifies your Raw Vault and sets you up for flexibility later.

Handling Validity Dates in Satellites

Once your Satellites are created, the question is how to handle valid_from and valid_to. Here are two key approaches:

1. Multi-Active Satellites

If your SCD2 records represent multiple simultaneously valid states (for example, different price lists where both current and future prices are valid), you can use a multi-active Satellite. In this design:

• The primary key is composed of hash_key + load_date + subsequence.
• valid_from and valid_to are stored as descriptive attributes, not as key parts.
• A staging-generated subsequence ensures uniqueness within a load.

2. Standard (CDC) Satellites with Subsequence

If only one record is active at a time (the classic SCD2 case), then you don’t need a multi-active design. Instead:

• Stick with the standard Satellite primary key: hash_key + load_date.
• Handle multiple intraday changes by adding micro- or nanosecond subsequences to load_date.
• This ensures only one active row per parent key at any given time.

Choosing between multi-active and CDC-style Satellites depends entirely on your data. Do you need multiple simultaneously valid records? Or does one replace the other in sequence? Your answer determines the right design.

Aligning Data with PIT Tables

Once the three Satellites are loaded, you’ll need to bring them together for reporting. This is where PIT (Point-in-Time) tables come in. PIT tables align deltas across Satellites to a common snapshot date, making it possible to present a unified view of related changes.

There are two main flavors:

• Standard PIT – aligns data based on load_date (technical historization).
• Temporal PIT (T-PIT) – aligns data based on business timelines like valid_from and valid_to.

A T-PIT lets you activate rows based on both the technical and business timelines. However, it comes with maintenance challenges: whenever valid_from or valid_to dates change, you may need to rebuild PIT partitions — which can be expensive if large date ranges are altered.

Where Should You Apply Business Timelines?

There are three strategies for applying business timelines like valid_from and valid_to:

1. Downstream in reporting or dashboards – simplest to maintain, and business users can define how timelines should be applied.
2. In dimension views – apply filters and conditions directly in the SQL layer that feeds reports.
3. Upstream in PIT/T-PIT – most performant but requires heavier maintenance whenever business timelines change.

A practical approach is to start downstream and only move timeline application upstream if performance issues demand it.

Building a Business Satellite

Once your PIT table aligns the Satellites, you may still need a Business Satellite (or Computed Satellite). This Satellite:

• Uses the PIT table as its foundation.
• Combines attributes from the three original Satellites using COALESCE or other business rules.
• Optionally applies T-PIT logic if strict business timeline alignment is required.

By separating Raw Satellites (which store raw source data) from Business Satellites (which apply interpretation and business logic), you preserve Data Vault’s flexibility while still meeting analytical requirements.

Best Practices Recap

• Create one Satellite per SCD2 source table — don’t denormalize too early.
• Treat valid_from and valid_to as descriptive attributes in the Raw Vault.
• Use multi-active Satellites only when multiple records are simultaneously valid; otherwise, stick with standard CDC Satellites.
• Align Satellites with PIT tables; consider T-PITs only if business timelines must be applied upstream.
• Whenever possible, push business timeline interpretation downstream to dimensions or reports for easier maintenance.
• Use Business Satellites when you need to merge multiple sources into a single unified view.

Conclusion

Joining multiple SCD2 tables into a single Satellite is rarely the right first step in a Data Vault 2.0 implementation. Instead, build your Raw Vault Satellites closely aligned with their sources, use PIT tables to align changes, and apply business timelines carefully — starting as far downstream as possible. This approach keeps your architecture maintainable, flexible, and scalable while still supporting complex historical analysis.

By following these principles, you’ll not only simplify your data model but also give your business users the power to interpret validity ranges in ways that make sense for them — without locking your warehouse into rigid rules that are hard to maintain.

Watch the Video

Meet the Speaker