Implementar uma dimensão que muda lentamente (SCD) tipo 2

Depois de selecionar o SCD Tipo 2 para uma dimensão, precisa de implementar a estrutura de tabela e a lógica de captura de alterações no Azure Databricks. Esta unidade foca-se na criação de tabelas SCD Tipo 2 e na implementação dos padrões MERGE que mantêm o histórico de versões à medida que os dados de origem mudam.

Crie a tabela SCD Tipo 2

O seguinte SQL cria uma tabela de dimensão do cliente com colunas de rastreio SCD Tipo 2:

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

A GENERATED ALWAYS AS IDENTITY cláusula cria uma chave substituta auto-incrementante para cada nova linha. Ativar o feed de dados de alterações permite que os processos posteriores capturem alterações incrementais de forma eficiente.

Implemente a captura de alterações com o MERGE

A MERGE instrução fornece uma forma eficiente de implementar lógica SCD Tipo 2 ao processar atualizações dos sistemas de origem. A instrução trata de inserções, atualizações e a lógica de versionamento numa única transação.

O padrão seguinte fecha a versão atual e insere a nova versão quando ocorrem alterações:

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;

Consultar dados históricos

Depois de implementar uma tabela SCD Tipo 2, pode consultar dados tal como existiam em qualquer momento. O padrão de consulta depende das suas necessidades analíticas.

Consultas a um ponto específico no tempo

Para encontrar o estado dos dados num momento específico, filtre pelo período de validade:

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 devolve exatamente uma linha por cliente — a versão válida no carimbo temporal especificado.

Histórico de desempenho

Para ver o histórico completo das alterações para uma entidade 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 as versões do cliente C-555, mostrando como os seus atributos mudaram ao longo do tempo.

Use Viagem no Tempo do Delta Lake

O Delta Lake oferece capacidades integradas de viagem no tempo que complementam projetos explícitos de tabelas SCD Tipo 2. Pode consultar versões anteriores das tabelas usando a TIMESTAMP AS OF sintaxe ou: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;