Grain: What One Row Means

An interviewer says: "Design the warehouse for an online shop." The first thing they check is one sentence. What does one row of your main table mean?

That meaning is called the grain. You met it in the SQL chapters: in orders, one row is one order. In order_items, one row is one product in one order.

Here are the shop tables again. Both describe the same sales, but at two different grains.

This chapter goes deeper. You learn five things.

• How to write the grain as one sentence, and what it gives you.
• The three kinds of fact table, each shown with shop rows.
• How a wrong grain gives wrong counts and wrong totals.
• Which numbers you can add up, and which you cannot.
• How to choose the grain.

Grain is what one row stands for. You write it as one sentence: "One row per ___." Here is the sentence for each shop table.

A key is the column, or the columns, that are different in every row. The grain sentence tells you the key.

Look at the order_items table in Start here. Order 101 has two rows, so order_id alone repeats. The pair order_id and product_id never repeats, so that pair is the key.

What a fact table is

A fact table is a table where each row records something that happened. In the shop, an order is something that happened, and so is an item sold.

A fact table also holds numbers you count or add up. These numbers are called measures.

In orders the measure is amount. In order_items the measure is quantity.

Tables like customers describe things, not events. Chapter 8 covers them. In the rest of this chapter, we model fact tables.

Declare the grain before you add any column. Write it as a comment at the top of the table, and make the key match it.

-- GRAIN: one row per product in one order.
create table fact_order_items (
  order_id    integer not null,
  product_id  integer not null,
  order_date  date    not null,
  quantity    integer not null,
  primary key (order_id, product_id)
);

Now use the sentence to decide which columns belong. A column fits when its value describes this one row, not a bigger group.

The shop has no price for each item. So this table has no money measure. The money stays in orders, at order grain.

You must also check that the data obeys the sentence. Group by the key columns. If any group has more than one row, the grain is broken.

select order_id, product_id, count(*) as row_count
from order_items
group by order_id, product_id
having count(*) > 1;

What do you get from declaring the grain?

One sentence settles four things. You do not need to argue about them later.

• The key: the columns that make a row unique.
• Which columns belong in the table, and which do not.
• What count(*) means: it counts rows, so it counts items here.
• Which joins and sums are safe at this grain (later sections show this).

Most fact tables use one of three grains. They differ in two ways.

• When is a row written?
• Does the row change later?

The next three sections show each kind with shop rows.

A transaction fact table records each event once, when it happens. You add rows. You do not edit old rows.

The shop sells items. So the grain is one row per product in one order. Join order_items to orders to add the order date.

select i.order_id, o.order_date, i.product_id, i.quantity
from order_items i
join orders o on o.order_id = i.order_id
order by i.order_id, i.product_id;

Each result row is one event: this product, in this order, on this day. Order 101 gives two rows because it had two products.

What if order 103 is returned? You do not edit its row. You add a new row.

A returned item is a new row with quantity -1. The old row stays.

Sometimes the question is about a state, not an event. A state is how things stand at one moment. For example: how many orders had we taken by the end of each day?

A periodic snapshot writes one row for every day, even a day when nothing happened. The grain is one row per day.

select d::date as day,
       count(o.order_id) as orders_placed,
       (select count(*) from orders x
         where x.order_date <= d::date) as orders_so_far
from generate_series(date '2026-01-10', date '2026-01-15',
                     interval '1 day') as d
left join orders o on o.order_date = d::date
group by d
order by d;

This query is for PostgreSQL. generate_series makes one row for each day. The LEFT JOIN keeps days that have no orders.

The part in brackets counts every order on or before that day. This gives orders_so_far.

You do not need to write this query yourself. Look at the result rows.

Look at 12, 13 and 14 January. No order was placed, but each day still has a row with 0. A transaction table has no rows on those days.

Both measures here are numbers you can read for one day. The additivity section later shows why you cannot add orders_so_far across days.

An order has a journey: placed, then paid, then shipped. An accumulating snapshot keeps the whole journey in one row. The grain is one row per order.

The shop does not store the paid and shipped dates yet. For this example, we announce them. A date is NULL until that step happens.

Order 105 is pending, so it has stopped after "placed". Order 106 is paid but not shipped yet.

The row fills in over time. Here is the one row of order 104 on three days.

This row type changes. On 2026-01-23 the warehouse ships order 106, and you update its row.

update fact_order_journey
set shipped_date = '2026-01-23'
where order_id = 106;

Now ask how many days each shipped order waited between payment and shipping.

select order_id,
       shipped_date - paid_date as days_to_ship
from fact_order_journey
where shipped_date is not null
order by order_id;

