Dimensões que mudam (SCD tipos 1 e 2)

*Slowly changing dimension* (SCD) é a dimensão cujo atributo muda com o tempo. A pergunta é: a venda de março deve aparecer na cidade antiga do cliente ou na nova?

• Tipo 0: nunca muda (data de nascimento).
• Tipo 1: sobrescreve. Simples, mas o passado passa a mostrar a cidade nova.
• Tipo 2: fecha a versão antiga e abre uma nova linha, com início e fim de vigência. O passado fica como era.

Tipo 2 em SQL que roda aqui

A Ana se mudou para Santos em 2024-06-03. A versão antiga é fechada na véspera e uma nova é aberta.

CREATE TABLE dim_cliente AS
SELECT id AS cliente_id, nome, cidade, '2024-01-01' AS inicio, '9999-12-31' AS fim, 1 AS atual
FROM clientes;

UPDATE dim_cliente SET fim = '2024-06-02', atual = 0
WHERE cliente_id = 1 AND atual = 1;
INSERT INTO dim_cliente VALUES (1, 'Ana', 'Santos', '2024-06-03', '9999-12-31', 1);

SELECT p.id AS pedido, p.data, d.cidade
FROM pedidos p
JOIN dim_cliente d ON d.cliente_id = p.cliente_id AND p.data BETWEEN d.inicio AND d.fim
WHERE p.cliente_id = 1
ORDER BY p.data;

pedido | data       | cidade
------ | ---------- | ---------
101    | 2024-06-01 | São Paulo
103    | 2024-06-02 | São Paulo
110    | 2024-06-05 | Santos

Cada pedido casa com a versão vigente na data dele: os dos dias 1 e 2 de junho ficam em São Paulo; o do dia 5, em Santos. Com tipo 1, os três apareceriam em Santos.

No Databricks

O MERGE faz o tipo 1 direto. Para o tipo 2, o padrão é um MERGE que fecha a versão antiga e insere a nova (a linha nova entra duas vezes no lote de origem, com chaves de casamento diferentes), ou o APPLY CHANGES dos pipelines declarativos, que faz o tipo 2 com STORED AS SCD TYPE 2.

• Fim de vigência "aberto" com uma data distante (9999-12-31) deixa o BETWEEN simples; NULL obriga a tratar o caso em toda consulta.
• Use datas no mesmo tipo dos dois lados do BETWEEN: aqui, texto ISO compara certo; misturar texto e data não.