Module 05: Satellite Design Deep Dive

Concept 01

Satellite Anatomy & Structure

🧠 ELI5 — Explain Like I’m Five

A Satellite is attached to either a Hub or a Link. It stores all the descriptive attributes — names, addresses, amounts, statuses — that describe that entity or relationship. Because it’s insert-only and every row has a timestamp, you automatically get complete historical tracking of every attribute change — for free. If a customer changes their email address on March 5th, a new satellite row is inserted with the new email. The old row stays forever. No data is ever lost.

📚 Analogy

Think of a satellite as a versioned snapshot log. Every time something about an entity changes, the satellite appends a new entry stamped with the exact moment that version was recorded. It’s like Git commits for your business data — every state of every attribute is preserved, and you can always “checkout” any point in time by filtering on LOAD_DATE.

Required Columns

Every satellite — regardless of how many payload columns it has — must include these mandatory metadata columns. They are the backbone of DV2’s auditability and change-detection patterns.

Column	Type	Rule
`HUB_{ENTITY}_HK` or `LINK_{NAME}_HK`	`CHAR(32)`	Foreign key to the parent Hub or Link. Part of the composite primary key.
`LOAD_DATE`	`TIMESTAMP`	When this version was loaded into the vault. Part of the composite PK. Never the business event date — always the technical load timestamp.
`LOAD_END_DATE`	`TIMESTAMP NULL`	When the next version arrived, closing this one. `NULL` = still the current active record. Optional — can be derived dynamically via PIT tables instead.
`HASH_DIFF`	`CHAR(32)`	MD5 or SHA-256 hash of all payload columns concatenated. Used for efficient change detection: matching HashDiff means no insert needed.
`RECORD_SOURCE`	`VARCHAR(100)`	The source system that provided this row (e.g., `CRM_SALESFORCE`, `ERP_SAP`). Enables lineage and conflict resolution between sources.
`[payload columns]`	Various	The actual descriptive data. All payload columns are concatenated and hashed to produce HASH_DIFF.

Naming Conventions

Hub satellite: SAT_{ENTITY}_{CONTEXT} — e.g., SAT_CUSTOMER_DEMOGRAPHICS, SAT_CUSTOMER_CONTACT_INFO
Link satellite: SAT_L_{LINK_NAME}_{CONTEXT} — e.g., SAT_L_CUSTOMER_ORDER_DETAILS
Effectivity satellite: SAT_EFF_{LINK_NAME} — e.g., SAT_EFF_EMPLOYEE_DEPARTMENT

The {CONTEXT} suffix differentiates satellites by source system, rate of change, or business domain. Without it, you end up with a single monolithic satellite per entity — the most common DV anti-pattern.

SQL — CREATE TABLE SAT_CUSTOMER_DEMOGRAPHICS

-- SAT_CUSTOMER_DEMOGRAPHICS: demographic attributes only
-- Split from SAT_CUSTOMER_CONTACT and SAT_CUSTOMER_FINANCIALS by classification
CREATE TABLE SAT_CUSTOMER_DEMOGRAPHICS (
    HUB_CUSTOMER_HK  CHAR(32)      NOT NULL,  -- FK to HUB_CUSTOMER; part of PK
    LOAD_DATE        TIMESTAMP     NOT NULL,  -- when this version was loaded
    LOAD_END_DATE    TIMESTAMP     NULL,      -- NULL = current active record
    HASH_DIFF        CHAR(32)      NOT NULL,  -- hash of all payload columns
    RECORD_SOURCE    VARCHAR(100)  NOT NULL,  -- e.g. 'CRM_SALESFORCE'
    -- Payload: demographic attributes only
    FIRST_NAME       VARCHAR(100),
    LAST_NAME        VARCHAR(100),
    DATE_OF_BIRTH    DATE,
    GENDER_CODE      CHAR(1),
    NATIONALITY      CHAR(3),
    COUNTRY_CODE     CHAR(3),
    LANGUAGE_PREF    VARCHAR(10),
    CONSTRAINT pk_sat_cust_demo PRIMARY KEY (HUB_CUSTOMER_HK, LOAD_DATE),
    CONSTRAINT fk_sat_cust_demo FOREIGN KEY (HUB_CUSTOMER_HK)
        REFERENCES HUB_CUSTOMER(HUB_CUSTOMER_HK)
);

