Skip to main content

Data Vault 2.0 Basics (Hubs, Links, Satellites)

While Star Schemas (Dimensional Modeling) are excellent for querying, they are notoriously difficult to update when business rules change. If two massive banks merge, combining their Star Schemas is a multi-year nightmare.

Data Vault 2.0 solves this. It is a highly agile modeling methodology designed specifically for enterprise-scale integration. Instead of forcing data into a rigid structure, Data Vault breaks data down into its core components: Hubs, Links, and Satellites.

Topics Covered​

  • The Data Vault Philosophy
  • Hubs (Entities)
  • Links (Relationships)
  • Satellites (Attributes & History)
  • Data Vault vs Star Schema

1. The Data Vault Philosophy​

The core philosophy of Data Vault is: "100% of the data, 100% of the time."

Unlike the Silver layer of a Medallion Architecture where you might discard bad data, Data Vault stores everything. It decouples the business entities from their descriptive attributes, allowing multiple source systems to feed the same entity independently.

Data Vault uses MD5 or SHA-1 hashes for primary keys instead of sequences. This allows parallel loading—you don't have to wait for the Customer table to generate an ID before you load the Orders table.

2. Hubs (Entities)​

A Hub represents a core business entity (e.g., Customer, Store, Product). It contains no descriptive data, only the Business Key (the Natural Key) and its MD5 Hash.

(Run this in your Snowflake worksheet to see how hashes are generated. Verify your role with SELECT CURRENT_ROLE();)

USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS BEEMS_VAULT_DB;
USE SCHEMA BEEMS_VAULT_DB.PUBLIC;

-- Create the Customer Hub
CREATE OR REPLACE TABLE HUB_CUSTOMER (
CUSTOMER_HASH_KEY BINARY(16), -- MD5 Hash of the Customer ID
CUSTOMER_ID STRING, -- The Natural Business Key
LOAD_DATE TIMESTAMP, -- When this key was first seen
RECORD_SOURCE STRING -- Where this key came from
);

-- Insert a new Customer from the CRM system
INSERT INTO HUB_CUSTOMER
VALUES (MD5_BINARY('C-101'), 'C-101', CURRENT_TIMESTAMP(), 'CRM_SYSTEM');

A Link represents a relationship or transaction between two or more Hubs. It contains the hashes of the Hubs it connects, plus its own unique hash.

Like a Hub, a Link contains no descriptive data.

-- Create the Order Hub
CREATE OR REPLACE TABLE HUB_ORDER (
ORDER_HASH_KEY BINARY(16),
ORDER_ID STRING,
LOAD_DATE TIMESTAMP,
RECORD_SOURCE STRING
);

INSERT INTO HUB_ORDER VALUES (MD5_BINARY('ORD-999'), 'ORD-999', CURRENT_TIMESTAMP(), 'ERP_SYSTEM');

-- Create the Link connecting the Customer to the Order
CREATE OR REPLACE TABLE LINK_CUSTOMER_ORDER (
LNK_CUST_ORD_HASH_KEY BINARY(16), -- Hash of (Customer ID + Order ID)
CUSTOMER_HASH_KEY BINARY(16), -- Points to HUB_CUSTOMER
ORDER_HASH_KEY BINARY(16), -- Points to HUB_ORDER
LOAD_DATE TIMESTAMP,
RECORD_SOURCE STRING
);

INSERT INTO LINK_CUSTOMER_ORDER
VALUES (MD5_BINARY('C-101ORD-999'), MD5_BINARY('C-101'), MD5_BINARY('ORD-999'), CURRENT_TIMESTAMP(), 'ERP_SYSTEM');

4. Satellites (Attributes & History)​

A Satellite contains the descriptive attributes for a Hub or a Link. This is where the actual data lives (Names, Cities, Amounts, Statuses).

A Satellite automatically tracks history, functioning similarly to an SCD Type 2 dimension. If a customer's name changes, you insert a new row into the Satellite. You never update Data Vault tables; it is strictly an Insert-Only architecture.

-- Create the Satellite for Customer attributes
CREATE OR REPLACE TABLE SAT_CUSTOMER_CRM (
CUSTOMER_HASH_KEY BINARY(16), -- Points to HUB_CUSTOMER
LOAD_DATE TIMESTAMP, -- When this attribute state was recorded
CUSTOMER_HASH_DIFF BINARY(16),-- Hash of all attributes (used to detect changes)
FIRST_NAME STRING,
CITY STRING,
RECORD_SOURCE STRING
);

-- Insert the initial state (Arjun in Bengaluru)
INSERT INTO SAT_CUSTOMER_CRM
VALUES (MD5_BINARY('C-101'), CURRENT_TIMESTAMP(), MD5_BINARY('ArjunBengaluru'), 'Arjun', 'Bengaluru', 'CRM_SYSTEM');

-- Arjun moves to Chennai. We simply insert a new row!
INSERT INTO SAT_CUSTOMER_CRM
VALUES (MD5_BINARY('C-101'), CURRENT_TIMESTAMP(), MD5_BINARY('ArjunChennai'), 'Arjun', 'Chennai', 'CRM_SYSTEM');

Because Satellites are separated by source system, if an Indian retail bank merges with a foreign bank, they simply create a new SAT_CUSTOMER_FOREIGN_CRM and point it to the existing HUB_CUSTOMER. The models do not break!

5. Data Vault vs Star Schema​

Do not expose a raw Data Vault to business analysts.

Querying a Data Vault requires massive, complex joins across dozens of Hubs, Links, and Satellites. An analyst using Tableau will not understand it.

Instead, Data Vault is used as the Enterprise Data Warehouse (the "Silver" layer). You then use Snowflake Views (or Dynamic Tables) to join the Hubs, Links, and Satellites back together, projecting them into a standard Star Schema for the "Gold" layer.

Summary​

  • Data Vault 2.0 is an agile modeling technique for massive enterprise integration.
  • Hubs store unique Business Keys.
  • Links store relationships between Hubs.
  • Satellites store descriptive attributes and track history (Insert-Only).
  • Use Hashes (e.g., MD5) for keys to enable parallel loading.
  • Always project the Vault into a Star Schema for final BI consumption.