Text-to-SQL does not fail on the model, it fails on the metrics

The expectation for text-to-SQL is understandable: a business team asks in plain language, the model writes SQL, the number appears. In demos this works impressively well. In grown warehouses it works considerably worse, and the usual explanation — the model is not good enough yet — misses the point.

An experiment anyone can run

Take the question “what was revenue last quarter?” and put it to three experienced people in your own organization — one from controlling, one from sales, one from accounting. In most companies three different numbers come back, and all three are defensible:

• With or without cancellations, and from when does a cancellation count as booked?
• By invoice date or by service date?
• Group currency at the daily, monthly or period-end rate?
• Intercompany revenue included or eliminated?

If three experts with context arrive at three answers, the question is underdetermined. A model that only sees table names and column types has less context than those three — it cannot answer the question better in principle, only faster.

A better model does not turn an underdetermined question into a determined one. It just guesses more convincingly.

Why the guess is so dangerous

Badly written SQL throws an error. Correctly written SQL against the wrong table returns a number. The number looks plausible, has the right order of magnitude and travels into a presentation. The error goes unnoticed because nothing breaks.

That sets text-to-SQL apart from most other model applications. With a translation you can see when it is poor. With a metric you cannot.

The groundwork that actually works

Instead of working on the prompt, work on unambiguity. Concretely: for every metric that appears in reports, record a definition — machine-readable, versioned, with an owner.

metric: net_revenue
  description: >
    Net revenue after cancellations, intercompany eliminated.
    Authoritative for external reporting.
  source: marts.fct_revenue
  expression: sum(amount_eur)
  filters:
    - cancelled = false
    - intercompany = false
  time_axis: service_date        # not invoice date
  currency: EUR, month-end rate
  dimensions: [region, product_line, sales_channel]
  approved_by: Controlling
  effective_from: 2026-01-01

With a definition like this the model's job changes fundamentally. It no longer has to decide which table is meant and how cancellations are handled. It only has to recognize that “revenue” means the metric net_revenue and pull the dimensions out of the question. That is a mapping task, and models do those reliably.

What this has to do with MCP

The Model Context Protocol is a good fit for exactly this catalog: one tool that returns the available metrics with descriptions, and one that queries a metric with dimensions and filters. The SQL is then produced in the warehouse from the definition, not in the model from the schema.

The uncomfortable part

Writing metric definitions is not technical work but a business agreement. It forces decisions on questions that could stay open for years because each department kept its own reading. That is exactly why this work gets skipped in favor of looking for a better model.

The effort pays off independently of AI, though. A semantic layer answers the same question for dashboards, notebooks and exports. The AI application then benefits from an order that was missing anyway — rather than being its purpose.