Handling Retroactive Changes to Historical Effectivity in Data Vault 2.0

Data Vault 2.0
1

Hi everyone,

I completed my Data Vault 2.0 certification about three years ago, and there is one modelling problem that keeps coming back in our implementations. I would be very interested to hear how other experienced Data Vault practitioners handle it, and whether there is a canonical DV 2.0 pattern that I may have overlooked.

A small disclaimer: I used ChatGPT to help me formulate this question as precisely as possible. I am very much a technical / IT person by background, and when trying to explain this kind of modelling problem I tend to disappear into implementation details and lose sight of the actual question. :wink:
( It’s a bit long but it contains everything so… hum… be prepared :sweat_smile: )

The problem

We have source systems that provide the complete historical timeline of an assignment, including explicit business validity dates.

For example, consider the assignment of an employee to an organizational unit.

At load time T1, the source provides:

Employee Unit Valid From Valid To
Mr. X Finance 2020-01-01 2020-12-31
Mr. X IT 2021-01-01 2025-12-31
Mr. X Finance 2026-01-01 NULL

A few days later, at T2, the source provides the complete history again, but the history has been retroactively corrected:

Employee Unit Valid From Valid To
Mr. X Finance 2020-01-01 2021-06-30
Mr. X IT 2021-07-01 2024-12-31
Mr. X Finance 2025-01-01 NULL

So this is not simply a new assignment becoming effective.

The source has changed its statement about the past.

For example:

  • the end of the first Finance assignment moved from 2020-12-31 to 2021-06-30;
  • the start and end of the IT assignment both changed;
  • the second Finance assignment moved from 2026-01-01 to 2025-01-01.

Where I struggle with the canonical Data Vault pattern

A natural Raw Vault model would seem to contain:

H_EMPLOYEE
H_ORG_UNIT

L_EMPLOYEE_ORG_UNIT
    Employee_HK
    OrgUnit_HK

with the source-provided effectivity stored separately.

However, the employee can return to the same organizational unit.

At T1, for example, the same Employee–Unit relationship has two distinct business-valid periods:

Mr. X → Finance
    2020-01-01 → 2020-12-31

Mr. X → Finance
    2026-01-01 → NULL

Therefore, for the same Link hash key and the same technical load timestamp, we need to represent multiple effectivity intervals.

This starts to look structurally like a Multi-Active Satellite, for example:

Link_HK
Load_DTS
Valid_From
Valid_To
Record_Source

with something such as Valid_From acting as a discriminator within (Link_HK, Load_DTS).

But semantically I am uncomfortable calling this a Multi-Active Satellite.

Mr. X is not multi-active in the business sense. At any given business point in time, he has only one organizational assignment.

What is “multi-active” is really the set of historical intervals reported by the source during one technical observation.

The second dimension: preserving what we knew

Since this is Raw Vault, we also do not want T2 to overwrite what was loaded at T1.

Ideally, we want to retain both:

What did the source tell us at T1?

and:

What does the source currently tell us about that same historical period?

This effectively gives us two timelines:

  • technical/system time — when we received a version of the history;
  • business/effective time — when the assignment was supposedly valid.

In other words, the problem starts looking distinctly bitemporal.

Possible approaches I see

I can think of several approaches, but none feels completely canonical:

  1. Treat the source effectivity as descriptive data in a regular Satellite.
    This immediately creates the issue of multiple rows for the same Link and Load_DTS when an Employee–Unit relationship occurs several times in the source history.
  2. Use a Multi-Active Satellite (or a MAS-like structure), with Valid_From or another sequence/discriminator identifying each interval.
    Technically this works, and it is a solution we already use, but I am not convinced that this represents the intended semantics of a MAS in DV 2.0.
  3. Include the start date in the grain/identity of the assignment.
    This is problematic because the source can retroactively change the start date itself. A correction from 2021-01-01 to 2021-07-01 would then appear to create a different business object rather than correcting the same assignment.
  4. Introduce an Assignment Hub / Link based on a source-provided Assignment ID.
    This would probably solve the identity problem nicely, but many of our source systems simply do not provide such an identifier.

My questions

For those following Data Vault 2.0 modelling practices closely:

What would you consider the canonical Raw Vault pattern for this situation?

More specifically:

  • How would you model a source that repeatedly delivers a complete, retroactively mutable business timeline?
  • Would you consider a MAS appropriate even though the records are not simultaneously active from a business perspective?
  • Should an Effectivity Satellite be used here, or is an Effectivity Satellite intended for a different problem?
  • Would you model each reported interval as a separate occurrence, and if so, what would you use as its stable identity when the source provides no interval/assignment identifier?
  • How do you preserve both the original history received at T1 and the retroactively corrected history received at T2 without inventing business keys that do not exist in the source?
  • Is there an established DV 2.0 pattern for this kind of bitemporal, source-provided effectivity, or is this one of those cases where a pragmatic extension of the standard is expected?

