Data Vault Modeling

Data Vault Modeling

When to Use This Skill

Use this skill when:

• Data Vault Modeling tasks - Working on design hub/link/satellite structures for enterprise data warehouses using data vault 2.0 methodology
• Planning or design - Need guidance on Data Vault Modeling approaches
• Best practices - Want to follow established patterns and standards

Overview

Data Vault is a detail-oriented, historical tracking, and uniquely linked set of normalized tables supporting enterprise data warehouses. Designed for agility, auditability, and scalability.

Data Vault Components

┌─────────────────────────────────────────────────────────────────┐
│                         DATA VAULT                               │
├─────────────────────────────────────────────────────────────────┤
│                                                                  │
│  ┌──────────┐         ┌──────────┐         ┌──────────┐        │
│  │   HUB    │◄───────►│   LINK   │◄───────►│   HUB    │        │
│  │ Customer │         │  Orders  │         │ Product  │        │
│  └────┬─────┘         └────┬─────┘         └────┬─────┘        │
│       │                    │                    │               │
│  ┌────┴─────┐         ┌────┴─────┐         ┌────┴─────┐        │
│  │SATELLITE │         │SATELLITE │         │SATELLITE │        │
│  │ CustInfo │         │ OrderDtl │         │ ProdInfo │        │
│  └──────────┘         └──────────┘         └──────────┘        │
│                                                                  │
└─────────────────────────────────────────────────────────────────┘

Hub Tables

Purpose

Hubs store unique business keys - the core business concepts that won't change.

Hub Structure

Hub Example

Note: Data Vault 2.0 uses snake_case with standardized prefixes (hub_, sat_, link_) as part of its methodology. This convention is acceptable for Data Vault schemas even on SQL Server.

CREATE TABLE hub_customer (
    hub_customer_hk BINARY(32) NOT NULL PRIMARY KEY,
    customer_bk VARCHAR(50) NOT NULL,
    load_dts DATETIME2 NOT NULL,
    record_source VARCHAR(100) NOT NULL
);

CREATE UNIQUE INDEX IX_hub_customer_bk ON hub_customer(customer_bk);

Hub Template

# Hub: hub_customer

## Business Key
customer_id - Unique customer identifier from CRM

## Sources
- CRM System (primary)
- E-commerce Platform
- Mobile App

## Hash Key Generation
SHA-256(UPPER(TRIM(customer_id)))

## Notes
- Business key is stable, never changes
- Multiple sources may provide same customer

Link Tables

Purpose

Links represent relationships between business concepts (hubs).

Link Types

Link Structure

Link Example

CREATE TABLE link_order (
    link_order_hk BINARY(32) NOT NULL PRIMARY KEY,
    hub_customer_hk BINARY(32) NOT NULL,
    hub_product_hk BINARY(32) NOT NULL,
    hub_store_hk BINARY(32) NOT NULL,
    order_bk VARCHAR(50) NOT NULL,  -- Degenerate key
    load_dts DATETIME2 NOT NULL,
    record_source VARCHAR(100) NOT NULL,

    CONSTRAINT FK_link_order_customer
        FOREIGN KEY (hub_customer_hk)
        REFERENCES hub_customer(hub_customer_hk),
    CONSTRAINT FK_link_order_product
        FOREIGN KEY (hub_product_hk)
        REFERENCES hub_product(hub_product_hk)
);

Satellite Tables

Purpose

Satellites store descriptive attributes and track history through delta detection.

Satellite Types

Satellite Structure

Satellite Example

CREATE TABLE sat_customer_details (
    hub_customer_hk BINARY(32) NOT NULL,
    load_dts DATETIME2 NOT NULL,
    load_end_dts DATETIME2,
    hash_diff BINARY(32) NOT NULL,
    -- Attributes
    customer_name VARCHAR(100),
    email VARCHAR(255),
    phone VARCHAR(20),
    address_line1 VARCHAR(200),
    city VARCHAR(100),
    state VARCHAR(50),
    postal_code VARCHAR(20),
    country VARCHAR(50),
    -- Audit
    record_source VARCHAR(100) NOT NULL,

    PRIMARY KEY (hub_customer_hk, load_dts),
    CONSTRAINT FK_sat_customer_hub
        FOREIGN KEY (hub_customer_hk)
        REFERENCES hub_customer(hub_customer_hk)
);

Hash Key Generation

Standard Hash Function

public static class DataVaultHasher
{
    public static byte[] GenerateHashKey(params string[] businessKeys)
    {
        var concatenated = string.Join("|",
            businessKeys.Select(k =>
                k?.Trim().ToUpperInvariant() ?? string.Empty));

        using var sha256 = SHA256.Create();
        return sha256.ComputeHash(Encoding.UTF8.GetBytes(concatenated));
    }

    public static byte[] GenerateHashDiff(object entity)
    {
        var json = JsonSerializer.Serialize(entity,
            new JsonSerializerOptions { PropertyNamingPolicy = null });

        using var sha256 = SHA256.Create();
        return sha256.ComputeHash(Encoding.UTF8.GetBytes(json));
    }
}

Data Vault Architecture Layers

┌─────────────────────────────────────────────────────────────────┐
│                        INFORMATION MART                          │
│         (Dimensional models, aggregates, reports)                │
└─────────────────────────────────────────────────────────────────┘
                              ▲
┌─────────────────────────────────────────────────────────────────┐
│                        BUSINESS VAULT                            │
│    (Derived data, business rules, calculated attributes)        │
└─────────────────────────────────────────────────────────────────┘
                              ▲
