DWH Interview Question 17 - What is the grain of a fact table, and why does it matter so much?

Data Warehouse Interview Q&A · Question 17

In this post we are going to cover the grain of a fact table, and why getting it wrong gives you wrong numbers with no error message.

The grain is the exact meaning of one row in a fact table. "One product on one order line" is a grain. "One account per day" is another. You declare it first, before you choose any dimensions or facts, because everything else in the design depends on it.

We will build a fact table with a mixed grain, see it double count, fix it, and then see why you should always store the most detailed (atomic) grain.

Question 17 of the Real DWH Question and Answers series on TechBrothersIT.

On this page

1. The interview answer
2. Say the grain in plain English
3. Let's create some sample data
4. Step 1: Mixed grain double counts
5. Step 2: Fix it with an order-grain fact table
6. Step 3: Atomic grain rolls up to any level
7. Step 4: A summary grain can't drill down
8. How to declare and test a grain
9. Follow-up questions interviewers ask
10. The full script

The interview answer

Say this first

The grain is the exact meaning of one row in a fact table, such as one product on one order line or one account per day. Declare it before choosing dimensions and facts, choose the lowest (atomic) level available, and never mix grains in one fact table.

Say the grain in plain English

A grain is written as a short sentence in business language. Here are four examples:

Fact table	Grain: one row per...
fact_order_lines	product on an order line
fact_inventory	product, per warehouse, per day
fact_page_views	page view
fact_orders	order (shipping fee, order discount)

If a customer buys three products in one order, the sales fact has three rows. Inventory is a snapshot, one row per product per warehouse per day. Different business processes have different grains, so they get different fact tables. That is normal and correct.

Let's create some sample data

We need three tables. fact_order_lines has the grain "one product on one order line", but someone also copied the order's shipping fee onto every line. fact_orders is the fix, at order grain. daily_sales is a summary table at day grain.

Sample data — run this first

DROP TABLE IF EXISTS fact_order_lines;
DROP TABLE IF EXISTS fact_orders;
DROP TABLE IF EXISTS daily_sales;

-- grain: ONE product on ONE order line ... but someone also copied the
-- order-level shipping fee onto every line (mixed grain!)
CREATE TABLE fact_order_lines (
  order_id     INT,
  line_no      INT,
  order_date   DATE,
  product      VARCHAR(50),
  amount       DECIMAL(10,2),
  shipping_fee DECIMAL(10,2),   -- belongs to the ORDER, not the line
  PRIMARY KEY (order_id, line_no)
);
INSERT INTO fact_order_lines VALUES
  (1001, 1, '2026-01-05', 'Laptop Stand',   35.00, 5.00),
  (1001, 2, '2026-01-05', 'USB-C Cable',    18.00, 5.00),
  (1001, 3, '2026-01-05', 'Desk Lamp',      30.00, 5.00),
  (1002, 1, '2026-01-09', 'Wireless Mouse', 25.00, 5.00);

-- the fix: order-level measures live in a fact table at ORDER grain
CREATE TABLE fact_orders (
  order_id     INT PRIMARY KEY,
  order_date   DATE,
  shipping_fee DECIMAL(10,2)
);
INSERT INTO fact_orders VALUES (1001, '2026-01-05', 5.00), (1002, '2026-01-09', 5.00);

-- a summary table at DAY grain (too coarse to be the only fact table)
CREATE TABLE daily_sales (
  order_date DATE PRIMARY KEY,
  total      DECIMAL(10,2)
);
INSERT INTO daily_sales VALUES ('2026-01-05', 83.00), ('2026-01-09', 25.00);

Let's look at the order lines.

Query

SELECT * FROM fact_order_lines ORDER BY 1;

Result4 rows · 6 columns

order_id	line_no	order_date	product	amount	shipping_fee
1001	1	2026-01-05	Laptop Stand	35.00	5.00
1001	2	2026-01-05	USB-C Cable	18.00	5.00
1001	3	2026-01-05	Desk Lamp	30.00	5.00
1002	1	2026-01-09	Wireless Mouse	25.00	5.00

Order 1001 has three lines, and its 5.00 shipping fee is on every one of them. Shipping belongs to the order, not to the line, so this table mixes two grains.

Query

SELECT * FROM fact_orders ORDER BY 1;

Result2 rows · 3 columns

order_id	order_date	shipping_fee
1001	2026-01-05	5.00
1002	2026-01-09	5.00

One row per order, so each 5.00 fee appears once.

Query

SELECT * FROM daily_sales ORDER BY 1;

Result2 rows · 2 columns

order_date	total
2026-01-05	83.00
2026-01-09	25.00

Two days, one total each. There is no product column here.

Step 1: Mixed grain double counts

Two orders paid 5.00 shipping each, so the true total is 10.00. Let's ask the order line table for total shipping.

Query

SELECT SUM(shipping_fee) AS shipping
FROM fact_order_lines;

Result1 row · 1 column

shipping
20.00

The answer is 20.00. Order 1001 counted its fee three times, once per line. There is no error, just a wrong number on a dashboard.

Step 2: Fix it with an order-grain fact table

The fix is to keep each measure at its own grain. Order-level measures go into an order-level fact table. Let's run the same sum there.

Query

SELECT SUM(shipping_fee) AS shipping
FROM fact_orders;

