Como implementar SCD Tipo 2 num Data Warehouse

Quando um cliente muda de cidade ou um produto muda de categoria, um Data Warehouse bem desenhado não apaga o valor antigo: guarda o histórico completo, para que os relatórios sobre o passado continuem corretos. A técnica mais comum para o conseguir chama-se SCD Tipo 2 (Slowly Changing Dimension) e é mais simples do que parece. A seguir vais criar uma dimensão de clientes que regista cada versão de cada cliente ao longo do tempo, usando apenas SQL.

Pré-requisitos

• Acesso a uma base de dados relacional ou Data Warehouse (SQL Server, Azure SQL, PostgreSQL, Snowflake…).
• Noções básicas de SQL: CREATE TABLE, INSERT e UPDATE.
• Uma tabela de origem (staging) com o retrato atual dos clientes, aqui chamada staging_cliente.

Passo 1: Perceber o que muda numa SCD Tipo 2

Uma dimensão guarda os atributos descritivos de uma entidade — por exemplo o nome e a cidade de um cliente. Imagina que o cliente número 1 se muda do Porto para Lisboa. Numa SCD Tipo 1 substituis «Porto» por «Lisboa» e perdes a informação anterior; qualquer relatório sobre o passado passa a estar errado. Numa SCD Tipo 2 crias uma linha nova para a nova cidade e manténs a linha antiga intacta, marcando o período em que cada versão foi válida.

Para isso precisas de três colunas de controlo: uma data de início (valido_de), uma data de fim (valido_ate) e um indicador da versão atual (atual).

Passo 2: Criar a tabela de dimensão

Além dos atributos do cliente, a tabela tem uma chave substituta (surrogate key) que identifica cada versão, e a chave de negócio cliente_id, que se repete entre as várias versões do mesmo cliente. A chave substituta é importante porque é para ela que as tabelas de factos vão apontar: assim cada venda fica ligada exatamente à versão do cliente que existia na data da venda.

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

O intervalo de validade é meio-aberto: vai de valido_de (inclusive) até valido_ate (exclusivo). A versão atual tem valido_ate a NULL e atual = 1.

Passo 3: Fazer a carga inicial

Na primeira execução, todos os clientes entram como versão atual. Corre este passo apenas uma vez, para arrancar a dimensão a partir do 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;

Passo 4: Fechar as versões que mudaram

A partir daqui, os passos 4 e 5 formam a carga recorrente, aquela que corres em cada atualização. Primeiro fechamos a versão atual dos clientes cujo nome ou cidade mudou: preenchemos a data de fim e passamos 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);

Se algum atributo permitir valores NULL, envolve a comparação com ISNULL (ou COALESCE) para detetar também as mudanças de e para NULL.

Passo 5: Inserir a nova versão

Agora inserimos uma linha nova para dois casos: os clientes que acabámos de fechar no passo anterior e os clientes totalmente novos. Ambos têm uma coisa em comum — neste momento não têm nenhuma linha com atual = 1. O LEFT JOIN encontra precisamente esses.

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 o resultado

Consulta o histórico de um cliente que tenha mudado de cidade. Deves ver duas linhas: a antiga com atual = 0 e uma data de fim preenchida, e a nova com atual = 1 e fim a NULL.

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

Faz também uma verificação de sanidade: cada cliente só pode ter uma versão atual. A consulta seguinte não deve devolver nenhuma linha.

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

Conclusão

Com estes cinco passos tens uma dimensão que nunca perde história e que responde a perguntas como «em que cidade estava este cliente quando fez a compra». O passo seguinte é automatizar os passos 4 e 5 num procedimento agendado e, quando a dimensão tiver muitos atributos, comparar um hash das colunas em vez de campo a campo, para o código ficar mais curto. Qual das dimensões do teu modelo beneficiaria mais de manter o histórico?