Snowflake Data Vault Modeling

Data Vault 2.0 is a methodology for designing scalable, auditable, and flexible data warehouses that support historical tracking and parallel loading.

Data Vault 2.0 Methodology

Data Vault 2.0 was designed for enterprise data warehouses where auditability, flexibility, and scalability are paramount. The methodology separates business keys (Hubs), relationships (Links), and descriptive attributes (Satellites), enabling parallel loading from multiple source systems without contention.

Core Principles

Business Keys First: Identify business keys before modeling relationships
Separation of Concerns: Hubs, Links, and Satellites are independently loadable
Hash Keys for Performance: SHA-256 hash keys replace long natural keys for join performance
Immutable Inserts Only: Data Vault tables are append-only; updates are never performed
Full Historical Tracking: Every change is captured as a new row with a load timestamp
Record Source Tracking: Every row records which source system loaded it

Hash Key Generation

-- Generate hash keys using SHA-256
-- Hub hash key: hash of business key(s)
CREATE OR REPLACE FUNCTION hash_key(bk1 VARCHAR, bk2 VARCHAR DEFAULT NULL)
RETURNS VARCHAR(64)
LANGUAGE SQL
AS
$$
  SHA2(
    CASE
      WHEN bk2 IS NULL THEN TRIM(bk1)
      ELSE CONCAT(TRIM(bk1), '||', TRIM(bk2))
    END,
    256
  )
$$;

-- Hash diff: hash of all descriptive attributes for change detection
CREATE OR REPLACE FUNCTION hash_diff(
  attr1 VARCHAR, attr2 VARCHAR, attr3 VARCHAR, attr4 VARCHAR DEFAULT NULL
)
RETURNS VARCHAR(64)
LANGUAGE SQL
AS
$$
  SHA2(
    CONCAT_WS('||',
      COALESCE(TRIM(attr1), ''),
      COALESCE(TRIM(attr2), ''),
      COALESCE(TRIM(attr3), ''),
      COALESCE(TRIM(attr4), '')
    ),
    256
  )
$$;

Creating Hub Tables

-- Hub: Customer
CREATE OR REPLACE TABLE HUB_CUSTOMER (
  HK_CUSTOMER      VARCHAR(64) PRIMARY KEY,
  BK_CUSTOMER_ID   VARCHAR(50) NOT NULL,
  LOAD_DATE        TIMESTAMP_NTZ NOT NULL DEFAULT CURRENT_TIMESTAMP(),
  RECORD_SOURCE    VARCHAR(100) NOT NULL
);

-- Hub: Order
CREATE OR REPLACE TABLE HUB_ORDER (
  HK_ORDER         VARCHAR(64) PRIMARY KEY,
  BK_ORDER_ID      VARCHAR(50) NOT NULL,
  LOAD_DATE        TIMESTAMP_NTZ NOT NULL DEFAULT CURRENT_TIMESTAMP(),
  RECORD_SOURCE    VARCHAR(100) NOT NULL
);

-- Hub: Product
CREATE OR REPLACE TABLE HUB_PRODUCT (
  HK_PRODUCT       VARCHAR(64) PRIMARY KEY,
  BK_PRODUCT_ID    VARCHAR(50) NOT NULL,
  LOAD_DATE        TIMESTAMP_NTZ NOT NULL DEFAULT CURRENT_TIMESTAMP(),
  RECORD_SOURCE    VARCHAR(100) NOT NULL
);

-- Insert into Hub from staging
INSERT INTO HUB_CUSTOMER (HK_CUSTOMER, BK_CUSTOMER_ID, LOAD_DATE, RECORD_SOURCE)
SELECT DISTINCT
  SHA2(TRIM(customer_id), 256) AS HK_CUSTOMER,
  TRIM(customer_id) AS BK_CUSTOMER_ID,
  CURRENT_TIMESTAMP() AS LOAD_DATE,
  'SOURCE_SYSTEM_A' AS RECORD_SOURCE
FROM STG.CUSTOMERS
WHERE customer_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM HUB_CUSTOMER h
    WHERE h.BK_CUSTOMER_ID = TRIM(STG.CUSTOMERS.customer_id)
  );

Creating Link Tables

