text-to-sql
verified6d6e9d65-b04c-4752-a580-61712925deb9
Build a production text-to-SQL system — schema grounding, few-shot examples, retrieval of relevant tables, SQL validation, and safe execution guardrails.
Metadata
Skill file
# Building Production Text-to-SQL
Use when you want non-technical users to ask questions about data in plain English
and get correct SQL (then results/answers) back. Text-to-SQL is deceptively hard:
naive single-prompt approaches top out around 70-85% accuracy and are dangerous on
write access. Production systems are a pipeline of grounding, generation, and
validation.
## The baseline: schema-aware prompting
Never send just the question to the LLM. Ground it with the schema:
- Table names, column names, types.
- Short business-meaning descriptions of each column.
- Primary/foreign keys and join paths.
- Synonyms, acronyms, and example values (this single step often outperforms
switching to a newer model — Lloyds reported ~80% -> ~86% exact-match by enriching
schema with synonyms and validated examples).
Start with few-shot: include 2-5 verified question->SQL pairs that mirror how your
users actually phrase queries. Tune this before adding RAG.
## Retrieval-augmented schema + examples
On a large schema you cannot stuff everything in the prompt. Retrieve the
**relevant** tables/columns and **similar past question-SQL pairs** at query time
using RAG over schema metadata and a corpus of validated examples. This keeps
prompts small and accurate. The quality of your verified example pairs matters far
more than the cleverness of the retrieval.
## Pipeline stages (the reliable design)
1. **Schema retrieval** — embed column/table descriptions; retrieve the subset
relevant to the question. Include join hints for the retrieved tables.
2. **SQL generation** — few-shot LLM call with grounded schema + retrieved examples.
Ask for just the SQL (or use structured output to force a single SQL field).
3. **Validation** — never execute raw LLM output directly:
- *Syntax*: parse with a SQL parser (`sqlglot`) or `EXPLAIN`/`PARSE` on the DB.
- *Schema*: check every table/column referenced actually exists.
- *Safety*: reject forbidden patterns (no `DROP`/`TRUNCATE`/`DELETE`, no unknown
functions, no writes on a read-only path).
4. **Self-correction** — if validation fails, feed the error back to the LLM for a
bounded retry (1-2 attempts), like LinkedIn's SQL Bot pulling different tables.
5. **Execute + explain** — run with a timeout and row limit; show the SQL and the
result so users can trust it.
## Safety guardrails (non-negotiable)
- **Read-only by default**: connect with a read-only role, `default_transaction_read_only=on`
in Postgres, and strip write/DDL.
- **Row limits and timeouts** to prevent runaway scans (`LIMIT`, statement timeout).
- **Least privilege**: the DB role should only see what a user is allowed to see
(row-level security if needed) — text-to-SQL is an access path, so enforce
authorization at the DB, not just in the prompt.
- **Query explainability**: log the generated SQL + question for audit and for
building your few-shot corpus.
## Pitfalls
- Prompting with the full schema when it's large — blows context and accuracy.
- No syntax/schema validation, then executing garbage or hallucinated columns.
- Writing to the DB through a text-to-SQL path without a read-only role.
- Fixed few-shot examples that don't match how users actually phrase things —
build examples from real logged questions.
- Ignoring ambiguous/multi-interpretation questions; better to ask a clarifying
question than guess a wrong join.
## Verify
- Build an eval set of representative user questions with hand-verified SQL answers
and a golden accuracy metric (exact SQL match + result-set match).
- Measure improvement from schema enrichment and from example retrieval
independently, so you know what moved the needle.
- Red-team with adversarial queries (billion-dollar, math-heavy, ambiguous joins)
and confirm the safety layer blocks all destructive statements.