Data Vault

Hubs, links and satellites

A warehouse with one wide customer table works until a second source arrives. Your company acquires a competitor whose CRM describes customers with 14 attributes, 9 of which yours has never heard of. Widening the table means editing and retesting each job that loads it and each view or report that reads it. Data Vault splits a model into 3 kinds of table so that a new source adds tables and edits none.

The 3 table types

• Hub: A business key is the identifier the business already uses: an order number, an email, a product code. The hub stores only that key, the first time it was seen, and the source that supplied it.
• Link: Customer c_204 placed order 51833: that fact is a link row. A link carries the hash keys of the hubs it joins plus load time and source, and no descriptive attributes.
• Satellite: Names, cities, plans, prices: every descriptive attribute lives in a satellite, stamped with when it loaded and which system said so. Rows are added, never updated.

customer_hk is a hash of the email. Link rows and satellite rows use that fixed-length value to refer to a customer, so the email is never repeated. Each satellite belongs to one source: sat_customer_crm holds what the CRM says about a customer and sat_customer_billing holds what the billing system says, and the 2 are never merged in storage. Here is the CRM satellite for 1 customer after 3 loads:

No row was overwritten when Ana moved or upgraded. Reading her current state means taking the newest row per customer:

SELECT
  h.customer_email,
  s.city,
  s.plan
FROM hub_customer h
JOIN (
  SELECT
    customer_hk,
    city,
    plan,
    row_number() OVER (
      PARTITION BY customer_hk
      ORDER BY load_ts DESC
    ) AS rn
  FROM sat_customer_crm
) s
  ON s.customer_hk = h.customer_hk
WHERE s.rn = 1

Add WHERE load_ts <= TIMESTAMP '2024-03-01 00:00:00' to the inner query and the Porto, basic row becomes the newest. That is what the warehouse knew on 2024-03-01, because load_ts records when a change loaded. When Ana actually moved is the source's effective date, which belongs in the satellite as its own column. Every row also names the system that asserted it, so an auditor gets a source and a timestamp.

Data Vault

• New source: add a satellite, touch nothing else
• History: every load kept, as-of queries by load_ts
• Audit: record_source on every row
• Queries: 3 to 6 joins to assemble one customer

One wide customer table

• New source: alter the table and edit everything that loads or reads it
• History: last value wins
• Audit: who said the city is Porto is unknown
• Queries: 1 table, written in a minute

The last row of that comparison is the price. Dashboards are rarely pointed at a vault directly. The vault is the storage behind the silver layer, and flatter tables are built from it for analysts. Before those, decide how a vault absorbs the acquisition.

About this lesson

This section is part of the Design Patterns lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.