# Three Kinds of Fact Tables: Transaction, Snapshot and Accumulating

> Most facts fit one of three patterns. Picking the right one decides whether questions like 'what was the balance on the 1st?' are easy or impossible.

- Source: https://akshaythakare.com/notes/data-modeling/fact-table-types
- Topic: Data Modeling
- Level: intermediate
- Reading time: 3 min
- Tags: data-modeling, dimensional-modeling, facts, interview
- Updated: 2026-10-11

## Why the type matters

A fact table records measurements at a declared grain. Kimball-style modelling recognises three common shapes. Each answers different questions cheaply, and choosing the wrong one makes some questions expensive or impossible.

| Type | One row per | Typical measures | Rows change? |
| --- | --- | --- | --- |
| Transaction | event (order line, payment, click) | amount, quantity | No — insert only |
| Periodic snapshot | entity per period (account per day) | balance, stock on hand | No — new row each period |
| Accumulating snapshot | process instance (one order's lifecycle) | dates and durations per milestone | Yes — updated as milestones happen |

## Transaction facts

The default. One row per business event at the finest grain you can get.

```sql
-- fact_order_line: one row per order line
order_line_id, order_date_key, customer_key, product_key, quantity, line_amount
```

Fully additive: you can `SUM(line_amount)` across any dimension. But "what was each customer's open balance on 1 March?" requires replaying every event up to that date.

## Periodic snapshot facts

One row per entity per period, capturing state *as of* the end of the period.

```sql
-- fact_account_daily: one row per account per day
snapshot_date_key, account_key, closing_balance, transactions_count
```

> **Warning: Semi-additive measures**
>
> A balance can be summed across accounts, but **not across time**. Adding daily balances for a month is meaningless — take the last value or the average instead.

Snapshots trade storage for speed: point-in-time questions become a simple filter on `snapshot_date_key`.

## Accumulating snapshot facts

One row per instance of a process with a clear start and end, updated as it moves through milestones.

```sql
-- fact_order_fulfilment: one row per order, updated in place
order_key, ordered_date_key, packed_date_key, shipped_date_key, delivered_date_key,
hours_to_ship, hours_to_deliver
```

```mermaid
flowchart TB
  accTitle: Accumulating snapshot milestones
  accDescr: An order row is inserted when ordered and updated when packed, shipped and delivered.
  O[Ordered: insert row] --> P[Packed: update] --> S[Shipped: update] --> D[Delivered: update]
```

Great for pipeline and lag analysis ("average hours from order to ship"), but it requires updates — use `MERGE` and keep it idempotent.

## How to choose

- **Need every event or flexible slicing?** Transaction fact.
- **Need state at regular points in time?** Periodic snapshot.
- **Need durations between known milestones?** Accumulating snapshot.

Many warehouses use all three for the same domain: transaction facts as the source of truth, with snapshots derived from them for fast reporting.