Data Vault 2.0 for Data Engineering: Hubs, Links, Satellites, Hash Keys & Automation

data vault is the modeling methodology enterprises reach for when a Kimball star schema and a 3NF operational model both start to buckle under the weight of a dozen source systems, a compliance auditor who wants to know exactly which record came from where and when, and a nightly load window that refuses to shrink no matter how many warehouses you throw at it. It is not a replacement for the star schema your BI team queries — it sits underneath it as the integration and history layer, absorbing every source system as-is, tracking every version of every attribute forever, and never once issuing an UPDATE or DELETE against a business row. That last property — an insert-only load philosophy — is what lets a Data Vault warehouse load a hundred tables in parallel across a hundred sources without a single ordering dependency between them.

This guide is the data-engineer's walkthrough of data vault 2.0 you wished existed the first time an interviewer said "explain the difference between a hub, a link, and a satellite," or "why do you hash the business key instead of using an identity column," or "how do you serve a star schema on top of a vault without the join fan-out killing your query." It works through the three structures that make up the model — hubs links satellites — the hash keys that make loads deterministic and parallel, the hashdiff trick that turns a satellite into a slowly-changing dimension, the split between the raw vault (source data untouched) and the business vault (computed PITs and bridges), and the data vault automation frameworks (dbt, datavault4dbt, AutomateDV) that generate the repetitive load SQL for you. Each section pairs a teaching block with a Solution-Tail interview answer — real SQL, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the dimensional modeling practice library →, rehearse the DDL on the SQL practice library →, and sharpen the load-pipeline axis with the ETL practice library →.

---

On this page

• Why Data Vault exists: Kimball and 3NF break at scale
• Hubs and the hash-key foundation
• Links and many-to-many relationships
• Satellites: history, hashdiff, and multiple sources
• Raw vault, business vault, automation, and marts
• Cheat sheet — Data Vault 2.0 recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why Data Vault exists: Kimball and 3NF break at scale

The problem Data Vault was built to solve — auditability, multi-source integration, and parallel loads at once

The one-sentence invariant: Data Vault is a modeling methodology that separates business keys (hubs), relationships (links), and descriptive context (satellites) into distinct insert-only tables, so that an enterprise warehouse can absorb an unbounded number of source systems, keep a fully auditable history of every attribute forever, and load every table in parallel without ordering dependencies — trading query simplicity (you rebuild star schemas on top) for load agility and audit rigor. Kimball optimizes for the query; 3NF optimizes for the transaction; Data Vault optimizes for integration and history, then hands the query problem to a mart layer built on top. Understanding which of the three problems you actually have is the entire decision.

Why the two classic models buckle at enterprise scale.

• Kimball star schema — great to query, painful to integrate. A conformed-dimension star schema is the gold standard for BI, but conforming dimensions across a dozen source systems is a serial, human-intensive design exercise. Adding a new source means re-designing dimensions and re-loading facts; a type-2 dimension change ripples into every fact grain that references it; and a full-refresh load has ordering dependencies (dimensions before facts) that serialize the load window.
• 3NF / Inmon — great for OLTP, brittle for history. A normalized model minimizes redundancy for transactional writes, but it was never designed to keep every historical version of every row, and schema churn in a source system forces ALTER TABLEs that ripple through foreign keys. History, when bolted on, becomes a tangle of effective-dated tables.
• The enterprise reality. A large organization has 20–200 source systems, each with overlapping-but-inconsistent notions of "customer" and "product," each evolving on its own release cadence, and a compliance regime that demands the warehouse can reproduce exactly what any source said on any past date. Neither Kimball nor 3NF was built for that mix.

The four things Data Vault buys you.

• Auditability. Every row carries a load_date and a record_source. Nothing is ever updated in place, so the warehouse can reconstruct the exact state of any entity as of any past load — the compliance answer to "what did we know about customer 42 on March 1st, and where did it come from?"
• Multi-source integration. The same business key from two systems collapses onto the same hub row (via the hash key), so integrating a new source is additive — add satellites, don't re-model dimensions.
• Parallel, dependency-free loads. Because hash keys are computed from business keys (not assigned by a sequence), every hub, link, and satellite can be loaded independently and in parallel — there is no "load dimensions before facts" ordering constraint.
• Insert-only resilience. Loads are idempotent and restartable. A failed load re-runs harmlessly because inserting the same hashed key twice is a no-op after dedupe.

The three core structures — the whole model in one breath.

• Hub. A distinct list of business keys for one core concept (customer, order, product). It holds the hash key, the business key, and load metadata. No descriptive attributes, no foreign keys.
• Link. A relationship between two or more hubs (customer places order; order contains product). It holds its own hash key plus the hash keys of the participating hubs. Always modeled as many-to-many. No descriptive attributes.
• Satellite. The descriptive context that changes over time — a customer's name, email, and status; an order's amount and ship-to. It hangs off exactly one parent (a hub or a link) and versions every change with load_date + a hashdiff.

What interviewers actually probe.

• Can you cleanly separate a business key (hub), a relationship (link), and an attribute (satellite) given a messy source table? — the core skill.
• Do you know why hash keys replace sequence surrogate keys — parallel loads and deterministic cross-source joins? — senior signal.
• Do you say "insert-only" and explain load idempotency without prompting? — required answer.
• Can you explain the raw vault vs business vault split and why you don't put business logic in the raw vault? — senior signal.
• Do you frame Data Vault as the integration layer under a star-schema mart, not as a competitor to Kimball? — required answer.

Worked example — Data Vault vs Kimball vs 3NF comparison table

Detailed explanation. The single most useful artifact for a data-modeling interview is a memorised comparison of the three methodologies across the axes that actually differ. Every senior modeling discussion converges on this table; having it in your head separates a fluent answer from a hand-wave. Walk through building it for a hypothetical orders domain that spans a CRM and an ERP.

• Optimization target. What is each model built to make cheap — the query, the transaction, or the integration-and-history?
• Load style. Full-refresh, upsert/merge, or insert-only?
• History. How does each keep prior versions of an attribute?
• New-source cost. What does adding a 13th source system cost in each model?

Question. Build the three-way comparison and state which layer of a modern stack each model belongs in.

Input.

Axis	3NF / Inmon	Kimball star	Data Vault 2.0
Optimizes for	transactional writes	analytical queries	integration + history
Load style	upsert	full-refresh / SCD merge	insert-only
History	effective-dated bolt-on	type-2 dimensions	satellites (native)
Key style	natural / identity FK	sequence surrogate key	hash of business key
New-source cost	schema ripple	dimension re-design	additive (new satellites)
Parallel load	limited	dimensions-before-facts	fully parallel

Code.

-- The SAME orders domain, three ways (abbreviated DDL)

-- 3NF: one normalized table, updated in place
CREATE TABLE orders_3nf (
    order_id     BIGINT PRIMARY KEY,
    customer_id  BIGINT REFERENCES customers(customer_id),
    total_cents  BIGINT,
    status       TEXT,
    updated_at   TIMESTAMPTZ          -- overwritten on every change
);

-- Kimball: a fact + a type-2 dimension
CREATE TABLE fact_orders (
    order_sk     BIGINT PRIMARY KEY,  -- sequence surrogate
    customer_sk  BIGINT,              -- FK to dim_customer version
    total_cents  BIGINT
);
CREATE TABLE dim_customer (
    customer_sk  BIGINT PRIMARY KEY,  -- sequence surrogate, new per version
    customer_id  BIGINT,              -- natural/business key
    name         TEXT,
    valid_from   DATE,
    valid_to     DATE,
    is_current   BOOLEAN
);

-- Data Vault: hub + link + satellite, all insert-only
CREATE TABLE hub_customer (
    customer_hk  BYTEA PRIMARY KEY,   -- hash of business key
    customer_id  TEXT,                -- business key
    load_date    TIMESTAMPTZ,
    record_source TEXT
);
CREATE TABLE sat_customer (
    customer_hk  BYTEA,
    load_date    TIMESTAMPTZ,
    hashdiff     BYTEA,               -- change-detection fingerprint
    name         TEXT,
    status       TEXT,
    PRIMARY KEY (customer_hk, load_date)
);

Step-by-step explanation.

1. In 3NF, orders_3nf.status is overwritten on every change — the warehouse cannot answer "what was the status yesterday" without a separate audit mechanism. This is fine for OLTP, fatal for an audited warehouse.
2. In Kimball, history lives in dim_customer as type-2 rows: each change spawns a new customer_sk with a valid_from/valid_to window. Facts point at the version that was current at fact time. Query is trivial; loading a new source means re-conforming the dimension.
3. In Data Vault, the business key lives once in hub_customer keyed by a hash of customer_id, and history lives in sat_customer as insert-only versions detected by hashdiff. Integrating a second source that also has customers means inserting into the same hub (same hash) and adding a second satellite — no re-modeling.
4. The key-style row is the crux: Kimball assigns customer_sk from a sequence (serial, load-order-dependent); Data Vault computes customer_hk from the business key (parallel, load-order-independent). This single choice is what enables Data Vault's dependency-free parallel load.
5. The three models are layers, not competitors: 3NF is the source OLTP, Data Vault is the integration + history layer in the warehouse, and Kimball star schemas are the marts served on top of the vault for BI.

Output.

Layer in the stack	Model	Why it lives there
Source OLTP	3NF / Inmon	optimized for transactional writes
Warehouse integration + history	Data Vault 2.0	absorbs all sources, keeps full history, parallel loads
BI / consumption	Kimball star	fast, intuitive analytical queries

Rule of thumb. Data Vault is not "instead of Kimball" — it is the integration and history layer beneath the star schema. If you only have one clean source and a BI team that wants dimensions, you may not need a vault at all; if you have a dozen messy sources and an auditor, the vault earns its keep.

Worked example — the "when to pick Data Vault" decision rubric

Detailed explanation. Data Vault is powerful and expensive in table count — a single business concept can explode into a hub, several satellites, and multiple links. Picking it when you don't need it buries a small team in boilerplate. The senior architect runs a short rubric before committing. Walk through it with three scenarios: a startup with one Postgres source, a bank consolidating 40 systems, and a mid-size retailer with a growing source count.

• Source count. One or two clean sources → probably not. Many overlapping sources → yes.
• Audit / compliance. "Reproduce state as of any past date, with provenance" → strong yes.
• Schema volatility. Sources that change shape often → yes (satellites absorb change additively).
• Team + tooling. Do you have automation (dbt + a vault package)? Without it, the boilerplate is punishing.

Question. Score the three scenarios and record the recommendation for each.

Input.

Scenario	Sources	Audit need	Schema churn	Automation ready
Startup, 1 Postgres	1	low	low	no
Bank, 40 systems	40	high (regulated)	high	yes
Retailer, growing	6 → 15	medium	medium	partial

Code.

# Data Vault fit score (illustrative rubric, not production)
def data_vault_fit(sources: int, audit: str, churn: str, automated: bool) -> str:
    score = 0
    score += 2 if sources >= 10 else (1 if sources >= 4 else 0)
    score += {"high": 2, "medium": 1, "low": 0}[audit]
    score += {"high": 2, "medium": 1, "low": 0}[churn]
    score += 1 if automated else 0

    if score >= 5:
        return "Data Vault: strong fit"
    if score >= 3:
        return "Data Vault: consider (automate first)"
    return "Data Vault: likely overkill — use Kimball directly"


print(data_vault_fit(1,  "low",    "low",    False))  # startup
# -> Data Vault: likely overkill — use Kimball directly
print(data_vault_fit(40, "high",   "high",   True))   # bank
# -> Data Vault: strong fit
print(data_vault_fit(15, "medium", "medium", False))  # retailer
# -> Data Vault: consider (automate first)

Step-by-step explanation.

1. The startup scores near zero: one clean source, no regulatory audit, stable schema, no automation. Data Vault's parallel-load and multi-source-integration strengths are wasted; the table-count tax is pure overhead. Build a Kimball star directly.
2. The bank maxes the rubric: 40 systems that each call the same customer something different, a regulator who demands provenance and point-in-time reproduction, and volatile source schemas. This is exactly the workload Data Vault was invented for.
3. The retailer is the ambiguous middle: enough sources and churn to benefit, but without full automation the boilerplate will slow them down. The recommendation is "adopt, but stand up dbt + a vault package first" — never hand-write hub/link/sat SQL at scale.
4. The rubric deliberately weights automation: the single biggest predictor of a failed Data Vault project is hand-writing the repetitive load SQL. Frameworks like datavault4dbt or AutomateDV turn a satellite into a ten-line macro call.
5. The honest senior answer to "should we use Data Vault" is often "not yet" — it is a methodology for a specific pain (many sources, hard audit), not a default.

Output.

