Cómo crear una Fact table de tipo 'Cumulative Balance' en Modelado de Datos (Kimball)

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

Este tutorial explica cómo crear una Fact table de tipo 'Cumulative Balance' en Modelado de Datos (Kimball) para calcular saldos acumulados por cuenta/entidad. Es útil cuando necesita consultar saldos diarios o mensuales sin recalcular toda la historia en cada consulta.

Pre-requisitos

Conocimientos básicos de Modelado de Datos (Kimball) y star schema.
Entorno SQL (ej.: SQL Server, Postgres) con tablas transaccionales de movimientos.
Familiaridad con ETL/ELT y con funciones de ventana (window functions) en SQL.

Paso 1: Entender el caso de uso y el grain

Decida el grain de la Fact Cumulative Balance. Ej.: saldo diario por account_id y currency. El grain determina las dimensiones de clave y la forma de agregación. Error común: elegir un grain demasiado fino (ej.: por transacción) y luego intentar mantener saldos acumulados a ese nivel, lo que complica las actualizaciones.

Paso 2: Definir las dimensiones y atributos

Elija dimensiones conformed que van a enlazarse con la fact: Dimension Account, Dimension Date, Dimension Currency, posiblemente Dimension Branch. Para un grain diario por cuenta: surrogate keys: account_key, date_key, currency_key. Incluya atributos útiles (account_type, region) en la dimensión para filtrado y agregación.

Paso 3: Estructura de la Fact table Cumulative Balance

Diseñe columnas esenciales: surrogate keys de las dimensiones, balance_amount (el saldo acumulado hasta la fecha), opening_balance, period_debit, period_credit, load_date, version_stamp. La Fact debe ser una tabla de snapshot por fecha (un 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)
);

Paso 4: Preparar la fuente de movimientos

Consolide la tabla de movimientos (transacciones) agrupando por cuenta y fecha. Incluya débitos y créditos y mapeo de keys para las dimensiones. Ejemplo de agregación diaria 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;

Paso 5: Calcular saldos acumulados

Use funciones de ventana para calcular balance_amount como opening_balance + SUM(period_debit - period_credit) a lo largo del tiempo. Importante: definir correctamente el orden por date_key. Error común: no reiniciar la acumulación por account_key y 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;

Paso 6: Actualizaciones incrementales y correcciones

Para cargas incrementales, cargue nuevas fechas y, si existen cambios en movimientos pasados, reaplique la acumulación desde la primera fecha afectada. Estrategia práctica: mantener un punto de recuperación (watermark) y recalcular por account_key y date_key a partir de la fecha mínima modificada.

-- 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 y recalcular para ese intervalo (pseudo-ejemplo)
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 el resultado

Valide con queries de suma y checks por account y total global: comparar balance_amount de la última fecha con opening_balance + SUM(period_debit - period_credit) desde el inicio. Verifique unicidad de la clave (account_key, date_key, currency_key) y fechas con valores nulos.

-- Verificación 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));

Conclusión

Una Fact table Cumulative Balance permite consultas rápidas de saldos históricos sin recalcular toda la historia en cada consulta. Próximos pasos: automatizar ETL de recálculo incremental, crear índices en (account_key,date_key) y exponer en Power BI. Consejo: comience con un subconjunto de cuentas para probar la lógica y monitorizar el rendimiento — ¿cuál es la principal restricción en su entorno: I/O o CPU?