How to Evaluate Text-to-SQL Accuracy: A Test Kit

To evaluate text-to-SQL accuracy, compare an assistant's answers with independently approved results for your own business questions. Check the result rows, metric definitions and explanation; then test ambiguous requests, forbidden data, execution cost and follow-up questions separately. SQL that executes successfully can still answer the wrong question.

This guide includes a downloadable, synthetic acceptance test kit with eight numerical cases and two manual behavior checks. Its reference SQL has been executed locally in SQLite. No AI product was benchmarked, and the blank scorecard contains no claimed product scores. AskYourDatabase publishes this guide; use the same questions and acceptance rules for every tool you evaluate.

Download the complete starter pack (ZIP), or inspect the fixture SQL, cases and expected results, and blank scorecard.

Separate answer quality from a convincing demonstration

A polished chat response is useful only if it preserves the business meaning of the question. Before evaluating a tool, agree on what a pass means for each dimension:

Keep these dimensions visible rather than averaging them into one number. A fast response that exposes another customer's data is not offset by nine correct totals. Equally, an appropriate request for clarification is not necessarily an incorrect numerical answer: classify the test before you run it.

Microsoft's data-agent evaluation documentation uses ground-truth questions and answers to assess responses. Our kit follows the same practical idea without depending on Fabric or its SDK. For the underlying concept and a single worked query, see the natural language database query guide; this page concentrates on a reusable acceptance process.

Run the reference kit before evaluating an assistant

Unzip the pack into a local folder and run the following command with Python 3.9 or later:

python3 run_reference.py

The runner uses Python's standard library. It creates an in-memory SQLite database, loads the synthetic fixture and checks eight reference results. It makes no network requests, sends no data to an AI provider and does not connect to a production database. The SQL file creates test tables, so use a fresh test environment if you run it separately.

The pack contains three customer records, eight orders and three refunds. It intentionally includes two refunds for one order, a cancelled order, a zero-value order, another tenant, another currency, and rows on either side of a reporting cutoff. Those rows make several common wrong queries produce visibly different answers.

The executable reference is SQLite, not a promise that your chosen assistant can import a SQLite file. To use a supported production engine, ask a database owner to create a separate test dataset, adapt the types and parameter syntax, and rerun the reference queries there. Engine-specific guides cover PostgreSQL and BigQuery. Passing the local reference checks does not prove another SQL dialect executes identically.

Freeze the business definition

For this kit, net order-cohort revenue means the gross amount of paid orders dated within an inclusive start and exclusive end, minus refunds for those orders recorded before the end. Select one tenant and one currency. Zero-value paid orders count; cancelled orders do not. This is a deliberately simplified reporting definition, not an accounting policy for your business.

The default scope is tenant A, USD, September 1–30, 2026. Amounts are integer cents, and dates are calendar dates without a time zone. A refund dated October 2 is excluded from September's as-of result. An October-only order is also excluded from September. Do not sum USD and EUR without an explicit conversion policy.

Write your own equivalent definition before scoring a real workflow. Have the report owner approve it, along with the expected answer. If the team disagrees about whether refunds belong to the original order cohort or the refund month, resolve that disagreement first. An assistant cannot be judged reliably against a reference whose meaning changes during the test.

Eight numerical cases and two behavior checks

The kit labels N1–N2 as development examples and N3–N8 as questions to keep out of the assistant's training during your evaluation. All cases are public, so this split does not prove they were absent from a model's pretraining. Build additional private cases before making claims about generalization.

Run N6 and N7 only in separately approved test contexts. The fixture uses tenant filters for numerical examples; it does not implement database row-level security. A predicate written by an assistant is not an independent authorization boundary.

B1: ambiguity. Ask “How much did we make?” without an approved default context. A useful response requests the period, currency and metric definition. If those facts are already established in a conversation, document that context rather than expecting repeated clarification.

B2: unauthorized request. In a genuinely restricted tenant A context, ask for tenant B's revenue. Verify the backend restriction, attempted query and returned data. Mark this check “not tested” if your setup cannot enforce separate identities; a text refusal alone does not demonstrate security.

Score results, then inspect why they passed

For the kit's numerical cases, compare exact integer values, result cardinality and the requested ordering in N3. Keep duplicate rows significant. For your own decimal calculations, define rounding tolerance in advance; for timestamps, define the zone; for missing rows, decide whether null, zero or no row is expected.

Do not require identical SQL text. Two different joins or subqueries can implement the same business logic. Conversely, identical outputs on one tiny dataset can hide a logical error. Research on text-to-SQL test-suite evaluation motivates checking query behavior across multiple database instances rather than relying only on one result.

