Module 01: Introduction to Data Vault 2.0

Concept 01

What is Data Vault 2.0?

🧠 ELI5 — Explain Like I'm Five

Imagine your company has 50 different apps — a CRM, an ERP, a website, a mobile app. Every one of them knows about your "Customer" differently. The CRM calls it CustomerID. The ERP calls it AccountNumber. The website calls it UserID.

Data Vault 2.0 is a way to store all that data in one place without destroying what makes each source special. It keeps a permanent, never-changing record of everything that ever happened — like a black box flight recorder for your business. You can always go back and see exactly what each system said at any point in time.

📚 Analogy

Think of a traditional data warehouse like a finished museum — curated, clean, beautiful, but hard to add new exhibits without rearranging everything. Data Vault is like a modular storage warehouse: every item has a precise location, you can add new items without moving the old ones, and you can always trace where any item came from.

The Formal Definition

Data Vault 2.0 (DV2) is a system of business intelligence that includes modeling, methodology, and architecture. It was formalized by Dan Linstedt and Michael Olschimke. The "2.0" adds ensemble modeling, NoSQL patterns, automation, and big data integration on top of the original Data Vault 1.0 specification.

At its core, DV2 is a detail-oriented, historized, uniquely linked set of normalized tables that support one or more functional areas of business. It is designed to be:

Flexible — Add new sources and entities without redesigning existing structures.
Scalable — Load data in parallel without cross-table dependencies.
Auditable — Every row has load timestamp and source system metadata. Nothing is ever deleted.
Resilient — Source system changes rarely break the vault; they add to it.

💡

Key insight: Data Vault is not a reporting system — it is an integration layer. Business users never query the Raw Vault directly. Reports are built on top of an Information Mart that transforms vault data into familiar star schemas or wide tables.

The Four Layers of a DV2 Architecture

A complete Data Vault 2.0 implementation has four distinct layers:

Staging Layer — Raw data lands here from source systems. No transformation except adding metadata (hash keys, load dates). This layer is typically truncated after each load.
Raw Vault — The permanent, insert-only integration store. Contains Hubs, Links, and Satellites loaded directly from staging. Data is never changed here.
Business Vault — Optional layer for derived data, soft business rules, PIT tables, and Bridge tables that make querying easier without breaking auditability.
Information Marts — Purpose-built reporting tables (star schemas, flat tables, OLAP cubes) consumed by BI tools and data scientists.

Concept 02

History & Evolution

🧠 ELI5

Data Vault wasn't invented overnight. Dan Linstedt spent 10+ years working in enterprise data warehousing, watching Kimball and Inmon approaches struggle with agility and auditability. He quietly developed Data Vault internally at the NSA and other agencies through the 1990s, published the first articles in 2000, and kept refining it. Version 2.0 came in 2013 to address cloud, automation, and big data.

1990–1999

Internal Development at NSA & Affiliates

Dan Linstedt develops the core concepts while solving real large-scale data integration problems. The "hub and spoke" model emerges as a way to integrate many source systems reliably.

2000

First Public Articles Published

Linstedt publishes "Data Vault Series" articles, introducing the terms Hub, Link, and Satellite to the public data warehousing community.

2002

Data Vault 1.0 Formalized

The first formal specification of Data Vault modeling is published. Early adopters in financial services and telecommunications begin implementations.

2013

Data Vault 2.0 Released

Co-authored with Michael Olschimke, DV2 adds: ensemble modeling for NoSQL/BigData, automation and metadata-driven development, SEI/CMMI-compliant methodology, and agile sprint integration.

2015–2020

Cloud Adoption Accelerates

Snowflake, BigQuery, and Databricks emerge. Community develops cloud-native DV patterns. Tools like dbt, WhereScape, and Datavault4dbt gain traction.

2021–Present

Mainstream Enterprise Adoption

Data Vault becomes a standard pattern in enterprise data strategy. CDVDM (Certified Data Vault Data Modeler) certification goes mainstream. The community adds streaming vault and AI/ML integration patterns.

Concept 03

Data Vault vs Kimball vs Inmon

🧠 ELI5

There are three main schools of thought for enterprise data warehousing. Inmon says build a normalized 3NF corporate warehouse first, then derive data marts. Kimball says build denormalized star-schema data marts first, and the warehouse emerges from joining them. Data Vault says separate the integration concerns from the reporting concerns completely — store everything raw and historized in the vault, then build any kind of reporting layer on top.

◆ Data Vault 2.0

