Review AI-generated SQL before you trust the number: a checklist with a runnable fixture

Photo by Chris Ried on Unsplash (Unsplash License).

A query that runs is not a query that's right. Most of us know this, and few of us act on it when an assistant hands back a query in four seconds and the number looks about right.

This is the review I run on AI-written SQL before its number goes anywhere near a sign-off. Eight checks, each written as a query that returns rows only when something is wrong. It comes with a tiny fictional fixture you can paste into SQLite, where a perfectly plausible revenue query returns 1,820 and the reviewed one returns 825. Same tables, same question, same month.

Nothing in the bad query is exotic. That's sort of the point.

Why the "it ran" signal got weaker, not stronger

The text-to-SQL benchmarks look great right now, and they're worth reading closely before you lean on them.

On the BIRD leaderboard, the top test execution accuracy as of 22 October 2026 is 83.40% (AskData, from AT&T's CDO team), with GrainSQL at 82.95% and Huawei's DataGallery at 82.39%. The human baseline on the same page, from data engineers and database students, is 92.96%. So the best system still misses roughly one question in six, and that's on a curated benchmark with clean schemas.

Numbers like these are easy to misread. Spider 2.0 uses enterprise-style workflows on Snowflake and BigQuery, and its top Spider 2.0-Snow entries sit around 96.70%, while the top Spider 2.0-Lite score is 76.23%. Same family of tasks, very different numbers. The Spider 2.0 repo also has a long changelog of evaluation fixes and ambiguity cleanups, and as of August 2026 a suspended Snowflake evaluation account was disrupting Snow access.

Then there's the paper that made me stop quoting leaderboard numbers at all. In Pervasive Annotation Errors Break Text-to-SQL Benchmarks and Leaderboards (Jin, Choi, Zhu and Kang, January 2026), expert review found annotation error rates of 52.8% in BIRD Mini-Dev and 62.8% in Spider 2.0-Snow. When they re-scored 16 open-source agents on a corrected BIRD Dev subset, relative performance moved by -7% to +31% and rankings shifted by up to nine places. BIRD has since shipped a 2026 Mini-Dev update with corrected annotations (28 October 2026), which is the right response. But think about what it means: the people grading the SQL got it wrong more than half the time on some subsets. The "gold" queries had the same class of mistakes I'm about to show you.

The vendors say it plainly too. Google's Gemini in BigQuery docs say the model "can generate output that seems plausible but is factually incorrect," recommend you "validate all output," and note it "might suggest different syntax each time that you enter the same prompt." That last line matters for review. If you regenerate, you're reviewing a new query.

And developers know it. In the Stack Overflow 2026 Developer Survey, the most common answer to "How much do you trust the output from AI tools or AI agents as part of your workflow?" was "I trust it when I can easily verify the output," at 48.0% of 14,304 respondents. Only 6.6% said they trust it for many tasks including important work decisions. (The survey announcement has the broader picture: more than 30,000 people took it.)

A revenue number in a steering-committee deck is an important work decision. So for an analyst the question is very practical: what does "easily verify" look like for SQL?

The take you can argue with

Here's my position. The person who reviews AI-written SQL before sign-off should be the person who owns the metric definition, not the strongest SQL writer in the room. On most teams that's the BA or the analyst, not the engineer.

The reason is in the fixture below. None of the five bugs is a syntax problem. Every one is a definition problem: which statuses count, whose calendar, which customers, what one row means. An engineer who writes beautiful SQL but wasn't in the room when finance said "test accounts don't count" will approve the query, and they'll approve it faster if it looks tidy.

You can disagree with me on this one. A fair counter is that BAs miss performance and correctness issues an engineer catches right away, like a NOT IN against a nullable column. True, and that's why the checklist includes those. But I'd still rather teach the definition owner eight mechanical checks than teach the strongest SQL writer every one of the business rules.

The fixture: Northstar Systems, October revenue

Northstar Systems is a fictional B2B software company, and every row below is invented. Here's the question someone typed into an assistant:

"What was Northstar's revenue from completed orders in October 2026, and how many orders?"

Before any SQL, write the definition down. A reader taught me this habit: name the grain first, "one row = one order line" (ontomato's comment). Shivam Pandit makes the same point at metric level in Defining revenue for an SQL agent, one row at a time: a definition an agent can use has to state grain, statuses, join behaviour and what an empty result means.

So here's Northstar's:

Now the schema and data. It runs as-is in the sqlite3 shell or any SQLite client.

-- Northstar Systems (fictional). All data invented.
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT, is_test INTEGER NOT NULL DEFAULT 0);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER, status TEXT, created_at_utc TEXT);
CREATE TABLE order_lines (line_id INTEGER PRIMARY KEY, order_id INTEGER, sku TEXT, qty INTEGER, unit_price INTEGER);
CREATE TABLE payments (payment_id INTEGER PRIMARY KEY, order_id INTEGER, amount INTEGER, paid_at_utc TEXT);
CREATE TABLE support_tickets (ticket_id INTEGER PRIMARY KEY, customer_id INTEGER, opened_at_utc TEXT);