You cannot get this answer from a transaction table. It would need a row per step, and then a join to match the steps.

count(*) counts rows. So it counts whatever one row means in that table. In order_items, it counts items, not orders.

Watch one table answer three questions. Each question needs its own expression.

select count(*)                 as item_rows,
       count(distinct order_id) as orders_with_items,
       sum(quantity)            as units
from order_items;

The question "how many orders?" has a trap. The right answer is 6, because order 105 exists.

select count(*) as orders
from orders;

The parent is the "one" side: orders. The child is the "many" side: order_items.

A join repeats a parent row once for each child row. This is fan-out, from the SQL joins chapter.

Look at it through grain. After you join orders to order_items, one row is one item. But amount is the money of a whole order.

select sum(o.amount) as total_amount
from orders o
join order_items i on i.order_id = o.order_id;

The extra 120.00 is the amounts of orders 101 and 106, counted twice. There are two ways to fix this.

• Sum amount from orders alone, where one row is one order.
• Or reduce order_items to one row per order first, then join.

The second fix is useful when you also need item facts. Here we add up the units of each order, then join, and sum both measures.

select sum(o.amount) as total_amount,
       sum(i.units)  as total_units
from orders o
join (
  select order_id, sum(quantity) as units
  from order_items
  group by order_id
) i on i.order_id = o.order_id;

Before you sum after a join, ask: what does one row mean now? If it does not match the measure, the sum is wrong.

The fan-out above has a name: the fan trap. One parent row has many child rows, and a parent measure gets repeated.

The chasm trap has a different shape. One parent has two separate child tables, and you join both in one query.

To see it, we add a small table for this section only. It lists the parcels of orders 101 and 102. Order 101 left in two parcels, and order 102 in one.

Now order 101 has two item rows and two parcel rows. Both are children of the same order. Ask: how many units were shipped?

select sum(i.quantity) as units
from order_items i
join shipments s on s.order_id = i.order_id;

Two things went wrong. The two child tables multiplied each other, so the sum is 7, not 4. The inner join also dropped the items of orders 103, 104 and 106, because shipments does not list them.

The fix is the same idea as before: reduce each child table to one row per order, then join.

select sum(i.units) as units, sum(s.parcels) as parcels
from (
  select order_id, sum(quantity) as units
  from order_items
  group by order_id
) i
join (
  select order_id, count(*) as parcels
  from shipments
  group by order_id
) s on s.order_id = i.order_id;

A table has mixed grain when its rows do not all mean the same thing. A common sign is a column such as row_type.

Here is a table that holds order rows and item rows together. We show it for orders 101 and 102 only.

Now ask how many orders there are. The first answer people try is count(*).

select count(*) as row_count
from sales_mixed;

You can add where row_type = 'order' and get 2. But every query must remember that filter. One forgotten filter gives a wrong number with no error.

The 340.50 total in the fan-out section is the same mistake, hidden. An order amount sat on item rows.

The fix is one table per grain. You already have them: orders has one row per order, and order_items has one row per item.

Grain also decides which measures you can add. A measure is additive when adding it across rows gives a true answer.

Some measures are only safe to add across some things. Look at the daily snapshot from before.

Add the two columns across all six days and compare.

select sum(orders_placed) as placed,
       sum(orders_so_far) as so_far_added
from daily_orders;

The first answer is true: three orders were placed in those six days. The second is nonsense. The same orders are counted again on every day.

For a running count, read one day. The value on the last day is the answer.

select orders_so_far
from daily_orders
where day = '2026-01-15';

The lowest grain is the most detailed one: one row per smallest event. This is also called the atomic grain. Use these four steps to choose.

1. Write down the questions people will ask.
2. Find the lowest level of detail that the source system gives you.
3. Pick the lowest grain that answers all the questions.
4. Write the grain sentence and the key. Then run the grain check.

Why the lowest grain? You can always add detail rows up into a summary. You can never split a summary back into rows.

Add up the item rows by day. This works because we kept one row per item.

select order_date, sum(quantity) as units
from fact_order_items
group by order_date
order by order_date;

Now imagine you saved only this daily table. A manager asks which products sold on 21 January.

The daily table cannot tell you. The item rows can.

select p.name, f.quantity
from fact_order_items f
join products p on p.product_id = f.product_id
where f.order_date = '2026-01-21'
order by p.product_id;

Do not go lower than the source gives you. If a snapshot is the question, a snapshot is the right grain.

State the grain before you build, count at the grain, and check the grain after every join.

Each exercise opens a design canvas. Draw the tables, then run the review. The review checks your model against real rows.

Each one has a fault planted in the data. Say the grain of every table before you draw the second one.