Result1 row · 1 column

shipping
10.00

10.00, which is correct. This is the header and line pattern: order-level measures in one fact table, line-level measures in another.

Step 3: Atomic grain rolls up to any level

Why choose the lowest grain? Because detail can always be rolled up. Let's produce daily totals from the line-level table.

Query

SELECT order_date, SUM(amount) AS total
FROM fact_order_lines
GROUP BY order_date
ORDER BY order_date;

Result2 rows · 2 columns

order_date	total
2026-01-05	83.00
2026-01-09	25.00

83.00 on January 5 and 25.00 on January 9, the same numbers as the daily_sales summary table. Daily totals are one GROUP BY away.

Step 4: A summary grain can't drill down

Now let's go the other way. The business has only the daily summary and asks for sales by product.

Statement

SELECT product, SUM(total)
FROM daily_sales
GROUP BY product;

Error (expected) — PostgreSQL

column "product" does not exist
LINE 1: SELECT product, SUM(total)
               ^

The query fails because the product column does not exist. The detail was thrown away when the data was summarized, and it cannot be recovered. So store the atomic grain, and build summary tables on top of it when you need speed.

How to declare and test a grain

First, write the sentence and agree on it with the business: one row per product on an order line. Then test every column against it. Does it have exactly one value for each row at this grain?

Column	One value per line?	Where it belongs
product_key	✓ yes	this fact table
line amount	✓ yes	this fact table
promotion code	✓ yes	this fact table
order shipping fee	✗ no	order-grain fact table
customer lifetime value	✗ no	customer dimension

Anything that fails the test lives somewhere else: the shipping fee in the order-grain fact table, and customer lifetime value on the customer dimension.

Follow-up questions interviewers ask

What if the business only needs monthly totals?

Store atomic data anyway; summaries can be derived, detail cannot be recovered.

Can a model have fact tables at different grains?

Yes, as separate fact tables (for example order and order line) sharing conformed dimensions.

What is the symptom of a mixed grain?

Double (or multiple) counting of measures that belong to a coarser level, without any error.

The full script

Here is everything from this post in one script. Copy it and run it top to bottom.

Full script — copy and run top to bottom

-- ========================================================================
-- Data Warehouse Interview Q&A #17 - What is grain?
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example:  CREATE SCHEMA dwh_q17;  SET search_path TO dwh_q17;
-- ========================================================================

-- Step 0: create and load the sample data
DROP TABLE IF EXISTS fact_order_lines;
DROP TABLE IF EXISTS fact_orders;
DROP TABLE IF EXISTS daily_sales;

-- grain: ONE product on ONE order line ... but someone also copied the
-- order-level shipping fee onto every line (mixed grain!)
CREATE TABLE fact_order_lines (
  order_id     INT,
  line_no      INT,
  order_date   DATE,
  product      VARCHAR(50),
  amount       DECIMAL(10,2),
  shipping_fee DECIMAL(10,2),   -- belongs to the ORDER, not the line
  PRIMARY KEY (order_id, line_no)
);
INSERT INTO fact_order_lines VALUES
  (1001, 1, '2026-01-05', 'Laptop Stand',   35.00, 5.00),
  (1001, 2, '2026-01-05', 'USB-C Cable',    18.00, 5.00),
  (1001, 3, '2026-01-05', 'Desk Lamp',      30.00, 5.00),
  (1002, 1, '2026-01-09', 'Wireless Mouse', 25.00, 5.00);

-- the fix: order-level measures live in a fact table at ORDER grain
CREATE TABLE fact_orders (
  order_id     INT PRIMARY KEY,
  order_date   DATE,
  shipping_fee DECIMAL(10,2)
);
INSERT INTO fact_orders VALUES (1001, '2026-01-05', 5.00), (1002, '2026-01-09', 5.00);

-- a summary table at DAY grain (too coarse to be the only fact table)
CREATE TABLE daily_sales (
  order_date DATE PRIMARY KEY,
  total      DECIMAL(10,2)
);
INSERT INTO daily_sales VALUES ('2026-01-05', 83.00), ('2026-01-09', 25.00);


-- Step 1: Mixed grain: total shipping is double counted
SELECT SUM(shipping_fee) AS shipping
FROM fact_order_lines;

-- Step 2: Fixed: order-level facts in an order-grain fact table
SELECT SUM(shipping_fee) AS shipping
FROM fact_orders;

-- Step 3: Atomic grain rolls up to any level
SELECT order_date, SUM(amount) AS total
FROM fact_order_lines
GROUP BY order_date
ORDER BY order_date;

-- Step 4: A summary grain can NOT drill down
-- This statement is EXPECTED TO FAIL: it shows the constraint protecting the data.
-- Remove the leading -- to run it and see the error.
-- SELECT product, SUM(total)
-- FROM daily_sales
-- GROUP BY product;

Key points

• Grain = what one row of a fact table means
• Declare it first, in plain English
• Never mix grains: it double counts with no error
• Choose the atomic grain: roll up, never drill down
• Test each column: one value per row at this grain?

Next in this series

Question 18 — Star schema vs snowflake schema What is the difference between a star schema and a snowflake schema?

Every query and every result on this page was run on PostgreSQL 16 using the script above. The SQL is standard and runs on most databases with small changes (noted in the script). All tables and data are invented demo data for teaching.