If your Text-to-SQL benchmark only tests clean questions against clean schemas, you are measuring the demo—not the production system.

Text-to-SQL has improved quickly.

Give a modern system a well-defined question such as:

What was revenue last quarter?

along with a clean schema and obvious relationships, and generating executable SQL is no longer the most interesting test.

Enterprise production environments look different.

They contain:

Ambiguous business language
Business terms that do not match column names
Several plausible metrics
Missing foreign keys
Multiple join paths
Legacy tables
Changing schemas

More importantly, some of the worst errors do not cause SQL failures.

The query executes.

The database returns rows.

The answer looks reasonable.

But the business logic is wrong.

That is why I would benchmark a production Text-to-SQL system around failure paths.

---

Happy-Path Accuracy Is a Weak Signal

A typical benchmark looks like:

Question
   ↓
Generated SQL
   ↓
Execution
   ↓
Expected Result

This is useful, but incomplete.

It mostly answers:

Can the system generate SQL when enough information is available?

Production requires another question:

What does the system do when the information is incomplete, ambiguous, or misleading?

A better evaluation should deliberately create those situations.

---

Test 1: Ambiguous Business Intent

Ask:

Show me our best customers.

Do not define best.

Possible interpretations include:

Revenue
Profit
Growth
Retention
Lifetime Value

All are reasonable.

That is what makes this a useful test.

Expected behavior

If the enterprise already has a governed definition of Best Customer, use it.

Otherwise, the system should recognize the ambiguity and clarify:

Should I rank customers by revenue, profit, growth, or retention?

Failure behavior

"best"
   ↓
LLM guesses Revenue
   ↓
SQL
   ↓
Confident answer

The SQL may be perfect.

The intent resolution is not.

What to measure

Ambiguity Detection
Clarification Accuracy
Candidate Quality

The key question:

Does the system know when not to guess?

---

Test 2: Business Terms That Do Not Match the Schema

Users do not speak SQL schemas.

They say:

Product Code
Active Customer
Sales Region
Recognized Revenue
Customer Tier

The database may contain:

material_id
cust_status_cd
sales_area_id
recognized_amt
cust_level_cd

Create a test question:

Show revenue by product code.

But ensure there is no product_code field.

Instead, include:

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

Then define:

business_term:
  name: Product Code

governed_mapping:
  table: product_master
  field: material_id

What this tests

The system must distinguish:

Closest Column Name

from:

Correct Business Mapping

A semantic similarity score is evidence of relevance.

It is not proof of business meaning.

What to measure

Semantic Mapping Accuracy

Did the system resolve the business concept to the governed physical field?

---

Test 3: Multiple Plausible Metrics

Build a schema containing:

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

Ask:

What was revenue last quarter?

All four fields are financially relevant.

Only one represents the governed Revenue metric.

Define:

metric:
  name: Revenue

source:
  table: finance_revenue
  field: recognized_amount

time_field:
  finance_revenue.recognition_date

Expected resolution

Revenue
   ↓
Recognized Revenue
   ↓
finance_revenue.recognized_amount

What to measure

Metric Resolution Accuracy
Authoritative Source Selection
Time-Field Selection

Do not only compare final numbers.

Inspect why the system chose the field.

A correct answer produced from the wrong metric definition is still a failure.

---

Test 4: Missing or Incomplete Relationships

Many enterprise databases do not have perfect foreign keys.

Create:

customer
account
sales_order
finance_revenue

The correct path is:

Customer
   ↓
Account
   ↓
Order
   ↓
Revenue

Then remove some explicit foreign-key constraints.

The system now needs relationship context beyond the database DDL.

Potential evidence may include:

Existing constraints
Column names
Compatible data types
Value overlap
Inclusion
Uniqueness
Validated metadata

For candidate columns A and B, one useful signal is:

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

A high inclusion ratio combined with appropriate uniqueness can provide evidence for a relationship.

What to measure

Relationship Path Accuracy

Specifically:

Correct tables selected?
Correct path selected?
Unsupported joins avoided?

If a Text-to-SQL system requires perfect foreign keys, test it on an imperfect database before buying it.

---

Test 5: SQL That Executes but Is Business-Wrong

This is the most important failure-path test.

Suppose both paths exist:

Customer → Order

and:

Customer → Account → Order

For consolidated accounts, only the second path preserves the correct business grain.

A generated query using:

JOIN sales_order o
  ON c.customer_id = o.customer_id

may execute successfully.

The database says:

SUCCESS

A syntax validator says:

VALID

But the query may duplicate transactions.

What this tests

Can the system distinguish:

Executable SQL

from:

Business-Valid SQL

What to measure

Relationship Validity
Aggregation-Grain Accuracy
Metric Consistency
Business Answer Accuracy

This is where simple execution benchmarks become insufficient.

---

Test 6: Questions the System Should Not Answer Yet

Ask:

Show our best-performing products recently.

Do not define:

best-performing
recently

Possible metrics:

Revenue
Profit
Units Sold
Growth

Possible periods:

7 Days
30 Days
Current Month
Current Quarter

If the enterprise has no governed defaults, the correct output should not be SQL.

It should be something like:

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

What this tests

A production system should know when it does not have enough information.

What to measure

Safe Failure Accuracy

Does the system:

Clarify?
Expose assumptions?
Refuse premature execution?

Or does it simply guess?

