Data Vault 2.0

Chapter 12 placed Data Vault beside Inmon and Kimball. This chapter is about how it actually works, because the mechanics are exact and most of what goes wrong with a vault is a mechanical mistake rather than a strategic one.

Step the walkthrough below through one row of an aircraft maintenance feed. Three business keys become hubs, then a link, then satellites — and the last step shows what it costs to put them back together again.

hub

A table of unique business keys plus a hash key, load date, and source.

link

A table recording a relationship/association between hubs.

satellite

A table of descriptive attributes and their history, attached to a hub or link.

hash key

A hash of the business key used as the surrogate, enabling parallel, lookup-free loads.

Three pressures justify the extra tables. You have to prove exactly what arrived, from where and when, and reproduce any past state. Sources change constantly. Many teams load at once. Where none of those bite, Data Vault is overhead.

The second pressure is worth feeling rather than reading about. Call each of the four changes below before you reveal it: three are absorbed by adding a table and touching nothing that already exists, and the fourth is the one no architecture absorbs cheaply.

auditability

Proving and reproducing exactly what arrived, from where, and when.

source resilience

Adding/changing a source by extension (new satellite) rather than rewrite.

parallel loading

Loading hubs, links, and satellites independently and concurrently.

A hub is the unique list of one business key and almost nothing else: the key, a hash of it, when it first appeared, and which system reported it. No descriptions, no relationships, nothing that can later change.

That austerity is the entire point, because the hub is where every source has to agree on identity. Change the key in the workbench below and watch two systems stop agreeing.

business key

The durable, source-independent identifier of an entity (customer_id).

hash key (hub)

A hash of the business key, used as the surrogate across the vault.

record_source

The audit column naming which source a row came from.

NULL sentinel

An agreed literal substituted for NULL before hashing so composite keys stay deterministic — UNKNOWN in many DV2 teams, _dbt_utils_surrogate_key_null_ in dbt (Chapter 3).

