# Semantic layers: why text-to-SQL gets "revenue" wrong, and how to define a metric once

> The SQL runs without errors and the number looks plausible. It is still wrong, because nobody told the model how your client calculates MRR.

Original: https://fdetimes.net/en/guides/semantic-layer-text-to-sql-revenue/

Picture this. You have just deployed an agent at a SaaS company, and the finance team asks it: "What was MRR in January?" It returns a number along with a tidy SQL query that ran without errors. By the afternoon, that number is well off finance's own report, and you are the one called into the meeting.

Almost every FDE working on a "query your data in natural language" project ends up here. The cause is not the model, and it is not the prompt. The cause is that the definition of "revenue" has never been written down anywhere a machine can read it.

This guide shows how to fix the problem at its source: define each metric once in a semantic layer, then have both the LLM and the client's reports use that same definition.

## Syntactically correct SQL can still answer the wrong question

The team at Omni describes the problem well. In their view, LLMs do not write bad SQL as such. The difficulty is that they write SQL that looks reasonable but means the wrong thing. Omni's example is an MRR question: the model joins orders directly to products, skips the subscription table, and returns the total value of orders. No error is raised.

Cube points to why models make this mistake: a warehouse schema does not say how your company defines revenue. A column named `amount` does not tell you whether it is cash collected, revenue recognised or money due each month. Those rules live in the finance team's heads, in a spreadsheet, or in a view that only one person still remembers.

At a client site, this is the FDE's job: turning definitions from the language of the business into something a machine can run. The model will not do this part for you.

## One customer, two numbers

Take a concrete example (the figures are illustrative). Customer A buys an annual plan for USD 12,000 and pays it all in January. If you sum order values by taking the orders → products shortcut, January shows USD 12,000 and the next eleven months show zero. If you go through the subscription table, MRR is 12,000 / 12 = USD 1,000 for every month of the year.

Both queries run, but only the second answers the question finance asked. A single annual plan has inflated January MRR twelvefold. If the client has a few hundred contracts like this, the wrong figure becomes very hard to spot by eye.

The second common error is fan-out. An order worth USD 100 has three product lines. Join orders to order_items and run `SUM(order_total)`, and you get USD 300, because the order total is repeated on each line. The dbt documentation calls this class of error fan-out joins and chasm joins, and MetricFlow is designed to avoid them when it chooses a join path.

## Define it once, call it by name everywhere

A semantic layer deals with both errors by fixing the join path inside the model. Omni reports that when the MRR question is asked again against a semantic layer, the join path is already defined, so the metric is calculated the same way every time. Cube takes the same approach: the agent queries named measures and dimensions rather than reconstructing business logic from table and column names.

In the dbt ecosystem, MetricFlow is the engine that does this. It sets the conventions for defining semantic models and metrics, and it generates SQL from those definitions. Each semantic model has three parts. Entities are join keys, the edges that connect models. Dimensions are the axes along which data is sliced. Metrics are built on top.

Below is an illustration for MRR. The exact syntax depends on your dbt version:

```yaml
semantic_models:
  - name: subscriptions
    model: ref('fct_subscriptions')
    defaults:
      agg_time_dimension: billing_month
    entities:
      - name: subscription
        type: primary
        expr: subscription_id
      - name: customer
        type: foreign
        expr: customer_id
    dimensions:
      - name: billing_month
        type: time
        type_params:
          time_granularity: month
      - name: plan_tier
        type: categorical
    measures:
      - name: mrr_amount
        agg: sum
        expr: monthly_amount

metrics:
  - name: mrr
    label: Monthly Recurring Revenue
    type: simple
    type_params:
      measure: mrr_amount
```

The detail to notice is `monthly_amount`. Dividing annual plans by 12 already happens in the `fct_subscriptions` table, which has been reviewed and has an owner. From here, the LLM only needs to call `mrr` by `billing_month` and `plan_tier`. Finance's dashboard calls the same metric, so the agent and finance no longer each have their own number.

## Failing with an error beats failing silently

In 2026 dbt Labs ran a benchmark of 11 questions on the ACME Insurance dataset, each asked 20 times, across several LLMs. The most memorable finding was not the accuracy rate but the way each approach failed. Text-to-SQL failed with an answer that sounded plausible but was wrong. The semantic layer failed with an error message.

For an FDE, that difference matters a great deal. An error message makes the user ask again or come and find you. A wrong number that looks plausible can sit in a board deck for a whole quarter.

The benchmark has limits worth knowing. To make text-to-SQL work, the authors loaded the entire schema into the context, and they themselves acknowledge that this is not feasible with larger datasets.

Atlan, summarising the benchmark, also notes that this is a vendor testing its own product. Treat the results as a pointer, and run your own evals on the client's data.

dbt's advice does not force a choice between the two. Any number that goes to the board, to auditors, into OKRs, KPIs or the weekly report should come from an LLM connected to the semantic layer. Keep text-to-SQL for ad hoc exploratory questions.

**Key point:** A wrong number that looks plausible is far more dangerous than an error message.

## Where to start at a client site

Do not begin by modelling the whole warehouse. Take the weekly KPI report or the most recent board deck, and pick out the metrics that appear most often. That list is the scope of your first pass.

Next, sit down with whoever actually owns each definition, usually finance or RevOps. Write the definition in plain words before writing any YAML, for example: "MRR counts annual plans divided by 12, excludes setup fees, and is recognised by billing month." Record the name of the person who approved each definition.

Then write the semantic model and run the metric for a few closed months. Compare every figure with the client's existing reports. Wherever they diverge, go back to the definition's owner rather than guessing.

Only connect the LLM once the numbers match. At that point, make the routing explicit: questions that touch a defined metric go through the semantic layer, and only the rest go to text-to-SQL.

## Common traps

The first trap is defining metrics by column name. A `revenue` column in the warehouse is not necessarily revenue as the accountants understand it. The second is quietly falling back to text-to-SQL when the semantic layer returns an error. Do that and you throw away the semantic layer's biggest advantage: errors that surface instead of staying hidden.

The third trap is letting the LLM create new metrics on the fly. A new metric should go through code review like any other change. The last is trusting a vendor benchmark without your own eval set of the client's real questions, each paired with an answer that finance has confirmed.

## How should this skill show up on a CV?

When reading FDE or solutions engineer job descriptions, look for keywords such as dbt, MetricFlow, Cube, semantic layer or "metrics definitions". They signal a company that needs someone to keep the numbers right, not just someone to connect an LLM to a database.

On a CV, a line such as "standardised definitions of core KPIs in a semantic layer and reconciled them with finance reports before exposing them to the agent" carries far more weight than "built a text-to-SQL chatbot".

LLMs will keep getting better at writing SQL. But finding out how a client calculates revenue still means asking people, then writing the answer down in exactly one place.

**Try this week:**

- Pick the metric the client asks about most (MRR, for example), have an LLM write SQL for it five times, and compare the results with the finance team's report
- Write a YAML semantic model for that metric, with entities, dimensions and measures, and check that the number matches the existing report
- Build a table listing each metric with its plain-language definition, the person who owns the definition, and the source table

## Sources

- [Why text-to-SQL fails](https://omni.co/blog/why-text-to-sql-fails)

- [Introducing Cube's Claude Connector and Skills](https://cube.dev/blog/cube-connector-and-skills-for-claude)

- [About MetricFlow | dbt Developer Hub](https://docs.getdbt.com/docs/build/about-metricflow)

- [Semantic Layer vs. Text-to-SQL: 2026 Benchmark Update](https://docs.getdbt.com/blog/semantic-layer-vs-text-to-sql-2026)