FAILURE MODES

AI Said $920. It Was $400. Five Lines Fixed It.

We asked two AI models for October revenue from a wide sales table, 12 times. All 12 said $920. The real number is $400: gross_amount was the order total repeated on every line item.

2026-11-05

We asked two AI models for October revenue from a wide sales table, 12 times. All 12 said $920. The real number is $400. The table, fct_sales, has one row per line item, and gross_amount is the order total copied onto every line. SUM(gross_amount) counts a 3-line order 3 times, and units still come out right, which is why nobody catches it.

The 46-second reel: AI said $920. It was $400. Five lines fixed it.

The bug

fct_sales is a wide reporting table: one row per order line item, keyed on (order_id, sku). gross_amount is the ORDER total, repeated on every line of the same order. It is not a line amount. October’s shipped rows:

order_id sku qty gross_amount
101 MUG 1 120
101 TEE 2 120
102 CAP 1 80
104 HOODIE 1 200
104 TEE 1 200
104 MUG 3 200

SUM(gross_amount) over those six rows is 920. SUM(qty) is 9, and 9 is the right unit count, so the query looks healthy from every angle except the one that matters. The real revenue is one row per order: 120 plus 80 plus 200, which is 400. Order 104 is one order with 3 lines, counted 3 times. Nothing errored. The query ran fine and returned a wrong number.

What the models did

We gave the same request to two AI models from one provider, 12 tries per setup: 10 tries on Nov 2 plus 2 on Oct 30, 6 tries per model per setup, same prompt both days. Without any context about the table, all 12 said $920 with 9 units. Then we added a 5-line note saying what one row means, and asked again. Same request, 12 more tries. All 12 said $400 with 9 units. The note changed every answer, and the units never changed, which is exactly why the bug survives review: the number that looks wrong is the only one that is wrong.

The fix

The 5-line note, verbatim from our AGENTS.md:

  • Grain: one row per order line item. Key: (order_id, sku).
  • gross_amount is the ORDER total. It repeats on every line of the same order. It is not a line amount.
  • qty is per line.
  • Never SUM or AVG gross_amount directly on fct_sales. Reduce to one row per order_id first. Do not use SUM(DISTINCT gross_amount): two orders can have the same total.
  • Every revenue query must state its reconciliation: revenue from your query must equal the sum of gross_amount over one row per order_id with the same filters.

A note is advice, not enforcement. The enforcement is a test: a dbt test that checks the total against the orders table rejects the $920 query. Our grain kit example runs dbt build with 8 tests, 7 pass and 1 errors, and the failing test is reconciles_with on the naive total. Three rules to keep: name the grain of every table, roll up before you join, reconcile every total.

What we did not verify

This was our own test on synthetic data, run Oct 30 and Nov 2, 2026, on DuckDB 2.0. Two models from one provider, 12 tries per setup. It is a small test, not a benchmark, and it says nothing about how often AI gets grain wrong in general. The voiceover in the reel is AI-generated. Watch the reel on Instagram and Threads.

This post started as an Instagram post →

Discussion

Talk it through

Argue with us on Instagram.