ai-analyst-workflow — can an LLM write correct SQL for a real analyst question?

HDB resale flat prices via data.gov.sg · 241,920 rows, 2017-01 → 2026-10 · Six-on-SG · 7 of 7

27/32 first try · 0 after one retry · 5 fail — all value_mismatch. On 2026-10-04 a free-tier LLM wrote SQL for 32 hand-written analyst questions. The part worth reading is the failures: all 5 were value-level wrong answers that looked right (correct row counts, wrong values), retries rescued none of them, and half the deliberate traps caught the model. Every wrong answer was caught by the result fingerprint (a hash of the actual rows returned), not by reading the SQL. In one line: I would trust it as a checker for single-table analyst SQL — not the model's SQL unverified, and not these numbers for multi-table work or model ranking.

27/32 · 0 · 5

first try · after one retry · fail — every failure is value_mismatch, row counts right on all 32; no sql_error, no guardrail_reject, no timeout. The retry saw the failed SQL plus an error class, never the expected numbers

3 of 6 traps

deliberate traps caught the model — Q13 grouped on non-unique block (196 instead of 295), Q15 ROWS 2 PRECEDING over a series with 14 missing months, Q24 the ambiguous "last year"; the other three traps were answered right first try

18/18 clean

clean question classes all passed — filter 4/4, aggregation 5/5, ratio 4/4, distinct 2/2, date 3/3; where it breaks: window 2/5, ambiguous 0/1, grouping 7/8

32/32 · 0 discrepancies

golden fingerprints reproduce from the DuckDB build (241,920 rows, 9/9 checks); 15 verdicts (14 passes + 1 failure) re-derived from the raw CSV with stdlib Decimal — no DuckDB, no repo code

Bar chart of pass rate by question type: filter 4 of 4, aggregation 5 of 5, ratio 4 of 4, grouping 7 of 8, window 2 of 5, distinct 2 of 2, date 3 of 3, ambiguous 0 of 1; trap questions passed 3 of 6 versus 24 of 26 for everything else

Pass rate by question type (left) and deliberate traps versus everything else (right). A pass is a byte-exact result fingerprint, not a human review. As-of run 2026-10-04.

The failure view

Failure-type breakdown: 5 value_mismatch, 0 wrong_row_count, 0 sql_error, 0 guardrail_reject, 0 timeout; verdict mix 27 first try, 0 after retry, 5 fail

Every failure in this run is a value-level mismatch: the model returned the right number of rows and the wrong values. Nothing failed on syntax or on the safety guardrails. The taxonomy is the contract even where a class is empty — a future run that starts producing guardrail_rejects means the prompt or schema changed. Full write-up in the failure catalogue.

What this cannot say

Not a benchmark, not a model ranking. 32 questions on one table from one prompt template is a validation demo; the as-of run spans three closely related free-tier model ids because of a daily quota wall (model note). Treat the aggregate as a workflow receipt, not a model measurement.
Not a claim that 27/32 is "good". The set is small and hand-written; the five failures are concentrated exactly where unverified SQL is most dangerous (windows, ambiguity). A different 32 questions would give a different number — the number is dated, the failure classes are the durable part.
Not proof the guardrails are sufficient. They reject the obvious escapes and are tested for those; the row cap and watchdog bound damage, they do not eliminate it.
Not a statement about multi-table work. One table on purpose: joins, schema evolution, dialect portability and explanation quality are unmeasured.
Not causal or predictive. The underlying data is transaction records; nothing here prices, forecasts or attributes anything.

Limits. The golden set's readings of deliberately ambiguous questions are pinned choices, not ground truth (Q24's mismatch is partly the question's fault, labelled as such). Two failures are contract slips the questions could have prevented (rounding order in Q14; year label type in Q17) — recorded as failures because the contract is the contract. Generation is a receipt of 2026-10-04: re-runs will differ; only the deterministic half (DB build, fingerprints, validation of the committed candidates) is byte-reproducible.

Method

Golden set — 32 hand-written questions in analyst phrasing across 8 question types, including 6 deliberate traps (a question whose obvious SQL is subtly wrong); canonical SQL with a deterministic ORDER BY where the result has multiple rows, plus a result fingerprint. eval/golden_set.yaml
Fingerprint — one canonical result formatter shared by the golden set and the comparison: every cell to 2 dp, rows joined in order, sha256 over the ordered result. Row count + hash, nothing else. src/fingerprint.py
Generate — one prompt template = question text + schema block, no rows; ≤2 attempts per question, attempt 2 sees attempt 1's SQL and an error class, never expected numbers. Re-runs land in outputs/reruns/<date>/ and never overwrite the committed receipt. src/generate.py
Guardrails — every candidate executes under a trust boundary: parse-based single-SELECT check, table allowlist, no filesystem/catalog functions, read-only connection, 15 s watchdog, 10,000-row cap. src/guardrails.py
Validate + catalogue + audit — golden fingerprints re-derived from the live database first (a moved input fails loudly), then every committed candidate replayed; every failure classified with a next-iteration fix; 15 verdicts (14 passes + 1 failure) re-derived by hand from the raw CSV. src/validate.py · audit

Reproduce

git clone https://github.com/faizsaifulnizam/ai-analyst-workflow && cd ai-analyst-workflow
uv venv .venv --python 3.12 && source .venv/bin/activate
uv pip install -r requirements.txt

python src/download.py      # raw CSV → data/raw/ (gitignored)
python src/build_db.py      # 241,920 rows → local DuckDB
python src/generate.py      # needs GOOGLE_API_KEY; skips gracefully without it
python src/validate.py      # replays the committed candidates
python src/figures.py
python tests/smoke_test.py

Spot-check: outputs/results.csv must show Q01 pass_first_try (Tampines 2023 count 1,634), Q05 pass_first_try (2023 4-room median S$550,000), and Q13 fail / value_mismatch. If those three rows read differently, you are not looking at the committed receipt. Full commands (incl. Windows) in the README.

The repository — the README carries the full method, rules-and-why table, validation receipts and caveats
Failure catalogue — every failure classified, with a concrete example and a next-iteration fix
Independent audit · Data audit · Decision memo
Model note — the dated LLM receipt and the free-tier quota wall · What never leaves the machine

Data: HDB resale flat prices, Jan 2017 → Oct 2026 (data.gov.sg d_8b84c4ee58e3cfc0ece0d773c8ca6abc, © Housing & Development Board, Singapore Open Data Licence). Pulled 2026-10-04 (16:28:47 +08:00): 241,920 rows, 23,928,760 bytes, 2017-01 → 2026-10, sha256 9835dfe6…db9a (committed pull_manifest.json). The LLM generation receipt is dated 2026-10-04 (three free-tier Gemini model ids, per-question model recorded).

The failure view

What this cannot say

Method

Reproduce

Read more