How to create a Periodic Snapshot Fact table in Data Modeling (Kimball)
This tutorial shows how to build a "Periodic Snapshot" Fact table in Data Modeling (Kimball) to capture balances or aggregated states at regular intervals. It is useful when you need to compare positions on fixed dates (day, month-end) without recalculating all the logic for every query.
Prerequisites
- Basic concepts of Data Modeling (Kimball): Fact table, Dimension, granularity.
- Access to a SQL database (for example SQL Server or PostgreSQL) to create tables and run queries.
- Transactional data with timestamps (e.g.: financial movements, inventory or events with quantities).
Step 1: Define the snapshot granularity and frequency
Choose the Fact table granularity: by account/customer/product and the temporal frequency (daily, weekly, monthly). Granularity determines the fact keys and the attributes that will compose the time dimension.
Step 2: Create or verify the required dimensions
Create the conformed dimensions that the Fact will reference: DimensionDate (one row per day), DimensionAccount (or DimensionProduct/DimensionCustomer). DimensionDate should have useful columns such as 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 );
Step 3: Determine the snapshot aggregation rule
Decide how to calculate the snapshot value on the reference date: balance up to midnight, position after all movements of the day, or last known state. This rule should be consistent and documented.
Step 4: Extract and prepare the transactional data
Filter and sum the movements up to the snapshot date for each granularity key. Typically you use a query that aggregates by account and by 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);
Step 5: Generate snapshots for each date in the period
To create periodic snapshots, repeat the aggregation for each desired date_key. In ETL environments you use a date loop or a join with DimensionDate to compute cumulatives up to each 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;
Step 6: Load the Snapshot Fact table
Create the Fact table with keys and measures: date_key, account_key, balance, load_timestamp and, optionally, status. Define the granularity as (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;
Step 7: Plan incremental load and maintenance
Define an ETL/ELT process that updates only new or changed dates. To reduce costs, load only new date_keys (e.g.: current month-end) and, if necessary, reprocess recent intervals when there are backdated changes.
-- Pseudocódigo para carga incremental -- 1) Determinar date_keys a carregar (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 (...);
Verify the result
Confirm that the Fact table has one row per combination (date_key, account_key) at the chosen granularity and that the balances match cumulative sums up to the date. Check samples comparing with calculations from the Transactions table and validate period ends (e.g.: is_month_end).
Conclusion
A Periodic Snapshot Fact table simplifies temporal analyses and reporting with faster and more predictable queries. Next steps: add derived metrics (month-over-month variations), create indexes for frequent queries and implement reprocessing policies. Tip: document the aggregation logic and the time window to avoid future discrepancies.