The grain of a table is a precise statement of what one row represents. "One row per order line." "One row per account per day." "One row per claim line per adjudication event." If you can say that sentence and every row in the table matches it, the table has a declared grain. If you cannot, the table will eventually produce a wrong number.
Grain is the single most important decision in dimensional modeling. Ralph Kimball's four-step design process puts it second, right after choosing the business process, and before you pick a single dimension or fact. That order is deliberate: the grain decides which dimensions can attach to the table and which measures are valid on it. Get it wrong and no amount of clever SQL downstream will fix it.
Grain in one sentence
A good grain declaration has three properties:
- It is written in business terms. "One row per item scanned at a register" is a grain. "One row per
pos_line_id" is a key, which is related but not the same thing. - It is atomic or explicitly aggregated. Either the row is the lowest level the source system captures, or the declaration says what it is summarised to: "one row per store per product per day".
- It is testable. You can write a uniqueness test that fails if the grain is violated.
The third property is what separates a declared grain from an aspirational one. If the grain is "one row per order line", then (order_id, line_number) must be unique. If it is "one row per account per day", then (account_id, snapshot_date) must be unique, and every account that existed that day must have exactly one row.
Worked examples
E-commerce: order lines
An online store's checkout produces orders, and each order has one or more lines. The natural grain for sales analysis is the order line, because that is where product, quantity and price live.
create table fct_order_line ( order_line_key bigint not null, order_id varchar(40) not null, line_number integer not null, order_date_key integer not null references dim_date (date_key), customer_key bigint not null references dim_customer (customer_key), product_key bigint not null references dim_product (product_key), promotion_key bigint not null references dim_promotion (promotion_key), quantity integer not null, unit_price numeric(12,2) not null, discount_amount numeric(12,2) not null, net_amount numeric(12,2) not null, constraint pk_fct_order_line primary key (order_line_key), constraint uq_fct_order_line_grain unique (order_id, line_number));Grain: one row per product line on a customer order, as captured at checkout.
Notice what that grain allows. Product attaches cleanly because each line has exactly one product. Promotion attaches cleanly if promotions are applied per line. Shipping cost does not, because shipping is charged per order, not per line. You have three options for it: allocate it down to lines with a documented rule, put it in a separate order-level fact, or leave it out. What you must not do is copy the order's shipping cost onto every line, because sum(shipping_cost) will then multiply it by the number of lines.
SaaS: subscription MRR
A subscription business wants to report monthly recurring revenue, churn and expansion. The source has subscription change events, but most questions are about state at a point in time.
Grain: one row per subscription per calendar month, showing its state at month end.
create table fct_subscription_monthly ( subscription_key bigint not null, month_end_date_key integer not null, account_key bigint not null, plan_key bigint not null, seats integer not null, mrr_amount numeric(12,2) not null, is_active boolean not null, constraint pk_fct_subscription_monthly primary key (subscription_key, month_end_date_key));This is a periodic snapshot. It is a different grain from the event stream, and that is fine. Many teams build both: a transaction fact of subscription changes (upgrade, downgrade, cancel) for movement analysis, and a monthly snapshot for balance reporting. The mistake is trying to answer both kinds of question from one table with an ambiguous grain.
Banking: account balances
Grain: one row per account per business day, with the closing ledger balance.
Balances are semi-additive. You can sum them across accounts on one day (total deposits held on 31 March), but you cannot sum them across days. Adding up 30 daily balances gives a number that means nothing. The grain declaration is where that rule becomes obvious, because the table has a date in its grain and the measure is a level, not a flow.
Healthcare: claims
A medical claim has a header (patient, provider, admission dates) and lines (procedure codes, billed amount, allowed amount). Claims also get adjudicated, sometimes more than once.
Grain options:
| Grain | Good for | Problem |
|---|---|---|
| One row per claim | Counting claims, claim-level status | Loses procedure detail; amounts must be summed from lines |
| One row per claim line | Procedure mix, billed vs allowed by code | Claim counts need count(distinct claim_id) |
| One row per claim line per adjudication | Audit trail of payment decisions | Needs a "current version" flag for most reporting |
None of these is wrong. What is wrong is a table where some rows are claim headers and some are claim lines, which happens surprisingly often when a source extract is loaded without a grain decision.
How to declare grain in practice
Start from the business process, not the source table
A source table often mixes grains. An orders table in an operational database might contain order headers with a JSON array of lines, or a denormalised export with one row per line and the header repeated. Ask what event or measurement the business cares about. Then find the lowest level at which the source records it.
Prefer the atomic grain
The default should be the most detailed level available. You can always aggregate an atomic fact up to daily or monthly. You can never disaggregate a summary back down. If a store manager asks for sales by hour next year and your fact is at day grain, you will rebuild the table.
Aggregated facts are still useful, for performance or because the source only provides summaries, but they should sit alongside the atomic fact, not replace it.
Write the grain into the model
Put the grain sentence in the table comment, in the dbt model description, and in the model documentation. In Snowflake or Postgres:
comment on table fct_order_line is 'Grain: one row per product line on a customer order, as captured at checkout.';Enforce it with a test
Most cloud warehouses do not enforce unique constraints. Snowflake, BigQuery and Databricks accept primary key declarations as metadata but will not reject duplicates. So the grain test has to live in your pipeline:
-- returns rows only when the grain is violatedselect order_id, line_number, count(*) as row_countfrom fct_order_linegroup by order_id, line_numberhaving count(*) > 1;In dbt, the same check is a unique_combination_of_columns test on order_id and line_number. Run it on every build.
Common mistakes
Mixing grains in one table
The most damaging mistake. A table holds order lines plus "adjustment" rows that apply to the whole order, or daily balances plus month-end summary rows. Every aggregate is now wrong unless the reader knows to filter. If you find yourself adding a row_type column to distinguish levels, you probably need two tables.
Declaring grain by key instead of meaning
"One row per event_id" says nothing about what an event is. If the source starts emitting retry events with new IDs, the grain is technically intact and the counts are silently doubled. State the business meaning so that a change in source behaviour is visibly a grain change.
Attaching dimensions at the wrong level
A dimension can only attach to a fact if there is exactly one member of it per fact row. Sales rep on an order line works if each order has one rep. If orders can be split between two reps for commission, the rep relationship is many-to-many and needs a bridge table or a separate commission fact. Forcing it into the line fact by picking "the first rep" produces confident, wrong commission reports.
Storing header measures on line rows
The shipping cost example above. Order-level amounts on line-level rows look harmless until someone sums them. Either allocate (and document the allocation rule) or keep them at their own grain.
Changing grain silently
A team changes a daily snapshot to a twice-daily snapshot to support a new dashboard. Every existing report that sums or averages over the date now double-counts. Grain changes are breaking changes. Treat them like a schema migration with a new table or version, not an in-place edit.
Choosing a summary grain too early
"Nobody needs individual transactions" is often true in the first month and false by the sixth. Build atomic first unless volume or source limits make it impossible.
Grain and the rest of the model
Once grain is fixed, the remaining design steps follow from it:
- Dimensions are the context that is single-valued at that grain: the date, customer, product and store for a sales line.
- Facts (measures) are the numeric values true at that grain: quantity, net amount, discount.
- Degenerate dimensions like order number are identifiers that belong at that grain but have no attributes worth a dimension table.
For more on separating those two kinds of columns, read fact vs dimension tables. For how the same dimensions get shared across several facts with different grains, see conformed dimensions and the bus matrix.
A quick grain checklist
Before you build a fact table, write down answers to these:
- What business process does this table measure?
- What does one row represent, in one sentence a business user would recognise?
- Which columns make that unique, and is there a test for it?
- Which dimensions are single-valued at this grain? Which are not, and how will you handle them?
- Which measures are additive, semi-additive or non-additive at this grain?
- Is this the atomic grain? If not, where is the atomic version?
If you can answer all six, the hard part of the design is done.
Practise declaring grain in offdataai Learn
Reading about grain is easy. Declaring it correctly for a messy source is the skill. The Dimensional Modeling (Kimball) track in offdataai Learn starts with grain: each lesson gives you a business scenario, you build the fact table in the Model Builder, and an automated check tells you instantly whether your grain, keys and dimension attachments hold up. It is hands-on, and it is included in the Explorer plan at $1/month.
If you want to see finished examples first, browse the dimensional modeling page or open one of the industry templates and read the grain on each fact table.