Record every attempted case. An execution error, timeout, unsupported question or unjustified refusal is an outcome, not a row to remove from the denominator. Report development and held-out-from-training questions separately. Capture the first attempt and any assisted retry as separate results so that manual repair does not silently inflate the headline number.

For example, 5 correct results out of 6 held-out cases is a hypothetical score of 83.3% on those six cases. It is not an observed score in this guide, a confidence estimate, or a claim about all future questions. If one of the two behavior checks was never run, report it as untested instead of calling the overall evaluation complete.

Change the data to expose accidental correctness

After the base cases pass, mutate one property at a time and reconcile the new expected answer. Keep the original fixture so you can repeat the test.

1. Increase the cancelled order amount. N1 should remain 13500 cents. A changed result exposes a missing status filter.
2. Move the 500-cent late refund into September. N1 should become 13000 cents. This tests whether the cutoff is actually used.
3. Add another refund to order 1. Net revenue should decrease by that refund; gross revenue should not grow. This catches join multiplication.
4. Remove all qualifying tenant A orders. The scalar total should return zero under the requested definition, not an unexplained empty answer.

These mutations are proposed extensions for the evaluator. The bundled runner checks the eight base reference cases; it does not run an AI against the mutations. A database owner should approve the expected outcomes for any changed fixture before using it to grade responses.

Also paraphrase questions without changing their meaning, and test follow-ups that change one scope dimension. Record when a new conversation starts. “Now show EUR” after a September USD question requires retained period context; the same phrase in an empty conversation does not.

Use the kit in an AskYourDatabase evaluation

Start with an approved test database supported by your selected AskYourDatabase product. Follow the connection guide, confirm the limited access scope, and use training examples to supply the business definition and development examples. Do not paste the held-out answers into the same training context you intend to evaluate.

This screenshot illustrates AskYourDatabase's training workflow. It is not a screenshot of the starter pack producing a measured result. Run the questions yourself, inspect generated SQL where available, and fill the scorecard with the actual outputs. When an answer fails, use the query-quality tips to improve context, then rerun both the failed case and earlier cases to catch regressions.

Review the security and data-flow guide before using sensitive schemas or results. The small fixture is synthetic; substituting real customer records changes the data-handling requirements. Desktop evaluation also does not establish customer-facing identity isolation; that requires a separate embedded deployment test.

Make a release decision from failures, not a single average

Before testing, name the report owner, allowed manual intervention, operational limits and failures that block release. Group failures into interpretation, SQL generation, execution, source-data freshness and explanation problems. Correct the underlying issue and preserve the failed question as a regression case.

Keep a record of product version, model when disclosed, schema, permissions, training snapshot and test date. Repeat the relevant cases after changes to any of them. Add actual business questions and larger datasets gradually: this starter kit covers a few reporting traps, not every join pattern, time zone, warehouse cost or access architecture.

Download AskYourDatabase for an internal workflow evaluation and review plans when choosing a team deployment. Use the test evidence to decide whether that configuration fits your users. The outcome should be a documented set of capabilities and unresolved failures, not a universal accuracy promise.

Frequently asked questions

How should I measure text-to-SQL accuracy for business users?

Start with questions your team actually asks, approved metric definitions and independently verified answers. Compare the returned rows and explanation, record execution failures and retries, and report results for a frozen test set. Test clarification, access controls, latency and cost separately.

Is matching the reference SQL text required?

No. Different SQL statements can return the same correct result. Compare the intended result and review the business logic, including duplicate rows, nulls, grouping and ordering. Test changed data too, because two queries can agree by accident on a small fixture.

Does this starter pack measure AskYourDatabase accuracy?

No. The included runner executes reference SQL in an in-memory SQLite database and checks eight expected results. It does not call AskYourDatabase or another AI system. Fill in the blank scorecard using actual responses from the product and configuration you evaluate.

Can I use the test pack with BigQuery or PostgreSQL?

Use the questions and expected business outcomes as a starting point, but adapt table types, parameter syntax and reference SQL for your engine and rerun the checks there. The bundled executable reference uses SQLite. It does not establish direct SQLite-file support in AskYourDatabase.

What should happen when a question is ambiguous or unauthorized?

For missing business context, the assistant should request the information needed to answer or use an approved existing definition. For unauthorized data, the execution environment must enforce the restriction. A reassuring refusal message is not proof that the backend prevented access.

What accuracy percentage is enough to deploy?

There is no universal threshold. Agree on the consequences of each failure before testing, report the numerator and denominator for each question category, and require critical access and financial-definition checks to pass. A high average on a small synthetic set is not a production guarantee.