Data Vault Modeling: Agile Data Warehousing for Enterprise Analytics

Data Vault is a data modeling methodology designed for enterprise data warehousing. It separates data into three distinct table types - hubs for business keys, links for relationships, and satellites for descriptive attributes - enabling flexible, auditable, and incrementally buildable data warehouses.

Developed by Dan Linstedt in the 1990s and formalized in Data Vault 2.0, the methodology addresses challenges that traditional dimensional modeling struggles with: integrating data from many source systems, adapting to frequent source changes, maintaining full audit history, and enabling parallel development by multiple teams.

Core Components

Hubs: Business Keys

Hubs store unique business keys - the identifiers that represent core business entities. Each hub contains:

• A surrogate hash key (computed from the business key)
• The business key itself
• Load date timestamp
• Record source

CREATE TABLE hub_customer (
  hub_customer_hk BINARY(32) PRIMARY KEY,  -- Hash of business key
  customer_id VARCHAR(50),                  -- Business key
  load_date TIMESTAMP,                      -- When first loaded
  record_source VARCHAR(100)                -- Source system
);

Key principles:

• Hubs are insert-only - once a business key exists, it's never deleted or modified
• Business keys are never updated - if they change in the source, that's a new entity
• Hubs contain no descriptive attributes - those go in satellites
• Hash keys enable parallel loading and easier joins

Links: Relationships

Links capture relationships between business entities. They connect two or more hubs, representing how business keys relate to each other:

CREATE TABLE link_customer_order (
  link_customer_order_hk BINARY(32) PRIMARY KEY,  -- Hash of hub keys
  hub_customer_hk BINARY(32),                      -- FK to customer hub
  hub_order_hk BINARY(32),                         -- FK to order hub
  load_date TIMESTAMP,
  record_source VARCHAR(100)
);

Key principles:

• Links are also insert-only - relationships once recorded are never deleted
• Links have no attributes about the relationship - those go in link satellites
• A link can connect more than two hubs (ternary or higher relationships)
• Same-as links connect different business keys that represent the same entity

Satellites: Descriptive Attributes

Satellites store all descriptive attributes and their history. They hang off either hubs (describing entities) or links (describing relationships):

CREATE TABLE sat_customer_details (
  hub_customer_hk BINARY(32),           -- FK to parent hub
  load_date TIMESTAMP,                   -- When this version was loaded
  load_end_date TIMESTAMP,               -- When superseded (optional)
  record_source VARCHAR(100),
  customer_name VARCHAR(200),
  email VARCHAR(200),
  segment VARCHAR(50),
  industry VARCHAR(100),
  hash_diff BINARY(32),                  -- Hash of all attributes for change detection
  PRIMARY KEY (hub_customer_hk, load_date)
);

Key principles:

• Satellites are insert-only - new attribute values create new records
• Full history is maintained automatically
• Multiple satellites can describe the same hub (e.g., different source systems)
• Change detection uses hash comparison for efficiency

Why Data Vault?

Handling Multiple Source Systems

Traditional dimensional models assume a single source of truth. When customer data comes from CRM, e-commerce, and support systems - each with different identifiers and attributes - dimensional models require upfront reconciliation.

Data Vault handles this naturally:

• Each source contributes its business keys to hubs
• Same-as links reconcile different keys representing the same entity
• Multiple satellites preserve source-specific attributes without conflict
• You can load sources independently and reconcile later

Adapting to Change

When source systems change - new attributes, modified relationships, different data formats - dimensional models often require restructuring. Data Vault localizes changes:

• New attributes: Add to existing satellite or create new satellite
• New relationships: Create new link
• New entities: Create new hub
• Structure changes: Rarely affect existing objects

Teams can develop and deploy changes independently without coordinating schema migrations.

Full Auditability

Data Vault maintains complete history by design:

• Every record has load date and source system
• Nothing is ever deleted or updated
• You can reconstruct the data warehouse state at any historical point
• Lineage is explicit in the model structure

For regulated industries or any organization requiring data governance, this auditability is valuable.

Parallel Development

The modular structure enables parallel development:

• Teams can work on different hubs, links, and satellites simultaneously
• New source systems can be integrated without touching existing structures
• Hash keys eliminate surrogate key conflicts between teams

Data Vault Architecture Layers

