How to implement SCD Type 2 in ELT: step by step

Keeping the history of a customer's changes — such as a new address or a different segment — is essential for reliable analysis over time. The SCD Type 2 (Slowly Changing Dimension) pattern solves this: it creates a new version of the record whenever an attribute changes, instead of overwriting the old value. In an ELT flow, the data first lands in the staging layer and is then transformed with SQL right inside the data warehouse.

Prerequisites

• Access to a data warehouse with SQL (SQL Server, Azure Synapse, Snowflake, Databricks or BigQuery).
• A staging table with the current source data (for example, stg_clientes).
• Basic SQL knowledge: INSERT, UPDATE and JOIN.
• Permissions to create and alter tables in the target.

Step 1: Understand what an SCD Type 2 dimension is

An SCD Type 2 dimension keeps several rows for the same entity — a customer, for instance — one for each period during which its data was valid. Three control columns make this possible: valid_from (start of validity), valid_to (end of validity) and is_current (marks the active version). This way, a query can tell which segment a customer belonged to on any past date, something impossible if you only stored the latest value.

Step 2: Create the dimension table with history columns

Besides the business attributes (name, segment), the final table needs a surrogate key and the three control columns. The cliente_id column holds the business key that links every version to the same real customer. The examples use SQL Server syntax; the logic is the same in other warehouses.

CREATE TABLE dim_cliente (
    cliente_sk INT IDENTITY(1,1) PRIMARY KEY,
    cliente_id INT NOT NULL,
    nome VARCHAR(100),
    segmento VARCHAR(50),
    valid_from DATE NOT NULL,
    valid_to DATE NULL,
    is_current BIT NOT NULL
);

Step 3: Close the old versions that changed

The first part of the transformation finds the records whose nome or segmento differs from the version in staging and "closes" them: it fills valid_to with today's date and sets is_current = 0. Only the current rows that actually changed are affected; the rest stay untouched.

UPDATE d
SET d.valid_to = CAST(GETDATE() AS DATE),
    d.is_current = 0
FROM dim_cliente AS d
INNER JOIN stg_clientes AS s
    ON s.cliente_id = d.cliente_id
WHERE d.is_current = 1
  AND (d.segmento <> s.segmento OR d.nome <> s.nome);

Step 4: Insert the new versions

Next, we insert a new row for every customer that is new or that we just closed in the previous step. The new version is marked as current (is_current = 1), with valid_from set to today and valid_to empty.

INSERT INTO dim_cliente (cliente_id, nome, segmento, valid_from, valid_to, is_current)
SELECT s.cliente_id, s.nome, s.segmento, CAST(GETDATE() AS DATE), NULL, 1
FROM stg_clientes AS s
LEFT JOIN dim_cliente AS d
    ON d.cliente_id = s.cliente_id AND d.is_current = 1
WHERE d.cliente_id IS NULL;

The LEFT JOIN followed by WHERE d.cliente_id IS NULL captures two cases at once: brand-new customers (who never had a row in the dimension) and customers whose current version was closed in Step 3 — and therefore no longer have any row with is_current = 1. Customers who did not change keep their current row and are not duplicated.

Step 5: Automate the transformation in the ELT pipeline

Combine the two statements into a single stored procedure or script, run every time after loading the staging layer. Order matters: first the UPDATE that closes, then the INSERT that creates. An orchestrator such as Azure Data Factory, Databricks Workflows or a simple scheduler can run this step on every data refresh. Because the process always compares staging against the current version, running the script twice in a row creates no duplicates: it is idempotent.

Check the result

The golden rule of an SCD Type 2 is simple: each entity can have only one current version. The query below should return no rows — if it does, some customer has too many current versions.

SELECT cliente_id, COUNT(*) AS versoes_atuais
FROM dim_cliente
WHERE is_current = 1
GROUP BY cliente_id
HAVING COUNT(*) > 1;

To see the history, query a specific customer and order by valid_from: you should see one closed row (with valid_to filled in and is_current = 0) and one current row. That confirms the change was stored without losing the previous value.

Conclusion

With just two SQL statements — closing the old versions and inserting the new ones — you can already keep a reliable history in an SCD Type 2 dimension within an ELT flow. From here, you can add a hash column to detect changes across many attributes at once, or use a maximum end date (such as 9999-12-31) instead of NULL to simplify joins. Which attributes in your business change over time and would be worth keeping in history?