How I Would Benchmark a Text-to-SQL System for Production

A clean question against a clean schema is a demo. A production benchmark should deliberately test ambiguity, missing relationships, competing metrics, and SQL that executes successfully but answers the wrong business question.

Most Text-to-SQL evaluations start with questions like:

What was revenue last quarter?
Show sales by region.
List the top 10 customers.

These tests are useful for checking whether the basic pipeline works.

They are not enough to tell you whether the system is ready for enterprise production.

In production, the difficult cases are rarely caused by SQL syntax alone. They come from business language, incomplete metadata, ambiguous metrics, undocumented relationships, aggregation grain, and assumptions that look reasonable but are wrong.

So if I were benchmarking a Text-to-SQL system, I would build the benchmark around failure paths, not just happy paths.

---

## Why Execution Accuracy Is Not Enough

A typical evaluation might measure:

Question
   ↓
Generated SQL
   ↓
Expected SQL

or:

Generated SQL
   ↓
Execute
   ↓
Compare Result

Execution-based evaluation is better than exact SQL matching because multiple SQL statements can produce the same correct answer.

But enterprise reliability requires additional questions:

Did the system understand the business term correctly?

Did it choose the authoritative metric?

Did it use the right relationship path?

Did it preserve the correct aggregation grain?

Did it recognize ambiguity?

Did it know when not to query?

A query can execute successfully and still fail every one of these tests.

---

# Build a Failure-Path Benchmark

I would create at least seven test categories.

A. Ambiguous Intent
B. Business Term Mapping
C. Competing Metrics
D. Missing Relationships
E. Executable-but-Wrong SQL
F. Insufficient Intent
G. Environment Change

Each category tests a different part of the system.

---

## Test A: Ambiguous Intent

Question:

Show me our best customers.

Possible interpretations:

Revenue
Profit
Growth
Retention
Lifetime Value

The benchmark should not define best in the question.

The correct behavior depends on enterprise context.

If a governed definition exists:

term: best_customer
metric: customer_lifetime_value
status: active

the system can resolve it.

If multiple valid definitions remain, the expected behavior should be:

CLARIFY

not:

GENERATE_SQL

### Example benchmark case

{
  "id": "ambiguity_001",
  "question": "Show me our best customers.",
  "valid_metrics": [
    "revenue",
    "profit",
    "growth",
    "retention"
  ],
  "governed_default": null,
  "expected_action": "clarify"
}

### What to score

Ambiguity detected?
Correct candidates identified?
Clarification triggered?
Clarification options useful?

This gives you a Clarification Accuracy metric rather than only SQL accuracy.

---

## Test B: Business Term Mapping

Question:

What was revenue by product code this quarter?

Now make sure the schema does not contain a field called:

product_code

Instead:

product_master.material_id
sales_detail.sku_no
inventory.item_code
product_dim.prod_master_id

The benchmark should contain a governed semantic mapping:

business_term:
  name: Product Code

maps_to:
  table: product_master
  field: material_id

### Failure mode

A schema-similarity system may select:

inventory.item_code

because the name looks closest.

But the benchmark expects:

product_master.material_id

because that is the governed enterprise definition.

### What to score

Business term resolved correctly?
Correct physical field selected?
Governed mapping preferred over lexical similarity?

Call this:

# **Semantic Mapping Accuracy**

---

## Test C: Competing Metrics

Create several plausible financial fields:

sales_order.total_amount
invoice.invoice_amount
finance_revenue.recognized_amount
payment.received_amount

Question:

What was revenue last quarter?

All four fields are relevant to money.

Only one represents the governed Revenue metric.

Benchmark definition:

metric:
  name: Revenue

source:
  table: finance_revenue
  field: recognized_amount

time_field:
  finance_revenue.recognition_date

### What to inspect

Do not only inspect the final number.

Inspect the query plan.

Expected:

Metric
→ Revenue

Field
→ finance_revenue.recognized_amount

Time
→ finance_revenue.recognition_date

### What to score

Metric resolution accuracy
Source selection accuracy
Time-field accuracy

This catches systems that accidentally return the right number from the wrong business definition.

---

## Test D: Missing Relationships

Production databases often lack perfect foreign keys.

