Correct SQL, Wrong Metric: Text-to-SQL Isn't Enough

Part 2 of 3 in the series "AI on Business Data — Reliable, Not Just Plausible": Part 1: ChatGPT vs. oneAgent · Part 2: Correct SQL, Wrong Metric (this article) · Part 3: Query Your Data Warehouse with AI

When a SQL query aborts with an error message, one thing is immediately clear:

The result cannot be used.

More dangerous is a query that runs flawlessly, returns a plausible number, and is convincingly explained by the AI — even though it's wrong in business terms.

That is one of the biggest challenges in AI-powered data analysis.

Language models have become remarkably good at translating natural language into SQL. In a demo with a few clearly named tables, it looks impressive.

Real business data, however, consists of historically grown models, differing definitions, and rules that aren't written in column names.

That's why it's not enough for an AI to generate SQL.

It has to know which business logic it is allowed to use.

The problem is not just SQL syntax

A query can:

• be syntactically correct,
• use only existing tables,
• run without errors,
• and return a plausible result.

And still answer the wrong question.

Take the question:

"What was our revenue in May?"

One possible query:

SELECT SUM(total_amount)
FROM orders
WHERE order_date >= '2026-05-01'
  AND order_date < '2026-06-01';

Technically, nothing about this stands out.

But many business questions remain open:

• Is total_amount gross or net?
• Are shipping costs included?
• Do cancelled orders count?
• How are returns handled?
• Is the order date or the invoice date relevant?
• How are foreign currencies converted?

Depending on the answers, the same database can return different revenue figures.

All of those queries can be technically correct.

Why plausible errors are so dangerous

An obvious error gets caught.

A plausible wrong number, on the other hand, can be used directly:

• in management reports,
• budget decisions,
• sales planning,
• or presentations.

The recurring problem in practical text-to-SQL projects is therefore not just the broken query.

It's the query that runs successfully and returns a convincing but wrong result. A related risk — numbers that are invented outright rather than miscalculated — is covered in ChatGPT hallucinations on business data.

That's why many data engineers recommend not letting a language model work completely freely against the raw data model.

Instead, it should build on defined metrics, dimensions, and controlled calculations.

Why real data warehouses are harder

Demo databases are usually small and clearly structured.

A real data warehouse, by contrast, contains:

• hundreds of tables and views,
• historized data,
• alternative join paths,
• technical helper tables,
• differing granularities,
• inconsistent naming,
• and business-specific special rules.

There, a language model needs more than SQL syntax.

It has to judge:

• which table is relevant in business terms,
• which relationship may be used,
• whether a join multiplies records,
• and which company definition applies.

The more complex and business-critical the data, the less quality can depend solely on the model's spontaneous interpretation. How sharply accuracy drops from clean benchmarks to real business data is shown with concrete numbers in How reliable is AI data analysis?

Metrics should not be reinvented

A metric like net revenue should be defined once, in business terms, within the company.

For example:

Net revenue is the invoiced goods value minus discounts, cancellations, and returns, excluding VAT and shipping costs.

That definition then has to be connected to the technical data:

• Which table contains invoices?
• Which status values count?
• Where are returns stored?
• Which date column is used?
• Which exchange rate applies?

Once this logic is verified, it should be applied reproducibly to every question.

The language model recognizes the intended metric. The approved logic computes it.

What a metric layer does

A metric layer connects business terms with technical calculation logic.

It defines, for example:

• which metrics are available,
• how they are calculated,
• which dimensions may be used,
• which filters apply,
• and which time logic belongs to them.

The free-form question

"What was our revenue in May?"

becomes a structured request:

Metric: Net revenue
Period: May 2026
Grouping: none
Filters: approved business logic

The model no longer has to generate every calculation from scratch.

It translates the question into known business building blocks.

Free text-to-SQL:

User question
→ language model generates SQL
→ database returns result

Controlled approach:

User question
→ recognize metric and dimensions
→ apply verified logic
→ controlled query
→ traceable result

The language model remains important. But it doesn't take on the tasks that can be solved unambiguously and programmatically.

Three correct revenue figures

Suppose a database contains orders, invoices, and returns.

The question is:

