Como criar uma snapshot incremental em Modelação de Dados (Kimball)

Este tutorial mostra como implementar uma snapshot incremental (periodic snapshot) num esquema Kimball para registar o estado de um objecto ao longo do tempo, cobrindo porque é útil e reduzindo armazenamento e trabalho ETL. A snapshot incremental é útil quando só nos interessam versões periódicas de uma fact (ex.: saldo de conta) e queremos optimizar cargas e consultas.

Pré-requisitos

• Conhecimentos básicos de Modelação Kimball (fact e dimensão).
• Ambiente SQL (por exemplo SQL Server, PostgreSQL ou Azure SQL).
• Fonte transaccional ou tabela staging com registos de alterações.

Passo 1: Definir o caso de uso e granularidade

Decida que objecto e que periodicidade pretende capturar. Exemplo: saldo diário por conta bancária. A granularidade determina as chaves da fact (account_id + snapshot_date) e que medidas serão agregadas (balance, transactions_count).

Passo 2: Criar a estrutura da tabela de snapshot

Crie uma tabela fact que armazene apenas um registo por chave de granularidade por período. Inclua campos para identificar se o registo é incremental (updated_flag) e para controlo de data de extracção.

CREATE TABLE fact_account_snapshot (
  account_id INT NOT NULL,
  snapshot_date DATE NOT NULL,
  balance DECIMAL(18,2),
  transactions_count INT,
  updated_flag BIT DEFAULT 0,
  load_ts DATETIME2 DEFAULT SYSUTCDATETIME(),
  PRIMARY KEY (account_id, snapshot_date)
);

Chave primária garante unicidade por período. O campo updated_flag ajuda a identificar registos alterados numa carga incremental.

Passo 3: Preparar a tabela staging com dados actuais

Na etapa ETL/ELT, traga o estado actual das contas para uma staging com a mesma granularidade. Pode ser uma SELECT a partir da fonte transaccional ou uma view que calcula o saldo até à data de snapshot.

-- Exemplo de staging com estado actual
CREATE TABLE stg_account_state (
  account_id INT,
  snapshot_date DATE,
  balance DECIMAL(18,2),
  transactions_count INT
);

-- Inserir dados de exemplo
INSERT INTO stg_account_state (account_id, snapshot_date, balance, transactions_count)
VALUES (1, '2026-07-01', 1250.00, 3), (2, '2026-07-01', 540.50, 1);

Passo 4: Lógica incremental (UPSERT) para carregar a snapshot

Execute um MERGE/UPSERT que compare staging com a fact existente por (account_id, snapshot_date). Só actualize se existir diferença nas medidas — isto reduz escrita e preserva histórico de snapshots anteriores.

-- Exemplo MERGE (SQL Server / Azure SQL)
MERGE INTO fact_account_snapshot AS target
USING stg_account_state AS src
  ON target.account_id = src.account_id
  AND target.snapshot_date = src.snapshot_date
WHEN MATCHED AND (
  ISNULL(target.balance,0) <> ISNULL(src.balance,0)
  OR ISNULL(target.transactions_count,0) <> ISNULL(src.transactions_count,0)
) THEN
  UPDATE SET
    target.balance = src.balance,
    target.transactions_count = src.transactions_count,
    target.updated_flag = 1,
    target.load_ts = SYSUTCDATETIME()
WHEN NOT MATCHED BY TARGET THEN
  INSERT (account_id, snapshot_date, balance, transactions_count, updated_flag)
  VALUES (src.account_id, src.snapshot_date, src.balance, src.transactions_count, 1);

Se usa PostgreSQL, substitua MERGE por INSERT ... ON CONFLICT DO UPDATE ou lógica equivalente.

Passo 5: Limpeza e retenção

Defina política de retenção das snapshots. Se não precisar de todas as datas, pode compactar (por exemplo manter diário 90 dias, depois semanal) ou eliminar snapshots duplicadas sem mudanças.

-- Exemplo: eliminar snapshots sem alterações antigas
DELETE FROM fact_account_snapshot
WHERE updated_flag = 0
  AND snapshot_date < DATEADD(day, -365, CAST(GETDATE() AS date));

-- Depois da limpeza, repor updated_flag a 0 para a próxima carga
UPDATE fact_account_snapshot SET updated_flag = 0 WHERE updated_flag = 1;

Verificar o resultado

Confirme que existe um registo por (account_id, snapshot_date) e que apenas registos alterados foram actualizados.

-- Contagem por chave
SELECT account_id, snapshot_date, COUNT(*) AS cnt
FROM fact_account_snapshot
GROUP BY account_id, snapshot_date
HAVING COUNT(*) > 1;

-- Registos marcados como actualizados na última carga
SELECT * FROM fact_account_snapshot WHERE updated_flag = 1 ORDER BY snapshot_date DESC;

Erros comuns: esquecer a chave única (leva a duplicados), não comparar correctamente NULLs nas medidas, ou não definir política de retenção (crescimento infinito).

Conclusão

Implementar uma snapshot incremental em Modelação Kimball permite capturar estados ao longo do tempo com eficiência e controlo. Próximos passos: integrar a lógica numa pipeline automatizada (ETL/ELT), adicionar dimensões consoante necessidades analíticas e considerar compressão/particionamento para performance. Dica: comece por uma janela pequena de retenção e monitorize o crescimento antes de alargar.