Data Vault: Enterprise-Ready Data Modeling for Scalable Warehouses

Kimball star schemas work well for moderate data volumes with relatively stable business requirements. But when you are dealing with enterprise-scale data, multiple source systems, and constantly evolving business needs, a different approach helps.

Data Vault was created by Dan Linstedt in the 1990s to handle these challenges specifically. It prioritizes auditability, scalability, and adaptability over query simplicity. The trade-off is more complex schemas and more complex queries.

The Core Problem Data Vault Solves

In traditional dimensional modeling, you design your warehouse schema around what the business needs today. When source systems change, new columns get added, or business definitions shift, you end up retrofitting your carefully designed star schema.

Data Vault embraces change as a first-class concern. The methodology separates concerns into three distinct table types: Hubs, Links, and Satellites. Each type has a specific responsibility and predictable behavior when requirements change.

The result is a warehouse that can absorb changes from source systems without requiring rewrites of existing structures.

Hubs: The Business Keys

A hub table represents a core business concept. A customer, a product, an order. The hub contains only the business key and metadata about when it was loaded.

CREATE TABLE hub_customer (
    hub_customer_key VARCHAR(50) PRIMARY KEY,  -- Hash of business key
    customer_id VARCHAR(50) NOT NULL,           -- Natural business key
    load_date TIMESTAMP NOT NULL,
    record_source VARCHAR(100) NOT NULL
);

CREATE TABLE hub_product (
    hub_product_key VARCHAR(50) PRIMARY KEY,
    product_id VARCHAR(50) NOT NULL,
    load_date TIMESTAMP NOT NULL,
    record_source VARCHAR(100) NOT NULL
);

The hub_key is a hash of the natural business key. This decouples the warehouse from source system key formats and enables loading from multiple sources into the same hub.

Note that hubs contain no attributes. Attribute storage is delegated to satellites.