SQL — CREATE TABLE SAT_PRODUCT_PRICING

-- SAT_PRODUCT_PRICING: fast-changing pricing data only
-- Split from SAT_PRODUCT_DESCRIPTION (rare change) by rate of change
CREATE TABLE SAT_PRODUCT_PRICING (
    HUB_PRODUCT_HK   CHAR(32)       NOT NULL,
    LOAD_DATE         TIMESTAMP      NOT NULL,
    LOAD_END_DATE     TIMESTAMP      NULL,
    HASH_DIFF         CHAR(32)       NOT NULL,
    RECORD_SOURCE     VARCHAR(100)   NOT NULL,
    -- Payload: pricing attributes only
    LIST_PRICE        DECIMAL(18,4),
    SALE_PRICE        DECIMAL(18,4),
    COST_PRICE        DECIMAL(18,4),
    CURRENCY_CODE     CHAR(3),
    PRICE_TIER        VARCHAR(20),   -- STANDARD, PREMIUM, BULK
    EFFECTIVE_DATE    DATE,          -- business date price became active
    CONSTRAINT pk_sat_prod_price PRIMARY KEY (HUB_PRODUCT_HK, LOAD_DATE),
    CONSTRAINT fk_sat_prod_price FOREIGN KEY (HUB_PRODUCT_HK)
        REFERENCES HUB_PRODUCT(HUB_PRODUCT_HK)
);

Concept 02

Satellite Splitting Strategies

🧠 ELI5 — Explain Like I’m Five

Should all of a customer’s attributes go in one big satellite, or should you split them into multiple satellites? The answer is: split them. But how? There are three rules: split by SOURCE (CRM data vs ERP data), split by RATE OF CHANGE (daily-changing prices should not share a satellite with rarely-changing account types), and split by CLASSIFICATION (demographic data vs financial data vs contact data).

1. Split by Source System

Each source system gets its own satellite for a given entity. If both CRM and ERP describe a customer, create SAT_CUSTOMER_FROM_CRM and SAT_CUSTOMER_FROM_ERP as separate tables.

Why it matters: when CRM data changes, only the CRM satellite receives a new row — the ERP satellite is completely unaffected. Without source splitting, a CRM change forces a new row that copies all ERP attributes alongside the changed CRM data, creating confusion about data origin and bloating history with unchanged ERP values.

2. Split by Rate of Change

Fast-changing attributes belong in one satellite; slow-changing attributes in another. The classic example: a product’s daily list price versus its description and category.

SAT_PRODUCT_DAILY_PRICE — changes every market day. SAT_PRODUCT_DESCRIPTION — changes perhaps once a quarter. If they share a satellite, every single price tick causes a new row that copies all product description columns verbatim — massive duplication with zero informational value on the description side.

3. Split by Classification / Business Domain

Group attributes that business users and business processes naturally access together. For a Customer entity, three natural groupings emerge:

SAT_CUSTOMER_DEMOGRAPHICS — name, date of birth, gender, nationality
SAT_CUSTOMER_CONTACT — email, phone, postal address, preferred channel
SAT_CUSTOMER_FINANCIALS — credit limit, account balance, risk score, payment terms

Each satellite can be independently loaded, versioned, and queried. A phone number change doesn’t touch the demographics satellite. Compliance teams can access financials without scanning demographic columns.

Attribute	Rate of Change	Source System	Target Satellite
FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, GENDER	Rare	CRM	`SAT_CUSTOMER_DEMOGRAPHICS`
EMAIL, PHONE, POSTAL_ADDRESS	Occasional	CRM	`SAT_CUSTOMER_CONTACT`
CREDIT_LIMIT, RISK_SCORE, PAYMENT_TERMS	Occasional	ERP	`SAT_CUSTOMER_FINANCIALS`
ACCOUNT_BALANCE, OUTSTANDING_AMOUNT	Frequent	ERP	`SAT_CUSTOMER_BALANCE`
LOYALTY_TIER, POINTS_BALANCE	Frequent	LOYALTY_APP	`SAT_CUSTOMER_LOYALTY`
SEGMENT_CODE, CAMPAIGN_COHORT	Occasional	MARKETING_PLATFORM	`SAT_CUSTOMER_MARKETING`

