Implementar una dimensión de cambio lento tipo 2 (SCD)

Después de seleccionar SCD Type 2 para una dimensión, debe implementar la estructura de tabla y la lógica de captura de cambios en Azure Databricks. Esta unidad se centra en la creación de tablas de tipo 2 de SCD e implementación de los patrones MERGE que mantienen el historial de versiones a medida que cambian los datos de origen.

Creación de la tabla SCD Type 2

El siguiente código SQL crea una tabla de dimensiones de cliente con columnas de seguimiento de tipo 2 de SCD:

CREATE TABLE sales.customer (
    customer_sk BIGINT GENERATED ALWAYS AS IDENTITY,
    customer_id STRING NOT NULL,
    customer_name STRING,
    email STRING,
    city STRING,
    region STRING,
    valid_from TIMESTAMP NOT NULL,
    valid_to TIMESTAMP NOT NULL,
    is_current BOOLEAN
)
USING DELTA
TBLPROPERTIES (
    delta.enableChangeDataFeed = true
);

La GENERATED ALWAYS AS IDENTITY cláusula crea una clave suplente de incremento automático para cada nueva fila. Habilitar el flujo de datos de cambios permite que los procesos posteriores capturen los cambios incrementales de forma eficaz.

Implementación de la captura de cambios con MERGE

La MERGE instrucción proporciona una manera eficaz de implementar la lógica SCD Type 2 al procesar actualizaciones procedentes de sistemas de origen. La declaración maneja inserciones, actualizaciones y la lógica de versiones en una sola transacción.

El siguiente patrón cierra la versión actual e inserta la nueva versión cuando se producen cambios:

MERGE INTO sales.customer AS target
USING (
    SELECT 
        source.customer_id,
        source.customer_name,
        source.email,
        source.city,
        source.region,
        current_timestamp() AS valid_from,
        CAST('9999-12-31' AS TIMESTAMP) AS valid_to,
        true AS is_current
    FROM staging.customers AS source
) AS updates
ON target.customer_id = updates.customer_id AND target.is_current = true
WHEN MATCHED AND (
    target.customer_name <> updates.customer_name OR
    target.email <> updates.email OR
    target.city <> updates.city OR
    target.region <> updates.region
) THEN UPDATE SET
    target.valid_to = current_timestamp(),
    target.is_current = false
WHEN NOT MATCHED THEN INSERT (
    customer_id, customer_name, email, city, region,
    valid_from, valid_to, is_current
) VALUES (
    updates.customer_id, updates.customer_name, updates.email, 
    updates.city, updates.region, updates.valid_from, 
    updates.valid_to, updates.is_current
);

-- Insert new versions for updated records
INSERT INTO sales.customer
SELECT 
    s.customer_id,
    s.customer_name,
    s.email,
    s.city,
    s.region,
    current_timestamp() AS valid_from,
    CAST('9999-12-31' AS TIMESTAMP) AS valid_to,
    true AS is_current
FROM staging.customers s
JOIN sales.customer h 
    ON s.customer_id = h.customer_id 
    AND h.valid_to = current_timestamp()
    AND h.is_current = false;

Consulta de datos históricos

Una vez que haya implementado una tabla scD tipo 2, puede consultar los datos tal como existían en cualquier momento. El patrón de consulta depende de las necesidades analíticas.

Consultas en un momento específico

Para buscar el estado de los datos en un momento específico, filtre por el período de validez:

SELECT customer_id, customer_name, city
FROM sales.customer
WHERE valid_from <= '2023-06-15 12:00:00'
  AND valid_to > '2023-06-15 12:00:00';

Esta consulta devuelve exactamente una fila por cliente, la versión válida en la marca de tiempo especificada.

Historial de desempeño

Para ver el historial completo de cambios de una entidad específica:

SELECT customer_name, city, valid_from, valid_to
FROM sales.customer
WHERE customer_id = 'C-555'
ORDER BY valid_from;

Esta consulta revela todas las versiones del cliente C-555, en las que se muestra cómo cambiaron sus atributos a lo largo del tiempo.

Uso del viaje en el tiempo de Delta Lake

Delta Lake proporciona funcionalidades integradas de viaje en el tiempo que complementan los diseños explícitos de tablas Tipo 2 SCD. Puede consultar versiones de tabla anteriores mediante la TIMESTAMP AS OF sintaxis o VERSION AS OF :

-- Query table state from 7 days ago
SELECT * FROM sales.customer 
TIMESTAMP AS OF '2024-01-15';

-- Query a specific table version
SELECT * FROM sales.customer 
VERSION AS OF 42;