Scenario	Score	Recommendation
Startup, 1 Postgres	0	Kimball directly; skip the vault
Bank, 40 systems	7	Data Vault: strong fit
Retailer, growing	3	Adopt, but automate first

Rule of thumb. Pick Data Vault when you have many sources, a hard audit requirement, and automation to generate the boilerplate. With one clean source and no compliance driver, a star schema is simpler and cheaper — do not model a vault for its own sake.

Worked example — anatomy of the three structure types

Detailed explanation. The fastest way to internalize Data Vault is to take one real source row and watch it fan out into a hub, a link, and a satellite. The rule that governs the split is mechanical: business keys go to hubs, relationships between keys go to links, everything descriptive goes to satellites. Walk through decomposing a single order source record.

• Source row. order_id=1001, customer_id=C-42, product_id=P-7, qty=3, status='paid', order_ts=...
• Business keys. order_id, customer_id, product_id → three hubs.
• Relationship. "customer C-42 ordered product P-7 on order 1001" → one link across the three hubs.
• Descriptive. qty, status, order_ts → a satellite hanging off the link (attributes of the relationship, not of any single hub).

Question. Decompose the source order row into the correct hub / link / satellite targets and explain where each column lands.

Input.

Source column	Data Vault target	Reason
order_id	hub_order (business key)	identifies an order
customer_id	hub_customer (business key)	identifies a customer
product_id	hub_product (business key)	identifies a product
(the combination)	link_order_line	relationship across the three
qty, status	sat_order_line	descriptive attributes of the line

Code.

-- One source order row fans out into 3 hubs, 1 link, 1 satellite

INSERT INTO hub_order    (order_hk,    order_id,    load_date, record_source) VALUES (...);
INSERT INTO hub_customer (customer_hk, customer_id, load_date, record_source) VALUES (...);
INSERT INTO hub_product  (product_hk,  product_id,  load_date, record_source) VALUES (...);

INSERT INTO link_order_line (
    order_line_hk,     -- hash of (order_id, customer_id, product_id)
    order_hk, customer_hk, product_hk,
    load_date, record_source
) VALUES (...);

INSERT INTO sat_order_line (
    order_line_hk,     -- parent = the link
    load_date,
    hashdiff,          -- hash of (qty, status)
    qty, status
) VALUES (...);

Step-by-step explanation.

1. Each business key column becomes its own hub. order_id, customer_id, and product_id are three separate concepts, so they are three hubs — never one wide table.
2. The relationship — "this order, for this customer, contains this product" — is a single link row whose order_line_hk is the hash of the three participating business keys. The link stores only keys.
3. The descriptive attributes qty and status describe the line (the relationship), not any single hub, so they land in a satellite hanging off the link. If an attribute described the customer (e.g. email), it would go in a satellite off hub_customer instead.
4. The mechanical test for "hub vs link vs satellite" is: Is it a key that identifies a thing? → hub. Is it the co-occurrence of keys? → link. Is it a describing value that can change over time? → satellite.
5. Notice there is no UPDATE anywhere — every target is an insert. Re-loading the same source row inserts nothing new (the hashes already exist), which is why the load is idempotent and restartable.

Output.

Structure	Table	Holds
Hub × 3	hub_order, hub_customer, hub_product	business keys + hash keys
Link × 1	link_order_line	the three hash keys + its own
Satellite × 1	sat_order_line	qty, status, hashdiff, load_date

Rule of thumb. Decompose every source row with the same three-question test — key, relationship, or attribute? Keys go to hubs, co-occurrences of keys go to links, and everything that can change goes to satellites. Master this split and the rest of Data Vault is mechanical.

Senior interview question on choosing Data Vault

A senior interviewer often opens with: "Our team knows Kimball well and ships star schemas fast. Leadership wants to onboard eight new acquired companies' data over the next year, each with its own CRM and ERP, and audit wants point-in-time reproduction with source provenance. Make the case for Data Vault to a skeptical Kimball-only team, and be honest about what it costs us."

Solution Using a raw vault as the integration layer under existing star-schema marts

Data Vault pitch to a Kimball-only team (5-minute answer)
=========================================================

Frame 1 — we are NOT replacing the star schema
  "Your marts stay. Data Vault becomes the integration + history
   layer UNDER them. BI still queries star schemas; we rebuild those
   marts as views/tables on top of the vault."

Frame 2 — the pain we are solving is integration, not query
  "Eight acquisitions = ~16 sources, each with its own 'customer'.
   Conforming 16 sources into shared dimensions is serial, human,
   and slow. In a vault, each source is ADDITIVE: same business key
   collapses onto the same hub (same hash), new attributes land in a
   new satellite. No dimension re-design per source."

Frame 3 — audit is native, not bolted on
  "Every row has load_date + record_source; nothing is updated in
   place. We can reproduce exactly what any source said on any date,
   with provenance. That is the compliance ask, answered by the model."

Frame 4 — loads become parallel and restartable
  "Hash keys are computed from business keys, so every hub/link/sat
   loads independently, in parallel, insert-only. A failed load
   re-runs harmlessly. No dimensions-before-facts ordering."

Frame 5 — the honest cost
  "Table count explodes (a concept = 1 hub + N sats + M links).
   Queries get more joins. This ONLY pays off with automation
   (dbt + datavault4dbt) and enough sources to amortize it.
   For one clean source, skip it and stay in Kimball."

-- Proof of the 'additive integration' claim:
-- two sources, ONE hub row, TWO satellites.

-- Source A (CRM) and Source B (ERP) both know customer 'C-42'.
-- Same business key -> same hash -> same hub row (deduped):
INSERT INTO hub_customer (customer_hk, customer_id, load_date, record_source)
SELECT digest('C-42', 'sha256'), 'C-42', now(), 'CRM'
WHERE NOT EXISTS (SELECT 1 FROM hub_customer
                  WHERE customer_hk = digest('C-42', 'sha256'));

-- Each source keeps its OWN satellite; no dimension re-modeling:
CREATE TABLE sat_customer_crm (
    customer_hk BYTEA, load_date TIMESTAMPTZ, hashdiff BYTEA,
    name TEXT, email TEXT, PRIMARY KEY (customer_hk, load_date));

CREATE TABLE sat_customer_erp (
    customer_hk BYTEA, load_date TIMESTAMPTZ, hashdiff BYTEA,
    tax_id TEXT, credit_limit_cents BIGINT, PRIMARY KEY (customer_hk, load_date));

Step-by-step trace.

Concern	Kimball-only today	With Data Vault underneath
Onboard a new source	re-conform dimensions (serial)	add satellites (additive, parallel)
Same customer, two sources	dimension merge logic	same hub row, two satellites
Point-in-time audit	bolt-on effective-dating	native (load_date, insert-only)
Provenance	often lost	record_source on every row
Load ordering	dims before facts	none (hash keys)
BI experience	unchanged	unchanged (star marts on top)

Walking the trace: the CRM load and the ERP load both target hub_customer, both compute the same customer_hk from C-42, so the hub ends with one deduped row. Each source's attributes land in its own satellite, so there is zero cross-source conforming at load time — conforming becomes a business-vault concern, done later, in one place, as computed logic rather than as a load-time bottleneck.

Output:

Metric	Kimball-only	Kimball on Data Vault
Cost to add source N	re-design dimensions	add satellites (hours)
Load parallelism	limited	full
Audit / point-in-time	manual	native
Table count	low	high (needs automation)
BI query shape	star	star (unchanged)

Why this works — concept by concept:

• Insert-only integration layer — the vault never updates a business row, so history and provenance are inherent, not features you build. Auditability falls out of the load philosophy.
• Hash keys from business keys — because customer_hk is derived from customer_id, two independent loads compute the same key with no coordination, so integration is additive and loads are parallel.
• Satellite-per-source — keeping each source's attributes in its own satellite means onboarding a source is an insert, not a re-model. Conforming is deferred to the business vault where it belongs.
• Star marts on top — the BI team's experience is unchanged because the star schema still exists; it is now a view over the vault rather than the system of record. You get vault benefits without a BI migration.
• Cost — table count and join depth both grow, so the methodology only pays off with automation and enough sources (roughly ≥ 4–10) to amortize the boilerplate. For one clean source, the star schema alone is the cheaper, correct choice — O(sources) integration cost in a vault versus O(sources²) conforming cost in pure Kimball.

Modeling
Topic — dimensional-modeling
Dimensional modeling and Data Vault design problems

Practice →

Design Topic — design Warehouse architecture and layering problems

Practice →

---

2. Hubs and the hash-key foundation

A hub is a distinct list of business keys, and the hash key is what makes loads parallel and joins deterministic

The mental model in one line: a hub is the single, deduplicated list of business keys for one core concept — nothing more — and each row carries a hash key (a deterministic MD5 or SHA-256 digest of the business key), the business key itself, a load_date, and a record_source; because the hash is computed from the business key rather than assigned by a sequence, every source can compute the same key independently, which is precisely what makes Data Vault loads parallel, restartable, and source-agnostic. The hub holds no descriptive attributes and no foreign keys — those live in satellites and links. If you remember one thing about hubs, remember that they answer "which distinct things of this type exist," and the hash key answers "how do I join to them without coordinating a sequence generator across a hundred parallel loads."

The four columns every hub has.

• Hash key (*_hk). A binary (or hex) digest of the business key, e.g. SHA-256(upper(trim(customer_id))). This is the primary key and the join target for links and satellites. In Data Vault 1.0 this was a sequence surrogate; Data Vault 2.0 switched to hashes specifically to enable parallel loads.
• Business key (*_id / *_bk). The natural key from the source — the account number, SKU, order number. This is the meaning the hash stands for, kept so humans and reconciliation jobs can read it.
• load_date. When this business key was first seen by the warehouse. Insert-only, so it never changes for a given key.
• record_source. Which source first contributed the key ('CRM', 'ERP.orders'). Provenance for audit.

Why hash keys, not sequence surrogate keys.

• Parallel, coordination-free loads. A sequence key must be assigned by a single generator; that serializes the load and creates a dependency (you must load the hub before anything that references it, to get its key). A hash is computed from data, so a link load can compute the same customer_hk without ever reading the hub.
• Deterministic cross-source joins. Two systems that both call the customer C-42 produce the identical hash, so they collapse onto one hub row and join together automatically — no lookup table, no sequence reconciliation.
• Idempotent, restartable loads. Because the key is derived, re-running a load recomputes the same keys; dedupe makes the second insert a no-op. A crashed load simply re-runs.
• The trade-offs you must name. Hashes are wider than integers (16 bytes for MD5, 32 for SHA-256) so they cost storage and join CPU; MD5 has a (astronomically small but non-zero) collision risk; and you must standardize the business key before hashing (trim, upcase, cast) or two representations of the same key produce two hashes.

Hash-key hygiene rules that separate seniors from juniors.

• Standardize before you hash. Always upper(trim(cast(bk as text))) — otherwise 'c-42 ' and 'C-42' become different hubs. This normalization is a hard contract across every load.
• Choose a delimiter for composite keys. A composite business key (e.g. line = order_id + product_id) is concatenated with a reserved separator like || before hashing, so ('1','23') and ('12','3') never collide.
• Handle NULLs explicitly. Replace NULL components with a sentinel ('^^' or empty string) before concatenation so the hash is stable and NULL-vs-empty never diverges.
• Pick one algorithm and freeze it. MD5 is common (fast, 16 bytes); SHA-256 is safer (32 bytes). Whatever you pick becomes a warehouse-wide contract — you cannot mix.

Worked example — hub DDL + hash-key computation across Postgres and Snowflake

Detailed explanation. The canonical hub is four columns, and the load is a SELECT ... WHERE NOT EXISTS insert that dedupes on the hash. The interesting part is computing the hash identically on two engines so that a Postgres-loaded hub and a Snowflake-loaded hub agree. Walk through the DDL and the hash expression on both.

• Business key. customer_id (text), standardized as upper(trim(...)).
• Hash algorithm. SHA-256, stored as BYTEA in Postgres and BINARY in Snowflake.
• Load metadata. load_date = now(), record_source = 'CRM'.
• Dedupe. Insert only business keys not already present in the hub.

Question. Write the hub DDL and the hash-key expression on both Postgres and Snowflake, and prove they produce the same key for 'C-42'.

Input.

Component	Postgres	Snowflake
Hash function	digest(x,'sha256') (pgcrypto)	SHA2_BINARY(x, 256)
Hash type	BYTEA	BINARY(32)
Standardize	upper(trim(customer_id))	UPPER(TRIM(customer_id))
Now	now()	CURRENT_TIMESTAMP()

Code.

-- ===== Postgres =====
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE hub_customer (
    customer_hk   BYTEA        PRIMARY KEY,
    customer_id   TEXT         NOT NULL,
    load_date     TIMESTAMPTZ  NOT NULL,
    record_source TEXT         NOT NULL
);

