Module 04: Link Design Deep Dive

Concept 01

Link Anatomy & Grain

🧠 ELI5 — Explain Like I'm Five

A Link is a table that says “these two (or more) things are related.” Where a Hub says “this customer EXISTS,” a Link says “this customer PLACED this order.” Links have no descriptive data — they just record the fact that the relationship happened.

Required Columns

Every Link table in a Data Vault must contain exactly these columns — no more in the Raw Vault, no less ever:

Column	Type	Rule
`LINK_{NAME}_HK`	`CHAR(32)`	Primary Key. Hash of all participating Business Keys concatenated in a fixed order.
`HUB_{A}_HK`	`CHAR(32)`	Foreign key to the first participating Hub. NOT NULL.
`HUB_{B}_HK`	`CHAR(32)`	Foreign key to the second participating Hub. NOT NULL. (Repeat for each additional Hub.)
`LOAD_DATE`	`TIMESTAMP`	When this relationship was first seen. Set in staging; never updated.
`RECORD_SOURCE`	`VARCHAR(100)`	Which source system reported this relationship.

💡

The grain of a Link is the combination of all participating business keys. One unique combination = one row. A Link for “Customer placed Order” has one row per unique (CustomerID, OrderID) pair — not one row per order line item. Getting the grain wrong is the most common Link design mistake.

Two-Way Link: Customer → Order

The classic two-hub link. The hash key is derived from both business keys concatenated in a deterministic order.

SQL — LINK_CUSTOMER_ORDER (two-way)

-- LINK_CUSTOMER_ORDER: records the fact that a customer placed an order
CREATE TABLE LINK_CUSTOMER_ORDER (
    LINK_CUSTOMER_ORDER_HK  CHAR(32)      NOT NULL,  -- MD5(CUSTOMER_BK || '||' || ORDER_BK)
    HUB_CUSTOMER_HK         CHAR(32)      NOT NULL,  -- FK to HUB_CUSTOMER
    HUB_ORDER_HK            CHAR(32)      NOT NULL,  -- FK to HUB_ORDER
    LOAD_DATE               TIMESTAMP     NOT NULL,
    RECORD_SOURCE           VARCHAR(100)  NOT NULL,
    CONSTRAINT pk_lnk_co PRIMARY KEY (LINK_CUSTOMER_ORDER_HK)
);

Three-Way Link: Order → Product → Warehouse

A three-hub link works identically — just add the third FK and include the third BK in the hash. The structural pattern never changes regardless of how many hubs participate.

SQL — LINK_ORDER_PRODUCT (three-way link)

-- LINK_ORDER_PRODUCT: order fulfilled from a specific warehouse location
-- Grain: one row per unique (OrderID, ProductID, WarehouseID) combination
CREATE TABLE LINK_ORDER_PRODUCT (
    LINK_ORDER_PRODUCT_HK   CHAR(32)      NOT NULL,  -- MD5(ORDER_BK || '||' || PRODUCT_BK || '||' || WAREHOUSE_BK)
    HUB_ORDER_HK            CHAR(32)      NOT NULL,  -- FK to HUB_ORDER
    HUB_PRODUCT_HK          CHAR(32)      NOT NULL,  -- FK to HUB_PRODUCT
    HUB_WAREHOUSE_HK        CHAR(32)      NOT NULL,  -- FK to HUB_WAREHOUSE
    LOAD_DATE               TIMESTAMP     NOT NULL,
    RECORD_SOURCE           VARCHAR(100)  NOT NULL,
    CONSTRAINT pk_lnk_op PRIMARY KEY (LINK_ORDER_PRODUCT_HK)
);

Concept 02

Many-to-Many Relationships

🧠 ELI5

