Data Vault Model Diagram

This sample models customers, orders and products as a Data Vault 2.0: hubs store business keys, links store relationships between them, and satellites store descriptive attributes with full history. It is a common pattern for the integration layer of an enterprise data warehouse.

1. The Diagram

Orders: Data Vault 2.0 Model hub_customer PK hk_customer BINARY(32) BK customer_id VARCHAR load_dts TIMESTAMP record_source VARCHAR hub_order PK hk_order BINARY(32) BK order_number VARCHAR load_dts TIMESTAMP record_source VARCHAR hub_product PK hk_product BINARY(32) BK sku VARCHAR load_dts TIMESTAMP record_source VARCHAR lnk_customer_order PK hk_customer_order BINARY(32) FK hk_customer BINARY(32) FK hk_order BINARY(32) load_dts TIMESTAMP lnk_order_product PK hk_order_product BINARY(32) FK hk_order BINARY(32) FK hk_product BINARY(32) load_dts TIMESTAMP sat_customer PK,FK hk_customer PK load_dts hash_diff BINARY name, email, city sat_order PK,FK hk_order PK load_dts hash_diff BINARY status, order_date sat_product PK,FK hk_product PK load_dts hash_diff BINARY name, list_price sat_order_line PK,FK hk_order_product PK load_dts quantity unit_price Blue = hub (business key) Yellow = link (relationship) Green = satellite (descriptive history)

Data Vault 2.0 (Dan Linstedt) splits every concept into keys, relationships and descriptive history.

  1. The three hubs in the middle row each hold one business key: customer ID, order number, SKU. Each also gets a hash key (here SHA-256, so BINARY(32)), a load timestamp and a record source.
  2. Links (yellow, bottom) record that keys relate. lnk_customer_order says this customer placed this order; lnk_order_product says this order contained this product. Links are always many-to-many, so a changed business rule never forces a redesign.
  3. Satellites (green, top) carry the descriptive columns. A new row is inserted only when hash_diff, a hash of the attribute values, changes; the history of every name, status and price is kept.
  4. sat_order_line hangs off a link: quantity and unit price describe the order-product pairing, not either hub alone.
  5. All tables are insert-only. Nothing is updated, which makes loads parallel and the warehouse fully auditable.

2. How to Read It

Data Vault Building Blocks Hub unique business key hash key + load_dts + source Link relationship between two or more hubs (hash keys) Satellite descriptive attributes versioned by load_dts Rules: hubs never connect to hubs directly; satellites hang off exactly one hub or link.

There is no official Data Vault notation; teams draw it as an ER diagram with colours for each table type (blue hubs, yellow or red links, green satellites is a common convention). Line ends follow crow's foot: one hub row matches zero or many satellite and link rows.

3. Variant: Load Flow

Load Flow: Source to Information Mart Sources CRM, shop, ERP Staging compute hash keys Raw Vault (insert-only) Hubs Links Sats loaded in parallel no joins between loads Business Vault PIT, bridge, derived sats Information Mart star schemas for BI The vault keeps auditable history; marts are rebuilt from it for consumers.

The vault is the integration layer, not what analysts query. Staging computes hash keys, hubs, links and satellites load independently, and a business vault adds point-in-time (PIT) and bridge tables. Information marts, typically star schemas, are then built on top for reporting.

4. Pros, Cons, and When to Use

Pros

Cons

When to use

When not to use

5. As Diagrams-as-Code

Mermaid erDiagram captures the tables and lines; colours must be added by hand or in your tool:

erDiagram
    hub_customer ||--o{ sat_customer       : describes
    hub_order    ||--o{ sat_order          : describes
    hub_product  ||--o{ sat_product        : describes
    hub_customer ||--o{ lnk_customer_order : ""
    hub_order    ||--o{ lnk_customer_order : ""
    hub_order    ||--o{ lnk_order_product  : ""
    hub_product  ||--o{ lnk_order_product  : ""
    lnk_order_product ||--o{ sat_order_line : describes
    hub_customer {
        binary hk_customer PK
        varchar customer_id
        timestamp load_dts
        varchar record_source
    }
    lnk_order_product {
        binary hk_order_product PK
        binary hk_order FK
        binary hk_product FK
        timestamp load_dts
    }