Semantic Layer with MCP for AI Agent Data Trust

Connecting AI agents to data warehouses often leads to inconsistent results. A semantic layer, exposed via Model Context Protocol, offers governed metrics, promising consistency and robust access control.

Connecting AI agents to enterprise data warehouses often begins with text-to-SQL, a method that allows agents to generate SQL from natural language queries. This approach, however, frequently encounters issues in production, including hallucinated JOINs and inconsistent aggregations. A recent post by Max Bonnard on dev.to outlines an alternative: leveraging a semantic layer exposed via Model Context Protocol (MCP) to provide governed metrics, claiming setup can be completed in under 30 minutes.

Text-to-SQL's Production Failures

The core issue with text-to-SQL, as described by Bonnard, is the agent's lack of business context. Agents interpret column names directly, unaware of specific business logic such as refund exclusions from revenue or differing definitions of "completed" status across tables. This leads to inconsistent results, where the same question phrased differently, or asked of different agents, yields varying answers that diverge from finance-reported figures.

Beyond consistency, the direct text-to-SQL method lacks critical enterprise features: no row-level security, no multi-tenancy, and no audit trail for agent queries. These omissions render it unsuitable for production environments handling sensitive data or requiring compliance. Bonnard states, "Text-to-SQL gives you speed. It doesn't give you trust."

Semantic Layer for Governed Metrics

Bonnard proposes defining business metrics once within a semantic layer using YAML. This layer encapsulates cubes, measures, dimensions, and access rules. Instead of agents writing arbitrary SQL against raw tables, they query these pre-defined, governed metrics.

This architecture ensures every agent receives the same answer for a given metric, as the definition (e.g., total_revenue with specific filters and aggregations) is fixed. Updates to metric definitions, such as excluding trial conversions from revenue, propagate instantly across all consumers without retraining agents or patching dashboards. The semantic layer also abstracts the underlying data warehouse dialect, allowing infrastructure changes (e.g., swapping BigQuery for Snowflake) without impacting agent queries.

MCP for Agent Integration and Rapid Setup

The Model Context Protocol (MCP) is presented as the mechanism to expose these semantic layer definitions to AI agents. This allows agents to interact with governed definitions rather than raw data. The post includes a comparative table asserting "Full" governance, "Guaranteed" consistency, "Built-in" multi-tenancy, and "Row-level" access control for the semantic layer via MCP approach, contrasting it with "None" or "Manual" for direct SQL and text-to-SQL.

Crucially, Bonnard claims the semantic layer via MCP setup can be achieved "Under 30 min," a significant reduction compared to "Hours" for text-to-SQL or "Minutes" for direct SQL, which offers no governance. The post references open-source engines like Cube that connect to various warehouses, including BigQuery and Snowflake.

What We'd Change

The claim of "under 30 min" for a production-ready semantic layer setup warrants scrutiny. While standing up a basic instance of an open-source semantic layer like Cube might take minutes, defining a comprehensive set of business metrics in YAML, configuring access rules, and integrating it with an existing data warehouse and multiple AI agents typically requires significantly more time. This estimate likely applies to a minimal proof-of-concept rather than a fully governed, enterprise-grade deployment.

The post omits details on the "one tool so the agent can chart the result in Claude or ChatGPT" step. This is a critical component for practical agent utility, as raw data is less valuable than visualized insights. Without a clear playbook for this integration, the proposed architecture remains incomplete for end-to-end use cases.

For smaller teams or early-stage startups, the overhead of implementing and maintaining a dedicated semantic layer might outweigh the benefits of governance and consistency, especially when data models are still evolving rapidly. Text-to-SQL, despite its flaws, offers a lower barrier to entry for initial exploration, provided the data context is simple and the stakes for accuracy are lower. The trade-off between speed of initial deployment and long-term data trust needs to be explicitly weighed against team size and data maturity.

Landing

The shift from direct text-to-SQL to a semantic layer with MCP represents a move from rapid, unconstrained data access to governed, consistent data consumption by AI agents. This architectural choice prioritizes data trust and scalability over immediate query flexibility. While the initial setup time claims for a semantic layer may be optimistic for complex environments, the underlying principle addresses a fundamental challenge in deploying AI agents responsibly within data-rich organizations.

The investor read

The increasing adoption of AI agents by enterprises necessitates robust data governance and reliability. This trend positions semantic layers, especially those integrating with emerging protocols like MCP, as critical infrastructure. Investors are tracking the market for tools that bridge AI's natural language capabilities with structured, trustworthy enterprise data. Solutions that offer verifiable consistency, auditability, and fine-grained access control for AI-driven analytics will attract capital, particularly as regulatory scrutiny on AI output intensifies. While open-source options like Cube exist, the opportunity lies in managed services or enterprise-grade platforms that simplify deployment, integration, and ongoing management of these complex data architectures. This is a foundational layer play for AI at scale.

Pull quote: “Text-to-SQL gives you speed. It doesn't give you trust.”