Links naturally express many-to-many relationships, which are the bane of traditional dimensional modeling (you'd need a bridge table). In Data Vault, many-to-many is the DEFAULT. A customer can have many orders. An order can have many products. These are just Links with multiple Hub foreign keys — no special workaround required.

Common Many-to-Many Link Patterns

Each example below captures a different business relationship. The structural pattern is identical — only the participating hubs change:

Link Name	Participating Hubs	Business Meaning	Grain
`LINK_CUSTOMER_ORDER`	`HUB_CUSTOMER`, `HUB_ORDER`	A customer placed an order	One row per unique (Customer, Order) pair
`LINK_ORDER_PRODUCT`	`HUB_ORDER`, `HUB_PRODUCT`	An order contained a product	One row per unique (Order, Product) pair
`LINK_EMPLOYEE_DEPARTMENT_ROLE`	`HUB_EMPLOYEE`, `HUB_DEPARTMENT`, `HUB_ROLE`	Employee holds a role in a department (3-way)	One row per unique (Employee, Department, Role) combination

Full Load Pattern for LINK_CUSTOMER_ORDER

The load is always idempotent: the NOT EXISTS guard means running the same load twice produces the same result. Hub hash keys are pre-computed in the staging layer before this query runs.

SQL — Load LINK_CUSTOMER_ORDER from staging

INSERT INTO LINK_CUSTOMER_ORDER (
    LINK_CUSTOMER_ORDER_HK,
    HUB_CUSTOMER_HK,
    HUB_ORDER_HK,
    LOAD_DATE,
    RECORD_SOURCE
)
SELECT
    MD5(
        COALESCE(UPPER(TRIM(stg.CUSTOMER_BK)), '-1') || '||' ||
        COALESCE(UPPER(TRIM(stg.ORDER_BK)),    '-1')
    )                           AS LINK_CUSTOMER_ORDER_HK,
    stg.HUB_CUSTOMER_HK,        -- pre-computed in staging
    stg.HUB_ORDER_HK,           -- pre-computed in staging
    stg.LOAD_DATE,
    stg.RECORD_SOURCE
FROM STG_ORDERS stg
WHERE NOT EXISTS (
    SELECT 1 FROM LINK_CUSTOMER_ORDER lnk
    WHERE lnk.LINK_CUSTOMER_ORDER_HK = MD5(
        COALESCE(UPPER(TRIM(stg.CUSTOMER_BK)), '-1') || '||' ||
        COALESCE(UPPER(TRIM(stg.ORDER_BK)),    '-1')
    )
);

Hash key concatenation order must be fixed and documented. Always define the canonical key order (e.g., alphabetical by BK name) in your data model registry. Changing the order generates different hash values and breaks your link. Use '||' (double pipe) as the separator to avoid false collisions.

Why Many-to-Many is the Default in Data Vault

In Kimball dimensional modeling, a many-to-many fact relationship requires a bridge table — an extra object that must be designed, built, and maintained separately from the dimension model. In Data Vault, every Link is structurally many-to-many by default. There is no bridge concept needed at the Raw Vault layer:

Multiple customers can reference the same order (shared billing scenarios) without any schema change.
An order can appear in multiple links (LINK_CUSTOMER_ORDER and LINK_ORDER_PRODUCT) independently.
Adding a new source that sends additional (Customer, Order) pairs simply inserts new rows — it never restructures the table.

Concept 03

Hierarchical Links

🧠 ELI5

Sometimes a relationship is between two instances of the same entity type. An employee manages another employee. A product is a component of another product. A region is a subdivision of another region. This is a “hierarchical link” or “self-referencing link” — it points back to the same Hub twice.

The Self-Referencing Pattern

A hierarchical link has two foreign keys that both point to the same Hub. Because SQL column names must be unique within a table, you must use role-qualified column names to distinguish the two FKs. The role prefix describes the business role each key plays in the relationship.

SQL — LINK_EMPLOYEE_HIERARCHY (self-referencing)

-- LINK_EMPLOYEE_HIERARCHY: records that one employee manages another
-- Both FKs point to HUB_EMPLOYEE, distinguished by role prefix
CREATE TABLE LINK_EMPLOYEE_HIERARCHY (
    LINK_EMPLOYEE_HIERARCHY_HK  CHAR(32)      NOT NULL,  -- MD5(MANAGER_BK || '||' || EMPLOYEE_BK)
    MANAGER_HUB_EMPLOYEE_HK     CHAR(32)      NOT NULL,  -- FK to HUB_EMPLOYEE (the manager)
    EMPLOYEE_HUB_EMPLOYEE_HK    CHAR(32)      NOT NULL,  -- FK to HUB_EMPLOYEE (the subordinate)
    LOAD_DATE                   TIMESTAMP     NOT NULL,
    RECORD_SOURCE               VARCHAR(100)  NOT NULL,
    CONSTRAINT pk_lnk_eh PRIMARY KEY (LINK_EMPLOYEE_HIERARCHY_HK)
);

-- Load example: Jane (E001) manages Bob (E002)
INSERT INTO LINK_EMPLOYEE_HIERARCHY
SELECT
    MD5(
        COALESCE(UPPER(TRIM(stg.MANAGER_EMP_ID)),  '-1') || '||' ||
        COALESCE(UPPER(TRIM(stg.EMPLOYEE_EMP_ID)), '-1')
    )                             AS LINK_EMPLOYEE_HIERARCHY_HK,
    stg.MANAGER_HUB_EMPLOYEE_HK,  -- pre-computed in staging
    stg.EMPLOYEE_HUB_EMPLOYEE_HK, -- pre-computed in staging
    stg.LOAD_DATE,
    stg.RECORD_SOURCE
FROM STG_ORG_CHART stg
WHERE NOT EXISTS (
    SELECT 1 FROM LINK_EMPLOYEE_HIERARCHY lnk
    WHERE lnk.LINK_EMPLOYEE_HIERARCHY_HK = MD5(
        COALESCE(UPPER(TRIM(stg.MANAGER_EMP_ID)),  '-1') || '||' ||
        COALESCE(UPPER(TRIM(stg.EMPLOYEE_EMP_ID)), '-1')
    )
);

💡

Role-qualified naming is mandatory. Use a meaningful business role prefix (e.g., MANAGER_, EMPLOYEE_) before the hub FK column name. Never use generic prefixes like A_ or 1_ — the prefix must describe what business role that key plays in the relationship. This naming convention is enforced during audit and review.

Non-Historized Links

🧠 ELI5

Most links record relationships that can change over time (a customer can switch from one account manager to another). But some links record events that are permanently true once they happen: a purchase transaction, a financial trade, a medical visit. These “transaction links” or “non-historized links” use a special pattern that avoids the overhead of tracking history that will never change.

Regular Links vs Non-Historized Links

Characteristic	Regular Link	Non-Historized (Transaction) Link
What it captures	A relationship that may open and close over time	A point-in-time event that is permanently true
Effectivity Satellite	Yes — tracks open/close window	Typically not needed — event is done
Degenerate Dimensions	No — attributes go in satellites	Yes — amounts, dates may live in the link
Load volume	Moderate — relationship changes are infrequent	High — often loaded from event streams
Examples	Employee→Department, Customer→AccountManager	Financial trade, ATM withdrawal, clickstream event

Transaction Link Design

A non-historized link may include Degenerate Dimension (DD) columns directly in the link table. These are small, stable, transactional attributes that describe the event itself but do not warrant their own satellite because they will never change after the event occurs.

SQL — LINK_FINANCIAL_TRANSACTION (non-historized with DDs)

-- LINK_FINANCIAL_TRANSACTION: a non-historized (transaction) link
-- Records individual financial events that are permanently true once executed
CREATE TABLE LINK_FINANCIAL_TRANSACTION (
    LINK_FINANCIAL_TRANSACTION_HK  CHAR(32)       NOT NULL,
    HUB_ACCOUNT_HK                 CHAR(32)       NOT NULL,  -- FK to HUB_ACCOUNT
    HUB_PRODUCT_HK                 CHAR(32)       NOT NULL,  -- FK to HUB_PRODUCT
    HUB_CURRENCY_HK                CHAR(32)       NOT NULL,  -- FK to HUB_CURRENCY
    -- Degenerate Dimensions: event data that belongs in the link itself
    TRANSACTION_DATE               DATE           NOT NULL,  -- DD: when did this happen?
    TRANSACTION_AMOUNT             DECIMAL(18,4)  NOT NULL,  -- DD: how much?
    LOAD_DATE                      TIMESTAMP      NOT NULL,
    RECORD_SOURCE                  VARCHAR(100)   NOT NULL,
    CONSTRAINT pk_lnk_ft PRIMARY KEY (LINK_FINANCIAL_TRANSACTION_HK)
);

💡

Degenerate Dimensions (DDs) — small, stable transactional attributes that don’t warrant their own satellite — can live in the link itself. Examples: transaction amounts, event dates, quantities, confirmation numbers. The rule of thumb: if the attribute might ever change, use a satellite instead. If it is permanently frozen at event time, a DD in the link is acceptable and efficient.

⚠

Do not confuse LOAD_DATE with TRANSACTION_DATE. LOAD_DATE is always when the data arrived in the vault (system time). TRANSACTION_DATE is the business event date (when the trade was executed). These are separate and both matter for different query types — operational queries use business date; lineage audit uses load date.

Concept 05

Same-As Links (SAL)

🧠 ELI5

You have a customer in your CRM with ID CRM-1001 and the same customer in your ERP with ID ERP-XYZ-99. They’re the same person but the source systems don’t know that. A Same-As Link (SAL) is a special link that says “these two business keys from different sources refer to the same real-world entity.” It’s the foundation of Master Data Management in Data Vault.

SAL Structure: Driver and Dependent

A Same-As Link always involves exactly two hub hash keys that both point to the same Hub. The Driver key is the “golden record” or canonical identity — the one the business has decided to trust. The Dependent key is the matched alternative identity that resolves to the driver. Both original keys are preserved in the Hub with their full history — the SAL only declares equivalence.

SQL — SAL_CUSTOMER (Same-As Link)

-- SAL_CUSTOMER: master data identity matching for HUB_CUSTOMER
-- Records that two different business keys refer to the same real-world customer
CREATE TABLE SAL_CUSTOMER (
    SAL_CUSTOMER_HK           CHAR(32)      NOT NULL,
    DRIVER_HUB_CUSTOMER_HK    CHAR(32)      NOT NULL,  -- "golden" / canonical key
    DEPENDENT_HUB_CUSTOMER_HK CHAR(32)      NOT NULL,  -- matched duplicate
    LOAD_DATE                  TIMESTAMP     NOT NULL,
    RECORD_SOURCE              VARCHAR(100)  NOT NULL,
    CONSTRAINT pk_sal_cust PRIMARY KEY (SAL_CUSTOMER_HK)
);

-- Associated satellite: records the matching confidence and method
CREATE TABLE SAT_SAL_CUSTOMER (
    SAL_CUSTOMER_HK  CHAR(32)      NOT NULL,
    LOAD_DATE        TIMESTAMP     NOT NULL,
    HASH_DIFF        CHAR(32)      NOT NULL,
    RECORD_SOURCE    VARCHAR(100)  NOT NULL,
    MATCH_CONFIDENCE DECIMAL(5,2),             -- 0.00 to 100.00
    MATCH_METHOD     VARCHAR(100),             -- 'EXACT_EMAIL', 'FUZZY_NAME_ADDR', etc.
    MATCH_DATE       DATE,
    IS_ACTIVE        CHAR(1)       DEFAULT 'Y',
    CONSTRAINT pk_sat_sal_cust PRIMARY KEY (SAL_CUSTOMER_HK, LOAD_DATE)
);

Driver vs Dependent — The Design Rules

The DRIVER key is the system-of-record or highest-trust source for this entity type. It becomes the canonical key used in Business Vault golden-record views.
The DEPENDENT key is an alternative identity that has been matched to the driver. It can come from any other source system.
The matching confidence and method (exact email match, fuzzy name+address match, ML-model match) are stored in SAT_SAL_CUSTOMER — not in the SAL itself.
The Raw Vault never consolidates the two keys. Both CRM-1001 and ERP-XYZ-99 remain in HUB_CUSTOMER with full independent history. The Business Vault uses the SAL to produce unified views.

When to Use Same-As Links

Master Data Management (MDM) — Matching a customer in CRM to the same customer in ERP.
Post-acquisition consolidation — A company is acquired; their customer IDs must be reconciled with existing IDs.
Duplicate detection — Analytics finds two customer records that represent the same person (same email, same address). A SAL declares the equivalence without deleting either record.
System migration — A legacy system is replaced; old IDs and new IDs coexist during transition and must be linked.

Concept 06

Interview Q&A — Links

Intermediate / Senior Questions

What is the grain of a Link and why does it matter? ▶

The grain of a Link is the unique combination of all participating Hub hash keys. One row = one unique combination of those keys. This matters for two critical reasons:

1. Idempotency: Because the grain is hashed and enforced via a NOT EXISTS filter (or MERGE), loading the same relationship from multiple source systems is idempotent — the same hash key is generated regardless of which system sent it, so duplicate rows are never inserted.

2. Scope of the relationship: The grain determines what the link can represent. A Link for “Customer placed Order” records one row per unique (CustomerID, OrderID) pair. If you need one row per order line item, you need a different link with a different grain — perhaps including a LineItemID in the hash. Mixing grains in one link is a fatal design error.

Can a Link contain more than two Hubs? Give an example. ▶

Yes — there is no upper limit. Three-way and four-way links are common in enterprise implementations. The structure is identical to a two-way link: just add additional Hub FK columns and include each additional BK in the concatenated hash.

Example — LINK_TRADE_EXECUTION in financial services:

This link might reference four hubs: HUB_TRADER (who traded), HUB_INSTRUMENT (what was traded), HUB_COUNTERPARTY (with whom), and HUB_VENUE (where it was traded). The link hash key is computed as:

MD5(TRADER_BK || '||' || INSTRUMENT_BK || '||' || COUNTERPARTY_BK || '||' || VENUE_BK)

The grain: one row per unique (Trader, Instrument, Counterparty, Venue) combination. Each of the four Hub FKs is populated with a pre-computed hash from staging. The load pattern is identical to any two-way link.

What is an Effectivity Satellite and how does it relate to a Link? ▶

The split of concern: A Link records that a relationship ever existed. An Effectivity Satellite (EFF SAT) on that link records when the relationship was active.

The EFF SAT has two key business dates: an open date (when the relationship started) and a close date (when it ended — NULL means still active). These are business dates, not load dates.

Example: LINK_EMPLOYEE_DEPARTMENT records that employee E1 ever worked in department D1. SAT_EFF_EMPLOYEE_DEPARTMENT records that this relationship was active from 2020-01-15 to 2022-06-30 (when they transferred).

Without the EFF SAT, you can only answer: “Did they ever work there?” With it, you can answer: “Were they working there on a specific date?” — which is what most business queries actually need.

How does a Non-Historized Link differ from a regular link? ▶

Regular links record relationships that can change over time (a customer can switch account managers). They typically receive an Effectivity Satellite to track when the relationship was active and when it closed.

Non-historized transaction links record transactional events that, once they happen, are permanently true (a customer made a purchase, a transaction was executed). Key differences:

1. No Effectivity Satellite: The transaction happened, it is done. There is no “open/close window” to track.

2. Degenerate Dimensions in the link: Transaction-specific attributes like TRANSACTION_AMOUNT, TRANSACTION_DATE, or QUANTITY may be stored directly in the link itself because they will never change after the event. Regular links would place changing attributes in a satellite instead.

3. High volume loading: Transaction links are often loaded from high-volume event streams. Their design optimizes for insert throughput over query flexibility.

What is a Same-As Link and when would you use one? ▶

A Same-As Link (SAL) records that two different Hub business keys refer to the same real-world entity. Unlike a regular link between two different entities, a SAL points back to the same Hub twice — just like a hierarchical link, but with a semantically distinct purpose: declaring identity equivalence.

Use cases:

1. Master Data Management: Matching a customer in CRM (CRM-1001) to the same customer in ERP (ERP-XYZ-99). The SAL states: “these two business keys are the same person.”

2. Post-acquisition consolidation: A company is acquired and their customer IDs need to be reconciled with your existing IDs. The SAL bridges old and new without deleting either.

3. Duplicate detection: Analytics discovers that two customer records are the same person. A SAL declares the equivalence. The Business Vault then uses the SAL to build a unified “golden record” view while the Raw Vault preserves both original keys with full, independent history.

The matching confidence and method are stored in a dedicated satellite on the SAL (SAT_SAL_{ENTITY}) — not in the SAL table itself, which remains a structural metadata object.

✅ Module 04 Key Takeaways

A Link records that a relationship exists between two or more business entities. It contains no descriptive data — only hub FKs, a composite hash key, load date, and record source.
The grain of a Link is the unique combination of all participating business keys. One wrong grain decision poisons the entire downstream query layer — always define grain before writing DDL.
Hierarchical (self-referencing) links point back to the same Hub twice. Use role-qualified column prefixes (e.g., MANAGER_, EMPLOYEE_) to make both FKs unambiguous and auditable.
Non-historized (transaction) links capture permanently-true events. They may include Degenerate Dimensions (amounts, dates) directly in the link and typically do not receive Effectivity Satellites.
Same-As Links (SALs) are the foundation of MDM in Data Vault. They declare identity equivalence between two business keys without destroying either key’s independent history in the Raw Vault.

Link Anatomy & Grain

Required Columns

Two-Way Link: Customer → Order

Three-Way Link: Order → Product → Warehouse

Many-to-Many Relationships

Common Many-to-Many Link Patterns

Full Load Pattern for LINK_CUSTOMER_ORDER

Why Many-to-Many is the Default in Data Vault

Hierarchical Links

The Self-Referencing Pattern

Other Common Hierarchical Link Patterns

Non-Historized Links

Regular Links vs Non-Historized Links

Transaction Link Design

Same-As Links (SAL)

SAL Structure: Driver and Dependent

Driver vs Dependent — The Design Rules

When to Use Same-As Links

Interview Q&A — Links

✅ Module 04 Key Takeaways