INSERT INTO customers VALUES
 (1,'Alder Clinic',0),(2,'Birch Freight',0),(3,'Cedar Labs',0),(4,'Dogwood Foods',0),(5,'QA Test Account',1);

INSERT INTO orders VALUES
 (1001,1,'completed','2026-10-01 02:30:00'),
 (1002,2,'completed','2026-10-05 15:00:00'),
 (1003,3,'completed','2026-10-12 18:20:00'),
 (1004,1,'cancelled','2026-10-14 09:00:00'),
 (1005,4,'refunded', '2026-10-20 13:45:00'),
 (1006,2,'completed','2026-10-31 21:15:00'),
 (1007,5,'completed','2026-10-22 16:00:00'),
 (1008,3,'completed','2026-11-01 02:10:00'),
 (1009,4,'pending',  '2026-10-25 11:30:00');

INSERT INTO order_lines (order_id,sku,qty,unit_price) VALUES
 (1001,'NS-BASE',1,120),
 (1002,'NS-PRO',1,200),(1002,'NS-ADDON',1,50),
 (1003,'NS-PRO',1,300),(1003,'NS-ADDON',1,80),(1003,'NS-SETUP',1,20),
 (1004,'NS-BASE',1,90),
 (1005,'NS-PRO',1,150),
 (1006,'NS-BASE',1,60),(1006,'NS-ADDON',1,40),
 (1007,'NS-ENT',1,500),
 (1008,'NS-BASE',1,75),
 (1009,'NS-PRO',1,110);

INSERT INTO payments (order_id,amount,paid_at_utc) VALUES
 (1001,120,'2026-10-01 02:31:00'),
 (1002,250,'2026-10-05 15:02:00'),
 (1003,200,'2026-10-12 18:25:00'),(1003,200,'2026-10-26 10:00:00'),
 (1005,150,'2026-10-20 13:50:00'),
 (1006,100,'2026-10-31 21:16:00'),
 (1007,500,'2026-10-22 16:01:00'),
 (1008,75,'2026-11-01 02:12:00');

INSERT INTO support_tickets (customer_id,opened_at_utc) VALUES
 (1,'2026-10-03 14:00:00'),(3,'2026-10-15 10:00:00'),(NULL,'2026-10-18 09:00:00');

Three rows are planted on purpose. Order 1001 is 2:30 a.m. UTC on 1 October, which is 10:30 p.m. on 30 September in New York. Order 1008 is the reverse: November in UTC, October in New York. Order 1003 was paid in two instalments.

The query that looks fine

This is an illustrative example I wrote myself, in the shape I see assistants produce for this kind of prompt. I'm not claiming any specific tool wrote it. Read it once and note what you'd flag before scrolling.

SELECT COUNT(DISTINCT o.order_id) AS orders,
       SUM(l.qty * l.unit_price)  AS revenue
