Fact Table Grain Definition Patterns

Overview

Fact tables have a grain — the level of detail at which measurements are stored. Misunderstanding grain (transaction-level vs aggregated) causes 5% of Gold layer bugs but has high impact (complete table rewrite required). This skill provides patterns to define and document grain during the design phase so that implementation is unambiguous.

Key Principle: The PRIMARY KEY you choose in the YAML schema reveals the grain. Get this right during design and the merge script writes itself.

Companion skill: For runtime grain validation before MERGE, see pipeline-workers/04-grain-validation/SKILL.md.

When to Use This Skill

• Designing fact table YAML schemas (choosing grain type)
• Deciding between transaction, aggregated, or snapshot patterns
• Documenting grain in YAML table_properties and column comments
• Applying the PK-grain decision tree to determine aggregation needs
• Reviewing fact table designs for grain ambiguity

Understanding Fact Table Grain