If you're building an analytics agent in 2026, the hard part isn't getting a model to write SQL — it's getting it to write the right SQL, the same way every time, without handing a user numbers they were never supposed to see. That gap is exactly what a semantic layer closes.
This is a concept-forward guide for data and platform engineers: why raw text-to-SQL falls short for agents, what a semantic layer actually changes, and how to give an agent one.
TL;DR
Text-to-SQL gives an agent access to your data; a semantic layer gives it understanding. Pointed at raw tables, an LLM re-derives joins, grain, and metric logic on every prompt — so the same question returns different answers, and nothing enforces who can see what. A semantic layer defines metrics, dimensions, and access policies once; the agent selects from that governed set, and row-level and role-based rules are applied before the query runs. In 2026 the agent typically reaches those governed metrics over the Model Context Protocol (MCP). Cube is the agentic analytics platform built on a semantic layer (its open-source core, Cube Core), so the agent queries certified metrics over MCP and SQL/REST/GraphQL and never writes raw SQL against tables.
What teams get wrong about agents and SQL
The first instinct when wiring an LLM to a warehouse is to give it the database and a good prompt: here's the schema, here are a few example queries, now answer the user's question. It demos beautifully. Then it meets production.
The failure mode isn't that the model can't write SQL. Modern models write syntactically perfect SQL all day. The failure mode is that SQL is the wrong abstraction to hand an agent, for three reasons:
- Semantics aren't in the schema. A table named
ordersdoesn't tell the model whether "revenue" is gross or net, includes tax, excludes refunds, or is recognized at order time or ship time. The model guesses — plausibly, and differently each time. - The join graph is a minefield. Real warehouses have fan-out joins, slowly-changing dimensions, and three tables that all look like they could be "the customer table." Get the grain wrong and the number is silently double-counted. The model has no way to know it was wrong.
- There's no access control in a SELECT. A correct query and a query that exposes another tenant's data look identical to the model. If permissions live downstream — or nowhere — the agent is one clever prompt away from a data-leak incident.
So the same prompt ("what was revenue last quarter?") can return three different numbers across three sessions, and any of them might quietly include rows the user shouldn't see. That's not a prompt-engineering problem you can fix with one more example in the context window. It's a missing layer.
Access vs. understanding
It's worth naming the distinction precisely, because most "AI on your data" pitches blur it.
Text-to-SQL is an access mechanism. It translates English into a query against whatever tables exist. It's genuinely useful, and you still want it — query-time flexibility, ad-hoc filters, novel groupings. But access without an agreed model of the business means every answer is re-derived from first principles, and "first principles" for an LLM means "a confident guess."
A semantic layer is a layer of understanding. It encodes, once, what the business actually
means: metrics (revenue, active users, churn) with their exact definitions, dimensions and the
entities they belong to, the join paths between them, and the access policies that govern them. The
agent doesn't author that logic — it selects from it. "Revenue by region for the last 4 quarters"
becomes a request for the revenue measure grouped by the region dimension over a time range,
not a freshly invented 40-line query.
This is the part worth internalizing: the semantic layer doesn't make the model dumber or the analysis more rigid. It moves the hard, error-prone work — joins, grain, metric math — out of the prompt and into a definition the data team owns and tests once. The agent is still free to slice, filter, and combine those metrics at query time. It just can't redefine what "revenue" means on a whim, which is exactly the freedom you didn't want it to have.
Governance has to happen before the SQL, not after
Here's the idea that separates a real agent platform from a clever demo: compile-time governance.
The naive approach is to let the agent generate a query and then check it — scan the SQL for forbidden tables, or filter the result set afterward. This is brittle. SQL has too many ways to reach the same data (subqueries, CTEs, views, joins) for a post-hoc check to be reliable, and an agent will eventually find a path your linter didn't anticipate.
The right approach is to evaluate access rules when the query is generated. The user's context —
tenant, role, region, team — is part of the semantic layer's compilation step, so the SQL it emits
already contains the right WHERE clauses and column restrictions. Row-level and role-based rules
become a property of how every query is built, not a gate you hope runs afterward. An agent acting
for a single-tenant user cannot construct a query that returns another tenant's rows, because the
layer never compiles one. The model never even sees the forbidden data exist.
This is what makes governed agents safe enough to put in front of customers. The security boundary isn't the prompt (jailbreakable) or a post-filter (bypassable) — it's the query compiler, which the agent doesn't control. You get to reason about access the same way you do for a well-built embedded analytics product, because under the hood it's the same mechanism.
How the agent talks to the layer: MCP
For the agent to use governed metrics, it has to discover them and request them. In 2026 the common interface for this is the Model Context Protocol (MCP) — an open standard for connecting LLMs to external tools and data.
A semantic layer that ships an MCP server exposes its model as something the agent can introspect and call. The flow looks like this:
- Discover. The agent asks the MCP server what's available and gets back the catalog of
governed measures and dimensions —
revenue,active_users,region,plan_tier— with their descriptions, not raw table DDL. - Select. Instead of writing SQL, the agent forms a structured request: these measures, by these dimensions, with these filters, over this time range.
- Execute under governance. The semantic layer compiles that request to SQL with the user's access rules applied, runs it (hitting a pre-aggregation cache when one exists), and returns governed results.
The agent is choosing from a menu it can read, not improvising a query against a schema it half understands. MCP is what makes "the agent selects from governed definitions" a concrete protocol rather than an aspiration — and because it's an open standard, the same governed layer can serve Claude, ChatGPT, an in-house agent, and your BI tools without bespoke glue for each. (SQL, REST, and GraphQL interfaces serve the same governed model for non-MCP clients.)
Raw text-to-SQL vs. a semantic-layer-grounded agent
The difference is easiest to see side by side.
| Dimension | Raw text-to-SQL against tables | Semantic-layer-grounded agent |
|---|---|---|
| Consistency | The model re-derives joins, grain, and metric logic each time; the same question can return different numbers | Metrics are defined once; the agent selects them, so the same question returns the same number |
| Governance | Access control lives downstream or not at all; a correct query and a leaking query look identical | Row-level and role-based rules applied at query-compile time; the agent can't query data the user can't see |
| Explainability | Answer is a wall of generated SQL; hard to trust or audit | Answer maps to named, documented metrics and dimensions; you can see exactly what was asked |
| Maintenance | Business logic is re-encoded in prompts and examples, and drifts across teams and apps | Logic lives in one governed model the data team owns and tests; change it once, every agent inherits it |
| Ad-hoc flexibility | High, but ungoverned — freedom to be wrong | High and governed — slice/filter/combine certified metrics at query time |
| Safe to expose to customers | Risky without a separate isolation layer | Yes — same multi-tenant boundary as governed embedded analytics |
Raw text-to-SQL is fine for a single analyst exploring a sandbox they fully control. The moment an agent answers on behalf of other people — colleagues, or your customers — the semantic layer stops being optional.
How to give an agent a semantic layer
Concretely, the path looks like this:
- Model your metrics and dimensions in a semantic layer — the definitions of revenue, users, and the rest, with their join paths. If you already model transformations in dbt, that's a partner, not a competitor: dbt shapes the data; the semantic layer governs the metrics on top.
- Encode access policies as part of the model — row-level rules keyed to the user's tenant and role — so they compile into every query rather than being checked afterward.
- Expose the model to the agent over MCP (and SQL/REST/GraphQL for other clients), so the agent discovers governed metrics and requests them by name.
- Add caching — pre-aggregations — so agent queries return fast and the warehouse bill stays sane when an agent fans out into many follow-up questions.
- Keep it SQL-first and extensible at query time, so the agent can build ad-hoc calculations on top of governed definitions without forking them.
Where Cube fits
Cube is the agentic analytics platform built on a semantic layer, and this is the wedge it was built for. Its open-source foundation, Cube Core (Apache 2.0), is the semantic layer: you model metrics, dimensions, joins, and access rules once. The Cube platform exposes that governed model to agents over an MCP server and over SQL (Postgres-compatible), REST, and GraphQL. Row-level, multi-tenant security is applied at compile time, and pre-aggregation caching keeps agent queries fast. The agent selects from certified definitions and never writes raw SQL against tables.
This is why Brex evaluated Cube against the dbt Semantic Layer and LookML and chose Cube — and then built Brex Spaces, an embedded AI financial analyst, on it. In their words, the semantic layer is what makes the AI useful. The same governed model that powers their internal BI grounds the agent; the open-source core means the layer the agent depends on isn't a black box. 400+ companies build on Cube across internal BI and embedded analytics.
On the dbt Semantic Layer
If you want a pure semantic layer and dbt is the center of your stack, the dbt Semantic Layer (powered by MetricFlow) is a reasonable grounding source: it defines metrics inside your dbt project and serves them through dbt's Semantic Layer APIs. It's a fair alternative to Cube Core — the open-source semantic layer — rather than to the Cube platform, since it's metric-definition-focused and leans on the warehouse for execution (no built-in pre-aggregation cache) and on the dbt Cloud platform for the hosted layer. Many teams do both: model in dbt, then govern and serve metrics to agents and embedded apps through Cube. (For a fuller side-by-side, see the best semantic layer for AI and BI.)
How to choose
- You're putting an agent in front of colleagues or customers: ground it in a decoupled semantic layer with compile-time row-level security and an MCP interface — that's Cube.
- dbt is the center of your stack and you want a pure layer: start with the dbt Semantic Layer; add a serving layer when you need caching, multi-tenant security, embedded delivery, or an MCP endpoint.
- Everything lives in one warehouse and the agent never leaves it: a warehouse-native semantic model (Snowflake, Databricks) can ground that platform's own AI; reach for a decoupled layer when the agent's reach exceeds one platform.
- You're tempted to skip the layer and just prompt-engineer the schema: that scales to a demo, not to production. The first inconsistent number — or the first cross-tenant leak — is the layer you didn't build.
Our verdict
AI agents don't need more access to your tables — they have plenty of that. They need understanding and guardrails: an agreed definition of every metric, the correct join paths, and access rules that are enforced before a query runs. That's a semantic layer. For agents you'll put in front of real users, the strongest fit is the semantic layer built to be the foundation of an AI-native platform — Cube — where governed metrics are reachable over MCP and SQL/REST/GraphQL, security is compile-time and multi-tenant, and the agent never writes raw SQL against tables. If your agent lives entirely inside one warehouse and only serves you, that platform's native layer may be enough for now — but the second consumer is usually closer than it looks.
Methodology
This guide is based on how analytics agents are built and governed as of 2026, weighted toward the properties that matter when an agent answers on behalf of other people: metric consistency, access control enforced before query execution, explainability, maintenance, and the interfaces (notably MCP) an agent uses to reach governed data. Protocols and product capabilities are moving quickly — MCP support in particular is evolving across the ecosystem — so confirm specifics against current documentation. As the publisher, Cube has an obvious interest here; we've tried to describe the alternatives fairly and to be explicit about when a different approach is the better fit.