flowchart TB
    subgraph Hubs[Hubs (Business Keys&#41]
        H1[hub_customer<br/>hub_customer_key&#44; customer_id&#44; load_date]
        H2[hub_product<br/>hub_product_key&#44; product_id&#44; load_date]
        H3[hub_order<br/>hub_order_key&#44; order_id&#44; load_date]
    end
    subgraph Links[Links &#40;Relationships&#41]
        L1[link_customer_order<br/>link_customer_order_key&#44; hub_customer_key&#44; hub_order_key]
        L2[link_product_category<br/>hub_product_key&#44; hub_category_key]
    end
    subgraph Satellites[Satellites &#40;Descriptive Data&#41]
        S1[sat_customer_details<br/>hub_customer_key&#44; name&#44; email&#44; region&#44; load_date]
        S2[sat_product_info<br/>hub_product_key&#44; brand&#44; category&#44; load_date]
        S3[sat_order_status<br/>hub_order_key&#44; status&#44; total_amount&#44; load_date]
    end
    H1 --> L1
    H3 --> L1
    H2 --> L2
    H1 --> S1
    H2 --> S2
    H3 --> S3

Links: The Relationships

A link table represents relationships between hubs. If a customer places an order, there is a link. If a product belongs to a category, there is a link.

CREATE TABLE link_customer_order (
    link_customer_order_key VARCHAR(50) PRIMARY KEY,
    hub_customer_key VARCHAR(50) NOT NULL REFERENCES hub_customer(hub_customer_key),
    hub_order_key VARCHAR(50) NOT NULL REFERENCES hub_order(hub_order_key),
    load_date TIMESTAMP NOT NULL,
    record_source VARCHAR(100) NOT NULL
);

Links can connect more than two hubs for complex relationships. A many-to-many relationship is handled naturally without junction tables.

-- Many-to-many: customer purchases product at store
CREATE TABLE link_transaction (
    link_transaction_key VARCHAR(50) PRIMARY KEY,
    hub_customer_key VARCHAR(50),
    hub_product_key VARCHAR(50),
    hub_store_key VARCHAR(50),
    load_date TIMESTAMP NOT NULL,
    record_source VARCHAR(100) NOT NULL
);

Satellites: The Descriptive Data

Satellites hold all the descriptive attributes. They link to their parent hub or link and carry the payload of descriptive data.

CREATE TABLE sat_customer_details (
    hub_customer_key VARCHAR(50) NOT NULL REFERENCES hub_customer(hub_customer_key),
    load_date TIMESTAMP NOT NULL,
    hash_diff VARCHAR(32) NOT NULL,  -- Hash of attribute values for change detection
    customer_name VARCHAR(200),
    customer_email VARCHAR(200),
    customer_phone VARCHAR(50),
    customer_region VARCHAR(100),
    is_current BOOLEAN DEFAULT TRUE,
    load_end_date TIMESTAMP,
    record_source VARCHAR(100)
);

Each satellite row has a load_date and load_end_date, enabling point-in-time queries. When a customer attribute changes, a new satellite row is inserted with the new values and the previous row’s load_end_date is set.

This gives you complete change history without any special SCD handling. Type 2 behavior is baked into the satellite design.

Hash Keys: The Link to the Business World

Data Vault uses hash keys instead of surrogate keys. The hub_customer_key is a hash of the customer_id from the source system.

-- Generate hash key in load process
SELECT SHA256(CONCAT(customer_id, '|', record_source)) AS hub_customer_key
FROM source_customer;

Hash keys provide several benefits:

Source independence: The same customer from CRM and ERP systems generates the same hash key if you use a consistent business key. This enables matching across systems.

No sequence management: You do not need a central key generation service. The hash is computed from the data itself.

Auditability: Given a business key and source, you can recompute the hash at any time and verify data integrity.

The trade-off is longer keys (SHA-256 produces 64 hex characters) and the theoretical possibility of collisions. For practical data volumes, collision risk is negligible.

Point-in-Time Tables and Bridge Tables

Data Vault enables complex historical analysis through point-in-time tables and bridge tables.

Point-in-Time Tables

A point-in-time (PIT) table solves a specific performance problem. Without one, every point-in-time query against a satellite requires scanning all rows for a given hub key and comparing load_date values to find which row was current at the requested timestamp. On a satellite with millions of rows and years of history, this means full scans on every ad hoc query. The PIT table pre-computes the answer.

The PIT table stores, for each hub key, the load_date of the most recent satellite row at the time of each historical load event. It acts as a bookmark into the satellite history.

CREATE TABLE pit_customer (
    hub_customer_key VARCHAR(50) PRIMARY KEY,
    load_date TIMESTAMP NOT NULL,
    sat_customer_details_load_date TIMESTAMP,
    sat_customer_address_load_date TIMESTAMP,
    sat_customer_contact_load_date TIMESTAMP
);

Consider a query: what was customer email as of January 15? Without a PIT table, you must scan sat_customer_details, compare load_date <= ‘2025-01-15’ and load_end_date > ‘2025-01-15’ for each row. With a PIT table, you find the row where hub_customer_key and load_date match, grab sat_customer_details_load_date, and fetch exactly one row from the satellite.

-- Without PIT: full satellite scan per query
SELECT customer_email
FROM sat_customer_details
WHERE hub_customer_key = 'CUST-001'
  AND load_date <= '2025-01-15'
  AND (load_end_date > '2025-01-15' OR load_end_date IS NULL);

-- With PIT: single lookup, then targeted fetch
SELECT s.customer_email
FROM pit_customer p
JOIN sat_customer_details s
  ON s.hub_customer_key = p.hub_customer_key
  AND s.load_date = p.sat_customer_details_load_date
WHERE p.hub_customer_key = 'CUST-001'
  AND p.load_date = (
    SELECT MAX(load_date)
    FROM pit_customer
    WHERE hub_customer_key = 'CUST-001'
      AND load_date <= '2025-01-15'
  );

The inner query finds the most recent PIT snapshot on or before the query date. The join then fetches exactly the satellite row that was current at that snapshot. The satellite scan disappears entirely. PIT tables are regenerated each time a satellite loads, so they always reflect the current state of satellite history.

Bridge Tables for Hierarchies

Bridge tables handle parent-child hierarchies without the complexity of recursive CTEs.

CREATE TABLE bridge_customer_hierarchy (
    hub_customer_key VARCHAR(50) NOT NULL,
    parent_hub_customer_key VARCHAR(50) NOT NULL,
    level INT NOT NULL,
    load_date TIMESTAMP NOT NULL
);

This lets you aggregate at any level of the hierarchy efficiently.

Loading Data into Data Vault

The Data Vault load process follows a predictable pattern.

def load_customer_satellite(source_record, hub_customer_key):
    # Compute hash of current attributes
    current_hash = compute_hash(source_record)

    # Check if this is new data or a change
    previous_row = get_latest_satellite(hub_customer_key)

    if previous_row is None or previous_row.hash_diff != current_hash:
        # Close out previous row
        if previous_row:
            update_satellite_end_date(previous_row, source_record.load_date)

        # Insert new row
        insert_satellite({
            'hub_customer_key': hub_customer_key,
            'load_date': source_record.load_date,
            'hash_diff': current_hash,
            'customer_name': source_record.name,
            'customer_email': source_record.email,
            'is_current': True,
            'record_source': source_record.system
        })

This is essentially Type 2 SCD behavior, but it emerges naturally from the loading pattern rather than requiring special handling.

Comparison with Kimball

Aspect	Kimball Star Schema	Data Vault
Schema complexity	Simple star	Complex hub/link/satellite
Query complexity	Simple joins	Multi-stage joins with PIT tables
Change handling	Requires SCD strategies	Built-in historical tracking
Scalability	Good to ~100TB	Designed for enterprise scale
Auditability	Limited	Full audit trail
Source flexibility	Single source per dimension	Multiple sources per hub
Agility	Schema changes are costly	New satellites add without modifying existing

Kimball wins on query simplicity and developer productivity. Data Vault wins on scalability, auditability, and adaptability.

For organizations with complex source landscapes and strong audit requirements, Data Vault is worth the added schema complexity. For smaller organizations with simpler needs, the Kimball approach remains practical.

For other approaches to data modeling, see Kimball Dimensional Modeling for the traditional star schema approach, or One Big Table for a denormalized alternative.

Integration Point Tables

One powerful Data Vault pattern is the integration point table. This is a link that represents a business event at a specific moment, capturing the full context of that event.

-- Instead of separate links for different event aspects:
-- Order placed link
-- Payment received link
-- Shipment confirmed link

-- Use a single integration point:
CREATE TABLE link_sales_transaction (
    link_sales_transaction_key VARCHAR(50) PRIMARY KEY,
    hub_customer_key VARCHAR(50),
    hub_product_key VARCHAR(50),
    hub_store_key VARCHAR(50),
    hub_date_key VARCHAR(50),
    hub_time_key VARCHAR(50),
    transaction_amount DECIMAL(12,2),
    load_date TIMESTAMP NOT NULL,
    record_source VARCHAR(100) NOT NULL
);

This captures the complete transaction context in one place, enabling comprehensive analysis without joins across multiple link tables.

Raw Vault and Business Vault

The Data Vault methodology distinguishes between the raw vault and the business vault.

Raw Vault contains exactly what came from the source systems, with minimal transformation. Hubs, links, and satellites match source structures. This layer preserves auditability and enables reprocessing if definitions change.

Business Vault applies business rules and derived calculations on top of the raw vault. Computed metrics, conformed dimensions, and business keys live here.

Separating these layers gives you a clean lineage from source data to business metrics. You can always trace a number back to its origin.

When to Use Data Vault

Data Vault is particularly well-suited for:

Large-scale enterprise data warehouses where multiple source systems feed into a central repository. The hub/link/satellite structure handles source heterogeneity gracefully.

Regulatory environments requiring complete audit trails. Every record has load dates, record sources, and change history built in.

Organizations with evolving requirements where source systems change frequently. Adding a new satellite does not affect existing structures.

Merger and acquisition scenarios where you need to integrate data from acquired companies with different key structures. Hash keys enable matching across systems.

The added complexity is not free. Plan for longer development cycles and more complex query logic. Data Vault pays off over years of operation, not weeks.

Data Vault Production Failure Scenarios

Hash collision causing duplicate business keys

The failure starts silently. Two different customers, from two different source systems, produce the same SHA-256 hash. CRM system A has customer_id 12345 for Alice Smith. ERP system B has customer_id 67890 for Bob Jones. Both generate the same hub_customer_key because their hash input happens to collide.

SHA-256 produces a 256-bit output. The theoretical collision probability for any single pair is 1 in 2^128, which sounds negligible. But enterprise data warehouses scale changes this calculation. A hub with 10 billion records does not check collisions one pair at a time. The birthday paradox applies: with sqrt(N) records, you cross into meaningful collision probability. For 10 billion records, sqrt(10B) is roughly 100,000. At that scale, collision audits stop being theoretical.

When two different customers share a hub key, the problem propagates outward. Link tables that should reference two distinct customers reference the same one. Business vault calculations that should split revenue, attribution, and history merge them instead. Downstream reports show Alice and Bob as a single customer. The error persists until a business user notices anomalous data and escalates it. By then, reprocessing months of link and satellite history may be required.

Using a composite hash with record_source mitigates the problem for cross-system duplicates. Alice from CRM and Alice from ERP produce different hashes because their record_source differs. This does not eliminate collisions entirely. Two different customer_ids within the same source system can still collide. Neither the composite hash nor the raw hash provides absolute guarantees at scale.

Periodic hash distribution audits catch clusters. If a hub shows an unusual number of distinct business keys mapping to the same hash, that hub needs investigation.

PIT table falling out of sync with satellites

The PIT table stores load_date references into satellites. It is a derived structure that depends on satellite state. When a satellite loads without updating its corresponding PIT row, the PIT table points to satellite rows that do not exist or have been superseded.

Consider a PIT table where sat_customer_details_load_date references a load_date that was later superseded. The satellite query path that goes through PIT returns the old attribute values. A query that hits the satellite directly and uses load_date filtering returns current values. Reports written against the PIT path show January data while reports written against direct satellite access show June data. Different teams drawing from the same warehouse reach different conclusions about the same customer.

The gap between PIT falling out of sync and detection can stretch for days. Business users notice inconsistencies and open tickets. The root cause investigation traces the problem back to a pipeline run where a satellite load completed but the PIT update step failed silently or was skipped. By that point, downstream business vault tables have already built on the stale PIT values.

The root cause is architectural: the PIT table is not protected by the same transactional boundary as the satellite it references. If the satellite and its PIT row are not updated in the same atomic transaction, a failure between those steps leaves them inconsistent.

Satellite version explosion from high-frequency changes

The source is a real-time CDC feed from a point-of-sale system. Every barcode scan, every price change, every cashier override generates a change event. For a high-volume retailer, a single product can emit hundreds of change events per minute during peak hours. Each event lands in the staging area and each event triggers a satellite insert.

The satellite stores every change as a separate row with its own load_date and load_end_date. After 30 days of continuous real-time feeding, a product that averaged 10 changes per minute has accumulated approximately 432,000 satellite rows. Each row carries the full satellite schema: hub key, load dates, hash diff, and all attribute columns. For a satellite with 50 columns and 8-byte base types, a single entity can consume gigabytes of storage.

The problem compounds across the full product catalog. A retailer with 100,000 active SKUs, each generating 50,000 rows over a month, produces 5 billion satellite rows for that single satellite table. Query performance degrades. Indexes bloat. PIT table generation slows because it must traverse millions of rows per hub key.

The mechanism is straightforward: every change event from the source becomes a satellite insert, regardless of how close it is to the previous change. There is no batching at the satellite layer, so the raw event rate flows directly into row count growth.

Raw vault loading sequence violations

The raw vault enforces a specific load order. Hubs must load before links. Links must load before satellites. Satellites covering the same hub can load in parallel only if they do not share a PIT table. Violating this order produces immediate failures or silent data corruption.

The FK constraint failure manifests when a link tries to reference a hub_customer_key that does not yet exist. Suppose link_sales_transaction is scheduled to load at 2:00 AM and hub_customer is running slow because last night’s ERP extract was delayed. The link insert fails on the FK constraint. The job fails. Downstream business vault builds that depend on link_sales_transaction do not run. Morning reports are blank.

The PIT table inconsistency follows a different pattern. Two satellites for the same hub, sat_customer_details and sat_customer_address, are scheduled to load in parallel. sat_customer_details completes first and inserts its load_date into the PIT table. sat_customer_address fails on a data quality check and rolls back. The PIT now references a load_date that does not exist in sat_customer_address. Any query through the PIT that joins to sat_customer_address returns no rows or stale rows for the window where the failed load would have inserted.

The cascading effect reaches business vault. Business vault tables built on raw vault links assume referential integrity. When a link load fails, business vault inserts that depend on that link are skipped. The gap in raw vault data propagates upward. Monthly reports that aggregate from business vault tables show missing transactions with no obvious explanation until someone traces back through the raw vault load logs.

Data Vault Anti-Patterns

Treating Data Vault like a star schema. Building Business Vault that looks exactly like a star schema on top of raw vault, but skipping the raw vault layer because “it’s simpler.” This defeats the auditability purpose and creates a schema that will need rewriting when source systems change again.

Adding satellites too early. Modeling every possible attribute before you understand which ones actually change and which source systems they come from. Satellites should be added incrementally as you discover new requirements, not upfront in one big design.

Ignoring the Business Vault layer. Staying in raw vault forever because it is “flexible.” Raw vault queries are complex and slow. The purpose of Data Vault is to eventually enable business users to query through a business-friendly layer, not to live in raw vault forever.

No automation for hash key generation. Hand-computing hashes in ad-hoc SQL instead of building a reusable hash generation function. This leads to inconsistent hash algorithms across loads and silent data quality issues.

Data Vault Quick Recap

• Hubs store business keys (customer_id, product_id); links store relationships (customer-to-order); satellites store attributes that change over time.
• Hash keys decouple the warehouse from source system key formats and enable cross-system matching.
• Raw vault preserves source data exactly as-is; business vault applies business rules on top.
• PIT tables make point-in-time queries efficient by pre-computing which satellite row was current at any given time.
• Plan for longer development cycles and more complex queries than star schema — Data Vault pays off over years, not weeks.
• Implement satellite batching to prevent row explosion from high-frequency CDC sources.

For other approaches to data modeling, see Kimball Dimensional Modeling for the traditional star schema approach, or One Big Table for a denormalized alternative.