Insert-only, never delete or update
Full historical auditability built-in
Highly agile: add sources without redesign
Parallel loading: no cross-table dependency
Separates integration from reporting layers
Excellent for regulatory compliance
Steeper initial learning curve
Requires information mart layer for BI

◆ Kimball (Dimensional)

Star schema: Facts + Dimensions
Business users can query directly
Fast query performance out of the box
SCD2 for slowly changing dimensions
Adding new sources requires refactoring
Limited auditability; history via SCD only
Well-understood; large skill pool
Best for stable, well-defined domains

◆ Inmon (3NF EDW)

Normalized 3NF enterprise data warehouse
Single source of truth at corporate level
Subject-area data marts derived from EDW
High data quality and consistency
Longest time-to-first-report
Expensive to build and maintain
Very structured; good for stable enterprises
Less flexible for agile environments

Criterion	Data Vault 2.0	Kimball	Inmon
Historical Auditability	✓ Full, automatic	Partial (SCD2)	Partial
Agility (add sources)	✓ Non-breaking	Requires refactor	Major effort
Parallel Loading	✓ Yes, by design	Limited	Limited
BI Query Simplicity	Needs Info Mart	✓ Direct star schema	Via data marts
Regulatory Compliance	✓ Excellent	Moderate	Moderate
Time to First Value	Medium	✓ Fast	Slow
Suitable for NoSQL/BigData	✓ Yes (DV2)	Limited	Limited

Practical guidance: Data Vault excels when you have many source systems, strict regulatory requirements, or a need for full historical lineage. Kimball remains the best choice for bounded, well-understood domains with a small number of sources where time-to-market is critical. Many enterprises use both — DV for the integration layer, star schema for the reporting layer.

Concept 04

The Three Pillars: Hub, Link, Satellite

🧠 ELI5

Data Vault uses exactly three types of tables. Hubs are for things (a Customer, a Product, an Order). Links are for relationships between things (a Customer placed an Order). Satellites are for descriptive details that change over time (the Customer's address, the Order's status). That's it. Every piece of data in your entire enterprise fits into one of these three buckets.

◯

HUB

Stores unique business keys. One row per unique entity. Never changes. No descriptive data.

🔗

LINK

Stores relationships between hubs. One row per unique combination of business keys. Insert-only.

📡

SATELLITE

Stores descriptive attributes. Many rows per hub/link key. Each row captures a point-in-time snapshot.

Hub: The Identity Store

A Hub stores the business key (BK) — the unique identifier that the business uses to refer to a real-world entity. For a Customer, that might be a CustomerID, a Tax Number, or an Email Address depending on your business context.

Hubs contain: a surrogate hash key, the business key, load date, and record source. Nothing else. No names, addresses, or any descriptive data — that all goes in Satellites.

SQL — Hub Customer Table Structure

-- HUB_CUSTOMER: stores unique customer business keys
CREATE TABLE HUB_CUSTOMER (
    HUB_CUSTOMER_HK  CHAR(32)      NOT NULL,  -- SHA-256 hash of business key
    CUSTOMER_BK      VARCHAR(50)   NOT NULL,  -- the actual business key
    LOAD_DATE        TIMESTAMP     NOT NULL,  -- when first seen
    RECORD_SOURCE    VARCHAR(100)  NOT NULL,  -- which system sent this
    CONSTRAINT pk_hub_customer PRIMARY KEY (HUB_CUSTOMER_HK)
);

-- Insert: only new business keys (no duplicates ever)
INSERT INTO HUB_CUSTOMER
SELECT
    MD5(UPPER(TRIM(src.CUSTOMER_ID))) AS HUB_CUSTOMER_HK,
    src.CUSTOMER_ID                    AS CUSTOMER_BK,
    src.LOAD_DATE,
    src.RECORD_SOURCE
FROM STG_CUSTOMER src
WHERE NOT EXISTS (
    SELECT 1 FROM HUB_CUSTOMER h
    WHERE h.HUB_CUSTOMER_HK = MD5(UPPER(TRIM(src.CUSTOMER_ID)))
);

Link: The Relationship Store

A Link records that a relationship exists between two or more hubs. Links are the key to Data Vault's flexibility — you can change how entities relate without touching the hub tables.

Links contain: a surrogate hash key (hash of all participating hub keys), the participating hub hash keys (as foreign keys), load date, and record source. No descriptive data.

SQL — Link CustomerOrder Table Structure

-- LINK_CUSTOMER_ORDER: customer placed an order
CREATE TABLE LINK_CUSTOMER_ORDER (
    LINK_CUSTOMER_ORDER_HK  CHAR(32)     NOT NULL,
    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)
);

