Your source systems change every quarter. A new CRM arrives, a billing platform is replaced, an acquisition brings a third customer database, and every change ripples through a carefully built star schema. Data Vault 2.0 was designed for exactly this problem. It separates what a business entity is from how entities relate and from what we know about them over time, so new sources are added rather than retrofitted.
This guide is for data engineers who build warehouses or lakehouses on Snowflake, Databricks, BigQuery or Microsoft Fabric. It explains the three core building blocks, shows the SQL to create and load them, and covers the query-side helpers (PIT and bridge tables) and the mistakes that make Data Vault projects slow and painful.
The three building blocks
A raw Data Vault has only three table types. Everything else is built from them.
Hubs: the business keys
A hub holds the unique list of business keys for one core concept, such as customer, product or order. A business key is the identifier the business actually uses (a customer number or an order reference), not a surrogate ID generated by one application. A hub row never changes once it is written. Its columns are the hash key, the business key, the load date and the record source.
Links: the relationships
A link records that two or more business keys were seen together, for example customer placed order. Links are always many-to-many, even when today’s source enforces one-to-many. That one decision is what lets the model absorb a change in business rules without restructuring tables.
Satellites: the history
A satellite hangs off one hub or one link and stores the descriptive attributes (name, email, status, amount) with full history. Each change creates a new row; nothing is updated in place. Satellites are usually split by source system and by rate of change, so a fast-moving status column does not force you to copy slow-moving address columns every day.
Hash keys, load dates and record sources
Data Vault 2.0 replaced the sequence-based surrogate keys of Data Vault 1.0 with hash keys: a deterministic hash (commonly MD5 or SHA-256) of the cleaned business key. Because the same input always gives the same hash, hubs, links and satellites can be loaded in parallel with no lookups between them, and loads from different platforms produce matching keys.
Two more standard columns appear on every table:
- load_dts: when the row arrived in the vault, set by the load process (not the source’s own timestamp).
- record_source: where the row came from, such as
crm.customers. This gives you audit and lineage for free.
Satellites add a hashdiff: a hash of all the descriptive columns. Comparing one hash is far cheaper than comparing twenty columns when you check whether anything changed.
Creating the tables
The DDL below uses Snowflake-style SQL, but it maps directly to Databricks SQL, BigQuery or a Fabric Warehouse. Hash keys are stored as fixed-length strings.
CREATE TABLE raw_vault.hub_customer (
customer_hk CHAR(32) NOT NULL, -- MD5 of business key
customer_number VARCHAR(50) NOT NULL, -- business key
load_dts TIMESTAMP_NTZ NOT NULL,
record_source VARCHAR(100) NOT NULL
);
CREATE TABLE raw_vault.link_customer_order (
customer_order_hk CHAR(32) NOT NULL, -- hash of both business keys
customer_hk CHAR(32) NOT NULL,
order_hk CHAR(32) NOT NULL,
load_dts TIMESTAMP_NTZ NOT NULL,
record_source VARCHAR(100) NOT NULL
);
CREATE TABLE raw_vault.sat_customer_crm (
customer_hk CHAR(32) NOT NULL,
load_dts TIMESTAMP_NTZ NOT NULL,
hashdiff CHAR(32) NOT NULL,
customer_name VARCHAR(200),
email VARCHAR(200),
status VARCHAR(20),
record_source VARCHAR(100) NOT NULL
);Loading a hub and a satellite
Every load starts from a staging view that cleans the business key and computes the hashes once. Cleaning matters: trim spaces and standardise case before hashing, and use a delimiter between columns so that 'AB' || 'C' and 'A' || 'BC' do not collide.
CREATE OR REPLACE VIEW stage.stg_crm_customers AS
SELECT
MD5(UPPER(TRIM(customer_number))) AS customer_hk,
UPPER(TRIM(customer_number)) AS customer_number,
MD5(CONCAT_WS('||',
COALESCE(TRIM(customer_name), ''),
COALESCE(LOWER(TRIM(email)), ''),
COALESCE(UPPER(TRIM(status)), ''))) AS hashdiff,
customer_name, email, status,
CURRENT_TIMESTAMP() AS load_dts,
'crm.customers' AS record_source
FROM landing.crm_customers;The hub load is insert-only: add business keys the hub has not seen before.
INSERT INTO raw_vault.hub_customer (customer_hk, customer_number, load_dts, record_source)
SELECT customer_hk, customer_number, MIN(load_dts), MIN(record_source)
FROM stage.stg_crm_customers s
WHERE NOT EXISTS (
SELECT 1 FROM raw_vault.hub_customer h
WHERE h.customer_hk = s.customer_hk
)
GROUP BY customer_hk, customer_number;The satellite load inserts a row only when the hashdiff differs from the latest row for that key. This is what keeps satellites small: an unchanged customer adds nothing, however often you reload.
INSERT INTO raw_vault.sat_customer_crm
(customer_hk, load_dts, hashdiff, customer_name, email, status, record_source)
WITH latest AS (
SELECT customer_hk, hashdiff
FROM raw_vault.sat_customer_crm
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_hk ORDER BY load_dts DESC) = 1
)
SELECT s.customer_hk, s.load_dts, s.hashdiff,
s.customer_name, s.email, s.status, s.record_source
FROM stage.stg_crm_customers s
LEFT JOIN latest l
ON l.customer_hk = s.customer_hk
WHERE l.customer_hk IS NULL -- brand new key
OR l.hashdiff <> s.hashdiff; -- attributes changedQUALIFY works in Snowflake, Databricks SQL and BigQuery. On platforms without it, wrap the window function in a subquery. Links load exactly like hubs: insert the combined hash key if it is not already present.
Querying the vault: PIT and bridge tables
Data Vault is built for loading and auditing, not for dashboards. Reporting users should never join raw satellites themselves. Two helper structures, usually kept in a business vault layer, make querying fast:
- Point-in-time (PIT) tables store, for each hub key and snapshot date, the load_dts of the valid row in each satellite. A query then uses simple equi-joins instead of range joins across several satellites.
- Bridge tables pre-join a chain of hubs and links (for example customer → order → product) so marts do not repeat the same multi-hop join.
A minimal daily PIT build for one satellite looks like this:
INSERT INTO business_vault.pit_customer (customer_hk, snapshot_date, sat_crm_load_dts)
SELECT h.customer_hk,
d.snapshot_date,
MAX(s.load_dts) AS sat_crm_load_dts
FROM raw_vault.hub_customer h
CROSS JOIN (SELECT CURRENT_DATE() AS snapshot_date) d
LEFT JOIN raw_vault.sat_customer_crm s
ON s.customer_hk = h.customer_hk
AND s.load_dts < DATEADD(day, 1, d.snapshot_date)
GROUP BY h.customer_hk, d.snapshot_date;On top of PITs and bridges you build information marts: ordinary star schemas, often as views, that BI tools and analysts use. Data Vault does not replace dimensional modelling; it sits underneath it.
Data Vault vs star schema
| Aspect | Data Vault 2.0 | Star schema (Kimball) |
|---|---|---|
| Main purpose | Integration, history and audit | Fast, simple reporting |
| Adding a new source | New satellites and links; existing tables untouched | Often changes dimensions and fact grain |
| Loading | Insert-only, highly parallel | Updates for SCD Type 1/2, ordered loads |
| Number of tables | High | Low |
| Query complexity | High without PIT and bridge tables | Low |
| Best fit | Many changing sources, regulated industries | Stable sources, small teams, BI-first |
A common lakehouse pattern is raw Data Vault in the silver layer and star-schema marts in gold. If you have one or two stable sources, a well-built star schema with SCD Type 2 dimensions is usually enough.
Automating it with dbt
Data Vault is very repetitive: every hub load looks the same, as does every satellite load. That makes it ideal for code generation. In dbt, the open-source AutomateDV package (formerly dbtvault) provides macros for staging, hubs, links, satellites and PIT tables, so each model is a few lines of metadata. Pair it with dbt tests for uniqueness of hash keys and not-null business keys, and you catch most load errors before they reach the marts.
Common pitfalls
- Using source surrogate IDs as business keys. If the CRM’s internal ID changes after a migration, your hub breaks. Use the key the business recognises.
- Inconsistent hashing. One pipeline trims and upper-cases, another does not, and the same customer gets two hash keys. Put the hashing rules in one shared macro or view.
- NULLs in hash inputs.
CONCATwith a NULL can return NULL on some platforms. AlwaysCOALESCEto an empty string or a fixed placeholder. - One giant satellite per hub. Splitting by source and by rate of change keeps satellites narrow and cuts storage.
- Letting analysts query the raw vault. Without PITs, bridges and marts, queries become slow multi-way range joins and people lose trust in the model.
- Putting business rules in the raw vault. The raw vault should store source data as received. Derived values and cleansing rules belong in the business vault, so you can change them and rebuild.
Key takeaways
- Hubs hold business keys, links hold relationships, satellites hold history. Everything is insert-only.
- Hash keys let you load hubs, links and satellites in parallel; a hashdiff makes change detection cheap.
- Standardise and delimit inputs before hashing, and handle NULLs explicitly.
- Use PIT and bridge tables, then star-schema marts, for reporting.
- Choose Data Vault when sources are many and changing, and audit matters. For a small, stable estate, a star schema is simpler.
- Generate the repetitive SQL with tools such as AutomateDV rather than writing it by hand.
