Slowly Changing Dimension Type 2: Como o SCD2 Funciona em um Data Warehouse

Um cliente muda da região Sul para a região Norte. Todos os pedidos que ele fez antes da mudança continuam tendo acontecido no Sul. Se o warehouse sobrescrever a região, o relatório de vendas do ano passado muda de forma da noite para o dia. Slowly Changing Dimension Type 2 (SCD2) é o padrão de modelagem que mantém a linha antiga, adiciona uma nova e permite que cada fato se junte à versão que era verdadeira no momento em que aconteceu.

O que é uma dimensão de mudança lenta?

Uma dimensão de mudança lenta (slowly changing dimension) é uma tabela de dimensão (cliente, produto, funcionário, localização) cujos atributos descritivos mudam ao longo do tempo, e o tipo de SCD é a regra sobre o que o warehouse faz quando isso acontece. As três respostas básicas são sobrescrever o valor, adicionar uma nova linha ou adicionar uma nova coluna.

Na modelagem dimensional, fatos são os eventos que você mede (um pedido, um envio, um pagamento) e dimensões descrevem o quem, o quê e o onde ao redor desses eventos. Atributos de dimensão mudam devagar em comparação com os fatos: um cliente muda de endereço algumas vezes na vida, enquanto pedidos chegam a cada minuto. O tipo de SCD é a escolha de quem administra o warehouse sobre como responder a mudanças nas descrições de entidades como Funcionário, Cliente, Produto, Fornecedor e Localização.

Um exemplo comum é a região de um funcionário ou o endereço de um cliente. Vendas creditadas à região antiga devem continuar lá. Um relatório de "vendas por região atual" é uma pergunta diferente, e o tipo de SCD decide se a tabela consegue responder uma, outra, ou as duas.

Quais são os tipos de SCD de 0 a 7?

Kimball define oito tipos. Os tipos 0, 1, 2 e 3 são as técnicas base (manter o original, sobrescrever, adicionar uma linha, adicionar uma coluna). O tipo 4 divide atributos voláteis em uma mini-dimensão, e os tipos 5, 6 e 7 são híbridos construídos em cima do tipo 2.

O tipo 4 na numeração de Kimball é uma mini-dimensão. Algumas ferramentas e posts de blog usam "tipo 4" para se referir a uma tabela de histórico separada ao lado de uma tabela atual. Esse é um padrão útil, mas é uma coisa diferente do que Kimball numerou.

Como funciona uma dimensão de mudança lenta do Tipo 2?

Quando um atributo rastreado muda, uma dimensão Tipo 2 mantém a linha antiga intocada, insere uma nova linha com os novos valores e uma nova chave substituta, e marca a linha antiga como expirada. O mínimo de Kimball é três colunas extras: uma data de início de vigência da linha, uma data de expiração da linha e um indicador de linha atual.

Duas chaves fazem isso funcionar. A chave natural (customer_id do sistema de origem) é a mesma em todas as versões e é o que Kimball chama de cola que mantém as versões unidas. A chave substituta (customer_sk) é um inteiro atribuído pelo warehouse, diferente em cada versão. As linhas de fato armazenam a chave substituta, então um pedido feito em fevereiro continua apontando para a versão de fevereiro do cliente para sempre.

Duas regras mantêm as janelas de validade organizadas. O timestamp de expiração de uma versão deve ser igual ao timestamp de vigência da próxima versão, sem lacunas e sem sobreposições. E a versão atual tem ou uma expiração NULL ou uma data distante no futuro, como 9999-12-31, dependendo da ferramenta. A flag "current" existe para que "me dê os clientes de hoje" seja um filtro simples em vez de uma comparação de datas.

Quais colunas uma tabela SCD2 precisa?

Uma tabela SCD2 precisa da chave natural, uma chave substituta por versão, os atributos rastreados, um timestamp de início de vigência, um timestamp de fim de vigência e uma flag de atual. Um hash dos atributos rastreados é opcional, mas transforma a detecção de mudanças em uma única comparação.

Só faça o hash dos atributos que você quer versionar. Se a tabela de origem tem uma coluna last_login que muda todo dia e você a inclui no hash, você gera uma nova versão de cliente todo dia. O exemplo prático da Microsoft usa o mesmo formato com nomes diferentes: SalesRepID como chave substituta, RepSourceID como chave natural, além de StartDate, EndDate, IsCurrent e Hash.

Como você implementa SCD Type 2 em SQL?

