Module 03: Hub Design Deep Dive

Concept 01

Hub Anatomy & Required Columns

🧠 ELI5 — Explain Like I'm Five

A Hub is the simplest table in Data Vault — but that simplicity is intentional. It stores ONLY the unique business identifiers for one type of real-world entity. Nothing else. The discipline of keeping hubs lean is what makes the whole architecture work.

The Four Required Columns

Every Hub in every Data Vault implementation has exactly four mandatory columns. No exceptions. No extras in the Hub itself — everything descriptive goes to a Satellite.

Column	Type	Rule
`HUB_{ENTITY}_HK`	`CHAR(32)`	Primary key. MD5 hash of normalized BK. Never null.
`{ENTITY}_BK`	`VARCHAR`	The actual business key. Indexed. Never null.
`LOAD_DATE`	`TIMESTAMP`	When first seen by the vault. Never updated.
`RECORD_SOURCE`	`VARCHAR(100)`	Which system first saw this entity. Enables lineage.

Naming Conventions

Hub tables: HUB_{ENTITY} — e.g., HUB_CUSTOMER, HUB_PRODUCT, HUB_ORDER
Hash key column: HUB_{ENTITY}_HK
Business key column: {ENTITY}_BK
Load date column: LOAD_DATE or LDTS (pick one, use consistently)
Record source column: RECORD_SOURCE or RSRC (pick one, use consistently)

SQL — HUB_PRODUCT with all conventions applied

-- HUB_PRODUCT: stores unique product business keys only
-- Nothing descriptive here — name, price, category go in Satellites
CREATE TABLE HUB_PRODUCT (
    HUB_PRODUCT_HK   CHAR(32)      NOT NULL,  -- MD5 of UPPER(TRIM(PRODUCT_BK))
    PRODUCT_BK       VARCHAR(50)   NOT NULL,  -- e.g. ProductSKU or GTIN barcode
    LOAD_DATE        TIMESTAMP     NOT NULL,  -- when first seen; never updated
    RECORD_SOURCE    VARCHAR(100)  NOT NULL,  -- e.g. 'ERP.ProductMaster'
    CONSTRAINT pk_hub_product PRIMARY KEY (HUB_PRODUCT_HK)
);

-- Recommended: index on BK for lookup performance
CREATE UNIQUE INDEX uix_hub_product_bk
    ON HUB_PRODUCT (PRODUCT_BK);

🔎

What goes IN a Hub vs what does NOT:

✓ Goes in Hub: Hash Key • Business Key • Load Date • Record Source

✗ Does NOT go in Hub: Product Name • Price • Category • Status • Description — all of these go in Satellites.

Concept 02

Choosing Valid Business Keys

🧠 ELI5

The most critical design decision in all of Data Vault is “what is the business key for this hub?” It must be something the business uses to talk about the entity — not a database auto-increment ID. The test: would a customer service rep type it into a search box? Would it appear on an invoice?

The Business Key Decision Framework

Ask the business analyst, not the DBA: “How do you look up a customer? What do you type in the search box?”

Test 1 — Business process: Is it used in emails, invoices, support tickets, or conversations with the customer?
Test 2 — Meaningful when printed: Would the key be meaningful if it appeared on a report or was sent in an email to a partner?
Test 3 — Uniqueness: Does it uniquely identify exactly one instance of this entity type?

If all three tests pass, you have a valid business key. If any test fails, dig deeper.

Common Business Key Examples by Entity

Entity	Common Business Keys	Notes
Customer	`CustomerID`, `TaxID`, `SSN`, `Email`	Depends on industry. Banking uses TaxID; e-commerce often uses Email.
Product	`ProductSKU`, `GTIN` / `EAN` barcode, `PartNumber`	GTIN is globally unique; SKU is only unique within one brand.
Account	`AccountNumber`, `IBAN`, `PolicyNumber`	IBAN for banking; PolicyNumber for insurance.
Employee	`EmployeeID`, `NationalID`	NationalID is stable; EmployeeID can be reassigned if retirees return.
Order	`OrderNumber`, `InvoiceNumber`	Watch for systems that reuse order numbers across fiscal years.

⚠

What NOT to use as a Business Key:

Database IDENTITY / SEQUENCE columns with no business meaning
Row GUIDs generated by an ORM that business users never see
Composite primary keys that include technical timestamp fields
Any key that can be reused or reassigned by the source system without tracking

