Hallucinations on Structured Data

LLM hallucinations on unstructured data have been known for the last few years. The chatbot makes up a citation and the summariser invents a quote. The RAG system produces a confident answer not in the source. This is a known failure mode and a known set of mitigations.

What’s a Reranker?

Hallucinations on structured data are different. They’re newer, less discussed, and in some ways more dangerous, because structured data carries an aura of precision that unstructured text doesn’t. A made-up quote is obviously a made-up quote once you check. A made-up number, or a real number computed wrongly, doesn’t look made up. It just looks like a number. This is the part of the analytics agent problem that nobody’s fully prepared for.

What Hallucination on Structured Data Actually Means

The taxonomy I find useful:

Fabricated columns. The agent references a column that doesn’t exist. Often confidently. The query fails, which is recoverable, but only because the database refused; the model thought the column was real.
Fabricated values. The agent claims a customer had £12,000 in revenue when the data says £120. Wrong by a factor of 100. Usually traceable to a misread of the data, sometimes a confident invention.
Confused joins. The agent joins two tables on plausible-looking but wrong keys. The result is a Cartesian-ish disaster that produces a real number, just not the right one.
Wrong filter logic. The agent excludes refunds when it should have included them, or vice versa. The number is computed correctly given the (wrong) filter; the explanation may or may not flag the choice.
Hallucinated semantics. The agent claims a column “means” something it doesn’t. A column called flag_a gets described as “a flag indicating active customers” when nobody really knows what it means.
Currency/unit confusion. The agent gives an answer in the wrong currency or unit, undetected because the number is just a number with no symbol attached.
Time-zone errors. The agent treats UTC timestamps as local time, producing answers that are off by hours and the user has no way to tell.

The model isn’t inventing a fictitious quote; it’s using real data wrongly. The output looks plausible because real numbers are involved, just used incorrectly.

Why This Matters More

Three reasons:

First, the trust gradient. Numbers carry more authority than text. A user reading a summary will instinctively scan for misstatements. A user reading a number will write it into a board pack. The signal-to-trust ratio is wrong.

Second, the detectability. A hallucinated quote can be checked against the source by a non-expert. A hallucinated number requires reconstructing the query, finding the source data, and computing the right answer. Detection requires substantially more expertise than the original question did.

Third, the consequence. Decisions get made off numbers. Wrong numbers lead to wrong decisions in a way that’s less true of wrong narrative summaries.

Where The Failures Actually Originate

Mostly upstream of the model. The patterns I’ve seen:

Inadequate metadata. The agent doesn’t know what amt_local_ccy is in. It guesses. Sometimes correctly. Currency confusion lives here.

Multiple plausible tables. The agent picks the wrong “revenue” table because three exist and the metadata didn’t make the canonical one obvious. Discussed in the text-to-SQL post; the semantic layer answer applies here too.

Implicit business logic. The official revenue calculation excludes refunds; the agent doesn’t know; the answer is gross when net was wanted. The logic exists in someone’s head and nowhere the model can read.

Time-handling ambiguity. “Last quarter” means different things depending on fiscal vs calendar year, time zone, and what “quarter” means for the specific business.

The model’s prior. Sometimes the model just confidently picks the wrong join key because the column names look right and the model has seen that pattern before. Pure inference error, no metadata involved.

What The Evidence Actually Shows

It’s tempting to treat all of this as anecdote. I did a recent controlled study on a synthetic financial-services dataset puts hard numbers on it, and the numbers are blunt (no commercially sensitive data points were harmed in the filming).

The setup is a synthetic trading and asset-management schema — 9 tables, 361 columns, ~815,000 rows — deliberately loaded with the exact traps that produce structured hallucinations: polymorphic foreign keys (an owner_id that points at one table or another depending on a sibling status code), overlapping cancellation signals strewn across the order, trade and allocation lifecycle, two coexisting credit-rating scales that both surface as "AAA", versioned positions that require an is_current_version filter on every aggregation, and multiple “book” identifiers (IBOR / ABOR / CBOR) carrying different valuation semantics for the same underlying holdings.

The same physical data was then exposed through four progressively richer schema tiers:

ACTUAL — cryptic legacy names (t_cpy, f9, n11), no comments, no keys.
BASIC — readable names, but still no comments and no constraints.
DESCRIBED — a column-level COMMENT on every meaningful field, primary and foreign keys, documented filter patterns.
SEMANTIC — Snowflake CREATE SEMANTIC VIEW definitions exposing canonical metrics, dimensions, relationships and synonyms on top of the described tables.

Two production engines answered four deliberately simple business questions, each with a single objectively-correct number: Anthropic’s Claude Sonnet 4.6 writing SQL against the schema, and Snowflake Cortex Analyst querying the semantic view. Here is the entire result:

Tier	Engine	Q1 exposure	Q2 nt3ae	Q3 filled+settled	Q4 credit global
ACTUAL	Claude	Wrong	Wrong	Wrong	Wrong
BASIC	Claude	Wrong	Wrong	Wrong	Wrong
DESCRIBED	Claude	Wrong	Wrong	Wrong	Wrong
SEMANTIC	Claude	Exact	Exact	Exact	Exact
SEMANTIC	Cortex Analyst	Exact	Exact	Exact	Exact

The headline the I drew is the one that should worry anyone shipping an analytics agent: tier dominates engine. Swapping the model, even to an entirely different engine, changed nothing at the semantic tier; both returned the gold answer to the penny. Changing the schema tier, with the same model, changed the answers by orders of magnitude. The model is doing roughly the same work in every case. What changes is the ground truth it has to compose against.

