Cómo implementar SCD Tipo 2 en ETL: paso a paso

Conservar el histórico de las dimensiones es uno de los problemas clásicos de cualquier data warehouse: cuando la dirección de un cliente cambia, quieres saber cuál era el valor antiguo y cuál es el nuevo, sin borrar nada. La técnica que resuelve esto se llama SCD Tipo 2 (Slowly Changing Dimension) y vas a aprender a implementarla en un pipeline ETL, paso a paso y con ejemplos sencillos en SQL.

Requisitos previos

• Una base de datos relacional (SQL Server, PostgreSQL, MySQL o equivalente) donde puedas crear tablas.
• Conocimientos básicos de SQL: SELECT, INSERT y UPDATE.
• Un origen con los datos actuales de los clientes — tu sistema operacional o un fichero que cargas en un área de staging.

Paso 1: Diseñar la dimensión con columnas de histórico

La idea central del SCD Tipo 2 es sencilla: en lugar de sobrescribir un valor que cambió, guardas una nueva fila y mantienes la antigua. Para ello, la tabla de dimensión necesita tres columnas de control además de los atributos normales — una fecha de inicio de validez, una fecha de fin y un indicador que dice cuál es la versión vigente.

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
);

Fíjate en la diferencia entre las dos claves. La surrogate key (cliente_sk) es única para cada versión y es lo que enlazan tus tablas de hechos. La clave de negocio (cliente_id) identifica al cliente real, que con el tiempo puede tener varias filas — una por cada cambio.

Paso 2: Llevar los datos actuales a staging

Un buen pipeline ETL nunca compara directamente contra el sistema de origen. En su lugar, copia la foto actual de los datos a una tabla de staging y trabaja desde ahí. Así aíslas la transformación y puedes volver a ejecutar el proceso sin sobrecargar el origen.

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

Paso 3: Detectar lo que cambió

Ahora comparas cada cliente de staging con su versión activa en la dimensión. Si un atributo relevante es diferente — por ejemplo, la cidade (ciudad) — entonces ese cliente cambió y su versión actual debe ser reemplazada.

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;

El JOIN por ativo = 1 garantiza que solo comparas con la versión vigente, ignorando las filas históricas ya cerradas. El operador <> (distinto) devuelve solo los registros que realmente cambiaron.

Paso 4: Cerrar las versiones antiguas

Para cada cliente modificado, marca la versión activa como terminada: rellena la fecha de fin y pon el indicador ativo a 0. Este "cierre suave" es el corazón del SCD Tipo 2 — nunca borras la fila, solo dices que dejó de estar vigente.

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;

Consejo: usa siempre la misma fecha (por ejemplo, la fecha de ejecución del pipeline) para el fin de una versión y el inicio de la siguiente. Así evitas huecos en la línea de tiempo.

Paso 5: Insertar las nuevas versiones

Falta insertar una fila nueva para cada cliente que ya no tiene versión activa. Y aquí está la parte elegante: como el Paso 4 acaba de cerrar las versiones modificadas, tanto los clientes nuevos como los modificados quedan ahora sin ninguna fila con ativo = 1. Un único INSERT resuelve los dos casos.

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;

El orden es obligatorio: primero cerrar (Paso 4), luego insertar (Paso 5). Si lo inviertes, te arriesgas a tener dos versiones activas del mismo cliente a la vez — y el histórico deja de tener sentido.

Verificar el resultado

Para confirmar que todo salió bien, consulta un cliente que sepas que cambió. Debes ver dos filas: la antigua, ya con valido_ate rellenado y ativo = 0, y la nueva, todavía abierta y activa.

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

La regla de oro para validar un SCD Tipo 2: cada cliente_id debe tener exactamente una fila con ativo = 1. Si aparecen dos, algo salió mal en el orden de los pasos.

Conclusión

Acabas de construir una dimensión SCD Tipo 2 completa — preparaste la tabla, detectaste los cambios, cerraste las versiones antiguas e insertaste las nuevas, sin perder un solo dato histórico. El siguiente paso es agrupar los Pasos 4 y 5 en una sola transacción (para que nunca queden a medias) y programar todo el proceso en tu orquestador, ya sea Azure Data Factory, dbt o un simple job de SQL. Antes de continuar, quédate con esta pregunta: de los atributos de tu dimensión, ¿cuáles necesitan realmente histórico — y cuáles puedes simplemente sobrescribir con un SCD Tipo 1?