Text-to-SQL Agents That Survive Real Production Databases

The benchmark lie

For a few years, text-to-SQL looked like a solved problem. Leaderboards for Spider 1.0 — the academic benchmark that defined the field — crept past 85% execution accuracy, demo videos showed analysts typing questions in plain English, and every data platform grew a "chat with your data" button. Then in November 2024 the XLang Lab released Spider 2.0, a benchmark built from real enterprise workflows instead of toy schemas, and the field got a correction it is still digesting. The headline result: agent frameworks built on GPT-4o-class models that solved 86.6% of Spider 1.0 managed just 6.0% of Spider 2.0 tasks at launch. Even o1-preview, the strongest reasoning model of the day, dropped from 91.2% to 21.3%.

What changed was not the models. It was the questions. Spider 2.0 comprises 632 workflow problems drawn from industrial settings — Google Analytics exports, Salesforce schemas, open data warehouses — hosted across BigQuery, Snowflake, SQLite, DuckDB and PostgreSQL, with individual databases exceeding 3,000 columns. (Its self-contained Snowflake setting, Spider 2.0-Snow, packages 547 of those examples against a single platform, which is why you will see both numbers quoted.) Queries routinely require nested schemas, dialect-specific functions, multi-step transformations and — the quiet killer — knowing which of forty similarly named revenue columns the finance team actually trusts.

As of July 2026, progress is real but sobering. The best published leaderboard results, as collated in Promethium's benchmark round-up, peak at roughly 59% on the Snowflake setting, about 38% on the harder multi-platform setting and around 40% on the DBT variant. An agentic system with structured semantic memory, AgentSM, reports 44.8% on Spider 2.0-Lite. And the July 2026 Spider 2.0-AIFunc extension — which tests whether models can also drive the AI functions now embedded in warehouse SQL — finds the strongest proprietary models at 67–70% execution accuracy against 58.1% for the best open-source model, with errors concentrating in predicate specification and schema grounding rather than syntax.

System / setting	Spider 1.0	Spider 2.0	Source
GPT-4o-class agent frameworks (Nov 2024 launch)	86.6%	6.0%	Spider 2.0 paper (arXiv:2411.07763)
o1-preview code agent (Nov 2024 launch)	91.2%	21.3%	Spider 2.0 paper
Best leaderboard, Snowflake setting (Jul 2026)	—	~59%	Promethium benchmark round-up
Best leaderboard, multi-platform / DBT (Jul 2026)	—	~38% / ~40%	Promethium benchmark round-up
AgentSM, semantic memory (Spider 2.0-Lite)	—	44.8%	arXiv:2601.15709
Galaksiya pipeline, Gemma-class open model	88.15%	—	Galaksiya engineering write-up

It is worth pausing on why the cliff is so steep, because the reasons dictate the architecture for everything that follows. Spider 1.0 databases average a handful of tables with self-explanatory names; the hard part was translating English into joins and aggregations, and models genuinely mastered that. Spider 2.0 tasks fail earlier in the chain: the model cannot find the right table among hundreds, cannot tell which of several near-duplicate columns is authoritative, does not know that the warehouse stores country as an ISO code, and writes BigQuery functions against a Snowflake database. In other words, the unsolved problem is not SQL generation — it is grounding: connecting language to a specific, messy, undocumented schema. Interestingly, the intermediate BIRD benchmark foreshadowed this: even with cleaner schemas than Spider 2.0, adding realistic values and external knowledge dropped GPT-4o to 52.54% while human experts scored 92–93%.

Two lessons fall out of that table. First, if a vendor quotes you Spider 1.0-era accuracy for "chat with your warehouse", they are quoting the wrong benchmark. Second — and this is the encouraging one — the systems climbing the Spider 2.0 leaderboard are not bigger models; they are better pipelines. Galaksiya's production write-up makes the point sharply: a multi-stage pipeline of schema retrieval, contextual grounding, validation and automatic error correction reached 88.15% execution accuracy on Spider 1.0 with a mid-sized open-weight model, and its authors conclude that architecture and context management moved accuracy more than model selection. The rest of this guide is that pipeline, stage by stage.

Schema linking: the retrieval problem hiding inside the SQL problem

