Numbers Explode After a JOIN? The Fan-Out Trap AI Analysis Often Misses

When numbers jump after a JOIN, the usual cause is incompatible grain: the left table has one row per order while the right has one row per item, so the order amount repeats for every item. AI can produce executable SQL without knowing which grain matches your decision. Before execution, verify keys, cardinality, and where aggregation occurs.

What is fan-out, and why does SQL report no error?

Suppose orders has one row per order and order_items one per item. A $1,000 order with three items becomes three rows after joining:

SUM(order_total) now returns $3,000. The SQL and relationship can be valid; the output simply moved from order grain to item grain. The BigQuery documentation likewise defines a primary key as unique per row and notes that some key constraints are not enforced—the user remains responsible for whether the data is actually unique. See BigQuery primary and foreign keys.

Fan-out can also corrupt customer and order counts without DISTINCT, denominators such as paid-order share, average order value, orders per customer, and every aggregation after a third one-to-many table is added. The result often looks plausible, which is why query succeeded is not analytical approval.

Answer four questions before the JOIN

1. What is the target grain of the decision metric? An order, user, product, or user-month?
2. Is the join key unique on either side? A column named order_id is not evidence; inspect distinct counts.
3. Is the relationship one-to-one, one-to-many, or many-to-many? Many-to-many is not always wrong, but aggregation must be defined first.
4. Which measure can be summed at which grain? Order total cannot repeat at item grain; item subtotals can be summed there.

Cardinality asks not merely whether tables can JOIN, but how many rows one input row can become.

Reusable fan-out diagnostic SQL

Measure rows and key uniqueness on both sides:

SELECT COUNT(*) AS rows, COUNT(DISTINCT order_id) AS distinct_orders
FROM "orders.csv";

SELECT COUNT(*) AS rows, COUNT(DISTINCT order_id) AS distinct_orders
FROM "order_items.csv";

Find how often a key appears on the right:

SELECT order_id, COUNT(*) AS item_rows
FROM "order_items.csv"
GROUP BY order_id
HAVING COUNT(*) > 1
ORDER BY item_rows DESC
LIMIT 20;

Then compare joined rows with distinct keys:

SELECT
  COUNT(*) AS joined_rows,
  COUNT(DISTINCT o.order_id) AS distinct_orders
FROM "orders.csv" o
LEFT JOIN "order_items.csv" i USING (order_id);

To preserve order grain, pre-aggregate items to one row per order:

WITH item_by_order AS (
  SELECT order_id,
         SUM(quantity * unit_price) AS item_revenue,
         COUNT(*) AS item_lines
  FROM "order_items.csv"
  GROUP BY order_id
)
SELECT o.order_id, o.customer_id, i.item_revenue, i.item_lines
FROM "orders.csv" o
LEFT JOIN item_by_order i USING (order_id);

SUM(DISTINCT order_total) is not a universal fix: two different orders can have the same amount and be collapsed. Fix the grain and keys, not the final symptom.

How Lantide Data makes grain a reviewable contract

In Lantide Data Project Analysis, the Plan states denominators, grain, join keys, and validation checkpoints before a user presses Approve & Execute. SQL remains a reviewable artifact instead of leaving only a total in chat. The value of this Plan → Execute → Report workflow is not that AI never errs, but that fan-out can be found before a number enters the Report.

A checkpoint can state:

After the JOIN, COUNT(DISTINCT order_id) must equal the order population. If row count grows, explain the one-to-many relationship, and aggregate every order-level metric before the JOIN or recalculate it at order grain.

A one-off, simple JOIN may need only one clear, validated query. When intermediate results will be reused or handed off, persistent SQL tabs, cache, and Source Run can retain dependencies; see the Unified Query Layer. Lantide supports read-oriented analysis and does not replace warehouse constraints or a data-quality platform.

Conclusion

When reviewing an AI-written JOIN, inspect grain before totals. Preserve four pieces of evidence: row and distinct-key counts for each input, and row and distinct-key counts after the JOIN. They protect decisions better than "the SQL ran successfully."

References

• BigQuery: Use primary and foreign keys
• Lantide Data: Agent-era data analysis workflow
• Lantide Data: Unified Query Layer