Reliability · Text-to-SQL reliability
Natural-language analytics people will actually trust
The hard part is not generating SQL. It is knowing when not to run it.
You probably have this problem if
- It answers confidently and is sometimes quietly wrong
- Nobody can tell whether a wrong answer came from retrieval or generation
- The whole schema is pasted into the prompt
- Generated SQL runs directly against the database
- Users have started checking every number by hand, which defeats the point
What you end up with
Answers users trust, because every number traces back to a query they can inspect.
- Usually part of
- Agent Production Readiness Audit
- $4,000 – $6,000
- Phases
- 5 steps, detailed below with effort per step
- Domains I have done this in
- HR analyticsRecruitmentEnterprise reporting
Why this is the thing to fix
I built one of these over HR data: descriptive reporting, diagnostic questions, and predictive workforce analytics, serving 2,000 concurrent users across isolated tenants. It took reporting turnaround from two to three days down to under 30 seconds.
Generating plausible SQL is the easy part and current models are good at it. The engineering is everywhere else: retrieving the right slice of schema, refusing questions the data cannot answer, blocking queries that should never execute, and making the result auditable enough that a sceptical analyst stops double-checking it.
The single most important thing we shipped was showing the executed SQL behind a disclosure. It made stakeholders nervous and it is why the system earned trust — analysts checked it, found it correct, and stopped checking.
The query gate — what runs between generation and execution
Never send model output straight to a database. Each of these checks is cheap, and together they are the reason the system was allowed near real data.
- 1Parse the generated SQL into an AST — reject anything that does not parse rather than passing the string through
- 2Allowlist statement types: SELECT only. No DDL, no DML, no multi-statement, no comments carrying a second statement
- 3Verify every referenced table and column exists and is within the authorised set for this user
- 4Inject the tenant and row-level predicates yourself; never trust the model to have included them
- 5Bound the query: statement timeout, row cap, and rejection of unbounded cross joins
- 6Execute as a read-only role scoped to the tenant, so the credentials cannot do damage even if the gate is bypassed
- 7Cap and paginate results rather than raising the row limit; route genuinely large exports to an async job
- 8Return the executed SQL alongside the answer, available for inspection
- 9Log rejected queries with the reason — rejection rate by cause is your best quality signal
- 10Treat "I could not answer that from this data" as a valid, expected outcome rather than a failure to eliminate
The gate rejects some legitimate queries. That is an acceptable cost, and it is cheaper than one confidently wrong number reaching a board deck.
Where wrong answers actually come from
Teams debug generation prompts when the defect is usually upstream. Measure these stages separately or you will tune the wrong one — a mistake I made before learning to instrument the stages independently.
| Stage | Failure | How to measure it |
|---|---|---|
| Schema retrieval | The relevant table was never retrieved, so the model answered from the wrong one | recall@k against labelled question→table pairs |
| Column semantics | Column name looks right, means something else — `status` vs `employment_status` | Human-written column notes in the retrieved context; check they exist |
| Join logic | Correct tables, wrong relationship, plausible number | Labelled expected result sets, not expected SQL strings |
| Aggregation & filters | Wrong grain, or a filter silently dropped | Compare result values against known-good answers |
| Question scope | The data cannot answer it and the agent answered anyway | A refusal set: questions it must decline |
| Grounding | Answer text does not match what the query returned | Assert the narrative numbers equal the query output |
Match on result sets, not SQL text. Many different queries produce the same correct answer, and grading SQL strings punishes correct work.
How I actually do it
With effort per step, so you can judge whether to hire me or hand this to someone on your team.
- 11 day
Instrument the stages
Separate retrieval, generation, execution and narration so a wrong answer can be attributed to a stage instead of debated.
- 22–3 days
Fix schema retrieval first
Retrieve a relevant subset with human-written column semantics rather than pasting the whole schema. Measure recall@k against labelled pairs.
- 32–4 days
Build the query gate
AST parsing, allowlisting, predicate injection, bounds, and a read-only scoped role. The checklist above, implemented.
- 41–2 days
Add refusal and grounding
Decline what the data cannot answer, and assert that narrated numbers match the query output.
- 52 days
Measure accuracy on real questions
An eval set of real user questions with known-good result sets, including ones that must be refused.
What you keep
- Stage-separated instrumentation so wrong answers are attributable
- Schema retrieval with column semantics and measured recall@k
- A query gate: AST validation, allowlisting, predicate injection, bounded execution
- Refusal behaviour for out-of-scope questions, and grounding assertions on narration
- An accuracy eval set built from real questions, matched on result sets
- Executed SQL surfaced to users, which is what actually earns trust
Failure modes this prevents
From the failure taxonomy — each links to the causes and fixes in full.
high · very common
The answer is wrong because retrieval returned the wrong context
The model is behaving correctly given what it was handed — and what it was handed was wrong.
high · very common
It works on our test prompts and fails on real users
The team's test inputs are clean, well-formed, and written by people who know how the system works.
critical · very common
The agent calls the right tool with invented arguments
The model picks a correct tool but fabricates its inputs — a plausible-looking customer ID, an out-of-range date, an enum value that was never defined.
Questions
How accurate can a text-to-SQL system realistically be?
Accuracy depends far more on schema quality and question scope than on the model. A clean warehouse with documented column semantics and a bounded question domain performs well; a sprawling legacy schema with ambiguous column names performs badly with any model.
Which is why the useful metric is not a benchmark score but accuracy on your real questions, measured on result sets. Improve the schema documentation and retrieval before changing models.
Should we let generated SQL run directly against the database?
No. Parse it, allowlist SELECT-only, verify tables and columns against the authorised set, inject the tenant predicate yourself, bound it with a timeout and row cap, and execute as a read-only scoped role.
That is roughly two weeks of work and it is the reason a text-to-SQL system can be allowed near real business data at all.
Should we paste the whole schema into the prompt?
Only if it is small. Beyond a few dozen tables it wastes tokens on every call and measurably degrades table selection — more options, worse choices.
Retrieve a relevant subset per question, and include human-written notes on what columns actually mean. Those notes usually improve accuracy more than any prompt engineering, because the ambiguity is in the schema rather than the phrasing.
Should users see the generated SQL?
Yes. It is the single highest-trust feature you can ship, and it makes stakeholders nervous for about a week.
Analysts inspect it, find it correct, and stop inspecting. Without it, every number is unverifiable and cautious users keep checking by hand — which removes the entire benefit of the system.
Want this done, or just want a second opinion on it?
The method above is genuinely what I do — if your team can run it themselves, run it. If you would rather it were done in a week by someone who has done it before, that is what the agent production readiness audit is for.