Data Vault 2.0: hubs, links and satellites, and what they really cost

Also called: DV2.0, hubs links and satellites, the raw vault, Linstedt’s model

Built for the case where the warehouse has to be able to say exactly what it was told, and when — and where the sources will not stop changing shape.

What it is

Data Vault splits every entity into three kinds of table. A hub holds a business key and nothing else — the customer number, the product code. A link holds a relationship between hubs. A satellite holds everything descriptive, hanging off a hub or a link, with a load date.

The rule that makes it work: nothing is ever updated or deleted. A satellite gets a new row when the incoming data differs from the newest row already there, and the old row stays exactly as it was. The warehouse can therefore always answer “what did we know about this customer on the 3rd of March, and when did we learn it?”

The pay-off is what happens when a source changes. Adding a system, or a new set of attributes, means adding tables rather than rewriting existing ones — nothing already loaded has to be migrated. That single property is what people buy Data Vault for, and it is genuinely rare.

How the tables are actually shaped

The three table types

Hub_Customer
  Customer_HK     BINARY(32)  -- hash of the business key
  CustomerNumber  NVARCHAR    -- the business key itself
  LoadDate, RecordSource

Link_Order_Customer
  Order_Customer_HK  BINARY(32)   -- hash of both parent keys
  Order_HK, Customer_HK           -- the hubs it joins
  LoadDate, RecordSource

Sat_Customer
  Customer_HK     BINARY(32)   -- key is (Customer_HK, LoadDate)
  LoadDate        DATETIME2
  HashDiff        BINARY(32)   -- hash of every descriptive column
  Name, Address, Segment, ...

HashDiff is how a satellite decides whether anything changed: hash all the descriptive columns, compare with the newest stored row, insert only if they differ. It is cheaper than comparing forty columns and it cannot be fooled by column order.

Why hash keys, and the invariant nobody may break

The hash key is the business key put through a hash function. That means a satellite can recompute its parent’s key from its own incoming row rather than looking the hub up — so the satellite can load before the hub does and still land on the right key. Loads become order-free and parallel, which on a large vault is the difference between a nightly window and no nightly window.

The price is an invariant you cannot be casual about: every expression that computes a key must be character-for-character identical. One load that trims the key and another that does not produce different hashes, the join finds nothing, and there is no error — just an empty warehouse. Normalise once (trim, upper-case, a known placeholder for NULL) and write that expression in exactly one place.

The ghost row

Every hub gets a row for “unknown”, whose key is what a NULL business key hashes to. Without it, a link involving a missing reference has to be dropped, and the transaction disappears from the warehouse entirely. With it, the row survives and points somewhere honest. It is Kimball’s -1 member under another name.

The business vault and the marts

The raw vault holds what arrived. Anything calculated, conformed or interpreted goes in a business vault beside it, and the readable layer is a set of dimensional marts on top of that. This is not an optional extra phase — it is how anybody gets an answer out.

When it fits, and when it does not

Data Vault is the most demanding model here and the most often chosen for the wrong reason. It is not a better Kimball; it solves a different problem.

Those two lists are Netune’s own judgement of this methodology, copied from the rule file it scores a real database with.

What it costs

The mistakes that are expensive

Letting analysts near the raw vault

It is not built to be read, and a team that has to query it directly will conclude the warehouse is broken — reasonably. Every question means joining hubs to links to satellites and filtering each satellite to its newest row. Plan the marts as part of the project, not as a phase two that gets cut.

Two expressions for one hash

Covered above because it is the failure mode that costs the most time: it produces no error, no warning and no rows. If you write vault loads by hand, put the key expression in one place — a function, a view, a generator — and never type it a second time.

Hashing a concatenation that silently truncates

A link key hashes several business keys joined together. In SQL Server, concatenating sized string types caps the result at 4000 characters, so a link over several hubs with long keys truncates before it hashes — only for the long ones, which is the worst possible distribution of a bug. Widen the first part to NVARCHAR(MAX) and the problem goes away.

How it compares

Data Vault or Kimball?

If you are choosing between them you are probably choosing both: Data Vault for the integration and history, a star schema on top for the reporting. Choosing Data Vault instead of a star only makes sense if nobody needs to report yet, which is rarely true.

Data Vault or Inmon?

Both are integration layers with history. Inmon’s core asks people to model the business and rewards them with something readable; the vault asks for far less modelling judgement and rewards you with something that absorbs change mechanically. Pick the vault when the sources change faster than the committee meets.

Data Vault or Anchor Modeling?

Anchor takes the same idea further — one table per attribute rather than a satellite of several — and pays for it in joins. Anchor is the stricter, more academic cousin; the vault is the one with more industry tooling around it.

Questions people ask