The Composite Business Key Pattern

Sometimes no single column uniquely identifies an entity. When a legacy system reuses order numbers across fiscal years, you need both fields to make the key unique. The hash key is derived from the combined value, but both columns are stored in the Hub.

SQL — Composite BK pattern for recycled keys

-- When you need multiple columns to identify one entity
-- Hub for Historical Sales Transactions
-- (legacy system reuses order numbers by year)
CREATE TABLE HUB_SALES_TRANSACTION (
    HUB_SALES_TRANSACTION_HK  CHAR(32)      NOT NULL,
    -- Composite BK: both parts together form the unique identifier
    FISCAL_YEAR               CHAR(4)       NOT NULL,
    TRANSACTION_NUMBER        VARCHAR(20)   NOT NULL,
    LOAD_DATE                 TIMESTAMP     NOT NULL,
    RECORD_SOURCE             VARCHAR(100)  NOT NULL,
    CONSTRAINT pk_hub_st PRIMARY KEY (HUB_SALES_TRANSACTION_HK)
);

-- Hash key computation (done in staging layer):
-- MD5(FISCAL_YEAR || '||' || TRANSACTION_NUMBER)
-- The '||' separator prevents '2024' + '1234' == '202' + '41234'
SELECT
    MD5(
        UPPER(TRIM(FISCAL_YEAR)) || '||' ||
        UPPER(TRIM(TRANSACTION_NUMBER))
    ) AS HUB_SALES_TRANSACTION_HK,
    FISCAL_YEAR,
    TRANSACTION_NUMBER,
    LOAD_DATE,
    RECORD_SOURCE
FROM STG_LEGACY_TRANSACTIONS;

Separator discipline: Always use a fixed multi-character separator (e.g., '||' or '~') between composite key parts during hashing. A single '|' can still collide if key values contain that character. Document your separator standard in your metadata and never change it.

Concept 03

Hub SQL Load Patterns

🧠 ELI5

Loading a Hub is the simplest ETL operation in Data Vault: take the incoming business keys from staging, hash them, and insert only the ones you haven’t seen before. That’s it. One INSERT...SELECT with a NOT EXISTS filter. The Hub never gets updated. You never delete from it. If the BK is already there, you move on.

Pattern 1: INSERT...SELECT with NOT EXISTS (Preferred)

This is the canonical Data Vault Hub load. It is idempotent — running it twice in the same batch produces the same result. Each row in staging either finds its hash key already in the Hub (and skips) or doesn’t (and inserts).

SQL — Canonical Hub load from CRM staging

-- ================================================================
-- LOAD: HUB_CUSTOMER from CRM staging
-- Pattern: INSERT new BKs only (idempotent)
-- ================================================================
INSERT INTO HUB_CUSTOMER (
    HUB_CUSTOMER_HK,
    CUSTOMER_BK,
    LOAD_DATE,
    RECORD_SOURCE
)
SELECT
    stg.HUB_CUSTOMER_HK,    -- pre-computed in staging layer
    stg.CUSTOMER_BK,
    stg.LOAD_DATE,
    stg.RECORD_SOURCE
FROM STG_CRM_CUSTOMER stg
WHERE NOT EXISTS (
    -- Only insert if this hash key has never been seen before
    SELECT 1
    FROM HUB_CUSTOMER hub
    WHERE hub.HUB_CUSTOMER_HK = stg.HUB_CUSTOMER_HK
);

💡

Why pre-compute hash keys in staging? Computing MD5 inside the Hub load SQL works, but if the same BK arrives from multiple staging tables in the same batch, you’ll compute the hash multiple times. Pre-computing in staging means the hash is computed exactly once, stored in a STG_* column, and reused everywhere. It also makes debugging easier — you can inspect STG_CRM_CUSTOMER.HUB_CUSTOMER_HK directly.

Pattern 2: MERGE (Alternative — Use with Caution)

MERGE is syntactically cleaner but harder to debug when things go wrong. More critically, a MERGE with WHEN MATCHED THEN UPDATE would violate Data Vault’s insert-only rules. Only the WHEN NOT MATCHED THEN INSERT branch is acceptable.

SQL — MERGE alternative (acceptable, with caveats)

-- Alternative: MERGE (use with caution — harder to debug)
MERGE INTO HUB_CUSTOMER AS target
USING STG_CRM_CUSTOMER AS source
    ON target.HUB_CUSTOMER_HK = source.HUB_CUSTOMER_HK
