Fact Table Types
The six major patterns for fact tables in dimensional modeling, each suited to different business measurement needs.
Last updated: 2026-04-12
Overview
Fact tables are the central tables in a star-schema. They record measurable business events. Choosing the right type depends on the grain (what each row represents) and how measurements evolve over time.
1. Transactional Fact Table
One row per individual transaction or event at the lowest grain. The most common type.
- Example: One row per sales line item, one row per web click, one row per claim submission
- Measures: Revenue, quantity, cost — typically fully additive
- When to use: Whenever events happen discretely and you want maximum detail
2. Periodic Snapshot Fact Table
One row per entity per standard time period, regardless of whether activity occurred.
- Example: Account balance at end of each month; inventory level at end of each day
- Measures: Semi-additive — can sum across products or stores but NOT across time periods (balance at end of Jan + balance at end of Feb ≠ meaningful number)
- When to use: When you need status at regular intervals, not just when events happen
3. Accumulating Snapshot Fact Table
One row per lifecycle instance (e.g., order, claim, application). The row is updated as the instance progresses through stages.
- Example: One row per insurance claim, with columns: filed_date_key, reviewed_date_key, approved_date_key, paid_date_key — each updated when that milestone occurs
- Measures: Cycle times (days between stages), amounts at each stage
- When to use: Pipeline/workflow processes with defined stages and a clear end state
- Note: The row is physically updated — rare in DWH, but correct here
4. Factless Fact Table
Records the occurrence of an event with no numeric measure — just dimension keys.
- Attendance factless: Students enrolled in courses (just student_key + course_key + date_key; enrollment is the fact)
- Coverage factless: Products that were on promotion on a given day (even if no sales occurred)
- When to use: Many-to-many relationships, or “what didn’t happen” questions (which products were on promotion but had zero sales?)
5. Aggregate Fact Table
A pre-summarized version of a transactional fact table, stored to accelerate common queries.
- Example: Monthly sales by product category and region, pre-calculated from daily transaction facts
- When to use: When query performance on the atomic fact table is too slow for common reporting queries
- Note: Must be kept in sync with the base fact table; often maintained by the ETL process
6. Consolidated Fact Table
Combines measures from multiple business processes into a single table that shares dimensions.
- Example: Combining sales and returns into one table so ratio queries don’t require a join
- When to use: When two closely related processes are always analyzed together
Additivity Summary
| Type | Additive | Semi-additive | Non-additive |
|---|---|---|---|
| Revenue | ✓ (sum across all dims) | ||
| Account balance | ✓ (not across time) | ||
| Ratios, percentages | ✓ (never sum) | ||
| Unit price | ✓ (use revenue/quantity) |
Connections
- dimensional-modeling — the methodology these patterns belong to
- star-schema — the schema these fact tables anchor
- slowly-changing-dimensions — how dimension context changes over time
Sources
- Dimensional Modeling — Joakim Dalby — added 2026-04-12