Chapter 2 · Practice 1 of 4
Pick the fact table type that matches the process
From Fact tables
Why it matters
Microsoft's guidance describes three types of fact table:
| Type | One row per | Example | Updated? |
|---|---|---|---|
| Transaction | Event | Each order line | Never, except to fix errors |
| Periodic snapshot | Entity per period | Stock level per product at end of day | New rows each period |
| Accumulating snapshot | Process instance | An order moving through placed, paid, shipped, delivered | Same row updated at each milestone |
Accumulating snapshots carry several date keys, one per milestone, and their measures are usually durations between milestones, such as days from payment to shipping. If you find yourself writing complicated self-joins over an events table to measure a workflow, an accumulating snapshot is probably the missing model.