How to create a Slowly Changing Dimension Type 4 in Data Modeling (Kimball)

João Barros 03 de October de 2026 5 min read

This task shows how to create a Slowly Changing Dimension Type 4 in Data Modeling (Kimball) — a dimension that keeps the current version and stores the full history in a separate table. It is useful when you want fast queries on current state while preserving the entire history for auditing and time-based analysis.

Prerequisites

Basic knowledge of SQL (SELECT, INSERT, UPDATE, JOIN).
A database environment for testing (for example SQL Server, PostgreSQL or Azure SQL).
Sample data about entities that change over time (e.g., customers with address and status).

Step 1: Understand what an SCD Type 4 is

A Slowly Changing Dimension (SCD) Type 4 separates history into a history table and keeps only the current version in the main dimension. The advantage: read queries on current state are fast and simple, while the history remains available for detailed analysis without complicating the main dimension.

Step 2: Define the table model

We will create two tables: Dimension_Current (only current records) and Dimension_History (all historical versions). Dimension_History will have a surrogate key, the natural key of the entity, and validity fields (valid_from, valid_to). Dimension_Current has the same natural key and current attributes.

-- Exemplo em SQL (compatível com SQL Server/Postgres com pequenas adaptações)
CREATE TABLE Dimension_Current (
  customer_id INT PRIMARY KEY,      -- chave natural
  name VARCHAR(200),
  address VARCHAR(300),
  status VARCHAR(50),
  last_updated TIMESTAMP
);

CREATE TABLE Dimension_History (
  history_id SERIAL PRIMARY KEY,    -- chave surrogate
  customer_id INT,                  -- chave natural
  name VARCHAR(200),
  address VARCHAR(300),
  status VARCHAR(50),
  valid_from TIMESTAMP,
  valid_to TIMESTAMP                -- NULL significa ainda válido antes de mover para current
);

Step 3: Initial load of data

Populate Dimension_Current with the present state and Dimension_History with an initial record that covers from the beginning until NULL (active in the present).

-- Supõe que temos uma staging table Stg_Customers com o estado actual
INSERT INTO Dimension_Current (customer_id, name, address, status, last_updated)
SELECT customer_id, name, address, status, now()
FROM Stg_Customers;

INSERT INTO Dimension_History (customer_id, name, address, status, valid_from, valid_to)
SELECT customer_id, name, address, status, now(), NULL
FROM Stg_Customers;

Step 4: ETL process to detect changes and update SCD Type 4

The ETL flow compares Stg_Customers with Dimension_Current. For each record that changed, close the period in Dimension_History (set valid_to) and insert a new history record with current valid_from; update Dimension_Current with the new attributes.

-- Exemplo transaccional simplificado
BEGIN;

-- 1) Fechar versões antigas na history para clientes que mudaram
UPDATE Dimension_History h
SET valid_to = now()
FROM Dimension_Current c
JOIN Stg_Customers s ON s.customer_id = c.customer_id
WHERE h.customer_id = c.customer_id
  AND h.valid_to IS NULL
  AND (c.name <> s.name OR c.address <> s.address OR c.status <> s.status);

-- 2) Inserir nova versão na history
INSERT INTO Dimension_History (customer_id, name, address, status, valid_from, valid_to)
SELECT s.customer_id, s.name, s.address, s.status, now(), NULL
FROM Stg_Customers s
JOIN Dimension_Current c ON s.customer_id = c.customer_id
WHERE (c.name <> s.name OR c.address <> s.address OR c.status <> s.status);

-- 3) Actualizar a dimensão actual
UPDATE Dimension_Current c
SET name = s.name,
    address = s.address,
    status = s.status,
    last_updated = now()
FROM Stg_Customers s
WHERE c.customer_id = s.customer_id
  AND (c.name <> s.name OR c.address <> s.address OR c.status <> s.status);

-- 4) Tratar novos clientes que não existem na Current
INSERT INTO Dimension_Current (customer_id, name, address, status, last_updated)
SELECT s.customer_id, s.name, s.address, s.status, now()
FROM Stg_Customers s
LEFT JOIN Dimension_Current c ON s.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

INSERT INTO Dimension_History (customer_id, name, address, status, valid_from, valid_to)
SELECT s.customer_id, s.name, s.address, s.status, now(), NULL
FROM Stg_Customers s
LEFT JOIN Dimension_History h ON s.customer_id = h.customer_id AND h.valid_to IS NULL
WHERE h.history_id IS NULL;

COMMIT;

Step 5: Common queries (examples)

Useful examples: get the current state (simple) and reconstruct the state at a past date (use Dimension_History).

-- Estado actual (simples)
SELECT * FROM Dimension_Current WHERE customer_id = 123;

-- Estado de um cliente numa data histórica
SELECT *
FROM Dimension_History
WHERE customer_id = 123
  AND valid_from <= '2025-01-15'::timestamp
  AND (valid_to IS NULL OR valid_to > '2025-01-15'::timestamp);

Verify the result

Confirm that Dimension_Current reflects the current values and that Dimension_History has entries with coherent valid_from/valid_to. Check change cases: after the ETL, the old record in history should have valid_to filled and there should be a new row with valid_to NULL; Current should show the new value.

Conclusion

SCD Type 4 clearly separates current state and history, simplifying queries and keeping full history. Next steps: automate the ETL process with jobs (e.g., Azure Data Factory or SQL Agent), add quality controls and manage large volumes with partitioning in Dimension_History. Tip: start with a subset of customers to test and validate behavior before applying in production.