Your Data Agent Needs One Definition of Revenue

Text-to-SQL demos never die on syntax. The model writes fluent SQL — better than most junior analysts, honestly — and the query runs, and a number comes back. The demo dies three weeks later, in production, when the CFO notices that the agent's "Q2 revenue" doesn't match the board deck. Not because the SQL was malformed, but because the warehouse contains three defensible definitions of revenue — bookings, recognized, and net-of-refunds — and the model confidently picked one. Just not the one finance uses.

This is the failure mode that matters, and it's invisible to every benchmark you've seen. The fix isn't a better model or a longer prompt. It's a piece of infrastructure most data teams already half-built and then abandoned: the semantic layer. The metrics definitions you wrote for BI dashboards — dbt metrics, LookML, cube definitions — turn out to be the missing tool contract for data agents. The teams shipping reliable agents figured out that the build order is inverted from what everyone assumed: semantic layer first, agent second.

Syntax Was Never the Bottleneck

The academic numbers tell a seductive story. Frontier models score above 90% on Spider 1.0 and around 73% on BIRD, the standard text-to-SQL benchmarks. Reading those leaderboards, you'd conclude the problem is nearly solved.

Then Spider 2.0 arrived — built from real enterprise workflows, with sprawling schemas, multiple SQL dialects, and the kind of documentation debt that actual companies accumulate. The best reasoning models scored around 21%. Same task on paper, a 70-point drop in practice. The gap between the two numbers is exactly the gap between "write SQL" and "know what the data means."

That's because a production warehouse isn't a schema; it's an archaeological site. There's orders, orders_v2, and orders_final_DO_NOT_USE. There's a revenue column that predates the refund policy and a net_revenue column that only some teams know exists. A human analyst survives this environment through tribal knowledge — they know which table the finance team blessed, they remember the Slack thread where "active user" was renegotiated. The model has none of that. It has column names and its priors, and it fills the semantic vacuum with confident guesses.

The cruelest property of this failure mode: small semantic mistakes don't crash. A wrong join or a wrong aggregation grain returns a plausible number, formatted beautifully, delivered with the calm authority of a system that has no idea it's wrong. Silent, scalable error is the most expensive kind, because you don't find it in the logs — you find it in a meeting.

The Metrics Layer You Already Half-Built

Here's the twist. Most data teams already own the artifact that solves this. Somewhere in your dbt project, your LookML files, or your Cube configs, someone has already written down what "revenue" means — the exact aggregation, the exact filters, the exact join path — because dashboards needed a single answer years before agents did.

That metrics layer was usually built grudgingly, adopted partially, and left at 60% coverage when the dashboard migration lost steam. It was treated as BI plumbing. But look at what it actually is: a machine-readable ontology of your business — metrics, dimensions, entities, and the relationships between them, version-controlled and owned by the people accountable for the numbers.

That is a tool contract. It's exactly the shape of thing an agent needs.

When you expose governed metrics as the agent's query surface instead of raw tables, the division of labor changes fundamentally:

• The model does what models are good at: decomposing a fuzzy natural-language question into a structured request — this metric, grouped by that dimension, filtered to this window.
• The semantic layer does what compilers are good at: deterministically generating correct SQL from that structured request. The joins are pre-declared. The aggregation grain is encoded. There is no step where the model improvises business logic.
• The organization does what organizations must do anyway: agree, once, in a reviewable file, on what the words mean.

The agent inherits the org's agreed-upon definitions instead of re-deriving them per query. "Revenue" stops being a token the model interprets and becomes a symbol the model references.

Loading…