The single largest failure class on enterprise databases is not malformed SQL — modern models rarely produce syntax errors. It is schema grounding: querying the wrong table, joining on the wrong key, or filtering status = 'active' when the column actually stores 'ACTIVE_SUBSCRIBER'. The Spider 2.0-AIFunc error analysis found exactly this — predicate specification and schema grounding dominate the failure distribution across every model family. You cannot paste a 3,000-column schema into a prompt and hope; even when it fits the context window, irrelevant columns actively degrade generation by giving the model plausible wrong choices.

The fix is to treat schema selection as a retrieval problem, exactly as you would in a RAG system. Build one retrievable "card" per table containing the trimmed DDL, a human-written description of what the table is for, representative sample values per column, and the table's foreign-key neighbours. The sample values matter more than anything else on the card: they are how the model learns that your Mumbai orders are tagged 'BOM' not 'Mumbai', and that fiscal year in a UK insurer's warehouse starts in April. Pull the descriptions from your business glossary or DBT documentation if you have them; write them by hand for your top fifty tables if you do not. It is the highest-leverage afternoon you will spend on this project.

At query time, embed the question, retrieve the top handful of cards, then expand the set with foreign-key neighbours so multi-table joins do not dead-end halfway. Six to ten tables of focused context reliably beats three hundred tables of noise.

# schema_cards.py -- retrievable schema context for a text-to-SQL agent
from dataclasses import dataclass, field

import numpy as np
from sentence_transformers import SentenceTransformer

encoder = SentenceTransformer("BAAI/bge-small-en-v1.5")

@dataclass
class SchemaCard:
    table: str
    ddl: str                    # trimmed CREATE TABLE statement
    description: str            # from your business glossary / DBT docs
    sample_values: dict         # column -> up to 3 representative values
    fk_neighbours: list = field(default_factory=list)

    def render(self) -> str:
        lines = [f"Table: {self.table}", f"Purpose: {self.description}", self.ddl]
        for col, vals in self.sample_values.items():
            lines.append(f"  {col} examples: {', '.join(map(str, vals))}")
        return "\n".join(lines)

def build_index(cards: list[SchemaCard]) -> np.ndarray:
    texts = [c.render() for c in cards]
    return encoder.encode(texts, normalize_embeddings=True)

def retrieve_context(question: str, cards: list[SchemaCard],
                     index: np.ndarray, k: int = 6) -> list[SchemaCard]:
    q = encoder.encode([question], normalize_embeddings=True)[0]
    top = np.argsort(-(index @ q))[:k]
    picked = {cards[i].table for i in top}
    for i in top:                       # pull in FK neighbours so joins resolve
        picked.update(cards[i].fk_neighbours)
    by_name = {c.table: c for c in cards}
    return [by_name[t] for t in picked]

The generate–validate–repair loop

The second structural upgrade over "prompt in, SQL out" is refusing to trust the first draft. Production pipelines that score well all share the same shape: generate a candidate query, check it cheaply, execute it safely, and feed any failure back to the model for a bounded number of repair attempts. Each stage catches a different error class.

Static validation comes first because it is free. Parse the candidate with a dialect-aware parser such as sqlglot: a parse failure, a write statement, or a reference to a table you never supplied in context all get caught before the query touches the database. This is also where dialect handling earns its keep — if your warehouse is BigQuery but your few-shot examples were Postgres, transpiling or at least parsing in the target dialect surfaces the DATE_DIFF-versus-DATEDIFF class of bug immediately.

Execution feedback comes second. Run EXPLAIN as a planner-level dry run, then execute with a hard row cap and timeout. A database error message — "column ord.total does not exist" — is the highest-quality repair signal you will ever get, far better than asking the model to critique its own work in the abstract. Zero rows returned is a softer signal: sometimes the honest answer is an empty set, but far more often it means a filter literal missed the real encoding, so it is worth exactly one retry with that hint.

# repair_loop.py -- generate, validate, execute, repair (max 3 attempts)
import sqlglot
from sqlglot import exp

MAX_ATTEMPTS = 3
ROW_CAP = 1000

def static_checks(sql: str, dialect: str) -> str | None:
    """Return an error description, or None if the query looks safe."""
    try:
        tree = sqlglot.parse_one(sql, read=dialect)
    except sqlglot.errors.ParseError as e:
        return f"{dialect} parse error: {e}"
    for node in tree.walk():
        if isinstance(node, (exp.Insert, exp.Update, exp.Delete,
                             exp.Drop, exp.Alter, exp.Create)):
            return "Write/DDL statement detected -- SELECT only"
    return None