WHEN NOT MATCHED THEN
    INSERT (HUB_CUSTOMER_HK, CUSTOMER_BK, LOAD_DATE, RECORD_SOURCE)
    VALUES (source.HUB_CUSTOMER_HK, source.CUSTOMER_BK,
            source.LOAD_DATE, source.RECORD_SOURCE);

-- CRITICAL: Never add WHEN MATCHED THEN UPDATE to a Hub load.
-- Updating Hub rows violates DV insert-only rules and destroys
-- your audit trail. Only WHEN NOT MATCHED INSERT is acceptable.

INSERT...SELECT preferred for auditability: With NOT EXISTS, the rejected rows are visible in staging — you can count them, log them, and report them. MERGE hides the rejection logic inside the engine. On large tables, NOT EXISTS with a hash key index is typically equally fast, and the transparency is worth it.

Concept 04

Multi-Source Hub Loading

🧠 ELI5

Real enterprises have multiple systems that all know about “customers” — CRM, ERP, loyalty platform, website, and more. The Hub can receive customer records from all of them. Each source loads into the same Hub table with its own RECORD_SOURCE tag. The earliest arrival for any given business key “wins” the Hub row, but all source records still load successfully via the NOT EXISTS check.

The Staging Union Pattern

Instead of running separate load jobs for each source, you can UNION ALL the staging tables and load everything in a single pass. This is more efficient and ensures all sources compete on a level playing field for the “first arrival” honour.

SQL — Load HUB_CUSTOMER from 3 source systems in one pass

-- Load HUB_CUSTOMER from 3 source systems in one pass
INSERT INTO HUB_CUSTOMER (HUB_CUSTOMER_HK, CUSTOMER_BK, LOAD_DATE, RECORD_SOURCE)
SELECT HUB_CUSTOMER_HK, CUSTOMER_BK, LOAD_DATE, RECORD_SOURCE
FROM (
    -- Source 1: CRM (Salesforce)
    SELECT HUB_CUSTOMER_HK, CUSTOMER_BK, LOAD_DATE, RECORD_SOURCE
    FROM STG_CRM_CUSTOMER

    UNION ALL

    -- Source 2: ERP (SAP)
    SELECT HUB_CUSTOMER_HK, CUSTOMER_BK, LOAD_DATE, RECORD_SOURCE
    FROM STG_ERP_ACCOUNT

    UNION ALL

    -- Source 3: E-Commerce Platform
    SELECT HUB_CUSTOMER_HK, CUSTOMER_BK, LOAD_DATE, RECORD_SOURCE
    FROM STG_WEB_USER
) all_sources
WHERE NOT EXISTS (
    SELECT 1 FROM HUB_CUSTOMER hub
    WHERE hub.HUB_CUSTOMER_HK = all_sources.HUB_CUSTOMER_HK
);

⚡

Key insight — First arrival wins, but nothing is lost: The Hub doesn’t care which source provides the first record. Whoever arrives first “claims” the Hub row, setting LOAD_DATE and RECORD_SOURCE to that batch. All subsequent sources still load their own Satellites successfully — SAT_CUSTOMER_FROM_CRM, SAT_CUSTOMER_FROM_ERP, SAT_CUSTOMER_FROM_WEB — all pointing to the same Hub row via the same hash key. This is how Data Vault integrates heterogeneous source systems without master data management overhead. The Hub provides shared identity; the Satellites preserve source-specific context.

SVG Diagram: Multi-Source Hub Integration

Concept 05

Hub Anti-Patterns & Interview Q&A

🧠 ELI5

Even experienced engineers make Hub mistakes. The most common: putting descriptive data in the Hub (it belongs in a Satellite), using technical IDs instead of business keys, and failing to normalize keys consistently before hashing. Know these cold — they come up in every senior Data Vault interview.

Best Practices vs Common Mistakes

✓ Best Practices

One Hub per distinct business entity type
Store only BK + four metadata columns in the Hub
Pre-compute hash keys in the staging layer, not at Hub load time
Apply TRIM/UPPER normalization consistently before hashing — everywhere, always
Filter NULL business keys in staging before they reach the Hub
Index the business key column for lookup performance

✗ Common Mistakes

