The Grain of a Table: Declare It Before the First Join

A report gets one new column, product category, and shipping revenue goes up 2.4× overnight. Nobody touched shipping. Nobody touched the orders table. The change was a join to order_lines so the report could show category, and that join made every order’s shipping fee appear once per line.

No query failed and no test went red. The SQL was valid, and the number was wrong because the table no longer meant what everyone assumed it meant. What changed was its grain.

Grain is a sentence, not a key

The grain of a table is what a single row represents. You should be able to say it out loud:

• orders: one row per order, latest state from the source system.
• order_lines: one row per line item per order.
• inventory_daily: one row per product per warehouse per calendar day, as of end of day.
• web_events: one row per event received, duplicates included.

The primary key is how the grain is expressed mechanically: order_id, (order_id, line_no), (product_id, warehouse_id, snapshot_date). The sentence carries the meaning the key leaves out. “As of end of day”, “latest state”, and “duplicates included” each change which questions the table can answer.

Kimball’s dimensional modelling process puts declare the grain before choosing dimensions or facts, and Microsoft’s star schema guidance makes the same point for Power BI models. That order isn’t arbitrary. Each later decision depends on it: which measures belong in the table, which dimensions you can join, how to partition, what to MERGE on.

How a join changes the grain

A join to a table at a finer grain fans out. Each row on the coarse side repeats once for every match on the fine side. Measures that belonged to the coarse grain get repeated too, and SUM doesn’t know they’re copies.

The grain changes in other ways too, and all of them pass every syntax check:

• Mixed-grain tables. Order-level totals stored on every line row “for convenience”. Each row is accurate on its own, but summing any of those columns is wrong.
• Joining a history table on the business key. A Type 2 dimension has one row per version of each customer. Joining facts on customer_id alone multiplies every fact by the number of versions.
• Unioning tables at different grains. Daily snapshot rows unioned with transaction rows “to get everything in one place”.
• A GROUP BY that gains a column. Adding channel to a grouping changes “one row per day” to “one row per day per channel”. Downstream code that assumed one row per day now sums several.
• Duplicates you didn’t know about. If orders actually holds two rows for some orders because of a non-idempotent load, the grain you declared isn’t the grain you have.

Grain decides which measures can be added up

Even when a table’s grain is correct, its measures don’t all aggregate the same way. The type of fact table tells you which aggregations are safe.

Fact table type	Grain	Example	Typical measures
Transaction	One row per event	order_lines	Amount, quantity: sum freely
Periodic snapshot	One row per entity per period	inventory_daily	Balance, on-hand: don’t sum across time
Accumulating snapshot	One row per process instance, updated as it moves	order_fulfilment	Durations between milestones

And the additivity of each measure:

Additivity	Example	Across dimensions	Across time
Additive	Sales amount, units sold	Sum	Sum
Semi-additive	Account balance, stock on hand	Sum	Last, average, or min/max. Never sum
Non-additive	Unit price, margin %, distinct customers	Recompute	Recompute

Distinct counts catch people out more than anything else on that list. Thirty daily-active-user numbers summed together aren’t a monthly active user count, because a user active on ten days gets counted ten times. Store the components (the user IDs, or a sketch like HyperLogLog) and recompute at the grain you want to report.

Three ways to join without fanning out

Aggregate to the coarser grain first

If the report is at order grain, bring lines up to order grain before the join:

WITH line_summary AS (
  SELECT order_id,
         count(*)                    AS line_count,
         sum(amount)                 AS lines_amount,
         count(DISTINCT category_id) AS category_count
  FROM silver.order_lines
  GROUP BY order_id
)
SELECT o.order_id, o.order_date, o.shipping_fee,
       s.line_count, s.lines_amount, s.category_count
FROM silver.orders o
LEFT JOIN line_summary s ON s.order_id = o.order_id   -- 1:1, grain preserved

Allocate the coarse measure down to the finer grain