-- Hash key = hash of all participating business keys combined
INSERT INTO LINK_CUSTOMER_ORDER
SELECT
    MD5(
        COALESCE(UPPER(TRIM(src.CUSTOMER_ID)),'-1') ||
        '|' ||
        COALESCE(UPPER(TRIM(src.ORDER_ID)),'-1')
    )               AS LINK_CUSTOMER_ORDER_HK,
    MD5(UPPER(TRIM(src.CUSTOMER_ID))) AS HUB_CUSTOMER_HK,
    MD5(UPPER(TRIM(src.ORDER_ID)))    AS HUB_ORDER_HK,
    src.LOAD_DATE,
    src.RECORD_SOURCE
FROM STG_ORDER src
WHERE NOT EXISTS (
    SELECT 1 FROM LINK_CUSTOMER_ORDER l
    WHERE l.LINK_CUSTOMER_ORDER_HK = MD5(
        COALESCE(UPPER(TRIM(src.CUSTOMER_ID)),'-1') || '|' ||
        COALESCE(UPPER(TRIM(src.ORDER_ID)),'-1')
    )
);

Satellite: The Descriptive Store

Satellites hold all the descriptive context — the attributes that describe a hub or a link. Because satellites are insert-only and each row carries a load date, you automatically get a full history of every change to every attribute. This is how Data Vault achieves effortless historical auditability.

Satellites contain: the parent hub/link hash key, a load date, an optional load end date, a HashDiff (hash of payload columns for change detection), the record source, and the actual descriptive columns.

SQL — Satellite Customer Demographics

-- SAT_CUSTOMER_DEMOGRAPHICS: descriptive attributes for HUB_CUSTOMER
CREATE TABLE SAT_CUSTOMER_DEMOGRAPHICS (
    HUB_CUSTOMER_HK  CHAR(32)     NOT NULL,  -- FK to HUB_CUSTOMER
    LOAD_DATE        TIMESTAMP    NOT NULL,  -- when this version was loaded
    LOAD_END_DATE    TIMESTAMP    NULL,      -- when the next version appeared
    HASH_DIFF        CHAR(32)     NOT NULL,  -- hash of all payload columns
    RECORD_SOURCE    VARCHAR(100) NOT NULL,
    -- Payload columns (the actual data)
    FIRST_NAME       VARCHAR(100),
    LAST_NAME        VARCHAR(100),
    EMAIL            VARCHAR(200),
    DATE_OF_BIRTH    DATE,
    COUNTRY_CODE     CHAR(3),
    CONSTRAINT pk_sat_cd PRIMARY KEY (HUB_CUSTOMER_HK, LOAD_DATE)
);

-- Only insert when payload has actually changed (HashDiff comparison)
INSERT INTO SAT_CUSTOMER_DEMOGRAPHICS
SELECT
    src.HUB_CUSTOMER_HK,
    src.LOAD_DATE,
    NULL    AS LOAD_END_DATE,
    src.HASH_DIFF,
    src.RECORD_SOURCE,
    src.FIRST_NAME, src.LAST_NAME, src.EMAIL,
    src.DATE_OF_BIRTH, src.COUNTRY_CODE
FROM STG_CUSTOMER_DEMOGRAPHICS src
LEFT JOIN SAT_CUSTOMER_DEMOGRAPHICS sat
    ON sat.HUB_CUSTOMER_HK = src.HUB_CUSTOMER_HK
    AND sat.LOAD_END_DATE IS NULL          -- current record only
WHERE sat.HASH_DIFF IS NULL             -- no existing record, OR
   OR sat.HASH_DIFF != src.HASH_DIFF;    -- payload has changed

Concept 05

When to Use Data Vault

🧠 ELI5

Data Vault isn't always the right tool. It shines in specific situations. If you have many source systems that change frequently, strict regulatory requirements for audit trails, or a need to integrate data from very different systems, Data Vault is probably your best choice. If you have one or two stable sources and just need fast reports, Kimball might be simpler.

🏠

Banking & Finance

Regulatory Reporting

Full audit trail for BCBS 239, GDPR, and AML compliance. Every data point traceable to source.

🌟

Insurance

Policy & Claims 360

Integrate policy, claims, customer, and agent data across dozens of legacy systems.

🛍

Retail & E-Commerce

Customer 360

Unify online, in-store, mobile, and loyalty system data into a single customer view.

📞

Telecom

Usage & Billing