This is a recurring problem in our environment, rather than a theoretical edge case, so I am especially interested in solutions that have been used successfully in production.

I would also be very interested in references to the DV 2.0 literature, training material, or modelling standards that explicitly address this scenario.

Thanks in advance for any insights.

2

Hi Juan,

I think you’re on the right track with Employee and Unit as Hubs and a Link between them. I would not introduce an Assignment Hub unless the source provides a genuine assignment identifier.

Since the source is delivering versions of a complete history rather than business events, I would preserve each extract exactly as received and store the validity periods in a satellite on the Link.

To support multiple assignment periods for the same Employee-Unit relationship within a single extract, the satellite needs an assignment-level identifier. Ideally this comes from the source. If one doesn’t exist, I’d use a sub sequence number generated in staging. The goal is to keep the satellite at the assignment level, not the Link level.

In practice, this structure looks very similar to a Multi-Active Satellite. Although the employee’s assignments are not multi-active, multiple historical periods exist for the same Link at a single load point.

I would not use an Effectivity Satellite. My understanding is that it is intended to manage relationship validity in the warehouse, not to preserve retroactively corrected source timelines.

For T1 vs T2, I would load both versions unchanged and use extract/load timestamps to preserve what the source said at each point in time. That naturally gives you both business time and system time.

So my answers are:

  • Canonical pattern: Link + satellite holding the source-provided validity periods.
  • MAS appropriate? Sort of, structurally, even if not multi-active from a business perspective.
  • Effectivity Satellite? Probably not.
  • No assignment ID available? Use a technical occurrence identifier and treat it as metadata, not a business key.
  • Preserve T1 and T2? Load every extract unchanged and keep the technical timeline.

To me, this is fundamentally a bitemporal problem, and preserving the source’s history is more important than forcing a strict interpretation of a specific DV pattern. If all history is recorded correctly, we can use business logic to align this with business understanding on the way out of the raw vault.

2 Likes
3

I think you need an additional CDK (child dependent key like assignment_id, so SAT pk would become (hk,ldts, assignment_id), to distinguish INSERTS from UPDATES.

And fun using window functions with proper (PARTITION BY/ORDER BY SLINK_EMP_DEP..order by assignment_id, ldts desc)

--current
SELECT *
FROM SLINK_EMP_DEP
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY LINK_HK, ASSIGNMENT_ID
    ORDER BY LDTS DESC
) = 1;
1 Like
4

While this is my preferred solution, if Juan is correct and they don’t have an assignment identifier available, what would be the best way to work around it?

1 Like
5

In fact at this point I use a MAS using the pk of the assignement to store every version of the history in the RAW.
And create some sort of non historised effectivity in Business.

6

Agree with Dylan.

The Load-DTS in the Link’s SAT would differentiate between source data loads

- T1, T2 etc.

But since a load may contain the same {Employee, Unit} HK’s more than once, U require a Sub-Sequence # in the Link SAT to differentiate them.

The Valid-From & To dates would just be attributes in the SAT.

1 Like
7

Thanks everyone for the answers. I think I may finally understand what you are suggesting, but I’d like to make sure I’m not misinterpreting it.

Are you referring to the concept described in section 4.4.5.2 “Dependent Child Key” of Dan Linstedt & Michael Olschimke’s Building a Scalable Data Warehouse with Data Vault 2.0?

If I understand it correctly, the idea would be that the Employee–Unit Link still represents the relationship itself, while an additional dependent child key / sub-sequence distinguishes the individual occurrences of that relationship within the source data.

So, conceptually, something along the lines of:

Employee_HK
OrgUnit_HK
Dependent_Child_Key
Load_DTS

with the validity dates remaining attributes describing each occurrence.

If that is what you mean, then I think this may actually be the concept I was missing.

Rather embarrassingly, I’ve read that book several times, but apparently my eyes have managed to pass over the Dependent Child Key section every single time without my brain registering its relevance to this problem. :slight_smile:

Am I understanding your suggestion correctly, or are you referring to a slightly different pattern?

1 Like
8

Classic time-blob pattern, I’d use a an MSAT as an MSAT will load complete SET is anything changes in the SET. Former SETs are historised.

What you end up with is

Employee Unit Valid From Valid To Applied Date
Mr. X Finance 2020-01-01 2020-12-31 2026-01-01
Mr. X IT 2021-01-01 2025-12-31 2026-01-01
Mr. X Finance 2026-01-01 NULL 2026-01-01
Mr. X Finance 2020-01-01 2021-06-30 2026-01-02
Mr. X IT 2021-07-01 2024-12-31 2026-01-02
Mr. X Finance 2025-01-01 NULL 2026-01-02

Day 1 would have been 2026-01-01 and Day 2 is 2026-01-02 (new SET).

HashDiff is (UNIT || VALID_FROM || VALID_TO)

1 Like