How to build a simplified Data Vault 2.0 in a Data Warehouse

João Barros 28 de August de 2026 5 min read

This tutorial shows how to build a simplified Data Vault 2.0 model in a Data Warehouse to record history and integrations from multiple sources. Data Vault is useful when we need scalability, auditability and ease of integration without losing history.

Prerequisites

Basic SQL knowledge (SELECT, INSERT, MERGE).
Data Warehouse environment with transaction support (e.g.: Azure SQL, SQL Server, PostgreSQL).
Exposure to ETL/ELT concepts and dimensional modeling.

Step 1: Understand the elements of the Data Vault

Before coding, it is important to know what we are going to create: Hub (unique entities, business keys), Link (relationships between hubs) and Satellite (historical attributes and metadata). This allows separating integration logic from history logic.

Step 2: Create physical tables for Hub, Link and Satellite

We create minimal schemas with essential columns: business_key in the Hub, optional hash keys, and metadata (load_date, record_source). The example below uses generic SQL syntax; adjust types and indexes to your RDBMS.

-- Hub Cliente
CREATE TABLE hub_cliente (
  cliente_hk BIGINT IDENTITY PRIMARY KEY,
  cliente_bk VARCHAR(100) NOT NULL UNIQUE,
  record_source VARCHAR(100),
  load_date DATETIME DEFAULT GETDATE()
);

-- Link Compra (relaciona Cliente a Produto)
CREATE TABLE link_compra (
  compra_hk BIGINT IDENTITY PRIMARY KEY,
  cliente_bk VARCHAR(100) NOT NULL,
  produto_bk VARCHAR(100) NOT NULL,
  record_source VARCHAR(100),
  load_date DATETIME DEFAULT GETDATE()
);

-- Satellite Cliente (atributos historizados)
CREATE TABLE sat_cliente (
  sat_cliente_hk BIGINT IDENTITY PRIMARY KEY,
  cliente_bk VARCHAR(100) NOT NULL,
  nome VARCHAR(200),
  morada VARCHAR(300),
  valid_from DATETIME DEFAULT GETDATE(),
  valid_to DATETIME NULL,
  record_source VARCHAR(100)
);

Step 3: Load and deduplicate business keys into the Hub

When loading source data, we extract the business_key (e.g.: customer_id). We insert only new keys into the Hub to maintain uniqueness and auditability.

-- Exemplo de carga incremental para Hub (SQL Server)
MERGE INTO hub_cliente AS target
USING (
  SELECT DISTINCT customer_id AS cliente_bk, 'sistema_vendas' AS record_source
  FROM staging_vendas
) AS source
ON target.cliente_bk = source.cliente_bk
WHEN NOT MATCHED THEN
  INSERT (cliente_bk, record_source) VALUES (source.cliente_bk, source.record_source);

Step 4: Create/update Satellites for historization

Satellites store attributes and allow recording changes over time. A common technique is to compare a hash of attributes or compare field by field and close the previous record when a change occurs.

-- Inserir novo registo no Satellite quando houver mudança (exemplo conceptual)
INSERT INTO sat_cliente (cliente_bk, nome, morada, valid_from, record_source)
SELECT s.customer_id, s.name, s.address, GETDATE(), 'sistema_vendas'
FROM staging_vendas s
LEFT JOIN (
  SELECT cliente_bk, nome, morada FROM sat_cliente WHERE valid_to IS NULL
) cur ON cur.cliente_bk = s.customer_id
WHERE cur.nome IS NULL OR cur.nome <> s.name OR cur.morada <> s.address;

-- Encerrar versão anterior
UPDATE sat_cliente
SET valid_to = GETDATE()
FROM sat_cliente sc
JOIN staging_vendas s ON sc.cliente_bk = s.customer_id
WHERE sc.valid_to IS NULL AND (sc.nome <> s.name OR sc.morada <> s.address);

Step 5: Load Links to represent relationships

Links use the business keys from the Hubs and represent transactions or associations. Load only new relationships or those with context changes (e.g.: quantity, price in the Satellite associated with the Link).

-- Inserir relações únicas no Link
MERGE INTO link_compra AS target
USING (
  SELECT DISTINCT customer_id AS cliente_bk, product_id AS produto_bk, 'sistema_vendas' AS record_source
  FROM staging_vendas
) AS source
ON target.cliente_bk = source.cliente_bk AND target.produto_bk = source.produto_bk
WHEN NOT MATCHED THEN
  INSERT (cliente_bk, produto_bk, record_source) VALUES (source.cliente_bk, source.produto_bk, source.record_source);

Step 6: Record metadata and auditing

Include record_source, load_date and, when possible, attrs_hash for fast change detection. Storing metadata facilitates traceability and audit of the loads.

-- Exemplo de coluna de hash para Satellite (funcionalidade DB depende do SGBD)
ALTER TABLE sat_cliente ADD attrs_hash VARCHAR(64);

-- Calcular hash simplificado (ex.: HASHBYTES no SQL Server)
UPDATE sat_cliente
SET attrs_hash = CONVERT(VARCHAR(64), HASHBYTES('SHA2_256', ISNULL(nome,'') + '|' + ISNULL(morada,'')), 2)
WHERE attrs_hash IS NULL;

Verify the result

Confirm that: (1) the Hub contains only unique business_keys; (2) the Satellites have versions with valid_from/valid_to and historical records; (3) the Links represent the expected relationships. Use simple queries to validate counts and sample changes.

-- Verificações rápidas
SELECT COUNT(*) AS hubs_totais FROM hub_cliente;
SELECT cliente_bk, COUNT(*) AS versoes FROM sat_cliente GROUP BY cliente_bk HAVING COUNT(*) > 1;
SELECT COUNT(*) AS links_totais FROM link_compra;

Conclusion

With a simplified Data Vault 2.0 you have a foundation to integrate multiple sources with history and auditability. Next steps: automate loads with ETL/ELT pipelines, add data quality tests and optimize indexes/partitions. Tip: start with a small set of entities and validate the process before scaling.