The most instructive row is BASIC. Readable column names rescue the model from outright “column not found” errors, but not from semantic confusion. The counterparty table carried at least six exposure-named columns — current_exposure_usd, gross_exposure_usd, net_exposure_usd, pfe_usd, ead_usd — each computed differently and consumed by a different function inside the firm. With nothing but names to go on, the model hallucinated a choice: it landed on current_exposure_usd, a nightly snapshot that looked defensible and undercounted the true figure by roughly £22 billion. No error was raised. The query ran. The number looked like a number. That is precisely the failure this post is about — quantified.

And here is the part that should puncture any “we’ll just use a smarter model” optimism: the DESCRIBED tier — full column comments, keys, documented filters — still failed all four questions. The comments correctly told the model that current_exposure_usd was stale, that ead_usd was a regulatory-capital construct, and that the canonical exposure had to be derived from live positions joined through accounts.custodian_cp_id. The model had the information and still had to synthesise the multi-table aggregation itself — and at that synthesis step it made the same class of mistake: the wrong join, the wrong grain, the missed soft-delete filter. Information was never the bottleneck. Composition was.

This isn’t an isolated finding. dbt Labs’ ADE-bench work reaches the same conclusion from a different direction — that data agents are “essentially useless without the right context,” and that the surrounding semantic layer dominates raw model capability — and data.world has reported a roughly three-fold accuracy uplift when an LLM is grounded in a structured knowledge layer rather than a bare schema.

The Synonym Test

The sharpest single result is a pair of questions that ask for the same number in two different vocabularies. Q1: “total exposure to AAA-rated counterparties.” Q2: “show me the nt3ae.” Same business question, same gold answer — £23.39 bn. The only difference is the noun.

ab3ae is a firm-internal token: in the scenario it decomposes as Northern Trust + 3A (in-house shorthand for a AAA rating) + Exposure, and it exists only in the firm’s own documentation (other company name abbreviations are available…). It is, deliberately, a word no pretraining corpus, thesaurus, or ticker list can resolve. A plain English word like footprint, or even an industry acronym like NTE, a strong general model would quietly bridge out of pretraining. nt3ae it cannot, because the token does not exist anywhere outside the business.

The lower three tiers fail Q2 for one simple reason: the bridge from the firm’s word to a physical column lives nowhere in the schema. The semantic tier passes because the metric carries an explicit WITH SYNONYMS = ('exposure', 'NTE', 'nt3ae', ...) clause. The synonym list is not decoration — it is the canonical record that the firm’s house token and the external industry term mean the same thing, and that record has to be sourced from the actual vocabulary of the business units that consume the data, then maintained as those vocabularies drift. A metric without synonyms serves one team; a metric with synonyms serves the firm.

This is the cleanest available test of whether a data layer is genuinely semantic or merely well-described. A described schema documents columns. A semantic layer documents concepts — and concepts have synonyms.

Comments are Advice; Contracts are Executed

The obvious objection to all of this is: if column comments help, why not push them harder — encode the synonyms, the join paths, even the metric recipes into ever-richer comments, and let the model read and assemble the right query from prose? The study measured exactly that (it is the DESCRIBED tier), and the answer is that comments are advice the model can ignore; a semantic view is a contract it can only execute. The distinction turns on a few concrete points:

Determinism. With a semantic view, the model’s job collapses to selecting a named metric and a named dimension; the engine substitutes the SQL. There is no path by which it can hallucinate a different aggregation. With comments, every single call is a fresh, non-deterministic test of whether the model read the note and obeyed it this time — and regression-testing that against every model upgrade is a real, recurring engineering cost.
Composed metrics. A column comment can describe one column. It cannot encode “AAA-rated, active, approved, non-terminated counterparties, joined to positions through accounts.custodian_cp_id, current version only, all books, base currency.” That is four-table SQL. The only way to make such a metric available rather than described is to materialise it as a view.
Provability. A semantic engine can return which canonical metric it used (Cortex Analyst surfaces exactly this through an is_semantic_sql flag and an interpretation field). With comments, you can only learn what the model did by reading its generated SQL, by hand, on every answer.
Governance and portability. Synonyms are business vocabulary, not schema. A semantic layer sits above the columns, so analytics engineers can register and update them without disturbing the source-of-truth tables — and the same definition is consumable by the dbt Semantic Layer, Cube, Looker, AtScale or Snowflake’s native semantic views. Switching engine no longer means rewriting your comments-as-prompts.

The practical reading is the one the study lands on: the semantic layer is not optional metadata or a legacy-BI artefact. It is the load-bearing component of any LLM-mediated analytical path — and on current evidence it matters far more than which model you point at it.

What Helps?

The same set of mitigations keeps coming up:

A semantic layer. Stop letting the model write SQL against raw tables. Force it to use a layer where the meaning is explicit, joins pre-defined, business logic baked in.
Aggressive metadata. Every column has a definition. Every metric has a formula. Every time field has a timezone. The model has nowhere to guess from.
Constrained tool use. Give the agent narrow, well-defined tools. “Compute revenue for this period and region” rather than “write SQL.” The model can’t make up columns it isn’t exposed to.
Result explanation. Every number comes with what the model did to produce it. The user can sense-check the path even if not the number.
Calibrated refusal. When the model isn’t sure, it says so. Hard to train, valuable when present.
Cross-checking. A second model verifies the first. Expensive but useful for high-stakes outputs.
Eval, eval, eval. The covered topic from last week. Without an eval harness, you don’t know whether you have this problem.

The Narrower Point

The conventional “LLMs hallucinate” framing is about the unstructured-data failure mode. The structured-data failure mode is its own thing, with its own mitigations, and the field hasn’t fully caught up. The agent vendors are mostly racing on capability and underselling the calibration problem. Most enterprise deployments are still building intuition for what wrong answers look like.