A text-to-SQL system can generate valid SQL, execute successfully, and still answer the wrong question. It may join at the wrong grain, include cancelled records, double-count revenue, mix timezones, ignore tenant boundaries, or invent a definition for an ambiguous metric. The result can look credible precisely because the query ran.
Enterprise text-to-SQL evaluation must connect natural-language intent to data semantics and operating consequences. That requires representative questions, controlled schemas and datasets, execution evidence, counterexamples, access boundaries, performance checks, and human SQL or analytics review where correctness cannot be reduced to one result comparison.
Need SQL and data experts for model evaluation? Datrick supports text-to-SQL task design, reference queries, model-output review, business-semantic rubrics, calibration, adjudication, and managed technical evaluation delivery.
Define the intended decision and operating context
State whether the evaluation will compare models, approve a release, validate an analytics assistant, qualify contributors, or improve a text-to-SQL pipeline. Define the target users, database engines, schemas, question types, metric definitions, permissions, latency and cost limits, expected ambiguity behavior, and consequences of a wrong answer.
Public benchmarks provide useful external context. Spider 2.0 focuses on real-world text-to-SQL workflows derived from enterprise database use cases. A production evaluation still needs private tasks that reflect the organization's schemas, terminology, access policies, data distributions, and historical failures.
| Dimension | What to verify | Example failure |
|---|---|---|
| Intent interpretation | The generated query answers the user's actual question and follows defined business terms. | "Active customer" is interpreted differently from the approved metric definition. |
| Result correctness | Rows, aggregates, ordering, grouping, and limits match trusted reference evidence. | The query runs but returns lifetime revenue instead of the requested quarter. |
| Join semantics | Keys, direction, cardinality, grain, and fan-out are correct. | A many-to-many join silently duplicates invoices. |
| Edge cases | Nulls, duplicates, empty sets, boundary dates, late data, and timezone rules are handled. | Orders at midnight move into the wrong reporting period. |
| Authorization | The query uses permitted databases, tables, columns, rows, and operations. | A correct result is obtained through restricted customer data. |
| Performance | Scan size, predicates, joins, recursion, concurrency, and warehouse cost stay within limits. | A simple question triggers a full production-table scan. |
| Ambiguity | The system asks, states an assumption, or applies an approved default. | The agent silently invents the meaning of net revenue. |
Execution accuracy is necessary but not sufficient
Comparing the result of a generated query with a trusted query is a strong starting point. It verifies behavior rather than SQL string similarity and allows several syntactically different queries to be accepted. Tools such as the Text-to-SQL Evaluation Toolkit and the Ragas text-to-SQL evaluation guide demonstrate execution-oriented approaches.
One static dataset can still hide semantic differences. Two non-equivalent queries may return the same rows by coincidence because the data does not contain the case that separates them. Create counterexample data for join fan-out, duplicates, nulls, boundary dates, missing dimensions, and conflicting status values. Where appropriate, compare query structure, test multiple datasets, or use formal and property-based techniques in addition to execution checks.
Use technical human review for business semantics
A SQL expert should inspect cases where a reference result does not fully define correctness, multiple queries may be valid, or the approved answer depends on business policy. The reviewer should cite the question, schema, metric definition, query, result, and relevant data behavior rather than assign a general quality score.
| Review question | Required evidence | Escalate when |
|---|---|---|
| Does the query preserve the intended grain? | Join keys, source-table grain, grouping, and representative row traces. | The business owner has not defined the unit of analysis. |
| Are metric definitions applied correctly? | Approved KPI definition, filters, statuses, time window, and exclusions. | Two authorized definitions conflict. |
| Is the assumption defensible? | User question, available context, system policy, and stated assumption. | The answer should have required clarification. |
| Is the query safe to execute? | Read-only enforcement, permissions, query plan, scan estimate, and timeout. | Production impact or data access cannot be bounded. |
Build the evaluation set from real question families
Sample questions from analytics requests, support tickets, dashboard definitions, data incidents, onboarding sessions, query logs where authorized, and known failure cases. Remove sensitive content and preserve provenance. Cover routine questions, hard joins, vague business language, incomplete context, permission boundaries, and cases where the correct behavior is to ask a question or refuse.
Keep a protected holdout set for release decisions. If prompt authors, model developers, or evaluators repeatedly see every task and reference answer, the program can measure familiarity instead of generalization. Version schemas, data snapshots, definitions, graders, model settings, and environment dependencies so results remain explainable.
Control access and execution risk
Use isolated or replicated data where possible, read-only credentials, row and column restrictions, query timeouts, scan and cost limits, audit logs, and an explicit approval path for any production access. Mask or synthesize sensitive data without removing the edge cases the evaluation is meant to test. Record which reviewers can see schemas, data samples, query outputs, and business definitions.
Evaluation should distinguish a model-quality failure from an environment failure. Missing schema context, stale metadata, unavailable tables, incorrect permissions, and broken reference queries can make the model appear wrong when the task itself is not executable. Validate the task and environment before using the result to compare models or reviewers.
Calibrate SQL reviewers and adjudicate disagreement
Have qualified reviewers independently score the same sample. Classify disagreement into reviewer error, weak reference evidence, ambiguous business meaning, multiple valid SQL formulations, dataset blind spots, and environment instability. An authorized data or business owner must resolve definition conflicts; an evaluation vendor should not invent the client's metric policy.
Track acceptance, rework, disagreement, query-error category, ambiguity rate, security escalation, execution failure, and rubric changes. Aggregate accuracy can hide a critical weakness in one high-risk task family, so report by domain, schema, complexity, failure type, and decision threshold.
What a managed text-to-SQL evaluation pilot should deliver
- A bounded use case, named decision owner, target users, databases, permissions, and business definitions.
- A representative question set with normal, difficult, ambiguous, adversarial, and protected holdout cases.
- Controlled schemas and data with reference evidence, counterexamples, access controls, and reproducible resets.
- Execution, result, state, permission, and performance checks matched to each task family.
- A technical review rubric, calibration sample, disagreement taxonomy, and adjudication route.
- A pilot report with failure patterns, limitations, rubric changes, and an expand, revise, or stop recommendation.
Frequently asked questions
What is text-to-SQL evaluation?
Text-to-SQL evaluation measures whether a model or agent correctly interprets a natural-language question, generates authorized SQL, returns the intended result, handles business semantics and edge cases, and avoids unacceptable performance or security risk.
Is execution accuracy enough for text-to-SQL evaluation?
No. Execution accuracy is useful, but two different queries can return the same result on one test dataset by coincidence. Evaluation should also inspect query semantics, use counterexample data, test permissions and performance, and apply technical review where business meaning is ambiguous.
How do you build an enterprise text-to-SQL benchmark?
Start from representative user questions, schemas, metric definitions, permissions, and historical failures. Include normal, boundary, ambiguous, adversarial, empty-set, duplicate, null, timezone, and large-scan cases. Version the dataset and preserve protected holdout tasks for release decisions.
When does text-to-SQL evaluation need human SQL experts?
Human SQL and data experts are needed when correctness depends on business definitions, multiple queries may be valid, datasets do not expose a semantic error, operational risk matters, or the system must decide whether to ask a clarifying question rather than invent an assumption.
What does a managed text-to-SQL evaluation pilot include?
A managed pilot includes one bounded use case, controlled schemas and data, representative questions, reference evidence, execution and state checks, a technical review rubric, reviewer calibration, disagreement adjudication, error analysis, and a recommendation to expand, revise, or stop.
Start with one schema and one question family. Datrick can review the data model, questions, reference evidence, graders, SQL rubric, security controls, calibration method, and pilot findings before recurring delivery is proposed.