What is Data Vault?

How it works

Data Vault, created by Dan Linstedt (version 2.0 was published in 2013), models three kinds of table:

• Hub: the list of unique business keys for one concept, such as customer or order, with the time each key was first seen and its source. Nothing else.
• Link: a relationship between hubs, such as customer placed order, stored as unique combinations of their keys.
• Satellite: the descriptive attributes of one hub or link and their full history, split by source and by how often they change.

Version 2.0 standardised hash keys: each hub and link key is a deterministic hash (often MD5) of the normalised business key. Because a loader can compute the key from the source row alone, hubs, links and satellites can be loaded in parallel without lookups. Satellites also store a hash diff of their attributes, so a load inserts a new satellite row only when something changed.

Everything is insert-only, which makes the vault auditable: you can always see what each source said and when. That strength is also its cost. The vault has many tables and is hard to query directly, so teams build dimensional marts or wide tables on top of it for reporting. Data Vault pays off when there are many sources that change often and strong audit requirements; for a small team with a few stable sources, a Kimball star on top of a staging layer is usually simpler.

Example

Hub keys are hashes of the trimmed, upper-cased business key, so ' c2' from one loader and 'C2' from another land on the same hub row, and reloading inserts nothing new.

CREATE TABLE hub_customer (customer_hk char(32) PRIMARY KEY, customer_id text, record_source text);
CREATE TABLE stg_customer (customer_id text);
INSERT INTO stg_customer VALUES ('C1'), (' c2'), ('C2');

-- Run the same hub load twice: insert-only, new keys only.
INSERT INTO hub_customer
SELECT DISTINCT md5(upper(trim(customer_id))), upper(trim(customer_id)), 'shop.customers'
FROM stg_customer s
WHERE NOT EXISTS (SELECT 1 FROM hub_customer h WHERE h.customer_hk = md5(upper(trim(s.customer_id))));

INSERT INTO hub_customer
SELECT DISTINCT md5(upper(trim(customer_id))), upper(trim(customer_id)), 'shop.customers'
FROM stg_customer s
WHERE NOT EXISTS (SELECT 1 FROM hub_customer h WHERE h.customer_hk = md5(upper(trim(s.customer_id))));

SELECT customer_id, left(customer_hk, 8) AS hk_prefix FROM hub_customer ORDER BY customer_id;

 customer_id | hk_prefix
-------------+-----------
 C1          | 1a2ddc2d
 C2          | f1a543f5

Common mistakes

• Normalising business keys differently in different loaders, so one customer gets two hub keys.
• One huge satellite per hub, so every small change copies dozens of unchanged columns.
• Letting analysts query the vault directly instead of building marts on top of it.
• Choosing Data Vault for a small, stable platform where its table count is pure overhead.