⚠

Anti-pattern: the catch-all satellite. The most common satellite design mistake is creating one giant satellite per entity — a single SAT_CUSTOMER with 60 columns from 4 source systems. This causes three serious problems:

(1) Excessive row duplication. Any single attribute change from any source creates a new row copying all 60 columns, even though 59 of them haven’t changed.

(2) Complex load logic. CRM and ERP loads can’t run independently. Every load must merge all sources into one row, handling NULLs and conflicts.

(3) Slow change detection. HASH_DIFF covers all 60 columns. Adding a new source column means recalculating the hash algorithm for all existing history.

Concept 03

CDC Loading with HashDiff

🧠 ELI5 — Explain Like I’m Five

Change Data Capture (CDC) in Data Vault means: only insert a new satellite row when the payload has actually changed. The HashDiff pattern makes this efficient — instead of comparing every single column between the incoming data and the current record, you compare two 32-character hash strings. Matching hashes mean the data is the same; skip the insert. Different hashes mean the data changed; insert a new row. One comparison instead of 30+ column comparisons.

The Standard Satellite Load Pattern

The canonical satellite load uses a LEFT JOIN to the current record (where LOAD_END_DATE IS NULL) and inserts only when the HashDiff differs or no current record exists. This pattern is idempotent — running it twice produces identical results because the second run finds matching HashDiffs and inserts nothing.

SQL — Standard Satellite INSERT Pattern (CDC via HashDiff)

-- ================================================================
-- LOAD: SAT_CUSTOMER_DEMOGRAPHICS
-- Pattern: INSERT only when HashDiff differs from current record
-- ================================================================
INSERT INTO SAT_CUSTOMER_DEMOGRAPHICS (
    HUB_CUSTOMER_HK,
    LOAD_DATE,
    LOAD_END_DATE,
    HASH_DIFF,
    RECORD_SOURCE,
    FIRST_NAME, LAST_NAME, EMAIL, DATE_OF_BIRTH, COUNTRY_CODE
)
SELECT
    stg.HUB_CUSTOMER_HK,
    stg.LOAD_DATE,
    NULL             AS LOAD_END_DATE,   -- NULL = current record
    stg.HASH_DIFF,
    stg.RECORD_SOURCE,
    stg.FIRST_NAME, stg.LAST_NAME, stg.EMAIL,
    stg.DATE_OF_BIRTH, stg.COUNTRY_CODE
FROM STG_CUSTOMER_DEMOGRAPHICS stg
LEFT JOIN SAT_CUSTOMER_DEMOGRAPHICS sat
    ON  sat.HUB_CUSTOMER_HK = stg.HUB_CUSTOMER_HK
    AND sat.LOAD_END_DATE IS NULL       -- JOIN to current record only
WHERE sat.HUB_CUSTOMER_HK IS NULL      -- no existing record (new customer)
   OR sat.HASH_DIFF != stg.HASH_DIFF;  -- payload has changed

Optional: Closing the Previous Record with LOAD_END_DATE

LOAD_END_DATE is not required in Data Vault, but populating it eliminates MAX(LOAD_DATE) aggregations when querying for the current record. If you choose to populate it, run this UPDATE immediately after each satellite load. It is the only legitimate UPDATE in a Data Vault implementation.

SQL — Optional LOAD_END_DATE Close Pattern

-- Optional: close the previous record when a new version is inserted
-- LOAD_END_DATE = the LOAD_DATE of the immediately following version
UPDATE SAT_CUSTOMER_DEMOGRAPHICS sat
SET LOAD_END_DATE = (
    SELECT MIN(s2.LOAD_DATE)
    FROM SAT_CUSTOMER_DEMOGRAPHICS s2
    WHERE s2.HUB_CUSTOMER_HK = sat.HUB_CUSTOMER_HK
      AND s2.LOAD_DATE > sat.LOAD_DATE
)
WHERE sat.LOAD_END_DATE IS NULL
  AND EXISTS (
    SELECT 1
    FROM SAT_CUSTOMER_DEMOGRAPHICS s3
    WHERE s3.HUB_CUSTOMER_HK = sat.HUB_CUSTOMER_HK
      AND s3.LOAD_DATE > sat.LOAD_DATE
  );
