Yardstick: when is LLM-generated SQL safe to trust?

01Setup and verification

Everything runs on free infrastructure: two Llama models on Groq for generation, Postgres for results, SQLite for the target databases. Before any experiment code ran, a verification script confirmed every dependency end to end.

$ python scripts/verify_setup.py

Yardstick. Phase 0 verification

  ✓ PASS env  (.env loaded; set: GROQ_API_KEY, DATABASE_URL)
  ✓ PASS supabase-postgres  (connected; all 5 tables present)
  ✓ PASS groq-api  (llama-3.1-8b-instant ✓  llama-3.3-70b-versatile ✓)
  ✓ PASS sqlite-readonly  (opened mode=ro, read 3 rows, writes blocked)

Phase 0 exit criteria MET ✓  (DB, both providers, read-only SQLite all verified)

Captured output. Generated SQL is untrusted, so every database is opened read-only and the check asserts that writes are actually rejected.

02Building the sample

Difficulty tiers come from Spider's own eval_hardness() function rather than from my judgement, so the tier boundaries cannot be accused of being drawn to fit the conclusion. Every gold query was executed before its question entered the sample.

Spider train and dev examples8,034

Labelled into three tiers by the official hardness function8,034

Seeded sample, capped at 3 questions per database159

Excluded, gold query returned zero rows-7

Excluded, broken gold SQL found by hand review-2

Analysed, 50 per tier across 93 databases150

The two hand-review exclusions shared a defect: a join condition comparing a column to itself, which silently produces a cartesian product, plus a select list returning last names where the question asked for first names. Benchmarks contain broken ground truth. Including it would have measured that rather than the models.

03Result: better models fail more quietly

A silent failure is a generated query that executed without error and returned the wrong rows. Ordered by accuracy, both large-model variants sit above both small-model variants on this measure.

Bars showing the share of each variant's errors that were silent: V2 81 percent, V1 89 percent, V4 95 percent, V3 100 percent. — **PLATE 1** Share of each variant's errors that were silent. n = 150 per variant. Colour encodes model size, not severity.

Broken out by question difficulty rather than by variant, 82% of errors on complex questions were silent, 95% on simple, and 97% on the middle tier. The strongest model also reported 95% average confidence on the answers it got wrong, so nothing in its own output flags the problem.

Exhibit: one real failure

QuestionWhich job titles correspond to jobs with salaries over 9000?

Gold

SELECT job_title
FROM jobs
WHERE min_salary > 9000

3 rows

Generated, 70B zero-shot

SELECT JOB_TITLE
FROM jobs
WHERE MAX_SALARY > 9000

10 rows, self-reported confidence 1.00

Valid SQL. Runs in milliseconds against the right table and returns real job titles. It answers a different question: jobs whose ceiling is above 9000 rather than jobs whose floor is. One word, no error, maximum stated confidence.

04Result: prompting did nothing, model size did

Forest plot. All three primary prompt-effect intervals cross zero. The model effect on complex queries is plus 28 points and significant. — **PLATE 2** Accuracy differences in percentage points, 95% bootstrap intervals. Upper block is the pre-registered primary comparison, Benjamini Hochberg corrected. Lower block is the exploratory model effect, uncorrected.

Every prompt-effect interval crosses zero. A ten-question pilot had suggested few-shot nearly doubled accuracy on complex queries. At the full sample it collapsed to a non-significant eight points. The pre-registration is the reason this appears as a null result rather than a discovery.

$ python scripts/analyze_primary.py

  complex (n=50 paired)
    V1 acc=0.600  V2 acc=0.680  diff=+8.0 pts  95% CI [-6.0, +22.0]
    discordant: V1-only=5  V2-only=9  (both correct=25, both wrong=11)
    McNemar exact p = 0.4240

Benjamini-Hochberg FDR across the primary comparisons (α=0.05):
  simple    raw p=1.0000  adj p=1.0000  not significant
  moderate  raw p=1.0000  adj p=1.0000  not significant
  complex   raw p=0.4240  adj p=1.0000  not significant

Captured output. The primary hypothesis was that few-shot lift would differ across tiers. There is no lift anywhere to differ.

Execution accuracy by variant and tier, silent-failure rate in brackets
Tier	V1 8B zero	V2 8B few	V3 70B zero	V4 70B few
simple	0.88 [.12]	0.86 [.14]	0.96 [.04]	0.92 [.06]
moderate	0.82 [.18]	0.82 [.16]	0.82 [.18]	0.84 [.16]
complex	0.60 [.32]	0.68 [.22]	0.88 [.12]	0.84 [.16]

The 8B to 70B upgrade gave +28 points on complex queries (95% CI [+14, +42], p = 0.0013) zero-shot and +16 points (p = 0.039) few-shot. Those are the only significant effects in the study. On the moderate tier the same upgrade gave exactly zero: every variant landed between 0.82 and 0.84, which suggests whatever limits accuracy there is not something this design manipulates.