A carga SCD2 portável é composta por dois comandos dentro de uma transação. Primeiro, feche a versão atual de qualquer cliente cujo hash seja diferente da linha em staging. Segundo, insira uma nova versão para cada linha em staging que agora não tem correspondência atual, o que cobre tanto clientes totalmente novos quanto os que acabaram de ser fechados.

A tabela de staging guarda uma linha por cliente por carga, com os valores como estão agora e o momento em que mudaram:

CREATE TABLE dim_customer (
customer_sk BIGINT,
customer_id INTEGER,
customer_name VARCHAR,
region VARCHAR,
attr_hash VARCHAR,
valid_from TIMESTAMP,
valid_to TIMESTAMP,
is_current BOOLEAN
);

CREATE TABLE stg_customer (
customer_id INTEGER,
customer_name VARCHAR,
region VARCHAR,
changed_at TIMESTAMP
);

O passo um fecha as versões que mudaram. Clientes sem mudança batem no hash e são ignorados:

UPDATE dim_customer AS d
SET
valid_to = s.changed_at,
is_current = FALSE
FROM stg_customer AS s
WHERE d.customer_id = s.customer_id
AND d.is_current
AND d.attr_hash <> md5(concat_ws('|', s.customer_name, s.region));

O passo dois insere uma nova versão para tudo que não tem uma linha atual. A chave substituta continua a partir do máximo atual:

INSERT INTO dim_customer (
customer_sk, customer_id, customer_name, region,
attr_hash, valid_from, valid_to, is_current
)
WITH max_sk AS (
SELECT coalesce(max(customer_sk), 0) AS value
FROM dim_customer
),
rows_to_insert AS (
SELECT s.*
FROM stg_customer AS s
LEFT JOIN dim_customer AS d
ON d.customer_id = s.customer_id
AND d.is_current
WHERE d.customer_id IS NULL
)
SELECT
row_number() OVER (ORDER BY s.customer_id) + max_sk.value AS customer_sk,
s.customer_id,
s.customer_name,
s.region,
md5(concat_ws('|', s.customer_name, s.region)) AS attr_hash,
s.changed_at AS valid_from,
NULL AS valid_to,
TRUE AS is_current
FROM rows_to_insert AS s
CROSS JOIN max_sk;

Para ver isso funcionando, carregue três clientes em 2024-01-01, depois um segundo lote em 2024-03-10 em que Ana (101) mudou do Sul para o Norte, Bruno (102) não mudou, e Diego (104) é novo. Carla (103) está ausente do segundo lote:

INSERT INTO stg_customer VALUES
(101, 'Ana', 'South', TIMESTAMP '2024-01-01 09:00:00'),
(102, 'Bruno', 'North', TIMESTAMP '2024-01-01 09:00:00'),
(103, 'Carla', 'South', TIMESTAMP '2024-01-01 09:00:00');
-- rode os dois passos

TRUNCATE stg_customer;
INSERT INTO stg_customer VALUES
(101, 'Ana', 'North', TIMESTAMP '2024-03-10 14:30:00'),
(102, 'Bruno', 'North', TIMESTAMP '2024-03-10 14:30:00'),
(104, 'Diego', 'West', TIMESTAMP '2024-03-10 14:30:00');
-- rode os dois passos de novo

A dimensão depois da segunda carga, do DuckDB 1.5.5:

+-------------+-------------+---------------+--------+---------------------+---------------------+------------+
| customer_sk | customer_id | customer_name | region | valid_from | valid_to | is_current |
+-------------+-------------+---------------+--------+---------------------+---------------------+------------+
| 1 | 101 | Ana | South | 2024-01-01 09:00:00 | 2024-03-10 14:30:00 | false |
| 4 | 101 | Ana | North | 2024-03-10 14:30:00 | NULL | true |
| 2 | 102 | Bruno | North | 2024-01-01 09:00:00 | NULL | true |
| 3 | 103 | Carla | South | 2024-01-01 09:00:00 | NULL | true |
| 5 | 104 | Diego | West | 2024-03-10 14:30:00 | NULL | true |
+-------------+-------------+---------------+--------+---------------------+---------------------+------------+

Quatro clientes de origem viraram cinco linhas de dimensão. Ana tem duas versões, a do Sul fechou exatamente no timestamp em que a do Norte abriu. O hash de Bruno bateu, então nada aconteceu com ele. Carla estava ausente do segundo lote e ainda está atual, porque esse padrão trata uma linha ausente como "sem novidade". Deleções são cobertas mais abaixo.

