Implementar uma dimensão de alteração lenta (SCD) tipo 2

Depois de selecionar o SCD Tipo 2 para uma dimensão, você precisa implementar a estrutura da tabela e alterar a lógica de captura no Azure Databricks. Esta unidade se concentra na criação de tabelas SCD Tipo 2 e na implementação dos padrões MERGE que mantêm o histórico de versão conforme os dados de origem são alterados.

Criar a tabela SCD Tipo 2

O SQL a seguir cria uma tabela de dimensão de cliente com colunas de acompanhamento 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 cláusula GENERATED ALWAYS AS IDENTITY cria uma chave substituta de incremento automático para cada nova linha. Habilitar o feed de dados de alteração permite que processos downstream capturem alterações incrementais com eficiência.

Implementar a captura de alterações com MERGE

A MERGE instrução fornece uma maneira eficiente de implementar a lógica SCD Tipo 2 ao processar atualizações de sistemas fontes. A instrução manipula inserções, atualizações e a lógica de controle de versão em uma única transação.

O padrão a seguir 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, você pode consultar dados como eles existiam a qualquer momento. O padrão de consulta depende de suas necessidades analíticas.

Consultas em um ponto no tempo

Para localizar o estado dos dados em um momento específico, filtre o 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';

Essa consulta retorna exatamente uma linha por cliente—a versão que era válida no timestamp especificado.

Histórico de desempenho

Para exibir o histórico completo de alterações de uma entidade específica:

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

Essa consulta revela todas as versões do cliente C-555, mostrando como seus atributos foram alterados ao longo do tempo.

Usar a viagem no tempo do Delta Lake

O Delta Lake fornece recursos internos de viagem no tempo que complementam designs de tabela SCD Tipo 2 explícitos. Você pode consultar versões anteriores da tabela usando a sintaxe TIMESTAMP AS OF 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;