Data Vault 2.0

Data Vault is a way to build a warehouse from many source systems. The chapter Inmon vs Kimball vs Data Vault gave the overview. This chapter shows how each part works.

Recap in one line: a hub holds business keys, a link holds relationships, and a satellite holds details and history. Data Vault 2.0 also describes a way of working. This chapter covers the table design.

The raw vault keeps data as the sources sent it. The business vault adds tables built by business rules. Marts sit on top, and people query them.

You can use earlier chapters here: Keys & Identity, Grain: What One Row Means and Slowly Changing Dimensions (SCD 0–7).

The story is the shop again. It has customers 1 to 5 and orders 101 to 106. Asha moves from IN to DE on 2026-01-08.

A second source joins: a CRM (customer relationship management) tool with its own customer codes, such as A-301. The CRM rows, and a few other rows, are made up. The text says so each time.

Hash keys in the tables are real MD5 values, cut to the first 8 characters.

What you will learn

1. Why you would pay for a vault, and what changes when a source changes.
2. Hubs, links and satellites, with real rows and loads.
3. Hash keys, hash diffs, and why loads can run at the same time.
4. Same-as links, for one person with two keys.
5. Multi-active and effectivity satellites.
6. PIT tables, bridge tables and marts, which make a vault easy to read.
7. The costs, and when to skip Data Vault.

Take Asha. The shop stores her as customer 1. The CRM stores her as A-301, with the country written as Germany.

Data Vault is built for three pressures:

• Audit: you must prove what arrived, from which system, and when.
• Change: sources add columns, and new sources join.
• Parallel loading: many loads must run at the same time.

Each pressure shapes the design. Rows are never updated or deleted, so you can prove the past. New sources add tables, and hash keys let loads run without waiting for each other.

Now see what happens when something changes:

A hub is a table of business keys. A business key is the id the business uses, such as customer_id 1. A hub holds the key and nothing that changes.

Every hub row has four columns:

• customer_hk: the hash key, a code made from the business key.
• customer_bk: the business key itself, as text.
• load_date: the day the warehouse first saw this key.
• record_source: the system that sent it.

A hash function turns any text into a fixed-length code. MD5 gives 32 letters and digits, and the same text always gives the same code. Tables here show the first 8 characters.

This is hub_customer after both sources loaded. The shop loaded on 2026-01-01 and the CRM on 2026-01-12:

Asha has two hub rows, because the two systems use two keys. A later section shows how the vault says they are one person.

Load a hub

The loader adds a row only for a key the hub does not have yet. It cleans the key first: it removes spaces at the start and end, and uses capital letters. The CRM export of 2026-01-12 repeats one code, typed badly:

INSERT INTO hub_customer (customer_hk, customer_bk, load_date, record_source)
SELECT DISTINCT
       md5(UPPER(TRIM(s.contact_code))),
       UPPER(TRIM(s.contact_code)),
       DATE '2026-01-12',
       'crm'
FROM stage_crm_contacts AS s
WHERE NOT EXISTS (
  SELECT 1 FROM hub_customer AS h
  WHERE h.customer_hk = md5(UPPER(TRIM(s.contact_code)))
);

SELECT LEFT(customer_hk, 8) AS hash_8, customer_bk, load_date, record_source
FROM hub_customer
WHERE record_source = 'crm'
ORDER BY customer_bk;

A hub row never changes after it is inserted. A key that stops being used stays in the hub.

A link is a table of relationships between hubs. One link row says: this customer placed this order.

A link row has its own hash key, the hash keys of the hubs it joins, a load_date and a record_source. Its own hash key comes from the business keys joined with a separator, such as 1|101.

This is link_customer_order for the six shop orders. The load_date is the order date, to keep the story short:

A link holds no details. Details about the relationship go in a satellite on the link. A later section shows one.

Many-to-many by design

A link has no rule such as "one customer per order". Many customers can point to many orders. A table with one customer_id column per order would need a schema change to allow two customers.

Suppose a made-up order 301 on 2026-01-22 is shared by customers 3 (Carla) and 5 (Omar). The loader inserts two link rows, and no table changes shape. This query finds orders with more than one customer:

SELECT o.order_bk, COUNT(*) AS customers
FROM link_customer_order AS l
JOIN hub_order AS o ON o.order_hk = l.order_hk
GROUP BY o.order_bk
HAVING COUNT(*) > 1;

Each combination of customer and order appears once. Before an insert, the loader checks the link hash key.

