The semantic layer is the API for AI

TL;DR

• Text-to-SQL on finance data fails silently in four ways: fan-out joins, summed semi-additive balances, ambiguous definitions and time cutoffs. The SQL is valid; the meaning is wrong.
• A semantic layer moves those decisions into reviewed definitions: declared grain, declared measure type, a named owner and a plain-English description that is now part of the prompt.
• Expose it as one tool over MCP with metrics as an enum, structured filters, a cap on dimensions and a known_at that defaults to the last closed period.
• In a 40-question test the semantic-tool agent went from 62% to 85% correct, refused five of six unanswerable questions instead of one, and made zero grain errors at a third of the tokens.
• Metric definitions are now the contract between your data and every model that will ever answer a question about it.

Every text-to-SQL demo I have seen this year works. Someone types “revenue by region last quarter”, a frontier model writes a tidy query against the warehouse, a chart appears, and the room nods. Then someone from finance checks the number against the close pack and it is off by 11%. Nobody in the room can say why, including the model.

I have spent most of this year wiring language models into finance data. My conclusion is blunt: an agent that writes raw SQL against a finance warehouse is a liability, no matter how good the model is. The SQL is usually valid. The meaning is usually wrong. The fix is not a better prompt. It is giving the model the same thing we should have been giving our BI tools all along: a semantic layer, exposed as a tool.

Why raw text-to-SQL breaks on finance data

There are four ways I see it fail, and they share one property. None of them raise an error.

Fan-out. Finance data is full of one-to-many relationships hanging off the same parent. An invoice has many lines and many payments. Join all three and every line gets repeated once per payment.

Here is a toy example. One customer, one invoice for $1,000, two lines ($600 and $400), paid in two instalments of $500. The question is “how much did we bill and collect for this customer?”

-- What the model wrote. Valid BigQuery, wrong numbers.
select
  i.customer_id,
  sum(l.amount_usd) as billed_usd,     -- returns 2000
  sum(p.amount_usd) as collected_usd   -- returns 2000
from invoices i
join invoice_lines l using (invoice_id)
left join payments p using (invoice_id)
group by i.customer_id;

-- What it should have written: aggregate each child to the parent grain first.
with billed as (
  select invoice_id, sum(amount_usd) as billed_usd
  from invoice_lines
  group by invoice_id
),
collected as (
  select invoice_id, sum(amount_usd) as collected_usd
  from payments
  group by invoice_id
)
select
  i.customer_id,
  sum(coalesce(b.billed_usd, 0))    as billed_usd,     -- 1000
  sum(coalesce(c.collected_usd, 0)) as collected_usd   -- 1000
from invoices i
left join billed b using (invoice_id)
left join collected c using (invoice_id)
group by i.customer_id;

The join produces four rows (two lines times two payments), so both totals double. With three payments the billed total would triple. The model can be told about fan-out in a prompt, and it will still do this one question in twenty, because nothing in the schema says which tables are safe to join at which grain (what one row of each table represents).

Semi-additive measures. Balances add across accounts but not across time. Ask “what were assets under custody in September?” and the natural query is sum(market_value_usd) ... where balance_date between '2025-09-01' and '2025-09-30'.

Toy data again. Client A holds $10.0M every day of September. Client B holds $4.0M for the first 20 days and $6.0M for the last 10.

Calculation	Client A	Client B	Total
Sum of daily balances (what the model did)	$300.0M	$140.0M	$440.0M
Month-end balance	$10.0M	$6.0M	$16.0M
Average daily balance	$10.0M	$4.67M	$14.67M

The first row is not a slightly wrong number. It is a number with no meaning, 27 to 30 times either real answer. The other two rows are both legitimate, and they answer different questions: month-end is what goes in a regulatory report, average daily is what a fee is charged on (I wrote about that in modeling basis-point fees). The model has no way to know which one the person meant unless someone has decided that in advance.

Definitions. “Revenue” means recognized, billed or collected depending on who is asking. Net of rebates or gross. Including or excluding intercompany. The model picks whichever column is called revenue and compiles.

Time. Fiscal quarters that do not align with calendar quarters, a day cutoff in New York rather than UTC, and the difference between what we knew at month-end and what we know now after a restatement. I covered the last one in as-of vs. as-was; a raw SQL agent will mix the two without noticing.

What a semantic layer gives the model

A semantic layer moves those decisions out of the query and into a reviewed definition. In MetricFlow-style YAML (MetricFlow is the engine behind dbt’s semantic layer), the custody metric looks like this:

semantic_models:
  - name: custody_balance_daily
    model: ref('fct_custody_balance_daily')
    defaults:
      agg_time_dimension: balance_date
    entities:
      - name: client
        type: foreign
    dimensions:
      - name: balance_date
        type: time
        type_params: { time_granularity: day }
      - name: asset_class
        type: categorical
    measures:
      - name: market_value_usd
        agg: sum
        non_additive_dimension:
          name: balance_date
          window_choice: max      # last balance in the period, never a sum over days

metrics:
  - name: assets_under_custody
    label: Assets under custody (end of period)
    description: >
      Market value of client assets held in custody at the close of the
      last day of the period, valued at the 00:00 UTC reporting cutoff price,
      the same cutoff the balances use.
    type: simple
    type_params:
      measure: market_value_usd
    meta:
      owner: finance-data
      certified: true

Four things in that file matter for an agent.

The grain is declared. The layer knows the measure lives at client by asset by day, and it only follows joins that cannot fan it out, instead of hoping the query author noticed.