-- Link: Order-Customer relationship
CREATE OR REPLACE TABLE LINK_ORDER_CUSTOMER (
  HK_LINK_ORDER_CUSTOMER  VARCHAR(64) PRIMARY KEY,
  HK_ORDER                VARCHAR(64) NOT NULL,
  HK_CUSTOMER             VARCHAR(64) NOT NULL,
  LOAD_DATE               TIMESTAMP_NTZ NOT NULL DEFAULT CURRENT_TIMESTAMP(),
  RECORD_SOURCE           VARCHAR(100) NOT NULL,
  FOREIGN KEY (HK_ORDER) REFERENCES HUB_ORDER(HK_ORDER),
  FOREIGN KEY (HK_CUSTOMER) REFERENCES HUB_CUSTOMER(HK_CUSTOMER)
);

-- Link: Order-Product (transaction line)
CREATE OR REPLACE TABLE LINK_ORDER_PRODUCT (
  HK_LINK_ORDER_PRODUCT   VARCHAR(64) PRIMARY KEY,
  HK_ORDER                VARCHAR(64) NOT NULL,
  HK_PRODUCT              VARCHAR(64) NOT NULL,
  LOAD_DATE               TIMESTAMP_NTZ NOT NULL DEFAULT CURRENT_TIMESTAMP(),
  RECORD_SOURCE           VARCHAR(100) NOT NULL,
  FOREIGN KEY (HK_ORDER) REFERENCES HUB_ORDER(HK_ORDER),
  FOREIGN KEY (HK_PRODUCT) REFERENCES HUB_PRODUCT(HK_PRODUCT)
);

-- Insert into Link from staging
INSERT INTO LINK_ORDER_CUSTOMER (HK_LINK_ORDER_CUSTOMER, HK_ORDER, HK_CUSTOMER, LOAD_DATE, RECORD_SOURCE)
SELECT DISTINCT
  SHA2(CONCAT(o.BK_ORDER_ID, '||', c.BK_CUSTOMER_ID), 256) AS HK_LINK_ORDER_CUSTOMER,
  o.HK_ORDER,
  c.HK_CUSTOMER,
  CURRENT_TIMESTAMP() AS LOAD_DATE,
  'SOURCE_SYSTEM_A' AS RECORD_SOURCE
FROM STG.ORDERS s
JOIN HUB_ORDER o ON o.BK_ORDER_ID = s.order_id
JOIN HUB_CUSTOMER c ON c.BK_CUSTOMER_ID = s.customer_id
WHERE NOT EXISTS (
  SELECT 1 FROM LINK_ORDER_CUSTOMER l
  WHERE l.HK_ORDER = o.HK_ORDER
    AND l.HK_CUSTOMER = c.HK_CUSTOMER
);

Creating Satellite Tables

-- Satellite: Customer descriptive attributes
CREATE OR REPLACE TABLE SAT_CUSTOMER_DETAILS (
  HK_CUSTOMER        VARCHAR(64) NOT NULL,
  LOAD_DATE          TIMESTAMP_NTZ NOT NULL,
  LOAD_END_DATE      TIMESTAMP_NTZ DEFAULT NULL,
  HASH_DIFF          VARCHAR(64) NOT NULL,
  CUSTOMER_NAME      VARCHAR(200),
  EMAIL              VARCHAR(500),
  PHONE              VARCHAR(50),
  ADDRESS            VARCHAR(500),
  CITY               VARCHAR(100),
  STATE              VARCHAR(50),
  COUNTRY            VARCHAR(100),
  RECORD_SOURCE      VARCHAR(100) NOT NULL,
  PRIMARY KEY (HK_CUSTOMER, LOAD_DATE),
  FOREIGN KEY (HK_CUSTOMER) REFERENCES HUB_CUSTOMER(HK_CUSTOMER)
);

-- Satellite: Order attributes
CREATE OR REPLACE TABLE SAT_ORDER_ATTRIBUTES (
  HK_ORDER           VARCHAR(64) NOT NULL,
  LOAD_DATE          TIMESTAMP_NTZ NOT NULL,
  LOAD_END_DATE      TIMESTAMP_NTZ DEFAULT NULL,
  HASH_DIFF          VARCHAR(64) NOT NULL,
  ORDER_DATE         DATE,
  ORDER_STATUS       VARCHAR(50),
  ORDER_TOTAL        DECIMAL(18,2),
  CURRENCY_CODE      VARCHAR(3),
  SHIPPING_METHOD    VARCHAR(100),
  RECORD_SOURCE      VARCHAR(100) NOT NULL,
  PRIMARY KEY (HK_ORDER, LOAD_DATE),
  FOREIGN KEY (HK_ORDER) REFERENCES HUB_ORDER(HK_ORDER)
);

