Text-to-SQL's Real Risk Isn't the Query That Fails — It's the One That Silently Lies

Text-to-SQL is one of those demos that lands in the first thirty seconds. You type "how many orders did we ship last week," a model writes the SQL, a number comes back, and a room full of non-technical people realizes they never have to file a data request again. It feels like magic, and the standard write-up tells you the two things to worry about are accuracy and safety. Get the schema in front of the model for accuracy; run it read-only for safety. Ship it.

I want to push back on that framing, because after you actually build one of these, the risk model inverts. Safety is the easy problem — it's a solved, boring, infrastructure problem. The thing that will quietly hurt you is a query that runs perfectly, returns a clean number, and is wrong in a way nobody in the room can see. That's the failure mode worth most of your attention, and it's the one the generic guides barely mention.

The one mental model: it's a translator, not an analyst

Here's the frame I'd anchor everything to. An LLM doing text-to-SQL is a translator, not an analyst. It converts English tokens into SQL tokens that are statistically likely given your schema. It does not know what your business means by "active," "revenue," or "last quarter. " It has never sat in a meeting where someone decided that refunds get subtracted from gross but chargebacks don't.

A translator producing grammatical output is not the same as a translator producing true output. Your text-to-SQL system can be 100% syntactically valid, 100% safe to run, and still hand your CFO a number that's off by 30% — because the English question was ambiguous and the model resolved the ambiguity by guessing, confidently, without telling anyone it guessed.

The dangerous text-to-SQL query is not the one that errors out. It's the one that returns a plausible number for the wrong question.

A worked example: two correct queries, one right answer

Say someone asks: "How many active users did we have in June? " Every word in that sentence hides a decision, and the model has to make all of them silently.

What does "active" mean — logged in, or actually did something? Does "users" include the internal test accounts your team uses? Is June bounded by when they signed up, when they last acted, or when a session started? A human analyst asks a clarifying question here. The model doesn't; it picks the most literal reading and moves on.

User asked: 'How many active users did we have in June?'

-- What the model wrote (runs fine, returns 48,213):
SELECT COUNT(*) FROM users
WHERE last_seen_at >= '2026-06-01'
  AND last_seen_at <  '2026-07-01';

-- What 'active' actually means in this company's warehouse:
--   a user with >= 1 completed session, excluding internal
--   test accounts and users who churned mid-month.
SELECT COUNT(DISTINCT s.user_id) FROM sessions s
JOIN users u ON u.id = s.user_id
WHERE s.completed = true
  AND s.started_at >= '2026-06-01'
  AND s.started_at <  '2026-07-01'
  AND u.is_internal = false;   -- answer: 31,904

Both queries are valid SQL. Both run in milliseconds. Both are "safe. " One is off by 16,000 users — a third of the answer — and nothing in the system flags it. The model chose the users table because the question said "users," chose last_seen_at because it sounded like activity, and never touched sessions, completed, or is_internal because nothing in the plain-English question pointed there. This is the entire game, and it's invisible in a demo where nobody knows the right answer already.

So fix the definitions, not the model

The instinct is to reach for a bigger, smarter model. That's the wrong lever. A smarter translator still doesn't know your refund policy. The fix is to remove the ambiguity before the model ever sees it, by encoding your business definitions somewhere the model has to go through.

In practice that means a semantic layer or a library of vetted metrics: "active_users" is defined once, as real SQL, by someone who knows the business, and the model's job shrinks from "invent the definition" to "call the right pre-defined metric and add the filters. " You are trading the model's freedom for correctness, and in analytics that's almost always the right trade. The narrower you make the model's job, the fewer silent decisions it gets to make.

The other high-leverage move is example pairs — five to ten real questions paired with their known-correct SQL, drawn from queries your analysts actually trust. This does more for accuracy than any prompt-tuning, because it shows the model your conventions instead of describing them. A well-curated handful of examples beats a paragraph of instructions every time.

The part everyone gets right: making it un-dangerous to run

