Text-to-SQL at Scale: Stop Feeding Your LLM the Whole Database

The first text-to-SQL demo you build always works. You have five tables — users, orders, products, subscriptions, events — you paste the whole schema into the prompt, and the LLM writes flawless queries. Ship it.

Then you point the same system at your real production database. Two hundred tables. Columns named flg_actv_ind and usr_ref_id_2. Three tables that all look like they hold "orders." Suddenly the model joins the wrong tables, invents a column that doesn't exist, or times out because the schema alone is 40,000 tokens before the user even asks a question.

This is the wall every team hits when moving text-to-SQL from demo to production. And here's the surprising part: study after study finds that generating SQL is not the hard part — picking the right tables is. Schema-linking errors (selecting the wrong tables or columns) account for roughly 20% of all text-to-SQL failures in major benchmarks. This post is about how to get that 20% back.

The core problem: schema linking

"Schema linking" is the step where the system figures out which tables and columns a question actually needs, before writing any SQL. Ask "How much revenue did we make from annual plans last month?" and the relevant subset might be just subscriptions, plans, and invoices — three tables out of two hundred.

Get this wrong and everything downstream fails. Miss a needed column and the query is incomplete. Include ten irrelevant tables and you drown the model in noise, so it joins orders to the wrong customers table. As one benchmark analysis put it, an incomplete or incorrect schema makes generating a correct query nearly impossible — the LLM can't recover from bad context.

So the naive approach — "just give the model everything" — fails for two reasons at once:

Problem	What happens
Token cost / limits	A 300-table schema can blow past the context window, or cost a fortune per query, before the user's question is even added.
Noise and distraction	Even when it fits, irrelevant tables confuse the model. More schema means more chances to link the wrong `status` column or pick the wrong `orders` table.

The fix is to stop treating the schema as a fixed prompt prefix and start treating it as something you retrieve on demand — the same idea behind RAG (retrieval-augmented generation).

Pattern 1: Retrieve the relevant schema with embeddings

Instead of dumping all tables into the prompt, index them and fetch only the ones that match the question.

The setup: for each table (and often each column), write a short natural-language description and embed it into a vector store. At query time, embed the user's question and pull back the top-k most similar schema elements. Those — and only those — go into the prompt.

Question: "revenue from annual plans last month"
        │
        ▼  embed + vector search over table descriptions
Top matches:
   subscriptions  (0.89)  — customer plan enrollments, billing cycle
   plans          (0.86)  — plan tiers, price, interval (monthly/annual)
   invoices       (0.81)  — issued charges, amount, paid_at
        │
        ▼  only these 3 tables go into the LLM prompt

The descriptions matter enormously. A raw column name like flg_actv_ind will never match "active users" semantically. But if you index the description "boolean flag, whether the subscription is currently active," it will. This is why teams that succeed at text-to-SQL invest in a data dictionary — the embeddings are only as good as the words you feed them.

Your prompt goes from this:

[ 300 tables, 40,000 tokens of DDL ]
Question: revenue from annual plans last month

to this:

