Fact Tables — Grain, Additivity, and Measures

The single most important decision when designing a fact table is its grain — what one row represents. Get it wrong and every aggregation downstream is broken.

What "grain" means

Grain = the level of detail of one row.

Examples:

• fct_orders grain: "one row per order"
• fct_order_items grain: "one row per order line item"
• fct_daily_sales grain: "one row per product per store per day"
• fct_user_session_events grain: "one row per event in a user session"

The grain is the contract. Every column on the fact respects it. Every downstream query interprets rows through it.

How to choose grain

Rule: choose the lowest grain you'll need to query at. You can always aggregate up; you can rarely disaggregate down.

order-line grain ← lowest, can derive order or daily totals
       ↓
   order grain ← can't break down to line items anymore
       ↓
   daily grain ← can't even compare orders within a day

If you build fct_orders at the order grain and later need per-line analysis, you have to rebuild the fact. If you build at line-item grain from the start, both queries work.

Additivity — when measures sum cleanly

Not all measures are equally additive. Three categories:

Fully additive

Sum across any dimension. The most useful.

Examples: quantity, revenue, discount_amount.

SELECT SUM(quantity) FROM fct_orders WHERE month = '2026-04' — sums orders, customers, products, stores, anything. Always right.

Semi-additive

Sum across some dimensions but not others.

Examples: account_balance, inventory_on_hand, user_count.

SELECT SUM(account_balance) FROM fct_balances WHERE date = '2026-04-30' — correct (total balance across accounts).

SELECT SUM(account_balance) FROM fct_balances WHERE account_id = 7 (across all dates) — wrong. You'd sum the same account's balance over many days, double-counting.

For semi-additive measures, you must aggregate over time using MAX, MIN, or AVG, never SUM.

Non-additive

Ratios, percentages, averages. Don't sum these; recompute from additive components.

Examples: conversion_rate, discount_percentage, unit_price.

Wrong: SUM(conversion_rate) GROUP BY week — meaningless.

Right: SUM(conversions) / NULLIF(SUM(visitors), 0) GROUP BY week — recompute from additive measures.

Rule: store the additive numerator and denominator in the fact, compute the ratio at query time.

Granularity vs. dimensional reach

Fine grain (line items) means more rows. Coarse grain (daily) means fewer rows.

For 1M orders/day with average 3 line items:

• Order grain: ~30M rows/month — manageable.
• Line item grain: ~90M rows/month — still manageable on modern warehouses.
• Per-event grain: billions of rows/month — needs care, partitioning, possibly pre-aggregation.

Modern columnar warehouses comfortably handle billions of rows. Don't trade grain for storage cost prematurely.

Common grain mistakes

Mixing grains in one fact

-- BAD: this table has TWO grains mixed
fct_orders_and_returns:
   row 1: order_id=100, type='order',  amount=500
   row 2: order_id=100, type='return', amount=-200

Now SUM(amount) is half right (net of returns) but COUNT(*) is double the order count. Either split into two facts (fct_orders, fct_returns) or carry both as separate measures (order_amount, return_amount).

Header + detail in one fact

fct_orders with both order-level measures (order_total) and line-item measures (item_quantity). When you SUM, you double-count the order total.

Fix: order-level measures stay on fct_orders (one row per order). Line-item measures go on fct_order_items (one row per line). Choose the right table for the right question.

"Snapshot" facts at the wrong grain

Inventory snapshots taken daily at 11pm. Each row: (product_id, store_id, snapshot_date, quantity_on_hand). Grain: "one row per product per store per day."

Common mistake: SUM(quantity_on_hand) GROUP BY product_id across dates — sums the same product's inventory over 30 days. That's a 30× overstate.

For snapshot facts, you must filter to one date OR use AVG/MAX. Semi-additive.

Documenting grain

In dbt, document the grain in the model description:

models:
  - name: fct_orders
    description: "One row per order. Order-level measures: subtotal, tax, shipping, total. For line-item analysis, see fct_order_items."

In the warehouse, comment the table:

COMMENT ON TABLE fct_orders IS 'Grain: one row per order. Order_total is order-level; for line-item revenue, query fct_order_items.';

Future you will thank present you.

Takeaway

Pick the lowest practical grain. Store additive measures. Document the grain clearly. Don't mix grains in one fact. These four rules prevent 80% of fact-table bugs.