Chasing the last few points: what 50+ BIRD runs taught me about NL-to-SQL accuracy

I spent the summer pushing a text-to-SQL pipeline from 63% to about 72% execution accuracy with open-weight models (Gemma 4 31B and Qwen3). Most of what moved the number wasn't what I expected, and most of what I expected to work didn't.

The system is a modular natural-language-to-SQL backend. You point it at a database, it ingests and profiles the schema, and at question time it retrieves the relevant tables and columns, builds a prompt, and asks an LLM for SQL. I evaluated it on BIRD, mostly on the 500-question mini-dev split (148 simple, 250 moderate, 102 challenging questions across 11 databases), using the official execution-accuracy (EX) metric: the predicted SQL counts as correct only if it returns the same result set as the gold SQL.

All the accuracy numbers here come from open-weight models: Gemma 4 31B and Qwen3, with no frontier API models in the loop. That constraint turned out to be useful: a model that can't brute-force its way past a bad prompt shows you exactly where the pipeline is weak.

Below is the pipeline, how the number moved, and the eight findings I'd want someone to tell me before I started.

THE PIPELINETen stages from question to SQL

Ingestion runs once per database: it reads the schema, profiles each column (row count, distinct count, min/max, nulls, a few sample values), embeds column descriptions into pgvector, indexes them for Postgres full-text search, builds an LSH index over sample values, and writes tables, columns and foreign keys into a Kuzu graph.

At question time:

1. Extract literal values, intent tags and the expected output shape from the question.
2. Expand the question into paraphrases to widen recall.
3. Hybrid search across four layers: LSH value matching (does 'APS' actually appear in some column?), pgvector semantic search, Postgres full-text search, and pairwise shortest paths over the foreign-key graph in Kuzu to find join routes, including bridge tables.
4. Fuse lexical and semantic results with Reciprocal Rank Fusion.
5. Draft + verify: a fast draft SQL on the wide context, then a deterministic check battery: does it bind, execute, use grounded columns, match the literal values, return the expected shape?
6. Rerank every fragment with a cross-encoder, keeping at least one column per candidate table.
7. Assemble the context: join anchors first, then the relevance-ranked tail up to a column budget.
8. Generate the SQL.
9. Gate: if draft and generation disagree, score both with the same deterministic battery and keep the one with the lower penalty.
10. Critic: a conservative last check that can trigger one regeneration.

THE CURVEHow the number actually moved

Here are the milestones on the 500-question set. Where I have several runs of the same config, the point is the mean.

About 8 points in two months. Notice what's not on that list: no new model, no fine-tuning, no ensemble. The ensemble, the LLM judge and the multi-agent setup were in the June baseline and were removed on the way up.

FINDING 01Measure your noise floor before you measure anything else

This was the single most important thing I learned, and I learned it late. I ran the identical config, same commit, same settings, twice. About 35 of 500 questions (~7%) gave different answers. Identical-config totals spanned 68.0 to 69.4. So any single-run difference under about 1.5 points is noise, and I had been reading tea leaves for weeks.

Three things followed from that:

• Small "regression subsets" lie in the flattering direction. My 50-question smoke set scored 100% on a change (SEARCH_TOP_K=5) that cost about a point on the full 500. The subset was built from questions good runs already pass, so it can only tell you "not catastrophically broken". A score below expectations is a real stop signal; a perfect score means almost nothing.
• "Consistent across two runs" doesn't filter noise either. With a 7% flip rate over 500 questions, some questions will flip the same way in both runs of each arm by chance. In one A/B, that filter reported 5 regressions. When I checked which questions the change could actually reach, only 1 of the 5 had ever touched the changed code path. The other 4 were noise that happened to line up.
• Blast-radius analysis beats totals. I added per-question telemetry recording which checks fired. Now "did this change help?" becomes "of the 11 questions where the new guard acted, how many flipped, and which way?" That answer (1 gain, 0 losses) was noise-free, when the total was not.

I ended up with a tiered validation flow, cheapest first. Any tier can stop the change:

One more boring rule that saved me: every run records its git commit, uncommitted diff, and the live config values that aren't in version control. Twice, an untracked .env change silently altered behaviour, once by swapping in a weaker reranker. Without the capture I'd have credited or blamed the wrong code change.

FINDING 02After a point, retrieval isn't the bottleneck

The instinct with RAG-style systems is that a wrong answer means you didn't retrieve the right thing. Early on that was partly true. In one traced sample of 25 failures, I followed each required gold column through every stage to see where it was first dropped:

