A multi-stage text-to-SQL pipeline for faster, more reliable answers

The problem

An LLM can write SQL for a question if you give it the schema, which makes text-to-SQL look easy. In practice, simple pipelines break on phrasing they didn't expect and are unreliable in production.

We built this for an enterprise application that had to run on locally hosted models, Qwen3-235B-A22B-Instruct-2507 and Qwen3-235B-VL-A22B-Instruct, instead of frontier APIs. Latency had to be predictable, cost had to stay under control and the data could not leave the company, so the pipeline had to work with models smaller than the frontier ones.

First attempt: LlamaIndex SQLIndex

We started from the official LlamaIndex SQLIndex guide, which uses SQLTableRetrieverQueryEngine and NLSQLRetriever. The database is wrapped in a SQLDatabase object, and a SQLTableNodeMapping turns table schemas into indexable nodes.

It had three problems:

• Phrasing: if the user's wording didn't closely match table or column names, retrieval missed the relevant data.
• Schema reasoning: there was no step for exploring the schema. If the first pass missed a table, the query failed silently or returned incomplete results.
• Recovery: when an answer was wrong or empty, nothing corrected it.

It worked for questions phrased close to the schema and broke down on natural ones.

Source: LlamaIndex SQLIndex guide

Second attempt: the agent workflow

Next we moved to LlamaIndex's advanced text-to-SQL workflow, which adds agent reasoning, tool use and iterative schema exploration. It retrieves only the relevant parts of the schema and some sample data before generating SQL, using two additions:

• Table summaries: an LLM describes each table from its first few rows, instead of indexing table names alone.
• Row retrieval: the rows of each table are embedded in a VectorStoreIndex, so the rows most likely to matter can be retrieved at query time.

Retrieved rows work as examples in the prompt. If a user asks about a customer by name, the matching row goes into the context, which makes a correct WHERE clause much more likely.

With GPT-5, Sonnet 4.5 or Opus 4.1 the answers were correct, but a request often took several minutes. Qwen3-235B-A22B-Instruct-2507 and Qwen3-235B-VL-A22B-Instruct couldn't reason through it reliably or ran out of context. The workflow was accurate, but too slow, and it didn't run on the models we needed.

Source: LlamaIndex advanced text-to-SQL

Our design: two stages

We split the problem into two tasks and gave each its own stage:

• Understanding the database structure
• Answering the question with SQL

Stage 1: schema analysis

An LLM reads the question and picks up to seven relevant tables. For each one we fetch the full schema, the row count and one example row, so the model sees both the structure and real data. Hundreds of tables become a handful. The model also writes a short note on which tables and columns it expects to use.

Example prompt: "What are my top 5 customers by revenue?"

Stage 2 starts with only that context.

Stage 2: an SQL agent with tools

The agent gets the reduced schema and two tools:

• sql_execute runs validated, read-only SELECT queries and returns the results. Joins, filters and aggregations happen in the database, which is fast and exact. DROP, DELETE, UPDATE and other destructive keywords are rejected.
• sql_lookup_table_schema returns the full schema and an example row for any table, so the agent can look at tables Stage 1 didn't pick.

Starting from the question and the Stage 1 notes, the agent writes and runs up to 20 queries. It usually starts with checks like SELECT COUNT(*), tests its join assumptions, and adjusts based on what comes back.

When a query returns nothing or something unexpected, the agent changes its approach. The query results are its feedback.

What the design changes

• Fewer hallucinations: the context holds only real tables, and every query runs against the database, so the agent can't invent tables or columns.
• Faster answers: the heavy work runs in the database engine, and answers come back in seconds instead of minutes.
• Smaller models: the LLM no longer has to hold a large schema in context or simulate joins, so the pipeline works with locally hosted Qwen3-235B-A22B-Instruct-2507 and Qwen3-235B-VL-A22B-Instruct.
• Safety: only read-only queries run, and every statement is checked against a keyword blacklist.

Trade-offs

The two-stage design has more moving parts. For small databases with simple schemas, or when GPT-5 or Claude 4.5 are available over an API, LlamaIndex's standard text-to-SQL pipelines are simpler and usually enough.