"What was our revenue last month?"

Order value

All created orders are summed up.

Result: 1,250,000 euros

This may include cancelled or unpaid orders.

Invoice value

All issued invoices are summed up.

Result: 1,080,000 euros

This may include invoices for older orders.

Realized net revenue

Invoiced net amounts are reduced by cancellations and returns.

Result: 935,000 euros

Which number is right?

SQL syntax doesn't decide that.

The company definition does.

Documentation alone is not enough

Well-documented tables and columns improve results significantly.

Still, the model has to decide:

• which definition fits the question,
• which join to use,
• and how to combine different rules.

Documentation should therefore be complemented by controllable structures:

• defined metrics,
• permitted dimensions,
• verified relationships,
• validations,
• and targeted clarifying questions.

Clarifying questions are a quality signal

The question

"Which campaign had the best revenue?"

can mean several things:

• First-touch or last-touch attribution?
• Gross revenue or revenue after returns?
• All customers or new customers only?
• Revenue or contribution margin?

A system should not simply pick one variant.

It should ask.

A clarifying question costs a few seconds. A wrong interpretation can distort a decision.

What additional checks are needed

Beyond metric definitions, further points need to be checked:

• Do the tables and columns exist?
• Is the join permitted in business terms?
• Does the join double-count values?
• Do the granularities match?
• Are user permissions respected?
• Is the result plausible?

No single check guarantees a perfect answer.

Together, however, they prevent too many decisions from resting uncontrolled with the language model.

What a "verified answer" means

"Verified" does not mean every arbitrary question is always answered perfectly.

It means important components are not left entirely to free model interpretation.

That includes:

• approved metric definitions,
• known tables and columns,
• controlled relationships,
• structured filters,
• permissions,
• and clarifying questions when things are ambiguous.

The goal is not to hide uncertainty.

The goal is to recognize it and handle it visibly.

oneAgent is more than text-to-SQL

Text-to-SQL answers the question:

"How do we translate this sentence into a database query?"

oneAgent answers a broader question:

"How do we translate a business question into a traceable analysis defined in business terms?"

For that, the system has to settle, among other things:

1. Which metric is meant?
2. Which definition applies?
3. Which filters and dimensions are permitted?
4. Which data is the user allowed to see?
5. Is a clarifying question needed?

SQL is part of this process.

It is not the whole process. Why this distinction from general AI assistants matters so much is covered in Part 1: ChatGPT vs. oneAgent.

Why the LLM doesn't decide everything

A bigger language model doesn't automatically solve:

• contradictory KPI definitions,
• unclear relationships,
• duplicate records,
• missing permissions,
• or differing granularities.

It can merely phrase a wrong interpretation more convincingly.

That's why the architecture of the overall system matters more than just the name of the model.

Trust in BI is binary

For creative tasks, an answer can be 90 percent right and still useful.

With business metrics, it's different.

Revenue of 9.5 million euros is not almost as good as the correct revenue of 10 million euros.

A single confidently presented wrong number can damage trust in the entire system.

That's why a data agent must not merely sound convincing.

It has to make traceable which logic was used.

Why we build oneAgent this way

We want to combine the simplicity of a chat with the control of classic BI.

The user shouldn't have to write SQL or know table names.

At the same time, central metrics should not be defined spontaneously by the language model.

Our core idea:

The language model understands the question. The verified data logic determines the calculation.

How this approach prevents hallucinations on business data is shown on AI without hallucinations.

That is more work than pointing a model straight at a database.

But that is exactly the difference between a good demo and a solution companies can trust.

Conclusion

The biggest danger in AI data analysis is not the query that fails.

It's the query that runs successfully, returns a plausible number, and still uses the wrong business logic.

That's why text-to-SQL alone is not enough.

It takes a controlled layer between question and database:

• verified metrics,
• defined dimensions,
• controlled relationships,
• clarifying questions,
• and traceable results.

The language model may understand what you're asking. But it shouldn't reinvent how your company computes the answer.

Keep reading

Part 3 of the series shows how oneAgent builds on an existing data warehouse — and why your current data platform is the most important prerequisite for it. Continue to Part 3: Query Your Data Warehouse with AI.