• 3 were never retrieved by search
• 2 were dropped at RRF fusion, 1 at the reranker
• 19 had every needed column in the prompt, and the model still wrote the wrong SQL

After fixing bridge tables and context truncation (below), gold-column recall in the final context reached 100% on my precheck. More retrieval work couldn't move the number. The remaining errors were reasoning errors: an extra WHERE clause, SUM where gold uses AVG, COUNT(DISTINCT x) where gold uses COUNT(*).

That doesn't mean retrieval choices stopped mattering. Two retrieval ablations were still clearly negative:

• Narrow search hurts. Cutting candidates per search layer from 40 to 5 lost about a point, mostly on multi-table challenging questions. Recall before reranking still matters.
• RRF is not a reranker. Dropping the cross-encoder and relying on rank fusion alone failed immediately. On a Formula 1 question it picked the decoy results table over driverStandings. When lexical and semantic search share a blind spot, fusing them keeps it.

FINDING 03More context makes a mid-size model worse

Injecting full table schemas into the generation prompt collapsed accuracy from 69.8% to 64.4%. The model latched onto similar-looking columns and added filters and joins nobody asked for. The two biggest failure categories in my June analysis were extra WHERE conditions and unnecessary JOINs.

What worked instead:

• Compact column profiles. I replaced prose like "Contains 10 distinct values with no missing values…" with distinct=10 | range=[2010, 2020] | samples=['2012','2013']. The schema block got about 3× smaller.
• "Expansion-lite" context. Only the ranked columns, plus primary/foreign-key anchors so joins still work. Same accuracy as full-table expansion with 16% less context.
• Anchors at the head of the budget. A column cap was silently truncating join keys that had been appended at the tail. Moving anchors to the front fixed two join failures outright.

FINDING 04The graph earns its place on joins

The biggest single class of early failures was a missing bridge table. Take member → attendance → event. The question mentions members and events, so search finds both. But attendance has nothing descriptive to match, so it never shows up, and the model can't write the join.

Keeping the FK structure in a graph database made the fix natural. I take the tables that retrieval is confident about and ask Kuzu for the top-N shortest paths between each pair (up to 3 hops). Intermediate tables and their join keys go into the context automatically. That replaced an earlier BFS expansion outright: the pairwise paths already contain the bridges, and the BFS mostly added noise.

A related fix was starvation prevention in the reranker. With a flat top-k, one table with ten matching columns could push every other table out of the prompt entirely. Now each retrieved table keeps at least its best column.

FINDING 05Prompts are code, with code's bugs

Three of my biggest jumps came from prompt bugs, not prompt ideas:

Contradictory rules: the model follows the permissive one

One guideline said "use COUNT(DISTINCT) when duplicates are expected". I later added a second rule forbidding unnecessary DISTINCT. The failures didn't go away, because the first rule licensed exactly what the second forbade. Merging them into one unambiguous rule fixed the target questions and produced the first run above 70%. My rule now: before adding a guideline, read the whole file; never keep two rules covering one decision.

A guideline file loaded twice

A refactor had one guidelines file injected into both the system prompt and the user prompt. I restructured the prompt into three layers, each loaded exactly once: a style shell (role, reasoning structure, output contract), dialect-agnostic guidelines, and dialect-specific rules. That was worth +1.4 EX (70.0 → 71.4 over four runs).

A dead alias confounded weeks of A/B tests

Two prompt "styles" I had been comparing mapped to the same file. Every historical comparison between them was measuring noise. Only a single-variable A/B with per-question telemetry exposed it.

The broader principle I landed on: code checks stay structural; semantic judgment lives in the prompt. Code can verify that SQL binds, executes, uses real columns and returns the expected number of columns. Code should not decide "was DISTINCT asked for?" by regex over the question. I tried that with dataset-specific "trap" regexes; they overfit and piled up. Replacing them with checklist rules in the prompt fixed the same target questions without the maintenance cost.

FINDING 06Self-correction breaks correct answers

In the June baseline, the best single agent scored 65.0% but the final output scored 63.4%. The critic-and-heal step was turning correct SQL into wrong SQL. I estimated about 19 questions harmed per 500.

• Healing is now syntax-only. It runs only when EXPLAIN QUERY PLAN fails. Valid SQL is never "improved".
• A hard "intent mismatch" trigger fired on 38% of questions, and 86% of the regenerations it caused were futile. Demoting it to a soft warning in the prompt cost nothing and removed a lot of churn.
• The draft-verify stage paid for itself in cost, not accuracy. It was accuracy-neutral, but a verified draft can short-circuit generation. That cut generation calls by about 50% and futile regenerations from 86% to 21%.

