Why AI Analytics Agents Need a Semantic Layer Instead of Raw SQL

Letting an AI analytics agent write SQL is the quickest way to get a demo that dazzles people. It's also the quickest way to get a hazard, if that SQL runs against raw warehouse tables with no business context. Syntax isn't where things go wrong. Meaning is.

Take the word "revenue" and ask what it covers:

• Gross or net?
• Are refunds taken out?
• Which date column decides the quarter?
• Does "active customer" mean active when the order was placed, or active today?
• Which region mapping applies?
• Which currency conversion rule?
• Is this user even allowed to see that customer's data?

Raw text-to-SQL breaks down on exactly these points. The model can emit valid SQL, the database can run it without complaint, and the number can still be wrong. For that reason an analytics agent needs a semantic layer between it and the business data. Cube is one well-built example of such a layer, and it serves as the running case below.

Schemas don't carry meaning

Text-to-SQL is a good idea. It lets developers, analysts and business users move quicker. But a warehouse schema seldom holds the meaning a person expects from it.

A table offers columns like amount, status, created_at, customer_type, region_id and currency, and nobody in the business phrases a question that way. Nobody asks for sum(amount) grouped by region_id where status = 'paid'. They ask how enterprise revenue grew in South India last quarter.

That single sentence hides a stack of decisions. What is revenue, and what makes a customer enterprise? Which date marks the quarter, and how is South India mapped? Are refunds and canceled orders removed? Which currency rule applies, and what is the asker allowed to see? A model can guess at each. Guessing isn't governance, though. If the agent's answer differs from the dashboard, people stop trusting it. If it joins the wrong table, the figure looks believable and is off. If it ignores access rules, the answer becomes a data leak.

The real question, then, isn't whether the model can write SQL. It's whether the model should be inventing business logic at all.

What a semantic layer does

A semantic layer sits between raw data and whoever consumes it, converting technical database structures into governed business concepts. It defines metrics, measures, dimensions, joins, segments, hierarchies, access rules, caching rules, pre-aggregations and the APIs used to query them, instead of exposing tables and columns.

Concretely, orders.amount, customers.type, orders.created_at and refunds.amount become Revenue, Enterprise Customer, Order Date and Refund-Adjusted Revenue. Agents need that vocabulary. With no semantic layer, an agent looks at a schema and infers. With one, it works from approved definitions. The agent still isn't perfect, but a major cause of analytics hallucination goes away, because the metric definition no longer has to be reconstructed from raw tables.

It's a category rather than one product. Cube, the dbt Semantic Layer (built on MetricFlow) and Looker's LookML each tackle the same problem in their own ecosystems. Cube gets the attention here because its story for agents is unusually complete, though the architecture matters more than the brand.

Why Cube makes a good example

Cube isn't only a dashboard layer. A single governed model feeds BI tools, embedded analytics, APIs and AI agents alike, built from cubes, measures, dimensions, joins, segments, hierarchies, pre-aggregations and access policies. Those are close to the exact building blocks an analytics agent needs.

Its own description of the design captures the thesis well: the agents don't query the warehouse directly, they query the semantic layer, and the runtime acts as a guardrail between agent and warehouse.

Compare what each one hands the model. Given a raw table, it sees column names and nothing else. Given a semantic layer, it also receives the approved concepts, the calculation behind each, how they relate, what the current user may touch and a safe way to query. Rediscovering business logic on every question is wasteful, so the agent should query a layer that already holds it.

Connection plus meaning: where MCP fits

MCP is the common way to connect AI assistants to outside tools and data, yet connecting isn't the same as understanding. Hand an agent direct access to raw warehouse tables over MCP and it still has to guess at meaning, which is the original problem in new packaging.

A stronger pattern routes the agent through the semantic layer:

