01-grain-definition

Fact table grain defines the exact level of detail stored in a data warehouse table. You need to define grain during the Gold layer design phase to prevent ambiguous data models and costly table rewrites.

To document fact table grain in YAML schemas, explicitly define the primary key structure and grain type, such as transaction or aggregated, ensuring unambiguous fact table design before implementation.

Common fact table grain patterns include transaction, aggregated, and snapshot fact tables. Selecting the correct pattern uses a PK-grain decision tree to clarify the level of detail and primary key structure.

To decide between transaction and aggregated fact table grain, apply a PK-grain decision tree during Gold layer design to determine if individual transactions or daily aggregated summaries best fit the schema.

Ambiguous fact table grain causes data warehouse rewrites because the primary key and level of detail are unclear, leading to incorrect aggregations and requiring costly schema restructuring after implementation.

You can apply dimensional modeling grain definition to any data warehouse. It provides pattern guidance for transaction and snapshot fact tables and enforces explicit documentation in standard YAML schemas.