┌─────────────────────────────────────────────────────────────────┐
│                          RAW VAULT                               │
│          (Hubs, Links, Satellites - source-aligned)             │
└─────────────────────────────────────────────────────────────────┘
                              ▲
┌─────────────────────────────────────────────────────────────────┐
│                        STAGING AREA                              │
│             (Transient, source-aligned extracts)                │
└─────────────────────────────────────────────────────────────────┘

Point-in-Time (PIT) Tables

Purpose

PIT tables optimize queries by pre-joining satellite data at specific points in time.

CREATE TABLE pit_customer (
    pit_customer_hk BINARY(32) NOT NULL,
    snapshot_dts DATETIME2 NOT NULL,
    hub_customer_hk BINARY(32) NOT NULL,

    -- Satellite load dates (for joins)
    sat_customer_details_load_dts DATETIME2,
    sat_customer_loyalty_load_dts DATETIME2,
    sat_customer_preferences_load_dts DATETIME2,

    PRIMARY KEY (pit_customer_hk),
    CONSTRAINT FK_pit_customer_hub
        FOREIGN KEY (hub_customer_hk)
        REFERENCES hub_customer(hub_customer_hk)
);

Bridge Tables

Purpose

Bridge tables resolve many-to-many relationships for easier querying.

CREATE TABLE bridge_customer_product (
    bridge_hk BINARY(32) NOT NULL PRIMARY KEY,
    hub_customer_hk BINARY(32) NOT NULL,
    hub_product_hk BINARY(32) NOT NULL,
    snapshot_dts DATETIME2 NOT NULL,
    order_count INT,
    total_revenue DECIMAL(18,2),
    first_order_dts DATETIME2,
    last_order_dts DATETIME2
);

Mermaid ER Diagram

erDiagram
    HUB_CUSTOMER {
        binary hub_customer_hk PK
        varchar customer_bk UK
        datetime2 load_dts
        varchar record_source
    }

    HUB_PRODUCT {
        binary hub_product_hk PK
        varchar product_bk UK
        datetime2 load_dts
        varchar record_source
    }

    LINK_ORDER {
        binary link_order_hk PK
        binary hub_customer_hk FK
        binary hub_product_hk FK
        varchar order_bk
        datetime2 load_dts
        varchar record_source
    }

    SAT_CUSTOMER_DETAILS {
        binary hub_customer_hk FK
        datetime2 load_dts PK
        binary hash_diff
        varchar customer_name
        varchar email
        varchar record_source
    }

    SAT_ORDER_DETAILS {
        binary link_order_hk FK
        datetime2 load_dts PK
        binary hash_diff
        decimal quantity
        decimal unit_price
        varchar record_source
    }

    HUB_CUSTOMER ||--o{ SAT_CUSTOMER_DETAILS : "has history"
    HUB_CUSTOMER ||--o{ LINK_ORDER : "places"
    HUB_PRODUCT ||--o{ LINK_ORDER : "ordered in"
    LINK_ORDER ||--o{ SAT_ORDER_DETAILS : "has details"

Loading Patterns

Hub Loading

public async Task LoadHub(
    IEnumerable<SourceCustomer> customers,
    CancellationToken ct)
{
    foreach (var batch in customers.Chunk(1000))
    {
        var hubRecords = batch.Select(c => new HubCustomer
        {
            HubCustomerHk = DataVaultHasher.GenerateHashKey(c.CustomerId),
            CustomerBk = c.CustomerId,
            LoadDts = DateTime.UtcNow,
            RecordSource = "CRM"
        });

        await _context.BulkMergeAsync(hubRecords,
            options => options.ColumnPrimaryKeyExpression = x => x.HubCustomerHk,
            ct);
    }
}

Satellite Delta Detection

public async Task LoadSatellite(
    IEnumerable<SourceCustomer> customers,
    CancellationToken ct)
{
    foreach (var customer in customers)
    {
        var hubHk = DataVaultHasher.GenerateHashKey(customer.CustomerId);
        var hashDiff = DataVaultHasher.GenerateHashDiff(customer);

        // Check if changed
        var current = await _context.SatCustomerDetails
            .Where(s => s.HubCustomerHk == hubHk && s.LoadEndDts == null)
            .FirstOrDefaultAsync(ct);

        if (current == null || !current.HashDiff.SequenceEqual(hashDiff))
        {
            // Close current record
            if (current != null)
            {
                current.LoadEndDts = DateTime.UtcNow;
            }

            // Insert new record
            _context.SatCustomerDetails.Add(new SatCustomerDetails
            {
                HubCustomerHk = hubHk,
                LoadDts = DateTime.UtcNow,
                HashDiff = hashDiff,
                CustomerName = customer.Name,
                Email = customer.Email,
                RecordSource = "CRM"
            });
        }
    }

    await _context.SaveChangesAsync(ct);
}

Naming Conventions

Validation Checklist

• Business keys identified for all hubs
• Links capture all significant relationships
• Satellites track appropriate change granularity
• Hash keys use consistent algorithm (SHA-256)
• Hash diff implemented for delta detection
• Load dates populated consistently
• Record source tracked for auditability
• Naming conventions applied consistently

Integration Points

Inputs from:

• conceptual-modeling skill → Business entities
• er-modeling skill → Source relationships
• Source system analysis → Business keys

Outputs to:

• dimensional-modeling skill → Information marts
• migration-planning skill → ETL/ELT design
• schema-design skill → Physical implementation