Handle billions of CDRs (call detail records) with complete lineage for billing disputes.

🏥

Healthcare

Patient Data Integration

HIPAA-compliant integration of EHR, billing, lab, and claims data with full provenance.

The Data Vault Fit Criteria

Data Vault is a strong fit when your environment has three or more of these characteristics:

5+ source systems contributing to the data warehouse
Regulatory requirement for full data lineage and auditability
Frequently changing source systems and business rules
Agile development with multiple parallel teams
Multi-decade data retention requirements
Need to detect and respond to source system changes quickly
Large data volumes requiring parallel loading strategies

⚠

When NOT to use Data Vault: If you have 1-2 stable sources, a small team, and purely analytical workloads with no regulatory requirements — a star schema (Kimball) will get you to insights faster with less overhead. Data Vault has real complexity costs that only pay off at a certain scale and compliance pressure.

Concept 06

DV2 Business Benefits

🧠 ELI5

Data Vault 2.0 gives businesses four superpowers: you can always see exactly what happened in the past (auditability), you can add new data sources without breaking what already works (agility), you can trust the data because nothing is ever deleted (integrity), and you can load data in parallel much faster (scalability).

🔐

Full Auditability

Every row has a load timestamp and source system tag. Nothing is ever updated or deleted. Auditors can trace any data point to its exact origin.

⚡

Agile by Design

Add a new source system by adding new tables. Existing loads keep running. New requirements don't break old ones.

⚙

Parallel Loading

Hubs, Links, and Satellites have no inter-dependencies. All tables can be loaded simultaneously — dramatically cutting load window times.

📈

Infinite Scalability

The insert-only, parallel pattern maps perfectly to cloud MPP platforms. Scale compute independently of storage.

🔍

Time-Travel Queries

Because all history is preserved with timestamps, you can reconstruct the state of any entity at any point in time without any extra effort.

🏆

Regulatory Compliance

GDPR, BCBS 239, SOX, HIPAA — all require data lineage and audit trails. DV2 provides these automatically as a byproduct of its design.

🔨

Automation Ready

DV structures are highly regular and templated — perfect for code generation. Metadata-driven tools can build 80% of the vault automatically.

👥

Team Scalability

Multiple teams can work on different hubs, links, and satellites simultaneously. No code merge conflicts on vault objects in different areas.

Concept 07

Core Vocabulary Reference

🧠 ELI5

Data Vault has its own language. Before reading any DV content, make sure you know these terms cold. They'll appear constantly, and confusion about them is the #1 cause of DV design mistakes.

Term	Abbreviation	What it means
Business Key	`BK`	The natural key used by the business to identify an entity (e.g., CustomerID, OrderNumber, ISIN). The most important concept in all of Data Vault.
Hash Key	`HK`	A surrogate key derived by hashing the business key. Used as primary key in Hubs and as foreign keys across all other tables. Typically MD5 or SHA-256.
HashDiff	`HDIFF`	A hash of all payload (descriptive) columns in a satellite. Used to detect whether a row has changed since last load — if HashDiff matches, no insert needed.
Load Date	`LDTS`	The timestamp when a row was inserted into the vault. Represents when the data arrived in the warehouse, not when the business event occurred.
Record Source	`RSRC`	A string identifying which source system provided this row. Enables lineage and helps resolve conflicts between source systems.
Raw Vault	`RV`	The insert-only, lossless integration layer containing Hubs, Links, and Satellites loaded directly from staging with no business rules applied.
Business Vault	`BV`	Optional layer above Raw Vault for derived data, soft business rules, PIT tables, and Bridge tables. Separate from Raw Vault to preserve lineage.
Information Mart	`IM`	The consumer-facing reporting layer. Star schemas, flat tables, or wide tables built from vault data for BI tools and analytics.
Point-in-Time Table	`PIT`	A Business Vault table that pre-joins multiple satellite timelines for a hub, creating one row per entity per date for fast snapshot queries.
Bridge Table	`BRIDGE`	A Business Vault table that pre-flattens a Hub-Link-Hub chain into a wide lookup table, avoiding complex multi-join queries at report time.
Effectivity Satellite	`EFF SAT`	A satellite on a Link that tracks when the relationship was active/inactive (open/close dates). Different from the link itself, which just records that the relationship ever existed.
Multi-Active Satellite	`MAS`	A satellite that stores repeating groups (e.g., a customer can have multiple phone numbers). Has an additional sequence/rank key in its composite PK.

Concept 08

Interview Q&A: DV Fundamentals

