Why LLMs Hallucinate on Analytics Questions — and How Grounding Fixes It

Ask an LLM to write a poem and hallucination is charming. Ask it "what was our Q2 revenue?" and hallucination is a career-limiting event — for you, not the model.

Here's the uncomfortable truth teams discover in their first text-to-SQL pilot: the SQL almost always runs. The answer is frequently wrong. Execution success and semantic correctness are different things, and the gap between them is where trust dies.

The five failure modes

1. Schema guessing

Your warehouse has users, users_v2, users_backup and dim_users. Only one is canonical, and nothing in the schema says which. The model picks the one with the friendliest name. Twenty percent of your rows are missing from the answer and nobody knows.

2. Join hallucination

The model joins orders.user_id to accounts.id because the names look plausible. The real relationship goes through a mapping table. The query runs, returns rows, and every number is silently inflated by the fan-out.

3. Definition drift

"Churn", "active", "enterprise", "margin" — none of these live in your schema. They're business definitions with edge cases (do trials count? are refunds excluded?). The model invents a reasonable definition — reasonable being the most dangerous word in analytics, because a plausible-but-different definition produces a plausible-but-different number.

4. Dirty-data blindness

Every real warehouse has conventions: status code 'X' means test data, negative quantities are returns, the region column was repurposed in 2024. Humans learn these by getting burned. The model can't get burned — so you do.

5. Confident presentation

The deepest problem isn't the error — it's the delivery. A junior analyst says "I think this is right, can someone check?" An LLM says "Enterprise churn in Q2 was 3.4%" with perfect fluency either way. Fluency is not accuracy, but human brains treat it that way.

Why prompting doesn't fix it

The instinctive fixes — longer prompts, schema dumps in context, RAG over documentation — all share a flaw: they're advisory, not enforcing. The model may use the context correctly, or it may not, and you can't tell which happened from the answer alone. Stale docs make it worse: now you're grounding on fiction.

Accuracy on public text-to-SQL benchmarks also flatters real-world performance. Benchmarks use clean, small, well-named schemas. Your warehouse has 3,000 tables, cryptic column names and fifteen years of migrations.

The fix: grounding through a semantic layer

The reliable architecture inverts the roles. Instead of the LLM knowing everything, the LLM handles language while a semantic layer handles truth:

1. Entity resolution — "enterprise customers" maps to a governed definition, not a guess.
2. Validated join paths — relationships are inferred once, verified with evidence, and reused deterministically. No join hallucination, because the model never invents joins.
3. Canonical metrics — "churn" resolves to the signed-off formula, with its edge cases.
4. Policy enforcement — restricted columns are masked or refused before execution. (This is a governance property too — see text-to-SQL governance.)
5. Mandatory explainability — every answer ships with the SQL that ran and the tables it touched. Wrong answers become findable, diagnosable and fixable.

The difference in failure behavior is the whole point. An ungrounded system fails silently and fluently. A grounded system fails loudly: "I don't have a definition for 'gross retention' — here's what I do have." One of these failure modes you can build a business on.

What to do this quarter

1. Build a 30-question benchmark from real questions with known answers.
2. Disqualify any tool that hides its SQL.
3. Encode your five most-argued-about metric definitions before scaling access.
4. Track corrections per week — a healthy grounded system converges; an ungrounded one repeats the same class of error forever.

This architecture is exactly what Sema implements: discovery maps your schema and relationships with evidence, a glossary grounds your terminology, and every plain-English answer arrives with its SQL attached. See it on your own data.