A system that answers every question is not necessarily more capable.

It may simply be less cautious.

---

Test 7: Change the Environment

Most benchmarks are static.

Production is not.

After the initial test succeeds, change the environment:

Add a table
Rename a field
Add a metric
Deprecate a metric
Add a relationship
Change a business mapping

Example:

Before:

Product Code
→ product_master.material_id

Later:

Product Code
→ product_dim.product_code

Then rerun the benchmark.

Measure:

What broke?
What updated automatically?
What required human intervention?
How long did recovery take?

What this tests

Semantic Maintenance Cost

This matters because enterprise environments evolve continuously.

A system can achieve impressive initial accuracy while requiring large amounts of manual work to maintain it.

---

Don't Use One Accuracy Number

After these tests, I would not want a benchmark report that says:

Text-to-SQL Accuracy: 94%

I would want something closer to:

Evaluation Dimension	Example Score
Semantic Mapping Accuracy	95%
Metric Resolution Accuracy	96%
Relationship Path Accuracy	91%
Clarification Accuracy	93%
SQL Execution Accuracy	98%
Business Answer Accuracy	90%
Safe Failure Accuracy	95%
Maintenance Effort	3.4 min/change

These metrics reveal very different failure modes.

---

Capture Intermediate Decisions

A useful evaluation harness should capture more than the final SQL.

For every question, record something like:

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

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

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

  "relationship_path": [
    "customer -> account",
    "account -> sales_order",
    "sales_order -> finance_revenue"
  ],

  "action": "execute",

  "generated_sql": "..."
}

Now when a test fails, you can identify where:

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

Without intermediate artifacts, everything becomes:

Wrong Answer

and debugging is much harder.

---

Add Adversarial Pairs

A useful benchmark should contain questions that differ by only one business concept.

For example:

Question A

What was revenue last quarter?

Expected:

recognized_revenue

Question B

What was invoiced amount last quarter?

Expected:

invoice_amount

Or:

Question A

Sales by customer region.

Expected:

customer_region

Question B

Sales by billing region.

Expected:

billing_region

These tests reveal whether the system actually resolves semantics or repeatedly falls back to the most common mapping.

---

Add Negative Cases

Some benchmark questions should intentionally have no valid answer.

For example:

Show profitability by customer happiness score.

If no governed happiness_score exists, the expected behavior might be:

UNRESOLVED

not fabricated SQL.

Negative tests measure whether the system understands the boundary of its knowledge.

That matters in production.

---

Use Messy Schemas

Avoid evaluating only:

customers
orders
products

Add realistic enterprise naming:

t_cust_m
cust_master_old
acct_rel
f_ord_h
ord_detail_v2
fin_rev_rec
inv_hdr
inv_line

Then introduce:

Deprecated tables
Duplicate concepts
Missing descriptions
Cross-system IDs
Incomplete foreign keys
Similar amount fields

The benchmark should resemble the environment where the product will actually run.

---

Measure Setup Cost Too

Suppose System A achieves:

96% business accuracy

but requires:

20 hours of manual semantic setup

System B achieves:

93% business accuracy

with:

4 hours of setup

Which system is better?

There is no universal answer.

But the setup cost belongs in the evaluation.

Track:

Manual descriptions
Metric definitions
Relationship configuration
Example SQL
Prompt tuning
Ongoing semantic maintenance

A production benchmark should measure both:

Accuracy

and:

Cost to achieve and maintain that accuracy

---

A Better Production-Value Model

A useful conceptual model is:

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

It is not a literal universal formula.

It is a reminder that enterprise value depends on more than benchmark accuracy.

Accuracy

Is the business answer correct?

Trust

Can the system explain and validate how it reached the answer?

Coverage

Can it handle messy schemas, multi-table relationships, and real business language?

Cost

How much work is required to deploy and maintain it?

---

A 100-Case POC

If I were building a serious POC, I might use:

10 Clear Questions
10 Ambiguous Questions
10 Business-Term Mapping Cases
10 Competing-Metric Cases
10 Missing-Relationship Cases
10 Multiple-Join-Path Cases
10 Executable-but-Wrong Traps
10 Clarification Cases
10 Unanswerable Cases
10 Environment-Change Regression Cases

Total:

100 cases

The objective is not to make the benchmark artificially difficult.

It is to approximate the failure modes the system will encounter after the demo.

---

The Key Distinction

A demo asks:

Can the system answer this question?

A production benchmark asks:

Can the system determine whether this question can be answered safely, with which business definition, using which data, through which relationships, and at what maintenance cost?

That is a much stronger test.

---

Final Thoughts

Modern LLMs are increasingly good at generating SQL when the problem is well specified.

So the most interesting enterprise benchmark is no longer:

Can the model write SQL?

It is:

Can the system resolve business meaning?
Can it select authoritative metrics?
Can it navigate imperfect relationships?
Can it reject plausible-but-wrong paths?
Can it recognize insufficient intent?
Can it adapt as the data environment changes?

Those are system-level capabilities.

If you want to know whether a Text-to-SQL product is ready for production, deliberately make the benchmark uncomfortable.

Give it ambiguity.

Give it messy metadata.

Remove a foreign key.

Add three revenue-like fields.

Create two executable join paths.

Ask something it should not answer.

Then change the schema and run the tests again.

Don't benchmark the happy path. Benchmark the failure path.