FROM orders o
JOIN order_lines l ON l.order_id = o.order_id
JOIN payments p    ON p.order_id = o.order_id
WHERE o.status != 'cancelled'
  AND o.created_at_utc BETWEEN '2026-10-01' AND '2026-10-31';

It looks tidy, and the aliases are sensible. Joining payments even feels responsible, as if it's making sure we only count money that actually came in. Here's the output (SQLite 3.53.1, run through Python's sqlite3 module):

orders | revenue
5 | 1820

The reviewed query

WITH order_totals AS (          -- grain: one row = one order
  SELECT order_id, SUM(qty * unit_price) AS order_revenue
  FROM order_lines
  GROUP BY order_id
)
SELECT COUNT(*)                          AS orders,
       COALESCE(SUM(t.order_revenue), 0) AS revenue
FROM orders o
JOIN order_totals t ON t.order_id = o.order_id
JOIN customers c    ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
  AND c.is_test = 0
  -- October in America/New_York (EDT, UTC-4 all month), half-open range
  AND o.created_at_utc >= '2026-10-01 04:00:00'
  AND o.created_at_utc <  '2026-11-01 04:00:00';

orders | revenue
4 | 825

Side by side:

The flawed number is 121% too high. Look at the order count, though: five versus four. If you sanity-checked the count against a CRM export, you'd shrug and approve it. COUNT(DISTINCT ...) quietly absorbed the duplication that SUM didn't.

That's the pattern Shivam's post names: "Distinct values are not distinct orders." I'd add a corollary. A DISTINCT you didn't ask for is a confession that someone saw duplicates and chose to hide them instead of explaining them.

Where the 995 went: a fix ladder

One fix at a time, real output:

step | revenue | orders
1 flawed as written | 1820 | 5
2 drop payments join | 1530 | 6
3 status = completed | 1270 | 4
4 ET half-open range | 1325 | 5
5 exclude test accounts | 825 | 4

Step 2 is the interesting one. Removing the payments join takes out 400 of double-counted revenue from order 1003, but the order count goes up. The inner join to payments had been silently dropping order 1009, the pending order with no payment. Two bugs were partly cancelling each other out. That happens more than you'd think, and it's why I don't trust "the total looks roughly right" as evidence of anything.

Step 4 also goes up: the BETWEEN with bare dates excluded order 1006 at 9:15 p.m. UTC on 31 October (because '2026-10-31 21:15:00' sorts after '2026-10-31'), and the UTC calendar put 1008 in November and 1001 in October. Fixing the calendar removes one order and adds two.

Photo by Ilya Pavlov on Unsplash (Unsplash License).

The checklist: 8 checks, each one a query that fails loudly

Manuel Diaz left a comment on one of my Dev.to posts that changed how I write these: make each check a query that returns rows only when something is wrong. Zero rows is a pass, and anything else is a conversation. Here's the review in that form.

1. Grain: does the join multiply rows?

Ask: after all the joins, how many rows does each order have, compared with how many lines it really has?

SELECT o.order_id, COUNT(*) AS joined_rows,
       (SELECT COUNT(*) FROM order_lines x WHERE x.order_id = o.order_id) AS line_rows
FROM orders o
JOIN order_lines l ON l.order_id = o.order_id
JOIN payments p    ON p.order_id = o.order_id
GROUP BY o.order_id
HAVING COUNT(*) > (SELECT COUNT(*) FROM order_lines x WHERE x.order_id = o.order_id);

order_id | joined_rows | line_rows
1003 | 6 | 3

One row back means fail. Order 1003 has three lines and two payments, so the join gives six rows. The usual sources of fan-out are payments, shipments, status history, any table with "events" in its name, and slowly changing dimensions joined without a date condition. The fix is to aggregate to the answer's grain before joining, which is exactly what the CTE in the reviewed query does.

2. Every inner join: what does it drop?

Fan-out inflates; inner joins deflate. For each JOIN, count the left-side rows that have no match. If the number isn't zero, someone has to decide whether those rows should be gone. The payments join dropped order 1009, which happened to be right for the wrong reason.

3. Status filters: list the values, don't guess them

SELECT COALESCE(status,'<NULL>') AS status, COUNT(*) AS n
FROM orders GROUP BY status ORDER BY n DESC;

status | n
completed | 6
refunded | 1
pending | 1
cancelled | 1

status != 'cancelled' is an exclusion list, and exclusion lists rot. Every new status (refunded, on_hold, chargeback) silently joins the revenue. Prefer an inclusion list, status IN ('completed'), that maps to the written definition. Also note that != drops rows where status is NULL, which is a separate decision nobody made.

4. Date boundaries and time zones

SELECT order_id, created_at_utc,
       datetime(created_at_utc, '-4 hours') AS created_at_et
FROM orders
WHERE strftime('%m', created_at_utc) <> strftime('%m', datetime(created_at_utc, '-4 hours'))
   OR created_at_utc LIKE '2026-10-31 %';

order_id | created_at_utc | created_at_et
1001 | 2026-10-01 02:30:00 | 2026-09-30 22:30:00
1006 | 2026-10-31 21:15:00 | 2026-10-31 17:15:00
1008 | 2026-11-01 02:10:00 | 2026-10-31 22:10:00

Three rows, three questions. What zone are the timestamps stored in? What zone does the business mean by "October"? Does the range include the whole last day? Use half-open ranges (>= start AND < next_start), never BETWEEN on timestamps.

Daylight saving time adds a trap, and this year it sits right on the boundary. US DST ended at 2 a.m. on Sunday, 1 November 2026, so midnight on 1 November in New York was still UTC-4, which is 04:00 UTC. Midnight on 1 December is UTC-5, 05:00 UTC. A hardcoded -4 hours is only right for part of the year. In a warehouse, use its real time-zone conversion function (CONVERT_TIMEZONE, AT TIME ZONE, DATETIME(ts, 'America/New_York')) and check what it does at the switch.

Alex Shev's comment on boundary records is the habit to copy here (comment): put a row a minute either side of every boundary into your test data, deliberately.

5. NULLs in aggregates and in NOT IN

Two different problems, both quiet.

Aggregates skip NULLs, and COUNT(*) doesn't:

SELECT COUNT(*) AS all_rows, COUNT(customer_id) AS non_null_ids,
       COUNT(DISTINCT customer_id) AS distinct_ids
FROM support_tickets;

all_rows | non_null_ids | distinct_ids
3 | 2 | 2

If an assistant writes AVG(discount) and a NULL discount means "no discount," the average covers discounted rows only. That might be the definition, but usually it's not. Ask what NULL means in that column, then choose between AVG(x) and AVG(COALESCE(x, 0)) on purpose.

NOT IN is worse. Ask for "customers with no support tickets":

SELECT customer_id, name FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM support_tickets);