-- Hash-key expression (standardize, THEN hash):
-- digest(upper(trim(customer_id)), 'sha256')

INSERT INTO hub_customer (customer_hk, customer_id, load_date, record_source)
SELECT DISTINCT
       digest(upper(trim(s.customer_id)), 'sha256') AS customer_hk,
       upper(trim(s.customer_id))                   AS customer_id,
       now()                                        AS load_date,
       'CRM'                                        AS record_source
FROM   stg_crm_customers s
WHERE  NOT EXISTS (
           SELECT 1 FROM hub_customer h
           WHERE  h.customer_hk = digest(upper(trim(s.customer_id)), 'sha256')
       );

-- ===== Snowflake =====
CREATE TABLE hub_customer (
    customer_hk   BINARY(32)  PRIMARY KEY,
    customer_id   STRING      NOT NULL,
    load_date     TIMESTAMP_NTZ NOT NULL,
    record_source STRING      NOT NULL
);

-- Hash-key expression: SHA2_BINARY(UPPER(TRIM(customer_id)), 256)

INSERT INTO hub_customer (customer_hk, customer_id, load_date, record_source)
SELECT DISTINCT
       SHA2_BINARY(UPPER(TRIM(s.customer_id)), 256) AS customer_hk,
       UPPER(TRIM(s.customer_id))                   AS customer_id,
       CURRENT_TIMESTAMP()                          AS load_date,
       'CRM'                                        AS record_source
FROM   stg_crm_customers s
WHERE  s.customer_id NOT IN (SELECT customer_id FROM hub_customer);

Step-by-step explanation.

1. Both engines standardize the business key with upper(trim(...)) before hashing. This is non-negotiable: the standardization must be byte-identical across engines, or 'C-42' from Postgres and ' c-42' from Snowflake produce different customer_hk values and fail to integrate.
2. SHA2_BINARY(x, 256) in Snowflake and digest(x, 'sha256') in Postgres both emit the raw 32-byte SHA-256 digest of the UTF-8 bytes of the standardized string — so 'C-42' hashes to the identical 32 bytes on both. (If you store hex instead, use encode(...,'hex') / SHA2(...) consistently.)
3. The WHERE NOT EXISTS / NOT IN clause is the dedupe: a business key already in the hub is skipped, so re-running the load inserts nothing. This is what makes the hub load idempotent.
4. load_date is set once, on first sight of the key. Because the load is insert-only, a key's load_date never changes — it records when the warehouse first learned the key existed.
5. record_source captures which source contributed the key first. If the ERP later loads the same C-42, the dedupe skips the insert, so the hub keeps the CRM as first-source — provenance for the key, while attribute provenance lives per-satellite.

Output.

customer_hk (hex, truncated)	customer_id	load_date	record_source
8f2a... (32 bytes)	C-42	2026-08-05 09:00:01	CRM
b1c9... (32 bytes)	C-99	2026-08-05 09:00:01	CRM

Rule of thumb. Standardize (upper(trim(...))), then hash with one frozen algorithm, and dedupe with NOT EXISTS. Prove your Postgres and Snowflake hash expressions agree on a known key before you trust cross-engine integration — a one-character normalization mismatch silently splits a hub in two.

Worked example — insert-only hub load with staging and dedupe

Detailed explanation. A real hub load doesn't read the source table directly; it reads a staging table where the hash keys have already been computed once, so links and satellites in the same batch reuse them. The load is insert-only with two levels of dedupe: within the incoming batch, and against the existing hub. Walk through the staged pattern.

• Staging. stg_customers already has customer_hk, customer_id, load_date, record_source computed at ingest.
• Batch dedupe. The same key can appear many times in one batch (e.g. 10 000 orders from 500 customers); collapse to distinct keys first.
• Hub dedupe. Skip keys already in the hub.
• Insert-only. No UPDATE — a re-run is a no-op.

Question. Load hub_customer from a staging table that may contain duplicate and already-known keys, inserting each new key exactly once.

Input.

Staging row	customer_id	in hub already?
1	C-42	yes
2	C-77	no
3	C-77	no (dup in batch)
4	C-88	no

Code.

-- Staging already carries the computed hash key (compute-once contract)
-- stg_customers(customer_hk, customer_id, load_date, record_source)

INSERT INTO hub_customer (customer_hk, customer_id, load_date, record_source)
SELECT customer_hk, customer_id, load_date, record_source
FROM (
    -- level 1: collapse duplicate keys WITHIN the batch,
    -- keeping the earliest load_date per key
    SELECT customer_hk, customer_id, load_date, record_source,
           ROW_NUMBER() OVER (PARTITION BY customer_hk
                              ORDER BY load_date ASC) AS rn
    FROM   stg_customers
) d
WHERE d.rn = 1
  AND NOT EXISTS (                       -- level 2: skip keys already in the hub
      SELECT 1 FROM hub_customer h
      WHERE  h.customer_hk = d.customer_hk
  );

Step-by-step explanation.

1. The staging table carries customer_hk already computed at ingest — the "compute the hash once" contract. Links and satellites in the same batch reuse this exact column, guaranteeing every structure agrees on the key.
2. Level-1 dedupe uses ROW_NUMBER() OVER (PARTITION BY customer_hk ORDER BY load_date ASC) to collapse the two C-77 rows in the batch to one, keeping the earliest load_date — the first time the warehouse saw the key in this batch.
3. Level-2 dedupe is the NOT EXISTS against the hub: C-42 is already present, so it is skipped. Only genuinely new keys survive both filters.
4. Because the operation is a pure INSERT with dedupe, running the batch twice inserts nothing the second time — the hub is idempotent under re-load, which is what makes a failed load safe to retry.
5. Note there is no ordering dependency: this hub load does not need any other table to have loaded first, because the hash key came from the data, not from a sequence. The link and satellite loads for the same batch can run concurrently.

Output.

customer_id	action
C-42	skipped (already in hub)
C-77	inserted once (batch dup collapsed)
C-88	inserted

Rule of thumb. Compute the hash key once in staging, dedupe twice (within the batch by ROW_NUMBER, against the hub by NOT EXISTS), and keep the load pure-insert. The result is a hub load that is parallel-safe, restartable, and idempotent by construction.

Worked example — composite business keys and collision safety

Detailed explanation. Not every hub has a single-column business key. A "product-in-store" or a "policy line" is identified by a combination of keys, and hashing a composite key naively invites collisions: ('1','23') and ('12','3') concatenate to the same '123'. The fix is a reserved delimiter plus explicit NULL handling. Walk through a hub_order_line keyed by (order_id, line_no).

• Composite key. (order_id, line_no) uniquely identifies an order line.
• Delimiter. Concatenate with a reserved separator (|| or a non-printable char) that cannot appear in a key.
• NULL handling. Replace NULL components with a sentinel so (NULL, '5') and ('', '5') don't collide unexpectedly.
• Standardize each part before concatenation.

Question. Define the collision-safe composite hash key for hub_order_line and show two near-identical inputs producing distinct hashes.

Input.

| order_id | line_no | naive concat | safe concat (\|\|) |
|---|---|---|---|
| 1 | 23 | 123 | 1||23 |
| 12 | 3 | 123 (collision!) | 12||3 |

Code.

-- Collision-safe composite hash key
CREATE TABLE hub_order_line (
    order_line_hk BYTEA        PRIMARY KEY,
    order_id      TEXT         NOT NULL,
    line_no       TEXT         NOT NULL,
    load_date     TIMESTAMPTZ  NOT NULL,
    record_source TEXT         NOT NULL
);

-- Reserved delimiter '||', NULL sentinel '^^', standardize each part
INSERT INTO hub_order_line (order_line_hk, order_id, line_no, load_date, record_source)
SELECT DISTINCT
       digest(
         concat_ws('||',
           coalesce(upper(trim(order_id)), '^^'),
           coalesce(upper(trim(line_no)),  '^^')
         ), 'sha256'
       )                                       AS order_line_hk,
       upper(trim(order_id))                   AS order_id,
       upper(trim(line_no))                    AS line_no,
       now()                                   AS load_date,
       'ERP'                                   AS record_source
FROM   stg_order_lines s
WHERE  NOT EXISTS (
    SELECT 1 FROM hub_order_line h
    WHERE  h.order_line_hk = digest(
        concat_ws('||',
          coalesce(upper(trim(s.order_id)), '^^'),
          coalesce(upper(trim(s.line_no)),  '^^')), 'sha256')
);

Step-by-step explanation.

1. concat_ws('||', ...) inserts the reserved || delimiter between parts, so ('1','23') becomes '1||23' and ('12','3') becomes '12||3' — distinct strings, distinct hashes. Without the delimiter both would be '123' and collide into one hub row.
2. coalesce(..., '^^') replaces a NULL component with a sentinel that cannot appear in a real key, so a NULL line_no produces a stable, distinct hash rather than a database-dependent NULL-concatenation result (in some engines, concatenating NULL yields NULL, silently dropping the whole key).
3. Each component is standardized with upper(trim(...)) before concatenation, so casing and whitespace differences don't split the hub.
4. The delimiter must be a sequence that genuinely cannot occur inside any component — || works if keys never contain it; some shops use a non-printable byte like \x1f for absolute safety. Choosing it is a warehouse-wide contract.
5. The dedupe recomputes the identical expression in the NOT EXISTS, so the collision-safe key is the join and dedupe target everywhere it appears — link loads that reference this order line concatenate the same way.

Output.

order_id	line_no	safe concat	distinct hash?
1	23	`1\	\
12	3	{% raw %}`12\	\

Rule of thumb. For any composite business key, concatenate standardized parts with a reserved delimiter and a NULL sentinel, then hash. Never concatenate raw — a missing delimiter turns {% raw %}(1,23) and (12,3) into the same hub row, a data-corruption bug that is invisible until two unrelated entities merge.

Senior interview question on hash keys versus sequence keys

A senior interviewer might ask: "In Data Vault 1.0 the surrogate key was a sequence-generated integer; Data Vault 2.0 switched to hashing the business key. Walk me through why that change was made, what it buys you operationally, what it costs, and how you'd guard against the one real risk it introduces."

Solution Using hash keys to make loads parallel while guarding standardization and collisions

-- The core proof: a link load computes the SAME customer_hk as the
-- hub load, WITHOUT reading the hub. That is impossible with sequences.

-- Hub load (runs on worker A)
INSERT INTO hub_customer (customer_hk, customer_id, load_date, record_source)
SELECT DISTINCT digest(upper(trim(customer_id)),'sha256'),
       upper(trim(customer_id)), now(), 'CRM'
FROM   stg WHERE NOT EXISTS (...);

-- Link load (runs on worker B, CONCURRENTLY, no read of hub_customer)
INSERT INTO link_cust_order (link_hk, customer_hk, order_hk, load_date, record_source)
SELECT DISTINCT
       digest(concat_ws('||', upper(trim(customer_id)),
                              upper(trim(order_id))), 'sha256')  AS link_hk,
       digest(upper(trim(customer_id)), 'sha256')               AS customer_hk,
       digest(upper(trim(order_id)),    'sha256')               AS order_hk,
       now(), 'CRM'
FROM   stg WHERE NOT EXISTS (...);

-- Guardrails against the one real risk (bad key -> wrong integration):
-- 1. Standardization is a shared, tested function used EVERYWHERE.
CREATE OR REPLACE FUNCTION dv_hk(bk TEXT) RETURNS BYTEA AS $$
    SELECT digest(coalesce(upper(trim(bk)), '^^'), 'sha256');
$$ LANGUAGE sql IMMUTABLE;

-- 2. A reconciliation check: business key <-> hash key must be 1:1.
SELECT customer_id, count(DISTINCT customer_hk) AS distinct_hashes
FROM   hub_customer
GROUP  BY customer_id
HAVING count(DISTINCT customer_hk) > 1;      -- must return ZERO rows

Step-by-step trace.

Property	Sequence surrogate (DV 1.0)	Hash key (DV 2.0)
Key source	central sequence generator	digest of business key
Hub load before link?	required (need the key)	not required
Parallel load	serialized by generator	fully parallel
Cross-source join	needs key lookup	automatic (same hash)
Restart after crash	risky (gaps, re-assign)	safe (recompute, dedupe)
Key width	8 bytes	16 (MD5) / 32 (SHA-256)
Main risk	none intrinsic	bad standardization / collision

Walking the trace: with a sequence, worker B (the link load) cannot know customer_hk for C-42 until worker A (the hub load) has assigned it and B has looked it up — a hard ordering dependency that serializes the load. With a hash, worker B computes digest('C-42') itself, identically to worker A, so both run concurrently and still agree. The dv_hk function centralizes standardization so every load hashes the same way, and the reconciliation query proves the business-key-to-hash mapping stayed 1:1 (any row returned means a standardization bug split a key).

