Choosing fact table grain: the one sentence that decides the model

A grain statement is a sentence that starts with one row per. One row per order line. One row per completed shipment. One row per subscription per calendar month. One row per support ticket state change. The test of a good grain sentence is that a business person can read it and say whether it matches how they count things.

Vague grain statements are the ones that cause damage, because they sound decided. One row per order is ambiguous the moment an order can be partially shipped or partially refunded. One row per customer is not a grain at all for a fact table, it is a dimension. If the sentence needs a paragraph of clarification, the grain has not been chosen yet.

Put the sentence in the model file and enforce it with a uniqueness test on the columns that define it. That turns the grain from a convention into something CI can defend on the day somebody adds a row at a different level of detail.

# models/marts/sales/_models.yml
models:
  - name: fct_order_lines
    description: "Grain: one row per order line, per shipment event."
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - order_line_id
            - shipment_event_id
    columns:
      - name: net_amount
        description: "Additive at the declared grain."
      - name: order_total_amount
        description: "NOT additive here: repeats per line. Use fct_orders."

The default advice is to model at the atomic grain, the finest level the source records. Atomic grain is future-proof in a way aggregates are not: any question that can be answered from a summary can be answered from the atoms, and questions nobody thought of at design time are exactly the ones that arrive later. Aggregating is always possible, disaggregating never is.

The exception is volume that outruns the value. Raw clickstream at one row per event is atomic and is also the reason many warehouses have a table nobody can query without a warning. When the atomic grain is genuinely unaffordable, keep the atoms in the lake or in a lower layer and build the fact table at the finest grain the business actually slices, then document that the atoms exist elsewhere and where to find them.

Building several fact tables at different grains for the same process is normal and correct: an atomic transaction fact, a daily periodic snapshot, and possibly a monthly aggregate. What is not correct is mixing grains inside one table. A table that holds both order lines and order totals will double count on any join, and the person who discovers it will not be you.

Every measure needs a distinct. If your sum queries look like sum of distinct amounts, rows are repeating for a reason the grain does not account for, usually because a dimension attaches at a finer level than the fact.

Totals inflate when a dimension is added to a report. Adding a product filter changes the revenue total, which means the fact rows fan out across product and the declared grain was coarser than the data.

Half the columns are null for half the rows. Two different events have been packed into one table because they share a source system. Split them, or add the event type as part of the grain sentence and accept the nulls explicitly.

The business asks a question the table cannot answer without going back to the source. That is the aggregation trap and it costs a rebuild plus a backfill. It is also the strongest argument for the atomic grain.

Transaction facts record something that happened. Grain: one row per event. They are the most common, they are sparse, and their measures are usually fully additive.

Periodic snapshot facts record a state at regular intervals. Grain: one row per entity per period, for example one row per account per day. They are dense by construction, and their measures are typically semi-additive, meaning a balance can be summed across accounts but not across days.

Accumulating snapshot facts track a process with a known set of milestones. Grain: one row per process instance, for example one row per order, with the row updated as it moves through the milestones. They are the only fact type that is regularly updated rather than appended, which matters for how they are stored and reprocessed.

Labelling each fact node with both its type and its grain sentence is the cheapest documentation in the whole model. On the Datadef canvas that sentence is the node description, so a modeling review starts from what the rows mean rather than from a column list, and column-level lineage traces a measure back to the source column that produced it. validate_canvas will tell you when you skipped it: it raises a warning once more than half the nodes on a canvas have no description, and another for any table node drawn with no columns at all, on the grounds that columns are the point of a table node.

• Kimball dimensional modeling
• How to design a data warehouse
• Star schema vs one big table
• Data quality best practices
• Column-level lineage