Text to SQL with LLMs, Part 3: Retrieval, Golden Queries, and Business Context

Part 2 built the static context layer: curated schema, column descriptions, real values, grain rules, and reporting views. That foundation eliminates the mechanical failures. What it cannot do is teach the model how your organization asks and answers questions, because that knowledge is not in the schema. It is in the hundreds of queries your analysts have already written and the definitions everyone carries in their heads.

Retrieval-augmented generation (RAG) is how you get that knowledge into the prompt. Instead of a fixed context, the system retrieves the most relevant examples, definitions, and values for each incoming question and assembles the prompt dynamically. This is the architecture behind essentially every strong open source text to SQL project, and it is where accuracy on your own database starts pulling ahead of what any benchmark number would predict, because unlike a benchmark, your question distribution repeats.

Golden queries: the highest-value asset you already have

A golden query is a verified pair: a natural-language question and the SQL that correctly answers it, blessed by someone who knows the data. A library of these is worth more to your text to SQL system than any other artifact, for a simple reason: showing a model how questions like this one were correctly answered against this exact schema transfers join paths, filter conventions, and metric definitions all at once.

The research record backs this hard. DAIL-SQL reached 86.6 percent execution accuracy on Spider (state of the art at the time) largely on the strength of smarter example selection: choosing few-shot examples whose questions and candidate SQL skeletons resembled the incoming question, rather than using a fixed example set. Example choice was worth more than prompt phrasing. And the idea keeps winning at the frontier: Oracle's SOMA-SQL, which took the top spot on the enterprise Spider 2.0 Lite leaderboard in April 2026 at 72.02 percent, builds an offline log of question and SQL pairs and retrieves the most relevant ones as few-shot context for every incoming question. Same pattern, industrial scale.

You are not starting from zero. Golden queries are already scattered across your BI tool, your dbt models, saved analyst scripts, and the "useful queries" doc someone keeps in a drawer. Harvesting and verifying the top 30 to 50 is usually a focused week, and it pays back immediately.

Store them somewhere queryable, with embeddings for retrieval. PostgreSQL with pgvector is a pragmatic choice:

CREATE EXTENSION IF NOT EXISTS vector;

CREATE SCHEMA IF NOT EXISTS ai_context;

CREATE TABLE ai_context.golden_queries (
    id           BIGSERIAL PRIMARY KEY,
    question     TEXT NOT NULL,
    sql_text     TEXT NOT NULL,
    dialect      VARCHAR(30) NOT NULL DEFAULT 'tsql',
    tables_used  TEXT[],
    notes        TEXT,
    verified_by  TEXT NOT NULL,
    verified_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    embedding    vector(1536)
);

The verified_by column is not decoration. The entire value of this table is that every row has been checked by a human who knows the data. An unverified example teaches the model your mistakes with perfect fidelity.

Retrieval: assembling the prompt per question

At question time, embed the incoming question and pull the closest verified examples:

SELECT question, sql_text
FROM ai_context.golden_queries
WHERE dialect = 'tsql'
ORDER BY embedding <=> $1   -- cosine distance to the question embedding
LIMIT 5;

Those pairs go into the prompt alongside the Part 2 schema block:

Here are verified examples of correct queries against this database:

Q: What was revenue by month last year?
SQL:
SELECT DATEFROMPARTS(YEAR(ordered_at), MONTH(ordered_at), 1) AS month,
       SUM(order_total) AS revenue
FROM analytics.order_facts
WHERE order_status IN ('paid','shipped')
  AND ordered_at >= DATEFROMPARTS(YEAR(GETDATE()) - 1, 1, 1)
  AND ordered_at <  DATEFROMPARTS(YEAR(GETDATE()), 1, 1)
GROUP BY DATEFROMPARTS(YEAR(ordered_at), MONTH(ordered_at), 1);

[... 3 to 4 more retrieved examples ...]

Now answer: "How did revenue trend by month this year compared to last?"

Look at what those five examples silently taught: the paid and shipped revenue convention, half-open date ranges, month truncation in this dialect, and the habit of querying the reporting view instead of raw tables. No instructions required. Three to five well-chosen examples is the practical sweet spot; past that you add tokens faster than you add signal, and a wall of examples can drag the model into copying structure that does not fit the new question.

One retrieval subtlety worth stealing from the research: match on the shape of the question, not just its words. "Top customers by revenue" and "top SKUs by revenue" share almost no entities but share their entire SQL skeleton, and it is the skeleton you want the model to reuse. Embedding questions with specific entity names masked (a trick from the DAIL-SQL line of work) makes structurally similar examples rank higher.

The business glossary: retrieval for definitions