The measure type is declared. Additive measures sum. Semi-additive measures take the last (or average) value over time and sum across everything else. Ratios such as a fee rate in basis points are computed after aggregation, never averaged row by row.

The definition has an owner and a description in plain English. That description is now part of the prompt, which means it deserves the same review as code.

And access control and audit come with it. Row-level security applied by the layer applies to the agent too, and every query is logged against a named metric, which is far easier to review than a log of arbitrary SQL.

Exposing it to an agent

The integration is a single tool. I serve it over MCP (Model Context Protocol, the open standard for plugging tools into agents) so the same definition works in every agent harness the team uses, and so that switching models does not mean rewriting the integration.

{
  "name": "query_metric",
  "description": "Query a governed, certified metric. Use list_metrics and describe_metric to find the right one. Do not write SQL. If no metric matches the question, say so and stop.",
  "inputSchema": {
    "type": "object",
    "properties": {
      "metric": {
        "type": "string",
        "enum": ["assets_under_custody", "average_daily_aum", "net_revenue", "net_flows"]
      },
      "dimensions": {
        "type": "array",
        "items": { "type": "string", "enum": ["client_segment", "asset_class", "legal_entity", "region"] },
        "maxItems": 3
      },
      "time_grain": { "type": "string", "enum": ["day", "month", "fiscal_quarter"] },
      "start": { "type": "string", "format": "date" },
      "end": { "type": "string", "format": "date" },
      "filters": {
        "type": "array",
        "items": {
          "type": "object",
          "properties": {
            "dimension": { "type": "string" },
            "op": { "type": "string", "enum": ["=", "!=", "in"] },
            "values": { "type": "array", "items": { "type": "string" } }
          },
          "required": ["dimension", "op", "values"]
        }
      },
      "known_at": { "type": "string", "format": "date", "description": "Knowledge date: answer with what the warehouse knew then. Defaults to the latest closed period." }
    },
    "required": ["metric", "time_grain", "start", "end"]
  }
}

A few choices in there are deliberate. The metric list is an enum, generated from the semantic layer at deploy time, so the model cannot invent total_revenue_v2. Filters are structured triples rather than a free-text where clause, which closes the obvious injection path. maxItems: 3 on dimensions stops the agent from requesting a 40,000-row cube and then trying to summarize it in context. And known_at defaults to the last closed period, because the most common silent error I see is an agent answering from a half-loaded day.

Alongside query_metric there are two read-only tools: list_metrics and describe_metric. The agent can explore definitions freely. It can only compute through the governed path. There is an escape hatch to raw SQL for analysts who need it, but it is a separate tool, it is logged, and its answers are labeled as uncertified in the response.

Measuring the difference

I ran a small internal test to see whether this was worth the effort. I took 40 questions harvested from our team’s request queue (real questions, lightly anonymized), fixed the data at a closed month, and computed ground truth from certified models. Same model, same system prompt apart from the tool description, two setups: the agent with read access to the warehouse schema, and the agent with only the semantic tool.

Results, 40 questions	Raw SQL agent	Semantic tool agent
Correct answers (34 answerable)	21 (62%)	29 (85%)
Correctly refused (6 should-refuse)	1 of 6	5 of 6
Fan-out or semi-additive errors	7	0
Median tokens per question	~19k	~7k
Median latency	41 s	16 s

This is a small sample and I would not quote the percentages to anyone. The shape is what matters. The semantic agent was no smarter. It had fewer chances to be wrong. All seven grain errors disappeared because the tool cannot express them. Most of the remaining misses were the agent choosing a near-miss metric (net_revenue when the person meant gross), which is a definitions problem I can fix in descriptions. The refusal row surprised me most: given only governed metrics, the model is much more willing to say “there is no metric for customer lifetime value” than to improvise one from three tables.

What goes wrong in practice

The semantic approach has its own failure modes, and I have hit all of these.

The catalog is too small. If the layer has twelve metrics and people ask about forty things, the agent either refuses constantly or everyone routes through the SQL escape hatch, and you are back where you started. Track escape-hatch usage per week; it is your metric backlog.

Descriptions are written for humans who already know. “Net revenue (see finance wiki)” is useless to a model. A good description says what is included, what is excluded, and which question it answers. Half of the semantic agent’s advantage in the test above came from descriptions I rewrote before the final run, not from the tool itself.

Near-duplicate metrics. aum, aum_eod, aum_avg, aum_reporting. The model picks one at random. Deprecate aggressively, and when two metrics must coexist, have each description name the other and say when to use which.

Dimensions that are not conformed. If region means booking region in one model and client domicile in another, the layer will happily slice both by it. The agent cannot detect this; the modeling has to. The entity work from one customer, five systems is a prerequisite here, not a nice-to-have.

The tool returns too much. A metric by day by client for a year is a large table. Cap rows server-side and return a summary plus a pointer, or the model will truncate silently and summarize the part it saw.

Definitions are the contract

The architectural claim is simple. Metric definitions in git used to be BI hygiene, something a disciplined team did so dashboards agreed with each other. With agents in the loop they become the contract between your data and every model that will ever answer a question about it.

That changes who should own them and how they are reviewed. A metric description is now a prompt. A grain declaration is now a guardrail. A missing metric is now a refusal, which is a better outcome than an invented one. If you are building on a ledger-first architecture, you already have certified facts at a known grain; the semantic layer is the thin, reviewed API that lets a model use them without being able to misuse them.

Text-to-SQL will keep improving. The model will still not know what your company means by revenue unless you write it down.