Home / Blog / Validate AI-Generated SQL

AI SQL validation

How to Validate AI-Generated SQL: 7 Checks Before Production

AI writes syntactically valid SQL in seconds. The dangerous query is not the one that errors. It is the one that runs, returns plausible numbers, and answers a question nobody asked.

Published September 9, 202610 min readBy Can Goktug Ozdem, Founder of SQL Quest

Short answer: write the result contract first, verify the schema is real, test filters and NULLs, measure join cardinality, confirm the aggregation grain, check known rows by hand, then read the plan. Never approve a query from syntax alone, and never from row count alone — the wrong query below returns eight times more rows than the right one.

A query that runs, and is wrong for every row it returns

A fraud reviewer asks a reasonable question: which cardholders have never used their card in their own country? Cards that only ever transact abroad are worth a look.

Here is what an assistant returns. The schema is real, the joins are correct, and it executes without complaint.

SELECT a.account_id, a.email, a.country, COUNT(*) AS txn_count
FROM accounts a
JOIN transactions t ON t.account_id = a.account_id
JOIN merchants m ON m.merchant_id = t.merchant_id
WHERE m.country <> a.country
GROUP BY a.account_id, a.email, a.country
ORDER BY txn_count DESC;

It returns 200 rows. There are exactly 200 cardholders in the ledger, so this query has flagged every single one as never having transacted at home. Nothing about the output looks broken: the emails are real, the countries are real, the counts descend sensibly from 21.

The fault is one line. WHERE decides one row at a time, and “never” is a statement about a whole group, so it cannot live there. WHERE m.country <> a.country keeps the foreign transactions and discards the domestic ones — which means it deletes exactly the evidence that would have disqualified an account. What survives is the set of cardholders with at least one foreign transaction, and in this ledger 1,715 of 2,165 transactions cross a border, so that is nearly everybody.

The condition has to be expressed as a property of the group: count the transactions you do not want, and require that count to be zero.

SELECT a.account_id, a.email, a.country, COUNT(*) AS txn_count
FROM accounts a
JOIN transactions t ON t.account_id = a.account_id
JOIN merchants m ON m.merchant_id = t.merchant_id
GROUP BY a.account_id, a.email, a.country
HAVING SUM(CASE WHEN m.country = a.country THEN 1 ELSE 0 END) = 0
ORDER BY txn_count DESC;

24 rows. That is the real answer, and it is 8× smaller than the wrong one.

Notice which error is harder to catch. A query that returns nothing makes you look at it. This one returned more rows than the truth, and a reviewer skimming the top of the result would have seen plausible data and moved on. Volume reads as confidence. That is what makes generated SQL worth a procedure rather than a glance.

Solve this one yourself, on the same ledger →

Check 1: write the result contract before you read the query

Before reviewing a line of SQL, state four things in plain language. If you cannot, the problem is the request, not the query.

• What does one output row represent? One cardholder? One transaction? One cardholder-month?
• What must be included and excluded, stated as a rule rather than an example.
• Which boundaries and time zone apply, and whether the range is inclusive at both ends.
• What happens when a value is missing. Missing is not zero and it is not false.

For the example above the contract is: one row per cardholder, where the count of transactions at merchants in the cardholder’s own country is zero. Written down, that sentence exposes the bug on its own — it is a statement about a count, and the wrong query never counts anything before filtering.

Check 2: verify the schema is real, not invented

Assistants that are not given a schema will infer one, and inferred column names are plausible by construction. Confirm every table and column exists, then confirm the ones that sound obvious.

In this ledger, country exists on both accounts and merchants, and they mean different things: where the cardholder lives, and where the shop is. A query that joins them and picks the wrong one still runs. So does one that reads transactions.status expecting refunds — every row in this table carries the same value, and the refund signal actually lives in a separate chargebacks table entirely.

Check 3: challenge filters, NULLs, and boundaries

A WHERE clause keeps only rows whose expression evaluates to true. Rows evaluating to false and rows evaluating to NULL are both discarded, and the second kind is silent.

The chargebacks table shows it. It holds 76 disputes, and resolved_at is NULL on the 17 that are still open.