A satellite holds the details of a hub or link, and their history. Details are the columns that change, such as name and country.

Each satellite row has these columns:

• The hash key of its hub or link.
• load_date: when the warehouse loaded this version.
• record_source: the system that sent it.
• hash_diff: a hash of all the detail columns.
• The detail columns themselves.

This is sat_customer_details after the load of 2026-01-08. Asha has two rows, because she moved:

The CRM gets its own satellite. The two loads never touch each other. The CRM keeps its own words, so the country is Germany, not DE:

What hash_diff does

The hash_diff is a hash of the detail columns, joined with a separator. The loader compares it with the newest row for the same key. The same value means no change, so nothing is inserted.

The shop sends all five customers again on 2026-01-08. The stage table already holds the hash key and hash_diff:

WITH latest AS (
  SELECT customer_hk, hash_diff
  FROM sat_customer_details AS d
  WHERE load_date = (SELECT MAX(load_date) FROM sat_customer_details AS x
                     WHERE x.customer_hk = d.customer_hk)
)
INSERT INTO sat_customer_details
  (customer_hk, load_date, record_source, hash_diff, name, country)
SELECT s.customer_hk, DATE '2026-01-08', 'shop_db', s.hash_diff, s.name, s.country
FROM stage_shop_customers AS s
LEFT JOIN latest AS l ON l.customer_hk = s.customer_hk
WHERE l.customer_hk IS NULL OR l.hash_diff <> s.hash_diff;

SELECT name, country, load_date
FROM sat_customer_details
WHERE load_date = DATE '2026-01-08';

An older design uses a sequence key: the hub counts up and gives each new business key the next number. A hash key is computed from the business key instead. Data Vault 2.0 uses hash keys.

Look at what the link loader needs in each design:

Here the link loader gets orders 101 to 103 and computes both keys itself:

SELECT order_id,
       customer_id,
       LEFT(md5(order_id::text), 8)    AS order_hk,
       LEFT(md5(customer_id::text), 8) AS customer_hk
FROM orders
WHERE order_id <= 103
ORDER BY order_id;

Every loader must hash the same way

The price is discipline. Here is one CRM code written four ways:

SELECT kind, LEFT(md5(code), 8) AS hash_8
FROM (VALUES
  ('capitals',     'A-301'),
  ('lowercase',    'a-301'),
  ('space before', ' A-301'),
  ('space after',  'A-301 ')
) AS t(kind, code);

Write one hashing rule and share it. Trim spaces, use capitals, and turn NULL into empty text. Join columns with a fixed separator.

Without a separator, keys 1 and 11 give 111, and so do 11 and 1. With the separator |, they give 1|11 and 11|1.

Two different keys can give the same hash. This is called a collision. It is very rare, and some teams choose a longer hash, such as SHA-256, to lower the risk.

Without a lookup, a link row can arrive before its hub row. After each load, check that every hash key in a link or satellite has a hub row.

The hub has two rows for Asha: customer 1 and A-301. The raw vault keeps both, as each source sent them. It does not guess that they are one person.

A same-as link says that two hub rows are the same thing. It holds the hash key of the main row and the hash key of the duplicate. Its own link hash key is left out to stay short.

Deciding the match is a separate job. The chapter Master Data Management (MDM) teaches how to do it.

A rule creates a same-as link, so it usually lives in the business vault. Here are two made-up matches:

Priya (A-303) is only in the CRM, so she has no row. This query joins the hub twice, once for each side:

SELECT m.customer_bk AS shop_id, d.customer_bk AS crm_code
FROM link_same_as_customer AS l
JOIN hub_customer AS m ON m.customer_hk = l.main_hk
JOIN hub_customer AS d ON d.customer_hk = l.duplicate_hk
ORDER BY shop_id;

The CRM also writes the country as a word, and the shop uses a code. A reference table maps one to the other. It is a small lookup table that sits outside hubs, links and satellites:

Most satellites hold one current row for each key. Two cases need something more.

Multi-active satellite

Sometimes one key has several active rows at once. Asha has a home phone and a work phone, so the satellite key gets one more column, phone_type. The shop sends the full set each time, and these rows are made up:

SELECT phone_type, phone
FROM sat_customer_phone
WHERE customer_hk = 'c4ca4238a0b923820dcc509a6f75849b'
  AND load_date = (
    SELECT MAX(load_date) FROM sat_customer_phone
    WHERE customer_hk = 'c4ca4238a0b923820dcc509a6f75849b'
  );

