Text-to-SQL Limitations: Why Direct SQL Generation Falls Short

Text-to-SQL - translating natural language directly into SQL queries - is an intuitive approach to conversational analytics. But in practice, it faces fundamental limitations that prevent it from delivering reliably accurate results.

Understanding these limitations explains why semantic approaches (querying semantic layers instead of generating raw SQL) are necessary for trustworthy analytics.

Limitation 1: Schema Ambiguity

Database schemas don't encode meaning. Column names are hints at best:

Problem: "Show me revenue"

• Is amount the revenue column? Or total? Or value?
• Is revenue in orders table? invoices? revenue_recognition?
• What about foreign currency amounts?

The AI must guess, and guesses are often wrong.

Semantic solution: "Revenue" is explicitly defined with exact columns, tables, and calculation logic.

Limitation 2: Missing Business Rules

Business logic isn't in the schema:

Problem: "What was revenue last quarter?"

• Revenue recognition rules (when booked vs. when recognized)
• Exclusions (refunds, credits, internal transactions)
• Currency conversion timing
• Quarter boundary definitions (calendar? fiscal?)

The AI has no way to know these rules. It applies generic logic that may be wrong.

Semantic solution: Business rules are encoded in metric definitions and applied automatically.

Limitation 3: Join Path Ambiguity

Complex schemas have multiple valid join paths:

Problem: "Show customers by region"

• Join through billing_address or shipping_address?
• Join through account or directly to customer?
• What about customers with multiple addresses?

The AI picks a path - often based on frequency in training data, not correctness for the query.

Semantic solution: Relationships are defined with specific join paths for each context.

Limitation 4: Aggregation Complexity

Aggregation semantics are subtle:

Problem: "What's the average order value?"

• Average of order amounts, or average revenue per customer?
• Include $0 orders? Cancelled orders?
• Weighted by something?

"Average" can mean many things. The AI's choice may not match intent.

Semantic solution: Metrics define exact aggregation logic, including all edge cases.

Limitation 5: Temporal Complexity

Time handling is error-prone:

Problem: "Compare this quarter to last year"

• Same quarter last year, or trailing year?
• Time zones - whose "this quarter"?
• How to handle incomplete periods?
• Adjusted for business days?

Temporal logic requires explicit definition the schema doesn't provide.

Semantic solution: Time dimensions and comparisons are explicitly modeled.

Limitation 6: Accuracy Ceiling

Even with improvements, text-to-SQL accuracy plateaus:

• Academic benchmarks: 70-85%
• Real-world complex schemas: 50-70%
• Highly domain-specific queries: Even lower

An analytics system wrong 20-30% of the time isn't trustworthy for decisions.

Semantic solution: Semantic systems achieve 95%+ for supported queries by eliminating inference.

Limitation 7: Undetectable Errors

Text-to-SQL errors often look correct:

• Valid SQL syntax
• Reasonable-seeming numbers
• Proper formatting

Users may not realize results are wrong until decisions are made.

Semantic solution: Results trace to certified definitions, making verification possible.

The Fundamental Issue

Text-to-SQL tries to infer meaning from structure. But meaning is a business decision, not a data property:

• What counts as "active"?
• How is "revenue" recognized?
• When does a "quarter" start?

No amount of AI sophistication can infer decisions that haven't been made explicit. Semantic layers make those decisions explicit, which is why they work where text-to-SQL doesn't.

When Text-to-SQL Is Appropriate

Despite limitations, text-to-SQL works for:

• Exploratory queries by technical users
• Simple schemas with obvious naming
• Situations where validation is easy
• Low-stakes analysis

For anything beyond this - business decisions, reporting, AI-powered analytics - semantic approaches are necessary.