The generated SQL was valid. It joined orders to order_items, then to shipments, filtered on orders.created_at, and summed order_items.quantity. It executed in 340ms and returned a revenue number that was 3.4× too high.
Every table was correct. Every column existed. The join syntax was impeccable. The problem was that orders → shipments is one-to-many, and the query fanned out before aggregating. Retrieval had found the right tables and descriptions. It had not retrieved the cardinality of the edge between them, because that fact doesn't live in a table comment. It lives in a foreign key constraint and a unique index.
That's the difference between RAG that retrieves schema text and RAG that understands schema structure. And it's why text-to-SQL accuracy collapses on real warehouses: a model that scores above 91% on academic benchmarks dropped to 21.3% on Spider 2.0's enterprise schemas . The model didn't get worse. The schemas got real.
This is a pipeline design for schemas that are real: hundreds of tables, missing foreign keys, ambiguous column names, and business definitions that exist only in a dbt YAML file.
📋 Table of Contents
- Why Generic RAG Fails on Database Schemas
- 1. Extract a Schema Graph, Not a Schema Dump
- 2. Make the Retrieval Unit a Join Path, Not a Table
- 3. Retrieve Tables Broadly, Then Prune Columns Aggressively
- 4. Enrich With the Metadata That Actually Changes SQL
- 5. Rerank With a Structural Prior, Not Just Cosine Similarity
- 6. Validate the Query Against the Graph, Not Just the Grammar
- 7. Feed Execution Errors Back, but Classify Them First
- 8. Treat Schema Drift as a First-Class Retrieval Problem
- 9. Evaluate Schema Recall, Join Correctness, and Answer Correctness Separately
- What I'd Build, What I'd Skip, and When RAG Is the Wrong Answer
Why Generic RAG Fails on Database Schemas
A database schema is not a document. It's a set of constraints.
A document has prose that can be split into paragraphs without losing much meaning. A schema has tables, columns, types, keys, indexes, and relationships—and the relationships are often the most important part. If you chunk a schema by table and embed the DDL, you capture names and types. You lose the edges. And most SQL correctness problems are edge problems.
There's a second issue: the information a model needs is scattered across systems that don't share a retrieval index. The column type is in information_schema. The business definition is in a dbt YAML file or a data catalog. The join path is in a foreign key constraint—or, if the database is old enough, nowhere at all. The allowed values for a status column might only exist in the data itself.
Research on schema linking keeps finding the same thing. On BIRD, there's a 14.91% performance gap between state-of-the-art schema linking and ground-truth linking . The retrieval step, not the generation step, is where most of the accuracy is lost. If the linker misses a table or picks the wrong column, no amount of prompt engineering fixes the SQL.
The pipeline in the rest of this article addresses four failure modes in order: missing structure, wrong granularity, missing semantics, and silent semantic errors.
🔍 Why this matters: A query that returns a wrong number is worse than a query that errors. An error tells the user something is broken. A plausible wrong number gets pasted into a board deck.
1. Extract a Schema Graph, Not a Schema Dump
Scenario: You connect to a production Postgres warehouse and dump every CREATE TABLE statement into a vector store. When a user asks about "revenue by region," retrieval returns orders, order_items, and regions. It does not return shipments, which is where the actual recognized revenue is recorded, because the shipments table description says "fulfillment events" and never uses the word "revenue."
Why the obvious solution fails: DDL text describes columns. It doesn't describe how tables connect, which table is the grain of the business fact, or which columns are foreign keys. The embedding of a DDL statement is dominated by column names and types, which are the least useful signals for question-to-schema matching.
Solution: Extract a graph with typed edges. At minimum, you need nodes for tables and columns, and edges for foreign-key relationships, joinability discovered from naming conventions or query logs, and containment relationships like view-to-base-table. Alongside each edge, store cardinality: one-to-one, one-to-many, or many-to-many. Cardinality is not optional. It's the fact that prevents the fan-out bug from the opening.
Here's a Postgres extraction that produces a graph table and a cardinality estimate:
-- Nodes and edges from the catalog.
CREATE TABLE schema_edges (
child_table text NOT NULL,
child_column text NOT NULL,
parent_table text NOT NULL,
parent_column text NOT NULL,
cardinality text, -- 'one_to_one' | 'one_to_many' | 'many_to_many'
source text -- 'foreign_key' | 'inferred_name' | 'query_log'
);
INSERT INTO schema_edges
SELECT
tc.table_name AS child_table,
kcu.column_name AS child_column,
ccu.table_name AS parent_table,
ccu.column_name AS parent_column,
-- A unique constraint on the child column makes this one-to-one.
CASE
WHEN EXISTS (
SELECT 1 FROM information_schema.table_constraints u
WHERE u.table_name = tc.table_name
AND u.constraint_type IN ('PRIMARY KEY', 'UNIQUE')
AND u.constraint_name IN (
SELECT constraint_name FROM information_schema.key_column_usage
WHERE table_name = tc.table_name AND column_name = kcu.column_name
)
) THEN 'one_to_one'
ELSE 'one_to_many'
END AS cardinality,
'foreign_key' AS source
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY';
The CASE expression is the part most pipelines skip. A foreign key tells you a relationship exists; it doesn't tell you the direction of the fan-out. Without cardinality, the model can't know that joining orders to shipments multiplies rows.
Real schemas have missing foreign keys. Legacy warehouses, schemas built by ELT tools, and anything older than a decade often have no declared constraints at all. SchemaGraphSQL handles this with an LLM-guided joinability discovery step that infers table connections before graph construction . A cheaper heuristic works surprisingly well: columns named <table>_id that match a primary key in another table, plus columns that co-occur in historical JOIN clauses in the query log.
⚠️ Gotcha: Do not trust
information_schemaalone for cardinality. A foreign key can be declared without a unique constraint on the parent, which means the "parent" side isn't actually unique. Check the parent side too, or you'll label a many-to-many as one-to-many and reintroduce the fan-out.
2. Make the Retrieval Unit a Join Path, Not a Table
Scenario: A user asks, "What was the average time from order to delivery by region last quarter?" The relevant path spans orders → shipments → regions. Table-level retrieval returns all three, but as three separate chunks in arbitrary order. The model sees the tables but not the path between them. It invents a join on region_id that exists in two tables with different meanings.
Why table-level retrieval fails: A table is a node. A question is a path. Retrieving nodes without edges forces the model to reconstruct the graph from names, and models are bad at that—they hallucinate join keys.
Solution: Retrieve paths, not nodes. Precompute the shortest join path between every pair of tables that can be joined, and index each path as a retrieval unit. The path text includes the tables, the join keys, and the cardinality of each edge.
from collections import deque
def shortest_join_path(graph: dict[str, list[tuple[str, str, str]]],
start: str, end: str) -> list[dict] | None:
"""BFS over table-level join edges. Returns the path with join keys and cardinality."""
queue = deque([(start, [])])
seen = {start}
while queue:
table, path = queue.popleft()
if table == end:
return path
for neighbor, child_col, parent_col in graph.get(table, []):
if neighbor in seen:
continue
seen.add(neighbor)
edge = {
"from_table": table,
"to_table": neighbor,
"from_column": child_col,
"to_column": parent_col,
"cardinality": lookup_cardinality(table, neighbor, child_col),
}
queue.append((neighbor, path + [edge]))
return None
Then serialize the path into a compact retrieval document:
JOIN PATH: orders → shipments → regions
orders.id = shipments.order_id (one_to_many)
shipments.region_id = regions.id (many_to_one)
Grain: one row per shipment. Multiple shipments per order.
Aggregation warning: SUM(shipments.amount) must be grouped by shipments.id
before joining to orders, or revenue double-counts.
That "aggregation warning" line is the highest-value sentence in the chunk. It encodes the cardinality consequence in natural language, which is what the embedding model actually needs to retrieve for questions about revenue or delivery time.
For schemas where the full path is too large to precompute, retrieve candidate tables first and then run a pathfinding step at query time. SchemaGraphSQL does exactly this with classical pathfinding over a foreign-key graph and reports state-of-the-art results on BIRD and Spider 2.0 .
🧠 The important part: The retrieval unit should match the shape of the question. "Which table has X?" is a node question. "How do I get from X to Y?" is a path question. Most business questions are path questions wearing node-question clothing.
3. Retrieve Tables Broadly, Then Prune Columns Aggressively
Scenario: You retrieve five relevant tables, and each has 80–150 columns. The prompt is 18,000 tokens of column definitions. The model pays attention to the first and last tables and ignores the middle. It uses status from the wrong table because three tables have a status column.
Why table-level context is too coarse: Sending every column of a relevant table is almost as bad as sending no schema. It burns context and creates ambiguity. ATLAS addresses this with two-stage adaptive schema linking: vector retrieval narrows hundreds of tables to a compact candidate set, then an LLM refines down to the specific columns needed . Multi-agent implementations report 84.4% fewer processed tokens with no quality loss on Firebird enterprise databases .
Solution: Separate table recall from column selection. Stage one maximizes recall—it's cheap and you'd rather include a false positive than miss a table. Stage two maximizes precision—use a small model or a scoring function to select only the columns the question touches, plus join keys and filter columns.
def select_columns(question: str, table: dict, llm) -> list[str]:
"""Return only the columns relevant to the question, plus mandatory join/filter keys."""
mandatory = set(table["primary_key"]) | set(table["foreign_keys"])
# Always include low-cardinality enum-like columns; they're common filters.
mandatory |= {
c["name"] for c in table["columns"]
if c.get("distinct_count") is not None and c["distinct_count"] <= 20
}
prompt = f"""Question: {question}
Table: {table['name']}
Columns: {[c['name'] for c in table['columns']]}
Return only the column names this question needs for SELECT, WHERE, GROUP BY,
or ORDER BY. Do not include join keys. Return JSON: {{"columns": [...]}}"""
chosen = llm.invoke_json(prompt)["columns"]
return sorted(mandatory | set(chosen))
Why this works: Join keys survive pruning even if the model doesn't select them, because without them the query can't connect tables. Enum-like columns survive because they're common filters ("status = 'shipped'"). Everything else is the model's job.
Column pruning also improves accuracy, not just cost. Fewer columns means fewer ambiguous names. If only one table in the pruned set has a region column, the model can't pick the wrong one.
💡 Practical note: Run column pruning with a cheap model, not your SQL generator. Pruning is a classification task, not a reasoning task. A small model plus deterministic rules for keys and enums gets most of the benefit at a fraction of the cost.
4. Enrich With the Metadata That Actually Changes SQL
Scenario: A column is called amt. The business calls it "gross booking value." The finance team excludes refunds. A user asks for "net revenue." Retrieval finds amt because the embedding is close enough, and the model sums it without excluding refunds. The SQL runs. The number is wrong.
Why schema text alone is insufficient: Column names are abbreviations chosen by whoever wrote the migration. The meaning that matters is in the semantic layer, the dbt descriptions, the data dictionary, and the query log. Snowflake's own benchmark work found the same LLM jumping from 57% to 78% on BIRD when grounded in a semantic model . That number is vendor-reported and averages across four databases, so the spread matters more than the headline—but the direction is consistent with independent observations that governed metadata improves accuracy substantially .
Solution: Build a metadata layer alongside the schema graph. Four types of enrichment have the highest return:
- Business definitions and synonyms. "Revenue" →
amt, with the exclusion rule. - Value mappings.
status = 'S'means "shipped." Without this, the model writesWHERE status = 'shipped'and gets zero rows. - Sample values. A few distinct values per low-cardinality column. This is how the model learns that
country_codeuses ISO-2, not full names. - Lineage and ownership. Which table is the source of truth for a metric, and which is a deprecated derivative.
dbt's MCP server exposes exactly this surface: metrics, lineage, documentation, and model metadata through tooling that agents can call . If you already have dbt, you have most of the semantic layer already written—you just need to index it next to the schema.
# dbt schema.yml — the enrichment that changes generated SQL.
models:
- name: fct_orders
description: "One row per order. Recognized revenue only; excludes refunds."
columns:
- name: amt
description: "Gross booking value in USD cents."
meta:
synonyms: ["revenue", "booking value", "GMV"]
excludes: "refunds, chargebacks"
- name: status
description: "Order lifecycle state."
data_tests:
- accepted_values:
values: ['pending', 'paid', 'shipped', 'cancelled']
Index the description, synonyms, and accepted_values into the same retrieval corpus as the table schema, keyed to the column. At query time, retrieve the column plus its business definition. This is what turns "sum amt" into "sum amt where status in ('paid','shipped')".
🔍 Why this matters: Most text-to-SQL failures aren't syntax failures. They're definition failures. The model wrote valid SQL for a different definition of the metric than the one the business uses. No amount of model scaling fixes a missing definition.
5. Rerank With a Structural Prior, Not Just Cosine Similarity
Scenario: Hybrid retrieval returns 30 candidate schema fragments. The top result by cosine similarity is a table whose description mentions "customer revenue" but which is a deprecated materialized view with no join path to the current fact table. The correct table is ranked ninth because its description is terse.
Why embedding similarity alone fails: Embeddings capture topical similarity, not query fitness. A deprecated table can be semantically closer to the question than the canonical table. A table with a long, well-written description will outrank a better table with a short one.
Solution: Rerank with features that reflect whether the candidate can actually answer the question. Useful signals:
- Joinability to already-selected tables. A candidate that can't be joined to the rest of the set is probably wrong.
- Lineage status. Deprecated or downstream-of-deprecated tables get penalized.
- Column coverage. Does the candidate contain columns that match the question's entities and measures?
- Query-log frequency. Has this table been used in successful queries for similar questions?
Arcana implements a version of this: hybrid BM25 plus vector search fused with Reciprocal Rank Fusion, then lineage expansion that automatically pulls in upstream and downstream tables, plus a confidence score that decays unless refreshed . The lineage expansion is the structural prior—it makes the retrieval set coherent rather than a bag of topically similar tables.
def structural_score(candidate: dict, selected: list[dict], question_entities: set[str]) -> float:
score = candidate["similarity"]
# Penalize tables that can't join to anything already selected.
if selected and not any(can_join(candidate, s) for s in selected):
score -= 0.25
# Penalize deprecated objects.
if candidate.get("deprecated"):
score -= 0.40
# Reward column-level entity coverage.
covered = question_entities & set(candidate["column_names"])
score += 0.05 * len(covered)
# Reward tables used in successful similar queries.
score += 0.10 * candidate.get("query_log_affinity", 0.0)
return score
The weights are deliberately modest. Structural signals should nudge the ranking, not dominate it—the embedding still knows more about topical relevance than a handful of heuristics. But a 0.25 penalty for unjoinability is enough to move a deprecated island table below a joinable one.
⚠️ Gotcha: Lineage expansion can explode the candidate set. A fact table with twenty upstream models will pull in all twenty if you expand blindly. Cap expansion at one hop, and only include upstream tables that are actual sources, not intermediate staging models with no unique columns.
6. Validate the Query Against the Graph, Not Just the Grammar
Scenario: The model generates a query that parses cleanly. It references orders.region_id, which doesn't exist—the column is on shipments. It also joins orders to shipments on orders.id = shipments.id, which is a type-compatible join that returns almost nothing. Grammar validation passes. Execution returns zero rows.
Why syntax validation is insufficient: A SQL parser answers "is this valid SQL?" It doesn't answer "does this query use the schema correctly?" Column existence, join-key correctness, and cardinality-safe aggregation are all semantic properties that a parser doesn't check.
Solution: Validate the parsed query against the schema graph. Walk the AST, resolve every table and column reference, check every join predicate against a known edge, and check aggregation safety for one-to-many joins.
import sqlglot
from sqlglot import exp
def validate_against_schema(sql: str, graph, allowed_tables: set[str]) -> list[str]:
errors = []
tree = sqlglot.parse_one(sql, read="postgres")
# 1. Every table reference must exist in the retrieved schema set.
tables = {t.name for t in tree.find_all(exp.Table)}
if unknown := tables - allowed_tables:
errors.append(f"unknown_tables: {sorted(unknown)}")
# 2. Every qualified column must exist on its table.
for col in tree.find_all(exp.Column):
if col.table and not graph.has_column(col.table, col.name):
errors.append(f"unknown_column: {col.table}.{col.name}")
# 3. Every join predicate must correspond to a known edge.
for join in tree.find_all(exp.Join):
on = join.args.get("on")
if on is None:
errors.append("cartesian_join")
continue
for eq in on.find_all(exp.EQ):
left, right = eq.left, eq.right
if isinstance(left, exp.Column) and isinstance(right, exp.Column):
if not graph.has_edge(left.table, left.name, right.table, right.name):
errors.append(f"unknown_join: {left} = {right}")
# 4. Aggregation safety: a SUM over the many side followed by a join to
# the one side must be pre-aggregated or grouped by the many-side key.
errors.extend(check_fanout(tree, graph))
return errors
The fourth check is the one that would have caught the opening bug. It's also the hardest to implement correctly, because not every fan-out is wrong—sometimes the business question genuinely wants the multiplied rows. A practical rule: flag any SUM or COUNT over a column from a one-to-many child table when the query also selects columns from the parent, and require the model to either pre-aggregate in a CTE or explicitly group by the child key. Force the model to make the choice visible rather than silent.
🚨 Production warning: Do not execute a query just because it parses and passes schema validation. Run
EXPLAINfirst and reject queries with estimated row counts above a threshold, or with a cartesian product in the plan. A valid query can still take down a warehouse.
7. Feed Execution Errors Back, but Classify Them First
Scenario: The query fails with column "region" does not exist. The agent feeds the raw Postgres error back to the model, which tries a different column, gets another error, and loops four times before giving up. Each retry costs tokens and latency, and the final answer is "I couldn't generate a query."
Why raw error feedback is inefficient: Database error messages are optimized for humans debugging one query, not for a model in a retry loop. column "region" does not exist doesn't say which tables do have a region column. syntax error at or near "ILIKE" doesn't say the dialect is BigQuery and ILIKE isn't supported.
Solution: Classify the error, enrich it with the schema context the model needs, and retry with a targeted correction. LangChain's SQL agent flow does a version of this: generate, double-check for common mistakes, execute, and correct based on database feedback . The classification layer makes the correction loop converge faster.
import re
def enrich_db_error(error: str, sql: str, graph, dialect: str) -> dict:
if m := re.search(r'column "([^"]+)" does not exist', error):
missing = m.group(1)
candidates = graph.tables_with_column(missing)
return {
"type": "unknown_column",
"column": missing,
"tables_with_column": candidates,
"instruction": "Qualify the column with the correct table, or pick a different column.",
}
if "syntax error" in error and dialect == "bigquery":
return {
"type": "dialect_error",
"dialect": dialect,
"instruction": "Use BigQuery Standard SQL. ILIKE is not supported; use LOWER(col) LIKE LOWER(pattern).",
}
if "division by zero" in error:
return {
"type": "runtime_error",
"instruction": "Guard the denominator with NULLIF(expr, 0).",
}
return {"type": "unknown", "raw": error[:500], "instruction": "Fix the query and retry once."}
The tables_with_column field is the key addition. It turns a dead-end error into a disambiguation problem. The model doesn't have to guess where region lives; it gets the list and picks the right one based on the rest of the query.
Cap retries at two. If the model can't fix the query after two targeted corrections, return a structured failure with the candidate tables and the last error. A third retry almost never succeeds and doubles the cost.
💡 Practical note: Log every failed generation with the question, the retrieved schema, the SQL, and the error class. That log is the best source of evaluation cases and the best signal for which metadata is missing. If
unknown_columnis the dominant error class, your column pruning is dropping something important, or your descriptions aren't matching the vocabulary users actually type.
8. Treat Schema Drift as a First-Class Retrieval Problem
Scenario: The pipeline works for six weeks. Then a migration renames customer_id to account_id, and every query that joins on the old column starts failing. The vector index still contains the old schema, because nothing invalidated it.
Why static indexes fail in production: Schemas change. Columns get renamed, tables get deprecated, new materialized views appear. An index that isn't tied to a schema version is an index that quietly rots. ATLAS calls this out directly: metadata goes stale as schemas evolve, and the fix is a lifecycle that detects DDL changes and regenerates annotations automatically .
Solution: Make schema extraction incremental and event-driven. Hash each table's DDL, compare on every sync, and re-embed only changed tables and the join paths that touch them. Store a schema version with every chunk, and refuse to answer from chunks whose version is older than the current schema version.
import hashlib
def table_fingerprint(ddl: str) -> str:
normalized = " ".join(ddl.split()).lower()
return hashlib.sha256(normalized.encode()).hexdigest()
def sync_schema(conn, vector_store):
current = {}
for table, ddl in fetch_all_table_ddls(conn):
current[table] = table_fingerprint(ddl)
indexed = vector_store.get_fingerprints() # table -> fingerprint
changed = {t for t in current if current[t] != indexed.get(t)}
removed = set(indexed) - set(current)
for table in changed:
vector_store.upsert_table(table, fetch_table_metadata(conn, table), current[table])
# Join paths touching this table are now stale; re-derive them.
vector_store.upsert_paths(paths_touching(table))
for table in removed:
vector_store.delete_table(table)
vector_store.delete_paths_touching(table)
The paths_touching call is what most incremental indexers miss. A table rename invalidates not just that table's chunk but every join-path chunk that referenced it. If you only re-embed the table, retrieval will return paths that reference a column that no longer exists.
🧠 The important part: Schema-aware RAG is a data pipeline, not a one-time embedding job. The retrieval quality is only as good as the freshness of the graph. Treat schema sync like you treat any other ETL: scheduled, idempotent, and monitored for drift.
9. Evaluate Schema Recall, Join Correctness, and Answer Correctness Separately
Scenario: The demo answers 80% of questions correctly. In production, users report wrong answers on questions involving refunds. The final-answer metric didn't distinguish between "retrieved the wrong table" and "retrieved the right table but used the wrong column," so nobody knows what to fix.
Why end-to-end accuracy is a useless debugging metric: Text-to-SQL has at least three separable failure stages: schema linking, query generation, and execution. A single accuracy number collapses them. You need per-stage metrics to know where the pipeline is breaking.
Solution: Evaluate three layers with separate golden sets.
Schema recall. For each question, annotate the required tables and columns. Measure table recall (did you retrieve all required tables?), column recall, and join-path recall. BIRD's schema-linking literature reports table recall and precision in the 77–85% range for strong systems . If your table recall is below 90% on your own schema, nothing downstream matters.
Join correctness. For questions that require a join, check whether the generated query uses the same join keys as the gold query. This catches the fan-out and wrong-key failures that execution accuracy misses—a query can return rows and still join on the wrong column.
Execution accuracy with a semantic check. Compare result sets, not SQL strings. But add a semantic assertion for metrics with business rules: does the query exclude refunds when the question asks for net revenue? Execution accuracy alone won't catch a query that runs and returns a plausible wrong number.
def evaluate(question, gold, generated, result, schema):
return {
"table_recall": len(set(gold["tables"]) & set(generated["tables"])) / len(gold["tables"]),
"column_recall": len(set(gold["columns"]) & set(generated["columns"])) / len(gold["columns"]),
"join_keys_match": set(gold["join_keys"]) == set(generated["join_keys"]),
"execution_match": result.equals(gold["result"]),
"business_rule_ok": check_business_rules(question, generated["sql"]),
}
Run this on every schema sync and every prompt change. Gate the merge on table recall and join-key match. Execution accuracy will fluctuate with data changes; schema recall and join correctness are properties of the pipeline and should be stable.
💡 Practical note: Fifty well-annotated questions beat five hundred unannotated ones. Annotate the tables, columns, join keys, and business rules for each. The annotation work is tedious, but it's the only way to attribute failures to the right stage.
What I'd Build, What I'd Skip, and When RAG Is the Wrong Answer
Here's the architecture I'd choose, based on what holds up under real schemas.
| Layer | Choice | Why |
|---|---|---|
| Schema extraction | Catalog + FK graph + cardinality, with LLM-guided joinability discovery for missing FKs | Edges and cardinality prevent fan-out; inferred joins cover legacy schemas . |
| Retrieval unit | Join paths and schema cards, not raw DDL | Paths match the shape of business questions. |
| Table recall | Hybrid BM25 + vector over schema cards | Lexical catches exact column names; dense catches paraphrases. |
| Column pruning | Small model plus deterministic key/enum retention | 71% token reduction is achievable without accuracy loss . |
| Metadata | dbt descriptions, synonyms, value mappings, lineage, query-log affinity | Semantic grounding is the largest single accuracy lever . |
| Reranking | Structural score: joinability, deprecation, entity coverage | Cosine similarity alone prefers well-written deprecated tables. |
| Generation | Constrained to the pruned schema; validated against the graph | Parse success is not schema correctness. |
| Execution | EXPLAIN gate, classified error retries capped at two, structured failure | Prevents runaway loops and warehouse incidents. |
| Evaluation | Separate table recall, join correctness, execution accuracy, business-rule check | End-to-end accuracy hides the failing stage. |
| Freshness | DDL fingerprinting, re-embed changed tables and adjacent paths, schema version on every chunk | Static indexes rot silently . |
What I'd skip
Embedding raw DDL. It's the least useful representation of a schema. The text is dominated by column names and types, and it contains no edges. Serialize a schema card with the table's grain, its keys, its relationships, and its business definition instead.
A generic "list all tables" tool with no schema context. It works on a 12-table demo and fails at 400 tables. LangChain's SQL agent tutorial shows the correct flow—list, decide relevance, fetch schemas, generate, double-check, execute, correct —but the "decide relevance" step needs a real retriever, not a model reading table names.
Fine-tuning a model on your schema before you've fixed retrieval. Fine-tuning learns the schema you trained on. It doesn't learn the join path you failed to retrieve. Fix schema linking first; it's cheaper and it generalizes.
Building on an archived framework without a migration plan. Vanna, for example, was archived by its owner on March 29, 2026, with the last release dated February 2, 2026 . The patterns are still instructive—DDL, documentation, and verified SQL pairs in a vector store —but the code isn't receiving fixes. Check maintenance status before you build a production dependency on any open-source text-to-SQL framework.
When RAG is the wrong answer
If your schema is small—say, under 30 tables and 300 columns—and stable, don't build a retrieval pipeline. Put the entire schema in the prompt. Retrieval adds latency and failure modes for a problem you don't have. The token cost is manageable, and the model sees every join path without inference.
If your schema is large but you have a well-maintained semantic layer with certified metrics, route through the semantic layer first. Snowflake's own reported numbers show a semantic model lifting the same LLM from 57% to 78% on BIRD , and independent analyses note the spread across databases is wide enough that the average hides real variance . The semantic layer is not a replacement for retrieval, but it's a stronger foundation than raw schema text.
And if the question requires reasoning over data values rather than schema structure—"which customers are likely to churn?"—schema-aware RAG won't help. That's a modeling problem, not a retrieval problem.
The decision that matters
The difference between a schema RAG that works and one that produces confident wrong numbers is not the embedding model or the LLM. It's whether the pipeline retrieves structure—edges, cardinality, grain, and definitions—or just text that mentions tables.
A schema is a graph wearing a document's clothing. Retrieve the graph, validate against the graph, and evaluate the graph. The SQL will follow.