Raw Vault (Staging)

The raw vault loads data from sources with minimal transformation:

• Business keys extracted and loaded to hubs
• Relationships captured in links
• All attributes captured in satellites
• No business rules applied - just clean extraction

This layer prioritizes load speed and auditability over query convenience.

Business Vault

The business vault applies business rules and derives calculated values:

• Bridge tables for simplified multi-link navigation
• Point-in-time tables for efficient historical queries
• Calculated satellites with derived attributes
• Business rules applied through controlled processes

This layer remains in vault structure but adds business logic.

Information Marts

For analytics consumption, Data Vault is typically transformed into dimensional models:

• Star schemas for BI tools
• Wide tables for data science
• API-friendly structures for applications

The vault provides the governed, auditable foundation; marts provide query-optimized views.

Implementation Patterns

Hash Keys

Data Vault 2.0 uses hash keys (MD5, SHA-1, or SHA-256) computed from business keys:

-- Computing hash key
hub_customer_hk = SHA256(UPPER(TRIM(customer_id)))

Benefits:

• Parallel loads don't require sequence generation coordination
• Deterministic - same business key always produces same hash
• Joins are efficient on fixed-length binary keys

Effectivity Satellites

Track when relationships were active:

CREATE TABLE sat_customer_order_eff (
  link_customer_order_hk BINARY(32),
  load_date TIMESTAMP,
  effective_from DATE,
  effective_to DATE,
  record_source VARCHAR(100)
);

This extends links with temporal validity without modifying the link structure.

Multi-Active Satellites

When entities have multiple concurrent values for the same attributes:

CREATE TABLE sat_customer_addresses (
  hub_customer_hk BINARY(32),
  load_date TIMESTAMP,
  address_type VARCHAR(20),        -- BILLING, SHIPPING, etc.
  address_line_1 VARCHAR(200),
  city VARCHAR(100),
  postal_code VARCHAR(20),
  PRIMARY KEY (hub_customer_hk, load_date, address_type)
);

The address type becomes part of the primary key, allowing multiple concurrent addresses.

Reference Tables

Static reference data (country codes, currency codes) that doesn't need vault treatment:

CREATE TABLE ref_country (
  country_code VARCHAR(3) PRIMARY KEY,
  country_name VARCHAR(100)
);

Referenced by satellites but not modeled as hubs - the overhead isn't warranted for stable reference data.

AI Analytics Considerations

Data Vault's normalized structure creates challenges for AI systems that expect simpler schemas.

The Semantic Layer Requirement

AI systems querying raw Data Vault structures face significant complexity:

• Multiple joins required for basic queries
• Temporal logic for getting current values
• Multiple satellites for complete entity views

A semantic layer is essential - it abstracts vault complexity into business-friendly concepts:

entity:
  name: Customer
  description: Complete customer view with current attributes
  source_hub: hub_customer
  satellites:
    - sat_customer_details
    - sat_customer_contact
  temporal_handling: current_only  # or point_in_time

The semantic layer generates the complex joins and temporal filters, presenting a simple Customer entity to AI consumers.

Historical Queries

Data Vault's historical completeness enables powerful temporal queries - but requires semantic layer support:

• "What was this customer's segment when they placed this order?"
• "How have customer attributes changed over the past year?"
• "Reconstruct the data as it existed on January 1st"

Without semantic layer abstraction, AI would need to generate complex temporal joins - a common source of errors.

Documentation Requirements

For AI-ready Data Vault, document:

• Business meaning of each hub (not just technical key)
• Semantic meaning of each link relationship
• Which satellite contains which attributes
• How to derive current vs. historical views
• Business rules applied in business vault

This documentation feeds the semantic layer, enabling AI to query vault-structured data accurately.

When to Use Data Vault

Good fit:

• Enterprise data integration across many source systems
• Regulatory requirements for auditability
• Frequent source system changes
• Need for parallel development
• Long-term historical analysis

Less suitable:

• Simple analytics with stable sources
• Near-real-time query requirements without mart layer
• Small-scale data warehousing
• Teams without Data Vault expertise

Data Vault adds complexity justified by enterprise scale and governance requirements. For simpler scenarios, dimensional modeling may be more appropriate. Many organizations use Data Vault for integration and transform to dimensional for consumption - combining the strengths of both approaches.