Data Vault 2.0 Tutorial: Hubs, Links and Satellites With Hash Keys and DDL

Data Vault 2.0 models an integration layer out of three kinds of table. Hubs hold business keys. Links hold relationships between business keys. Satellites hold descriptive attributes and their history. Every other Data Vault construct (point-in-time tables, bridges, same-as links, effectivity satellites) is built from or on top of those three.

This tutorial works through a small but realistic example from source tables to DDL and load SQL, so you can see exactly what each table contains and why. It assumes you know basic SQL and have a rough idea of why teams choose Data Vault: many sources, full auditability, and a model that absorbs change without rework. For the comparison with dimensional modeling, see Data Vault vs Kimball.

The example: an online retailer with two source systems

• A CRM with customers: crm.customer(customer_number, name, email, phone, tier, updated_at).
• An order system with orders and order lines: shop.orders(order_no, customer_number, order_date, status, channel) and shop.order_lines(order_no, line_no, sku, qty, unit_price).
• A product catalogue: pim.product(sku, title, category, list_price).

Step 1: Identify business keys (hubs)

A business key is the identifier the business uses to refer to something, and that stays stable across systems and time. It is not a database sequence number. Ask: "If I phoned someone in the business, what would they call this thing?"

• Customer: customer_number (printed on invoices, used in the CRM and order system)
• Order: order_no
• Product: sku

Each becomes a hub. Order lines are not a hub here: a line has no business key of its own beyond "order plus line number", and it is really the relationship between an order and a product. We will model it as a link.

Hub DDL

create table raw_vault.hub_customer (    hk_customer      char(32)     not null,  -- hash of the business key    customer_number  varchar(40)  not null,  -- the business key itself    load_dts         timestamp    not null,  -- when the key was first loaded    record_source    varchar(100) not null,  -- which system it came from first    constraint pk_hub_customer primary key (hk_customer));​create table raw_vault.hub_order (    hk_order       char(32)     not null,    order_no       varchar(40)  not null,    load_dts       timestamp    not null,    record_source  varchar(100) not null,    constraint pk_hub_order primary key (hk_order));​create table raw_vault.hub_product (    hk_product     char(32)     not null,    sku            varchar(40)  not null,    load_dts       timestamp    not null,    record_source  varchar(100) not null,    constraint pk_hub_product primary key (hk_product));

A hub contains nothing else. No names, no statuses, no foreign keys. One row per unique business key, ever. Rows are never updated or deleted.

Step 2: Hash keys

Data Vault 2.0 uses a hash of the business key as the primary key of each hub, rather than a sequence. The reasons are practical:

• Parallel loading. A link can compute hk_customer from the source row without first looking it up in the hub. Hubs, links and satellites can load at the same time.
• Deterministic. The same business key always produces the same hash, in any environment and on any platform, so reloads and multi-system integration are consistent.
• Uniform joins. Every key is the same fixed-width type.

The hashing rules must be standardised across the whole vault, because one inconsistency means the same customer gets two keys:

1. Cast to string.
2. Trim whitespace.
3. Standardise case (usually upper).
4. Replace null with an agreed placeholder, or reject the row.
5. For composite keys, concatenate with a fixed delimiter, such as ||.
6. Hash with an agreed algorithm. MD5 is common and produces 32 hex characters; SHA-1 or SHA-256 are also used where collision risk or policy requires it.

-- staging view for customers (Snowflake / Postgres syntax)create or replace view stg.crm_customer asselect    md5(upper(trim(cast(customer_number as varchar))))  as hk_customer,    upper(trim(cast(customer_number as varchar)))       as customer_number,    name, email, phone, tier,    md5(concat_ws('||',        coalesce(trim(name), ''),        coalesce(lower(trim(email)), ''),        coalesce(trim(phone), ''),        coalesce(trim(tier), '')    ))                                                  as hd_customer_crm,  -- hash diff    current_timestamp                                   as load_dts,    'crm.customer'                                      as record_sourcefrom crm.customer;

If two source systems use different formats for the same key (one pads C-00889, the other stores C-889), fix it in staging before hashing, or record both keys in the hub and connect them with a same-as link. Do not let the hash function hide the difference.

Step 3: Relationships (links)

A link records that business keys are related. Its primary key is a hash of the combined business keys of the hubs it connects.

• An order is placed by a customer: link_order_customer.
• An order line connects an order to a product: link_order_product, with the line number as a dependent child key.

create table raw_vault.link_order_customer (    hk_order_customer  char(32)     not null,  -- hash of order_no || customer_number    hk_order           char(32)     not null,    hk_customer        char(32)     not null,    load_dts           timestamp    not null,    record_source      varchar(100) not null,    constraint pk_link_order_customer primary key (hk_order_customer));​create table raw_vault.link_order_product (    hk_order_product   char(32)     not null,  -- hash of order_no || line_no || sku    hk_order           char(32)     not null,    hk_product         char(32)     not null,    line_no            integer      not null,  -- dependent child key    load_dts           timestamp    not null,    record_source      varchar(100) not null,    constraint pk_link_order_product primary key (hk_order_product));

Links are always many-to-many in structure, even when the business relationship is one-to-many today. That is a deliberate choice. If the business later allows orders to be split across two customers (a gift order with a payer and a recipient, say), the link already supports it and nothing needs remodelling.

Like hubs, links are insert-only. A link row says "this relationship was observed", not "this relationship is currently true". To track when a relationship starts and stops being valid, add an effectivity satellite on the link.

Step 4: Descriptive attributes (satellites)

A satellite holds the descriptive attributes of a hub or link, with full history. Each change in the source creates a new row.

