Fact Types by Grain
The grain of a fact table defines exactly what a single row represents. Choosing the correct grain is one of the most important decisions in dimensional modeling.
Fact tables generally fall into three categories based on their grain:
Transaction Facts
A transaction fact table records individual business events at a defined grain.
- Characteristics: Highly detailed, potentially very large and often contain additive measures. Transaction fact tables may be dense or sparse because rows exist only if measurements take place.
- Example: One row per product sold per order line.
| transaction_date | store | customer | product | quantity | sales_amount |
|---|---|---|---|---|---|
| Jan 1 | Store A | Alice | Laptop | 1 | $1,000 |
| Jan 1 | Store A | Alice | Mouse | 1 | $50 |
Periodic Snapshot Facts
A periodic snapshot fact table records the state of a business process at regular intervals, such as daily, weekly or monthly. In Periodic Snapshots, the grain is the period, not the individual transaction.
- Characteristics: Regular intervals, predictable growth and often semi-additive measures. These fact tables are uniformly dense in their foreign keys because even if no activity takes place during the period, a row is typically inserted in the fact table containing a zero or null for each fact.
- Example: Daily inventory levels.
| snapshot_date | warehouse | product | quantity_on_hand | total_value |
|---|---|---|---|---|
| Jan 31 | WH 1 | Laptop | 50 | $50,000 |
| Feb 28 | WH 1 | Laptop | 45 | $45,000 |
Snapshots avoid repeatedly reconstructing historical states from large volumes of transactions.
Accumulating Snapshot Facts
An accumulating snapshot fact table tracks the lifecycle of a business process through predefined milestones. The row is updated as the process progresses.
- Characteristics: Multiple milestone date keys and often lag/duration measures.
- Example: Order fulfillment.
| order_id | placed_date | picked_date | shipped_date | delivered_date | days_to_ship |
|---|---|---|---|---|---|
| ORD-100 | Jan 1 | Jan 2 | Jan 3 | Jan 5 | 2 |
| ORD-101 | Jan 2 | Jan 3 | NULL | NULL | NULL |
ORD-101 is still in progress, so its later milestones remain NULL.
Lag/Duration Facts
Accumulating snapshot fact tables capture multiple milestones of a business process, typically using date foreign keys and, when needed, timestamps. Business users often need to analyze the time between these milestones.
When a process has many milestones, calculating every possible duration from the milestone dates can become complex. Instead, store the elapsed time from the process start to each milestone. Any duration between two milestones can then be calculated by simply subtracting their stored lags.
| order_id | placed_date | shipped_date | delivered_date | lag_to_ship | lag_to_deliver |
|---|---|---|---|---|---|
| ORD-100 | Jan 1 | Jan 3 | Jan 5 | 2 | 4 |
| ORD-101 | Jan 2 | Jan 5 | NULL | 3 | NULL |
For ORD-100, the time from shipping to delivery is: lag_to_deliver - lag_to_ship = 4 - 2 = 2 days
Timespan Tracking (Advanced Pattern)
The three basic fact table grains - transaction, periodic snapshot, and accumulating snapshot - cover the vast majority of business processes. In some specialized cases, however, a timespan-tracking fact can be useful.
A timespan-tracking fact stores the period during which a fact value is effective by adding an effective date, expiration date, and current-row indicator to the fact table. This resembles a Type 2 Slowly Changing Dimension, but here the technique is applied to a fact value rather than a descriptive dimension attribute.
This pattern can be useful for slowly changing balances or other state-based measures where the value remains unchanged for long periods. Instead of loading an identical periodic snapshot row every day, a new row is created only when the fact value changes.
| account_id | balance_amount | effective_date | expiration_date | is_current |
|---|---|---|---|---|
| ACC-123 | $5,000 | 2026-01-01 | 2026-03-14 | False |
| ACC-123 | $4,500 | 2026-03-15 | 9999-12-31 | True |
Here, the $5,000 balance was effective from January 1 through March 14, while the $4,500 balance became effective on March 15. No new row is required while the balance remains unchanged.
NOTE
This is an advanced and relatively uncommon pattern. It should be used when tracking the validity period of a fact value provides a clear benefit over a standard periodic snapshot.
Choosing the Fact Type
The three fact types differ primarily in what a row represents and how the data changes over time:
- Transaction → records an event
- Periodic Snapshot → records state at regular intervals
- Accumulating Snapshot → records a process lifecycle