Your AI Writes Correct SQL and Still Gets the Wrong Answer. Here's Why.

You wire up an AI SQL assistant, type "what was our revenue last month," and get back a clean, valid query. It runs. It returns a number. Everyone nods.

Then someone in finance runs the same question through the accounting system and gets a different number. Now you're in a meeting arguing about which one is real.

Here's the uncomfortable truth about text-to-SQL: the SQL is almost never the hard part. Modern models write syntactically correct SQL the vast majority of the time. The failures that actually hurt you aren't syntax errors — they're queries that run perfectly and quietly return the wrong answer. And no amount of "use a better model" fixes them, because the model was never given the one thing it needed: your definitions.

This post is about why that happens and how a semantic layer — a place where each metric is defined exactly once — turns an unreliable guessing machine into something you can actually trust.

The problem: your business terms are ambiguous, and the database doesn't know it

Ask three teams what an "active user" is and you'll get three answers. To product, it's a user with a login event in the last 30 days. To billing, it's a user with a live subscription. To growth, it's someone who hit a core action this week.

Your warehouse doesn't encode any of that. It has an events table, a users table, and a subscriptions table. When an LLM sees "active users," it picks one plausible interpretation, writes valid SQL for it, and hands you a number. The number looks reasonable, so nobody questions it — until it disagrees with every other system in the company.

This is what makes text-to-SQL dangerous rather than merely imperfect. As the team at Omni put it, the queries that fail don't throw an error — they return wrong data. A syntax error you catch immediately. A silently wrong active_user count you ship to a board deck.

Consider what the model actually has to guess:

Business term	Possible SQL meaning A	Possible SQL meaning B
Active user	login event in last 30 days	subscription status = 'active'
Revenue	SUM(orders.amount)	SUM(orders.amount) minus refunds
Churn	cancellations this month	MRR lost / MRR at start of month
New customer	first order this month	first paid subscription this month

The model isn't wrong for picking column A. It's wrong because there is no way for it to know you meant column B. That knowledge lives in people's heads and in scattered SQL snippets, not in the schema.

Why "just prompt it better" only gets you so far

The common first fix is to stuff definitions into the prompt: "active user = a user with at least one login event in the last 30 days." This genuinely helps — including business glossaries in context is one of the most effective mitigations you can apply today.

But prompt-based definitions don't scale:

• Every question re-derives the metric from a fuzzy English sentence, so you get subtle drift between runs.
• Nobody updates the prompt when the definition changes, so the glossary rots.
• Complex metrics (net revenue retention, cohort churn) don't fit in a sentence — they need real SQL logic.
• You have no guarantee the model used the definition rather than ignoring it.

Prompting treats the symptom. The disease is that your metric definitions aren't a first-class, queryable object anywhere in your stack.

The fix: define each metric exactly once

A semantic layer is a centralized catalog that sits between your warehouse and whatever queries it — a BI tool, a dashboard, or an AI assistant. Instead of every analyst (and every LLM prompt) re-deriving "monthly revenue," you define it one time, as explicit SQL, and everything references that definition.

Here's the shift in concrete terms. Without a semantic layer, your AI generates the whole query from scratch:

-- AI free-writing against raw tables. Which interpretation did it pick?
SELECT COUNT(DISTINCT user_id)
FROM events
WHERE event_name = 'login'
  AND created_at >= NOW() - INTERVAL '30 days';

With a semantic layer, the metric is pre-defined and the AI's job shrinks to picking the right metric and dimensions — not inventing the SQL. A definition might look like this (dbt/MetricFlow-style YAML):

metrics:
  - name: active_users
    label: "Active Users"
    description: "Distinct users with a login event in the last 30 days"
    type: simple
    sql: user_id
    agg: count_distinct
    filters:
      - "event_name = 'login'"
      - "created_at >= dateadd(day, -30, current_date)"
    dimensions: [plan, country, signup_month]

Now when someone asks "active users by plan last quarter," the AI doesn't guess the join or the filter. It selects the active_users metric and the plan dimension, and the semantic layer compiles the guaranteed-correct SQL. As dbt Labs describes it: if the model picks the right metric and dimensions, the query is correct by construction — it can't produce a bad aggregation or a wrong join.

You can express richer metrics the same way. Net revenue as explicit, reviewed logic:

-- Definition behind the "net_revenue" metric, written once, reviewed by humans
SELECT
    date_trunc('month', o.order_date) AS month,
    SUM(o.amount) - COALESCE(SUM(r.refund_amount), 0) AS net_revenue
FROM orders o
LEFT JOIN refunds r ON r.order_id = o.id
GROUP BY 1;

Every question about "revenue" now resolves to this, whether it comes from a human, a dashboard, or an AI prompt. One source of truth, not one interpretation per query.

What this does to accuracy (the numbers are dramatic)

This isn't a marginal polish. Recent benchmarking tells a stark story. On raw, unnormalized tables, text-to-SQL systems have historically landed around 32.7% end-to-end accuracy. Point the same models at a properly modeled semantic layer and accuracy jumps to roughly 72–100% depending on question type, with enterprise deployments commonly reporting 85–95%.

The other benefit is subtler but just as important: when a question can't be answered from the defined metrics, a semantic layer can say so instead of fabricating a plausible query. "I don't have a metric for that" is infinitely safer than a confident wrong number.

Common mistakes and gotchas

Treating the semantic layer as documentation. A glossary in Notion doesn't help the AI. The definition has to be a live, queryable object — SQL the engine actually compiles — not prose a human has to remember to copy into a prompt.

Modeling everything on day one. You don't need 300 metrics. Start with the 10–15 numbers that show up in exec meetings and cause arguments. Those are where silent wrongness costs you the most.

Forgetting synonyms. Users say "signups," "new accounts," "registrations," and "new users" for the same thing. Register synonyms so the AI maps natural language to the right metric instead of free-writing a fourth variant.

No ownership. A metric definition without an owner drifts. When finance changes how churn is calculated, someone has to update the one definition — and because it's centralized, that update propagates everywhere instead of leaving twelve stale copies.

Skipping row-level security. A semantic layer is a great place to enforce tenant isolation, but only if you wire it in. Don't assume defining a metric also scopes it to the right customer's data.

Key takeaways

• With modern models, correct syntax is the easy part. Correct meaning is the hard part, and it's where text-to-SQL silently fails.
• Ambiguous business terms — active user, revenue, churn — have no single meaning in your schema, so the AI guesses, and the guess looks right.
• A semantic layer defines each metric once as explicit SQL, shrinking the AI's job from "write the whole query" to "pick the right metric and dimensions."
• This moves accuracy from roughly a third of questions to the 85–95% range in real deployments — and lets the system admit when it doesn't know.
• Start small, assign owners, register synonyms, and treat definitions as code, not documentation.

If you're bolting an AI assistant onto your database, the highest-leverage thing you can build isn't a better prompt — it's a place where "revenue" means exactly one thing.

How are you handling this? Are you defining metrics in dbt, a dedicated semantic layer, or still shipping definitions inside prompts? Drop your approach in the comments — I'd love to hear what's actually holding up in production, and what tools you reach for to expose these metrics to non-technical teammates.

---

Sources: dbt: Semantic Layer vs. Text-to-SQL 2026 benchmark, Omni: Why text-to-SQL fails, Wren AI: Why the semantic layer is essential for reliable text-to-SQL, dbt Labs: How the dbt Semantic Layer works with MetricFlow.