def capped(sql: str, dialect: str) -> str:
    return sqlglot.parse_one(sql, read=dialect).limit(ROW_CAP).sql(dialect=dialect)

def answer(question: str, context: str, dialect: str, conn):
    feedback = ""
    for attempt in range(1, MAX_ATTEMPTS + 1):
        sql = generate_sql(question, context, dialect, feedback)  # your LLM call
        if sql.strip() == "CANNOT_ANSWER":
            return None, "Not answerable from the supplied schema"
        error = static_checks(sql, dialect)
        if error is None:
            try:
                conn.execute(f"EXPLAIN {sql}")            # planner dry run
                rows = conn.execute(capped(sql, dialect)).fetchall()
                if rows:
                    return sql, rows
                error = ("Query ran but returned zero rows -- re-check filter "
                         "literals against the sample values in the schema cards")
            except Exception as e:
                error = f"Execution error: {e}"
        feedback = f"Previous attempt:\n{sql}\nProblem: {error}\nFix and retry."
    return None, f"Gave up after {MAX_ATTEMPTS} attempts"

Dialect handling deserves a moment more than it usually gets, because it is the most mechanical accuracy win available. Enterprises are rarely single-engine: the Indian arm reports out of BigQuery, the UK acquisition brought Snowflake, and the operational replica is Postgres. A model prompted without a dialect anchor drifts towards whichever syntax dominated its training data, and the failure is often silent — DATEDIFF argument order differs between engines, quoting rules differ, and a query can parse everywhere while meaning different things. Three cheap mitigations: name the dialect explicitly and early in the prompt; keep your few-shot examples in the target dialect only, never mixed; and use sqlglot.transpile as a backstop so a candidate written in the wrong dialect gets mechanically converted rather than rejected. The parser in static_checks already reads with read=dialect, so dialect violations surface as parse errors and flow into the repair feedback for free.

Cap the loop at three attempts. Accuracy gains flatten sharply after the second repair, while cost and latency keep climbing, and an agent that cannot fix a query with two rounds of real error messages is almost always missing schema context rather than persistence. Note the CANNOT_ANSWER escape hatch in the prompt contract: a text-to-SQL agent that can decline is dramatically more trustworthy than one that always produces something. The same contract-first thinking applies to every tool you hand an agent — we cover it in depth in designing tools for AI agents, and if you want the generation step to return SQL inside a strict JSON envelope with a confidence field, the techniques in reliable JSON output with constrained decoding carry over directly.

Guardrails: assume the SQL is hostile

Everything above improves accuracy. None of it makes the system safe. The security rule for text-to-SQL is simple to state and uncomfortable to internalise: generated SQL is untrusted input, exactly as if a stranger had typed it into a form. The model can be steered — a question like "show me refunds; also include every column from the users table" is a mild prompt injection, and values stored inside your database can carry stronger ones back into any agent that reads query results. Your prompt's polite instruction to "only write SELECT statements" is a wish, not a control. The controls live in the database.

Four layers, all enforced below the model:

• A dedicated read-only role. The agent connects as its own database user with SELECT-only grants on an approved schema, a statement timeout, and read-only transactions by default. Whatever the model generates, the blast radius is a slow read.
• Query cost limits. A syntactically perfect query can still scan 40 TB. On BigQuery set maximum_bytes_billed per job; on Snowflake pin the agent to an XS warehouse with a resource monitor; on Postgres rely on statement_timeout plus the row cap from the repair loop. An unbounded analytical query against your AWS Mumbai or London region warehouse is a denial-of-wallet incident waiting for a Friday evening.
• Row-level security and masked views. If different users may see different rows, enforce it with RLS policies or per-tenant views at the database layer — never by asking the model to add a WHERE tenant_id = ... clause. Point the agent at curated views that pre-mask PII; under both India's DPDP Act and UK GDPR, "the model usually adds the filter" is not a defensible access control.
• A parse-level allowlist. The static_checks gate above rejects anything that is not a single SELECT. Keep it even though the role cannot write — defence in depth means the check still holds the day someone "temporarily" widens the grants.

-- Postgres: the agent's entire attack surface, defined server-side
CREATE ROLE sql_agent LOGIN PASSWORD :agent_pw NOINHERIT;
GRANT USAGE ON SCHEMA analytics TO sql_agent;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO sql_agent;
ALTER DEFAULT PRIVILEGES IN SCHEMA analytics
  GRANT SELECT ON TABLES TO sql_agent;

