AI-generated SQL: test the query before trusting the explanation

SQL can execute successfully and still answer the wrong business question. Before accepting an assistant's explanation, test the query against a metric contract and a fixture with known edge cases: multiple item rows, missing dimension records, boundary dates and status exclusions.

Syntax success is one check. Business correctness requires evidence about grain, eligibility and the resulting values.

Inspect a plausible wrong query

Suppose an assistant proposes the following query for January completed-order amount. This is a deliberately seeded teaching candidate, not a recorded output from a named model.

SELECT COUNT(*) AS rows_counted,SUM(o.order_total_paise) AS amount_paise
FROM orders o JOIN order_items i ON i.order_id=o.order_id
WHERE o.status='completed'
 AND o.ordered_at>='2026-01-01T00:00:00'
 AND o.ordered_at<'2026-02-01T00:00:00';

The query executes, but it counts twelve item-level joined rows and sums repeated order-header amounts to 171,000 paise. The correct order-level population contains eight orders totaling 104,000.

Joining items is not inherently wrong. Summing an order-level value after that join without restoring the intended grain is the error.

Compare execution with an independent reference

The Python example loads the original synthetic commerce database and compares both SQL results with the separate CSV-based calculator used by the analyst-AI lab.

import importlib.util
from pathlib import Path
from calculator import calculate

path = Path('../commerce-sql/build_and_verify.py').resolve()
spec = importlib.util.spec_from_file_location('commerce_reference_fixture',path)
module = importlib.util.module_from_spec(spec)
spec.loader.exec_module(module)
db = module.database()
parameters = ('2026-01-01T00:00:00','2026-02-01T00:00:00')
wrong = db.execute('''SELECT COUNT(*),SUM(o.order_total_paise)
 FROM orders o JOIN order_items i ON i.order_id=o.order_id
 WHERE o.status='completed' AND o.ordered_at>=? AND o.ordered_at<?''',parameters).fetchone()
correct = db.execute('''SELECT COUNT(*),SUM(order_total_paise) FROM orders
 WHERE status='completed' AND ordered_at>=? AND ordered_at<?''',parameters).fetchone()
db.close()
reference = calculate()
assert wrong==(12,171000)
assert correct==(reference['eligible_order_count'],reference['value'])==(8,104000)
assert wrong!=correct
print({'seeded_wrong_query':wrong,'verified_order_grain_query':correct})

Both paths use the same original teaching fixture, but one calculates directly from CSV source rows while the other executes SQL against its reference database. If either fixture representation changes, reconciliation should fail until the difference is understood.

Test the tempting fixes too

SUM(DISTINCT order_total_paise) is not a general repair. Different orders can have equal amounts, so it removes legitimate values as well as repeated ones. In this fixture it produces 82,000 paise.

An inner customer join creates another problem: the unmatched customer for O1009 causes a valid 9,000-paise order to disappear. That produces 95,000, violating a contract that includes every otherwise eligible order.

The seeded answer cases preserve these failures so a checker can be evaluated against them. They are not evidence of a live model's failure rate.

Separate permission checks from result checks

Run untrusted generated queries only within a suitably restricted environment. This example uses a disposable synthetic in-memory database and executes only the displayed read queries.

A query starting with SELECT is not a complete safety policy, and read access can still expose confidential information or consume excessive resources. A deployed analyst tool needs appropriate data permissions, permitted operations, result limits and resource controls.

Those controls protect the execution boundary. They do not make the query's business logic correct. A read-only query can still double-count orders, use a wrong denominator or infer an unsupported period.

Review the explanation after the result

Ask whether the narrative matches the tested measure, units and limitations. Correct SQL for completed-order amount still does not establish recognized revenue, profit or a causal effect of a campaign.

Exercise: add another completed order with the same amount as an existing order. Predict why a SUM(DISTINCT amount) repair becomes more misleading, then verify the order-grain query against the source-row calculator.

NeuraPath's Data Analytics with Generative AI course connects AI-assisted SQL with independent verification. A useful explanation follows a tested query and a clear metric contract.

Continue learning

This article is part of the Generative AI for verified analyst work sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Write an analyst prompt with a metric definition and evidence contract.
• Continue with Use an LLM to explain a chart without inventing causality.