customer_id | name
(0 rows)

Zero. A single ticket with a NULL customer_id makes NOT IN evaluate to unknown for every customer, so nothing comes back, and there's no error. The same question with NOT EXISTS:

SELECT c.customer_id, c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM support_tickets t WHERE t.customer_id = c.customer_id);

customer_id | name
2 | Birch Freight
4 | Dogwood Foods
5 | QA Test Account

And notice the test account turned up again. Every check leaks into the others.

6. The empty set: what should "nothing" return?

SELECT SUM(l.qty*l.unit_price) AS raw_sum,
       COALESCE(SUM(l.qty*l.unit_price),0) AS coalesced,
       COUNT(*) AS rows_matched
FROM orders o JOIN order_lines l ON l.order_id=o.order_id
WHERE o.created_at_utc >= '2026-12-01 05:00:00' AND o.created_at_utc < '2027-01-01 05:00:00';

raw_sum | coalesced | rows_matched
NULL | 0 | 0

SUM over nothing is NULL. Assistants often wrap it in COALESCE(..., 0) because a 0 looks friendlier on a dashboard. But "zero revenue in December" and "December hasn't loaded yet" are different statements, and the coalesced query can't tell them apart. My rule: return the row count next to the total, and let the definition decide what an empty month displays. I used COALESCE in the reviewed query above because Northstar's definition says October is loaded. That's a claim you should check with a control total from the source system, not assume.