Duas coisas para observar nesse padrão. Ele espera uma linha por customer_id no staging por execução. Se sua tabela de staging carrega várias versões da mesma chave em uma carga (um feed de CDC faz isso), mantenha só a versão mais recente por chave antes de rodar isso, ou rode os dois passos em loop na ordem de changed_at. E o UPDATE e o INSERT precisam rodar na mesma transação, para que uma falha entre eles não deixe um cliente com zero linhas atuais.

Como você consulta o registro atual ou um ponto no tempo com SCD2?

O estado atual é um filtro na flag. Uma consulta de ponto no tempo usa um intervalo semiaberto: o timestamp de referência é maior ou igual a valid_from e estritamente menor que valid_to, com valid_to NULL contando como aberto.

SELECT customer_id, customer_name, region
FROM dim_customer
WHERE is_current
ORDER BY customer_id;

+-------------+---------------+--------+
| customer_id | customer_name | region |
+-------------+---------------+--------+
| 101 | Ana | North |
| 102 | Bruno | North |
| 103 | Carla | South |
| 104 | Diego | West |
+-------------+---------------+--------+

A consulta de referência. O intervalo semiaberto importa na fronteira: pedir exatamente 2024-03-10 14:30:00 retorna a linha do Norte, porque esse instante pertence à nova versão e não à antiga:

SELECT customer_id, region, customer_sk, valid_from, valid_to
FROM dim_customer
WHERE customer_id = 101
AND TIMESTAMP '2024-02-15 00:00:00' >= valid_from
AND (TIMESTAMP '2024-02-15 00:00:00' < valid_to OR valid_to IS NULL);

+-------------+--------+-------------+---------------------+---------------------+
| customer_id | region | customer_sk | valid_from | valid_to |
+-------------+--------+-------------+---------------------+---------------------+
| 101 | South | 1 | 2024-01-01 09:00:00 | 2024-03-10 14:30:00 |
+-------------+--------+-------------+---------------------+---------------------+

A mesma lógica de intervalo é como os fatos recebem sua chave substituta no momento da carga. Dois pedidos de Ana, um em fevereiro e um em abril, caem em versões diferentes do cliente:

SELECT f.order_id, f.ordered_at, d.region, d.customer_sk
FROM fact_order AS f
JOIN dim_customer AS d
ON d.customer_id = f.customer_id
AND f.ordered_at >= d.valid_from
AND (f.ordered_at < d.valid_to OR d.valid_to IS NULL)
ORDER BY f.order_id;

+----------+---------------------+--------+-------------+
| order_id | ordered_at | region | customer_sk |
+----------+---------------------+--------+-------------+
| 1001 | 2024-02-01 10:00:00 | South | 1 |
| 1002 | 2024-04-01 16:45:00 | North | 4 |
+----------+---------------------+--------+-------------+

Depois que customer_sk é armazenado na linha de fato, os relatórios se juntam pela chave substituta sozinha e nunca tocam nas colunas de data. O pedido de fevereiro fica no Sul para sempre, sem cálculo de datas no momento da consulta.

De onde vêm as mudanças: dbt snapshots, CDC ou um modo de histórico gerenciado?

Uma transformação SCD2 só consegue versionar as mudanças que chegaram ao warehouse. Se a origem é lida uma vez por dia e um cliente muda de região duas vezes nesse dia, a dimensão recebe uma versão, não duas. Então o método de captura decide o quão fiel é o histórico, antes de qualquer SQL rodar.

Há três formas de as mudanças chegarem:

dbt snapshots são uma abordagem de captura de mudança de dados baseada em lote feita para rodar entre de hora em hora e uma vez por dia. Isso é suficiente quando uma granularidade diária de histórico é suficiente. O History Mode da Fivetran mostra a mesma divisão. Quando a origem tem logs, ela captura todas as mudanças entre sincronizações. Quando não tem, um valor que mudou de 10 para 15 para 20 entre duas sincronizações é registrado só como 20.

Change Data Capture (CDC) lê o log que o próprio banco de dados já escreve para sua própria recuperação. No PostgreSQL, esse é o write-ahead log (WAL), e um processo de CDC o lê através de um replication slot e recebe cada operação, em ordem, com o estado da linha. Para que updates e deletes carreguem a linha completa antes e depois, a tabela precisa de REPLICA IDENTITY FULL, que os docs do conector PostgreSQL da Erathos listam junto com wal_level = logical e um limite de max_slot_wal_keep_size para que um slot travado não encha o disco.

