Cómo implementar SCD Tipo 2 en un Data Warehouse

Cuando un cliente cambia de ciudad o un producto cambia de categoría, un Data Warehouse bien diseñado no borra el valor antiguo: guarda el historial completo, para que los informes sobre el pasado sigan siendo correctos. La técnica más común para lograrlo se llama SCD Tipo 2 (Slowly Changing Dimension) y es más sencilla de lo que parece. A continuación vas a crear una dimensión de clientes que registra cada versión de cada cliente a lo largo del tiempo, usando solo SQL.

Requisitos previos

• Acceso a una base de datos relacional o Data Warehouse (SQL Server, Azure SQL, PostgreSQL, Snowflake…).
• Nociones básicas de SQL: CREATE TABLE, INSERT y UPDATE.
• Una tabla de origen (staging) con la foto actual de los clientes, aquí llamada staging_cliente.

Paso 1: Entender qué cambia en una SCD Tipo 2

Una dimensión guarda los atributos descriptivos de una entidad — por ejemplo el nombre y la ciudad de un cliente. Imagina que el cliente número 1 se muda de Oporto a Lisboa. En una SCD Tipo 1 sustituyes «Oporto» por «Lisboa» y pierdes la información anterior; cualquier informe sobre el pasado queda incorrecto. En una SCD Tipo 2 creas una fila nueva para la nueva ciudad y mantienes la fila antigua intacta, marcando el periodo en que cada versión fue válida.

Para ello necesitas tres columnas de control: una fecha de inicio (valido_de), una fecha de fin (valido_ate) y un indicador de la versión actual (atual).

Paso 2: Crear la tabla de dimensión

Además de los atributos del cliente, la tabla tiene una clave subrogada (surrogate key) que identifica cada versión, y la clave de negocio cliente_id, que se repite entre las distintas versiones del mismo cliente. La clave subrogada es importante porque es a ella a la que apuntarán las tablas de hechos: así cada venta queda ligada exactamente a la versión del cliente que existía en la fecha de la venta.

CREATE TABLE dim_cliente (
  cliente_sk  INT IDENTITY(1,1) PRIMARY KEY,
  cliente_id  INT          NOT NULL,
  nome        VARCHAR(100) NOT NULL,
  cidade      VARCHAR(100) NOT NULL,
  valido_de   DATE         NOT NULL,
  valido_ate  DATE         NULL,
  atual       BIT          NOT NULL
);

El intervalo de validez es semiabierto: va de valido_de (inclusive) hasta valido_ate (exclusivo). La versión actual tiene valido_ate en NULL y atual = 1.

Paso 3: Hacer la carga inicial

En la primera ejecución, todos los clientes entran como versión actual. Ejecuta este paso solo una vez, para arrancar la dimensión a partir del staging.

INSERT INTO dim_cliente (cliente_id, nome, cidade, valido_de, valido_ate, atual)
SELECT cliente_id, nome, cidade, CAST(GETDATE() AS DATE), NULL, 1
FROM staging_cliente;

Paso 4: Cerrar las versiones que cambiaron

A partir de aquí, los pasos 4 y 5 forman la carga recurrente, la que ejecutas en cada actualización. Primero cerramos la versión actual de los clientes cuyo nombre o ciudad cambió: rellenamos la fecha de fin y ponemos atual a 0.

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

Si algún atributo permite valores NULL, envuelve la comparación con ISNULL (o COALESCE) para detectar también los cambios a y desde NULL.

Paso 5: Insertar la nueva versión

Ahora insertamos una fila nueva para dos casos: los clientes que acabamos de cerrar en el paso anterior y los clientes totalmente nuevos. Ambos tienen algo en común — en este momento no tienen ninguna fila con atual = 1. El LEFT JOIN encuentra precisamente esos.

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

Verificar el resultado

Consulta el historial de un cliente que haya cambiado de ciudad. Debes ver dos filas: la antigua con atual = 0 y una fecha de fin rellenada, y la nueva con atual = 1 y fin en NULL.

SELECT cliente_id, nome, cidade, valido_de, valido_ate, atual
FROM dim_cliente
WHERE cliente_id = 1
ORDER BY valido_de;

Haz también una comprobación de sanidad: cada cliente solo puede tener una versión actual. La siguiente consulta no debe devolver ninguna fila.

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

Conclusión

Con estos cinco pasos tienes una dimensión que nunca pierde historia y que responde a preguntas como «en qué ciudad estaba este cliente cuando hizo la compra». El siguiente paso es automatizar los pasos 4 y 5 en un procedimiento programado y, cuando la dimensión tenga muchos atributos, comparar un hash de las columnas en vez de campo a campo, para que el código quede más corto. ¿Cuál de las dimensiones de tu modelo se beneficiaría más de mantener el historial?