Build:

customer
account
sales_order
finance_revenue

Expected relationship path:

Customer
   ↓
Account
   ↓
Order
   ↓
Revenue

Then deliberately remove some database constraints.

The benchmark should still contain enough data evidence for a relationship system to infer or retrieve valid relationships.

For example:

account.customer_id
sales_order.account_id
finance_revenue.order_id

### Useful relationship evidence

A relationship engine might consider:

Column names
Data types
Uniqueness
Value overlap
Inclusion
Known metadata
Validated relationships

One useful inclusion signal is:

Inclusion(A → B)
=
|distinct(A) ∩ distinct(B)|
---------------------------
|distinct(A)|

where A is a candidate foreign-key-like column and B is a candidate referenced column.

### What to score

Correct tables selected?
Correct relationship path found?
Unsupported direct joins avoided?

Call this:

# **Relationship Path Accuracy**

---

## Test E: Executable-but-Wrong SQL

This is the test I would care about most.

Suppose the schema allows:

Customer → Order

and:

Customer → Account → Order

Both can generate executable SQL.

But for consolidated customers, only:

Customer → Account → Order

preserves the correct business grain.

Create two candidate queries.

### Query A

SELECT
    c.customer_name,
    SUM(o.amount)
FROM customer c
JOIN sales_order o
    ON c.customer_id = o.customer_id
GROUP BY c.customer_name;

### Query B

SELECT
    c.customer_name,
    SUM(o.amount)
FROM customer c
JOIN account a
    ON c.customer_id = a.customer_id
JOIN sales_order o
    ON a.account_id = o.account_id
GROUP BY c.customer_name;

Both may execute.

Only Query B is business-valid for the benchmark scenario.

### What to score

SQL executable?
Business relationship valid?
Aggregation grain valid?
Result semantically correct?

This creates an important distinction:

Execution Accuracy
≠
Business Accuracy

---

## Test F: Insufficient Intent

Question:

Show our best-performing products recently.

The benchmark intentionally leaves unresolved:

best-performing
recently

Possible metric candidates:

Revenue
Profit
Units Sold
Growth

Possible time candidates:

7 Days
30 Days
Current Month
Current Quarter

If no governed defaults exist, the expected result is not SQL.

It is:

{
  "action": "clarify",
  "unresolved": [
    "metric",
    "time_range"
  ]
}

### What to score

Did the system detect missing intent?
Did it avoid premature SQL generation?
Did it ask the minimum useful clarification?

This measures:

# **Safe Failure Accuracy**

A production system should not be rewarded for answering every question.

---

## Test G: Environment Change

Static benchmarks miss one of the largest production costs: maintenance.

Start with a working environment.

Then introduce changes:

Add a table
Rename a field
Add a new metric
Deprecate an old metric
Add a new relationship
Change a semantic mapping

For example:

Before:
Product Code → product_master.material_id

After:
Product Code → product_dim.product_code

Then rerun the benchmark.

Measure:

How many cases break?

How much manual configuration is required?

How quickly does the system recover?

Which knowledge objects need updating?

This produces a metric that benchmark leaderboards rarely show:

# **Semantic Maintenance Cost**

---

# Score More Than SQL

A useful benchmark report could look like:

Metric	Score
Semantic Mapping Accuracy	94%
Metric Resolution Accuracy	96%
Relationship Path Accuracy	91%
Clarification Accuracy	93%
SQL Execution Accuracy	98%
Business Answer Accuracy	89%
Safe Failure Accuracy	95%
Maintenance Effort	3.2 min/change

The exact metrics depend on the product.

The important point is that:

SQL Accuracy

should be one row, not the entire benchmark.

---

# Use a Multi-Stage Evaluation Harness

Instead of treating Text-to-SQL as one black box:

Question
→ SQL

capture intermediate artifacts.

For example:

{
  "question": "Revenue by customer last quarter",

  "intent": {
    "metric": "recognized_revenue",
    "dimension": "customer",
    "time": "last_quarter"
  },

  "semantic_objects": [
    "metric.revenue",
    "dimension.customer"
  ],

  "tables": [
    "customer",
    "account",
    "sales_order",
    "finance_revenue"
  ],

  "relationship_path": [
    "customer.customer_id -> account.customer_id",
    "account.account_id -> sales_order.account_id",
    "sales_order.order_id -> finance_revenue.order_id"
  ],

  "generated_sql": "...",

  "action": "execute"
}

