Part 182: Data Vault 2.0 Modeling: Hubs, Links & Satellites in SQL

Building agile, scalable, and fully auditable enterprise data warehouses.

1. The Core Data Vault 2.0 Pillars

Data Vault 2.0 decomposes data into three distinct table types:

1. Hubs (Core Business Concepts): Contains only unique Business Keys (e.g. CustomerNumber, VIN, ISBN) hashed with SHA-256. Never changes.
2. Links (Relationships & Transactions): Connects two or more Hubs (e.g. Customer purchased Product).
3. Satellites (Context & Descriptive State): Contains descriptive attributes, load timestamps, and hash diffs. Every update is an append!

2. SQL Schema Blueprint

-- 1. HUB: Unique Business Key
CREATE TABLE hub_customer (
    h_customer_hashkey CHAR(64) PRIMARY KEY, -- SHA-256 Hash of customer_id
    customer_id VARCHAR(50) NOT NULL UNIQUE,
    load_timestamp TIMESTAMPTZ NOT NULL,
    record_source VARCHAR(50) NOT NULL
);

-- 2. LINK: Relationship between Customer and Account
CREATE TABLE link_customer_account (
    l_cust_acct_hashkey CHAR(64) PRIMARY KEY,
    h_customer_hashkey CHAR(64) REFERENCES hub_customer(h_customer_hashkey),
    h_account_hashkey CHAR(64) REFERENCES hub_account(h_account_hashkey),
    load_timestamp TIMESTAMPTZ NOT NULL,
    record_source VARCHAR(50) NOT NULL
);

-- 3. SATELLITE: Temporal descriptive attributes
CREATE TABLE sat_customer (
    h_customer_hashkey CHAR(64) REFERENCES hub_customer(h_customer_hashkey),
    load_timestamp TIMESTAMPTZ NOT NULL,
    hash_diff CHAR(64) NOT NULL, -- SHA-256 of all attributes (detects changes)
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    record_source VARCHAR(50) NOT NULL,
    PRIMARY KEY (h_customer_hashkey, load_timestamp)
);

Key Takeaways

• Satellites are append-only; historical changes are never overwritten, satisfying 100% auditing compliance.

In Part 183, we explore Database Multi-Tenancy Architecture: Database vs Schema vs Shared!