Partitioned Multi-Active Satellite

For any business object or relationship, it is plausible that you may have multiple active records for a parent entity. A business object is of course that thing your business forms business capabilities around. The policy management capability manages policies and policies are uniquely identifiable by the policy number (the business key). Claims management manages claims and is uniquely identified by a claim number. A customer may have many policies and many claims, and the customer entity itself is managed by a customer management capability and uniquely identified by a customer id, another business key. Relationships as a parent entity is the recorded interaction, transaction or business event between business entities and that may have multiple active records too. We have shown how such a pattern can be supported by link-satellite tables when simulating Kimball-styled models using data vault tables objects here.

In a data vault, business objects are managed and tracked in hub tables; relationships are tracked and managed in link tables and the current and historical state of business objects and relationships stored in hub and link satellite tables respectively. By extension we manage multi-record state in multi-active satellite tables or as satellite tables with dependent-child keys; they are different in how they manage the parent entity state.

• A satellite with dependent-child key tracks changes to the parent entity with a dependent child key or keys. For example, tracking order line changes to an order, an order line could have changed but no changes being applied to any of the other order lines to an order. An order line identifier means nothing without its parent order number and thus it is a dependent-child key.
• A multi-active satellite tracks the changes in a set of records by a parent entity; we track if any of the records in a set whose record digest changes or the number of records in the set changes. For example, tracking active addresses for a customer if the set of addresses for a customer changes then we want to track what is the active set.

Tracking multi-record state is not limited to raw vault either, we can therefore design multi-record state into business vault too if the outcome of your business rule warrants it, see the table below.

Note: raw vault satellite tables are still supported by a single staged source; one staged source = one or more (satellite splitting) raw vault satellite tables. Satellite tables are also not updated by multiple sources and its columns from source are not changed in any way. If you need an in-depth comparison of the effect of choosing between satellite tables with dependent-child keys and multi-active satellite tables then please visit this published article.

What this article is proposing is a combination of multi-active satellite with dependent-child keys and why such a table design would be necessary for a data vault.

Let’s begin with structural changes.

satpma{rv|bv}{hub|lnk}{{source-badge}_{source_table}|{concept}}

Satellite table according to complexity and grain

A multi-active satellite table tracks changes in a set of active records by parent key (hub or link), a dependent-child key can exist within that set but that would not change the behaviour of the multi-active satellite table. The proposed innovation in this article is focussing on tracking subset changes of those dependent-child keys effectively tracking these as subsets of the parent key. To visualise the difference in this proposal, recall that a multi-active satellite includes a record subsequence key (a set counter) to help index the set of records per parent key, this proposal shifts that subsequence key to the right of a dependent-child key (or keys) to index changes to subsets of the parent entity. Any change in any of the record digests of that subset or the number of records in that subset therefore creates a new subset and versions the old subset.

Dependent-child key satellite table

A satellite table with a dependent child key has no need for a sub-sequence key; but tracking sets and subsets do.

Multi-active satellite tables and partitioned multi-active satellite tables

The subsequence key is not a dependent-child key, and has no meaning to the business, it is merely another tool (a metadata column) we use to ensure the integrity of the set complies with the constraints of database systems. Note that the sub-sequence key on the left counts for the whole set per hash-key, the partitioned multi-active satellite table’s sub-sequence key on the right counts for the hash-key with the dependant-child key.

Multi-active satellites track changes to a set. Partitioned multi-active satellites track changes to multiple independent sets within the same parent.

Impact of choosing different satellite table patterns

Now let’s run through examples comparing the three approaches side by side.

Multi-state records initiated

A regular satellite table pattern would already not work here as we are tracking changes based on hash-key and code (the dependent-child key).

Day 2 is when webegin to see changes in the approach

Satellite with dependent-child key

• Key: 101, dep-key: ‘A’ sees a change and we insert a new version for the row
• Key: 456, dep-key: ‘A’ sees a change and we insert a new version for the row

Multi-active satellite

• Key: 101: sees a change and we insert a new version for the set
• Key: 456: sees a change and we insert a new version for the set

