Historized or non-historized link?

Data Vault 2.0
1

Hi,

I have following use case.
There is DebitAndCredit data sourced from 2 systems.
Unit Of Work is Period, AccountingSegment, AccountingDocument.
Financial attributes are Debit and Credit values.
First system delivers immutable data, transactions cannot be modified or deleted (INSERT only), modifications are managed through corrections.
Second system can me modified within last 2 months.
In the infomart data should be federated, business wants to report total balances, regardless the source.

What DV structures should I create?

  1. Common link + satellite for the second system>
  2. Nonhistorized link for 1st, regular link + satellite for 2nd (debit / credit might be changing), bridge to integrate both and provide acceptable performance?
  3. Other options?

I’d appreciate your feedback on above options.

Regards
Marcin

2

Non-historised links are about streaming data which it seems you do not have

Both should be satellites off a common link it seems, the dependent-child key probably the transaction date or transaction id in each satellite

DV2.0 is insert only, for the 1st system you will not see ‘updates’ and the 2nd you will but in both cases they are still insert only with the same satellite table loading pattern — no difference, it’s only in the translation to the information mart layer where the differences in calculating changes are calculated.

Non-historised guidance being immutable refers to the fact that with streaming data the management of exactly-once semantics and ordering is managed upstream; therefore this means you do not need to check for either when loading a non-historised link or sat.

Hope this helps,

3

Thanks Patrick for the response.
Of course there is a unique transaction ID from both systems I forgot to mention.
BTW: if I get unique transaction ID and also combination of AccountingPeriod, AccountingSegment, AccountingDocument and TransactionTimestamp are unique, what is the actual Unit Of Work and what should be hashed in the link hash?

Regarding your recommendation to split data into transaction I see possible technical issue.
1st system delivers roughly 50m transactions on a monthly basis, we have build a history of over 1b rows so far in DWH. If there are 1b rows in link and 1b in satellite it will not possible work fast in the consumption layer? I could not even partition satellite since I’d have only the financial figures in the satellite, right? Of course I could build a bridge in BV and load it incrementally using HWM, but this bridge would essentially resemble the non historized link.

Marcin

4

Remember… the link is a relationship between business objects — so you will not see 1bn records in there; details of that interaction are in the link-satellite.

We strictly stick to business objects in hub tables, nothing more, anything otherwise ends up with way more tables then you will ever need and headed towards a new legacy platform.

Just published this morning, The Different Grains of Multi-Active Records. | by Patrick Cuba | The Modern Scientist | Oct, 2024 | Medium
Scroll down to " Intra-day key (the right way to model transactions)"