01-grain-definition

Fact table grain defines the level of detail stored in a data warehouse table. You define it by identifying whether the table records transaction-level, aggregated, or snapshot data, ensuring unambiguous primary keys and measures from the start.

Transaction grain records one row per business event, aggregated grain groups measures to a specific level like daily sales per product, and snapshot grain captures the state of measures at a specific point in time.

Document fact table grain in YAML schemas by explicitly declaring the grain type and listing the primary key columns. This enforces unambiguous implementation and prevents merge logic errors during Gold layer design.

Grain ambiguity causes data warehouse rewrites because it leads to incorrect primary keys and merge logic. When fact table grain is not explicitly defined during the Gold layer design phase, measures are aggregated or joined incorrectly.

You need to define grain for Gold layer fact tables during the design phase before implementation. Defining grain early prevents costly rewrites by ensuring the correct primary keys and merge logic are applied from the start.