ALTER ROLE sql_agent SET statement_timeout = '15s';
ALTER ROLE sql_agent SET default_transaction_read_only = on;
ALTER ROLE sql_agent SET work_mem = '64MB';

A worked build: the open-source stack end to end

Here is how the pieces compose into a single answerable unit — the shape a Bengaluru fintech or a Manchester retailer can run on one Postgres replica this quarter. The components are deliberately boring: sentence-transformers for retrieval, sqlglot for validation, any competent instruction-following model behind an OpenAI-compatible endpoint for generation, and the two modules above doing the structural work.

The generation prompt is the one place where every earlier investment pays out. It carries the rendered schema cards, the glossary entries retrieval selected, the dialect, the rules of engagement and the escape hatch:

# generate.py -- the generation step used by answer() above
PROMPT = """You are a senior analytics engineer writing {dialect} SQL.

Database context (the ONLY tables and columns that exist):
{schema_context}

Business definitions:
{glossary}

Rules:
1. Use only tables and columns shown above. If the question cannot be
   answered from them, return exactly: CANNOT_ANSWER
2. Produce ONE read-only SELECT statement. No DDL, DML or comments.
3. Qualify every column with a table alias.
4. Match filter literals to the sample values shown (e.g. status =
   'ACTIVE_SUBSCRIBER', not 'active').
5. If a metric is defined in the business definitions, use that exact
   formula rather than inventing one.
{feedback}

Question: {question}

Return only the SQL."""

def generate_sql(question, context, dialect, feedback=""):
    fb = f"\nRepair context:\n{feedback}\n" if feedback else ""
    prompt = PROMPT.format(dialect=dialect, schema_context=context,
                           glossary=load_glossary_snippets(question),
                           feedback=fb, question=question)
    return llm.complete(prompt, temperature=0).strip().removeprefix(
        "```sql").removesuffix("```").strip()

A note on the model behind llm.complete. The Spider 2.0-AIFunc results put the strongest proprietary models roughly nine to twelve points ahead of the best open-source alternative on enterprise SQL (67–70% versus 58.1% execution accuracy), so if accuracy is the only axis, a frontier API model is the default. But the Galaksiya result cuts the other way: with the full pipeline around it, a mid-sized open-weight model reached 88.15% on Spider 1.0 — and for teams with data-residency constraints, a self-hosted model in an AWS Mumbai (ap-south-1) or London (eu-west-2) region VPC may be the only configuration a compliance team will sign off, since the schema cards and sample values you send with every request are themselves metadata about customer data. The pipeline is model-agnostic by construction; benchmark both on your golden set and let the numbers, not the marketing, choose.

Wiring it together: build the schema cards nightly from information_schema plus your glossary; embed and cache the index; on each question run retrieve_context, render the cards into schema_context, and hand the lot to answer(). Temperature zero, because you want the same question to produce the same query on Tuesday as it did on Monday — determinism is a feature in analytics, whatever it costs you in flair. The whole system is perhaps four hundred lines before observability, and every line is inspectable, which is exactly the property regulated teams in both India and the UK need when an auditor asks why a number appeared in a board pack.

Two additions earn their place as the workload grows. First, a verified-query memory: when a human confirms an answer was right, store the question, the SQL and the schema snapshot, and retrieve near-neighbour examples into future prompts as few-shot demonstrations. This is the pattern the AgentSM work formalises — structured reuse of prior reasoning cut token spend by 25% and trajectory length by 35% while setting a Spider 2.0-Lite state of the art. Second, a semantic cache in front of generation, so the fifth phrasing of "revenue by region last quarter" costs you a lookup, not a model call.

Evaluating your own system honestly

Public leaderboards tell you the field's ceiling; they say nothing about your warehouse. The metric that matters is execution accuracy — does the generated query return the same result set as a known-correct one — not exact string match, which flunks every legitimate rewrite (a different join order, a CTE instead of a subquery) and taught a generation of researchers to game surface form. Compare result sets order-insensitively unless the question specifies an ordering, and normalise types before comparing: Decimal('42.0') versus 42.0 has wasted more eval afternoons than any model regression.

The comparison itself is twenty lines you will reuse for years:

# eval_harness.py -- execution accuracy over a golden set
from collections import Counter
from decimal import Decimal

