How to create a 'Cumulative Balance' Fact table in Data Modeling (Kimball)

João Barros 12 de November de 2026 5 min read

This tutorial explains how to create a 'Cumulative Balance' Fact table in Data Modeling (Kimball) to calculate cumulative balances by account/entity. It is useful when you need to query daily or monthly balances without recalculating the entire history on each query.

Prerequisites

Basic knowledge of Data Modeling (Kimball) and star schema.
SQL environment (e.g.: SQL Server, Postgres) with transactional movement tables.
Familiarity with ETL/ELT and with window functions in SQL.

Step 1: Understand the use case and the grain

Decide the grain of the Cumulative Balance Fact. E.g.: daily balance by account_id and currency. The grain determines the key dimensions and the aggregation method. Common mistake: choosing a too-fine grain (e.g.: per transaction) and then trying to maintain cumulative balances at that level, which complicates updates.

Step 2: Define the dimensions and attributes

Choose conformed dimensions that will link to the fact: Dimension Account, Dimension Date, Dimension Currency, possibly Dimension Branch. For a daily-per-account grain: surrogate keys: account_key, date_key, currency_key. Include useful attributes (account_type, region) in the dimension for filtering and aggregation.

Step 3: Structure of the Cumulative Balance Fact table

Design essential columns: surrogate keys of the dimensions, balance_amount (the cumulative balance up to the date), opening_balance, period_debit, period_credit, load_date, version_stamp. The Fact should be a snapshot table by date (one snapshot per account_id per 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)
);

Step 4: Prepare the movements source

Consolidate the movements (transactions) table by grouping by account and date. Include debits and credits and map keys to the dimensions. Example of daily aggregation from 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;

Step 5: Calculate cumulative balances

Use window functions to calculate balance_amount as opening_balance + SUM(period_debit - period_credit) over time. Important: correctly define ordering by date_key. Common mistake: not restarting accumulation by account_key and 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;

Step 6: Incremental updates and corrections

For incremental loads, load new dates and, if there are changes in past movements, reapply the accumulation from the first affected date. Practical strategy: maintain a recovery point (watermark) and recalculate per account_key and date_key from the minimum changed date.

-- 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

Verify the result

Validate with sum queries and checks by account and global total: compare balance_amount of the last date with opening_balance + SUM(period_debit - period_credit) since the beginning. Check uniqueness of the key (account_key, date_key, currency_key) and dates with null values.

-- 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));

Conclusion

A Cumulative Balance Fact table enables fast queries of historical balances without recalculating the entire history on each query. Next steps: automate incremental recalculation ETL, create indexes on (account_key,date_key) and expose in Power BI. Tip: start with a subset of accounts to test the logic and monitor performance — what is the main constraint in your environment: I/O or CPU?