SELECT COUNT(*)           AS all_disputes,      -- 76
       COUNT(resolved_at) AS resolved_disputes -- 59
FROM chargebacks;

COUNT(*) counts rows. COUNT(column) counts non-NULL values. Neither is wrong; they answer different questions, and an assistant will pick one without telling you which question it thought you asked. The same gap appears in any filter written as resolved_at <> something, which quietly drops all 17 open disputes.

Test each predicate on its own, and build a fixture for every edge case — a test dataset of clean rows proves almost nothing. If NULL semantics are the weak point, the NULL handling exercises drill exactly this, and IS NULL versus = NULL covers why the comparison form never matches.

Count resolved and still-open disputes yourself →

Check 4: measure join cardinality before you aggregate

Ask what relationship each join represents, then measure it rather than assuming.

SELECT COUNT(*)                     AS joined_rows, -- 2165
       COUNT(DISTINCT a.account_id) AS people      -- 200
FROM accounts a
JOIN transactions t ON t.account_id = a.account_id;

One cardholder, many transactions. After this join the grain is a transaction, not a person, and every per-person number computed downstream is now a per-transaction number wearing a person’s name. Run this pair before and after each join. If the two numbers differ and you expected them to match, stop.

Reach for DISTINCT only once you know why the duplicates are there. It can be the correct fix; it is just as often a lid on a broken join. Joins and cardinality walks through the shapes.

Check 5: confirm the grain and the denominator

Fan-out does not announce itself in an aggregate. Sum a per-account value across a joined transaction table and the total is multiplied by each account’s transaction count. The result is a number, formatted correctly, and far too large.

Two questions catch most of it. What is one row of the input to this aggregate? And what is on the bottom of every ratio? — COUNT(*) after a one-to-many join counts transactions, while COUNT(DISTINCT account_id) counts cardholders, and a spend-per-customer figure built on the first is not spend per customer.

When both aggregates are needed, compute them at their own grain in separate CTEs and join the results, rather than asking one pass to be two grains at once.

Compute spend and disputes without the fan-out →

Check 6: check known rows by hand

Aggregates hide errors; individual rows do not. Pick one entity you can reason about end to end and follow it through.

• Take one cardholder from the output and list their transactions directly. Does the count match?
• Take one row the query excluded and confirm it deserved to be excluded. Wrong answers are usually wrong about what is missing, and no output row will ever tell you about a row that is not there.
• Recompute one number a second way — a different join order, or a subquery instead of a join. Agreement is evidence; disagreement is a finding.

This is the check that would have caught the opening example in under a minute. Any one of the 200 accounts, listed transaction by transaction, would have shown a domestic charge sitting in plain view.

Check 7: read safety, cost, and the plan — last

Only after the meaning is settled. Confirm the statement is a read, that no UPDATE or DELETE arrived alongside it, and that it is bounded on tables that will not stay small.

EXPLAIN QUERY PLAN shows how SQLite intends to execute a query: which tables are scanned, which indexes are used, whether a temporary B-tree is built for sorting. That is a performance and diagnostic tool. It cannot tell you the query answers the wrong question — the plan for the 200-row query and the plan for the 24-row query are both perfectly healthy. SQLite’s own documentation warns that the output format is not stable across releases, so read it, do not parse it.

The checklist

• The result contract is written down, in a sentence, before review.
• Every table and column exists, and same-named columns on different tables are distinguished.
• Each predicate is tested alone, including its behaviour on NULL.
• Row count and entity count are measured on both sides of every join.
• The aggregate’s input grain and every denominator are named explicitly.
• At least one included row and one excluded row are verified by hand.
• Group-level conditions live in HAVING, never in WHERE.
• The statement is a read, is bounded, and its plan has been looked at.

Validating SQL is one part of reviewing AI output. For a reusable workflow that tests prompts against success criteria and failure cases, continue with ClaudeQuest’s structured AI-output review loop.

SQL Quest and ClaudeQuest are independent Datrick learning products. SQL Quest is not affiliated with, endorsed by, or sponsored by Anthropic. Anthropic and Claude are trademarks of Anthropic PBC.