Partitioned Multi-active satellite

• Key: 101, dep-key: ‘A’ sees a change and we insert a new version for the set
• Key: 456, dep-key: ‘A’ sees a change and we insert a new version for the set

It would appear that the satellite table with a dependent-child key and the partitioned multi-active satellite behave in the same way. Let’s explore the loading pattern a little further.

Day 3 shows a limitation of satellites with dependant-child keys

Satellite with dependent-child key

What are staged shows two active states for the key 456, dep-key ‘C’. If the data shows this pattern, this means that either:

• The data has not been properly profiled to understand what the valid constraints are.
• Or there is an upstream business rule issue, an occurrence in the business rule that is not supposed to be possible, the multiple active state by business key + dependent-child key.

Do not employ multi-active satellite table structures by default, you must profile the upstream data to understand expected behaviour against the data you need to support your business case. Failure in doing so leads to unexpected behaviour in your modelled analytical business case.

Multi-active satellite

• Key: 101: sees a change and we insert a new version for the set
• Key: 456: sees a change and we insert a new version for the set

Partitioned multi-active satellite

• Key: 101, dep-key: ‘B’ is a new set and we insert the new set
• Key: 456, dep-key: ‘C’ sees a change and we insert a new version for the set

This latter scenario is not possible if we do not treat the records belonging to the parent and dependent-child keys as a subset.

Benefits, alternatives and risks.

Raw vault modelling patterns stipulates that a single staged source will load to one or more raw vault satellite tables (hubs, links and satellite). Data vault also stipulates that a single raw vault satellite table is never multi-sourced as it would lead to schema evolution complexities and conflicting naming standards if we ever change what we get from a source system.

“There are no solutions, only trade-offs” — Thomas Sowell

In my literature I have always advocated for including the business keys you need into the satellite table structure itself, and it is no different here. We avoid the need to join to the hub table to retrieve that business key and therefore offset the cost of storing business keys in satellite tables by avoiding unnecessary joins (especially if you do not need any data from any other satellite tables).

One of the motivations behind this pattern (and it is the same motivation for building satellite tables with dependent child keys) is to avoid what is colloquially known as hub table sprawl. This is where a data modeller assumes that every code he sees is a business key and models those codes and keys as hub tables. Hub sprawl inevitably leads to link table sprawl where every hub table needs to be joined with link tables to rebuild what could have already been denormalized in the satellite tables themselves. Another anti-pattern is modelling dependent-child keys into link tables too; this leads to link tables being single-purposed and all link-satellite tables either inheriting that dependent-child key depiction or forcing downstream information marts factor in that the dependent child key will not inherit that dependent-child key. Worse, if a dependent-child key is not relevant or versioned then do you add that relationship to the same link table or do you create a new one? And what if the relationship effectivity goes back to the previous state? Without an effectivity satellite table it is not possible and your data model becomes a switch architecture where everything you do in the model has to be carefully explained. You can avoid this complexity entirely if you simply push dependent-child keys into satellite tables instead.

Like the use case illustrated above, the intent of using a partitioned multi-active satellite table must be purposeful.

What about the alternatives?

1. Satellite Splitting

A staged source can lead to one or more satellite table structures and the standard advisory behind satellite splitting is to split the satellite table:

• By what it is describing — hub or link satellite table, describing an entity or the relationship between entities.
• By rates of change — fast changing transactions loaded into link-satellite tables and tracking slowly changing dimensions in hub-satellites tables.
• If there is a need to isolate personally identifiable information away from the rest of the descriptive data.
• Schema evolution may see the need to deploy as a new satellite table if it is not possible to evolve an existing satellite table in your data platform of choice.

If the profiled data does not fit any of the above reasons for splitting satellites then the choice becomes a balance of managing multiple data pipelines for multiple satellite tables for simplicity versus having to rejoin that data anyway if the business case requires it. That is a modelling choice.

It is a matter of preference

2. Subset of subsets