create table raw_vault.sat_customer_crm (    hk_customer      char(32)     not null,    load_dts         timestamp    not null,    hd_customer_crm  char(32)     not null,  -- hash diff of the payload    record_source    varchar(100) not null,    name             varchar(200),    email            varchar(255),    phone            varchar(50),    tier             varchar(20),    constraint pk_sat_customer_crm primary key (hk_customer, load_dts));​create table raw_vault.sat_order_shop (    hk_order         char(32)     not null,    load_dts         timestamp    not null,    hd_order_shop    char(32)     not null,    record_source    varchar(100) not null,    order_date       date,    status           varchar(30),    channel          varchar(30),    constraint pk_sat_order_shop primary key (hk_order, load_dts));​create table raw_vault.sat_order_line_shop (    hk_order_product  char(32)     not null,    load_dts          timestamp    not null,    hd_order_line     char(32)     not null,    record_source     varchar(100) not null,    qty               integer,    unit_price        numeric(12,2),    constraint pk_sat_order_line_shop primary key (hk_order_product, load_dts));

Satellite design rules worth following:

• Split by source. sat_customer_crm holds what the CRM says. If the order system also carries a customer email, that goes in sat_customer_shop. Each satellite is a faithful record of one source, which is what makes the raw vault auditable.
• Split by rate of change. If tier changes monthly but last_login_at changes hourly, put them in separate satellites so the slow one does not get a new row every hour.
• Split sensitive data. Put PII in its own satellite so it can be secured, masked or deleted independently.
• Satellites on links hold relationship attributes. Quantity and price describe the order line, which is the order-product link, so they live on a link satellite.

The hash diff

The hash diff (hd_*) is a hash of all payload columns. When loading, compare the incoming hash diff with the latest one in the satellite. Insert only if they differ. That is one comparison per row instead of one per column, and it handles nulls consistently because the null placeholders are part of the hashed string.

Step 5: Load patterns

Hubs, links and satellites each have one standard, idempotent load pattern.

Hub load: insert new keys

insert into raw_vault.hub_customer (hk_customer, customer_number, load_dts, record_source)select distinct s.hk_customer, s.customer_number, s.load_dts, s.record_sourcefrom stg.crm_customer swhere not exists (    select 1 from raw_vault.hub_customer h    where h.hk_customer = s.hk_customer);

Run the same statement for every source that carries customer numbers (the order system too). A customer first seen on an order is still registered in the hub.

Link load: insert new relationships

insert into raw_vault.link_order_customer    (hk_order_customer, hk_order, hk_customer, load_dts, record_source)select distinct    md5(concat_ws('||', upper(trim(order_no)), upper(trim(customer_number)))),    md5(upper(trim(order_no))),    md5(upper(trim(customer_number))),    current_timestamp,    'shop.orders'from shop.orders owhere not exists (    select 1 from raw_vault.link_order_customer l    where l.hk_order_customer =          md5(concat_ws('||', upper(trim(o.order_no)), upper(trim(o.customer_number)))));

Satellite load: insert changed payloads

insert into raw_vault.sat_customer_crm    (hk_customer, load_dts, hd_customer_crm, record_source, name, email, phone, tier)select s.hk_customer, s.load_dts, s.hd_customer_crm, s.record_source,       s.name, s.email, s.phone, s.tierfrom stg.crm_customer sleft join (    select hk_customer, hd_customer_crm,           row_number() over (partition by hk_customer order by load_dts desc) as rn    from raw_vault.sat_customer_crm) cur  on cur.hk_customer = s.hk_customer and cur.rn = 1where cur.hk_customer is null                     -- new customer   or cur.hd_customer_crm <> s.hd_customer_crm;   -- changed payload

All three are insert-only. Re-running a load with the same data inserts nothing. That idempotence is one of the main operational benefits of the method.

Querying the vault

The raw vault is not designed for end users. To report on it, you build an information mart on top, often a star schema. A current-state customer view looks like this:

select    h.customer_number,    s.name,    s.email,    s.tierfrom raw_vault.hub_customer hjoin raw_vault.sat_customer_crm s  on s.hk_customer = h.hk_customerqualify row_number() over (partition by s.hk_customer order by s.load_dts desc) = 1;

When several satellites hang off one hub, point-in-time (PIT) tables precompute which satellite row was current for each key at each snapshot date, so marts can join with equality instead of range conditions. Business rules (deduplicating customers across sources, deriving a golden email) live in the business vault, never in the raw vault.

Common mistakes

Using surrogate IDs as business keys. Hashing the CRM's internal id column instead of the customer number means the same customer from another source never integrates. Choose keys the business recognises.

Putting descriptive columns on hubs or links. A hub with a customer_name column breaks the insert-only pattern and mixes structure with content.

Inconsistent hash rules. One pipeline trims and uppercases, another does not. The vault quietly fills with duplicate keys. Centralise hashing in a macro or function, and test that the same key from two sources produces one hub row.

One giant satellite per hub. Combining every source and every attribute in one satellite loses source lineage and creates a new row whenever any column changes. Split by source, change rate and sensitivity.

Applying business rules in the raw vault. Filtering out "test customers" or fixing statuses during load destroys auditability. Load raw, apply rules downstream.

Modeling hubs, links and satellites by hand is a good way to learn. For production work, the conventions are mechanical enough to generate; automating Data Vault 2.0 modeling covers that.

Practise Data Vault in offdataai Learn

The Data Vault 2.0 track in offdataai Learn takes you through hubs, links, satellites, hash keys and hash diffs with hands-on exercises. You model each step in the Model Builder from a source description, and an instant check confirms your business keys, link structure and satellite splits. It is included in the Explorer plan at $1/month.

For an overview of the methodology and generated examples, see the Data Vault page or open a Data Vault model in the templates.