Grouped bars of error types. Wrong joins fall from 33 with the small model to 13 with the large one. — **PLATE 3** What the upgrade actually fixes. Wrong joins fall from 33 to 13, invented tables or columns from 10 to 1. Every category except schema error is a silent failure mode.

05Result: confidence is useless, agreement is not

Dumbbell chart of stated confidence against measured accuracy. Gaps run from 22 points down to 6, all overconfident. — **PLATE 4** Stated confidence against measured accuracy, sorted by gap. Every variant is overconfident. The gap narrows as models improve but never closes.

The weakest variant reported 98.6% average confidence while being correct 76.7% of the time. All four stay between 87% and 96% confident on the answers they get wrong, so confidence carries almost no information about whether any specific query is right.

Predicting correctness, area under the ROC curve
Signal	AUC	Flags	Errors caught	False alarms
Cross-model result agreement	0.861	28% (168/600)	84% (87/104)	16% (81/496)
Query structure agreement	0.710	74%	100%	69%
Self-reported confidence	0.647	n/a	n/a	n/a

None of these signals requires the correct answer, so all three are available in production. Reviewing only the 28% of queries (168 of 600) where four model setups (two models, two prompts each) disagree on the executed result catches 84% of all errors (87 of 104), at a 16% false-alarm rate (81 of 496 correct queries flagged). The price is running every question through all four setups.

06Result: difficulty routing did not beat random

Cost against accuracy for six routing policies. Difficulty routing and random both reach 78.3 percent. — **PLATE 5** Six routing policies on the held-out test split. Difficulty routing is cheaper than random but no more accurate. The oracle is both cheaper and more accurate than always using the expensive model.

The predictor uses only features computable from the question text and the schema, before any SQL is generated. A single feature, question length, scored AUC 0.724 and beat the six-feature model at 0.665. Schema size was noise at 0.507. Holding out an entire tier dropped complex-tier prediction to chance. Holding out entire databases, which is the production question, held at 0.670 across 93 folds.

Two findings here matter more than the routing number. The oracle reached 0.900 accuracy at 37% of the always-expensive cost, beating always-expensive on both axes, so real headroom exists and these features could not reach it. And cost and difficulty are driven by different things: question length predicts difficulty but not cost, while schema size predicts cost almost perfectly (r = 0.95) and difficulty not at all. Input tokens are 95% of spend because the schema is pasted into every prompt.

08Validation and constraints

The scorer was cross-validated against the official Spider evaluation script. The one disagreement is a spurious match, two structurally different queries that both happened to return 12, which is the known limitation of single-instance execution accuracy rather than a defect in the comparison logic.

$ python scripts/crosscheck_official.py --variant V1 --limit 30

Cross-check of 30 V1 predictions vs official eval_exec_match:

  scored by both : 19
  AGREE          : 18/19  (95%)
  disagree       : 1
  unscorable by official (parser/schema limits): 11

Captured output. The official parser could not score 11 of the 30 predictions at all. The executable scorer handled every one.

The experiment ran entirely on free-tier infrastructure, which imposed a real constraint. The 70B model carries a hard daily token ceiling, and the run hit it:

$ python scripts/run_experiment.py --variant V4 --tier complex

Error code: 429 {'error': {'message': 'Rate limit reached for model
`llama-3.3-70b-versatile` in organization `org_01kv...` service tier
`on_demand` on tokens per day (TPD): Limit 100000, Used 99060,
Requested 1048. Please try again in 12m16s.'}}

Generated 165, skipped(cached) 19, failed 16.

Captured output. Generation writes to Postgres after every call and the resume logic treats a failed call as pending rather than done, so the run continued the next day and completed all 600 cells at zero re-cost.

Limitations

Benchmark contamination: Spider is public and predates these models' training cutoffs, so absolute accuracies are probably optimistic. All four variants are affected equally, which makes the comparisons more trustworthy than the raw numbers.
Underpowered for small effects: Minimum detectable effect at 50 questions per tier is roughly 14 to 27 points. A real but moderate few-shot benefit would not have been detected.
Complexity is tangled with length and schema size: Harder tiers came from larger databases and longer questions, so the complexity effect cannot be fully separated from a context-length effect.
The moderate tier is uninformative: All four variants landed between 0.82 and 0.84, so neither factor in this design moves it.
One model family, list-price costs: Both models are Llama variants from one provider, so the model effect means size within a family rather than a comparison between vendors.

Design	2 × 2 factorial (prompt strategy × model size), three complexity tiers
Corpus	Spider, 150 questions across 93 databases, CC BY-SA 4.0
Endpoint	Execution accuracy, binary per question, set comparison against gold
Test	McNemar exact on paired outcomes, 10,000 resample bootstrap CI, Benjamini Hochberg
Stack	Python, SQLite, Postgres, statsmodels, scikit-learn, sqlglot, matplotlib
Cost	$0.00 actual, $0.19 at published list prices