-- Note: PIT tables in the Business Vault can replace this UPDATE entirely,
--       keeping the Raw Vault 100% insert-only.

PIT table alternative: LOAD_END_DATE updates are optional. Point-in-Time (PIT) tables in the Business Vault compute the active satellite row for any snapshot date dynamically — making the UPDATE step unnecessary. Many teams skip LOAD_END_DATE entirely and rely on PIT tables, keeping the Raw Vault purely insert-only.

Concept 04

Multi-Active Satellites (MAS)

🧠 ELI5 — Explain Like I’m Five

A regular satellite stores one current value per attribute. But what about attributes that have multiple current values simultaneously? A customer can have 3 phone numbers, 2 email addresses, and 5 shipping addresses — ALL active at the same time. A Multi-Active Satellite (MAS) handles this with an extra sequence column added to the primary key, allowing multiple rows to coexist for the same entity at the same load timestamp.

Why a Regular Satellite Cannot Handle This

You cannot model multiple simultaneous values by adding columns (PHONE_1, PHONE_2, PHONE_3) — that’s a repeating group that violates First Normal Form and breaks when a customer has a 4th phone. You cannot use separate rows per phone number either, because the composite primary key (HUB_CUSTOMER_HK, LOAD_DATE) permits only one row per entity per load date.

The MAS solves this by adding a PHONE_SEQ column to the PK, enabling multiple rows to share the same HK and LOAD_DATE while differing by sequence number.

SQL — Multi-Active Satellite: SAT_CUSTOMER_PHONE_MULTI_ACTIVE

-- MAS: multiple phone numbers simultaneously active for one customer
CREATE TABLE SAT_CUSTOMER_PHONE_MULTI_ACTIVE (
    HUB_CUSTOMER_HK  CHAR(32)     NOT NULL,
    LOAD_DATE        TIMESTAMP    NOT NULL,
    PHONE_SEQ        INT          NOT NULL,  -- extra PK component: 1, 2, 3 …
    LOAD_END_DATE    TIMESTAMP    NULL,
    HASH_DIFF        CHAR(32)     NOT NULL,  -- hash of entire set, not one row
    RECORD_SOURCE    VARCHAR(100) NOT NULL,
    -- Payload
    PHONE_NUMBER     VARCHAR(20),
    PHONE_TYPE       VARCHAR(20),            -- MOBILE, HOME, WORK, FAX
    IS_PRIMARY       CHAR(1),                -- Y / N
    CONSTRAINT pk_sat_cust_phone PRIMARY KEY
        (HUB_CUSTOMER_HK, LOAD_DATE, PHONE_SEQ)
);

Loading a MAS: The Set-Based Approach

The critical difference from a regular satellite load: the HashDiff covers the entire current set of rows for a given entity, not just one row. This means:

Compute HashDiff as a hash of ALL phone numbers for the customer, concatenated in a deterministic sort order (e.g., by PHONE_TYPE then PHONE_NUMBER).
If any single phone number changes — added, removed, or updated — the HashDiff changes, triggering a complete new set of rows under a new LOAD_DATE.
All rows in the new set share the same LOAD_DATE. PHONE_SEQ is assigned 1, 2, 3… for each phone number in the set.
This ensures you can always reconstruct the complete set of phone numbers at any point in time by querying for the most recent LOAD_DATE on or before your snapshot date.

💡

Design guidance: Use a Multi-Active Satellite only when the attribute is genuinely set-valued and all members of the set are simultaneously valid. For sequenced history (value A replaced by value B over time), a regular satellite is correct. For concurrent values (three phone numbers all active right now), MAS is the right tool.

Concept 05

Effectivity Satellites

🧠 ELI5 — Explain Like I’m Five

A Link says “these two things were EVER related.” An Effectivity Satellite (EFF SAT) on that link says “these two things were related FROM this date TO that date.” Without an EFF SAT, you cannot tell when a relationship was active. With one, you get full relationship lifecycle history — open date, close date, re-open events, everything.

📚 Analogy

The Link is the marriage certificate — it proves the relationship happened. The Effectivity Satellite is the marriage and divorce record combined — it tells you the exact dates the relationship was active. You need both: the Link for permanent lineage, the EFF SAT for the active timeline.

