Blog /
Schema Prompting vs. Schema Retrieval: Choosing an NL2SQL Context Strategy
Published . Written for nlqueries-core 0.4.
schema-retrieval text-to-sql evaluation
Picture a finance analyst typing a simple question into your internal data assistant: "For tenant A, what were paid-order revenue and paid-order count in October 2026?" The words in that sentence point squarely at an orders table. But the tenant doesn't live on orders. It lives on customers, one foreign key away. If the context you hand the model contains orders and not customers, the model either invents a tenant_id column, quietly drops the filter, or guesses a join it can't see. None of those failures will look like a crash. They'll look like a number.
That's the whole tension between the two main ways of giving a natural-language-to-SQL (NL2SQL) system its schema context. You can put the full schema in the prompt and accept the token bill, or you can retrieve the parts that look relevant and accept the risk of leaving out the table that mattered. This post is about choosing between them deliberately: what each strategy actually does, how to measure the failure each one is prone to, and why "retrieval saves tokens" isn't a sufficient reason to adopt it. Product behaviour here comes from documentation and source at pinned versions; the only code executed for this article is a tiny SQLite fixture and a helper script, both shown below.
Two ways to lose the table you needed
It helps to name the two failure modes, because they call for different instruments.
Schema prompting, also called full-schema context, means the model sees every table and column it's allowed to use. Nothing relevant is missing by construction, so the risk is overload. On a schema with hundreds of tables, including a few generations of similarly named historical ones like orders, orders_v2 and orders_archive_2019, the model has to pick the right object out of a crowd, and you pay for that crowd on every call.
Schema retrieval means a search step picks a subset of tables (sometimes columns or sample rows too) and only that subset reaches the model. Prompts get smaller, but you've added a second component that can be wrong. When retrieval misses a table, the generator never had a chance. Bridge tables are a likely victim: a table that exists to connect two others often shares little vocabulary with the question, so semantic similarity alone has no strong reason to rank it highly.
The trap is evaluating either strategy only by whether the final answer came out right. A retrieval step that dropped customers might still produce a correct answer on a lucky day, if the model guessed the join from naming conventions. That's not a success you can rely on, and you'll only see it if you measure retrieval separately from generation.
Know what your baseline actually does
Before comparing strategies, be precise about the system you already have. It's easy to describe a tool as "retrieval-based" when it's doing something subtler.
NLQueries Core 0.4.1 is a good example. In its prompt-assembly module, the full knowledge-base schema goes into a stable block of the prompt, while relevant-table hints and query capsules (example query patterns built from query history) are added in a separate dynamic section (prompt_assembly.py at Core 0.4.1). So stock NLQueries isn't a pruning system. It's full-schema prompting with retrieval used to point at likely tables, not to remove the rest. A hint and a prune are different things, and conflating them will wreck your experiment design.
Two caveats keep that description honest. First, "full schema" means the full schema in the knowledge base, not necessarily every physical object in the database. What gets exported into the KB is a decision you make. Second, the extra_dynamic_context option visible in the same module, and among the parameters of the Python runner entry points (sync_runner.py), appends guidance to the prompt. It doesn't replace the schema block. If you want to test a genuinely pruned prompt around NLQueries, that's custom work: a fork or an explicit prompt-assembly adapter that you write, instrument and label as yours.
LlamaIndex sits on the other side of the comparison in a useful way. Its text-to-SQL guide documents both a fixed-table setup built around SQLDatabase and NLSQLTableQueryEngine, and variants that retrieve table schemas (optionally rows or columns too) at query time (LlamaIndex Text-to-SQL guide). Both need an LLM, embeddings and runtime configured explicitly, and the guide's existence doesn't tell you how well its retriever will do on your schema. What it does show is that "full" and "retrieved" are configuration choices, not product identities.
So for a fair experiment you have three lanes, not two:
| Lane | What the model sees | Who maintains it |
|---|---|---|
| Full authorized schema | Every table the caller may use | You (schema export) |
| Full schema + retrieval hints (stock NLQueries Core 0.4.1) | Everything, plus "look here first" guidance | Upstream prompt assembly |
| Retrieval-pruned + relation expansion | Only retrieved tables, plus the related tables your expansion step adds | Custom adapter you write |
Measure retrieval before you look at SQL
If you take one thing from this post, make it this: log what the retriever selected before the model generates anything, and compare it to a hand-labelled set of objects the question requires. That gives you a metric called required-object recall, the fraction of the tables (and, if you go that far, columns) the correct query needs that actually made it into the context.
Recall alone isn't enough, because a retriever can return orders and order_items and miss customers entirely. The second check is join closure: given what was retrieved, can you reach every table the query needs by following declared foreign keys? One common mitigation is to expand the retrieved set along those relationships before generation.
Here's a small helper that does both against the fixture schema. It's custom code, not part of NLQueries or LlamaIndex, and it was run on Python 3.12.9 with this output:
# recall_check.py -- custom evaluation helper (not part of any NL2SQL tool).
# Measures required-object recall and join closure for a retrieved table set.
FOREIGN_KEYS = { # child table -> parent tables it references
"orders": {"customers"},
"order_items": {"orders"},
}
def join_closure(tables):
"""Add every parent table reachable through declared foreign keys."""
closed, frontier = set(tables), list(tables)
while frontier:
for parent in FOREIGN_KEYS.get(frontier.pop(), ()):
if parent not in closed:
closed.add(parent)
frontier.append(parent)
return closed
def required_recall(retrieved, required):
hit = retrieved & required
return len(hit) / len(required), sorted(required - retrieved)
required = {"orders", "customers"} # oracle set for the tenant-A question
retrieved = {"orders", "order_items"} # what a similarity search might return
print("raw:", required_recall(retrieved, required))
print("closed:", required_recall(join_closure(retrieved), required))
raw: (0.5, ['customers'])
closed: (1.0, [])
The retrieved set is made up to show the mechanics, not taken from a real retriever. Still, it shows the shape of the problem. Raw recall is 0.5, and the missing table is the one holding the tenant. Following the orders → customers foreign key recovers it.
Don't read more into that 1.0 than it says. join_closure only walks from a child table to the parents it references, which is enough here because orders points at customers. It isn't enough for a bridge. Give the same function a bridge table that references both customers and products, start it from those two parents, and it returns only the parents: required-object recall stays at 2/3 and the bridge is still missing. Nothing in the parents' own foreign keys leads back to the child that connects them, and that's exactly the shape of the bridge-table omission described earlier.
So treat the helper as a demonstration of parent expansion only. A general join-context strategy must also consider child and bridge relations and the join paths you actually select, with explicit authorization and size limits on what expansion may add. Validate required-object recall against the oracle after expansion; no closure heuristic guarantees that the needed bridge was found.
Expansion isn't free either. On a real schema, walking every relationship in both directions can pull half the database back in, which is exactly what you were trying to avoid. So log both numbers: how much expansion added, and whether it recovered what was missing. And count an omission as an omission even when the model happens to produce the right SQL anyway.
A wrong join can look right
Recall tells you whether the needed tables were present. It doesn't tell you whether the model used them correctly, and over-inclusion has its own failure mode. Here's the fixture: three tiny tables, labelled sample data, designed so the mistakes are visible.
-- Synthetic fixture (sample data). Executed on SQLite 3.45.3.
CREATE TABLE customers(id INTEGER PRIMARY KEY, tenant_id TEXT NOT NULL, region TEXT);
CREATE TABLE orders(id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id),
order_date TEXT, amount_cents INTEGER, status TEXT);
CREATE TABLE order_items(id INTEGER PRIMARY KEY, order_id INTEGER REFERENCES orders(id), sku TEXT);
INSERT INTO customers VALUES(1,'A','East'),(2,'A','West'),(3,'B','North');
INSERT INTO orders VALUES
(101,1,'2026-10-01',10000,'PAID'),(102,1,'2026-10-02',5000,'CANCELLED'),
(103,2,'2026-10-31',20000,'PAID'),(104,3,'2026-10-02',70000,'PAID'),
(105,1,'2026-11-01',40000,'PAID'),(106,2,'2026-10-15',NULL,'PAID');
INSERT INTO order_items VALUES(1,101,'a'),(2,101,'b'),(3,103,'c');
-- Reference answer
SELECT SUM(o.amount_cents) AS revenue_cents, COUNT(*) AS paid_order_count
FROM orders AS o JOIN customers AS c ON c.id = o.customer_id
WHERE c.tenant_id = 'A' AND o.status = 'PAID'
AND o.order_date >= '2026-10-01' AND o.order_date < '2026-11-01';
Against this fixture the reference query returns (30000, 3). Order 106 has a NULL amount, so it counts as an order but adds nothing to revenue. Order 105 falls on November 1, outside the half-open date range, and order 104 belongs to tenant B. Now suppose a context strategy hands the model order_items as well, and the model adds an inner join to it, perhaps because the question said "orders" and the table name looked relevant. The same filters then return (40000, 3).
Look closely at why. Order 101 has two line items, so its 10,000 cents is counted twice. Order 106 has no items, so the inner join drops it. The count happens to stay at 3 because one row was duplicated and another vanished, and that's the dangerous part: the number a reviewer is most likely to sanity-check looks fine while revenue is inflated by a third. Both queries are syntactically valid, reference real columns and would pass a schema check. This is why you compare result sets against an independently adjudicated answer, and why you don't treat a valid query as a correct one.
The fixture is a smoke test, not a benchmark. But it gives you a handful of variants worth adding to any evaluation set: omit the date or tenant from the question, ask about a column that doesn't exist, request a table the caller shouldn't see, and change one amount before re-running, so you know the system isn't answering from a cache.
Change one thing at a time
A full-schema versus retrieval comparison is only meaningful if the retrieval step is the only thing that differs. That means the same model, the same metadata quality (descriptions, comments, examples), the same questions, the same permissions and clean sessions with caching disabled. If one lane gets better column descriptions, you're measuring documentation, not context strategy.
Three experiments are worth running, in order of how much they cost. None of them has been run for this article.
Inspect the prompt first. Load the fixture schema into the knowledge base along with twenty irrelevant table definitions, then capture the stable and dynamic blocks that stock NLQueries actually assembles. Count the table names and tokenizer tokens in each. Based on the source, you'd expect the full KB schema in the stable block. The experiment passes if your written description of the architecture matches the captured prompt, whatever the answers turn out to be. It's cheap, and it stops you from publishing a wrong claim about your own baseline.
Then run the ablation. Add an explicit bridge-table relationship and oracle object sets, freeze the question list, and run all three lanes with the same model. Vary the retrieval cutoff (often called k) and whether relation expansion is on, and make sure the expansion lane is tested on a question whose oracle set includes the bridge, since that's the case parent-only expansion misses. For every run, log selected objects, recall, tokens, generated SQL and the result. The outcome could be positive, neutral or negative for retrieval, and you shouldn't claim an advantage without paired records for the same question across lanes.
Finally, test drift and fallback. Rename a relation, add a forbidden table and ask an ambiguous question. Check that the retrieval index is invalidated after the schema change, and that a low-confidence retrieval leads to an explicit fallback or a clarifying question rather than a silent answer built from whatever was found. Retrieval adds index-freshness failure modes. Full-schema prompting still needs its exported KB refreshed when the physical schema or permissions change: in NLQueries the prompt's schema comes from the knowledge base (prompt_assembly.py), which you produce with nlqueries export-kb (getting started). So run the rename test against both lanes, not just the retrieval one.
When you report results, keep parse validity, policy acceptance, result correctness, refusal quality and execution success as separate columns. Separate cold starts, warm uncached requests and cache hits too, and label any token counts you estimated rather than took from the provider.
Retrieval is not an access control
It's tempting to think of retrieval as a safety feature, since tables the model never sees are tables it can't query. Don't build on that. Retrieval is a relevance heuristic. It will happily return a sensitive table if the question resembles it.
The order of operations matters. Filter the schema down to what the caller is authorized to use first, then retrieve within that set, then generate. If your custom pruning adapter runs search over the full catalogue and filters afterward, you've already leaked metadata into the retrieval step, and possibly into logs. The same ordering applies to relation expansion: an expansion step that follows foreign keys out of the authorized set can pull a forbidden table back in, which is why its limits have to include authorization and not just size.
And neither strategy replaces the controls that sit after generation. NLQueries' own hardening guide treats its generated-statement SQL policy and database-level least privilege as separate safeguards, each with engine-specific limits (database hardening). The policy code inspects statements and functions per dialect (sql_policy.py), but reading that source isn't the same as adversarially testing it, and a policy that accepts a statement says nothing about whether the statement answers the business question. Whatever context strategy you pick, run the generated SQL under a database role that can only see what the user may see. The tenant filter in the reference query above is the math for the expected answer, not a security boundary.
So which should you choose?
On a small, well-named schema, start with full-schema prompting and stay there unless the numbers push you off it. You avoid a whole category of omission failures, the prompt is easy to inspect, and there's no retrieval index to keep fresh, though the exported schema still has to track the database. Adding retrieval hints on top, as stock NLQueries does, gives the model guidance without taking anything away.
Retrieval-pruned context starts to earn its complexity when the schema is large or noisy enough that full context becomes expensive or confusing: hundreds of tables, overlapping historical names, wide tables with columns nobody uses. Even then, adopt it with three guardrails. First, a relation-expansion step that considers child and bridge relations as well as parents, bounded by authorization and size limits, with required-object recall re-checked after expansion rather than assumed. Second, a recall log so omissions are visible. Third, a fallback to full authorized context (or a clarifying question) when retrieval confidence is low.
What you shouldn't do is assume retrieval helps because the prompts are shorter. Shorter prompts are a cost metric. Correct answers are the goal, and the two can move in opposite directions.
The practical next step is small. Take ten real questions from your users, hand-label the tables each one requires, and make sure at least one of them needs a bridge or junction table. Run your current context path with selection logging switched on. If required-object recall is already at 1.0 with full context and your token bill is acceptable, you have your answer. If it isn't, you now have the harness to test retrieval honestly.
References
- NLQueries Core 0.4.1,
nlqueries/orchestrator/prompt_assembly.py(commit 2fbf276). https://github.com/nlqueries/nlqueries/blob/2fbf276bf299e0b019c41cb62c5e6969d4c33ff9/nlqueries/orchestrator/prompt_assembly.py - LlamaIndex, Text-to-SQL Guide (Query Engine + Retriever), live page accessed 2026-11-06. https://developers.llamaindex.ai/python/examples/index_structs/struct_indices/sqlindexdemo/
- NLQueries Core 0.4.1,
nlqueries/orchestrator/sync_runner.py. https://github.com/nlqueries/nlqueries/blob/2fbf276bf299e0b019c41cb62c5e6969d4c33ff9/nlqueries/orchestrator/sync_runner.py - NLQueries Core 0.4.1, Hardening the database NLQueries reads. https://github.com/nlqueries/nlqueries/blob/2fbf276bf299e0b019c41cb62c5e6969d4c33ff9/docs/database-hardening.md
- NLQueries Core 0.4.1,
nlqueries/sql_policy.py. https://github.com/nlqueries/nlqueries/blob/2fbf276bf299e0b019c41cb62c5e6969d4c33ff9/nlqueries/sql_policy.py - NLQueries Core 0.4.1, Getting started (knowledge-base export). https://github.com/nlqueries/nlqueries/blob/2fbf276bf299e0b019c41cb62c5e6969d4c33ff9/docs/getting-started.md
- NLQueries Documentation (version banner v0.4.1, docs 2fbf276, 2026-11-02). https://nlqueries.com/docs/