Database agents that know when they're wrong

The failure I care about in a database agent is not the query that errors. It is the query that runs clean, returns a number, and is wrong because a join fanned out and silently multiplied a SUM. Nothing in the stack complains. The figure goes into a report, it gets acted on, and if the error is caught at all it is caught weeks later, after the decision it informed has already been made.

I built the query layer for a system whose numbers were read and used without anyone re-deriving them by hand. That constraint, rather than any interest of mine in benchmarks, drove everything below. The job was to turn silent wrongness into loud failure, against Postgres and against a document store, and the answers had less to do with prompting than with what sits around the model.

The gap that hasn't closed

BIRD is the benchmark I keep returning to, because its authors bothered to measure what a human does on the same questions. The human baseline is 92.96% execution accuracy, set by data engineers and database students, with the figure dated December 16 2025. The top of the leaderboard reaches roughly 82% on test. AskData with GPT-4o sits at 77.64 dev and 81.95 test; Agentar-Scale-SQL at 74.90 and 81.67. Every one of those numbers is produced under what BIRD calls oracle knowledge, meaning an external evidence string is supplied alongside the question, a generous setting compared to production.

The benchmark itself is 95 databases totalling 33.4 GB, with 12,751 question-SQL pairs. The roughly eleven point gap to the human baseline has persisted, and the closing that did happen came out of multi-stage pipelines rather than raw model capability.

Spider 2.0 paints a harsher picture, and I cite only the paper baselines. Spider-Agent with o1-preview reaches 23.58 on Snow and 23.03 on Lite. With GPT-4o-2024-11-20 it reaches 12.98 and 13.16. The most clarifying line comes from the project site: "GPT-4o achieves only 10.1% success on Spider 2.0, compared to 86.6% on Spider 1.0." That sentence quantifies how much of Spider 1.0 was benchmark rather than problem.

I deliberately don't cite the top of the public Spider 2.0 leaderboard. Snow shows 96.70 while Lite tops out at 76.23 on the same 547 examples under different execution settings. Those entries are self-reported, and a configuration scoring 96.70 on a set of problems where a sibling configuration scores 76.23 on that identical set is not describing something physically sensible. The paper baselines are lower and believable, so those are the ones I use.

What the leaderboards are partly measuring

The paper that changed how I read all of this is arXiv 2601.08778, which also appeared at CIDR 2026. The authors re-annotated benchmark data and found an annotation error rate of 52.8% on BIRD Mini-Dev and 62.8% on Spider 2.0-Snow. Re-evaluating all sixteen open-source BIRD leaderboard agents against corrected data moves results by anywhere from -7% to +31% and moves rankings by -9 to +9 positions.

The number that matters most is the correlation. Spearman correlation against the full set is r=0.85 on the uncorrected subset and r=0.32 on the corrected subset. Read plainly, that means the ordering you see on the leaderboard tracks annotation noise about as much as it tracks capability. The error categories are mundane: timestamp casting, join semantics, output-format ambiguity. They are the same things that go wrong in real analytics work.

One honest caveat. The arXiv and CIDR versions of that work disagree with each other. The arXiv abstract gives 62.8% for Spider 2.0-Snow; the CIDR version gives 66.1%. I cite the arXiv figure and flag that the two versions differ, because quoting one without acknowledging the other would be presenting more precision than the source supports.

Execution accuracy has its own problems underneath all of that, though here I'm on softer ground. The ETM paper in MDPI Future Internet 17(8):325 is reported to find false-positive rates up to 23.0% and false-negative rates of 28.9% for execution accuracy and exact set match. I have not read its methodology, so I treat those as an indication of magnitude rather than as measurements I'd defend. The mechanism needs no figure: a false positive means two queries returned identical result sets on one test database and were scored equivalent when they are not. The lineage of fixes runs from test-suite accuracy (arXiv 2010.02840), the official Spider metric since 2020, through ETM's tree matching to LLM-based equivalence judging.

Scaffolding, in rough order of value

The systems that do well are pipelines, and their published ablations tell you which pieces earn their cost.

CHESS runs four agents: Information Retriever, Schema Selector, Candidate Generator, Unit Tester. It reaches 71.10% on BIRD test, within 2% of the leading proprietary method at the time, with roughly 83% fewer LLM calls. The Schema Selector alone accounts for around +2% accuracy and a 5x token reduction, which is the clearest evidence I know that narrowing the schema before generation buys more per unit of effort than anything else in the pipeline.

