Lesson 1: The Grain of a Table and of a Result

Why your totals go wrong before you write a single join

Ask an analyst why a revenue figure came out twice as big as it should have, and the answer usually turns out to be that a row didn't mean what they thought it meant. That is the grain of a table: what one row stands for. Before you join, group or sum anything, you should be able to finish this sentence for every table you touch:

"One row in this table is one ______."

Think of it like the label on a stack of index cards. If each card is "one order", counting cards counts orders. If each card is "one line on an order", counting cards counts lines, and adding up a per-order figure from those cards will count some orders more than once. The bugs in later levels (duplicates, fan-out, double counting) nearly all start with a wrong grain sentence.

A grain sentence is only a guess until you test it. The test is simple: find the column, or set of columns, that should make each row unique, and prove that no value appears twice.

Step 1: Set up the dataset and count every table

Open a free tool: DB Fiddle (db-fiddle.com offers SQLite, PostgreSQL and MySQL), the SQLite3 Fiddle at sqlite.org/fiddle (runs entirely in your browser), or a local SQLite or PostgreSQL install. Paste and run this:

CREATE TABLE customers (customer_id INTEGER, name TEXT, city TEXT);
CREATE TABLE orders (order_id INTEGER, customer_id INTEGER, order_date DATE);
CREATE TABLE order_lines (order_id INTEGER, line_no INTEGER, product TEXT,
                          qty INTEGER, price NUMERIC(8,2));
CREATE TABLE product_prices (product TEXT, price NUMERIC(8,2), valid_from DATE);

INSERT INTO customers VALUES
  (1,'Asha','Leeds'), (2,'Ben','York'), (3,'Chen','Leeds'), (4,'Dina','Hull');

INSERT INTO orders VALUES
  (101,1,'2026-09-01'), (102,1,'2026-09-03'), (103,2,'2026-09-03'),
  (104,3,'2026-09-05'), (105,3,'2026-09-07');

INSERT INTO order_lines VALUES
  (101,1,'Pen',3,1.50), (101,2,'Notebook',1,4.00), (102,1,'Pen',10,1.50),
  (103,1,'Stapler',1,8.00), (103,2,'Pen',2,1.50), (103,3,'Notebook',2,4.00),
  (104,1,'Notebook',5,4.00), (105,1,'Stapler',1,8.00);

INSERT INTO product_prices VALUES
  ('Pen',1.50,'2026-01-01'), ('Notebook',4.00,'2026-01-01'),
  ('Stapler',7.00,'2026-01-01'), ('Stapler',8.00,'2026-08-01');

Now confirm that what you loaded is what you meant to load. One query lists every table:

SELECT 'customers' AS tbl, COUNT(*) AS n FROM customers
UNION ALL SELECT 'orders',         COUNT(*) FROM orders
UNION ALL SELECT 'order_lines',    COUNT(*) FROM order_lines
UNION ALL SELECT 'product_prices', COUNT(*) FROM product_prices;
-- Expect: customers 4, orders 5, order_lines 8, product_prices 4

Habit worth building: if a count is off here (an INSERT ran twice, or a paste got cut short), every later result is wrong in ways that look like query bugs. Check the counts before you write a query.

Dialect notes: multi-row VALUES lists work in PostgreSQL, MySQL, SQL Server and SQLite 3.7.11 or later. SQL Server caps a single INSERT ... VALUES at 1,000 rows. That won't matter for these lesson datasets, but it will for bigger pastes.

Step 2: Write a grain sentence, then test it

The usual guesses:

The test pattern: group by the supposed key and ask for any group with more than one row.

-- Is order_id unique in orders?
SELECT order_id, COUNT(*) AS n
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;
-- 0 rows returned = PASS. Each order_id appears once.

Zero rows is the result you want. Any row that comes back is a duplicate key value, and the query shows you which one.

What if someone assumes order_lines is one row per order?

SELECT order_id, COUNT(*) AS n
FROM order_lines
GROUP BY order_id
HAVING COUNT(*) > 1;
-- order_id | n
-- 101      | 2
-- 103      | 3

FAIL. order_id alone doesn't identify a line. Test the two-column key:

SELECT order_id, line_no, COUNT(*) AS n
FROM order_lines
GROUP BY order_id, line_no
HAVING COUNT(*) > 1;
-- 0 rows = PASS. Grain: one row per order line (order_id + line_no).

Step 3: Catch the table that looks simpler than it is

product_prices reads like a lookup table, one price per product. Test it:

SELECT product, COUNT(*) AS n
FROM product_prices
GROUP BY product
HAVING COUNT(*) > 1;
-- product | n
-- Stapler | 2

The stapler's price changed on 1 August, so the table keeps price history. The honest grain sentence is "one row per product per price start date", and the key is (product, valid_from). Run the test with both columns and it passes. If you'd joined this table to order_lines thinking it was one row per product, every stapler line would have come back twice. Level 2 shows exactly how that happens.

Gotcha: a declared PRIMARY KEY enforces uniqueness, but the tables analysts work with (exports, views, warehouse tables) often have none, and our CREATE TABLE statements didn't declare any. Don't trust a column name like id. Run the test.

Step 4: Predict the grain of a result before you run it

A query result has a grain too, and GROUP BY sets it: one row per distinct combination of the GROUP BY columns. Write your prediction down first, then run the query.

-- Prediction: one row per customer *who has an order*. Customers 1, 2, 3 → 3 rows.
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;
-- 1 | 2
-- 2 | 1
-- 3 | 2      → 3 rows. Dina (customer 4) isn't here: she has no orders.

-- Prediction: one row per order date. Dates: 09-01, 09-03, 09-05, 09-07 → 4 rows.
SELECT order_date, COUNT(*) AS orders_that_day
FROM orders
GROUP BY order_date;
-- 4 rows. Two orders share 2026-09-03, so there are fewer rows than the 5 orders.

-- Prediction: one row per order → 5 rows, rolled up from 8 lines.
SELECT order_id, SUM(qty * price) AS order_value
FROM order_lines
GROUP BY order_id;

When the actual count differs from your prediction, stop. Either the data isn't what you thought or the query isn't. Both are worth knowing before the number goes into a report.

Key takeaways

• Grain is what one row stands for. Write it as a sentence: "one row per order line".
• Prove a grain with GROUP BY key HAVING COUNT(*) > 1. Zero rows means the key is unique, and any rows that come back are the duplicates.
• Keys are often several columns, like (order_id, line_no) or (product, valid_from). Don't stop at the first column that looks like an ID.
• A GROUP BY result has one row per distinct combination of its grouping columns. Predict the row count, then check it.
• After loading data, count every table before you write any queries.