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

Keeping the history of your dimensions is one of the classic problems in any data warehouse: when a customer's address changes, you want to know both the old value and the new one, without deleting anything. The technique that solves this is called SCD Type 2 (Slowly Changing Dimension), and you will learn to implement it in an ETL pipeline, step by step and with simple SQL examples.

Prerequisites

• A relational database (SQL Server, PostgreSQL, MySQL, or equivalent) where you can create tables.
• Basic SQL knowledge: SELECT, INSERT, and UPDATE.
• A source with the current customer data — your operational system or a file you load into a staging area.

Step 1: Design the dimension with history columns

The core idea of SCD Type 2 is simple: instead of overwriting a value that changed, you store a new row and keep the old one. To do that, the dimension table needs three control columns in addition to the normal attributes — a validity start date, an end date, and a flag that tells you which version is current.

CREATE TABLE dim_cliente (
    cliente_sk  INT IDENTITY(1,1) PRIMARY KEY,  -- chave substituta
    cliente_id  INT          NOT NULL,          -- chave de negócio
    nome        VARCHAR(100),
    cidade      VARCHAR(100),
    valido_de   DATE         NOT NULL,
    valido_ate  DATE         NULL,
    ativo       BIT          NOT NULL DEFAULT 1
);

Notice the difference between the two keys. The surrogate key (cliente_sk) is unique for each version and is what your fact tables link to. The business key (cliente_id) identifies the real customer, who over time may have several rows — one per change.

Step 2: Load the current data into staging

A good ETL pipeline never compares directly against the source system. Instead, it copies the current snapshot of the data into a staging table and works from there. This isolates the transformation and lets you re-run the process without overloading the source.

CREATE TABLE stg_cliente (
    cliente_id INT,
    nome       VARCHAR(100),
    cidade     VARCHAR(100)
);
-- a extracao (E do ETL) preenche esta tabela com o estado de hoje

Step 3: Detect what changed

Now you compare each customer in staging with their active version in the dimension. If a relevant attribute differs — for example, the cidade (city) — then that customer changed and their current version must be replaced.

SELECT s.cliente_id, s.nome, s.cidade
FROM   stg_cliente AS s
JOIN   dim_cliente AS d
       ON  d.cliente_id = s.cliente_id
       AND d.ativo = 1
WHERE  s.cidade <> d.cidade
   OR  s.nome   <> d.nome;

The JOIN on ativo = 1 makes sure you only compare against the current version, ignoring the historical rows that are already closed. The <> (not equal) operator returns only the records that actually changed.

Step 4: Close the old versions

For each changed customer, mark the active version as ended: fill in the end date and set the ativo flag to 0. This "soft close" is the heart of SCD Type 2 — you never delete the row, you just say it is no longer in effect.

UPDATE d
SET    d.valido_ate = CAST(GETDATE() AS DATE),
       d.ativo      = 0
FROM   dim_cliente AS d
JOIN   stg_cliente AS s
       ON  d.cliente_id = s.cliente_id
       AND d.ativo = 1
WHERE  s.cidade <> d.cidade
   OR  s.nome   <> d.nome;

Tip: always use the same date (for example, the pipeline run date) for the end of one version and the start of the next. That way you avoid gaps in the timeline.

Step 5: Insert the new versions

What is left is to insert a new row for every customer that no longer has an active version. And here is the elegant part: because Step 4 just closed the changed versions, both the new customers and the changed ones now have no row with ativo = 1. A single INSERT handles both cases.

INSERT INTO dim_cliente (cliente_id, nome, cidade, valido_de, valido_ate, ativo)
SELECT s.cliente_id, s.nome, s.cidade, CAST(GETDATE() AS DATE), NULL, 1
FROM   stg_cliente AS s
LEFT JOIN dim_cliente AS d
       ON  d.cliente_id = s.cliente_id
       AND d.ativo = 1
WHERE  d.cliente_sk IS NULL;

The order is mandatory: close first (Step 4), then insert (Step 5). If you reverse it, you risk having two active versions of the same customer at once — and the history stops making sense.

Check the result

To confirm everything went well, query a customer you know has changed. You should see two rows: the old one, now with valido_ate filled in and ativo = 0, and the new one, still open and active.

SELECT cliente_id, cidade, valido_de, valido_ate, ativo
FROM   dim_cliente
WHERE  cliente_id = 42
ORDER  BY valido_de;

The golden rule for validating an SCD Type 2: each cliente_id must have exactly one row with ativo = 1. If two show up, something went wrong in the order of the steps.

Conclusion

You have just built a complete SCD Type 2 dimension — you prepared the table, detected the changes, closed the old versions, and inserted the new ones, without losing a single piece of historical data. The next step is to wrap Steps 4 and 5 in a single transaction (so they never end up half-done) and schedule the whole process in your orchestrator, whether that is Azure Data Factory, dbt, or a simple SQL job. Before you move on, sit with this question: of your dimension's attributes, which ones truly need history — and which can you simply overwrite with an SCD Type 1?