Como criar uma Fact table tipo 'Snapshot por Período' em Modelação de Dados (Kimball)
Este tutorial mostra como construir uma Fact table do tipo "Snapshot por Período" em Modelação de Dados (Kimball) para capturar saldos ou estados agregados em intervalos regulares. É útil quando precisa de comparar posições em datas fixas (dia, fim de mês) sem recalcular toda a lógica a cada consulta.
Pré-requisitos
- Conceitos básicos de Modelação de Dados (Kimball): Fact table, Dimension, granularidade.
- Acesso a uma base de dados SQL (por exemplo SQL Server ou PostgreSQL) para criar tabelas e executar queries.
- Dados transaccionais com timestamps (ex.: movimentos financeiros, inventário ou eventos com quantidades).
Passo 1: Definir a granularidade e a frequência do snapshot
Escolha a granularidade da Fact table: por conta/cliente/produto e a frequência temporal (diária, semanal, mensal). A granularidade determina as chaves da fact e os atributos que vão compor a dimensão temporal.
Passo 2: Criar ou verificar as dimensões necessárias
Crie as dimensões conformed que a Fact vai referenciar: DimensionDate (uma linha por dia), DimensionAccount (ou DimensionProduct/DimensionCustomer). DimensionDate deve ter colunas úteis como calendar_date, month_key, is_month_end.
-- Exemplo simplificado para DimensionDate (PostgreSQL/SQL Server compatível) CREATE TABLE DimensionDate ( date_key INT PRIMARY KEY, -- formato YYYYMMDD calendar_date DATE, year INT, month INT, day INT, is_month_end BIT );
Passo 3: Determinar a regra de agregação do snapshot
Decida como calcular o valor do snapshot na data de referência: saldo até à meia-noite, posição após todos os movimentos do dia, ou último estado conhecido. Esta regra deve ser consistente e documentada.
Passo 4: Extrair e preparar os dados transaccionais
Filtre e sume os movimentos até à data de snapshot por cada chave de granularidade. Normalmente usa-se uma query que agrega por conta e por date_key.
-- Exemplo: sumarizar movimentos até à data de snapshot (fim do dia) SELECT account_id, CAST(to_char(movement_date,'YYYYMMDD') AS INT) AS date_key, SUM(amount) AS movement_sum FROM Transactions WHERE movement_date <= :snapshot_date -- parâmetro para cada data do calendário GROUP BY account_id, CAST(to_char(movement_date,'YYYYMMDD') AS INT);
Passo 5: Gerar snapshots para cada data do período
Para criar snapshots periódicos, repita a agregação para cada date_key desejada. Em ambientes ETL usa-se um loop de datas ou um join com DimensionDate para calcular cumulativos até cada date_key.
-- Exemplo: criar snapshot cumulativo por cada date_key usando join com DimensionDate SELECT d.date_key, t.account_id, SUM(t.amount) OVER (PARTITION BY t.account_id ORDER BY t.movement_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS balance_to_date FROM Transactions t JOIN DimensionDate d ON t.movement_date <= d.calendar_date -- Depois agrega por date_key e account_id GROUP BY d.date_key, t.account_id;
Passo 6: Carregar a Fact table Snapshot
Crie a Fact table com as chaves e medidas: date_key, account_key, balance, load_timestamp e, opcionalmente, status. Defina a granularidade como (account_key, date_key).
CREATE TABLE FactSnapshot ( date_key INT, account_key INT, balance DECIMAL(18,2), load_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (date_key, account_key) ); -- Inserção típica (exemplo simplificado) INSERT INTO FactSnapshot (date_key, account_key, balance) SELECT date_key, account_id, SUM(amount) FROM AggregatedSnapshots GROUP BY date_key, account_id;
Passo 7: Planeamento de carga incremental e manutenção
Defina um processo ETL/ELT que atualize apenas as datas novas ou alteradas. Para reduzir custos, carregue apenas date_keys novas (ex.: fim do mês corrente) e, se necessário, reprocessar intervalos recentes quando há retroativos.
-- Pseudocódigo para carga incremental -- 1) Determinar date_keys a carregar (novos ou alterados) -- 2) Recalcular agregação até esses date_keys -- 3) UPsert para FactSnapshot (INSERT ou UPDATE) MERGE INTO FactSnapshot AS target USING (SELECT ...) AS src ON target.date_key = src.date_key AND target.account_key = src.account_key WHEN MATCHED THEN UPDATE SET balance = src.balance, load_timestamp = CURRENT_TIMESTAMP WHEN NOT MATCHED THEN INSERT (...);
Verificar o resultado
Confirme que a Fact table tem uma linha por combinação (date_key, account_key) na granularidade escolhida e que os saldos correspondem a somas cumulativas até à data. Verifique amostras comparando com cálculos a partir da tabela Transactions e valide datas finais (ex.: is_month_end).
Conclusão
Uma Fact table Snapshot por Período simplifica análises temporais e reporting com consultas mais rápidas e previsíveis. Próximos passos: adicionar métricas derivadas (variações mês a mês), criar índices para consultas frequentes e implementar políticas de reprocessamento. Dica: registe a lógica de agregação e a janela temporal para evitar discrepâncias futuras.