# 7 Failure-Path Tests Every Enterprise Text-to-SQL System Should Pass

![](https://cdn.hashnode.com/uploads/covers/6a21675e9e7f258f5d0b25c7/addaa79f-d87b-4547-a37b-9d851dc485a5.jpg align="center")

**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:

```text
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:

```text
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:

```text
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

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

The SQL may be perfect.

The intent resolution is not.

### What to measure

```text
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:

```text
Product Code
Active Customer
Sales Region
Recognized Revenue
Customer Tier
```

The database may contain:

```text
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:

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

Then define:

```yaml
business_term:
  name: Product Code

governed_mapping:
  table: product_master
  field: material_id
```

### What this tests

The system must distinguish:

```text
Closest Column Name
```

from:

```text
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:

```text
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:

```yaml
metric:
  name: Revenue

source:
  table: finance_revenue
  field: recognized_amount

time_field:
  finance_revenue.recognition_date
```

### Expected resolution

```text
Revenue
   ↓
Recognized Revenue
   ↓
finance_revenue.recognized_amount
```

### What to measure

```text
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:

```text
customer
account
sales_order
finance_revenue
```

The correct path is:

```text
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:

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

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

```text
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:

```text
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:

```text
Customer → Order
```

and:

```text
Customer → Account → Order
```

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

A generated query using:

```sql
JOIN sales_order o
  ON c.customer_id = o.customer_id
```

may execute successfully.

The database says:

```text
SUCCESS
```

A syntax validator says:

```text
VALID
```

But the query may duplicate transactions.

### What this tests

Can the system distinguish:

```text
Executable SQL
```

from:

```text
Business-Valid SQL
```

### What to measure

```text
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:

```text
best-performing
recently
```

Possible metrics:

```text
Revenue
Profit
Units Sold
Growth
```

Possible periods:

```text
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:

```json
{
  "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:

```text
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:

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

Example:

```text
Before:

Product Code
→ product_master.material_id
```

Later:

```text
Product Code
→ product_dim.product_code
```

Then rerun the benchmark.

Measure:

```text
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
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:

```json
{
  "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:

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

Without intermediate artifacts, everything becomes:

```text
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:

```text
recognized_revenue
```

### Question B

> What was invoiced amount last quarter?

Expected:

```text
invoice_amount
```

Or:

### Question A

> Sales by customer region.

Expected:

```text
customer_region
```

### Question B

> Sales by billing region.

Expected:

```text
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:

```text
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:

```text
customers
orders
products
```

Add realistic enterprise naming:

```text
t_cust_m
cust_master_old
acct_rel
f_ord_h
ord_detail_v2
fin_rev_rec
inv_hdr
inv_line
```

Then introduce:

```text
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:

```text
96% business accuracy
```

but requires:

```text
20 hours of manual semantic setup
```

System B achieves:

```text
93% business accuracy
```

with:

```text
4 hours of setup
```

Which system is better?

There is no universal answer.

But the setup cost belongs in the evaluation.

Track:

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

A production benchmark should measure both:

```text
Accuracy
```

and:

```text
Cost to achieve and maintain that accuracy
```

* * *

# A Better Production-Value Model

A useful conceptual model is:

```text
                  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:

```text
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:

```text
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:

```text
Can the model write SQL?
```

It is:

```text
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.**