Part 1's failure mode number 4 was ambiguous terms: "active customer" means something different in every company, and sometimes in every department. The fix is a small glossary of business definitions, stored and retrieved exactly like golden queries:

CREATE TABLE ai_context.glossary (
    id          BIGSERIAL PRIMARY KEY,
    term        TEXT NOT NULL,
    definition  TEXT NOT NULL,
    sql_hint    TEXT,
    embedding   vector(1536)
);

INSERT INTO ai_context.glossary (term, definition, sql_hint) VALUES
('active customer',
 'A customer with at least one paid or shipped order in the trailing 90 days.',
 'EXISTS (SELECT 1 FROM analytics.order_facts f
          WHERE f.customer_id = c.customer_id
            AND f.order_status IN (''paid'',''shipped'')
            AND f.ordered_at >= DATEADD(day, -90, GETDATE()))');

When a question mentions "active customers," the definition and its SQL fragment land in the prompt, and the model stops improvising your KPIs. As a side effect, writing this glossary tends to surface that your departments never actually agreed on these definitions. Settling them is worth doing regardless of any AI project; the AI project just forces the conversation.

Value grounding: connecting words to stored data

Part 2 put static samples of low-cardinality values into the schema block. That covers statuses and state codes. It does not cover "show me orders from Lakeside," where the database says 'Lakeside HVAC Supply LLC'. The BIRD benchmark's central finding was precisely this: real databases have dirty, abbreviated, inconsistently formatted values, and systems that cannot ground question terms in actual stored values produce confident zero-row answers.

The fix is a lookup step before generation. When the question contains a probable entity, search for matching stored values and feed the hits into the prompt:

-- Ground a free-text company mention against real stored values
SELECT DISTINCT TOP 5 company_name
FROM analytics.order_facts
WHERE company_name LIKE '%' + @candidate + '%'
ORDER BY company_name;

For bigger estates, maintain a searchable index of distinct values per groundable column (full-text or trigram indexed) and query that instead of hitting fact tables. Either way, the prompt now says company_name = 'Lakeside HVAC Supply LLC' because the pipeline looked, rather than guessed.

Open source projects worth studying, and the flywheel

You can assemble the above from scratch, and for some stacks that is right. But study the open source projects first, because they encode years of accumulated lessons:

Vanna is the clearest reference implementation of this exact architecture: an open source Python RAG framework that you "train" on three things (DDL, documentation, and question-SQL pairs) and that retrieves from all three per question. It connects to Postgres, SQL Server, Snowflake, BigQuery, ClickHouse, and more, and swaps LLMs and vector stores freely. Even if you build your own, its three-part training taxonomy is the right mental model:

vn.train(ddl="CREATE TABLE analytics.order_facts (...)")

vn.train(documentation=(
    "An active customer has at least one paid or shipped order "
    "in the trailing 90 days."
))

vn.train(
    question="Top 10 customers by revenue last quarter",
    sql="SELECT TOP 10 company_name, SUM(order_total) AS revenue ..."
)

WrenAI approaches the same problem through an explicit semantic modeling layer, and DB-GPT offers a broader private-deployment framework with text to SQL among its capabilities. Different architectures, same lesson everywhere: retrieval over curated organizational knowledge is the engine.

Whatever you run, build the flywheel: when the assistant produces a correct answer to a new question, one click should send that pair into a review queue, and a human check should promote it into golden_queries. Sixty days of normal usage grows your example library exactly along the distribution of questions your team really asks, which is a data asset no vendor can sell you. Keep it honest with maintenance: re-embed and re-verify examples when schemas change, and retire examples touching renamed columns, ideally as a CI check rather than a memory.

Key takeaways

Retrieval turns your organization's existing knowledge into per-question context: golden queries transfer conventions, the glossary pins definitions, and value grounding connects language to what is actually stored. Each one closes a failure mode that no amount of schema documentation could reach, and the feedback flywheel means the system gets better along precisely the axis your team uses it.

What retrieval does not give you is a safety story or a real accuracy number. A retrieved example cannot stop a runaway query, and "it seems pretty accurate" is not a metric. Part 4 closes the loop with semantic layers, agentic self-correction, database guardrails, and evaluation.

Turn your team's knowledge into an asset

Field Craft Labs builds this full retrieval layer as a service: we harvest and verify your golden queries, formalize your business glossary, stand up the retrieval pipeline, and hand your team an assistant that answers in your company's own definitions. The result is that anyone, not just the SQL-fluent, can pull trustworthy numbers and assemble reports in minutes. Start the conversation.

Previous: Part 2: Schema Context Engineering. Next: Part 4: Production-Grade Systems.