Storing descriptive columns (name, address, status) directly in the Hub
Using “smart” keys that encode data (e.g., “NY-2024-00143” encoding state + year + seq)
Using composite keys in the Hub PK instead of hashing them into a single HK
Updating Hub rows when a source system “changes” the business key
Inconsistent normalization (some loads TRIM, some don’t) — causes phantom duplicate Hub rows
Allowing NULL business keys to reach the Hub

Hub Design — Interview Questions

What happens when a Hub receives the same business key from two different source systems? ▶

Both source systems load into the same Hub table. The first to arrive creates the Hub row — setting LOAD_DATE to the first batch timestamp and RECORD_SOURCE to the first system. The second system’s load hits the NOT EXISTS filter and skips the insert.

However, both systems will successfully load their own Satellite data (SAT_CUSTOMER_FROM_CRM, SAT_CUSTOMER_FROM_ERP) pointing to the same Hub row via the same hash key. This is exactly how Data Vault achieves multi-source integration — shared Hub identity, source-specific satellite context. The Hub’s RECORD_SOURCE tells you who first introduced this entity to the vault, not who “owns” it.

Why shouldn’t you put descriptive columns in a Hub? ▶

Hubs must be insert-only, but descriptive data changes over time. If you put a customer’s name in the Hub, you’d need to UPDATE the Hub when the name changes — violating the insert-only principle. Worse, you’d lose the history of what the name was before the update.

Satellites exist precisely to hold this changing context while preserving full history. Every time a name changes, a new Satellite row is inserted with a new LOAD_DATE, and the old row remains permanently. A Hub with descriptive data is a sign the designer is conflating the Hub pattern with a traditional dimension table.

The practical consequence: once you put a descriptive column in a Hub and data starts loading, you have no clean way to remove it without rebuilding the Hub and replaying all loads.

What is the “smart key” anti-pattern and why should you avoid it? ▶

A smart key (also called an intelligent key) is a business key that encodes information in its structure — e.g., NY-2024-00143 where NY = state, 2024 = year, 00143 = sequence number.

The anti-pattern: if the business ever restructures the key format (removes the state prefix, changes year format, zero-pads differently), your Hub now contains two different BK formats for the same entity type. Hash keys will differ even for logically equivalent entities. You end up with phantom duplicates and broken lineage.

The fix is to parse the meaning out of smart keys during staging and store the components either as separate Hub attributes in a Satellite, or extract them as distinct Hubs if they represent distinct entities — not embedded in the BK itself. The BK stored in the Hub should be the raw, uninterpreted value from the source.

How do you handle a business key that can be reused by the source system? ▶

This is the “key recycling” problem — a source system reuses OrderNumber 12345 for a completely different order after some period. There are three valid solutions:

1. Composite BK with discriminating attribute: Add ORDER_DATE or FISCAL_YEAR to the BK so each instance is unique: MD5('2023' || '||' || '12345') vs MD5('2024' || '||' || '12345'). Best when the recycled key truly represents a different entity.

2. Add SOURCE_SYSTEM to the BK: If different source systems use the same key space but mean different things, namespace the BK with the source: 'CRM' || '||' || CUSTOMER_ID. This prevents cross-system collisions.

3. Same-As Link (SAL): If the recycled key might represent the same real-world entity (e.g., a returning customer getting a new ID), use a SAL to track the succession relationship and let the Business Vault resolve the canonical identity. The right choice depends on whether recycled keys represent the same entity (uncommon — use SAL) or a different entity with the same ID (common — use composite BK).

✅ Module 03 Key Takeaways

A Hub has exactly four columns: Hash Key (PK), Business Key, Load Date, and Record Source. No descriptive data — ever. Descriptive attributes belong in Satellites.
The business key must be something the business actually uses to identify the entity — not a database surrogate, ORM GUID, or auto-increment. Ask the analyst, not the DBA.
Hub load SQL is always insert-only: INSERT...SELECT with NOT EXISTS. Pre-compute hash keys in staging. Apply TRIM/UPPER normalization consistently or you’ll create phantom duplicate rows.
Multiple source systems load into the same Hub. The first arrival claims the Hub row; all sources still load their own Satellites successfully. This is how Data Vault achieves multi-source integration without MDM overhead.
The three most dangerous Hub anti-patterns are: storing descriptive data in the Hub (violates insert-only), using smart/intelligent keys that encode meaning (breaks when key format changes), and inconsistent normalization before hashing (creates duplicates that are nearly impossible to clean up).