The Grain Problem: Why Your Joins Inflate Your Totals

Revenue is up 340% and nobody is celebrating

The most dangerous bug in analytics is the one that inflates a number. An error that makes revenue look small gets caught in a day, because someone whose bonus depends on it complains. An error that makes revenue look large gets presented at a board meeting.

Both usually come from the same place: a join that changed the grain of your data without telling you.

What grain means

The grain of a table is the answer to one question: what does one row represent?

• orders — one row per order
• order_items — one row per line item within an order
• daily_revenue — one row per day
• customer_daily_activity — one row per customer per day

That's it. But writing the answer down is the discipline that prevents almost every double-counting bug, because the moment you join two tables you have created a third grain, and it is rarely the one you had in mind.

How a join changes the grain

Two tables. orders has one row per order, and order_items has one row per item.

select
  o.order_id,
  o.order_total,
  i.product_id
from orders o
join order_items i on i.order_id = o.order_id;

The result is at item grain, not order grain. An order with four items now appears four times — and order_total is repeated on all four rows.

So this is wrong:

select sum(o.order_total) as revenue
from orders o
join order_items i on i.order_id = o.order_id;

Every order is counted once per item it contains. A shop averaging three items per order reports roughly triple its actual revenue. The query has no syntax error, produces no warning, and returns a plausible-looking number.

Callout: The tell is a metric that grows faster than the business. If revenue jumps 340% in one release and nothing changed commercially, look for a join that gained rows — not for a marketing success.

The four fixes

Once you can see the problem, the fixes are mechanical. Which one you pick depends on what you actually want.

1. Don't join at all. If the measure lives on orders, aggregate orders:

select sum(order_total) as revenue
from orders;

Obvious in isolation, easy to miss inside a query with six joins where somebody added order_items months ago to filter on a product category.

2. Aggregate before you join. Collapse the many-side to the grain you want first, then join one-to-one:

with item_counts as (
  select order_id, count(*) as item_count
  from order_items
  group by order_id
)
select
  sum(o.order_total) as revenue,
  sum(c.item_count)  as items
from orders o
left join item_counts c on c.order_id = o.order_id;

This is the workhorse. It keeps the query at order grain, so order_total is summed once per order. Note left join — an inner join here would silently drop orders that have no items, which is a different bug wearing the same clothes.

3. Deduplicate the measure. Sometimes you genuinely need item-level rows in the output but still want a correct order total. Count each order's total exactly once:

select
  i.product_id,
  count(distinct o.order_id) as orders,
  sum(o.order_total) / count(*) over (partition by o.order_id) as attributed_revenue
from orders o
join order_items i on i.order_id = o.order_id
group by i.product_id, o.order_id, o.order_total;

Be careful here. This is attribution, not measurement — you're deciding how to split an order's value across its products, which is a business decision, not a technical one. Make it explicitly and document it, rather than letting a join make it for you.

4. Use a semi-join for filtering. When a table is present only to filter, use exists instead of join. It cannot change the grain, because it returns a boolean rather than rows:

select sum(o.order_total) as revenue
from orders o
where exists (
  select 1
  from order_items i
  where i.order_id = o.order_id
    and i.category = 'electronics'
);

Compare that to join order_items i ... where i.category = 'electronics', which duplicates any order containing two electronics items. exists is the right tool whenever the answer you need from the other table is "does a matching row exist" — and that's most of the time.

Where fan-out hides

The single-join case is easy to spot. These are the ones that get through review.

A dimension that isn't unique. You join a "customer" table expecting one row per customer, but it has one row per customer per address, or per historical version. Now every fact row is duplicated per version. This is the most common cause of fan-out in a warehouse, and it's why slowly changing dimensions need an is_current flag and why you must filter on it.

Two many-to-one joins from the same table. Join orders to order_items and separately to order_payments. An order with 3 items and 2 payments produces 6 rows. Each aggregate is now wrong by a different multiple, which makes the numbers look inconsistent rather than obviously broken.

Date range joins. Joining on where d.date between s.start_date and s.end_date fans out by however many periods overlap. Easy to write, hard to see.

A left join that looks safe. left join protects you from losing rows. It does nothing to stop you gaining them. If the right side has duplicates, a left join fans out exactly like an inner join.

Test it instead of hoping

Two checks catch essentially all of this, and both take seconds.

Before joining, confirm the right side is unique on the join key:

select customer_id, count(*)
from dim_customers
group by customer_id
having count(*) > 1;

No rows means the join is safe. Rows mean you need a filter (is_current = true) or a deduplication step first.

After joining, confirm the row count didn't change:

select
  (select count(*) from orders) as before_join,
  count(*) as after_join
from orders o
join dim_customers c on c.customer_id = o.customer_id;

If those two numbers differ, your grain changed. Either you meant it or you didn't — but now you know, which is the entire point.

In dbt, encode the second check permanently so it runs on every build:

models:
  - name: fct_orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null

That single unique test on the grain column is the cheapest insurance in analytics engineering. It catches the day an upstream dimension gains a duplicate row — which will happen, and it will not be announced.

The habit worth building

Write the grain in a comment at the top of every model, before you write the query:

-- grain: one row per order

It sounds trivially simple, and it works, because it forces you to notice when a join violates the sentence you just wrote. Every fan-out bug is ultimately the gap between the grain you intended and the grain you produced. Naming the intention is what makes the gap visible.