Output:

Metric	Sequence keys	Hash keys
Load parallelism	serialized	full
Load ordering constraint	hub → link → sat	none
Cross-source integration	lookup required	automatic
Restartability	fragile	idempotent
Storage / join cost	lower	higher (wider key)

Why this works — concept by concept:

• Derived vs assigned keys — a hash is a pure function of the business key, so any process can compute it without coordination. A sequence is assigned by shared state, which forces serialization and load ordering. This single distinction is the whole reason DV 2.0 switched.
• Parallel, dependency-free loads — because the link load recomputes customer_hk itself, hubs, links, and satellites load concurrently. There is no "load the hub first to get its surrogate" step.
• Deterministic cross-source integration — identical business keys from different systems hash identically, collapsing onto one hub and joining automatically, with no surrogate-key lookup table.
• Standardization as a contract — the one real risk (two representations of the same key hashing differently, or two different keys colliding) is controlled by a single tested dv_hk function and a 1:1 reconciliation check, not by hoping every author trims the same way.
• Cost — hash keys are 2–4× wider than an 8-byte integer, so joins and storage cost more, and MD5 carries a negligible-but-nonzero collision probability (mitigated by SHA-256). You trade that width for O(1) parallel loads and O(1) cross-source joins — nearly always the right trade at enterprise scale.

SQL
Topic — sql
SQL hashing, dedupe, and window-function problems

Practice →

Modeling Topic — dimensional-modeling Hub and business-key modeling problems

Practice →

---

3. Links and many-to-many relationships

A link records that hubs co-occur — always many-to-many, always keys only

The mental model in one line: a link is the table that records a relationship between two or more hubs — "this customer placed this order," "this order contains this product" — and it holds only hash keys (its own link hash key plus one foreign hash key per participating hub) plus load metadata; every relationship in Data Vault is modeled many-to-many by default, so that when a source that was one-to-many yesterday becomes many-to-many tomorrow, the model does not have to change at all. Links never carry descriptive attributes; those go in a link-satellite. And a link's hash key is computed from the combination of the participating business keys, so — exactly like a hub — it can be loaded in parallel with no ordering dependency.

What a link holds — and what it must not.

• Link hash key (*_link_hk). Hash of the concatenated business keys of all participating hubs, in a fixed order with the reserved delimiter. This is the primary key and the parent for any link-satellite.
• One foreign hash key per hub. customer_hk, order_hk, product_hk — the hashes of the participating hubs, recomputed from the same business keys so no hub read is needed.
• load_date + record_source. Same load metadata as a hub.
• No descriptive attributes. Amount, quantity, status, effective dates — none of these belong in the link. They go in a link-satellite. A link with attributes is the single most common Data Vault modeling error.

Why every link is many-to-many.

• Cardinality changes; the model shouldn't. A source may enforce "one customer per order" today, but a business rule change (household accounts, order transfers) can make it many-to-many tomorrow. Modeling the link as many-to-many from day one means that change is a data change, not a schema change.
• A link row is one observed co-occurrence. Each distinct combination of participating keys is one link row. If a customer is later associated with the same order twice under different circumstances, that is captured in the link-satellite history, not by mutating the link.
• No foreign-key constraints between hub and link at load time. Because loads are parallel, the link may load before the hub in wall-clock time; referential integrity is a logical guarantee (the hashes match), not an enforced database constraint during load.

The link variants you must know.

• Standard link. Relates two or more hubs; its history (if any) lives in a link-satellite.
• Non-historized / transactional link. For immutable events (a payment, a sensor reading, a click) that never change. The measures live on the link itself as an exception to the "keys only" rule, because a transaction is a point-in-time fact with no history to track — there is no satellite because the event never updates.
• Same-as / hierarchical link. A link between a hub and itself — e.g. link_same_as mapping duplicate customer keys to a master, or a link_hierarchy for manager-employee. Two foreign keys point at the same hub.
• Degenerate fields. A truly key-like attribute of the relationship that has no hub of its own (e.g. an order_line_no) may sit on the link as a degenerate field — used sparingly, only for values that are part of the relationship's identity.

Worked example — link DDL + insert-only load

Detailed explanation. The canonical link relates two hubs — customer and order — and its load is the same insert-only, dedupe-on-hash pattern as a hub, just with more key columns. Walk through link_customer_order.

• Participating hubs. hub_customer, hub_order.
• Link hash key. SHA-256(customer_id || order_id) with the reserved delimiter.
• Foreign hashes. customer_hk, order_hk, each recomputed from its business key.
• Load. Insert distinct new combinations only.

Question. Write the link DDL and the insert-only load that records each distinct customer↔order relationship exactly once.

Input.

Staging row	customer_id	order_id	new relationship?
1	C-42	O-1	yes
2	C-42	O-1	no (dup)
3	C-42	O-2	yes

Code.

CREATE TABLE link_customer_order (
    cust_order_hk BYTEA        PRIMARY KEY,   -- link hash key
    customer_hk   BYTEA        NOT NULL,      -- FK hash to hub_customer
    order_hk      BYTEA        NOT NULL,      -- FK hash to hub_order
    load_date     TIMESTAMPTZ  NOT NULL,
    record_source TEXT         NOT NULL
);

INSERT INTO link_customer_order
       (cust_order_hk, customer_hk, order_hk, load_date, record_source)
SELECT DISTINCT
       digest(concat_ws('||', upper(trim(customer_id)),
                              upper(trim(order_id))), 'sha256') AS cust_order_hk,
       digest(upper(trim(customer_id)), 'sha256')              AS customer_hk,
       digest(upper(trim(order_id)),    'sha256')              AS order_hk,
       now()                                                   AS load_date,
       'CRM'                                                   AS record_source
FROM   stg_orders s
WHERE  NOT EXISTS (
    SELECT 1 FROM link_customer_order l
    WHERE  l.cust_order_hk = digest(
        concat_ws('||', upper(trim(s.customer_id)),
                        upper(trim(s.order_id))), 'sha256')
);

Step-by-step explanation.