7. Silent DISTINCT

Search the query for DISTINCT. For each one, ask what duplicate it was hiding and why that duplicate exists. If nobody can answer, remove the DISTINCT and run check 1. In the fixture, COUNT(DISTINCT o.order_id) gave a believable order count on top of a revenue figure that was more than double.

SELECT DISTINCT * at the top of a query and UNION (instead of UNION ALL) belong in the same bucket. Both deduplicate without telling you what got merged.

8. Hallucinated columns, and the SQLite trap

Assistants invent plausible column names: region, order_total, is_active. Usually the database throws an error and you're fine:

SELECT order_id, region FROM orders LIMIT 2;

Error: no such column: region

But wrap that invented name in double quotes, as some generators do by habit:

SELECT order_id, "region" FROM orders LIMIT 2;

order_id | "region"
1001 | region
1002 | region

No error. In this SQLite build, a double-quoted identifier that doesn't match a column falls back to a string literal, so every row gets the word "region." Group by it and you get one tidy bucket. Whether this happens depends on how your SQLite build is compiled and configured (look up its "double-quoted string literals" setting); the library build I ran here allows it. Other engines may behave differently, but the habit still matters: for every column the query uses, confirm it exists in the schema and means what the query assumes. A column that exists but means something else, like created_at versus booked_at, is the harder version to catch.

The review card

If you want one thing to copy, it's this. Paste it into the PR description or the ticket next to any AI-drafted query.

Row 9 isn't SQL review in the strict sense. It's Alex Shev's control-total habit, and it's the only check that catches a query that's internally consistent and still wrong because the data underneath is incomplete.

I keep a longer set of these as copy-paste queries in data-validation-checks.sql in my BA Toolkit, and the reconciliation walk-through is in examples/revenue-reconciliation.

What the sharpest commenter will say

"Just give the model the schema and the definition and it'll get it right." Partly. Giving an assistant the definition table above would probably fix the status and test-account bugs. It's the single best thing you can do on the generation side, and it's what the r/LLMDevs commenters meant by a semantic layer. But it doesn't remove the review. The definition can be incomplete (ours says nothing about NULL statuses), and Google's own docs warn the same prompt can give different SQL each time. You'd be reviewing a better first draft, which is nice, not skipping the review.

"Ask the AI to review its own query." I do this, and it does catch things. I treat it like a linter: helpful for the mechanical items (NOT IN, BETWEEN on timestamps), weak on definition items, because the model doesn't know that finance excludes test accounts unless you tell it, and if you told it, it should have used that the first time.

"Benchmarks say 83%, so it's fine most of the time." Benchmarks score whether the result set matches a reference answer. The annotation-error paper suggests the references themselves were wrong a lot of the time. And your schema isn't a benchmark schema. A per-query review is the only number that applies to your query.

When to rerun the review: whenever the query is regenerated, the source schema changes, a new status value appears, the reporting period crosses a DST change or year end, or the definition changes. A reviewed query is reviewed against a definition and a snapshot of data. Change either and the review expires.

What I didn't test

Honest limits of this fixture:

• It's SQLite, with timestamps stored as text. Warehouses store real timestamp types, and time-zone functions differ between engines. Check 4's -4 hours is a teaching shortcut, not a production pattern.
• There's no NULL status row in the data, so I haven't shown != dropping one. Add (1010, 2, NULL, '2026-10-15 12:00:00') to the orders and see what each query does.
• No currency, no tax, no partial refunds, no line-level discounts. Real revenue definitions have all of these, and each one is another grain question.
• I didn't time anything. Some fixes (pre-aggregating in a CTE) change query plans on large tables.
• I wrote the flawed query. It's realistic, but it's mine, so it's a teaching example, not evidence about how often any assistant makes these mistakes.

What I'd do on Monday

If you're reviewing AI-drafted SQL starting this week, here's the order I'd go in.