The rest of what I read, I'm citing secondhand from reported figures rather than from papers I've worked through, so I use them for direction and not for decimals. The XiYan-SQL paper is reported at 75.63% on BIRD and 89.65% on Spider test, with an ablation showing that replacing its trained candidate selector with plain self-consistency costs about 3 points. If that holds, generate N and vote is the weak version of candidate selection, which matches what I saw without giving me a number I'd quote.

MAC-SQL, reported at 59.59 EX on BIRD test, uses a Selector, Decomposer and Refiner, and the part I took from it is architectural rather than numerical: its Refiner checks syntax, execution feasibility, and empty result sets. Treating an empty result as a suspicious signal rather than as an answer is close to free and catches a whole family of wrong filters.

DAIL-SQL is reported at 86.6% on Spider, and its stated core finding changed how I do few-shot selection. Models learn the mapping between a question and a SQL skeleton, so examples should be selected by skeleton similarity rather than surface text similarity. PremSQL takes the simplest version of repair, execution-guided decoding, where you append the database error to the context and regenerate, with a reported default cap of five trials.

Ordered by value per unit of engineering: parse and syntax repair first, because it is nearly free, then execution errors fed back and regenerated, then empty results treated as suspicious, then multiple candidates with a real selector, then LLM-generated natural-language unit tests over the returned rows. The first is where a parser earns its place.

# sql_guard.py
"""AST-level checks over model-generated SQL.
 
sqlglot is a parser and a transpiler. It is not a validator, and nothing here
is sqlglot promising safety: every rule below is one I wrote on top of the AST,
and a query that passes all of them can still be semantically wrong.
"""
 
from sqlglot import exp, parse_one
from sqlglot.errors import ParseError
 
ALLOWED_TABLES = {
    "analytics.v_orders",
    "analytics.v_customer",
    "analytics.v_product",
}
 
FORBIDDEN = (
    exp.Insert, exp.Update, exp.Delete, exp.Drop, exp.Create,
    exp.Alter, exp.Merge, exp.Grant, exp.Command,
)
 
MAX_ROWS = 5_000
 
 
class Rejected(Exception):
    """Carries a message the agent is allowed to read and retry against."""
 
 
def guard(sql: str, dialect: str = "postgres") -> str:
    try:
        tree = parse_one(sql, read=dialect)
    except ParseError as err:
        raise Rejected(f"unparseable: {err}") from err
 
    if not isinstance(tree, exp.Select):
        raise Rejected(f"root is {type(tree).__name__}, expected Select")
 
    for node in tree.walk():
        if isinstance(node, FORBIDDEN):
            raise Rejected(f"forbidden node: {type(node).__name__}")
 
    cte_names = {c.alias_or_name for c in tree.find_all(exp.CTE)}
    for table in tree.find_all(exp.Table):
        name = table.sql(dialect=dialect, identify=False)
        if name in cte_names:
            continue
        if name not in ALLOWED_TABLES:
            raise Rejected(f"table not in allowlist: {name}")
 
    limit = tree.args.get("limit")
    if limit is None or int(limit.expression.this) > MAX_ROWS:
        tree = tree.limit(MAX_ROWS)
 
    return tree.sql(dialect=dialect)

The alternative you see everywhere is a regex denylist over INSERT|UPDATE|DROP and friends, and it is trivially defeated. Comments split keywords, CTEs hide the shape of the statement, and string literals trip naive matchers while genuinely destructive statements slip past differently naive ones. Walking the AST and allowlisting is the only version I'd defend in a review.

The prompt is not a security boundary

Even a good validator is a piece of application code that someone will eventually bypass, so the real enforcement belongs in the database.

-- roles.sql
CREATE ROLE llm_agent LOGIN PASSWORD :'agent_password';
 
ALTER ROLE llm_agent SET default_transaction_read_only = on;
ALTER ROLE llm_agent SET statement_timeout = '10s';
ALTER ROLE llm_agent SET idle_in_transaction_session_timeout = '15s';
ALTER ROLE llm_agent SET lock_timeout = '2s';
ALTER ROLE llm_agent CONNECTION LIMIT 8;
 
REVOKE ALL ON SCHEMA public FROM llm_agent;
GRANT USAGE ON SCHEMA analytics TO llm_agent;
 