One bug here is worth describing because it's so easy to write. The value checker decided "this column doesn't contain 'APS'" by looking only at the top-5 sampled LSH matches. On a high-cardinality column (220 distinct values, 18 real 'APS' rows) the sample missed it, flagged the correct filter as a critical mismatch, and the gate shipped the wrong query. The fix is one database lookup to confirm absence before flagging. The general lesson: a sample can prove presence, never absence.

FINDING 07Ensembles, judges and multi-agent didn't pay

This was the most expensive lesson because it's the most appealing idea. The evidence stacked up from several directions:

• The two agents agreed 97.8% of the time. In June, a "divide and conquer" agent and a "query plan" agent produced identical SQL on 489 of 500 questions. The LLM judge picked agent A 89.6% of the time, even though B was better on challenging questions.
• Multi-agent lost on average: 68.2% vs 69.3% for single-agent across three runs each, with higher variance.
• The oracle ceiling was tiny. Comparing two prompt styles on identical code: both right on 341, only one right on 27, both wrong on 132. A perfect judge could reach 73.6% against 72.0% for the better style alone. No judge is perfect, so no ensemble can pay for twice the cost.
• Selectors were near chance. Across 10 logged runs, even a perfect per-run choice between the draft and the generated SQL would reach only 73.7%. On the questions where the choice mattered, every selector I tried, an LLM judge included, was barely better than a coin flip. A simple deterministic penalty score beat the LLM judge.
• Majority voting over ~20 candidates scored exactly the current EX.

If I could give one piece of advice here: compute the oracle ceiling before building an ensemble. Once you have two arms logged, it costs nothing to calculate.

FINDING 08The benchmark's gold SQL has opinions

22 failures were "arity" errors: the prediction returned a different number of columns than gold. That looked like a prompt problem. My guidelines only warned against projecting too many columns, and most of these errors projected too few. So I rewrote the guideline to be symmetric and to name the compound-question pattern explicitly. I verified the new text was actually in the assembled prompt. It fixed 0 of 22.

When I looked closer, most of the class wasn't model error:

• 45% were gold queries projecting extra columns, sometimes an unrequested primary key or the ordering metric, that my own guidelines (reasonably) forbid
• 18% were gold splitting a person's name into first and last name, a dataset convention
• 14% were gold emitting a RANK() column no question asks for

The remainder had a telling signature. On compound questions ("who is X and how much did they spend?") the model returns exactly one of the asks, and which one survives looks arbitrary. That's a capability limit, not an instruction-following gap. Lesson: some of the errors on a leaderboard are convention mismatches, and no self-consistent rule will recover them. Know which of your errors those are before spending a week on them.

A related trap: on the full 1,534-question BIRD dev set the pipeline scores 70.9%, a bit under mini-dev. mini-dev is a cleaned subset of dev, and I tuned on it. When I split one improvement by subset membership, all of the gain was inside the 500 I'd tuned on; the held-out 1,034 were flat to slightly negative. Always check your wins on data you didn't tune against.

WHERE IT STANDSThe plateau is real

With open-weight models in the 30B range, the best configurations sit in a roughly 71–72.6% band, and high single runs are the top of the noise, not the centre. Retrieval is saturated and selection is exhausted, so the remaining levers are on the generation side: a stronger model with prompts tuned to its own failure modes (guidelines tuned against one model's mistakes don't transfer for free), or a real change in how generation reasons about compound questions.

IN HINDSIGHTWhat I'd do differently

• Measure the noise floor on day one. Two identical full runs before any experiment. I'd have skipped weeks of chasing 0.6-point "wins".
• Add per-question telemetry before it's needed. Which checks fired, which candidate the gate kept, why. It turns "did this help?" from a statistics problem into a lookup.
• Log tried-and-removed ideas with the measurement that killed them. Cheap to write, and it stopped me rebuilding the same bad idea more than once.
• Check oracle ceilings and gold-column recall first. Both are free once runs are logged. Both would have told me early that selection and retrieval were saturated, and that the 19% "never correct" set is where to dig.
• One EX-relevant change per validation cycle. Batched changes felt faster and made every result unattributable.

Most of the gains came from removing things: a judge, a second agent, a duplicated prompt, a contradictory rule, a critic that was too eager, context the model didn't need. At this stage the useful work was less about adding capability and more about subtracting sources of confusion, and measuring well enough to tell the difference.