Tables, Grain, and Why Dashboards Lie

The Mental Model

Grain means what one row represents. Most bad metrics come from joining tables with different grains and then aggregating without noticing the duplication.

Before writing any SQL, ask: one row per what? If you cannot answer, you are not ready to aggregate.

Dataset reference: ecommerce tables and grain assumptions.

Measure join fanout before aggregating

Order 101 is worth 100 and has two line items. Order 102 is worth 50 and has one. This standalone SQL example compares an unsafe join with a calculation at order grain.

with orders(order_id, amount) as (values (101, 100), (102, 50)),
items(order_id, item_id) as (values (101, 1), (101, 2), (102, 3))
select
  (select sum(o.amount) from orders o
   join items i on i.order_id = o.order_id) as unsafe_total,
  (select sum(amount) from orders) as order_total;

Expected result: unsafe_total = 250; order_total = 150. The extra 100 is the repeated order amount. SUM(DISTINCT amount) is not a repair: two different orders can have the same amount. Aggregate items to one row per order before joining, or aggregate the order fact separately.

Acceptance check: compare row count and distinct order count before and after every join intended to preserve order grain.

Interactive Check

Question: You join orders to order_items and then sum order amount. Why might revenue become too high?

Practice: Find the Grain

Identify the grain of five sample tables and decide whether each can be safely joined before aggregation.

Use the guided lab below to record your result, assumptions, and the check that would catch an incorrect result.