TL;DR — Years of effort went into making LLMs better at writing SQL against raw warehouse schemas, but that's solving the wrong layer of the problem. Business logic — what a customer, a churn event, or net revenue actually means — lives in the semantic layer, not the schema, and no amount of prompt engineering recovers it from column names. The real shift underway is treating dbt metrics and semantic layers as the tool contract for agents, not a BI convenience, which also makes them the natural place to put governance and guardrails.
Every text-to-SQL demo looks the same. Someone types a plain-English question, a model spits out a query, the query runs, and everyone claps. Then it goes to production against a real warehouse with four hundred tables, four definitions of "active user," and a customers table that's actually a slowly-changing dimension with four join paths to revenue. The demo dies there. Not because the model got worse at writing SQL — because SQL was never the hard part.
The hard part is business logic, and business logic doesn't live in the schema. It lives in someone's head, in a Slack thread from eighteen months ago, in a dbt model buried three layers deep, or in a spreadsheet macro nobody remembers writing. An LLM staring at column names and foreign keys is doing archaeology, not reasoning. It can infer that orders.status probably has a value like 'completed', but it cannot infer that finance excludes refunded orders from revenue while marketing includes them, or that "active" means logged in during the trailing 28 days for one team and made a purchase in the trailing 90 for another. Those aren't schema facts. They're semantic layer facts, and if they aren't written down somewhere the model can read, the model will confidently make them up.
The industry bet on the wrong fix
For a while, the fix on offer was: better models, bigger context windows, fine-tune on your query history, add a retrieval step over past queries. All of that helps at the margins. None of it solves the actual gap, because the gap isn't linguistic — it's definitional. You can give a model perfect syntax and a flawless understanding of joins, and it will still get the number wrong if it doesn't know that net_revenue subtracts discounts before tax and gross_revenue doesn't. That distinction is a business decision encoded once, ideally in one place, by the people who own the metric. Asking a language model to reverse-engineer it from raw tables every single time is asking it to redo work that a metrics layer already exists to avoid doing twice.
This is the part that's easy to miss if you came to AI engineering from the model side rather than the data side: the modern data stack already solved the "what does this number mean" problem years before LLMs needed an answer. dbt's metrics definitions, and semantic layers like the ones built on top of warehouses, exist precisely to give every consumer — a dashboard, an analyst, a scheduled report — the same answer to "what is churn" without re-deriving it. An agent asking a warehouse a question is just another consumer. It should get the same guarantee.
What the semantic layer actually is, once you see it correctly
Reframe the semantic layer as an API, not a reporting convenience, and a lot clicks into place. A well-built semantic layer exposes named metrics and dimensions with fixed, versioned definitions: revenue, active_users, churn_rate, each with an explicit filter, grain, and join path baked in by someone who actually owns that number. When an agent queries through that layer instead of writing raw SQL against source tables, it isn't guessing at business logic anymore — it's calling a function with a stable contract. The LLM's job shrinks to something it's actually good at: mapping a fuzzy natural-language ask onto the closest matching metric and set of dimensions, and asking a clarifying question when there's genuine ambiguity between two named metrics.
That's a categorically easier problem than text-to-SQL against raw schema, and it's easier in a specific, structural way: the space of legal answers is finite and named, instead of infinite and syntactic. A model choosing between metric: net_revenue and metric: gross_revenue is doing intent classification. A model writing a six-table join with a correct COALESCE and the right filter on a soft-deleted-rows flag is doing something much closer to program synthesis under uncertainty, with no way to check its own work except running it and hoping the number looks plausible.
Governance falls out of this for free
There's a second, less obvious benefit, and it matters more the closer these agents get to production. Once the semantic layer is the interface, it's also the natural place to put guardrails, because it's the one layer that already understands both the data model and the business meaning attached to it. Row-level security, metric-level access control, definitions that are frozen for finance and audit purposes — all of that already exists in mature semantic layer tooling, built for human BI users. Route an agent through the same layer and it inherits the same boundary for free. It cannot query a metric it isn't permitted to see, because the permission lives below the model, not in a prompt instruction the model might ignore under adversarial pressure. Compare that to an agent with raw SQL access to a warehouse, where the only thing stopping it from joining into a restricted table is a system prompt telling it not to. That's not a security boundary. That's a suggestion.
This is also why "just give the agent a read-only SQL connection" is a worse idea than it sounds. Read-only stops destructive writes, but it does nothing about an agent surfacing a number it was never supposed to compute, built on a join it half-understood, presented with total confidence to someone who will put it in a board deck. The failure mode isn't a dropped table. It's a wrong number that looks exactly like a right one.
What this means if you're building the stack, not just using it
If you own a data platform and you're being asked to "make the warehouse talk to the LLM," the instinct to bolt an SQL-generation model onto the warehouse directly is the instinct to resist. The higher-leverage move is investing in the semantic layer itself — naming the metrics that matter, writing down the definitions that currently live in someone's head, and exposing that layer as the tool surface for agents instead of the raw tables underneath it. That work pays for humans too; a semantic layer good enough for an agent to use safely is also good enough to stop your analysts from getting three different answers to "what was revenue last quarter" in three different dashboards, which was always the actual point of building one.
The teams that get this right won't have the flashiest text-to-SQL model. They'll have the most honest metrics layer, and an agent that's only ever allowed to ask it questions it already knows how to answer correctly. That's a smaller, less impressive-sounding claim than "our agent can query any table in natural language." It's also the one that survives contact with a real board meeting.