-- An explicit view list, never a blanket GRANT on the schema.
GRANT SELECT ON analytics.v_orders   TO llm_agent;
GRANT SELECT ON analytics.v_customer TO llm_agent;
GRANT SELECT ON analytics.v_product  TO llm_agent;
 
-- With the role configured this way, an INSERT emitted by the model fails
-- with SQLSTATE 25006, "cannot execute INSERT in a read-only transaction",
-- no matter how the request was phrased or what the system prompt said.
 
-- Cost gate: run this on the same connection before the real query and
-- reject when estimated rows or total cost exceed budget. Catches the
-- accidental cross join before it executes rather than after.
EXPLAIN (FORMAT JSON)
SELECT c.region, sum(o.amount)
FROM analytics.v_orders o
JOIN analytics.v_customer c ON c.customer_id = o.customer_id
GROUP BY 1;

Point the agent at a read replica rather than the primary, cap rows on the fetch path, and size the pool so a runaway agent can't exhaust it, and the worst outcome of a bad generation becomes a wasted ten seconds. The EXPLAIN gate has caught more genuine mistakes for me than any prompt instruction, because a missing join predicate shows up as an absurd row estimate long before it shows up as a wrong answer.

On reference implementations: LangChain now flags AgentExecutor as legacy, so create_sql_agent is not the thing to build a 2026 system on, whatever the tutorials still say.

The ambiguity classes that produce confidently wrong SQL

This is the part I underestimated. Most wrong answers weren't failures of SQL generation. They were correct SQL for a different question than the one asked.

Metric definitions come first. "Active user" and "revenue" are not English words in a warehouse, they are decisions someone made, and the model will invent a plausible version of that decision. Grain and deduplication come second and are the most dangerous. COUNT(*) against COUNT(DISTINCT ...) diverges quietly, and a join that fans out multiplies a SUM without any signal that anything happened. That single pattern accounts for more silent wrong answers in analytics than everything else combined.

Then time boundaries, where inclusive versus exclusive endpoints, timezone handling and fiscal versus calendar periods all produce answers that look right. Then soft-delete and status filters the model has no way to know exist, because nothing in the schema announces that orders holds rows everyone already knows to exclude. Then currency and unit mismatches. Then NULL semantics, whose sharpest edge is NOT IN against a subquery containing a NULL, which returns zero rows rather than the answer you expected.

That list is mine, assembled from wrong answers I had to explain after the fact. The nearest published taxonomy is AMBROSIA (NeurIPS 2024 Datasets and Benchmarks, arXiv 2406.19073), which I have only secondhand: it is reported to formalise three ambiguity types, scope, attachment and vagueness, and to find that the ambiguity persists even when the database is provided, with Ambrosia+ adding 2,535 unanswerable questions across 6,777 examples in 16 domains. If that second finding holds it is the important one, because it means this is not something you fix by pasting more schema into context. BIRD-Interact, an ICLR 2026 oral, benchmarks clarifying-question behaviour itself, which suggests the field now treats asking as a first-class action.

Which means the output type has to admit it.

# contract.py
from dataclasses import dataclass
from typing import Literal
 
Outcome = Literal["answer", "clarification_request", "refusal"]
 
 
@dataclass(frozen=True)
class Answer:
    kind: Outcome = "answer"
    sql: str = ""
    rows: list[dict] = None
    row_count: int = 0
    checks_passed: list[str] = None
 
 
@dataclass(frozen=True)
class ClarificationRequest:
    kind: Outcome = "clarification_request"
    question: str = ""           # "revenue gross or net of refunds?"
    ambiguity_class: str = ""    # metric | grain | time | filter | unit | null
    candidates: list[str] = None # the readings the agent found plausible
 
 
@dataclass(frozen=True)
class Refusal:
    kind: Outcome = "refusal"
    reason: str = ""             # no schema path, denied table, budget exceeded
    attempted_sql: str | None = None
 
 
Result = Answer | ClarificationRequest | Refusal

Declaring the union is the easy part. The discipline is making sure the second and third arms are reachable often enough to be real, which means tracking their rate. An agent that has never returned a refusal in a month of live traffic is not well calibrated, it is an agent whose refusal path is dead code.

Why document stores are harder

I expected MongoDB to be roughly text-to-SQL with different syntax. It is harder for four reasons that don't overlap.