SQL — Effectivity Satellite on LINK_EMPLOYEE_DEPARTMENT

-- EFF SAT: records WHEN the employee-department relationship was active
CREATE TABLE SAT_EFF_EMPLOYEE_DEPARTMENT (
    LINK_EMPLOYEE_DEPARTMENT_HK  CHAR(32)     NOT NULL,
    LOAD_DATE                    TIMESTAMP    NOT NULL,
    LOAD_END_DATE                TIMESTAMP    NULL,
    HASH_DIFF                    CHAR(32)     NOT NULL,
    RECORD_SOURCE                VARCHAR(100) NOT NULL,
    -- Effectivity payload
    EFFECTIVE_FROM               DATE         NOT NULL,  -- business start date
    EFFECTIVE_TO                 DATE         NULL,      -- NULL = still active
    IS_ACTIVE                    CHAR(1)      DEFAULT 'Y',  -- Y / N
    CONSTRAINT pk_sat_eff_ed PRIMARY KEY
        (LINK_EMPLOYEE_DEPARTMENT_HK, LOAD_DATE)
);

-- Query: was employee E001 in department D003 on 2024-06-01?
SELECT eff.IS_ACTIVE, eff.EFFECTIVE_FROM, eff.EFFECTIVE_TO
FROM  LINK_EMPLOYEE_DEPARTMENT      lnk
JOIN  SAT_EFF_EMPLOYEE_DEPARTMENT   eff
       ON  eff.LINK_EMPLOYEE_DEPARTMENT_HK = lnk.LINK_EMPLOYEE_DEPARTMENT_HK
       AND eff.LOAD_END_DATE IS NULL
       AND eff.EFFECTIVE_FROM  <= '2024-06-01'
       AND (eff.EFFECTIVE_TO IS NULL OR eff.EFFECTIVE_TO > '2024-06-01')
WHERE lnk.HUB_EMPLOYEE_HK   = MD5('E001')
  AND lnk.HUB_DEPARTMENT_HK = MD5('D003');

Loading an Effectivity Satellite

When you detect that a relationship is no longer being sent by the source system — the employee no longer appears in the department roster — you close the EFF SAT by inserting a new row with IS_ACTIVE = 'N' and EFFECTIVE_TO = today. This is still an INSERT, not an UPDATE, preserving the insert-only principle.

The original open row (IS_ACTIVE = 'Y', EFFECTIVE_TO = NULL) remains untouched. The new closing row sits alongside it with a later LOAD_DATE, recording exactly when the system first detected the relationship end.

Detecting Relationship End

Compare today’s full source extract to the current state in the EFF SAT. Keys present in the EFF SAT as IS_ACTIVE = ‘Y’ but absent from today’s extract have likely ended. Use a set-difference approach:

Load today’s roster into staging.
LEFT JOIN against current active EFF SAT rows (IS_ACTIVE = 'Y').
Where the staging join finds no match — insert a closing row with IS_ACTIVE = 'N'.

⚠

Grace period caution. Absent from today’s extract does not always mean the relationship ended — it might be missing due to a source outage, late-arriving data, or extract filter changes. Apply a grace period (e.g., absent for 3 consecutive load cycles) before inserting a closing row. Closing too eagerly creates false gaps in relationship history that are difficult to correct retroactively.

Concept 06

Interview Q&A: Satellites

Intermediate / Senior Questions

Why would you split one entity’s attributes across multiple satellites? ▶

Three reasons — source isolation, rate of change, and classification.

(1) Source isolation: if CRM and ERP both describe a customer, give each its own satellite. A CRM change then does not create a new ERP satellite row. Without splitting, a CRM change forces a new row copying all ERP attributes — muddying lineage and bloating history with unchanged data.

(2) Rate of change: a stock price changes every second; a product description changes monthly. Combine them and every price tick creates a new row with the full description copied verbatim — massive duplication with zero informational value on the description side. Separate them and each changes independently at its natural velocity.

(3) Classification: group attributes that business users access together. Targeted satellites mean smaller row sizes, faster scans, and independent load pipelines for each domain. A compliance team can access financials without pulling demographic columns.

What is LOAD_END_DATE and is it required in Data Vault? ▶

