The Analytics Agent Said Revenue Was Up 38%. The Join Had Counted Every Order Once Per Line Item.

A finance lead asks the internal analytics agent a routine question: "How did revenue in Q3 compare to Q2?" The agent writes a query, runs it against the warehouse, and replies in one confident sentence: revenue grew 38%. The query ran without errors. The number was wrong, and nothing in the answer gave anyone a way to notice.

This is an autopsy of that pattern. The model did not hallucinate and no tool failed. The agent was built to return only the conclusion, and a SQL mistake that any analyst would have caught by looking at a row count went out with a clean finish.

🔍 What actually happened

The warehouse has two tables the agent needed:

• orders: one row per order, with order_total and created_at
• order_items: one row per line item, with order_id, sku, quantity

The question also mentioned "excluding the discontinued product line", so the agent needed order_items to filter by SKU. It wrote this:

SELECT date_trunc('quarter', o.created_at) AS quarter,
       SUM(o.order_total) AS revenue
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE i.sku NOT LIKE 'LEGACY-%'
GROUP BY 1;

The join changes the grain. An order with four line items now appears four times, and SUM(o.order_total) adds its total four times. If Q3 happened to have more multi-item baskets than Q2, say because a bundle promotion ran, the inflated sum grows faster than real revenue. The "growth" is partly a change in basket size, counted as money.

The query is valid SQL. It returns plausible numbers of plausible size. Nothing throws an error, so the agent had no failure signal to react to.

The anti-pattern: answer-only output

It is tempting to blame the model for writing a bad join. That misses the problem. Analysts write this exact join too. They catch it because their workflow puts evidence in front of them before they present anything: a row count that looks too high, a spot check on one order, a total that doesn't match the finance dashboard.

The agent's design removed all of that. Its contract was:

1. Receive a question.
2. Generate and execute SQL.
3. Return a sentence with the number.

Three decisions made the bug invisible:

• ❌ The SQL was hidden. It was logged in a trace but not shown to the person asking.
• ❌ No grain was declared. The agent never stated what one row of its result, or of its intermediate join, represented.
• ❌ No reconciliation. Nothing compared the answer against a known figure, even though a monthly revenue total already existed in a trusted reporting table.

Each of these is a reasonable UX choice in isolation. Finance users do not want to read SQL. Put together, they produce an agent whose errors look exactly like its successes.

✅ The fix: make the agent prove the grain before it states the total

The fix does not require a smarter model. It needs a few cheap checks, run as tool calls, whose results the agent must report next to its answer.

1. Declare the grain, then test it

Before aggregating, the agent states the expected grain of the source table and checks that the join preserved it:

-- expected: one row per order
SELECT COUNT(*) AS joined_rows,
       COUNT(DISTINCT o.id) AS distinct_orders
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE i.sku NOT LIKE 'LEGACY-%';

If joined_rows is greater than distinct_orders, the join fanned out and any SUM over an order-level column is wrong. This single comparison would have stopped the incident. Put it in the system instructions as a rule, not a suggestion: any SUM or AVG over a column from a table that was joined one-to-many requires a fan-out check first.

2. Rewrite to filter without joining

Once fan-out is detected, the right pattern is usually a semi-join, which filters orders without multiplying them:

SELECT date_trunc('quarter', o.created_at) AS quarter,
       SUM(o.order_total) AS revenue
FROM orders o
WHERE EXISTS (
  SELECT 1 FROM order_items i
  WHERE i.order_id = o.id AND i.sku NOT LIKE 'LEGACY-%'
)
GROUP BY 1;

Note that this still counts the full order total for an order that includes a legacy SKU alongside other items. Whether that is correct is a business question, not a SQL one, and the agent should say so rather than pick silently. A good agent response flags it: "Orders containing both legacy and current items are counted at full value; say so if legacy line revenue should be subtracted instead."

3. Reconcile against a trusted number

If a vetted source exists, such as a finance-owned monthly revenue view, the agent queries it for the same period and reports the gap:

• Within a small tolerance → report the answer.
• Outside tolerance → report both numbers and the likely cause, and do not present either as final.

A 38% jump that a reconciliation check shows as 9% in the trusted view is a finding, not a rounding difference.

What the reply should look like

Answer-only:

Q3 revenue grew 38% over Q2.

Evidence-backed:

Q3 revenue was up 9% on Q2 (Q2: 1.20M, Q3: 1.31M). Checks: one row per order confirmed (48,112 orders, 48,112 rows). Matches the finance monthly revenue view within 0.2%. Assumption: orders containing both legacy and current SKUs are counted at full value. Query: [expand]

The figures above are illustrative, but the structure is the point. The finance lead still gets a one-line answer. Below it sits the evidence a sceptical reader needs, collapsed out of the way until someone wants it.

A checklist for any data-answering agent

Before an agent built on a warehouse, spreadsheet, or BI tool is allowed to answer with a number:

• [ ] It states the grain of every table it aggregates.
• [ ] It runs a row-count vs distinct-key check after every one-to-many join.
• [ ] It reconciles against a trusted figure when one exists, and reports the gap.
• [ ] It lists business assumptions it made (inclusion rules, time zones, refunds, currency).
• [ ] The query is attached to the answer, collapsed by default.
• [ ] A failed check changes the reply's wording, not only the trace log.

The takeaway

A data agent's most dangerous output is a clean, single number. Plausible-but-wrong aggregates are the normal failure of SQL, human or generated, and people catch them by looking at evidence, not by being smarter. If the agent's design strips that evidence out for a tidy reply, fixing the model will not help. Make the agent show its grain, its reconciliation, and its assumptions every time, and the 38% gets caught before it reaches a slide.