1. Before opening the query, write the definition table: grain, statuses, calendar and zone, exclusions, empty-set rule. Five lines. If you can't fill it in, the problem isn't the SQL.
2. Build a ten-row fixture with one boundary row on each side of the period, one order with two payments, one test account, one NULL in a key you filter on. Fifteen minutes. Reuse it for every query on that metric.
3. Run the grain check and the status list first. They catch the most, fastest.
4. Ban BETWEEN on timestamps and NOT IN on subqueries in your team's review notes. These two are pure mechanics and easy to agree on.
5. Return the row count with every total. It costs one column and answers the empty-set question for free.
6. Get one control total from the source system for the period and write down the tolerance you accept.
7. Save the reviewed query with its definition next to it, so the next regeneration has something to be compared against.

If you're on the product side setting up an AI analytics tool, my AI agent test plan template is built for the next step: a golden set of questions with known answers, run more than once.

Watch: finding the gap when two numbers don't match

This is my short animated explainer on reconciling two numbers that should agree. It walks through definition, calendar and time zone, grain and fan-out, and orphan rows, which are most of the same failure modes from the reconciliation side.

Video: "Dashboard Numbers Don't Match? Find the Gap in SQL" by Prachit Parikh, on YouTube.

For the testing side of AI tools in general (golden sets, running each case three times, flagging unstable answers), there's AI Agent Testing for Business Analysts: Dev, UAT, Prod.

Discussion

A few questions I'd like real answers to:

1. Who signs off on AI-drafted SQL on your team: the person who wrote the prompt, an engineer, or the metric owner? Has that ever gone wrong?
2. What's a bug you've caught in generated SQL that isn't on this list? I suspect window functions with the wrong PARTITION BY belong here.
3. Should an empty period return 0, NULL, or no row at all? I've seen teams argue all three.
4. Has giving the assistant a written metric definition measurably reduced the fixes you make?

Comment prompt: paste the one-line check you'd add to the review card, written as a query that returns rows only when something's wrong. I'll test the good ones against the Northstar fixture and post the output in reply.

References

• BIRD benchmark leaderboard and news (top test EX 83.40%, human 92.96%, Mini-Dev 2026 update): https://bird-bench.github.io
• Spider 2.0 leaderboard (Snow and Lite top scores): https://spider2-sql.github.io
• Spider 2.0 GitHub repository and changelog: https://github.com/xlang-ai/Spider2
• Jin, Choi, Zhu, Kang, "Pervasive Annotation Errors Break Text-to-SQL Benchmarks and Leaderboards," arXiv:2601.08778, January 2026: https://arxiv.org/abs/2601.08778
• Google Cloud, "Write queries with Gemini assistance" (BigQuery): https://docs.cloud.google.com/bigquery/docs/write-sql-gemini
• Stack Overflow 2026 Developer Survey, full results: https://survey.stackoverflow.co/2026/
• Stack Overflow blog, "The results of the 2026 Developer Survey are here," 6 October 2026: https://stackoverflow.blog/2026/10/06/the-results-of-the-2026-developer-survey-are-here/

Reader lessons credited above: ontomato, Shivam Pandit, Alex Shev, and Manuel Diaz.

Related posts of mine: 7 SQL checks I run before UAT sign-off, Data mapping document template, and AI agents can write the code now. The bottleneck is the requirements. More of my work is on my portfolio page.

This article was created with the help of AI. Every query and output above was run against the Northstar fixture in SQLite and checked before publishing.

About me

I'm Prachit Parikh, a business analyst. I turn what stakeholders ask for into requirements a team can test, check the data with SQL, and see changes through UAT. I keep free templates, including the SQL data validation checks behind this review card, in my BA Toolkit on GitHub, and I make short animated explainers on YouTube. Besides Dev.to, I write on Substack, Medium, Hashnode, Blogger and WordPress, and I answer questions on Quora. You can also find me on LinkedIn, Bluesky, Kaggle, Credly, Crunchbase and Gravatar. Everything is collected on my website and my profile hub.