Types of Fact Tables: Transaction, Periodic Snapshot, Accumulating

Not everything a business measures is an event that happens and is over. Some things are levels that exist at every moment, like a bank balance or the stock on a shelf. Some are processes with a start, a middle and an end, like an order being fulfilled or a loan being approved. Kimball gave us three fact-table types for these, and any mature warehouse runs all three side by side. Which one to pick comes up in interviews, and it comes up on the job roughly once a week.

1. Transaction fact table

One row per event, written once, never updated. A sale, a payment, a call, a click, a bank transfer. This is the default and by far the most common type. Rows are sparse. If a customer did nothing on Tuesday, there's no Tuesday row for them. Facts are usually fully additive.

It gives you maximum detail and answers almost anything. Some questions are awkward, though. "What was every account's balance at month end?" means replaying every transaction from the start of time.

2. Periodic snapshot fact table

One row per thing per period, written at the end of each period, even if nothing happened. Stock level per product per store per day. Account balance per account per month. Headcount per department per month. Outstanding fee balance per student per term. Rows are dense. Every product, every day, whether it sold or not.

Facts here are often semi-additive. You can add stock across stores (total stock in Lahore) but not across days (Monday's stock plus Tuesday's stock means nothing). Over time you take the last value, the average or the minimum, and BI tools have functions for this (LASTNONBLANK in DAX, for example).

Snapshots are usually derived from transactions or copied from the source's current state each night. They trade storage for query simplicity. Month-end balance becomes a filter, not a replay.

3. Accumulating snapshot fact table

One row per process instance, created at the start and updated as each milestone is reached. An e-commerce order goes placed, paid, packed, shipped, delivered. A loan goes applied, verified, approved, disbursed. A university admission goes applied, tested, interviewed, offered, enrolled. The row has one date key per milestone (most of them NULL at first) and lag measures such as days from placed to shipped.

This is the only fact type where updating rows is normal. The pipeline finds each open process instance and fills in newly reached milestones. Once the final milestone is set, the row stops changing. Reports on pipeline health ("orders stuck at packed for more than 48 hours", "average approval time by branch") become trivial.

Comparison

	Transaction	Periodic snapshot	Accumulating snapshot
Grain	One event	One thing per period	One process instance
Date keys	One (event date)	One (period end)	Many (one per milestone)
Row updates	Never	Never	Yes, until complete
Density	Sparse	Dense	One row per instance
Additivity	Fully additive	Semi-additive over time	Lags additive; counts by status
Typical size	Largest	Medium to large	Smallest
Examples	Sales, payments, calls	Balances, stock, headcount	Orders, claims, admissions

Three tables for one wallet business

-- 1. Transaction: every wallet movement
CREATE TABLE dw.fact_wallet_txn (
  txn_id        TEXT PRIMARY KEY,
  date_key      INT NOT NULL,
  account_key   INT NOT NULL,
  txn_type_key  INT NOT NULL,        -- cash-in, send, bill, withdraw
  amount        NUMERIC(14,2) NOT NULL,  -- signed: negative for debits
  fee           NUMERIC(10,2) NOT NULL
);

-- 2. Periodic snapshot: balance per account at each day end
CREATE TABLE dw.fact_wallet_balance_daily (
  date_key       INT NOT NULL,
  account_key    INT NOT NULL,
  closing_balance NUMERIC(14,2) NOT NULL,   -- semi-additive
  txn_count       INT NOT NULL,             -- additive
  PRIMARY KEY (date_key, account_key)
);

-- 3. Accumulating snapshot: agent onboarding process
CREATE TABLE dw.fact_agent_onboarding (
  application_id   TEXT PRIMARY KEY,
  agent_key        INT NOT NULL,
  applied_date_key   INT NOT NULL,
  verified_date_key  INT,
  trained_date_key   INT,
  activated_date_key INT,
  days_to_verify     INT,
  days_to_activate   INT,
  current_status     TEXT NOT NULL
);

-- Month-end balances: a filter, not a replay
SELECT d.year, d.month, SUM(b.closing_balance) AS total_float_pkr
FROM dw.fact_wallet_balance_daily b
JOIN dw.dim_date d ON d.date_key = b.date_key
WHERE d.is_last_day_of_month
GROUP BY d.year, d.month;

-- Onboarding bottleneck: applications waiting for training more than 14 days
SELECT COUNT(*) AS stuck
FROM dw.fact_agent_onboarding
WHERE verified_date_key IS NOT NULL
  AND trained_date_key IS NULL
  AND CURRENT_DATE - TO_DATE(verified_date_key::TEXT, 'YYYYMMDD') > 14;

Note the signed amount in the transaction table. Storing debits as negative numbers keeps the fact fully additive, so SUM(amount) over any period is the net movement. One warning from experience. Write the sign convention down and enforce it in the load, because the first time someone loads refunds as positive you'll spend a day finding out why the float grew.

The regulator asks for two years of daily float: a wallet operator we know of had only a transaction fact. The regulator asked for total customer float at the close of every day for the last two years. Replaying 1.4 billion transactions took a whole weekend, and the numbers still didn't match the finance ledger because of a handful of backdated corrections. The team added a daily balance snapshot, loaded each night from the core system's balance table and reconciled to the ledger. The regulatory report became a five-second query, and the reconciliation caught two more bugs in the source along the way.

Which one, when

• Start with a transaction fact for every process. It's the raw truth.
• Add a periodic snapshot when users ask "how much was there at time X" and replay is slow or the source doesn't keep history of levels.
• Add an accumulating snapshot when users ask about durations between steps or where things are stuck.

Where this leaves you

Transaction facts are your default, one immutable row per event. Periodic snapshots handle balances and stock, one dense row per thing per period, semi-additive over time. Accumulating snapshots handle processes, one row per instance with milestone dates, updated in place. Store debits as negatives and flags as 1/0, and expect a real warehouse to use all three for the same business.

Do this tonight

1. For a private school, name one measurement that suits each fact type (transaction, periodic snapshot, accumulating snapshot) and write the grain sentence for each.
2. Explain why summing closing_balance across 30 days is wrong, then write the correct query for "average daily float in August 2025".
3. Design an accumulating snapshot for a bank's home-loan process with at least five milestones and two lag measures. Which report would it make easy?