Fact Table Design Patterns

Overview

Beyond basic transaction and aggregated fact tables (covered in 01-grain-definition), real-world dimensional models require specialized fact patterns to handle non-additive measures, coverage tracking, multi-stage processes, and late-arriving data. Choosing the wrong measure type or fact pattern leads to incorrect aggregations that silently produce wrong numbers in dashboards.

Key Principle: Store additive components in the fact table; compute non-additive metrics (ratios, percentages, averages) in the BI/semantic layer.

Companion skill: For grain type selection (transaction/aggregated/snapshot), see design-workers/01-grain-definition/SKILL.md.

When to Use This Skill

• Classifying measures as additive, semi-additive, or non-additive
• Designing factless fact tables (event tracking, coverage)
• Modeling multi-stage business processes (accumulating snapshots)
• Combining multiple processes into consolidated fact tables
• Handling late-arriving facts or dimensions in the design
• Deciding what to store vs what to compute at query time