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

João Barros 16 de August de 2026 4 min de lectura

Este tutorial enseña a crear una Fact table del tipo Periodic Snapshot en Modelado de Datos (Kimball) para almacenar métricas periódicas (por ejemplo, saldo diario de cuenta). La Periodic Snapshot es útil para informes de tendencia y conciliación cuando los eventos no capturan el estado en cada período.

Prerequisitos

Conocimientos básicos de SQL (SELECT, JOIN, GROUP BY).
Comprensión de conceptos Kimball: fact tables, dimension tables, claves surrogate.
Una base de datos para pruebas (p. ej.: SQL Server, PostgreSQL) con tablas de origen.

Paso 1: Definir el objetivo y la granularidad

Explique por qué necesita una Periodic Snapshot: ¿quiere registrar el estado al final de cada día, semana o mes? La granularidad determina las dimensiones de la fact y la frecuencia de carga. Decida también qué métricas son snapshots (por ejemplo, saldo, número de usuarios activos) y cuáles son acumuladas.

Paso 2: Identificar dimensiones y clave de grain

Elija las dimensiones que describen el grain. Para un snapshot diario de cuenta, el grain puede ser: un registro por fecha + account_id. Las dimensiones comunes: DimDate, DimAccount, DimBranch, DimProduct. Cree o confirme las surrogate keys de las dimensiones.

Paso 3: Esquema de la Periodic Snapshot fact table

Defina columnas: surrogate keys para dimensiones, snapshot_date, métricas (saldo, número de transacciones en el día) e indicadores (flags). Incluya columnas de auditoría (load_date, source_system).

CREATE TABLE FactAccountDailySnapshot (
  FactSnapshotSK BIGINT IDENTITY(1,1) PRIMARY KEY,
  DateSK INT NOT NULL,
  AccountSK INT NOT NULL,
  BranchSK INT NULL,
  SnapshotDate DATE NOT NULL,
  Balance DECIMAL(18,2) NULL,
  DailyTxCount INT NULL,
  LoadDate DATETIME NOT NULL DEFAULT GETDATE(),
  SourceSystem VARCHAR(50) NULL
);

Paso 4: Mapear fuentes y calcular estado por período

Identifique las tablas fuente y la lógica para calcular el estado al final del período. Por ejemplo, calcular el saldo diario puede sumar transacciones hasta el final del día o usar una columna de saldo corriente si existe.

-- Exemplo: calcular saldo por account no fim do dia somando transacções
WITH TxUntilDay AS (
  SELECT
    account_id,
    CAST(transaction_date AS DATE) AS txn_date,
    SUM(amount) AS day_amount
  FROM StgTransactions
  WHERE transaction_date <= @SnapshotDate + '23:59:59'
  GROUP BY account_id, CAST(transaction_date AS DATE)
)
SELECT a.account_id, d.DateSK, a.AccountSK,
       ISNULL(t.day_amount,0) AS Balance -- simplificação de exemplo
FROM DimAccount a
CROSS JOIN (SELECT @SnapshotDate AS SnapshotDate, DateSK FROM DimDate WHERE FullDate = @SnapshotDate) d
LEFT JOIN TxUntilDay t ON t.account_id = a.account_id AND t.txn_date = @SnapshotDate;

Paso 5: Estrategia de carga (ETL/ELT)

Decida si realiza carga completa diaria (recrea los registros de ese día) o incremental (elimina/regenera por grain). El enfoque más seguro para snapshots es cargar por período: eliminar registros de SnapshotDate y grain e insertar los nuevos. Automatice con transacciones para atomicidad.

BEGIN TRANSACTION;
  DELETE FROM FactAccountDailySnapshot
  WHERE SnapshotDate = @SnapshotDate;

  INSERT INTO FactAccountDailySnapshot (DateSK, AccountSK, BranchSK, SnapshotDate, Balance, DailyTxCount, SourceSystem)
  SELECT d.DateSK, a.AccountSK, a.BranchSK, @SnapshotDate, t.Balance, t.DailyTxCount, 'OLTP'
  FROM (... cálculo anterior ...) t
  JOIN DimAccount a ON a.account_id = t.account_id
  JOIN DimDate d ON d.FullDate = @SnapshotDate;
COMMIT;

Paso 6: Manejar errores comunes

Errores frecuentes: 1) grain mal definido (duplicados por SnapshotDate/account); 2) no utilizar surrogate keys de las dimensiones; 3) carga parcial sin limpieza que causa valores antiguos; 4) rendimiento en la unión con tablas grandes. Para evitarlo, implemente constraints de unicidad (DateSK+AccountSK+SnapshotDate), índices y partición por SnapshotDate si es compatible.

ALTER TABLE FactAccountDailySnapshot
ADD CONSTRAINT UQ_FactSnapshot_Date_Account UNIQUE (DateSK, AccountSK, SnapshotDate);

-- Índice para mejorar consultas por data
CREATE INDEX IX_FactAccountDailySnapshot_SnapshotDate ON FactAccountDailySnapshot(SnapshotDate);

Verificar el resultado

Valide integridad y valores: 1) confirmar unicidad por grain; 2) conciliar sumas agregadas con sistemas fuente para varias fechas; 3) probar query de tendencia (p. ej.: saldo por account en los últimos 30 días). Ejemplos de checks en SQL:

-- 1. Verificar duplicados
SELECT DateSK, AccountSK, SnapshotDate, COUNT(*)
FROM FactAccountDailySnapshot
GROUP BY DateSK, AccountSK, SnapshotDate
HAVING COUNT(*) > 1;

-- 2. Reconciliar total de saldo (amostra)
SELECT f.SnapshotDate, SUM(f.Balance) AS TotalBalance
FROM FactAccountDailySnapshot f
WHERE f.SnapshotDate BETWEEN '2026-07-01' AND '2026-07-31'
GROUP BY f.SnapshotDate
ORDER BY f.SnapshotDate;

Conclusión

Una Periodic Snapshot en Modelado de Datos (Kimball) captura estados regulares para análisis de tendencia y auditoría. Próximos pasos: agregar otras métricas, particionar la tabla para escala y automatizar la carga con un scheduler. Consejo: comienza con cargas diarias en entorno de prueba y verifica siempre las conciliaciones antes de producción — ¿qué métrica quieres capturar primero?