Junior / Mid-Level Questions

What is Data Vault 2.0 and how does it differ from a traditional data warehouse? ▶

Data Vault 2.0 is an enterprise data warehousing methodology that separates integration from reporting. The Raw Vault layer stores all incoming data without transformation, using Hubs (business keys), Links (relationships), and Satellites (descriptive attributes). Traditional star schema warehouses transform data during load and often lose historical context.

Key DV2 differentiators: insert-only (no updates/deletes), full historical auditability via load timestamps, parallel loading capability, and resilience to source system changes. The reporting layer (Information Marts) is built separately on top, consuming vault data.

What is a Hub and what should (and shouldn't) it contain? ▶

A Hub stores unique business keys — the identifiers the business uses to refer to real-world entities like customers, products, or orders.

Must contain: Hash Key (primary key, derived from BK), Business Key (the natural key), Load Date, Record Source.

Must NOT contain: Any descriptive attributes, names, addresses, amounts, status codes, or any data that could change over time. Those belong in Satellites. Descriptive data in Hubs is the most common DV design mistake.

What is a HashDiff and why do we need it? ▶

A HashDiff is a hash (usually MD5 or SHA-256) computed over all the payload (descriptive) columns in a satellite row. It's the key to efficient change detection.

When loading a satellite, instead of comparing every individual column between the new incoming row and the current satellite row, you simply compare the HashDiff values. If they match, no insert is needed — the data hasn't changed. If they differ, insert a new row to capture the change.

This pattern makes satellite loading dramatically simpler and faster, especially for satellites with many columns. The HashDiff is always computed in the staging layer before vault loading begins.

Why does Data Vault use insert-only loading? What about performance? ▶

Insert-only loading exists for three reasons: auditability (you can never accidentally destroy data), parallelism (INSERT operations don't cause row-level locks like UPDATE does), and resilience (re-loading data is idempotent — running twice produces the same result).

Performance concern is valid but manageable. Most vault tables have hash key indexes. Satellites use the HashDiff pattern to avoid duplicate inserts. PIT tables in the Business Vault pre-materialize the current-record lookup, so queries don't need expensive MAX(LOAD_DATE) aggregations at query time. On cloud platforms (Snowflake, BigQuery), the columnar storage model handles large insert-only tables very efficiently.

Senior / Architect Level Questions

How do you handle a situation where a business key changes? (e.g., customer number re-assignment) ▶

This is a classic challenge in Data Vault design. There are two valid approaches:

1. Same-As Link (SAL): Create a Link that relates the old business key to the new business key. Both keys remain in the Hub. The SAL records the mapping, and the Business Vault uses this mapping to produce a "golden record" with a canonical key. This preserves full history and lineage.

2. Record Source routing: If the BK re-assignment is a clean cut-over from one source system to another, you may simply start loading from the new source, and the PIT table + effectivity satellite handle the timeline correctly.

The SAL approach is preferred for regulatory environments where you must prove data lineage across the key change event.

What is the difference between a Hard Business Rule and a Soft Business Rule in DV2? ▶

Hard Business Rules are structural rules that do NOT change the values of the data — things like determining which field is the business key, parsing a composite key, filtering out NULL business keys, and standardizing key format (TRIM/UPPER). These are applied in the staging layer and must run before Raw Vault loading.

Soft Business Rules are interpretive rules that transform or combine values — things like calculating a customer's risk score, deriving a product category from a code table, or joining CRM and ERP customer records. These never go in the Raw Vault. They belong in the Business Vault or Information Marts.

This separation is fundamental to DV2. If you put soft rules in the Raw Vault and a rule changes, you've corrupted your audit trail. Keeping rules in the Business Vault means you can re-derive any result as rules evolve, while the Raw Vault remains unchanged.

✅ Module 01 Key Takeaways

Data Vault 2.0 is an integration methodology: Raw Vault (Hubs/Links/Sats) stores all data permanently; Information Marts are the reporting layer built on top.
The three building blocks are Hubs (business keys), Links (relationships), and Satellites (descriptive attributes). Every piece of enterprise data fits one of these categories.
DV2 is insert-only: no updates, no deletes. Every row has a Load Date and Record Source for complete auditability.
DV2 shines with many source systems, regulatory requirements, or agile teams. It's not always the right tool — Kimball is often simpler for fewer sources.
Hard business rules (structural) go in staging. Soft business rules (interpretive) go in Business Vault or Info Marts — never in Raw Vault.
HashDiff enables efficient change detection in Satellites without comparing every column individually.