Building reliable Text to SQL systems I

“Ask your database” is quickly becoming a non-negotiable feature in modern BI systems. From small teams to large enterprises, analysts expect to query data in natural language, without needing to understand SQL or underlying schemas, and get answers instantly.
The promise is simple. Making it reliable in production is not.

Table of contents

Modelling the problem

Assume we are dealing with the following data model and a user asks:

The solution can be modelled if we:

1. Capture the user’s natural language query
2. Inject database schema context (tables, fields, relationships, sometimes sample data)
3. Prompt the LLM to translate the query into SQL
4. Execute the generated SQL against the database

Looks like a simple task at first, until we start investigating the failure modes:

What can go wrong?

Answering the wrong question

For the given query, the following ambiguities may arise:

The Join Graph Is a Minefield

Joins are one of the easiest ways for valid SQL to produce the wrong answer. The problem goes beyond choosing the right tables. The join itself can change the grain of the data, duplicate rows, or connect entities through the wrong relationship. Some common failure modes are:

• fan-out joins
• chasm joins
• wrong grain
• multiple customer tables
• slowly-changing dimensions
• multiple plausible join paths

Performance Issues

So for the discussed query, the following ambiguities may arise :

-- Example query generated by LLM
SELECT
    p.category,
    SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi
    ON o.id = oi.order_id
JOIN products p
    ON oi.product_id = p.id
JOIN customers c
    ON o.customer_id = c.id
WHERE
    YEAR(o.order_date) = 2026
    AND QUARTER(o.order_date) = 2
    AND c.region = 'US'
    AND c.signup_date > '2025-01-01'
GROUP BY p.category
HAVING SUM(oi.quantity) >= 2
ORDER BY revenue DESC
LIMIT 3;

-- Non-sargable
WHERE YEAR(order_date) = 2025

-- Sargable
WHERE order_date >= '2025-01-01'
  AND order_date < '2026-01-01'

Security Risks

Generating SQL directly from natural language introduces a new attack surface. Unlike traditional applications where queries are carefully authored and reviewed, LLM-generated SQL must defend against ambiguous requests, malicious prompts, and accidental misuse.

-- User: "Show me all customer information for users in the US."

SELECT
    c.*,
    cc.card_number,
    cc.cvv,
    ssn.ssn,
    a.api_key
FROM customers c
LEFT JOIN credit_cards cc
    ON c.id = cc.customer_id
LEFT JOIN customer_ssn ssn
    ON c.id = ssn.customer_id
LEFT JOIN api_keys a
    ON c.id = a.customer_id
WHERE c.country = 'US';

Solutions and Trade-Offs

There’s a popular saying in engineering circles that goes something like:

Every abstraction leaks. Every optimization has a cost. Every architecture makes trade-offs.

Building a reliable text-to-SQL system is no different. The shortcomings we’ve discussed boil the problem down to three competing design goals:

1. Accuracy & Reliability - Does the system consistently generate SQL that is correct, performant, and safe for production use?
2. Development & Maintenance Overhead - How much engineering effort is required to build, maintain, and extend the system?
3. Flexibility - How well can the system answer a wide variety of questions, including ad hoc analytical queries?

Improving one often comes at the expense of another.

In the next two articles, I’ll explore two approaches to this problem in more detail:

1. Incremental Discovery with MCP — allowing the LLM to discover schema, relationships, business logic, and other context progressively rather than injecting everything into the prompt upfront.

2. Semantic Layer + DSL — moving business logic and query semantics outside the LLM, and having the model generate a constrained representation that can be deterministically compiled into SQL.