There is no schema to link to. Schema linking is the single most productive technique in text-to-SQL, and in a document store it has no input, so you induce a schema by sampling documents and hope the sample was representative. Polymorphism makes that worse: the same field is a scalar in one document, an array in another, absent in a third, and any induced schema flattens that away. Denormalisation destroys the join signal, because the foreign key graph is what tells you how entities relate, and in a document store the relationship is implicit in nesting.

The fourth reason is the one I find most interesting. Aggregation pipelines are programs, not declarations. Stage ordering changes semantics, and no planner will normalise a mistake the way a SQL optimiser quietly rescues a badly ordered set of predicates. Two specific behaviours produce silent wrongness on their own: $unwind on a missing field drops documents without comment, and {status: {$ne: "cancelled"}} matches documents where status doesn't exist at all. Both of those return a smaller, clean-looking result set and no error.

TEND (arXiv 2502.11201) is the benchmark for this, with 1,210 MongoDB-native tasks across 11 databases. Its citable finding is that models with strong NL2SQL performance degrade substantially on it. There is no per-model accuracy figure to quote, only that direction, and the direction matches what I saw well enough to distrust anyone reporting document-store agent quality by analogy to their SQL numbers.

Checks that cost almost nothing

A handful of plausibility assertions catch a surprising share of the silent cases.

# plausibility.py
from dataclasses import dataclass
 
 
@dataclass
class Suspicion:
    check: str
    detail: str
 
 
def screen(rows, table_row_count, expected, controls) -> list[Suspicion]:
    """Cheap post-execution screening. None of these prove correctness;
    each one flags a shape that is usually a bug rather than a finding."""
    out: list[Suspicion] = []
    n = len(rows)
 
    if n == 0:
        out.append(Suspicion("empty", "zero rows: verify filters, not an answer"))
 
    if n == table_row_count:
        out.append(Suspicion("unfiltered", f"row count equals full table ({n})"))
 
    for row in rows:
        for col, val in row.items():
            if col.endswith(("_pct", "_rate", "_share")) and val is not None:
                if not 0 <= float(val) <= 100:
                    out.append(Suspicion("range", f"{col}={val} outside [0,100]"))
 
    for col, total in controls.items():
        observed = sum(r[col] for r in rows if r.get(col) is not None)
        if observed > total * 1.001:
            out.append(
                Suspicion("control_total", f"sum({col})={observed} exceeds {total}")
            )
 
    if expected.get("shape") == "scalar" and n > 1:
        out.append(Suspicion("cardinality", f"{n} rows for a scalar question"))
 
    return out

The strongest check isn't in that function, because it costs a second query: generate two structurally different queries for the same question and compare results. When they disagree you have found a real ambiguity, and the right output is a clarification request rather than a coin flip.

Semantic layers, and the caveat that beats the win

The dbt Developer Blog published a piece titled "Semantic Layer vs. Text-to-SQL: 2026 Benchmark Update", run on the data.world ACME Insurance benchmark with a 15-table schema. Claude Sonnet 4.6 went from 90.0% to 98.2% with a semantic layer in front, and GPT-5.3-Codex from 84.1% to 100.0%.

I find their caveats more valuable than their headline. The benchmark is 11 questions run 20 times each, for 220 queries total, and n=11 is a very small question set for an architectural conclusion. They loaded the entire schema as context and concede this "isn't practical for larger datasets", which quietly removes one of the harder parts of the real problem. Some questions required three additional dbt models before MetricFlow could answer them at all, so part of what improved was the data model rather than the query interface. And more reasoning effort bought latency without buying accuracy.

The line I'd anchor on isn't the accuracy delta at all. It is this one: "Text-to-SQL returns plausible but incorrect answers; the Semantic Layer returns errors when unable to answer." A system that fails loudly belongs to a different category from one that fails quietly, and that categorical difference is the real argument for a semantic layer.

The counterargument deserves airing. MotherDuck has argued that BIRD's difficulty is largely an artifact of badly modelled schemas, which implies semantic-layer gains partly measure data-modelling effort rather than architecture. I think that's substantially right, and it relocates the case rather than weakening it: if the fix is modelling work, the semantic layer is where that work becomes durable.

What I'd keep

Rebuilding this tomorrow, the order would be a read-only role with an explicit view allowlist and timeouts, AST validation with an allowlist rather than a denylist, an EXPLAIN budget gate, empty results routed to suspicion rather than to the user, a three-armed output type with a monitored refusal rate, and an ambiguity classifier in front of generation.

Model choice moves things less than any of that. The benchmark numbers, once you account for the annotation noise inside them, move things less still.