Built never to be rebuilt

Why design a warehouse so that a new source never forces you to restructure the old ones?

A warehouse is built well. The dimensions are conformed, the facts are clean, the reports are trusted. Then the company acquires another company, and its customer system arrives — with its own identifiers, its own idea of what a customer is, and three attributes the original never had.

What happens next is the thing worth explaining. Not "some work" — specifically, the shared customer dimension has to change, and everything already built on top of it has to be revalidated. The new source did not add to the model; it disturbed it. And the disturbance is proportional to how much has already been built, which means the warehouse gets harder to extend precisely as it gets more valuable.

Why does adding a source disturb what exists? Because in a conformed dimensional model, the shared dimension is a negotiated agreement. Everyone who joins to it depends on its columns, its keys, and its definition of a customer. A second source arriving with a different definition cannot simply be appended; it forces a renegotiation, and every party to the old agreement is affected.

Notice the shape of that. Each integration decision constrains the next — what you can do in year three depends on what you agreed in year one. Path dependence. And the standard responses to it are all bad: refuse the source, bolt it on beside the model as a private silo, or periodically stop and rebuild.

So the question becomes: could you design an integration layer where the addition of a source is structurally an addition — new tables, no edits?

Work out what that would require. Every place where a new source could force a change must be isolated. Where are they?

They are the descriptive attributes, which differ per source; the relationships, which differ per source; and the identity of the thing itself, which is the only part all sources genuinely share. So separate exactly those three, and you have the Data Vault, as developed by Dan Linstedt and published in the early 2000s:

A hub holds nothing but a business key — a customer number, an order number — plus when it was first loaded and where it came from. Nothing descriptive at all, which is precisely why nothing about a new source can require it to change.

A link holds a relationship between hubs, and holds it as many-to-many by construction, even when today's rule says one-to-many. That looks wasteful until you notice why: a cardinality assumption is the commonest thing a new source breaks, so refusing to assert it removes an entire class of future restructuring.

A satellite holds the descriptive attributes, hanging off one hub or link, keyed by its parent plus a load timestamp. Crucially there is one satellite per source. The acquired CRM's view of a customer becomes a new satellite on the existing hub, and the original satellite is not touched.

Now trace the acquisition through that structure. New customer identifiers? They go into the existing hub as new rows — rows, not columns. New attributes? A new satellite. A relationship the old system never recorded? A new link. Nothing that exists is altered, and every report built on the old satellites still returns exactly what it did yesterday.

The second half of the design follows the same instinct: the vault is insert-only. Nothing is updated or deleted. A changed attribute becomes a new satellite row with a later load timestamp. So history is not a feature you add; it is a consequence of never overwriting — and the state of the warehouse as of any past date is recoverable by construction, because nothing was ever thrown away.

Now the honest cost, which is substantial. You will have several times as many tables, and answering an ordinary business question means joining hub to link to satellite repeatedly. A data vault is not something users query. It is an integration layer, and it is normal and intended to build star schemas on top of it for consumption — so this is not an alternative to dimensional modelling but a layer beneath it. Choosing the vault means accepting more machinery in exchange for the property that the machinery never has to be rebuilt.

One genuine ongoing disagreement: Data Vault 2.0, published in 2013, replaced sequence surrogate keys with hash keys computed from the business key, letting hubs and satellites load in parallel without lookups. Whether hashes are worth their collision risk and storage cost is still argued, and I would not call either side settled.

Three refinements.

First, the misconception that the vault stores integrated, cleaned data. It does not. It stores source data faithfully, keyed by business key, with the source and load time recorded — including contradictions between sources, deliberately preserved rather than resolved. Business rules that reconcile them are applied on the way out, into the marts. That is what lets a rule change without a reload: the raw material is still there.

Second, the hub's business key is the load-bearing decision. It must be the identifier the business actually recognises and that is stable across systems, not a source system's internal surrogate. A hub keyed on the wrong thing reintroduces exactly the restructuring the design exists to avoid.

Third, this is not free and is not always right. For a single-source warehouse with a stable domain, the vault's overhead buys nothing that a well-run dimensional model does not already provide. It earns its keep where sources are many, arrive unpredictably, and where auditability of what a source said, and when, is itself a requirement.

• Business vault and information marts: where the reconciliation rules actually live.
• Hash keys versus sequence keys, and the parallel-loading argument for each.
• How insert-only satellites compare with slowly changing dimensions for keeping history.

Every term the collection defines is gathered in the glossary.