def normalise(rows):
    """Order-insensitive, type-normalised multiset of result rows."""
    def norm(v):
        if isinstance(v, Decimal):
            return float(v)
        if isinstance(v, float):
            return round(v, 6)
        return v
    return Counter(tuple(norm(v) for v in row) for row in rows)

def execution_match(conn, predicted_sql, gold_sql) -> bool:
    try:
        got = conn.execute(predicted_sql).fetchall()
    except Exception:
        return False
    want = conn.execute(gold_sql).fetchall()
    return normalise(got) == normalise(want)

def run_suite(conn, golden_set, generate):
    passed = 0
    for case in golden_set:                    # {"question", "gold_sql", "tier"}
        sql, _ = generate(case["question"])
        if sql and execution_match(conn, sql, case["gold_sql"]):
            passed += 1
        else:
            print(f"FAIL [{case['tier']}] {case['question']}")
    print(f"Execution accuracy: {passed}/{len(golden_set)}")

The asset you are building is a golden query set. Collect 50–150 real questions your analysts actually asked — pull them from your BI tool's history or your support channel, not your imagination — and pair each with gold SQL written and verified by someone who knows the warehouse. Stratify by difficulty: single-table aggregations, two-to-three-table joins, and the genuinely hard tier of window functions, self-joins and business-metric definitions. Freeze a database snapshot for the harness so results are reproducible, then run the suite on every prompt edit, model swap or schema migration, in CI, with the pass rate tracked over time. Fifty questions sounds small next to Spider 2.0's hundreds, but fifty questions from your own workload will predict your production accuracy better than any public benchmark.

Production adds a twist: live traffic has no gold queries. This is where schema-agnostic monitoring earns a look — the STEF framework scores generated SQL against the question's extracted semantic intent without needing reference queries or even schema access, producing a 0–100 signal you can alert on. Route low-scoring answers to human review, and promote every confirmed failure into the golden set: your eval suite should grow from production pain, not synthetic padding. For the wider discipline — scoring trajectories and outcomes rather than single outputs — see our guide to evaluating AI agents end to end.

Common pitfalls

• Demoing on Spider-sized schemas. Ten clean tables tell you nothing. Pilot on your ugliest schema — the one with cust_mstr_v2_final in it — or the pilot's success will not survive contact with production.
• Stuffing the full schema into context. Past a few dozen tables, accuracy falls as context grows: every irrelevant column is a plausible wrong answer. Retrieval is not an optimisation; it is the mechanism.
• Skipping sample values. Column names alone cannot tell the model that your status codes are upper-case, your regions are IATA-coded or your UK fiscal year starts in April. Most "wrong answer, valid SQL" failures trace to predicate literals — the same predicate-specification cluster the Spider 2.0-AIFunc error analysis flags.
• Trusting the prompt for security. "Only write SELECTs" belongs in the prompt and in the database role and in the parser gate. Any layer alone will eventually fail; three layers fail loudly instead of silently.
• Unbounded repair loops. Retrying five, six, seven times converts a wrong answer into an expensive wrong answer. Three attempts, then decline gracefully with the best diagnostic you have.
• Answering everything. The most damaging output is a confident, plausible, wrong number in a spreadsheet. Ship the CANNOT_ANSWER path, surface the generated SQL alongside every result, and let users one-click flag wrong answers into your golden set.
• Evaluating once. Schemas drift weekly. A golden suite that ran only at launch is documentation, not evaluation — wire it to CI and to schema-migration hooks.

Where this leaves you

The honest summary of text-to-SQL in mid-2026: the demo is easy, the product is an engineering discipline, and the gap between them is measured by an 80-point benchmark cliff. But the discipline is now well mapped. Schema linking with real sample values attacks the grounding failures that dominate the error distribution. A three-attempt generate–validate–repair loop converts database error messages — the best feedback signal you have — into accuracy. Database-enforced guardrails make the inevitable bad query boring instead of catastrophic. And a golden set built from your own analysts' questions tells you the truth about your system when no leaderboard can.

Start narrow. Pick one schema, twenty tables, the fifty most-asked questions, and get execution accuracy above 80% on that scope before widening it. That is a four-to-six-week project for one engineer with the components in this guide, and it produces something a "connect your warehouse" chat button cannot: a system whose failure modes you understand and whose answers you can defend. If you build one — and especially if you have leaderboard-grade war stories about the queries that broke it — that is exactly the kind of concrete, verifiable work worth showing where hiring teams can find it.