The subtle difference between multi-active satellite tables and satellites with dependent child keys makes a significant difference to storage and how the information is eventually presented from these satellite table variations. The same is true of the partitioned multi-active satellite table; if a dependent-child key is used to track subsets then can we use another dependent-child key at the lower grain to identify a lower grain set of the parent set. Well, no, even if you attempted to code this the net effect is still a set change to upper-level grain and thus the effect of implementing subset of subsets will be redundant.

However, this pattern does enable the possibility of choosing lower-level dependent child keys to subset by, but that is down to business case and modelling choice (if the result is effective).

What you declare as the leaf node of your ontology tree determines the independent-subset and satellite table grain

Note that the sub-sequence key is arbitrary, it does not matter which record in a multi-record state gets which subsequence key.

3. Deviation of the data vault standard

Does this pattern introduce anything different to the data vault standards? No, the proposal combines two well-known patterns into a single satellite table loading pattern. There are enormous efficiencies to gain by not splitting the data as well as to splitting them to take advantage of an OLAP platform’s capabilities around hash-joins, bloom-filters and data sketches (to name a few), this is yet another pattern. We see customers who do not want to split the data and in fact in their platform of choice it would be beneficial not to do so!

Although earlier in the article I emphasised that we include business keys in satellite tables to avoid unnecessary joins to hub tables I did not say that hub tables are unnecessary. They are! Hub tables remain the integration between source systems in your source system landscape (horizontal integration) and the immutable mapping between business understanding and those source systems (vertical mapping).

Hub tables are crucial to a data vault

The same is true of SNOPIT as it has shown to beat the traditional PIT table performance simply by reusing a well-known Kimball modelling join technique to enhance the efficiency of hash-joins!

4. Impact to how we build PITs, Bridges and SNOPITs

Point-in-time constructs we call PITs, Bridges and SNOPITs are essentially join-indexes that act like geo-locators to where your data resides. They are query assistance tables built to support information marts, disposable and therefore they are not business vault artefacts. If designed efficiently, they will coerce the OLAP platform to use the optimal join path for analytical data, the hash join (build and probe). You will see it being utilised by your analytical platform by looking at the shape of your query plan and if you see what is known as a *right-deep join tree* you know the platform is using a hash join.

The primary purpose of these query assistance structures is guiding a join query running on top of these constructs to locate that data within the adjacent satellite tables. It is not the purpose of PITs, Bridges and SNOPITS to resolve multi-record constructs into a join-index, that is the job of the information mart query itself to resolve after the query has found that data.

Let’s imagine you have a scenario with multiple satellite table types surrounding a hub table and you intend to build a PIT table to optimise the join path to related records in those satellite tables. The PIT table construct does not store any information, only surrogate hash keys and applied timestamps. You also cannot predict how many active records will be present for a parent-entity at a point in time; so how will you solve this? Don’t. Multi-record state satellite tables should have zero impact to your PIT, SNOPIT and Bridge table construction, but they do impact how you present that information once you have retrieved it.

It is an advanced use case

To be frank, I don’t expect a partitioned multi-active satellite table to be the default method for tracking true changes to a business object’s state. However, when you do model more complex scenarios around sub-keys of a parent entity a data vault using the existing tools (hubs, links and satellites) may be forced to build and maintain a multitude of data vault artefacts when in fact there is a method to resolve it using just one. This does not deviate from any standards especially since it is simply combining existing patterns and the load and query pattern can be standardised and repeatable.

Chart of accounts, hospital diagnosis codes and subcodes, manufacturing part numbers and a host other business ontologies all deal with pre-defined codes and subcodes (and subcodes of subcodes) that would benefit from a disciplined data modelling approach that keeps all the data query-able from a single satellite table structure. Each of these codes are not business keys as they depend on the existence of parent keys to uniquely identify them; but they do carry their own state only if they are describing a business entity. To track these artefacts independently as codes and descriptions is in fact in the realm of reference data management which we anyway use to enrich the information we get from the data vault typically at the information mart layer.

Multi-active satellites track changes to a set. Partitioned multi-active satellites track changes to multiple independent sets within the same parent.

The views expressed in this article are that of my own, you should test implementation performance before committing to this implementation. The author provides no guarantees in this regard.