-- Retrieved schema (only what's relevant):
CREATE TABLE subscriptions (
  id            bigint PRIMARY KEY,
  customer_id   bigint,
  plan_id       bigint,
  status        text,          -- 'active','canceled','past_due'
  started_at    timestamptz
);
CREATE TABLE plans (
  id       bigint PRIMARY KEY,
  name     text,
  interval text,               -- 'month' | 'year'
  price_cents integer
);
CREATE TABLE invoices (
  id         bigint PRIMARY KEY,
  subscription_id bigint,
  amount_cents    integer,
  paid_at         timestamptz
);
-- Question: revenue from annual plans last month

Now the model has a clean, focused context and reliably produces:

SELECT SUM(i.amount_cents) / 100.0 AS revenue
FROM invoices i
JOIN subscriptions s ON s.id = i.subscription_id
JOIN plans p        ON p.id = s.plan_id
WHERE p.interval = 'year'
  AND i.paid_at >= date_trunc('month', CURRENT_DATE) - INTERVAL '1 month'
  AND i.paid_at <  date_trunc('month', CURRENT_DATE);

Pattern 2: Bring the foreign keys along

Here's a mistake that bites people the first week: they retrieve tables by semantic similarity but forget the relationships. The question mentions "revenue" and "plans," so retrieval returns invoices and plans — but not subscriptions, the join table that connects them. The model now has no path between the two tables and either hallucinates a join or fails.

The fix is a graph-expansion step. After semantic retrieval, walk the foreign-key graph one hop out and pull in any bridging tables:

retrieved:  invoices, plans
FK graph:   invoices → subscriptions → plans
add:        subscriptions  (bridges the two)

Always include the foreign-key definitions in the DDL you pass to the model. Relationships are the single most valuable piece of context for correct joins, and they're cheap to include.

Pattern 3: Add a few real values for tricky columns

The model often has to guess what a status column contains. Does "canceled" mean status = 'canceled', 'cancelled' (British spelling), or 0? It can't know from the schema alone.

Feeding a handful of sample distinct values per low-cardinality column removes the guesswork:

-- Sample values:
--   subscriptions.status: 'active', 'canceled', 'past_due', 'trialing'
--   plans.interval: 'month', 'year'

This one trick eliminates a whole class of "the query ran but returned zero rows" bugs, where the SQL is syntactically perfect but filters on a string that never appears in the data.

Common mistakes and gotchas

Gotcha	Why it hurts	Fix
Indexing cryptic column names, not descriptions	usr_ref_id_2 matches nothing semantically	Embed human-written descriptions from a data dictionary
Top-k too small	You drop a needed table and the query is impossible	Tune k on real questions; err on including bridge tables
Top-k too large	Noise returns; the model links the wrong table	Rerank, and cap at what the question realistically needs
Ignoring foreign keys	Model can't find a join path	Expand one hop along the FK graph after retrieval
No sample values	Correct SQL, zero rows (wrong filter literal)	Include distinct values for low-cardinality columns
Duplicate-looking tables	Three tables named like "orders"; model picks the stale one	Descriptions must disambiguate: "legacy, do not use" vs "current"

One more subtlety worth knowing: the newest, strongest reasoning models are surprisingly good at handling large schemas directly, and some research now questions whether aggressive schema pruning is always necessary. But that's a bet on token budget and latency. For most teams shipping to production today — where every query costs money and users expect answers in under two seconds — retrieving a focused schema is still the pragmatic default. Retrieval also gives you a debuggable artifact: when a query is wrong, you can inspect exactly which tables were fed in and see whether the failure was retrieval or generation.

Key takeaways

The hard part of text-to-SQL at scale isn't writing SQL — it's picking the right tables. Don't paste your whole schema into the prompt; it's expensive, it overflows context, and the noise makes the model worse. Instead, treat the schema as a retrievable resource: embed human-readable descriptions of your tables and columns, fetch the top matches for each question, expand along foreign keys so join paths stay intact, and sprinkle in sample values for ambiguous columns. Together these turn a flaky demo into something you can actually put in front of users.

If you're building this, the unglamorous prerequisite is a good data dictionary. The LLM can only retrieve what you've described well.

Your turn

Are you building a natural-language query layer over a big database? What's been your biggest headache — wrong joins, hallucinated columns, or latency? And how are you handling schema selection: full dump, embeddings, or hand-curated table groups? Drop your approach in the comments — I'm collecting patterns that hold up in production.

Comments

[Mads Hansen]

Schema retrieval also has to be authorization-aware. A global vector index can leak table names, column descriptions, and especially “sample distinct values” before SQL execution ever reaches RLS. Build the candidate catalog from the caller's effective grants/security-barrier views, apply sensitivity/column allow-lists before embedding or returning samples, and key every retrieval cache by principal/tenant/role plus schema-policy digest. Then re-authorize the compiled query at execution time because permissions can change between retrieval and dispatch.

I would also separate schema linking from semantic correctness. “Revenue from annual plans last month” is underspecified: paid cash or recognized revenue, gross or net of tax/refunds, which currency conversion rule, and whose timezone? The example silently chooses paid invoice amount. A production system needs governed metric definitions and approved join paths, not only table descriptions. Returning the chosen metric version, retrieved schema IDs, policy digest, generated SQL, parameters, row count/truncation state, and source freshness makes a plausible answer auditable.

For evaluation, top-k recall is not enough. Keep a versioned question set with required/forbidden tables, expected result invariants, authorization-negative cases, and adversarial values. Measure end-to-end answer correctness and leakage, segmented by tenant/role—not just whether the SQL parses.