Why Conversational BI Tools Hallucinate Metrics, and the Guardrails That Actually Reduce It

A conversational BI tool that returns broken SQL is annoying but harmless — the query errors, the analyst notices, nothing ships. The dangerous failure is the one where the tool returns a number. A clean, plausible, precisely-formatted number that is wrong. Someone pastes it into a board deck, and the error propagates faster than anyone can trace it.

Metric hallucination isn't the model "making things up" in the way people imagine. It's a set of specific, recurring failure modes, most of which are about semantics rather than syntax. If you understand where they come from, you can build for them.

The five ways the number goes wrong

1. Ambiguous metric definitions

Ask ten analysts to define "revenue" and you'll get gross versus net, booked versus recognized, with or without refunds, in transaction currency or reporting currency. Ask a model, and it picks one — usually the most literal SUM(amount) it can find — with total confidence and no indication that it made a choice. The number isn't wrong because the model failed; it's wrong because the question was underspecified and the model resolved the ambiguity silently.

2. Fan-out from bad joins

The classic. Join orders to order_items to compute order-level revenue, forget the grain, and every order's total gets multiplied by its line-item count. The SQL is valid. It runs. The number is inflated by 3x and looks like a great quarter. This is the most common source of confidently-wrong figures in text-to-SQL, and it's invisible unless you're checking against a known total.

3. Silent filter drift

Your canonical active_users metric excludes internal accounts, test users, and soft-deleted records. The model, generating fresh SQL, doesn't know those exclusions exist unless you told it. It counts everyone. The definition drifts from your governed one, and now the assistant's "active users" and the dashboard's "active users" disagree by a few percent — the worst kind of discrepancy, big enough to matter and small enough to miss.

4. Date-grain and timezone errors

"How did we do last week" requires the model to decide what "week" means (ISO week? Sunday-start? trailing seven days?) and what timezone the timestamps are in. Warehouses store UTC; the business thinks in local time. A model that groups UTC timestamps by calendar day will misattribute every transaction near midnight, and the daily numbers will be subtly, permanently off.

5. Averaging averages and other stats traps

Ask for "average conversion rate by region" and a naive query averages the per-region rates, giving every region equal weight regardless of traffic. The correct answer weights by volume. The model produces a number that is arithmetically fine and analytically meaningless.

Why prompting alone won't fix it

The instinct is to write a better prompt: "be careful about grain, use net revenue, exclude test accounts." This helps at the margin and fails in aggregate. You cannot enumerate every business rule in a prompt, the model won't apply them consistently across a long session, and you have no way to verify it did. Prompting reduces the error rate; it doesn't give you a floor. For numbers that go in front of executives, you need architectural guardrails, not better instructions.

The guardrails that move the needle

Put a semantic layer between the model and the SQL

This is the highest-leverage control, and it addresses three of the five failure modes at once. When the model queries a semantic layer — dbt's MetricFlow, Cube, or a warehouse-native metrics layer — the metric definition, the join paths, the grain, and the filters are all pre-specified. The model chooses which metric and which dimensions to slice by. It cannot redefine revenue, cannot pick the wrong join, cannot forget the test-account exclusion, because it isn't writing those parts. Fan-out and filter drift essentially disappear because the model never controls the join.

Return the definition alongside the number

Every answer should surface not just the value but the metric definition and applied filters, in plain language: "Net revenue (gross minus refunds), USD, excluding internal accounts, June 1–30, in America/New_York." This does two things. It lets the analyst catch a wrong assumption instantly, and it changes the tool's posture from oracle to collaborator. A number without its definition is a liability; a number with its definition is a checkable claim.

Show the SQL, always

Never hide the generated query. Analysts can read SQL, and the fastest hallucination detector is a domain expert glancing at a GROUP BY. Tools that hide the query in the name of "accessibility" remove your best line of defense. Expandable-but-visible is the right default.

Build sanity checks into the pipeline

Automated checks catch a surprising fraction of errors before a human sees them:

• Reconciliation against golden totals. If you have a trusted daily-revenue table, cross-check the model's revenue answer against it and flag discrepancies over a threshold. This is the single most effective catch for fan-out.
• Row-count reasonableness. A "top 10 customers" query that returns 40,000 rows is signaling a grain problem.
• Null and zero flags. A metric that suddenly returns zero or null for a period that had data before is worth surfacing rather than displaying.

Make refusal a first-class outcome

Instruct the tool, and design the UI, so that "I can't answer this reliably from the governed metrics" is an acceptable and visible response. The most dangerous conversational BI tools are the ones that always produce an answer. A tool that occasionally says "this question needs a metric we haven't defined — here's what's missing" builds the kind of trust that keeps people using it.

Calibrate trust to stakes

Not every question needs the full treatment. An analyst poking at a hypothesis in an exploratory session can tolerate a rough number they'll verify anyway. A figure headed for a regulatory filing or an earnings deck cannot. Match the guardrails to the stakes: exploratory queries can run against the raw-SQL path with the number clearly marked as unverified; anything that leaves the analytics team should go through the semantic layer and reconcile against golden totals.

A short, honest note worth putting in your own tool's UI: the output of a conversational BI system is a draft analysis, not a verified figure. It doesn't replace a reviewed data model or an analyst's judgment, and for anything with financial, legal, or compliance weight, the number should be independently confirmed before anyone acts on it.

The uncomfortable truth

You will not drive metric hallucination to zero. The realistic goal is to make errors loud instead of silent — to build a system where a wrong number gets flagged, questioned, or refused rather than formatted nicely and shipped. A semantic layer, visible SQL, definitions attached to every answer, and reconciliation checks won't make the model perfect. They'll make its mistakes survivable, which is the actual bar for putting one of these tools in front of your organization.