If the report really needs line grain, as with “shipping cost by product category”, shipping has to be allocated to lines, not copied onto them. Allocate in proportion to line amount and assign the rounding remainder to one line, so the parts still add up to the whole:

WITH base AS (
  SELECT l.order_id, l.line_no, l.category_id, l.amount, o.shipping_fee,
         ROUND(o.shipping_fee * l.amount / SUM(l.amount) OVER (PARTITION BY l.order_id), 2)
           AS shipping_raw,
         ROW_NUMBER() OVER (PARTITION BY l.order_id ORDER BY l.amount DESC, l.line_no)
           AS rank_in_order
  FROM silver.order_lines l
  JOIN silver.orders o ON o.order_id = l.order_id
)
SELECT order_id, line_no, category_id, amount,
       shipping_raw
         + CASE WHEN rank_in_order = 1
                THEN shipping_fee - SUM(shipping_raw) OVER (PARTITION BY order_id)
                ELSE 0 END AS shipping_allocated
FROM base

SUM(shipping_allocated) now equals SUM(shipping_fee) at order level, to the cent. The allocation rule (by amount, by quantity, by weight) is a business decision. Write it down next to the table.

Keep each measure at its own grain and let the model join

In a semantic layer such as a Power BI or Fabric semantic model, the cleanest approach is often not to join facts at all. Keep orders and order_lines as separate fact tables that share conformed dimensions (date, customer), and let each measure aggregate in its own table. Problems start when someone flattens everything into one wide table so it’s “easier to use”.

Declare it, then test it

Grain only protects you if it’s written down where people will see it and checked where a machine will notice.

CREATE TABLE silver.orders (
  order_id      BIGINT NOT NULL,
  customer_id   BIGINT NOT NULL,
  order_date    DATE   NOT NULL,
  shipping_fee  DECIMAL(18,2),
  source_updated_at TIMESTAMP NOT NULL
)
COMMENT 'Grain: one row per order (latest source state). Key: order_id.'
TBLPROPERTIES ('grain' = 'order_id');

Most lakehouse engines won’t enforce it for you. Delta Lake supports NOT NULL and CHECK constraints but no unique constraint. Primary keys in Unity Catalog and the PRIMARY KEY ... NOT ENFORCED syntax in Fabric Warehouse are informational: the optimiser may rely on them, and nothing stops you inserting a duplicate. So the check has to be explicit:

from pyspark.sql import functions as F

def assert_grain(df, keys: list[str], name: str):
    dupes = (df.groupBy(*keys).count()
               .where(F.col("count") > 1)
               .limit(5).collect())
    if dupes:
        raise AssertionError(f"{name}: grain {keys} violated, e.g. {dupes}")

def assert_no_fanout(left, joined, keys: list[str], name: str):
    before, after = left.count(), joined.count()
    if after != before:
        raise AssertionError(
            f"{name}: join changed row count {before} -> {after}; "
            f"expected 1:1 on {keys}")

Put assert_grain after every write of a table people query. Put assert_no_fanout after any join where the code assumes 1:1, and especially after joins to dimensions, where a duplicated dimension key can multiply a fact table without anyone noticing.

Grain shows up in the rest of the series

Once you notice grain, it turns up in most of the other topics here:

• The MERGE key in an idempotent write is the grain. If you merge on something else, duplicates follow.
• A slowly changing dimension is a deliberate change of grain, from “one row per customer” to “one row per customer version”.
• Partitioning should follow a column that’s part of how the table is queried, and that usually means the time component of its grain.

On Monday

• Add a one-sentence grain to the comment of every table in your gold layer. Any table you can’t describe is your first bug.
• Add a uniqueness check on the grain key after each write.
• Search reports and models for SUM over semi-additive or non-additive measures: balances, prices, ratios, distinct counts.
• Find the wide “everything” table and check which of its measures are repeated across rows.

Most wrong numbers have nothing to do with bad data. They come from correct data summed at the wrong grain.