create table hub_part (
  part_hk       bytea       primary key,     -- hash of part_serial (the surrogate)
  -- bytea = Postgres-native binary hash. CHAR(32) MD5 hex
  -- (as in Ch. 12's sketch) is equally standard.
  part_serial   text        not null unique,  -- the business key
  load_dts      timestamptz not null,         -- when first seen
  record_source text        not null          -- which system reported it
);

-- One row per distinct part_serial, ever. No description, no condition,
-- no work order. Everything that can change lives elsewhere.
insert into hub_part values (sha256('SN-77-04412'), 'SN-77-04412', now(), 'maint_core');

A link records that two or more hubs are associated. Where a dimensional model puts a foreign key on a fact, Data Vault gives the relationship its own table: the hash keys of the participating hubs, its own hash key, and audit columns. Many-to-many needs no special handling, because a link is only ever a set of associations.

That last word does the damage. A link asserts that a pairing happened, never that it is still true — and since links are insert-only, an ended relationship cannot be expressed by deleting the row. Run the same question against the three designs below and watch two of them over-report without erroring.

link

A table of associations between hubs, modeling a relationship as its own object.

many-to-many by default

Links naturally express M:N because they are just sets of associations.

link satellite

A satellite attached to a link, holding the relationship's attributes/measures.

effectivity satellite

A satellite on a link recording whether the association is currently live — the only way an insert-only link can express "this ended".

create table link_fitment (
  fitment_hk    bytea       primary key,   -- hash of the combined business keys
  airframe_hk   bytea       not null references hub_airframe(airframe_hk),
  part_hk       bytea       not null references hub_part(part_hk),
  workorder_hk  bytea       not null references hub_workorder(workorder_hk),
  load_dts      timestamptz not null,
  record_source text        not null
);

-- Attributes of the relationship go in a satellite on the link:
create table sat_link_fitment (
  fitment_hk    bytea       not null references link_fitment(fitment_hk),
  load_dts      timestamptz not null,
  position      text,                      -- 'LH', 'RH', 'APU bay'
  torque_nm     numeric(6,1),
  primary key (fitment_hk, load_dts)
);

-- ...and its lifecycle goes in an effectivity satellite, which is what
-- makes "was this still fitted on 1 July?" answerable at all:
create table sat_link_fitment_eff (
  fitment_hk    bytea       not null references link_fitment(fitment_hk),
  load_dts      timestamptz not null,
  is_fitted     boolean     not null,      -- flips false on removal
  primary key (fitment_hk, load_dts)
);

A satellite hangs off a hub or a link and holds the attributes that describe it, keyed on the parent hash key plus load_dts. Every change inserts a new dated row instead of overwriting, so a satellite is Type 2 history by construction — the same versioning you hand-built in Chapter 11, produced by the loading rule rather than by bespoke SCD code.

Whether a row is written at all is decided by the hash_diff: a hash of the whole payload, compared against the previous row. Two settings control that machinery, and the replay below shows what each one costs when a file is delivered twice and when a part changes twice in one day.

satellite

A table of an entity's descriptive attributes with full, dated history.

hash_diff

A hash of all attribute values, compared to detect whether a change occurred.

insert-only history

Each attribute change is a new dated row; nothing is updated or deleted.

multi-active satellite

A satellite whose PK includes a discriminator so multiple rows are active simultaneously.

idempotent load

A load that can be re-run without changing the result — what hash_diff buys you when an extract is delivered twice.

create table sat_part_maint (
  part_hk       bytea       not null references hub_part(part_hk),
  load_dts      timestamptz not null,  -- timestamp, not date: a part can
                                       -- change twice in one day
  condition     text,
  cycles_ovh    int,
  hash_diff     bytea       not null,  -- hash of (condition, cycles_ovh)
  record_source text        not null,
  primary key (part_hk, load_dts)      -- one row per detected change
);

-- Some facts are genuinely plural. An airframe holds several airworthiness
-- directives at once, so a standard satellite (one row per parent per
-- load_dts) cannot represent them. Adding a discriminator to the PK can.
create table sat_airframe_ad (
  airframe_hk   bytea       not null references hub_airframe(airframe_hk),
  load_dts      timestamptz not null,
  ad_ref        text        not null,  -- 'AD-2026-11-03' — part of the PK
  status        text,                  -- 'open', 'closed', 'deferred'
  hash_diff     bytea       not null,
  record_source text        not null,
  primary key (airframe_hk, load_dts, ad_ref)
);

Here is the bill for all that decomposition. A perfectly ordinary question — what is fitted to this aircraft, and in what condition — touches six objects and needs a correlated subquery per satellite to pick the row that was current. Run the three routes below and watch the cost move.

A Point-in-Time table stores, per snapshot date and entity, the load_dts of the row that was current in each satellite, turning every one of those subqueries into an equality join. A Bridge table does the same job across hubs, pre-materialising a multi-hop link path so a query need not re-walk the chain. Both are rebuilt on a schedule, which is where they get dangerous.

PIT table

A pre-computed table mapping each snapshot date and entity to the correct satellite row load_dates.

Bridge table

A pre-computed table materializing multi-hop link paths between hub entities.

PIT staleness

The gap when a satellite row arrives after the PIT was last rebuilt, making the PIT pointer stale.

snapshot date

The business date at which a PIT query resolves satellite row versions.

-- One PIT per hub, covering every satellite on that hub.
create table pit_part (
  part_hk             bytea       not null,
  snapshot_date       date        not null,
  -- Per satellite: the load_dts of the most recent row whose
  -- load_dts <= snapshot_date. NULL if no row existed yet — which is
  -- why vaults insert a zero-key "ghost" row into each satellite, so
  -- the equi-joins above stay inner joins instead of becoming outer ones.
  sat_part_maint_ldts timestamptz,
  sat_part_ref_ldts   timestamptz,
  sat_eff_ldts        timestamptz,
  primary key (part_hk, snapshot_date)
);

-- If a PIT must never be stale, a view is always current...
create view pit_part_live as
select p.part_hk,
       current_date            as snapshot_date,
       max(m.load_dts)         as sat_part_maint_ldts
from   hub_part p
join   sat_part_maint m on m.part_hk = p.part_hk
group  by p.part_hk;
-- ...but it recomputes the very scan the PIT existed to avoid.
-- Pick one: pre-computed and scheduled, or live and expensive.

A surrogate key you have to look up forces an order: the hub must finish loading before anything that references it can start, because the loader has to read the hub to learn the key. A hash of the business key removes that entirely — every loader derives the same key from the source data it already holds, so nothing waits for anything.

Schedule the six jobs below and see what that buys. Then switch to surrogate lookups and watch the same schedule fall apart into a dependency chain.

One hard rule protects all of it: no business logic in the raw vault. Derivations belong in the business vault or the marts, because a rule applied on the way in cannot be un-applied when it turns out to be wrong, and reprocessing then diverges from what was originally recorded.

hash key loading

Computing surrogates as hashes so tables load in parallel without lookups.

business vault

A layer of derived vault objects applying business logic atop the raw vault.

consumption layer

The dimensional marts built on the vault for users to query.

orphan record

A link or satellite row loaded before its parent hub row exists — the early-arriving load-order failure. (Distinct from a DV2 ghost record: the zero-key placeholder row inserted into satellites so PIT equi-joins always resolve.)

-- A Type-2 dimension is just hub + satellite history, with the end date
-- derived rather than stored. Nothing in the vault records "valid_to".
create view dim_part as
select
  s.part_hk              as part_key,     -- surrogate from the hash key
  h.part_serial,                          -- business key
  s.condition,
  s.cycles_ovh,
  s.load_dts             as valid_from,
  lead(s.load_dts) over (                 -- the next change closes this version
    partition by s.part_hk order by s.load_dts
  ) as valid_to
from hub_part h
join sat_part_maint s on s.part_hk = h.part_hk;

Reference tables are the vault's home for code and lookup data: condition codes, status enums, currency codes. They sit outside the hub/link/satellite structure because they are neither business entities nor relationships — just stable lookups with a code, a description and the same audit columns, versioned insert-only so a code whose meaning changed can still be read as it was.

The bigger problem at scale is repetition. A vault with fifty sources and four hundred satellites cannot be hand-written and stay consistent, so teams generate it: AutomateDV (formerly dbtvault) is a dbt macro library that emits hub, link, satellite, PIT and Bridge loads from YAML, and Roelant Vos's DV Accelerator follows the same pattern. What they really provide is not typing saved but conventions that cannot be forgotten — switch them off below and read what each one was holding up.

reference table

A versioned lookup table of code/description pairs with audit columns, outside the hub/link/sat structure.

AutomateDV

A dbt macro library (formerly dbtvault) that generates DV2 hub, link, satellite, PIT, and Bridge SQL from YAML metadata.

dbt macro

A Jinja template in dbt that generates SQL at compile time, used by AutomateDV to enforce DV2 conventions.

-- Reference tables sit outside hub/link/sat but carry the same
-- audit columns and are loaded insert-only.
create table ref_part_condition (
  condition_code text        primary key,
  description    text        not null,
  load_dts       timestamptz not null,
  record_source  text        not null
);

insert into ref_part_condition values
  ('NEW', 'Never installed, factory packed',        now(), 'maint_core'),
  ('SVC', 'Serviceable — cleared for installation', now(), 'maint_core'),
  ('OVH', 'Overhauled to zero-time condition',      now(), 'maint_core'),
  ('REP', 'Repaired, awaiting recertification',     now(), 'maint_core'),
  ('SCR', 'Scrapped — must not be installed',       now(), 'maint_core');

# Declare the mapping once; the macros emit the DML.
# This replaces a hand-written hub_part.sql and sat_part_maint.sql.

models:
  hub_part:
    source_model: staged_parts
    src_pk: part_hk              # AutomateDV computes the hash
    src_nk: part_serial          # natural/business key
    src_ldts: load_dts
    src_source: record_source

  sat_part_maint:
    source_model: staged_parts
    src_pk: part_hk
    src_hashdiff: hash_diff      # AutomateDV computes hash_diff
    src_payload:
      - condition
      - cycles_ovh
    src_ldts: load_dts
    src_source: record_source

Six object types carry the whole methodology, and knowing them is not the same as knowing which one answers a given question. Route the eight below — two of them are the exact distinctions this chapter watched go wrong.

Four design scenarios for this chapter, on the ERD canvas. Each one is graded against a real rubric, seeded with real rows, and each has one fault planted in the data rather than in the diagram.

Build them with the chapter closed. If one goes wrong, come back to the section it belongs to rather than re-reading the whole thing.