Text-to-SQL Benchmark & Retrieval Tuning
The pilot was writing runnable SQL that returned the wrong rows. The fix was retrieval quality, not a bigger model.
An internal team wanted to query a production database in plain English. The pilot — GPT-4o generating SQL, with pgvector retrieving relevant schema context — demoed well but failed in the way that matters least visibly: it produced valid, runnable SQL that returned the wrong result. Nobody could say how often, because "it looks like SQL" is not a measure of correctness.
I built a benchmark of 150 gold-SQL pairs and scored the pilot on execution accuracy — does the generated query, when run, return the same result as the hand-written gold query? That set a hard baseline of 48%. The failures clustered on the model not knowing which column meant what, so I rewrote the schema documentation feeding the retrieval context — clearer table and column descriptions, join keys, and worked examples — and left the prompt and the model untouched.
On the same 150-pair benchmark, execution accuracy went from 48% to 71%. The lift came from what the model was given to read, not from a larger model or a cleverer prompt.
The pipeline is deliberately boring: a question is embedded, pgvector runs a similarity search over chunked schema documentation, the retrieved snippets are assembled into context alongside the question, GPT-4o generates SQL, and that SQL is executed against the database.
The design rule is where the interesting decision lives: the model owns only the translation from language to query. It is not asked to know the schema from memory. Everything it needs to be correct — table names, what each column means, which keys to join on — is supplied by retrieval, deterministically, at request time. Code owns retrieval, execution, and scoring; the LLM owns the sentence-to-SQL step and nothing else.
That boundary is what made the system debuggable. When a query came back wrong, the question split cleanly in two: was the right schema context retrieved (a retrieval problem) or did the model misuse context it was given (a generation problem)? Reading the failures through that lens is what located the fix in the documentation rather than in the prompt.
The benchmark is 150 question-and-gold-SQL pairs. Each pair is scored on execution accuracy: run both the generated query and the gold query, then compare their result sets. A query that errors, or that runs but returns a different result, is a miss. "Valid-looking SQL" earns nothing — only returning the right answer counts.
The controlled comparison is the point. Baseline and post-change runs used the same model, the same prompt, and the same 150 pairs; the only thing that changed between 48% and 71% was the schema documentation in the retrieval context. That isolation is what lets the 23-point gain be attributed to retrieval quality rather than to noise or a model swap.
| Execution accuracy | Benchmark | |
|---|---|---|
| Baseline pilot | 48% | 150 gold-SQL pairs |
| After retrieval-context rewrite | 71% | 150 gold-SQL pairs |
The failing queries were not random. They were the queries where the model had to guess the meaning of a column or the relationship between two tables — and guessed wrong. The model was competent at SQL; it was under-informed about this schema.
So the intervention went to the source of that information. I rewrote the schema docs that get chunked and retrieved: plain-language descriptions of what each table and column actually holds, explicit join keys, and a handful of worked question-to-SQL examples for the patterns the benchmark exercised. Better raw material for retrieval to surface — not a longer prompt, not a heavier model.
Credibility matters more than the headline number, so the honest read: 71% execution accuracy is a strong benchmark result, not a shippable-to-users one. Execution accuracy rewards returning the right rows on a fixed 150-pair set; it says nothing about queries that are correct but expensive, and the benchmark cannot cover every long-tail join and aggregation a real user would try.
- Add an abstention path — when retrieval confidence is low, the system should decline rather than confidently return a wrong answer.
- Grow the golden set from real user misses, so the benchmark tracks what people actually ask, not what I anticipated.
- Instrument latency and cost per query alongside accuracy, so quality is never improved blind to what it costs.
- Report accuracy per error type (bad join, wrong aggregation, wrong filter), not just the aggregate, so the next gain is aimed rather than guessed.
- Wrap execution in a safety layer — read-only access, row limits, and query timeouts — before any non-benchmark use.