Now failures become diagnosable.

If the final answer is wrong, you can determine whether the failure came from:

Intent
Semantic Mapping
Table Selection
Relationship Resolution
Metric Selection
SQL Generation
Execution

That is much more useful than:

FAILED

---

# Build Adversarial Test Pairs

A strong benchmark should include pairs of questions that look similar but require different interpretations.

Example:

Q1

What was our revenue last quarter?

Expected metric:

recognized_revenue

Q2

What was our invoiced amount last quarter?

Expected metric:

invoice_amount

Another pair:

Q1

Sales by customer region.

Expected dimension:

customer_region

Q2

Sales by billing region.

Expected dimension:

billing_region

These pairs test whether the system is actually resolving semantics or simply reusing the most common mapping.

---

# Include Negative Tests

Traditional benchmarks mostly contain questions that should be answerable.

Production benchmarks should include questions that should not.

For example:

Question:
"Show profitability by happiness score."

No governed metric:
happiness_score

Expected:
Cannot resolve metric / clarification required

Or:

Question:
"Join employee salary with public customer data."

Expected:
Rejected by policy

The exact safety rules depend on the deployment.

The principle is:

A system should be tested on its ability to refuse invalid query plans, not only generate valid ones.

---

# Test With Messy Schemas

Do not benchmark only:

customer
orders
products

Use schemas that look more like enterprise reality:

t_cust_m
cust_master_old
acct_rel
f_ord_h
ord_detail_v2
fin_rev_rec
inv_hdr
inv_line

Add:

Deprecated tables
Duplicate concepts
Missing descriptions
Inconsistent naming
Cross-system IDs
Partial foreign keys

If the product claims to work with enterprise data, the benchmark should look like enterprise data.

---

# Measure Human Setup Cost

Before the first benchmark run, track setup effort.

For example:

Schema descriptions:       4 hours
Metric definitions:        3 hours
Relationship configuration: 6 hours
Example SQL:                5 hours
Prompt tuning:              2 hours

Total:

20 hours

Another system may achieve slightly lower raw accuracy but require:

4 hours

of setup.

That difference matters.

So benchmark:

Accuracy

and:

Cost to achieve that accuracy

together.

---

# A More Useful Production Score

I would think about production value approximately as:

                  Accuracy × Trust × Coverage
Value  ≈  ─────────────────────────────────────
                Setup + Maintenance Cost

Again, this is not a universal mathematical formula.

It forces the benchmark to consider what enterprises actually pay for.

A system that scores 97% on 50 carefully prepared questions may be less valuable than one scoring 93% across thousands of messy tables with far less manual configuration.

---

# What I Would Put in a POC

If I were designing a vendor POC, I would include:

10 clear questions
10 ambiguous questions
10 semantic-alias questions
10 competing-metric questions
10 missing-relationship questions
10 multi-path join questions
10 executable-but-wrong traps
10 questions that require clarification
10 unanswerable questions
10 post-schema-change regressions

That gives:

100 cases

with intentionally different failure modes.

Then evaluate both:

Can it answer correctly?

and:

Can it recognize when it should not answer yet?

---

# Final Thoughts

A production Text-to-SQL benchmark should not primarily test whether an LLM knows SQL.

Modern models are already good at SQL generation under clean conditions.

The harder questions are:

Did it understand the business term?

Did it select the right metric?

Did it find the right data?

Did it use the right relationship?

Did it preserve the right grain?

Did it detect ambiguity?

Did it fail safely?

How much human work was required?

Those are system-level questions.

So when evaluating a Text-to-SQL platform:

Don't benchmark the easiest route from question to SQL.

Deliberately break the assumptions.

Remove the foreign key.

Add three plausible revenue fields.

Use business terminology that does not match the schema.

Create two executable join paths where only one is correct.

Ask a question that should trigger clarification.

Then change the data model and run everything again.

That is where you start learning whether you have a good demo—or a production system.