LOAD_END_DATE is the timestamp when the next version of a satellite record arrived, marking when the current record stopped being “current.” It is NOT required in Data Vault.

The vault is fully functional without it. You can always find the current record with WHERE LOAD_END_DATE IS NULL, or reconstruct any snapshot with WHERE LOAD_DATE = (SELECT MAX(LOAD_DATE) FROM SAT WHERE HK = x AND LOAD_DATE <= :snapshot_date).

LOAD_END_DATE is a query-performance optimization. It eliminates MAX() aggregations at query time and simplifies point-in-time queries. The trade-off: it requires an UPDATE to the previous row — the only legitimate UPDATE in Data Vault. Many teams use PIT tables in the Business Vault instead, keeping the Raw Vault entirely insert-only.

How do you handle satellite loading when a source sends duplicate rows for the same entity in one batch? ▶

De-duplicate in the staging layer before vault loading. Two standard approaches:

DISTINCT: SELECT DISTINCT HUB_CUSTOMER_HK, HASH_DIFF, FIRST_NAME, … in the staging SELECT. Works when all columns of a duplicate row are truly identical.

ROW_NUMBER(): Assign ranks using a window function partitioned by HK and HashDiff, then filter to rank = 1. More robust when duplicates differ on non-payload columns (e.g., two rows with different extract timestamps but identical business data).

Add a UNIQUE constraint on (HUB_CUSTOMER_HK, LOAD_DATE) in the satellite DDL as a safety net. If a duplicate survives staging, the constraint prevents it from corrupting the satellite rather than silently accepting it.

What is a Multi-Active Satellite and when do you need one? ▶

A Multi-Active Satellite (MAS) stores multiple simultaneous active values for an attribute with a “set” nature — phone numbers, email addresses, product categories — where all values are valid concurrently, not sequentially.

A regular satellite cannot handle this: separate columns (PHONE_1, PHONE_2) is a repeating group anti-pattern, and separate rows without a discriminator would violate the composite PK (HK, LOAD_DATE).

The MAS adds a SEQUENCE or RANK column to the PK, allowing multiple active rows per entity per LOAD_DATE. Loading requires inserting the complete current set every time any item in the set changes. The HashDiff covers the entire set, so you can always reconstruct the full set at any snapshot date by filtering to the most recent LOAD_DATE on or before that date.

How does an Effectivity Satellite differ from regular satellite history? ▶

They answer different questions and track different things.

A regular satellite’s LOAD_DATE history shows when attributes changed in the source system. Example: “the customer’s email changed on Jan 5th.” It answers: “What was this entity’s email on a given date?”

An effectivity satellite tracks when a relationship was active — open and close dates for the Link itself. Example: “this employee was in department D1 from Jan 2020 to Jun 2022, then moved to D2.” It answers: “Was this employee in this department on a given date?”

You can have both on the same Link simultaneously — a regular Link Satellite tracking relationship metadata changes, and an EFF SAT tracking the activity window. They are independent and serve different analytical purposes.

✅ Module 05 Key Takeaways

Every satellite requires five metadata columns — parent HK (FK + part of PK), LOAD_DATE (PK), LOAD_END_DATE (optional; NULL = current), HASH_DIFF (change detection), and RECORD_SOURCE (lineage). Everything else is payload.
Always split satellites by three criteria: source system (CRM vs ERP get separate satellites), rate of change (fast-moving attributes away from slow-moving ones), and classification (demographics vs financials vs contact). A single catch-all satellite per entity is the most expensive DV design mistake.
The CDC load pattern inserts a new satellite row only when HashDiff differs from the current record. It is idempotent — safe to re-run. LOAD_END_DATE is optional; PIT tables in the Business Vault handle current-record lookups without requiring any UPDATE on the Raw Vault.
Multi-Active Satellites handle set-valued attributes (multiple simultaneous phone numbers, emails) by adding a SEQUENCE key to the composite PK. The HashDiff covers the entire set — any change in any member of the set triggers a complete new set of rows under a new LOAD_DATE.
Effectivity Satellites sit on Links and record relationship lifecycle (open/close dates), while regular satellites track attribute changes. Use a grace period before closing an EFF SAT to avoid false gaps caused by missing source extracts or outages.