Como criar uma Fact table de tipo 'Cumulative Balance' em Modelação de Dados (Kimball)

Este tutorial explica como criar uma Fact table de tipo 'Cumulative Balance' em Modelação de Dados (Kimball) para calcular saldos acumulados por conta/entidade. É útil quando precisa de consultar saldos diários ou mensais sem recalcular toda a história em cada consulta.

Pré-requisitos

• Conhecimentos básicos de Modelação de Dados (Kimball) e star schema.
• Ambiente SQL (ex.: SQL Server, Postgres) com tabelas transaccionais de movimentos.
• Familiaridade com ETL/ELT e com funções de janela (window functions) em SQL.

Passo 1: Entender o caso de uso e o grain

Decida o grain da Fact Cumulative Balance. Ex.: saldo diário por account_id e currency. O grain determina as dimensões de chave e a forma de agregação. Erro comum: escolher grain muito fino (ex.: por transacção) e depois tentar manter saldos acumulados nesse nível, o que complica as actualizações.

Passo 2: Definir as dimensões e atributos

Escolha dimensões conformed que irão ligar à fact: Dimension Account, Dimension Date, Dimension Currency, possivelmente Dimension Branch. Para um grain diário por conta: surrogate keys: account_key, date_key, currency_key. Inclua atributos úteis (account_type, region) na dimensão para filtro e agregação.

Passo 3: Estrutura da Fact table Cumulative Balance

Projete colunas essenciais: surrogate keys das dimensões, balance_amount (o saldo acumulado até à data), opening_balance, period_debit, period_credit, load_date, version_stamp. A Fact deve ser uma tabela de snapshot por data (um snapshot por account_id por date_key).

CREATE TABLE fact_cumulative_balance (
  account_key INT,
  date_key DATE,
  currency_key INT,
  opening_balance DECIMAL(18,2),
  period_debit DECIMAL(18,2),
  period_credit DECIMAL(18,2),
  balance_amount DECIMAL(18,2),
  load_date TIMESTAMP,
  PRIMARY KEY (account_key, date_key, currency_key)
);

Passo 4: Preparar a fonte de movimentos

Consolide a tabela de movimentos (transacções) agrupando por account e data. Inclua débitos e créditos e mapa de keys para as dimensões. Exemplo de agregação diária a partir de transactions_raw.

WITH daily_movements AS (
  SELECT
    a.account_key,
    CAST(t.transaction_date AS DATE) AS date_key,
    t.currency_key,
    SUM(CASE WHEN t.amount >= 0 THEN t.amount ELSE 0 END) AS period_debit,
    SUM(CASE WHEN t.amount < 0 THEN ABS(t.amount) ELSE 0 END) AS period_credit
  FROM transactions_raw t
  JOIN dim_account a ON t.account_id = a.account_id
  GROUP BY a.account_key, CAST(t.transaction_date AS DATE), t.currency_key
)
SELECT * FROM daily_movements;

Passo 5: Calcular saldos acumulados

Use funções de janela para calcular balance_amount como opening_balance + SUM(period_debit - period_credit) ao longo do tempo. Importante: definir correctamente a ordenação por date_key. Erro comum: não reiniciar acumulação por account_key e currency_key.

WITH daily AS (
  SELECT dm.*, a.opening_balance AS opening_balance_base
  FROM daily_movements dm
  LEFT JOIN (
    SELECT account_key, currency_key, MIN(balance_date) AS start_date, opening_balance
    FROM account_opening_balances
    GROUP BY account_key, currency_key, opening_balance
  ) a
  ON dm.account_key = a.account_key AND dm.currency_key = a.currency_key
), cumulative AS (
  SELECT
    account_key,
    date_key,
    currency_key,
    opening_balance_base AS opening_balance,
    period_debit,
    period_credit,
    SUM(period_debit - period_credit) OVER (
      PARTITION BY account_key, currency_key ORDER BY date_key
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) + COALESCE(opening_balance_base,0) AS balance_amount
  FROM daily
)
INSERT INTO fact_cumulative_balance
SELECT account_key, date_key, currency_key, opening_balance, period_debit, period_credit, balance_amount, CURRENT_TIMESTAMP
FROM cumulative;

Passo 6: Atualizações incrementais e correcções

Para cargas incrementais, carregue novas datas e, se existirem alterações em movimentos passados, reaplique a acumulação desde a primeira data afectada. Estratégia prática: manter um ponto de recuperação (watermark) e recalcular por account_key e date_key a partir da data mínima alterada.

-- Exemplo: recalcular a partir de min_changed_date por account
WITH changed_accounts AS (
  SELECT account_key, currency_key, MIN(affected_date) AS recalc_from
  FROM transaction_changes
  GROUP BY account_key, currency_key
)
-- apagar e recalcular para esse intervalo (pseudo-exemplo)
DELETE FROM fact_cumulative_balance f
USING changed_accounts c
WHERE f.account_key = c.account_key
  AND f.currency_key = c.currency_key
  AND f.date_key >= c.recalc_from;

-- depois recriar como no passo 5 apenas para datas >= recalc_from

Verificar o resultado

Valide com queries de soma e checks por account e total global: comparar balance_amount da última data com opening_balance + SUM(period_debit - period_credit) desde o início. Verifique unicidade da chave (account_key, date_key, currency_key) e datas com valores nulos.

-- Verificação básica
SELECT
  account_key,
  MAX(date_key) AS last_date,
  MAX(balance_amount) AS reported_balance,
  (SELECT COALESCE(opening_balance,0) + SUM(period_debit - period_credit)
   FROM fact_cumulative_balance f2
   WHERE f2.account_key = f1.account_key AND f2.currency_key = f1.currency_key
     AND f2.date_key <= f1.date_key) AS recomputed_balance
FROM fact_cumulative_balance f1
GROUP BY account_key, currency_key
HAVING MAX(balance_amount) <> (SELECT COALESCE(opening_balance,0) + SUM(period_debit - period_credit) FROM fact_cumulative_balance f3 WHERE f3.account_key = f1.account_key AND f3.currency_key = f1.currency_key AND f3.date_key <= MAX(f1.date_key));

Conclusão

Uma Fact table Cumulative Balance permite consultas rápidas de saldos históricos sem recalcular toda a história em cada consulta. Próximos passos: automatizar ETL de recalculação incremental, criar índices em (account_key,date_key) e expor em Power BI. Dica: comece por um subconjunto de contas para testar a lógica e monitorizar performance — qual a principal restrição no seu ambiente: I/O ou CPU?