Com a Erathos, o tipo de sincronização Partial Versioned anexa cada versão de registro ao destino e marca qual é a mais recente. Isso dá à carga de dois passos acima uma tabela de staging que já contém todas as versões, com uma ressalva da seção anterior: várias versões da mesma chave podem cair na mesma execução, então a carga processa em ordem de changed_at. A configuração do MySQL para a mesma coisa está no guia de CDC do MySQL para BigQuery.

Como dbt, dlt, Fivetran, Databricks e Airbyte nomeiam as colunas de SCD2?

As ferramentas abaixo usam as mesmas três ideias (início de vigência, fim de vigência, marcador de atual) sob nomes de coluna diferentes e com padrões diferentes para a linha aberta e para deleções.

Dois padrões importam mais que os nomes. O dbt recomenda a estratégia timestamp sempre que possível porque precisa de apenas uma coluna e lida melhor quando a origem adiciona ou remove colunas. O hash padrão do dlt cobre todas as colunas do recurso, então uma coluna instável (um array cuja ordem muda, por exemplo) produz uma versão falsa a menos que você forneça seu próprio hash sobre os campos que importam.

Como um pipeline SCD2 deve lidar com deleções e mudanças que chegam atrasadas?

Uma deleção precisa de uma política declarada, porque a carga de dois passos acima não consegue enxergá-la. Mudanças atrasadas ou fora de ordem precisam de uma coluna de sequência, porque fechar a linha atual em um timestamp anterior ao seu valid_from produz uma versão de duração negativa.

Para deleções há três políticas, e as opções hard_deletes do dbt mapeiam para elas uma a uma:

• Ignorar. A linha continua atual para sempre. Esse é o padrão do dbt, então um cliente deletado ainda aparece em "clientes de hoje".
• Invalidar. Define valid_to e is_current = FALSE na linha atual, para que o cliente não tenha versão atual. O Fivetran History Mode faz isso e fecha a linha no momento da deleção.
• Novo registro. Insere uma versão com uma flag de deletado, para que uma consulta de "quem era cliente nessa data" ainda tenha uma linha para encontrar, e uma restauração posterior vira mais uma versão.

Detectar a deleção em primeiro lugar precisa de uma extração completa para comparar (o dlt encerra qualquer linha ausente de uma extração completa) ou uma origem baseada em log em que a deleção é um evento. Com uma sincronização por cursor, não há nada para detectar.

Para mudanças que chegam atrasadas, o Databricks exige uma coluna SEQUENCE BY e a usa para ordenar eventos que chegam fora de ordem. O equivalente no padrão de dois passos é processar as linhas de staging na ordem de changed_at e dividir uma janela existente quando uma mudança cai dentro dela, o que dá mais trabalho do que o simples fechar-e-inserir. Se sua origem é CDC e o log é lido em ordem, eventos fora de ordem são raros e a ordenação já está no feed.

Quando o Tipo 1 é suficiente, e quanto custa o Tipo 2?

O Tipo 1 é suficiente quando nenhum relatório precisa do valor como ele estava no momento do evento. O Tipo 2 é necessário quando os fatos precisam ficar ligados à versão que era verdadeira quando aconteceram, e custa uma linha extra por mudança rastreada mais uma carga e um join mais complexos.

O custo é direcional em vez de uma proporção fixa. Toda mudança rastreada adiciona uma linha, então uma dimensão com atributos voláteis pode crescer rápido, e um hash que inclui as colunas erradas transforma uma dimensão de mudança lenta em uma rápida. O mesmo efeito aparece na fatura. No Fivetran History Mode, cada registro de origem alterado ou inserido cria uma linha de destino que conta para o MAR pago (monthly active rows, sua unidade de precificação).

Um caminho intermediário útil é o Tipo 6 de Kimball: manter linhas Tipo 2 e adicionar uma coluna de valor atual que é sobrescrita em todas as versões do mesmo cliente. Uma única tabela então responde "vendas por região no momento do pedido" e "vendas pela região atual do cliente hoje" sem uma segunda dimensão.

A Erathos carrega mudanças de PostgreSQL e MySQL do log de transações para BigQuery, Databricks ou Redshift, com o tipo de sincronização versionada que mantém cada versão de registro para uma carga SCD2 como a acima. Experimente a Erathos grátis por 14 dias.