-- Insert into Satellite with SCD Type 2 logic
INSERT INTO SAT_CUSTOMER_DETAILS (
  HK_CUSTOMER, LOAD_DATE, LOAD_END_DATE, HASH_DIFF,
  CUSTOMER_NAME, EMAIL, PHONE, ADDRESS, CITY, STATE, COUNTRY,
  RECORD_SOURCE
)
SELECT
  h.HK_CUSTOMER,
  CURRENT_TIMESTAMP() AS LOAD_DATE,
  NULL AS LOAD_END_DATE,
  SHA2(CONCAT_WS('||',
    COALESCE(s.customer_name, ''),
    COALESCE(s.email, ''),
    COALESCE(s.phone, '')
  ), 256) AS HASH_DIFF,
  s.customer_name,
  s.email,
  s.phone,
  s.address,
  s.city,
  s.state,
  s.country,
  'SOURCE_SYSTEM_A' AS RECORD_SOURCE
FROM STG.CUSTOMERS s
JOIN HUB_CUSTOMER h ON h.BK_CUSTOMER_ID = s.customer_id
WHERE NOT EXISTS (
  SELECT 1 FROM SAT_CUSTOMER_DETAILS sat
  WHERE sat.HK_CUSTOMER = h.HK_CUSTOMER
    AND sat.HASH_DIFF = SHA2(CONCAT_WS('||',
      COALESCE(s.customer_name, ''),
      COALESCE(s.email, ''),
      COALESCE(s.phone, '')
    ), 256)
    AND sat.LOAD_END_DATE IS NULL
);

Data Vault vs Star Schema vs 3NF Comparison

Aspect	Data Vault 2.0	Star Schema	3rd Normal Form
Design goal	Auditability, flexibility, scalability	Query performance, simplicity	Minimal redundancy, normalization
Table types	Hub, Link, Satellite	Fact, Dimension	Normalized entities
Relationships	Many-to-many via Links	Star (facts reference dimensions)	Foreign keys
Historical tracking	Native (Satellites)	SCD Type 1/2 in dimensions	Trigger-based or temporal
Loading pattern	Append-only, parallel	Upsert/merge on dimensions	Upsert/merge
Query complexity	High (many joins)	Low (star joins)	Medium (multiple joins)
Schema evolution	Easy (add Satellites)	Moderate (add columns/tables)	Difficult (restructure)
Best for	Enterprise DW, compliance	BI reporting, dashboards	OLTP, operational systems
Performance tuning	Materialized views, marts	Indexes, partitioning	Indexes, normalization

When to Use Data Vault in Snowflake

Use Case	Data Vault?	Reason
Multiple source systems	Yes	Hubs and Links support multi-source integration
Regulatory compliance (audit trail)	Yes	Immutable inserts track every change
Frequent schema changes	Yes	Add Satellites without impacting existing tables
Real-time ingestion	Yes	Parallel loading without table locks
Simple BI reporting	No	Star schema is simpler and faster for queries
Small data warehouse	No	Overhead not justified for small datasets
OLTP workload	No	Data Vault is for analytical workloads
Quick prototyping	No	Star schema is faster to implement

Best Practices for Snowflake Data Vault

Practice	Description
Use TRANSIENT tables for staging	Reduce storage costs for intermediate data
Cluster Hubs on hash key	Snowflake clustering key on HK for fast lookups
Cluster Satellites on load_date	Efficient time-based queries and Time Travel
Create views for analytics	Build Star Schema views over Data Vault for BI tools
Use Streams for incremental loading	Track changes in source systems for CDC
Implement record source tracking	Always populate RECORD_SOURCE column
Use Snowflake Tasks for orchestration	Automate loading pipeline with Tasks
Leverage Time Travel	Data Vault + Time Travel provides full audit capability

Snowflake Data Vault Modeling

Snowflake Data Vault Modeling

Data Vault 2.0 Methodology

Core Principles

Hash Key Generation

Creating Hub Tables

Creating Link Tables

Creating Satellite Tables

Data Vault vs Star Schema vs 3NF Comparison

When to Use Data Vault in Snowflake

Best Practices for Snowflake Data Vault

See Also

Need Expert Snowflake Help?