1. The link's primary key cust_order_hk is the hash of the combination of the participating business keys, delimited by ||. Two rows with the same (customer_id, order_id) produce the same link hash and dedupe to one row.
2. customer_hk and order_hk are recomputed here from the business keys — the link load never reads hub_customer or hub_order. That is what lets the link load run concurrently with the hub loads.
3. The SELECT DISTINCT plus NOT EXISTS gives the same two-level dedupe as the hub: duplicate (C-42, O-1) rows in the batch collapse, and an already-recorded relationship is skipped.
4. There are no descriptive columns — no amount, no status. If the source has those, they go into a separate sat_customer_order link-satellite (next section's territory). Keeping the link keys-only is the discipline.
5. The relationship (C-42, O-2) is a new combination, so it inserts; (C-42, O-1) appearing twice inserts once. Recording each distinct co-occurrence once, insert-only, keeps the link idempotent and parallel-safe.

Output.

cust_order_hk	customer_id	order_id	action
a91f...	C-42	O-1	inserted (dup collapsed)
c07b...	C-42	O-2	inserted

Rule of thumb. A link is a hub with more key columns and zero attributes. Hash the combination of business keys for the link key, recompute each hub's hash inline so you never read the hubs, and dedupe on the link hash. If you find yourself adding an amount column to a link, stop — that belongs in a link-satellite.

Worked example — a transactional (non-historized) link for events

Detailed explanation. Some relationships are immutable events: a payment, a shipment scan, a click. They never update, so tracking their history is pointless. The non-historized (transactional) link records the event and — as a deliberate exception — carries its immutable measures on the link itself, because there is no satellite to hold them (a satellite implies change; an event has none). Walk through link_payment.

• Event. A payment against an order — happens once, never changes.
• Participating hubs. hub_order (and optionally hub_customer).
• Immutable measures. amount_cents, paid_at, payment_method — fixed at event time.
• No satellite. The event is point-in-time; there is nothing to version.

Question. Model an immutable payment event as a non-historized link and explain why the measures live on the link rather than in a satellite.

Input.

Column	Placement	Reason
payment_hk	link PK	hash of the payment business key
order_hk	link FK	the order paid
amount_cents	on the link	immutable measure
paid_at	on the link	event timestamp, never changes

Code.

-- Non-historized / transactional link: measures live ON the link
-- because a payment event is immutable (no satellite, no history).
CREATE TABLE link_payment (
    payment_hk     BYTEA        PRIMARY KEY,  -- hash of payment_id (the event's own BK)
    order_hk       BYTEA        NOT NULL,     -- FK hash to hub_order
    -- immutable transactional measures (exception to 'keys only'):
    amount_cents   BIGINT       NOT NULL,
    payment_method TEXT         NOT NULL,
    paid_at        TIMESTAMPTZ  NOT NULL,
    load_date      TIMESTAMPTZ  NOT NULL,
    record_source  TEXT         NOT NULL
);

INSERT INTO link_payment
    (payment_hk, order_hk, amount_cents, payment_method, paid_at, load_date, record_source)
SELECT DISTINCT
    digest(upper(trim(payment_id)), 'sha256'),
    digest(upper(trim(order_id)),   'sha256'),
    amount_cents, payment_method, paid_at, now(), 'PAYMENTS'
FROM   stg_payments s
WHERE  NOT EXISTS (
    SELECT 1 FROM link_payment lp
    WHERE  lp.payment_hk = digest(upper(trim(s.payment_id)), 'sha256')
);

Step-by-step explanation.

1. A payment has its own business key (payment_id), so the link's primary key is the hash of that key — the event identifies itself. The order_hk foreign key ties it to the order it paid.
2. The measures amount_cents, payment_method, and paid_at sit directly on the link. This is the sanctioned exception to "links hold only keys": because the event is immutable, there is no history to track, so a satellite would only ever hold one row per key — pointless overhead.
3. The load is still insert-only and deduped on the event's hash. A payment event, once recorded, never changes; re-loading it is a no-op.
4. Contrast with a standard link (link_customer_order): a customer's relationship to an order can gain attributes over time (status transitions), so those go in a link-satellite. A payment cannot, so it does not get one.
5. The senior signal is knowing when to break the "keys only" rule: only for genuinely immutable transactional events, never for a relationship whose descriptive attributes can change.

Output.

payment_hk	order_hk	amount_cents	paid_at
f4d2...	c07b...	4999	2026-08-05 10:12:00
77aa...	a91f...	12000	2026-08-05 10:15:30

Rule of thumb. Use a non-historized link for immutable events and put the event's measures on the link itself — a satellite implies change, and an event has none. For any relationship whose attributes can evolve, keep the link keys-only and hang a link-satellite off it.

Worked example — a three-way order↔product↔customer link

Detailed explanation. Many relationships involve three or more hubs at once. An order line is the co-occurrence of a customer, an order, and a product. Modeling it as a single three-hub link (rather than three pairwise links) keeps the grain honest: one link row per line. Walk through link_order_line.

• Participating hubs. hub_customer, hub_order, hub_product.
• Link key. Hash of (customer_id || order_id || product_id).
• Grain. One row per (customer, order, product) co-occurrence.
• Attributes. qty, line_status → a link-satellite, not the link.

Question. Model the order-line relationship across three hubs and explain why one three-way link beats three pairwise links.

Input.

Source line	customer_id	order_id	product_id
1	C-42	O-1	P-7
2	C-42	O-1	P-9

Code.

CREATE TABLE link_order_line (
    order_line_hk BYTEA        PRIMARY KEY,   -- hash of all three BKs
    customer_hk   BYTEA        NOT NULL,
    order_hk      BYTEA        NOT NULL,
    product_hk    BYTEA        NOT NULL,
    load_date     TIMESTAMPTZ  NOT NULL,
    record_source TEXT         NOT NULL
);

INSERT INTO link_order_line
    (order_line_hk, customer_hk, order_hk, product_hk, load_date, record_source)
SELECT DISTINCT
    digest(concat_ws('||', upper(trim(customer_id)),
                           upper(trim(order_id)),
                           upper(trim(product_id))), 'sha256') AS order_line_hk,
    digest(upper(trim(customer_id)), 'sha256'),
    digest(upper(trim(order_id)),    'sha256'),
    digest(upper(trim(product_id)),  'sha256'),
    now(), 'ERP'
FROM   stg_order_lines s
WHERE  NOT EXISTS (
    SELECT 1 FROM link_order_line l
    WHERE  l.order_line_hk = digest(concat_ws('||',
              upper(trim(s.customer_id)),
              upper(trim(s.order_id)),
              upper(trim(s.product_id))), 'sha256')
);

Step-by-step explanation.

1. The three-way link key is the hash of all three business keys concatenated with || in a fixed, documented order (customer, order, product). Fixing the order is essential — reordering the parts would produce a different hash and split the link.
2. One link row captures the full co-occurrence "customer C-42, on order O-1, bought product P-7." The two source lines produce two distinct link rows because the product differs.
3. Modeling this as one three-way link, rather than three pairwise links (customer↔order, order↔product, customer↔product), preserves the true grain: an order line is a single fact about three entities together, not three separate pairwise facts. Pairwise links would lose the "which product on which order for which customer" combination.
4. Descriptive attributes like qty and line_status are deliberately absent from the link — they go in sat_order_line, a link-satellite parented by order_line_hk. The link stays keys-only.
5. As always, each hub hash is recomputed inline, so the link load needs none of the three hubs to have loaded first — full parallelism holds even for three-way links.

Output.

order_line_hk	customer_id	order_id	product_id
1a2b...	C-42	O-1	P-7
3c4d...	C-42	O-1	P-9

Rule of thumb. When a fact involves three or more entities at once, model one multi-hub link with the business keys concatenated in a fixed order — not a web of pairwise links. Pairwise decomposition loses the true grain of the co-occurrence; the single link preserves it.

Senior interview question on modeling relationships

A senior interviewer might ask: "Model the order domain in Data Vault. An order belongs to a customer, contains one or more products, and its shipping address and status change over time. Show the hubs, the links, and where the changing attributes go — and defend why the link carries no attributes."

Solution Using a keys-only link plus a link-satellite for changing attributes

-- Hubs (keys only)
CREATE TABLE hub_customer (customer_hk BYTEA PRIMARY KEY, customer_id TEXT,
    load_date TIMESTAMPTZ, record_source TEXT);
CREATE TABLE hub_order    (order_hk    BYTEA PRIMARY KEY, order_id    TEXT,
    load_date TIMESTAMPTZ, record_source TEXT);
CREATE TABLE hub_product  (product_hk  BYTEA PRIMARY KEY, product_id  TEXT,
    load_date TIMESTAMPTZ, record_source TEXT);

-- Link: the order line, KEYS ONLY (customer + order + product)
CREATE TABLE link_order_line (
    order_line_hk BYTEA PRIMARY KEY,
    customer_hk BYTEA, order_hk BYTEA, product_hk BYTEA,
    load_date TIMESTAMPTZ, record_source TEXT);

-- Link-satellite: the CHANGING attributes of the line hang off the link
CREATE TABLE sat_order_line (
    order_line_hk BYTEA,               -- parent = the link
    load_date     TIMESTAMPTZ,
    hashdiff      BYTEA,               -- change detection
    qty           INT,
    line_status   TEXT,
    ship_to       TEXT,
    PRIMARY KEY (order_line_hk, load_date));

-- Current view of each line: latest satellite row per link key
SELECT l.order_line_hk, hc.customer_id, ho.order_id, hp.product_id,
       s.qty, s.line_status, s.ship_to
FROM   link_order_line l
JOIN   hub_customer hc ON hc.customer_hk = l.customer_hk
JOIN   hub_order    ho ON ho.order_hk    = l.order_hk
JOIN   hub_product  hp ON hp.product_hk  = l.product_hk
JOIN   LATERAL (
    SELECT qty, line_status, ship_to
    FROM   sat_order_line s
    WHERE  s.order_line_hk = l.order_line_hk
    ORDER  BY s.load_date DESC
    LIMIT  1
) s ON true;

Step-by-step trace.

Element	Where it goes	Why
customer_id / order_id / product_id	three hubs	each is a business key
the line (their co-occurrence)	link_order_line	a relationship across hubs
qty, line_status, ship_to	sat_order_line	descriptive + changing → satellite
status change over time	new sat row (insert-only)	history via load_date
current state	latest sat row per link	ORDER BY load_date DESC LIMIT 1

Walking the trace: the three business keys become three hubs; their co-occurrence is one keys-only link; the attributes that can change (qty, line_status, ship_to) live in a link-satellite keyed by (order_line_hk, load_date). When the status moves paid → shipped, a new satellite row is inserted with a new load_date — the link itself never changes. The "current state" query joins the hubs for readable business keys and takes the latest satellite row per link key via a LATERAL top-1.

Output:

order_id	product_id	qty	line_status (current)
O-1	P-7	3	shipped
O-1	P-9	1	paid

Why this works — concept by concept:

• Keys-only link — the link records that the three hubs co-occur, nothing about how. Keeping attributes out means a cardinality or attribute change never forces a link schema change; it is absorbed by data or by the satellite.
• Link-satellite for change — because qty, line_status, and ship_to evolve, they live in a satellite that versions them insert-only by load_date. History is native: every past state is a prior row.
• Many-to-many by default — modeling the line as a multi-hub link means "one customer, many products, many orders" needs no schema change; the grain is the co-occurrence, and new combinations are new link rows.
• Current-state via latest satellite — the consumer view reconstructs the present by taking the newest satellite row per link key, so the raw vault stays append-only while queries still see "now."
• Cost — an extra join per attribute group and a per-key latest-row scan, versus a Kimball fact that would carry the attributes inline. You trade query-time join depth for load-time flexibility and native history — which is why marts (next section) precompute this with PIT tables. Roughly O(joins) at query time, O(1) insert at load time.

Modeling
Topic — dimensional-modeling
Link and relationship modeling problems

Practice →

SQL Topic — sql SQL join and many-to-many query problems

Practice →

---

4. Satellites: history, hashdiff, and multiple sources

A satellite versions descriptive attributes over time, and the hashdiff decides when a new version is worth inserting

The mental model in one line: a satellite holds the descriptive, changeable attributes of exactly one parent (a hub or a link), keyed by the parent's hash key plus a load_date, and it is insert-only — a new version is appended whenever the attributes change, where "change" is detected by comparing a hashdiff (a hash of all the attribute values) against the latest stored row, so an unchanged reload inserts nothing. This is Data Vault's answer to slowly-changing dimensions: SCD Type-2 history falls out naturally because every version is just another insert-only row, timestamped by load_date, and the hashdiff is the cheap, single-column change detector that keeps you from storing a new row every load when nothing actually moved.

The anatomy of a satellite.

• Parent hash key. customer_hk (for a hub-satellite) or order_line_hk (for a link-satellite). Part of the primary key; the join back to the parent.
• load_date. The other half of the primary key. (parent_hk, load_date) uniquely identifies a version.
• hashdiff. A hash of all the descriptive attribute values, standardized and concatenated. If the incoming hashdiff equals the latest stored hashdiff for that parent, nothing changed → skip the insert.
• Descriptive attributes. The actual payload — name, email, status, amount, ship-to. These are the columns that change over time.
• Optional load_end_date. Some teams add an end-date (updated when the next version arrives) for query convenience, but the purist insert-only approach derives it at query time with LEAD(load_date).

The hashdiff — the whole change-detection story.

• What it is. SHA-256(concat_ws('||', standardized_attr_1, standardized_attr_2, ...)) over all descriptive columns. One column that fingerprints the entire attribute set.
• Why it exists. Comparing every attribute column-by-column on every load is verbose and error-prone (especially with NULLs). One hashdiff comparison replaces N column comparisons.
• The NULL trap. Every attribute must be coalesced to a sentinel before concatenation, or a NULL turns the whole hashdiff NULL and change detection breaks. This is the single most common satellite bug.
• Ordering and delimiters. Attributes must be concatenated in a fixed order with a reserved delimiter — same discipline as composite hash keys — so the hashdiff is stable and collision-safe.

Satellite splitting — one per source and per rate-of-change.

• One satellite per source. The CRM's view of a customer and the ERP's view get separate satellites (sat_customer_crm, sat_customer_erp). This keeps each source's history clean and its load independent, and it is why integrating a new source is additive.
• One satellite per rate-of-change. Attributes that change constantly (a customer's last_login) should not share a satellite with attributes that rarely change (a customer's date_of_birth), or every login churns a full version of the stable attributes. Split by volatility to control row growth.
• Effectivity satellites. A special satellite that tracks when a relationship was active — attached to a link, holding an effective start/end for the driving key. Used to model "which address was the shipping address between these dates" without mutating the link.

Worked example — satellite DDL + hashdiff-based insert

Detailed explanation. The canonical satellite load computes a hashdiff over the incoming attributes and inserts a new version only if that hashdiff differs from the latest stored one for the parent. Walk through sat_customer_crm.

• Parent. hub_customer via customer_hk.
• Attributes. name, email, status.
• Hashdiff. SHA-256(name || email || status), standardized and NULL-coalesced.
• Insert rule. Insert only if the incoming hashdiff ≠ the latest stored hashdiff for that customer_hk.

Question. Write the satellite DDL and the hashdiff-based insert that appends a new version only when an attribute actually changed.

Input.

customer_hk	name	email	status	vs latest
C-42	Ada L.	[e-mail]	active	new (no prior)
C-42	Ada L.	[e-mail]	active	unchanged → skip
C-42	Ada L.	[e-mail]	churned	changed → insert

Code.

CREATE TABLE sat_customer_crm (
    customer_hk BYTEA        NOT NULL,
    load_date   TIMESTAMPTZ  NOT NULL,
    hashdiff    BYTEA        NOT NULL,
    name        TEXT,
    email       TEXT,
    status      TEXT,
    record_source TEXT       NOT NULL,
    PRIMARY KEY (customer_hk, load_date)
);

-- Hashdiff-based insert: append a version only if attributes changed
INSERT INTO sat_customer_crm
    (customer_hk, load_date, hashdiff, name, email, status, record_source)
WITH incoming AS (
    SELECT digest(upper(trim(customer_id)), 'sha256') AS customer_hk,
           now() AS load_date,
           digest(concat_ws('||',
             coalesce(name,'^^'), coalesce(email,'^^'), coalesce(status,'^^')
           ), 'sha256') AS hashdiff,
           name, email, status, 'CRM' AS record_source
    FROM   stg_customers
),
latest AS (                                  -- most recent version per parent
    SELECT DISTINCT ON (customer_hk) customer_hk, hashdiff
    FROM   sat_customer_crm
    ORDER  BY customer_hk, load_date DESC
)
SELECT i.customer_hk, i.load_date, i.hashdiff, i.name, i.email, i.status, i.record_source
FROM   incoming i
LEFT   JOIN latest l ON l.customer_hk = i.customer_hk
WHERE  l.hashdiff IS DISTINCT FROM i.hashdiff;   -- changed OR brand-new

Step-by-step explanation.

1. The satellite's primary key is (customer_hk, load_date) — one row per version per parent. The parent hash key joins back to hub_customer.
2. The incoming CTE computes the hashdiff over the three attributes, each coalesced to '^^' so a NULL email never nulls the whole hashdiff. The concatenation order and || delimiter are fixed.
3. The latest CTE picks the most recent stored version per parent with DISTINCT ON (customer_hk) ... ORDER BY load_date DESC (Postgres). This is the row the incoming attributes are compared against.
4. WHERE l.hashdiff IS DISTINCT FROM i.hashdiff is the change gate: IS DISTINCT FROM treats "no prior row" (l.hashdiff NULL) as different, so a brand-new key inserts, an unchanged reload is filtered out, and a real change inserts a new version.
5. The three input rows produce: an insert (new key), a skip (identical hashdiff), and an insert (status changed to churned) — exactly SCD Type-2 behavior, achieved with one insert-only statement and no UPDATE.

Output.

customer_hk	load_date	status	hashdiff action
C-42	09:00:00	active	inserted (new)
C-42	09:15:00	active	skipped (unchanged)
C-42	09:30:00	churned	inserted (changed)

Rule of thumb. Compute the hashdiff over all attributes with NULL sentinels and a fixed delimiter, compare it to the latest stored hashdiff with IS DISTINCT FROM, and insert only on a difference. That single pattern gives you SCD Type-2 history, idempotent reloads, and no wasted rows — with zero UPDATEs.

Worked example — splitting satellites by source and rate-of-change

Detailed explanation. Cramming every attribute of a customer into one satellite creates two problems: a busy attribute churns full versions of stable ones, and two sources fight over one table. The fix is splitting — one satellite per source, and within a source, one per rate-of-change. Walk through splitting customer attributes.

• By source. sat_customer_crm (name, email, status) and sat_customer_erp (tax_id, credit_limit).
• By rate-of-change. Split the CRM's fast last_login into sat_customer_crm_activity, separate from stable sat_customer_crm_profile.
• Why. A daily last_login change would otherwise version name/email daily too, exploding storage.

Question. Split the customer satellites by source and by rate-of-change, and quantify the row-growth difference.

Input.

Attribute	Volatility	Source	Target satellite
name, email	low	CRM	sat_customer_crm_profile
last_login	high (daily)	CRM	sat_customer_crm_activity
tax_id, credit_limit	low	ERP	sat_customer_erp

Code.

-- Split by source AND by rate-of-change
CREATE TABLE sat_customer_crm_profile (   -- low churn
    customer_hk BYTEA, load_date TIMESTAMPTZ, hashdiff BYTEA,
    name TEXT, email TEXT, status TEXT,
    PRIMARY KEY (customer_hk, load_date));

CREATE TABLE sat_customer_crm_activity (  -- high churn, isolated
    customer_hk BYTEA, load_date TIMESTAMPTZ, hashdiff BYTEA,
    last_login TIMESTAMPTZ, session_count INT,
    PRIMARY KEY (customer_hk, load_date));

CREATE TABLE sat_customer_erp (           -- different source, own satellite
    customer_hk BYTEA, load_date TIMESTAMPTZ, hashdiff BYTEA,
    tax_id TEXT, credit_limit_cents BIGINT,
    PRIMARY KEY (customer_hk, load_date));

-- Row-growth intuition (365 daily loads, 1 profile change, 365 logins):
--   ONE combined satellite:  ~365 rows (every login versions the profile too)
--   SPLIT satellites:        profile ~2 rows + activity ~365 rows
--   -> profile history stays tiny and readable

Step-by-step explanation.

1. Splitting by source gives sat_customer_crm_profile and sat_customer_erp separate lives. Each source loads its own satellite independently and in parallel, and neither can churn the other's history — the additive-integration property.
2. Splitting by rate-of-change isolates the daily last_login into sat_customer_crm_activity, away from the near-static name/email in sat_customer_crm_profile.
3. The row-growth math is the point: in a single combined satellite, each daily login changes the hashdiff (because last_login is part of it), so a full version — including the unchanged name and email — is inserted every day. Over a year that is ~365 rows for a customer whose profile never changed.
4. With the split, the profile satellite gets ~2 rows (only when name/email/status actually change) and the activity satellite absorbs the 365 daily rows. The profile history is now tiny, fast to scan, and readable.
5. The senior heuristic: put attributes that change together and at the same rate in one satellite; split anything whose volatility differs by an order of magnitude. Over-splitting adds joins; under-splitting explodes rows — the balance is per-attribute-group volatility.

Output.

Design	profile rows / yr	activity rows / yr
one combined satellite	~365	(same table)
split satellites	~2	~365

Rule of thumb. One satellite per source, and within a source one per rate-of-change. Never let a high-churn attribute share a satellite with stable ones — the hashdiff will version the stable attributes on every churn, silently multiplying your storage and slowing every history scan.

Worked example — an effectivity satellite for a changing driving key

Detailed explanation. Some relationships have a "driving key" that changes — e.g. an order's current shipping address, where the address can be reassigned. You don't mutate the link; instead an effectivity satellite records when each relationship version became effective and when it ended. Walk through tracking the active shipping address for an order.

• Link. link_order_address (order ↔ address), many-to-many over time.
• Driving key. The order — at any moment exactly one address is "current."
• Effectivity satellite. Records effective_from (its load_date) per link version; the end is derived from the next version's start.
• Query. "Which address was current on date D" via a window over the effectivity satellite.

Question. Model the effectivity satellite that tracks which shipping address was active for an order over time.

Input.

order	address link	effective_from	(derived) effective_to
O-1	addr A	2026-08-01	2026-08-04
O-1	addr B	2026-08-04	(open)

Code.

-- Effectivity satellite on the order-address link
CREATE TABLE eff_sat_order_address (
    order_address_hk BYTEA        NOT NULL,   -- the link version (order + address)
    order_hk         BYTEA        NOT NULL,   -- driving key
    load_date        TIMESTAMPTZ  NOT NULL,   -- = effective_from
    hashdiff         BYTEA        NOT NULL,
    is_active        BOOLEAN      NOT NULL,
    record_source    TEXT         NOT NULL,
    PRIMARY KEY (order_address_hk, load_date)
);

-- "Which address was active for each order on 2026-08-03?"
SELECT order_hk, order_address_hk, effective_from, effective_to
FROM (
    SELECT order_hk, order_address_hk,
           load_date AS effective_from,
           LEAD(load_date) OVER (PARTITION BY order_hk
                                 ORDER BY load_date) AS effective_to
    FROM   eff_sat_order_address
    WHERE  is_active
) v
WHERE  DATE '2026-08-03' >= effective_from
  AND (effective_to IS NULL OR DATE '2026-08-03' < effective_to);

Step-by-step explanation.

1. The effectivity satellite hangs off the link_order_address link and is keyed by the driving key (order_hk) so that "which address is current for this order" has a single answer at any time.
2. Each version's load_date is its effective_from. The satellite stays insert-only — a new active address is a new row, not an update.
3. effective_to is derived, not stored: LEAD(load_date) OVER (PARTITION BY order_hk ORDER BY load_date) gives the start of the next version, which is the end of the current one. The most recent version has a NULL effective_to (still open).
4. The point-in-time query filters effective_from <= D < effective_to to find the address active on date D — here, on 2026-08-03, address A (effective 08-01 to 08-04) is the answer.
5. This is how Data Vault models "the relationship that changes over time" without ever mutating the link: the link records that the co-occurrence ever happened; the effectivity satellite records when it was the active one.

Output.

order_hk	address active on 2026-08-03	effective window
O-1	addr A	2026-08-01 → 2026-08-04

Rule of thumb. When a relationship has a driving key whose "current" member changes, track it with an effectivity satellite keyed by the driving key, storing effective_from as load_date and deriving effective_to with LEAD. Never mutate the link — the link records that a co-occurrence existed; the effectivity satellite records when it was active.

Senior interview question on satellite history

A senior interviewer might ask: "You need full SCD Type-2 history of a customer's profile from a source that reloads the whole table nightly, with no CDC. Design the satellite, the change-detection logic, and the query that returns the customer's state as of any past date — and explain how you avoid inserting a version every night when nothing changed."

Solution Using a hashdiff satellite for insert-only SCD Type-2 with point-in-time reads

CREATE TABLE sat_customer_profile (
    customer_hk BYTEA        NOT NULL,
    load_date   TIMESTAMPTZ  NOT NULL,
    hashdiff    BYTEA        NOT NULL,
    name        TEXT,
    email       TEXT,
    tier        TEXT,
    record_source TEXT       NOT NULL,
    PRIMARY KEY (customer_hk, load_date)
);

-- Nightly full-reload, but insert ONLY changed rows (hashdiff gate)
INSERT INTO sat_customer_profile
    (customer_hk, load_date, hashdiff, name, email, tier, record_source)
WITH incoming AS (
    SELECT digest(upper(trim(customer_id)),'sha256') AS customer_hk,
           now() AS load_date,
           digest(concat_ws('||', coalesce(name,'^^'),
                    coalesce(email,'^^'), coalesce(tier,'^^')),'sha256') AS hashdiff,
           name, email, tier, 'CRM' AS record_source
    FROM   stg_customers_full          -- the whole table, every night
),
latest AS (
    SELECT DISTINCT ON (customer_hk) customer_hk, hashdiff
    FROM   sat_customer_profile
    ORDER  BY customer_hk, load_date DESC
)
SELECT i.*
FROM   incoming i
LEFT   JOIN latest l ON l.customer_hk = i.customer_hk
WHERE  l.hashdiff IS DISTINCT FROM i.hashdiff;

-- Point-in-time read: customer state AS OF any past date
SELECT customer_hk, name, email, tier, load_date AS valid_from,
       LEAD(load_date) OVER (PARTITION BY customer_hk
                             ORDER BY load_date) AS valid_to
FROM   sat_customer_profile
WHERE  customer_hk = digest('C-42','sha256')
QUALIFY DATE '2026-08-03'                        -- Snowflake QUALIFY;
        >= valid_from                            -- (Postgres: wrap in a subquery)
   AND (valid_to IS NULL OR DATE '2026-08-03' < valid_to);

Step-by-step trace.

Nightly load	incoming hashdiff	latest hashdiff	action
Night 1	H(active)	(none)	insert (new)
Night 2	H(active)	H(active)	skip (unchanged)
Night 3	H(active)	H(active)	skip (unchanged)
Night 4	H(churned)	H(active)	insert (changed)

Walking the trace: even though the source reloads the entire customer table every night, the hashdiff gate compares each incoming row's fingerprint to the latest stored version and inserts only on a difference. Nights 2 and 3 insert nothing because the profile did not move; night 4 inserts one new version when the tier changes. The point-in-time query then reconstructs any past state by treating each version's load_date as valid_from and the next version's load_date (via LEAD) as valid_to, so "state on 2026-08-03" resolves to the version whose window contains that date.

Output:

Metric	Value
Source load shape	full table, nightly
Rows inserted when unchanged	0 (hashdiff gate)
History type	SCD Type-2 (insert-only)
Point-in-time read	via LEAD(load_date) window
Updates issued	none

Why this works — concept by concept:

• Hashdiff change detection — one fingerprint column compares the entire attribute set against the latest version, so a full nightly reload inserts a new row only when something actually changed. No CDC needed; the satellite manufactures history from full snapshots.
• Insert-only SCD Type-2 — every change is a new row keyed by (customer_hk, load_date), so the complete version history is inherent. There is no valid_to to maintain at write time and no UPDATE to race.
• Derived validity windows — LEAD(load_date) turns the insert-only rows into [valid_from, valid_to) intervals at query time, giving exact point-in-time reads without storing an end date that could drift out of sync.
• NULL-safe hashdiff — coalescing every attribute to '^^' before hashing means a NULL email never nulls the fingerprint, so change detection stays correct — the failure mode that silently breaks naive implementations.
• Cost — one hashdiff computation and one latest-row lookup per incoming row at load time (O(rows) but insert-only), and one window scan per key at read time. Versus a mutable SCD-2 dimension, you trade a query-time window for zero write-time UPDATEs and a fully restartable, idempotent load — O(1) inserts, O(versions) point-in-time reads.

SCD
Topic — slowly-changing-data
Slowly-changing-dimension and history-tracking problems

Practice →

SQL Topic — sql SQL window-function and point-in-time problems

Practice →

---

5. Raw vault, business vault, automation, and marts

The raw vault stores source data as-is; the business vault computes PITs, bridges, and rules; marts serve a star schema on top

The mental model in one line: Data Vault layers itself — the raw vault holds hubs, links, and satellites loaded exactly as the source delivered them with no business logic, the business vault adds computed structures (point-in-time and bridge tables, plus rule-applied satellites) that make querying fast and conform sources, and the information mart on top is a plain star schema (or wide view) that BI queries — and the whole stack is generated by data vault automation frameworks (dbt with datavault4dbt or AutomateDV) that turn each hub/link/satellite into a templated macro call. The discipline is that no business logic ever touches the raw vault: it stays a faithful, auditable record of what each source said, and all interpretation happens in the business vault, where it can be re-run and corrected without ever losing the source truth.

The layers, top to bottom.

• Staging. The landing zone where hash keys and hashdiffs are computed once, before anything hits the vault. Ephemeral; not history.
• Raw vault. Hubs, links, and satellites loaded as-is. No business rules, no conforming, no cleansing beyond standardizing keys for hashing. This is the auditable system of record.
• Business vault. Computed structures on top of the raw vault: point-in-time (PIT) tables and bridge tables for performance, plus "computed satellites" that apply business rules (currency conversion, deduplication, master-data resolution). Fully rebuildable from the raw vault.
• Information marts. Star schemas, wide tables, or views the BI tools query. Disposable and re-derivable; the mart is a presentation of the vault, not a store of truth.

PIT and bridge tables — the business-vault performance layer.

• Point-in-time (PIT) table. For one hub, a PIT precomputes, for each snapshot date, the exact load_date to use in each of that hub's satellites. It turns an expensive "find the latest satellite row per satellite as of date D" into a single equi-join. It is a pure performance structure — it stores no new truth.
• Bridge table. Precomputes the join across a hub and several links (and their PITs) so a query that would traverse many links becomes one join. Bridges flatten the many-hop link graph for a specific query pattern.
• Both are disposable. PITs and bridges are rebuilt on a schedule (often per snapshot date) and can be dropped and regenerated from the raw vault at any time. Losing them costs performance, never data.

Automation — why you never hand-write vault SQL at scale.

• The boilerplate is extreme and repetitive. Every hub load is the same shape; every satellite load is the same hashdiff pattern. Hand-writing hundreds of them is slow and error-prone.
• dbt + a vault package. datavault4dbt and AutomateDV (formerly dbtvault) provide macros: you declare the business keys, source columns, and parent, and the macro generates the insert-only, deduped, hashdiff-gated SQL.
• Metadata-driven. The vault's structure lives in configuration (YAML), so adding a satellite is a config edit plus a macro call — not a hand-written CTE.
• Consistency. Generated loads all standardize, hash, and dedupe identically, which eliminates the standardization-drift bug that splits hubs.

Serving the mart — star schema on top of the vault.

• Dimensions from hubs + satellites. A dim_customer is a view that joins hub_customer to its latest (or PIT-selected) satellite rows.
• Facts from links + link-satellites. A fact_orders is a view over link_order_line joined to its satellites and to the hubs for the business keys.
• PIT-accelerated. The mart views join through PIT tables so the "latest as of snapshot" selection is a cheap equi-join, not a correlated subquery per satellite.

Worked example — building a point-in-time (PIT) table

Detailed explanation. A PIT table precomputes, for a hub and a set of snapshot dates, the load_date to use in each satellite — collapsing "latest version as of date" into a join. Walk through a PIT for hub_customer over two satellites.

• Hub. hub_customer.
• Satellites. sat_customer_profile, sat_customer_activity.
• Snapshots. One row per customer per snapshot date (e.g. daily).
• Content. For each (customer_hk, snapshot_date), the max load_date <= snapshot_date in each satellite.

Question. Build the PIT table that, for each customer and snapshot date, records which satellite version was current.

Input.

customer_hk	snapshot_date	profile load_date ≤ snap	activity load_date ≤ snap
C-42	2026-08-03	2026-08-01	2026-08-03
C-42	2026-08-05	2026-08-04	2026-08-05

Code.

CREATE TABLE pit_customer (
    customer_hk        BYTEA        NOT NULL,
    snapshot_date      DATE         NOT NULL,
    profile_load_date  TIMESTAMPTZ,          -- version to use in sat_customer_profile
    activity_load_date TIMESTAMPTZ,          -- version to use in sat_customer_activity
    PRIMARY KEY (customer_hk, snapshot_date)
);

INSERT INTO pit_customer (customer_hk, snapshot_date, profile_load_date, activity_load_date)
SELECT h.customer_hk,
       d.snapshot_date,
       (SELECT max(load_date) FROM sat_customer_profile p
         WHERE p.customer_hk = h.customer_hk
           AND p.load_date <= d.snapshot_date)  AS profile_load_date,
       (SELECT max(load_date) FROM sat_customer_activity a
         WHERE a.customer_hk = h.customer_hk
           AND a.load_date <= d.snapshot_date)  AS activity_load_date
FROM   hub_customer h
CROSS  JOIN (SELECT generate_series(DATE '2026-08-01',
                                    DATE '2026-08-05', INTERVAL '1 day')::date
             AS snapshot_date) d;

-- Consuming the PIT: a fast equi-join instead of correlated max() per satellite
SELECT pit.customer_hk, pit.snapshot_date, pr.tier, ac.session_count
FROM   pit_customer pit
LEFT   JOIN sat_customer_profile  pr
       ON pr.customer_hk = pit.customer_hk AND pr.load_date = pit.profile_load_date
LEFT   JOIN sat_customer_activity ac
       ON ac.customer_hk = pit.customer_hk AND ac.load_date = pit.activity_load_date
WHERE  pit.snapshot_date = DATE '2026-08-03';

Step-by-step explanation.

1. The PIT is keyed by (customer_hk, snapshot_date) and stores, per satellite, the exact load_date that was current as of that snapshot — computed with max(load_date) <= snapshot_date per satellite.
2. Building it is a one-time (per snapshot batch) cost: a CROSS JOIN of the hub against the snapshot-date series, with a correlated max per satellite. Expensive to build, cheap to use.
3. Consuming the PIT is the payoff: instead of a correlated subquery per satellite at query time, the consumer does a plain equi-join ON load_date = pit.<sat>_load_date. The "which version was current" logic is precomputed.
4. This is a business-vault structure — it stores no new truth, only a performance-oriented index into the raw vault's satellites. It can be dropped and rebuilt from the raw vault at any time.
5. PITs are typically rebuilt per snapshot period (daily/weekly) and often kept only for recent snapshots plus period-ends, trading storage for the query speedup on the dates people actually query.

Output.

customer_hk	snapshot_date	profile version	activity version
C-42	2026-08-03	2026-08-01	2026-08-03
C-42	2026-08-05	2026-08-04	2026-08-05

Rule of thumb. Build a PIT per hub whose satellites are queried "as of a date" — it converts a per-satellite correlated max(load_date) into a single equi-join. PITs (and bridges) live in the business vault, store no truth, and are always rebuildable from the raw vault, so treat them as disposable performance caches.

Worked example — a dbt-templated satellite load

Detailed explanation. At scale you declare a satellite in dbt config and let a vault package generate the hashdiff-gated, insert-only SQL. Walk through an AutomateDV-style satellite model plus its YAML metadata.

• Model file. A short .sql that calls the package macro.
• Metadata. YAML declaring the parent key, hashdiff, source columns, and load metadata columns.
• Generated SQL. The macro expands to the same insert-only hashdiff pattern you'd hand-write.
• Result. Adding a satellite is a config edit, not a hand-written CTE.

Question. Write the dbt satellite model and its metadata so the package generates the insert-only, hashdiff-gated load.

Input.

Config key	Value
src_pk	CUSTOMER_HK
src_hashdiff	CUSTOMER_HASHDIFF
src_payload	NAME, EMAIL, TIER
src_ldts	LOAD_DATE
source_model	stg_customers

Code.

-- models/raw_vault/sat_customer_profile.sql  (AutomateDV-style)
{{ config(materialized='incremental') }}

{%- set yaml_metadata -%}
source_model: stg_customers
src_pk: CUSTOMER_HK
src_hashdiff: CUSTOMER_HASHDIFF
src_payload:
  - NAME
  - EMAIL
  - TIER
src_ldts: LOAD_DATE
src_source: RECORD_SOURCE
{%- endset -%}

{% set metadata_dict = fromyaml(yaml_metadata) %}

{{ automate_dv.sat(src_pk=metadata_dict['src_pk'],
                   src_hashdiff=metadata_dict['src_hashdiff'],
                   src_payload=metadata_dict['src_payload'],
                   src_ldts=metadata_dict['src_ldts'],
                   src_source=metadata_dict['src_source'],
                   source_model=metadata_dict['source_model']) }}

# The staging layer computes the hash key + hashdiff ONCE (also templated):
# models/staging/stg_customers.yml
stg_customers:
  hashed_columns:
    CUSTOMER_HK: CUSTOMER_ID          # hub hash key
    CUSTOMER_HASHDIFF:                # satellite change-detection hash
      is_hashdiff: true
      columns:
        - NAME
        - EMAIL
        - TIER
  derived_columns:
    RECORD_SOURCE: "'CRM'"
    LOAD_DATE: "CURRENT_TIMESTAMP()"

Step-by-step explanation.

1. The stg_customers staging model (its YAML shown second) computes CUSTOMER_HK and CUSTOMER_HASHDIFF once, applying the standardization and NULL-coalescing rules uniformly — so every downstream structure reuses identical hashes.
2. The satellite model is nearly empty: it declares the parent key, the hashdiff column, the payload columns, and the load-metadata columns, then calls automate_dv.sat(...). The macro expands to the full insert-only, hashdiff-gated SQL you would otherwise hand-write.
3. materialized='incremental' plus the package's logic means each run inserts only rows whose hashdiff differs from the latest stored version — the same change gate as the hand-written CTE, generated for free.
4. Adding a new satellite (say sat_customer_activity) is a copy of this file with a different payload list — a config edit, not new logic. This is what makes vault automation scale to hundreds of tables.
5. Because every generated load standardizes and hashes through the same macros, the standardization-drift bug (two authors trimming differently and splitting a hub) is eliminated by construction — consistency is the quiet superpower of automation.

Output.

Artifact	Hand-written	Templated (dbt package)
Lines per satellite	~25 (CTEs)	~10 (config + macro call)
Standardization drift risk	per-author	none (shared macros)
Add a satellite	new CTE	copy config, edit payload

Rule of thumb. Never hand-write vault load SQL at scale — declare hubs, links, and satellites in dbt with datavault4dbt or AutomateDV and let the macros generate the insert-only, hashdiff-gated loads. The payoff is not just less typing; it is uniform standardization and hashing across every structure, which is what keeps integration correct.

Worked example — serving a star-schema information mart

Detailed explanation. The mart is a plain star schema (or wide view) built over the vault, so BI tools never see hubs and satellites. Walk through a dim_customer and a fact_orders served from the vault via PITs.

• Dimension. dim_customer = hub_customer + latest/PIT-selected sat_customer_profile.
• Fact. fact_orders = link_order_line + sat_order_line + the participating hubs.
• PIT-accelerated. Dimension joins through pit_customer for the "current" version.
• Consumption. BI queries the mart, not the vault.

Question. Build the mart views that present a star schema (dim_customer, fact_orders) on top of the raw vault.

Input.

Mart object	Built from
dim_customer	hub_customer + sat_customer_profile (via pit_customer)
fact_orders	link_order_line + sat_order_line + hubs

Code.

-- Dimension: hub + current satellite (PIT-accelerated), business-friendly names
CREATE VIEW dim_customer AS
SELECT h.customer_id                       AS customer_natural_key,
       encode(h.customer_hk, 'hex')        AS customer_key,   -- stable surrogate
       pr.name, pr.email, pr.tier
FROM   hub_customer h
JOIN   pit_customer pit
       ON pit.customer_hk = h.customer_hk
      AND pit.snapshot_date = CURRENT_DATE          -- "current" snapshot
LEFT   JOIN sat_customer_profile pr
       ON pr.customer_hk = pit.customer_hk
      AND pr.load_date   = pit.profile_load_date;

-- Fact: link + link-satellite + hubs, at the order-line grain
CREATE VIEW fact_orders AS
SELECT encode(l.order_line_hk, 'hex')      AS order_line_key,
       ho.order_id, hc.customer_id, hp.product_id,
       s.qty, s.line_status
FROM   link_order_line l
JOIN   hub_order    ho ON ho.order_hk    = l.order_hk
JOIN   hub_customer hc ON hc.customer_hk = l.customer_hk
JOIN   hub_product  hp ON hp.product_hk  = l.product_hk
JOIN   LATERAL (
    SELECT qty, line_status FROM sat_order_line s
    WHERE  s.order_line_hk = l.order_line_hk
    ORDER  BY s.load_date DESC LIMIT 1
) s ON true;

Step-by-step explanation.

1. dim_customer joins hub_customer to its profile satellite, using pit_customer at today's snapshot to select the current version with a cheap equi-join. The BI tool sees a normal dimension with a stable surrogate (customer_key) and a readable natural key.
2. The hash key becomes the dimension's surrogate key (encode(...,'hex')), so it is stable across reloads — another benefit of derived keys: the mart's surrogate never has to be re-generated.
3. fact_orders presents the order-line grain by joining the link to the three hubs (for readable business keys) and to its link-satellite for the measures, taking the latest satellite version per line.
4. Both are views (or scheduled tables) — disposable presentations. The truth stays in the raw vault; the mart can be dropped and rebuilt, or reshaped for a new BI need, without touching the vault.
5. This is the payoff of the whole stack: the vault absorbs sources, keeps history, and audits; the business vault (PITs) makes "current version" a fast join; and the mart hands BI a clean star schema — each layer doing exactly one job.

Output.

Mart view	Grain	Current-version mechanism
dim_customer	one row per customer	pit_customer @ CURRENT_DATE
fact_orders	one row per order line	latest sat_order_line per link

Rule of thumb. Serve BI from star-schema views over the vault, use the hash key as the mart surrogate, and accelerate "current version" selection with PIT joins. Keep the mart disposable — all truth and history live in the raw vault, so a mart can be reshaped for a new question without a re-model or a data migration.

Senior interview question on serving a mart from a Data Vault

A senior interviewer might ask: "You have a raw vault with a customer hub, three customer satellites from three sources, and an order-line link with its own satellite. The BI team wants a fast star schema with a single conformed customer dimension and month-end snapshots. Walk me through the business-vault and mart design, and explain where conforming logic lives."

Solution Using a business vault to conform sources, a PIT for snapshots, and a star-schema mart

-- 1. BUSINESS VAULT: a computed satellite that CONFORMS three sources
--    (raw vault stays untouched; conforming logic lives HERE)
CREATE TABLE bsat_customer_conformed AS
SELECT customer_hk, load_date, name, email, tier, record_source
FROM (
    SELECT customer_hk, load_date, name, email, tier, record_source,
           ROW_NUMBER() OVER (
             PARTITION BY customer_hk
             ORDER BY CASE record_source            -- source-of-truth priority
                        WHEN 'CRM' THEN 1 WHEN 'ERP' THEN 2 ELSE 3 END,
                      load_date DESC) AS rn
    FROM (
        SELECT customer_hk, load_date, name, email, NULL::text AS tier, 'CRM' record_source
          FROM sat_customer_crm
        UNION ALL
        SELECT customer_hk, load_date, name, NULL, tier, 'ERP'
          FROM sat_customer_erp
    ) all_sources
) ranked
WHERE rn = 1;

-- 2. BUSINESS VAULT: month-end PIT over the conformed satellite
CREATE TABLE pit_customer_month AS
SELECT h.customer_hk, d.snap AS snapshot_date,
       (SELECT max(load_date) FROM bsat_customer_conformed b
         WHERE b.customer_hk = h.customer_hk AND b.load_date <= d.snap) AS conf_ldts
FROM   hub_customer h
CROSS  JOIN (SELECT DATE '2026-06-30' AS snap
             UNION ALL SELECT DATE '2026-07-31'
             UNION ALL SELECT DATE '2026-08-31') d;

-- 3. MART: conformed dimension, one row per customer per month-end
CREATE VIEW dim_customer_monthly AS
SELECT encode(pit.customer_hk,'hex') AS customer_key,
       pit.snapshot_date, b.name, b.email, b.tier
FROM   pit_customer_month pit
LEFT   JOIN bsat_customer_conformed b
       ON b.customer_hk = pit.customer_hk AND b.load_date = pit.conf_ldts;

Step-by-step trace.

Layer	Object	Job
Raw vault	sat_customer_crm / _erp	source truth, untouched
Business vault	bsat_customer_conformed	conform 3 sources by priority
Business vault	pit_customer_month	month-end version selection
Mart	dim_customer_monthly	star dimension for BI

Walking the trace: the three raw satellites stay exactly as loaded — the audit record is preserved. The conforming logic (source-of-truth priority: CRM beats ERP for name/email; ERP owns tier) lives in a business-vault computed satellite, bsat_customer_conformed, built with a ROW_NUMBER priority rank. A month-end PIT selects the conformed version current at each month-end, and the mart view exposes one conformed customer row per month-end to BI. Conforming happens once, in the business vault, and is fully rebuildable from the raw vault.

Output:

customer_key	snapshot_date	name (source)	tier (source)
8f2a...	2026-07-31	Ada L. (CRM)	gold (ERP)
8f2a...	2026-08-31	Ada L. (CRM)	platinum (ERP)

Why this works — concept by concept:

• Raw vault stays pure — the three source satellites are never edited, so the audit trail and provenance survive; every conforming decision can be revisited because the raw truth is intact.
• Business vault owns rules — conforming (source priority, master-data resolution) lives in a computed satellite, rebuildable from the raw vault. If the priority rule changes, you rebuild one table — no history is lost.
• PIT for snapshots — the month-end PIT precomputes which conformed version is current at each period-end, turning snapshot reads into equi-joins instead of correlated max(load_date) scans.
• Mart as presentation — the star dimension is a thin view over the conformed satellite via the PIT; BI never touches hubs or raw satellites, and the mart can be reshaped without a re-model.
• Cost — one extra computed-satellite build and one PIT build per snapshot batch (both O(rows), both rebuildable), in exchange for fast, conformed, point-in-time BI. You pay compute in the business vault to keep the raw vault pure and the mart cheap — the central trade of the layered architecture.

ETL
Topic — etl
ETL layering, PIT, and load-pipeline problems

Practice →

Modeling
Topic — dimensional-modeling
Star-schema and mart-on-vault design problems

Practice →

---

Cheat sheet — Data Vault 2.0 recipes

• The three structures in one line. Hub = distinct business keys (hash key + business key + load_date + record_source, keys only). Link = a relationship between two or more hubs (link hash key + one FK hash per hub, keys only, always many-to-many). Satellite = descriptive attributes over time hanging off one parent (parent hash key + load_date + hashdiff + attributes, insert-only). Everything else is a variant of these three.
• Hash-key recipe. hk = SHA-256(upper(trim(business_key))) for single keys; for composites, SHA-256(concat_ws('||', standardized_part_1, coalesce(part_2,'^^'), ...)) with a reserved delimiter and NULL sentinel. Standardize before hashing; freeze one algorithm warehouse-wide; store as BYTEA/BINARY(32). Compute the hash once in staging and reuse it everywhere.
• Why hash keys, not sequences. Hashes are derived from the business key, so every hub/link/satellite computes the same key independently → parallel, dependency-free, restartable loads and automatic cross-source joins. Sequences require a central generator → serialized loads and hub-before-link ordering. The cost is a wider key (16–32 bytes) and a negligible collision risk (use SHA-256).
• Hashdiff recipe. hashdiff = SHA-256(concat_ws('||', coalesce(attr_1,'^^'), coalesce(attr_2,'^^'), ...)) over all satellite attributes in a fixed order. Insert a new version only when the incoming hashdiff IS DISTINCT FROM the latest stored hashdiff — that one comparison gives SCD Type-2 history and skips unchanged reloads. Coalesce every attribute or a single NULL breaks detection.
• Load order (logical, not enforced). Compute keys in staging → load hubs → load links → load satellites. Because keys are hashed, this order is logical (referential integrity is guaranteed by matching hashes), so in practice all three load in parallel — there is no enforced FK dependency during load.
• Insert-only, always. No UPDATE, no DELETE on vault tables. Every change is a new row; dedupe on the hash makes reloads idempotent and failed loads safe to retry. This is the property that makes the whole methodology auditable and restartable.
• Link variants. Standard link (keys only, history in a link-satellite); non-historized / transactional link (immutable events — measures live on the link, no satellite); same-as / hierarchy link (two FKs to the same hub); degenerate field (a key-like relationship attribute with no hub of its own, used sparingly).
• Satellite splitting. One satellite per source (sat_customer_crm, sat_customer_erp) and, within a source, one per rate-of-change (fast last_login separate from stable name/email). Under-splitting explodes rows (busy attributes version stable ones); over-splitting adds joins. Split when volatility differs by an order of magnitude.
• Effectivity satellite. Tracks when a relationship version was active — keyed by the driving key, load_date as effective_from, effective_to derived with LEAD(load_date). Never mutate the link; the link says a co-occurrence existed, the effectivity satellite says when it was current.
• Raw vault vs business vault. Raw vault = source data as-is, no business logic, the auditable record. Business vault = computed structures (PIT tables, bridge tables, rule-applied "computed satellites" for conforming/currency/master-data). All interpretation lives in the business vault; it is fully rebuildable from the raw vault, so raw truth is never lost.
• PIT and bridge tables. PIT precomputes, per (hub_hk, snapshot_date), the load_date to use in each satellite → turns "latest as of date" into an equi-join. Bridge precomputes multi-link traversals → turns a many-hop join into one. Both store no truth, live in the business vault, and are disposable/rebuildable performance caches.
• Automation. Never hand-write vault SQL at scale — use dbt with datavault4dbt or AutomateDV. Declare business keys, payload, and parent in YAML; the macro generates the insert-only, deduped, hashdiff-gated load. The real win is uniform standardization/hashing across every structure, which prevents the drift bug that splits hubs.
• Data Vault vs Kimball vs 3NF. 3NF optimizes the transaction (source OLTP). Kimball optimizes the query (BI marts). Data Vault optimizes integration + history (the warehouse layer under the marts). They are layers, not competitors — serve Kimball star schemas as views/tables on top of the vault.

Frequently asked questions

What is Data Vault 2.0?

Data Vault 2.0 is a data-warehouse modeling methodology (and an accompanying set of load, architecture, and automation standards) designed for enterprise-scale integration of many source systems with full, auditable history. It decomposes every domain into three structure types — hubs (distinct business keys), links (relationships between hubs), and satellites (descriptive attributes over time) — and loads them insert-only using hash keys computed from the business keys so that loads run in parallel with no ordering dependency. The "2.0" specifically added hash keys (replacing DV 1.0's sequence surrogate keys) to enable parallel loading, plus standards for the raw-vault/business-vault split and for automation. It is not a query model — you build Kimball-style star-schema marts on top of the vault for BI — it is the integration and history layer beneath them.

What is the difference between a hub, a link, and a satellite?

A hub is a deduplicated list of business keys for one concept (customer, order, product); it holds the hash key, the business key, and load metadata (load_date, record_source) and nothing descriptive. A link records a relationship between two or more hubs — the co-occurrence "this customer placed this order" — and holds its own hash key plus one foreign hash key per participating hub, again keys only, and always modeled many-to-many. A satellite holds the descriptive, changeable attributes of exactly one parent (a hub or a link), keyed by the parent's hash key plus load_date, and versions every change insert-only. The mechanical test: a key that identifies a thing is a hub, a co-occurrence of keys is a link, and a describing value that can change is a satellite.

Why does Data Vault use hash keys instead of sequence surrogate keys?

Because hash keys are derived from the business key (e.g. SHA-256(upper(trim(customer_id)))), any load process can compute the same key independently without coordinating a central sequence generator — which is what makes hub, link, and satellite loads run in parallel with no ordering dependency and makes a failed load safe to re-run (recompute, dedupe, no-op). Sequence surrogate keys, by contrast, must be assigned by a single generator, forcing serialized loads and a "load the hub first to get its key" dependency. Hash keys also make cross-source integration automatic: two systems that call the customer C-42 produce the identical hash and collapse onto one hub row with no lookup table. The costs you must acknowledge are a wider key (16 bytes for MD5, 32 for SHA-256) and a negligible collision probability, mitigated by choosing SHA-256 and standardizing keys before hashing.

What is the difference between the raw vault and the business vault?

The raw vault stores hubs, links, and satellites loaded exactly as the source delivered them — no business rules, no conforming, no cleansing beyond standardizing keys for hashing — so it is the faithful, auditable system of record. The business vault sits on top and holds computed structures: point-in-time (PIT) and bridge tables for query performance, plus "computed satellites" that apply business logic such as source conforming, currency conversion, or master-data resolution. The critical discipline is that all interpretation lives in the business vault and none in the raw vault, so the business vault is always fully rebuildable from the raw vault and source truth is never lost. If a business rule changes, you rebuild the affected business-vault objects; the raw vault — and therefore your audit history — is untouched.

Data Vault vs Kimball — when do I pick each?

Pick a Kimball star schema directly when you have one or a few clean sources, a BI team that wants dimensions and facts, and no hard requirement to reproduce past state with provenance — it is simpler, faster to build, and cheaper to query. Reach for Data Vault when you must integrate many overlapping source systems, keep a fully auditable point-in-time history, absorb frequent source schema changes, and load large volumes in parallel — the enterprise-integration pain that Kimball's conformed-dimension design handles serially and expensively. The two are not mutually exclusive: the common enterprise pattern is Data Vault as the integration + history layer with Kimball star schemas served as marts on top, so BI keeps its familiar star while the warehouse gains vault-grade integration and audit. The honest rule is that Data Vault only pays off with enough sources and automation to amortize its higher table count and join depth.

Is Data Vault overkill for my warehouse?

Often, yes — Data Vault is a solution to a specific, expensive problem (many sources, hard audit, high schema churn), and adopting it without that problem buries a small team in boilerplate for no benefit. A single clean source feeding a BI tool almost never justifies a vault; a star schema is the right, cheaper answer. Data Vault starts to earn its keep when your source count climbs into the high single digits or beyond, a regulator or auditor demands point-in-time reproduction with provenance, and your sources change shape often enough that additive integration (new satellites) beats repeated dimension re-design. Even then, it is overkill without automation — hand-writing hundreds of hub/link/satellite loads is the classic failure mode, so treat "do we have dbt plus datavault4dbt or AutomateDV standing by?" as a precondition, not an afterthought.

Practice on PipeCode

• Drill the dimensional modeling practice library → for the hub/link/satellite decomposition, star-schema-on-vault, and PIT/bridge design problems senior modeling interviews love.
• Rehearse the DDL and hashing on the SQL practice library → for the hash-key computation, hashdiff change detection, window-function point-in-time, and dedupe problems that make up a vault load.
• Sharpen the pipeline axis with the ETL practice library → for the staging, insert-only load, raw-vault/business-vault layering, and slowly-changing-history patterns.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the three-structure model against real graded inputs — from slowly-changing-data problems → to end-to-end design problems →.

Lock in Data Vault muscle memory

Docs explain the structures. PipeCode drills explain the decision — when a hub versus a link versus a satellite, why a hash key beats a sequence, when the hashdiff should skip a load, where conforming logic belongs, and how to serve a star schema without the join fan-out biting. Pipecode.ai is Leetcode for Data Engineering — modeling-first practice tuned for the production trade-offs data engineers and architects actually face.

Practice modeling problems →
Practice design problems →