AI agent
→ MCP client
→ semantic-layer MCP server (e.g. Cube's)
→ semantic layer (governed metrics, dimensions, joins, policies)
→ data warehouse

MCP handles access and the semantic layer handles meaning. The agent shouldn't ask the database which tables to join in order to compute revenue. It should ask the semantic layer for the approved revenue metric and the dimensions it can use.

Two caveats keep this honest, since the area moves fast and is easy to overstate. The official Cube MCP server, part of Cube Cloud and reached over HTTPS with OAuth, is a different thing from community MCP servers for Cube, such as one distributed through Smithery. Their trust, support and maintenance differ, and production use calls for the official, governed route. Also, native MCP support is a recent arrival in this space, layered over the REST, SQL and GraphQL APIs these products have offered for years. It works, but it's 2025-to-2026 functionality, so check it against the version you actually run.

A safer architecture end to end

Assembled in full, the flow looks like this:

user question
→ AI analytics agent
→ MCP / tool call
→ semantic layer (approved metrics, dimensions, joins, segments, policies)
→ data warehouse
→ governed result
→ agent explanation

The agent doesn't get free rein over raw data. It gets a governed interface, so the team controls which metrics exist, which dimensions are valid, how joins work, which filters are permitted, who sees which rows, whether answers come from cache or pre-aggregated data, and how queries are exposed over APIs. That beats telling a model to write SQL and hoping the business logic comes out right.

Worked example: revenue growth

Ask for last quarter's enterprise revenue growth. With no semantic layer, the agent infers which table holds revenue, whether that means gross, net, booked or recognized, whether refunds drop out, how enterprise accounts are identified, which time column defines last quarter, whether currencies need converting and whether this user can view every segment. The SQL may run, but running isn't the same as being right.

With a semantic layer, those answers already exist. Revenue is an approved measure, Enterprise Customer is an approved segment, Order Date is an approved time dimension, the refund logic is defined once and the access policy applies according to who's asking. The agent isn't making up a metric, it's requesting an approved one. That separates natural-language SQL generation from governed agentic analytics.

What can go wrong with raw warehouse access

Direct access leaves the agent too much room to guess, and the failures are predictable:

• Metric invention: revenue gets computed differently from the finance dashboard.
• Wrong joins: the query runs, but a join duplicates rows or drops records.
• Missing filters: canceled orders, test accounts, deleted records or internal users stay in.
• Permission leakage: a legitimate question returns data from customers, teams or regions the person shouldn't see.
• Stale logic: the schema changed, and the agent keeps using the old pattern.
• Plausible wrong answers, the worst case, where confident numbers look reasonable and are incorrect.

Agents answering business questions need stronger guardrails than SQL generation provides. They need semantic constraints.

Keeping SQL without exposing raw tables

Giving up flexibility isn't the price of governance. A mature semantic layer can keep a SQL door open, pointed at governed objects instead of physical tables. In Cube's SQL API every cube or view looks like a table, and its measures, dimensions and segments look like columns. Because it uses the Postgres wire protocol and dialect, nearly any Postgres-capable client can connect.

Tools and agents keep SQL-style querying, but the model shapes what can be queried. The agent isn't wandering through raw tables. It's working with approved metrics and dimensions, a practical middle ground that sits much nearer to how production analytics ought to run.

Access rules count as much as metrics

For conversational agents, access control becomes critical, since a chat box invites open-ended questions. Someone types "show me revenue by customer," and the system has to know which customers that person may view, which region or tenant applies, and whether customer-level detail is allowed or only aggregates should come back.

Cube applies access policies and row-level security inside the semantic layer. That placement is deliberate: nobody should rely on an agent to recall every rule unprompted. Writing "never reveal unauthorized data" into a prompt is a request. Having the layer restrict a given user to certain rows, dimensions and measures is enforcement. Enforcement is much stronger, and it lives in the data layer instead of the prompt.

The performance side: caching and pre-aggregations

Conversational analytics is iterative. A person asks for revenue by region, then splits it by product, then compares with last quarter, then limits it to enterprise customers. If each step becomes a new complex warehouse query, latency and cost climb fast.

Cube's pre-aggregations and caching, handled by its CubeStore engine, speed up common analytical queries. One reported outcome was roughly halved warehouse costs after rollups moved into Cube's store. So for repeated questions, the semantic layer serves as a performance layer in addition to a governance one.

Where the pattern pays off

Agents that answer questions about revenue, customer segments, product usage, embedded customer-facing analytics, operational dashboards, KPIs or metric explanations gain the most, and so does any self-service BI scenario.

The payoff is biggest where recurring definitions already exist. Model ARR, MRR, revenue, churn, retention, active users, conversion rate, gross margin, ticket resolution time and feature adoption once, and every agent reuses them rather than recalculating each time a prompt changes.

Where teams should be careful

None of this is magic, and the governance has to be real rather than a label. An incomplete model leaves the agent struggling. Wrong metric definitions produce governed but wrong numbers. Overly broad access policies still over-expose data. A model crowded with confusing near-duplicate metrics lets the agent pick the wrong one. And if freshness isn't visible, an answer can look current while sitting on stale data.

So the useful question isn't whether a semantic layer exists. It's whether that layer is good enough for an agent to use safely. The model has to be documented, versioned, tested, access-controlled and understandable, line up with the dashboards people already trust, connect to data-quality checks and be clear about freshness and limits. A weak layer yields weak agent answers.

From raw SQL to governed analytics

What differs between approaches is what the agent sees and what can break:

• Raw text-to-SQL: the agent sees warehouse tables and columns, and the main risk is invented business logic.
• SQL plus schema documentation: an improvement, yet joins and metrics still get misused easily.
• A semantic layer alone: the agent works with defined metrics, dimensions, relationships and policies. Safer, with quality capped by the model behind it.
• Semantic layer plus MCP: governed analytics exposed as agent tools, currently the best fit for controlled AI analytics.

Text-to-SQL stays useful, but for business analytics it should run against governed semantics instead of guesses about a raw schema.

A checklist before an agent touches business data

• Put the core metrics in a semantic layer first.
• Spell out which dimensions and joins are legitimate.
• Enforce access policies and row-level rules in the layer.
• Offer governed APIs where raw tables used to be.
• Reach the layer over MCP only through approved, official tooling.
• Keep a log of every agent query along with the SQL it produced.
• Ship each answer with the definition of the metric behind it.
• Report how fresh the data is inside every response.
• Replay familiar business questions and compare the results with trusted dashboards.
• Shut off direct access to sensitive warehouse tables.
• Decide what happens when a requested metric has no definition.
• Teach the agent to answer "not defined" rather than improvise.
• Version metric definitions like code.
• Try access rules out with real user roles.
• Watch for queries that error out or give ambiguous results.

The rule that matters most: when a metric isn't defined, the agent shouldn't make one up. It should ask for clarification or say the metric isn't available.

Questions for product teams

Before giving a new analytics agent your full warehouse schema, pause and settle a few things first. Decide the business questions it should answer, the approved metrics, the valid dimensions and the joins that are safe. Work out who can see what, how fresh the data has to be, which answers need a definition or citation attached, and which queries must be refused. Only then expose that narrowed surface to the agent.

Cube is one way to build the surface, and the dbt Semantic Layer, LookML and MetricFlow approach the same problem from other angles. The lasting lesson has nothing to do with a product. Agents need governed business semantics before they touch business data.

The core point

Treating AI analytics as purely a text-to-SQL task misses the point, since governance of meaning is the harder half. An agent pointed at raw warehouse tables can write perfectly valid SQL and still report the wrong business answer, through invented metrics, bad joins, skipped access rules or figures that contradict the dashboard. A semantic layer swaps the interface: approved metrics, dimensions, joins, policies and APIs replace a raw schema to be interpreted. MCP supplies the connection, the semantic layer supplies the meaning, and governed analytics needs both.