How to create a 'Periodic Snapshot' Fact table in Data Modeling (Kimball)

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

This tutorial teaches how to create a Periodic Snapshot Fact table in Data Modeling (Kimball) to store periodic metrics (for example, daily account balance). The Periodic Snapshot is useful for trend reporting and reconciliation when events do not capture the state at each period.

Prerequisites

Basic SQL knowledge (SELECT, JOIN, GROUP BY).
Understanding of Kimball concepts: fact tables, dimension tables, surrogate keys.
A database for testing (e.g.: SQL Server, PostgreSQL) with source tables.

Step 1: Define the objective and granularity

Explain why you need a Periodic Snapshot: do you want to record the state at the end of each day, week or month? Granularity determines the fact dimensions and load frequency. Also decide which metrics are snapshots (for example, balance, number of active users) and which are accumulative.

Step 2: Identify dimensions and the grain key

Choose the dimensions that describe the grain. For a daily account snapshot, the grain can be: one record per date + account_id. Common dimensions: DimDate, DimAccount, DimBranch, DimProduct. Create or confirm the surrogate keys of the dimensions.

Step 3: Periodic Snapshot fact table schema

Define columns: surrogate keys for dimensions, snapshot_date, metrics (balance, number of transactions in the day) and indicators (flags). Include audit columns (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
);

Step 4: Map sources and calculate state per period

Identify source tables and the logic to calculate the state at the end of the period. For example, calculating the daily balance may sum transactions up to the end of the day or use a current balance column if it exists.

-- Example: calculate balance per account at day end by summing transactions
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 -- simplification example
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;

Step 5: Load strategy (ETL/ELT)

Decide whether to perform a full daily load (recreate that day's records) or incremental (delete/regenerate per grain). The safest approach for snapshots is to load by period: delete records for SnapshotDate and grain and insert the new ones. Automate with transactions for atomicity.

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 (... previous calculation ...) t
  JOIN DimAccount a ON a.account_id = t.account_id
  JOIN DimDate d ON d.FullDate = @SnapshotDate;
COMMIT;

Step 6: Handle common mistakes

Frequent mistakes: 1) poorly defined grain (duplicates by SnapshotDate/account); 2) not using surrogate keys from dimensions; 3) partial load without cleanup causing stale values; 4) performance when joining large tables. To avoid, implement uniqueness constraints (DateSK+AccountSK+SnapshotDate), indexes and partitioning by SnapshotDate if supported.

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

-- Index to improve queries by date
CREATE INDEX IX_FactAccountDailySnapshot_SnapshotDate ON FactAccountDailySnapshot(SnapshotDate);

Verify the result

Validate integrity and values: 1) confirm uniqueness by grain; 2) reconcile aggregated sums with source systems for multiple dates; 3) test trend query (e.g.: balance per account in the last 30 days). Example checks in SQL:

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

-- 2. Reconcile total balance (sample)
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;

Conclusion

A Periodic Snapshot in Data Modeling (Kimball) captures regular states for trend analysis and auditing. Next steps: add other metrics, partition the table for scale and automate the load with a scheduler. Tip: start with daily loads in a test environment and always verify reconciliations before production — which metric do you want to capture first?