Why is my revenue doubled? Join fan-out in SQL, and a check that catches it

Revenue looks 40% too high. Finance has the right number, the dashboard has a bigger one, and the query “only added a join”. That is the classic join fan-out: a join to a table with several rows per key repeated the parent row, and every sum over the parent column counted it more than once.

This post shows how to see it, fix it and watch for it in plain Postgres SQL.

What goes wrong

Say orders has one row per order and order_items has one row per item. Ten orders worth 100 each, four of them with two items:

SELECT sum(o.amount)
FROM orders o
JOIN order_items i ON i.order_id = o.id;

That returns 1400, not 1000. Each order with two items appears twice in the joined rows, so its amount is added twice. Nothing errors, the query is valid, and the number is plausible enough to ship.

The same thing happens with a second many-side join. Joining orders to both order_items and payments multiplies the two: an order with 2 items and 3 payments appears 6 times.

Detect it: count versus count distinct

After a join, the number of rows should equal the number of distinct parent keys, if the join is one to one. If it is bigger, rows fanned out:

SELECT count(*) AS joined_rows,
       count(DISTINCT o.id) AS orders
FROM orders o
JOIN order_items i ON i.order_id = o.id;

Here you get 14 joined rows for 10 orders. The ratio, 1.4, is the inflation factor on any sum over the parent table, and it matches the 1400 above.

Two details matter. An inner join also drops parents with no children, so use a LEFT JOIN when you compare against the parent count. And count the key, not a nullable column: count(DISTINCT o.id) is safe, count(DISTINCT i.sku) is not.

Fix it: aggregate the many side first

Collapse the many side to one row per key, then join:

SELECT sum(o.amount) AS revenue, sum(i.n) AS items
FROM orders o
JOIN (
  SELECT order_id, count(*) AS n
  FROM order_items
  GROUP BY order_id
) i ON i.order_id = o.id;

Revenue is 1000 again, and you still have item counts. With two many-side tables, aggregate each in its own subquery. Joining two already-collapsed tables on the key cannot fan out.

Two shortcuts you will see, and why they are weaker:

• sum(DISTINCT o.amount) removes repeats of the same value, not repeats of the same row. Two different orders worth 100 collapse into one. Do not use it to fix fan-out.
• A window function marking the first row per parent works, but it is easy to get wrong when you add a filter, and it still scans every fanned-out row.

The check, scheduled

The query that opens this post turns detection into a daily check. It returns one row for each recent day where the join produced more rows than there are orders, and nothing when the join is clean:

SELECT o.created_at::date AS day,
       count(*) AS joined_rows,
       count(DISTINCT o.id) AS orders
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
WHERE o.created_at >= current_date - 7 AND o.created_at < current_date
GROUP BY 1
HAVING count(*) <> count(DISTINCT o.id)
ORDER BY 1;

Use the exact join your dashboard query uses. The point is to protect that query, so if it is meant to fan out (you really want one row per item), count the grain you intend and compare against that instead.

Run it daily. In arcodash, wrap it as SELECT count(*) AS fanned_days FROM (...) t and alert when fanned_days > 0, as described in the data quality monitors guide.

Where fan-out sneaks in

• A new join added to an old query. The query was right yesterday. The new table has more than one row per key.
• A dimension that became historical. A customer table that used to hold one row per customer now keeps one row per version (slowly changing dimension), and every order joins to all versions.
• Retries and replays. Duplicate rows in the child table, which the row count check may not flag because the total looks plausible.
• A many-to-many mapping table used as if it were one to one.

Limits

This catches a join that repeats rows. It does not tell you whether the right number is right, and it will not fire if the fan-out is the same every day and was there from the first version of the query. Pair it with a reconciliation against the source: fan-out is one of the most common reasons that check returns a day. Both belong in the set described in lightweight data observability.

For a check in your own dialect, the SQL data check generator writes one for any table.