Now the safety half — which I'm calling easy not because it's unimportant but because it's mechanical and fully solved. The core idea from the standard framing is exactly right and worth repeating: never trust the prompt to keep you safe. Prompting a model to "only write SELECT statements" is advice, not a control. A jailbroken or just-confused model will cheerfully ignore it.

The enforcement lives in the database, in a role that physically cannot do harm. If the connection has no write permission, it does not matter what the model generates — a DROP or UPDATE simply fails at the engine. This is the single most important line of defense, and it's five lines of DDL:

-- The guardrail that matters is a database role, not a prompt.
CREATE ROLE nl_query_readonly LOGIN PASSWORD 'redacted';

-- No writes, no DDL, no reaching outside one schema.
GRANT USAGE ON SCHEMA analytics TO nl_query_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO nl_query_readonly;
REVOKE ALL ON SCHEMA public FROM nl_query_readonly;

-- Kill runaway scans at the connection level, not in the prompt.
ALTER ROLE nl_query_readonly SET statement_timeout = '8s';
ALTER ROLE nl_query_readonly SET default_transaction_read_only = on;

On top of the read-only role, add a cheap SQL parse step that rejects anything that isn't a single SELECT — no multiple statements, no CTEs that sneak in a write, no DDL. Belt and suspenders. The statement_timeout matters more than people expect: the classic incident isn't malice, it's an innocent "show me everything about our customers" that turns into an unbounded join across a billion rows and pins the database. A timeout turns that from an outage into an error message.

A decision framework: when text-to-SQL is worth it

Not every "let people query the data" problem should be solved this way. Here's how I'd decide.

• Reach for it when: the schema is stable and well-named, the questions are exploratory and low-stakes, and a human eyeballs the result before anyone acts on it. Internal analytics for a curious team is the sweet spot.
• Be cautious when: the numbers feed a dashboard executives trust blindly, or the schema is a sprawl of cryptic column names and undocumented joins. Here the ambiguity tax is brutal — invest in a semantic layer first, or the wrong-but-plausible answers will erode trust fast.
• Don't use it when: the output triggers automated action (billing, alerts, downstream jobs) with no human in the loop, or the data is regulated and a cross-tenant leak is a legal event. The failure mode is too expensive for a probabilistic translator.

Notice the axis isn't "how smart is the model. " It's "how much does a silently-wrong answer cost, and is there a human between the answer and the consequence. " That's the real design question.

Non-obvious gotchas

A few things that don't show up until you've run one of these against real data and real users:

1. Show the SQL, always. Rendering the generated query next to the answer is your cheapest correctness control — it turns a silent wrong number into something an analyst can spot-check in five seconds. If you show only the number, you've hidden the one artifact that lets anyone catch the mistake.
2. The empty result is the trap. When a query returns zero rows, users read it as "we have none" when it often means "the filter was wrong. " A guess at a status value like 'active' that's really stored as 'ACTIVE' returns a confident, clean zero. Treat empty results as suspicious, not as answers.
3. Let it self-correct, but cap the retries. Feeding the database's error message back so the model fixes its own syntax is a genuine accuracy win. But loop it two or three times max — an unbounded retry loop is both a cost sink and a way for a model to grind toward a query that runs but is nonsense.
4. Schema drift silently rots your examples. Rename a column and your carefully curated example pairs now teach the model the wrong thing. Whatever context you feed the model has to be generated from the live schema, not a doc someone updates by hand.

The bottom line

The standard advice — feed it the schema, run it read-only, validate before executing — is correct and you should do all of it. But it under-weights the real risk. Safety is a role and a timeout; you write it once and it holds. Correctness is a moving target because English is ambiguous and your business definitions live in people's heads, not in your column names.

So spend your effort where the risk actually is: encode your definitions in a semantic layer, teach the model with real example queries, always show the SQL, and put a human between any generated number and any decision that matters. Treat the model as a fast, literal-minded translator that will never ask a clarifying question — build the system so it doesn't have to, and text-to-SQL goes from a demo that impresses to a tool people can actually trust.