Cómo crear una Fact table tipo 'Snapshot por Período' en Modelado de Datos (Kimball)
Este tutorial muestra cómo construir una Fact table del tipo "Snapshot por Período" en Modelado de Datos (Kimball) para capturar saldos o estados agregados en intervalos regulares. Es útil cuando necesita comparar posiciones en fechas fijas (día, fin de mes) sin recalcular toda la lógica en cada consulta.
Requisitos previos
- Conceptos básicos de Modelado de Datos (Kimball): Fact table, Dimension, granularidad.
- Acceso a una base de datos SQL (por ejemplo SQL Server o PostgreSQL) para crear tablas y ejecutar queries.
- Datos transaccionales con timestamps (ej.: movimientos financieros, inventario o eventos con cantidades).
Paso 1: Definir la granularidad y la frecuencia del snapshot
Elija la granularidad de la Fact table: por cuenta/cliente/producto y la frecuencia temporal (diaria, semanal, mensual). La granularidad determina las claves de la fact y los atributos que compondrán la dimensión temporal.
Paso 2: Crear o verificar las dimensiones necesarias
Creé las dimensiones conformed que la Fact va a referenciar: DimensionDate (una fila por día), DimensionAccount (o DimensionProduct/DimensionCustomer). DimensionDate debe tener columnas útiles 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 );
Paso 3: Determinar la regla de agregación del snapshot
Decida cómo calcular el valor del snapshot en la fecha de referencia: saldo hasta la medianoche, posición tras todos los movimientos del día, o último estado conocido. Esta regla debe ser consistente y documentada.
Paso 4: Extraer y preparar los datos transaccionales
Filtre y sume los movimientos hasta la fecha de snapshot por cada clave de granularidad. Normalmente se usa una query que agrega por cuenta y 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);
Paso 5: Generar snapshots para cada fecha del período
Para crear snapshots periódicos, repita la agregación para cada date_key deseada. En entornos ETL se usa un bucle de fechas o un join con DimensionDate para calcular acumulados hasta 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;
Paso 6: Cargar la Fact table Snapshot
Crear la Fact table con las claves y medidas: date_key, account_key, balance, load_timestamp y, opcionalmente, status. Defina la granularidad 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;
Paso 7: Planificación de carga incremental y mantenimiento
Defina un proceso ETL/ELT que actualice solo las fechas nuevas o modificadas. Para reducir costes, cargue solo date_keys nuevas (ej.: fin del mes corriente) y, si es necesario, reprocesar intervalos recientes cuando hay retroactivos.
-- Pseudocódigo para carga incremental -- 1) Determinar date_keys a cargar (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 el resultado
Confirme que la Fact table tiene una fila por combinación (date_key, account_key) en la granularidad elegida y que los saldos corresponden a sumas acumuladas hasta la fecha. Verifique muestras comparando con cálculos a partir de la tabla Transactions y valide fechas finales (ej.: is_month_end).
Conclusión
Una Fact table Snapshot por Período simplifica análisis temporales y reporting con consultas más rápidas y previsibles. Próximos pasos: añadir métricas derivadas (variaciones mes a mes), crear índices para consultas frecuentes e implementar políticas de reprocesamiento. Consejo: registre la lógica de agregación y la ventana temporal para evitar discrepancias futuras.