Effectivity satellite

A link row never ends, because the vault never deletes rows. An effectivity satellite says when the relationship was valid. In this made-up case, sales rep R-310 had Asha from 2026-01-12, and R-311 from 2026-01-20:

The effectivity satellite has three rows. The second row closes the first link. It is a new row, not an update:

Today’s rep is the link whose newest effectivity row ends on 9999-12-31. That is 9bf9d9ec, so R-311.

A question about one customer now touches a hub and several satellites. For each satellite, you must find the row that was current on a date. That is slow and easy to get wrong.

A point-in-time (PIT) table does this work in advance. It holds one row for each customer and snapshot date. Each row names the satellite row that was current, by its key and load_date.

The shop also sends marketing choices, so customers get a second satellite. It leaves out hash_diff to stay short, and its rows are made up.

The satellite also holds one ghost row, an empty row that means "no data yet". Its key is all zeros, and its load_date is 1900-01-01:

The PIT points at it for every customer with no row, so the joins keep Carla, Dev and Omar.

For snapshot 2026-01-10, Asha’s row would point at 2026-01-08 and 2026-01-01. This is pit_customer for the snapshot date 2026-01-22:

Now the join needs no "latest row" subquery. Each satellite joins on two equal columns:

SELECT s.name, s.country, m.opt_in
FROM pit_customer AS p
JOIN sat_customer_details   AS s
  ON s.customer_hk = p.customer_hk AND s.load_date = p.details_load_date
JOIN sat_customer_marketing AS m
  ON m.customer_hk = p.marketing_hk AND m.load_date = p.marketing_load_date
WHERE p.snapshot_date = DATE '2026-01-22'
ORDER BY s.name;

A PIT adds one join, but each join is simple. The gain is that no query has to search for the latest row.

Bridge tables

A bridge table does the same for a path across hubs. It stores the result of joining hubs and links in advance. This is bridge_customer_order, built from the link, with the business keys added:

A report can now go from customer to order with no hub joins. A bridge can also cover several links in a row. This one has a single link, to stay short.

People read marts, not the raw vault. A mart is often a Kimball star, and PIT and bridge tables build it fast. A fact table can be built from a link and its satellite.

Here is a small customer summary. It uses the PIT for the current name and country, the bridge for orders, and the order satellite for status and amount:

SELECT s.name, s.country,
       COUNT(o.order_hk)             AS paid_orders,
       COALESCE(SUM(o.amount), 0.00) AS paid_revenue
FROM pit_customer AS p
JOIN sat_customer_details AS s
  ON s.customer_hk = p.customer_hk AND s.load_date = p.details_load_date
LEFT JOIN bridge_customer_order AS b ON b.customer_hk = p.customer_hk
LEFT JOIN sat_order_shop AS o
  ON o.order_hk = b.order_hk AND o.status = 'paid'
WHERE p.snapshot_date = DATE '2026-01-22'
GROUP BY s.name, s.country
ORDER BY s.name;

A mart can be a view or a table. If it is wrong, fix the rule and rebuild it. The raw vault still holds every source row.

Each order here has one satellite row. With more rows, use a PIT on orders too.

Count the tables. In the SQL chapters, the shop has 2 tables: customers and orders. In the raw vault they become 5: hub_customer, hub_order, link_customer_order, sat_customer_details and sat_order_shop.

Skip Data Vault in these cases:

• You have one or two stable sources.
• No one asks you to prove the past.
• A small team needs a first report fast.
• People will query the tables directly.

A Kimball star with SCD Type 2 is simpler there. Many teams use both: a vault to join the sources, and stars to read the data.

A hub stores business keys. A link stores relationships, many-to-many by design. A satellite stores details and history, and it only gets new rows.

Hash keys come from the business key, so loaders need no lookup. Hash diffs let a loader skip rows that did not change.

A same-as link joins two keys for one thing. The matching rules come from MDM.

PIT and bridge tables make reads fast, and marts are what people query.

Check yourself

• Why does a hash key let a link load at the same time as a hub?
• Dev has no country. What goes wrong if you hash without COALESCE?
• The CRM sends A-301, and the shop sends 1. Where does the vault say they match?
• Name two cases where you would not use Data Vault.

Each exercise opens a canvas for an entity-relationship diagram (ERD). You draw the tables, and the review checks them against sample rows.

Start at the top. The last two are optional.