In this post we are going to cover Data Vault, and answer the interview question: what is a Data Vault, and what are hubs, links and satellites?
Data Vault is a modeling method for the integration layer of a warehouse. It splits the data into three kinds of tables: hubs for business keys, links for relationships between those keys, and satellites for the descriptive attributes and their history. Every load only inserts rows, and every row records when it was loaded and which source it came from.
We will build a tiny vault with SQL, load a change the Data Vault way, and then build a simple reporting view on top of it.
Question 42 of the Real DWH Question and Answers series on TechBrothersIT.
The interview answer
Data Vault is a modeling method for the integration layer of a warehouse, built for many sources, change and auditability. Hubs hold business keys, links hold relationships between hubs, and satellites hold descriptive attributes with history. Loads are insert-only with load dates and record sources, and users query star schemas built on top.
The problem Data Vault solves
Large companies have dozens of sources (CRM, ERP, POS, web), and those sources keep changing: new systems, new columns, acquisitions. In a normalized or star model, each change can force us to redesign existing tables.
- Many sources - everything has to be integrated in one place.
- Constant change - new systems and new columns arrive all the time.
- Auditability - auditors want to know what arrived, from which source, and when.
Loads also need to run in parallel at scale. Data Vault answers all of this by splitting keys, relationships and attributes into separate tables, so a new source or a new attribute becomes a new table, and the existing tables never change.
Hubs, links and satellites
- Hub - one row per business key, like a customer ID or a product ID. Just the key, a load date and the record source. Hubs almost never change.
- Link - one row per relationship between hubs, like a sale that connects a customer and a product. Links are always many-to-many, so a change in business rules does not break them.
- Satellite - hangs off a hub or a link and holds the descriptive attributes, like name and city, with a new row for every change.
Let's create some sample data
We create two hubs (hub_customer, hub_product), one link (link_sale) and one satellite (sat_customer). Data Vault 2.0 uses a hash of the business key as the hub key; the script uses small integers so it runs on any database.
DROP VIEW IF EXISTS dim_customer_current;
DROP TABLE IF EXISTS sat_customer;
DROP TABLE IF EXISTS link_sale;
DROP TABLE IF EXISTS hub_product;
DROP TABLE IF EXISTS hub_customer;
-- HUBS: one row per business key, nothing else
-- (Data Vault 2.0 uses a hash of the business key as the hub key;
-- we use small integers so the script runs on any database)
CREATE TABLE hub_customer (customer_hk INT PRIMARY KEY, customer_id VARCHAR(10), load_date DATE, record_source VARCHAR(10));
CREATE TABLE hub_product (product_hk INT PRIMARY KEY, product_id VARCHAR(10), load_date DATE, record_source VARCHAR(10));
INSERT INTO hub_customer VALUES (1, 'C101', '2025-01-01', 'CRM'), (2, 'C102', '2025-01-01', 'CRM');
INSERT INTO hub_product VALUES (1, 'P10', '2025-01-01', 'ERP'), (2, 'P20', '2025-01-01', 'ERP');
-- LINK: one row per relationship between hubs (here: a sale of a product to a customer)
CREATE TABLE link_sale (sale_hk INT PRIMARY KEY, customer_hk INT, product_hk INT, load_date DATE, record_source VARCHAR(10));
INSERT INTO link_sale VALUES
(1, 1, 1, '2026-02-10', 'POS'),
(2, 2, 2, '2026-02-15', 'POS'),
(3, 1, 2, '2026-04-02', 'WEB');
-- SATELLITE: descriptive attributes with full history, insert-only
CREATE TABLE sat_customer (
customer_hk INT,
load_date DATE,
name VARCHAR(50),
city VARCHAR(50),
record_source VARCHAR(10),
PRIMARY KEY (customer_hk, load_date)
);
INSERT INTO sat_customer VALUES
(1, '2025-01-01', 'Sara Khan', 'Toronto', 'CRM'),
(1, '2026-03-20', 'Sara Khan', 'Vancouver', 'CRM'),
(2, '2025-01-01', 'Omar Ali', 'Dallas', 'CRM');The customer hub has two business keys, C101 and C102, each with a load date and its record source, the CRM.
SELECT * FROM hub_customer ORDER BY 1;| customer_hk | customer_id | load_date | record_source |
|---|---|---|---|
| 1 | C101 | 2025-01-01 | CRM |
| 2 | C102 | 2025-01-01 | CRM |
The sale link connects customers and products. Look at the record source: two sales came from the store POS and one from the WEB shop. The vault integrates both.
SELECT * FROM link_sale ORDER BY 1;| sale_hk | customer_hk | product_hk | load_date | record_source |
|---|---|---|---|---|
| 1 | 1 | 1 | 2026-02-10 | POS |
| 2 | 2 | 2 | 2026-02-15 | POS |
| 3 | 1 | 2 | 2026-04-02 | WEB |
The customer satellite holds the history. Sara Khan has two rows: Toronto from 2025-01-01, then Vancouver from 2026-03-20. Omar Ali has one row, in Dallas.
SELECT * FROM sat_customer ORDER BY 1;| customer_hk | load_date | name | city | record_source |
|---|---|---|---|---|
| 1 | 2025-01-01 | Sara Khan | Toronto | CRM |
| 1 | 2026-03-20 | Sara Khan | Vancouver | CRM |
| 2 | 2025-01-01 | Omar Ali | Dallas | CRM |
Step 1: A change arrives, and we only insert
Now Omar moves from Dallas to Austin. In a Type 2 dimension, we would expire the old row with an UPDATE and then insert a new one. In a Data Vault, we only insert a new satellite row with the new load date.
INSERT INTO sat_customer VALUES
(2, DATE '2026-06-01', 'Omar Ali', 'Austin', 'CRM');Now let's check the result.
SELECT * FROM sat_customer ORDER BY customer_hk, load_date;| customer_hk | load_date | name | city | record_source |
|---|---|---|---|---|
| 1 | 2025-01-01 | Sara Khan | Toronto | CRM |
| 1 | 2026-03-20 | Sara Khan | Vancouver | CRM |
| 2 | 2025-01-01 | Omar Ali | Dallas | CRM |
| 2 | 2026-06-01 | Omar Ali | Austin | CRM |
Omar now has two satellite rows: Dallas from 2025-01-01 and Austin from 2026-06-01. Nothing was updated or deleted. Insert-only loads are simple, fast and easy to run in parallel, and every row that ever arrived is still there for the auditors.
Step 2: Build an information mart on top
Users do not query hubs and satellites directly. That would mean many joins and date logic in every report. So we build information marts on top of the vault: views or tables shaped like star schema dimensions and facts. Let's build a view with the current version of every customer, which is the satellite row with the latest load date.
CREATE VIEW dim_customer_current AS
SELECT h.customer_id, s.name, s.city
FROM hub_customer h
JOIN sat_customer s ON s.customer_hk = h.customer_hk
WHERE s.load_date = (SELECT MAX(s2.load_date)
FROM sat_customer s2
WHERE s2.customer_hk = s.customer_hk);Now let's check the result.
SELECT * FROM dim_customer_current ORDER BY customer_id;| customer_id | name | city |
|---|---|---|
| C101 | Sara Khan | Vancouver |
| C102 | Omar Ali | Austin |
C101 Sara Khan is in Vancouver, and C102 Omar Ali is in Austin. The view is simple for users, and every value can be traced back to the vault.
Data Vault vs star schema
Data Vault does not replace star schemas. The two work together, each in its own layer.
| Data Vault | Star schema |
|---|---|
| integration layer (silver) | presentation layer (gold) |
| built for loading, change, audit | built for querying |
Data Vault is the integration layer, like silver in the medallion architecture. Kimball stars are the presentation layer, like gold.
Follow-up questions interviewers ask
Why are links always many-to-many?
So a change in business rules (one-to-many becoming many-to-many) never forces a redesign.
Why hash keys in Data Vault 2.0?
Keys can be computed from the business key in parallel by every load, without looking up a sequence.
Is Data Vault a replacement for star schemas?
No: it is the integration layer; star schemas (information marts) are still built on top for users.
The full script
Here is everything from this post in one script. Copy it and run it top to bottom.
-- ========================================================================
-- Data Warehouse Interview Q&A #42 - Data Vault: hubs, links and satellites
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example: CREATE SCHEMA dwh_q42; SET search_path TO dwh_q42;
-- SQL Server: write DATE '2026-06-01' as CAST('2026-06-01' AS DATE).
-- ========================================================================
-- Step 0: create and load the sample data
DROP VIEW IF EXISTS dim_customer_current;
DROP TABLE IF EXISTS sat_customer;
DROP TABLE IF EXISTS link_sale;
DROP TABLE IF EXISTS hub_product;
DROP TABLE IF EXISTS hub_customer;
-- HUBS: one row per business key, nothing else
-- (Data Vault 2.0 uses a hash of the business key as the hub key;
-- we use small integers so the script runs on any database)
CREATE TABLE hub_customer (customer_hk INT PRIMARY KEY, customer_id VARCHAR(10), load_date DATE, record_source VARCHAR(10));
CREATE TABLE hub_product (product_hk INT PRIMARY KEY, product_id VARCHAR(10), load_date DATE, record_source VARCHAR(10));
INSERT INTO hub_customer VALUES (1, 'C101', '2025-01-01', 'CRM'), (2, 'C102', '2025-01-01', 'CRM');
INSERT INTO hub_product VALUES (1, 'P10', '2025-01-01', 'ERP'), (2, 'P20', '2025-01-01', 'ERP');
-- LINK: one row per relationship between hubs (here: a sale of a product to a customer)
CREATE TABLE link_sale (sale_hk INT PRIMARY KEY, customer_hk INT, product_hk INT, load_date DATE, record_source VARCHAR(10));
INSERT INTO link_sale VALUES
(1, 1, 1, '2026-02-10', 'POS'),
(2, 2, 2, '2026-02-15', 'POS'),
(3, 1, 2, '2026-04-02', 'WEB');
-- SATELLITE: descriptive attributes with full history, insert-only
CREATE TABLE sat_customer (
customer_hk INT,
load_date DATE,
name VARCHAR(50),
city VARCHAR(50),
record_source VARCHAR(10),
PRIMARY KEY (customer_hk, load_date)
);
INSERT INTO sat_customer VALUES
(1, '2025-01-01', 'Sara Khan', 'Toronto', 'CRM'),
(1, '2026-03-20', 'Sara Khan', 'Vancouver', 'CRM'),
(2, '2025-01-01', 'Omar Ali', 'Dallas', 'CRM');
-- Step 1: A change arrives: Omar moves. Data Vault only INSERTS a new satellite row
INSERT INTO sat_customer VALUES
(2, DATE '2026-06-01', 'Omar Ali', 'Austin', 'CRM');
-- check the result
SELECT * FROM sat_customer ORDER BY customer_hk, load_date;
-- Step 2: Build a reporting view (information mart): the current version of each customer
CREATE VIEW dim_customer_current AS
SELECT h.customer_id, s.name, s.city
FROM hub_customer h
JOIN sat_customer s ON s.customer_hk = h.customer_hk
WHERE s.load_date = (SELECT MAX(s2.load_date)
FROM sat_customer s2
WHERE s2.customer_hk = s.customer_hk);
-- check the result
SELECT * FROM dim_customer_current ORDER BY customer_id;
Key points
- Hubs: business keys
- Links: relationships between hubs
- Satellites: attributes with history
- Insert-only, with load date and record source
- Users query stars built on top
Question 43 — One Big Table vs star schema One big table (OBT) or star schema: which is better?
Every query and every result on this page was run on PostgreSQL 16 using the script above. The SQL is standard and runs on most databases with small changes (noted in the script). All tables and data are invented demo data for teaching.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.