Data Vault is a modelling method for the integration layer of a warehouse: built to absorb many changing sources, keep full history and stay auditable. This page explains its three table types, hash keys and loading patterns with runnable SQL.
Data Vault is a data warehouse modelling method created by Dan Linstedt; Data Vault 2.0 (2013) added hash keys, loading standards and an architecture and methodology around the model. Its aim is an integration layer that absorbs many changing source systems, keeps complete history, and remains auditable, while letting teams add new sources without reworking what already exists. It uses exactly three table types.
Classic dimensional models are optimised for querying, and they bake business rules into the structure: which source wins for a customer's address, how an order status maps to "completed". When a new source arrives or a rule changes, star schemas often need redesign and historical reloads. 3NF enterprise models are rigorous but tightly coupled; adding a relationship can ripple through many tables.
Data Vault separates concerns. The raw vault stores what every source said, when, unchanged and insert-only, which gives a complete audit trail and makes loads repeatable. Business rules live downstream in the business vault and the marts, which can be rebuilt from the vault at any time. New sources add new satellites and links without touching existing tables. That makes it popular in regulated industries such as banking, insurance and healthcare, and in enterprises with dozens of source systems.
Data flows from sources into staging, where hash keys and hash diffs are computed, then into the raw vault. The business vault adds derived structures: point-in-time (PIT) tables that record, for each hub key and snapshot date, which satellite row was current, and bridge tables that pre-join hubs and links for performance. Information marts, usually star schemas, are built on top for analysts.
Data Vault 2.0 replaced sequence surrogate keys with deterministic hash keys: customer_hk = MD5(UPPER(TRIM(customer_id))), or SHA-1/SHA-256. Because every loader can compute the same key from the business key alone, hubs, links and satellites can load in parallel without lookups, and keys match across platforms. Composite keys are concatenated with a delimiter before hashing so that ("ab","c") and ("a","bc") differ. A hash diff is a hash over all descriptive columns of a satellite, used to detect change cheaply.
Every load is insert-only and idempotent. Hubs: insert business keys not already present. Links: insert key combinations not already present. Satellites: for each staged row, compare its hash diff with the most recent satellite row for the same parent key; insert only if it differs. Rerunning a load inserts nothing new, which makes recovery simple. Deletions in the source are recorded with status-tracking or effectivity satellites rather than by deleting rows.
Querying the vault. Reconstructing a customer as of a date means finding, for each satellite, the latest row with load_dts at or before that date, then joining them all to the hub. With several satellites per hub this becomes a stack of window functions or correlated subqueries. A PIT table precomputes the answer: one row per hub key per snapshot date, holding the matching load_dts for every satellite, so the query becomes simple equality joins. Bridge tables do the same for paths across several links. Both are disposable and can be rebuilt from the raw vault, which is why they live in the business vault rather than the raw vault.
Real-time feeds fit the same pattern: a streaming job can compute hash keys and append to hubs, links and satellites continuously, because none of the loads depend on each other.
The script below is standard SQL that runs in DuckDB, Snowflake or Postgres (with md5 available). It builds a minimal customer and order vault, loads two batches, and queries current state.
-- Staging for one batch
CREATE TABLE stg_orders AS
SELECT * FROM (VALUES
('C42', 'Ana', 'ana@old.com', 'O1001', 'open', 120.00),
('C42', 'Ana', 'ana@old.com', 'O1002', 'open', 80.00),
('C77', 'Bo', 'bo@mail.com', 'O1003', 'shipped', 45.00)
) t(customer_id, name, email, order_id, status, amount);
CREATE TABLE hub_customer (customer_hk VARCHAR PRIMARY KEY, customer_id VARCHAR,
load_dts TIMESTAMP, record_source VARCHAR);
CREATE TABLE hub_order (order_hk VARCHAR PRIMARY KEY, order_id VARCHAR,
load_dts TIMESTAMP, record_source VARCHAR);
CREATE TABLE lnk_customer_order (link_hk VARCHAR PRIMARY KEY, customer_hk VARCHAR,
order_hk VARCHAR, load_dts TIMESTAMP, record_source VARCHAR);
CREATE TABLE sat_customer_crm (customer_hk VARCHAR, load_dts TIMESTAMP, hash_diff VARCHAR,
name VARCHAR, email VARCHAR, PRIMARY KEY (customer_hk, load_dts));
-- Hub load: new business keys only
INSERT INTO hub_customer
SELECT DISTINCT md5(upper(trim(customer_id))), customer_id, now(), 'crm'
FROM stg_orders s
WHERE md5(upper(trim(customer_id))) NOT IN (SELECT customer_hk FROM hub_customer);
INSERT INTO hub_order
SELECT DISTINCT md5(upper(trim(order_id))), order_id, now(), 'erp'
FROM stg_orders WHERE md5(upper(trim(order_id))) NOT IN (SELECT order_hk FROM hub_order);
-- Link load: new relationships only
INSERT INTO lnk_customer_order
SELECT DISTINCT md5(upper(trim(customer_id)) || '||' || upper(trim(order_id))),
md5(upper(trim(customer_id))), md5(upper(trim(order_id))), now(), 'erp'
FROM stg_orders
WHERE md5(upper(trim(customer_id)) || '||' || upper(trim(order_id)))
NOT IN (SELECT link_hk FROM lnk_customer_order);
-- Satellite load: insert only when attributes changed
INSERT INTO sat_customer_crm
SELECT DISTINCT s.hk, now(), s.hd, s.name, s.email
FROM (SELECT md5(upper(trim(customer_id))) AS hk,
md5(coalesce(name,'') || '||' || coalesce(email,'')) AS hd, name, email
FROM stg_orders) s
LEFT JOIN (SELECT customer_hk, hash_diff,
row_number() OVER (PARTITION BY customer_hk ORDER BY load_dts DESC) AS rn
FROM sat_customer_crm) cur
ON cur.customer_hk = s.hk AND cur.rn = 1
WHERE cur.hash_diff IS DISTINCT FROM s.hd;
-- Current view of each customer (what a mart or PIT table would expose)
SELECT h.customer_id, s.name, s.email, s.load_dts
FROM hub_customer h
JOIN sat_customer_crm s ON s.customer_hk = h.customer_hk
QUALIFY row_number() OVER (PARTITION BY h.customer_hk ORDER BY s.load_dts DESC) = 1;Load a second batch in which Ana's email becomes ana@new.com and rerun the same inserts: the hubs and link gain nothing, and the satellite gains exactly one new row for C42. The old email remains, timestamped, which answers "what did we know, and when" for an auditor. Tools such as dbt with the AutomateDV or datavault4dbt packages generate these patterns from metadata.
| Aspect | Data Vault 2.0 | Kimball (star) | Inmon 3NF |
|---|---|---|---|
| Primary goal | Integration, history, auditability | Fast, simple analytics | Single enterprise model |
| Adding a source | New satellites and links, no rework | Often restructure dimensions | Model changes ripple |
| Business rules | Downstream (business vault, marts) | In the ETL into the star | In the ETL into 3NF |
| Query ease | Hard directly; needs marts or PITs | Easiest | Moderate, many joins |
| Table count | High | Low | Medium |
| Best fit | Many sources, regulation, change | Few sources, BI focus | Stable enterprise domains |
The approaches combine well: a common pattern is Data Vault as the integration layer and Kimball star schemas as the presentation layer.