A text-to-SQL system should not pass because its query runs. Executable SQL can use the wrong metric, date, population, join, or permission scope and still return a polished table.
Evaluation must begin with business truth: what the question means, which data the user may access, what properties a correct result must satisfy, and which alternative queries are acceptable.
That makes the truth set an engineering asset, not a list of golden SQL strings.
Start with the questions people actually ask
Public benchmarks help compare research systems, but your environment has its own schemas, business language, access rules, and ugly edge cases. Google Cloud notes that public text-to-SQL benchmarks often do not represent broad real-world schemas and workloads. Its engineering guidance combines coverage, offline and user measures, human and automated review, and continuous evaluation. Google Cloud’s text-to-SQL article provides useful background.
Build the initial set from real analytical demand:
- Recurring questions from operating reviews
- Ad hoc questions that required analyst interpretation
- Disputed metrics
- Questions that cross several tables
- Requests that should be clarified
- Requests that should be refused because of access or missing evidence
- Incidents caused by a prior query or report
Redact sensitive data where necessary, but preserve the ambiguity and structure that made the question hard.
A truth item needs more than expected SQL
Two SQL statements can be different and still return the same correct business result. One SQL statement can also match a golden query on today’s data while encoding the wrong logic.
Define each truth item with this schema:
| Field | Purpose |
|---|---|
| Question | Original user wording |
| Intended meaning | Metric, population, time, grain, and comparison |
| User role | Identity and authorized data scope |
| Required clarification | Missing information the system must request |
| Approved sources | Tables, views, semantic models, and versions |
| Valid joins | Allowed relationship paths and grain constraints |
| Result invariants | Properties any correct result must satisfy |
| Reference result | Expected rows, aggregates, or validation method |
| Tolerated alternatives | Equivalent filters, joins, rounding, or output forms |
| Refusal condition | When the request should not execute |
| Owner | Person accountable for meaning and change |
The item should be versioned. A metric definition or schema change can make last month’s reference wrong without making the system worse.
Separate four kinds of correctness
Syntax and execution
Does the query parse and run in the target SQL dialect? This is necessary and easy to automate. It is also the weakest gate.
Result correctness
Does the result match the reference result or satisfy the approved invariants? Compare values, row sets, ordering only where it matters, null behavior, rounding, and time boundaries.
Semantic correctness
Did the system implement the intended business meaning? Inspect metric selection, filters, population, date logic, grain, deduplication, and joins. A correct-looking output on one fixture can hide logic that fails when the data changes.
Authorization correctness
Did the query stay inside the user’s allowed data scope? Check tables, rows, fields, and derived values. An analytically correct answer is still a failure if the user was not allowed to see it.
Keep these results separate. An aggregate accuracy number can hide a severe access failure or a systematic metric error.
Use data mutations to catch accidental passes
A query may return the expected answer for the wrong reason. Build small fixture variations that expose the logic.
For a revenue-by-region question, add cases such as:
- A refunded transaction
- A duplicate line item
- A customer with no region
- Two currencies
- A late-arriving correction
- A boundary timestamp
- A restricted region
Then state the expected invariant for each variation. This checks whether the query carries the business rule rather than merely matching one static output.
The IBM text-to-SQL evaluation toolkit is a current primary implementation resource for teams exploring evaluation workflows. Its public repository demonstrates that this is an active engineering discipline, not a settled single metric. IBM’s toolkit can inform tooling choices without defining your business truth.
Test clarification and refusal as first-class outcomes
“Show me our best customers” is not fully specified. Best could mean revenue, margin, retention, growth, or strategic importance. The system should identify the exact missing choice rather than guess silently.
Include tasks where the correct outcome is:
- Ask which metric applies
- Ask for a date range
- Ask which business unit or population applies
- Explain that the requested source is unavailable
- Refuse because the user’s role lacks access
- Return a partial answer with the omitted scope stated clearly, if policy permits
Grade the specificity of the clarification. “Can you clarify?” is weaker than “Should best mean recognized revenue or gross margin, and for which period?”
Inspect the trace behind the final table
Retain the question, retrieved schema context, selected examples, generated query, dry-run or parser results, execution details, source version, result hash, explanation, and final answer.
Trace review finds failure modes that result comparison misses:
- The system selected an unauthorized table but filters happened to hide the data.
- It used an obsolete column that still matches the current fixture.
- It generated several bad queries before landing on a correct result.
- It applied a post-query calculation not visible in the SQL.
- It stated a conclusion the result set did not support.
Deterministic checks should handle what code can prove. Human reviewers should adjudicate business meaning and material ambiguity. A model-based grader may help triage explanations, but it should not be the sole judge of access or numerical truth.
Design the split before reporting results
Separate development and holdout items. Group related phrasings so near-duplicates do not leak across the split. If five versions of the same question appear in both sets, the holdout result will exaggerate generalization.
Report performance by slices that matter:
- SQL dialect
- Question complexity
- Join count or schema breadth
- Metric family
- User role
- Ambiguity type
- Time logic
- Failure severity
Also report the number of items, trial policy, truth-set version, and unresolved adjudications. Do not present the result as a universal accuracy rate.
Make every production correction improve the set
When a real user finds a failure, preserve the original wording, role, context, query, result, and corrected interpretation. Add a regression item after the owner approves the expected behavior.
Run the suite when models, prompts, retrieval, semantic definitions, permissions, or schemas change. Continuous evaluation is valuable because any of those layers can alter a previously acceptable query.
Brainiac can help an analytics team define this truth-set contract, build representative fixtures, and connect query results to governed business meaning. That work stays distinct from a buyer-level platform review and from troubleshooting one wrong answer. It is the engineering system that makes both more credible.