Agents Don't Query Your Warehouse — They Query Your Semantic Layer

Table of Contents

1. The Warehouse Trap: Why Text-to-SQL Breaks at Scale
2. The Semantic Layer as the Agent’s Compass
3. Reference Architecture: Agents + Semantic Layer + Warehouse
4. Metric Definition Governance: Making Metrics Trustworthy
5. The Eval Harness: Measuring Query Accuracy in Production
6. AI Model Selection for Agentic Analytics
7. From Architecture to ROI: What This Means for Mid-Market and PE
8. Getting Started: PADISO’s Approach
9. Summary and Next Steps

A mid-market COO asks a simple question: “What was our net revenue retention last quarter, broken out by product line?” The agent fires off a text-to-SQL query straight against the production warehouse. Thirty seconds later, the COO gets a number that’s wrong — off by 14 percentage points because the agent joined on the wrong version of the customer dimension, missed the currency conversion logic, and treated trial accounts as paying. The board meeting is in an hour. That’s not an edge case; it’s the default outcome when agents query a warehouse without a semantic layer.

Gartner projects that 60% of MCP-only agentic analytics efforts will fail by 2028 — not because the models aren’t smart enough, but because raw schemas don’t carry business meaning. A governed semantic layer is what makes agentic analytics survivable, and it’s the architecture PADISO, the venture studio and AI transformation firm led by Kevin Kasaei, deploys for mid-market brands, scale-ups, and private-equity portfolios that need AI to deliver measurable ROI, not hallucinations dressed up as dashboards.

This guide is the reference architecture for the data cluster. It covers why text-to-SQL degrades at enterprise schema size, how a semantic layer turns an agent into a trustworthy analyst, and the operational pieces — metric governance, an eval harness for query accuracy, and model selection — that turn a prototype into a production system you can bet a board deck on.

The Warehouse Trap: Why Text-to-SQL Breaks at Scale

Text-to-SQL sounds like magic. Give a model a natural-language question, let it generate a query, and return an answer. The demos are flawless on five tables and a hundred rows. The problem starts when you point the same agent at a real warehouse — 2,000 tables, 40,000 columns, nested views, slowly changing dimensions, and logic that exists only in the heads of three senior analysts.

The agent doesn’t know that customer_latest is the correct dimension table, not customer_raw. It doesn’t know that revenue must be calculated net of refunds, chargebacks, and FX adjustments. It doesn’t know that invoice_date and recognition_date differ under ASC 606. Without a semantic layer, the model guesses. And on a schema of enterprise size, it guesses wrong more often than right.

We see this across PADISO’s platform engineering engagements in the United States and Australia. Companies that built impressive data warehouses on Snowflake, BigQuery, or Redshift suddenly hit a wall when they tried to layer AI on top. The warehouse had all the data; it just didn’t have the meaning. For instance, a Chicago-based logistics firm had a perfectly tuned operational data pipeline but no way for an agent to distinguish between “booked revenue” and “billed revenue” — two concepts that share the same column name across different schemas. The agent produced answers that looked plausible and were completely wrong.

This is why the AWS prescriptive guidance on semantic layers for agentic AI calls for an ontology reasoning layer that sits between the agent and the data. The guidance makes clear that virtual knowledge graphs and governed business definitions are not optional add-ons — they are the foundation. Without them, you’re asking a model to be both a database engineer and a domain expert simultaneously, a combination that fails under pressure.

The Semantic Layer as the Agent’s Compass

A semantic layer is a governed translation plane that sits between your data warehouse and any consumer — human analyst, BI tool, or AI agent. It maps physical table names like fct_trx_v2_2024_q3 to business terms like “Net Revenue.” It encodes join paths, aggregation rules, time-series logic, and security policies. When an agent queries the semantic layer instead of the raw warehouse, it’s no longer guessing. It’s asking a curated, version-controlled set of definitions that the business has already validated.

Data Meaning: Why the Semantic Layer Is the Brain of Agentic Analytics puts it bluntly: the semantic layer is the brain of agentic analytics. It provides the context that turns a language model from a pattern matcher into something that reasons about business logic. The article argues that agents need governed meaning, not raw tables — a principle that echoes through every successful production deployment we’ve seen.

In practice, the semantic layer handles several critical functions that raw text-to-SQL cannot:

• Business vocabulary normalization: Translating “churn,” “attrition,” and “logo loss” into a single, auditable metric definition.
• Join logic: Pre-defining the correct path from fact to dimension, so the agent never performs a many-to-many join that inflates revenue.
• Aggregation patterns: Knowing that “monthly active users” is a distinct count, not a sum, and that “average deal size” must be weighted by contract value.
• Temporal reasoning: Handling fiscal calendars, 4-4-5 retail calendars, and trailing-twelve-month windows without the agent having to reconstruct them in SQL.
• Row-level security: Enforcing that a regional VP only sees their territory’s data, even when the agent writes a query that would otherwise return everything.

Semantic Layers in Data Warehouses Explained details how this layer translates technical data into business definitions and governed metrics. It’s the difference between an agent that says “here’s a number” and an agent that says “here’s the number, here’s exactly how it was calculated, and here’s the lineage back to the source table.” That audit trail is non-negotiable when the output drives a board decision or a PE portfolio review.

Reference Architecture: Agents + Semantic Layer + Warehouse

The reference architecture we deploy at PADISO for agentic analytics places the semantic layer as the mandatory intermediary between any AI agent and the data warehouse. The diagram below captures the data flow, governance hooks, and the eval feedback loop that keeps accuracy measurable.

graph TD
    User[Business User / Operator] -->|Natural Language Query| Agent[AI Agent]
    Agent -->|Resolves Intent| SL[Governed Semantic Layer]
    SL -->|Metric Definitions, Joins, Security| QE[Query Engine]
    QE -->|Optimized SQL| DW[Data Warehouse / Lakehouse]
    DW -->|Result Set| QE
    QE -->|Structured Answer| Agent
    Agent -->|Response + Explanation| User
    SL -.->|Governance Feedback| Gov[Metric Governance Registry]
    Gov -.->|Policy Updates| SL
    Agent -.->|Query + Result Pair| Eval[Eval Harness]
    Eval -.->|Accuracy Scores, Drift Alerts| Gov

The architecture separates concerns cleanly. The agent never touches a physical table name. It interacts exclusively with the semantic layer’s API — a set of governed metrics, dimensions, and filters. The query engine translates those semantic requests into warehouse-optimized SQL, handling dialect differences between Snowflake, BigQuery, and Redshift. The eval harness continuously scores query accuracy, feeding anomalies back into the governance registry so metric definitions improve over time.

This pattern shows up in our platform development work in New York for financial services, where low-latency data platforms must serve both human analysts and AI agents without compromising consistency. The same architecture underpins embedded analytics in Los Angeles for media and entertainment companies that need content rights data surfaced through natural-language interfaces.

Agentic Analytics & the Semantic Layer: The Complete Guide describes this separation in detail, noting that the semantic layer translates physical schemas into AI-reasonable business meaning. The guide reinforces a point we make in every AI strategy and readiness engagement: you cannot bolt a semantic layer onto a broken data model. The model must be clean first. That’s why our AI readiness test starts with data maturity, not model selection.

Metric Definition Governance: Making Metrics Trustworthy

A semantic layer without governance is just another source of inconsistency. If two teams define “Gross Margin” differently — one including shipping costs, the other excluding them — the agent will inherit that ambiguity and produce results nobody trusts. Metric definition governance is the discipline of making every business metric a version-controlled, stakeholder-signed artifact.

The governance registry stores each metric as code, not documentation. A metric like “Net Revenue Retention” isn’t a paragraph in a wiki; it’s a defined calculation with explicit source columns, filter conditions, and aggregation logic. When the finance team changes the definition — say, to exclude one-time setup fees from NRR — the change propagates through the semantic layer instantly, and every agent query picks up the new logic. There’s no lag, no stale dashboard, and no argument in the boardroom about which number is right.

What is a Semantic Layer? A Detailed Guide from DataCamp underscores that the semantic layer’s value comes from the business vocabulary, rules, and relationships it encodes. Those rules must be governed with the same rigor as production code. At PADISO, we treat metric definitions as part of the platform engineering discipline — versioned in Git, reviewed in pull requests, and tested with the same CI/CD pipeline that ships the warehouse models. This is the approach we bring to platform development in Boston for biotech firms that need GxP-aware data platforms, where a wrong metric isn’t just embarrassing — it’s a compliance risk.

Semantic Layers Evolving with Agentic AI traces the evolution from static metric layers to dynamic, ontology-driven systems that incorporate knowledge graphs and LLM orchestration. The article highlights that as agents become more autonomous, the semantic layer must become more expressive — encoding not just what a metric is, but why it exists, what it influences, and who owns it. That ownership model is critical in PE roll-ups, where a newly acquired company’s metrics must be mapped into the portfolio’s standard definitions within weeks, not quarters.

The Eval Harness: Measuring Query Accuracy in Production

You can’t improve what you don’t measure. The eval harness is the feedback loop that tells you whether your agent is getting better or worse over time. It captures every query the agent issues, the result it returned, and — critically — a human-validated correctness score. Without this harness, you’re flying blind, hoping the semantic layer is doing its job.

A production-grade eval harness works as follows:

1. Log every agent interaction: Store the natural-language question, the resolved semantic query, the generated SQL, and the final answer.
2. Sample for human review: Not every query needs a human in the loop, but a statistically significant sample does. Reviewers — typically data stewards or domain experts — label each answer as correct, partially correct, or incorrect.
3. Compute accuracy metrics: Track precision, recall, and a business-impact score that weights errors by the financial materiality of the question. An error on “total revenue” matters more than an error on “number of support tickets.”
4. Feed errors back into governance: When a query fails, the harness opens a ticket in the metric governance registry. The metric owner investigates whether the semantic definition needs refinement or whether the agent’s reasoning pattern needs a guardrail.
5. Automate regression testing: Build a golden dataset of known questions and correct answers. Run it against every new version of the semantic layer and every model update. If accuracy drops, block the release.

This harness is what separates a demo from a production system. In our CTO as a Service engagements, we often find that companies have deployed agents with no eval infrastructure at all. They’re making decisions on numbers they can’t validate. The eval harness closes that gap and gives the board — and the PE sponsor — confidence that the AI isn’t drifting.

What is an Agentic Semantic Layer? from ThoughtSpot defines an agentic semantic layer as a governed semantic foundation that translates natural-language intent into policy-aware queries. That definition implies an eval loop: policy-aware means you can audit whether the policy was followed, and that audit requires a harness. The ThoughtSpot white paper on building and governing a semantic layer that scales with agents reinforces the same point — governance without measurement is theater.

AI Model Selection for Agentic Analytics

The model you choose for agentic analytics matters, but not as much as the architecture around it. That said, the current generation of frontier models brings specific capabilities that change what’s possible.

The Claude 5 family — Opus 5 and Sonnet 5 with 1M-token context windows, and Fable 5 as the most capable widely released model — excels at the multi-step reasoning that semantic-layer queries demand. When an agent needs to decompose a question like “show me month-over-month revenue growth by region, adjusted for currency, excluding intercompany transactions,” the model must hold the entire metric definition, the dimensional model, and the query plan in context simultaneously. A 1M-token window makes that feasible without chunking or summarization losses. Haiku 4.5, with its 200K-token context and fast inference tier, works well for simpler, high-volume queries where latency matters more than reasoning depth.

Competing models — GPT-5.6 Sol and Terra, Gemini 3, Kimi K3, and various open-weight models — are closing the gap, but the architectural decision remains the same: the model is a component, not the solution. A model without a semantic layer will produce confident-sounding wrong answers regardless of how advanced it is. The semantic layer is what makes model selection a performance optimization rather than a correctness gamble.

For cost-tiering, some organizations keep Opus 4.8 or Sonnet 4.6 in the mix for workloads that don’t require the full context window or reasoning depth of the Claude 5 series. That’s a legitimate optimization, provided the eval harness confirms that accuracy doesn’t degrade on the cheaper tier.

From Architecture to ROI: What This Means for Mid-Market and PE

Mid-market companies and private-equity portfolios don’t deploy agentic analytics for the technology. They deploy it for the outcome: faster decisions, fewer analyst hours wasted on manual queries, and a single source of truth that survives acquisitions. The semantic layer architecture delivers those outcomes in measurable terms.

Consider a PE firm running a roll-up of six HVAC service companies. Each arrives with its own ERP, its own chart of accounts, and its own definition of “job profitability.” Without a semantic layer, the portfolio analytics team spends months normalizing data and still can’t answer a simple question like “which region has the highest EBITDA margin?” An agent pointed at the raw warehouse would produce six different answers, each reflecting the acquired company’s local logic.

With a governed semantic layer, the roll-up playbook changes. During due diligence, the target’s data model is mapped to the portfolio’s standard metric definitions. Post-close, the semantic layer absorbs the new entity within weeks. The agent can answer cross-portfolio questions on day one of integration. That’s the kind of portfolio value creation that PE operating partners call PADISO about — tech consolidation that drives EBITDA lift, not just cost takeout.

We see this pattern in our Sydney-based fractional CTO engagements with Australian scale-ups backed by PE. The semantic layer becomes the connective tissue between the acquired entities and the platform’s reporting stack. It’s also the foundation for AI advisory in financial services, where APRA and ASIC compliance demands an audit trail that raw text-to-SQL can’t provide.

For mid-market operators not yet in a PE portfolio, the semantic layer is a readiness play. It makes the company more attractive to buyers because the data is already governed, the metrics are already auditable, and the AI stack is already producing trusted numbers. That’s a due-diligence advantage that translates directly to valuation.

Getting Started: PADISO’s Approach

PADISO’s engagement model for agentic analytics starts with a Venture Architecture & Transformation assessment that maps your current data landscape against the reference architecture above. We identify the gaps — missing semantic definitions, ungoverned metrics, no eval harness — and build a 90-day plan to close them.

For PE firms, the conversation often begins with a roll-up thesis. We’ll look at the target portfolio, map the data consolidation path, and design a semantic layer that scales across acquisitions. The goal is a platform that gets stronger with each add-on, not more fragmented. Our case studies show how this approach has delivered measurable AI ROI for companies across industries.

For mid-market operators, the entry point is frequently our AI Strategy & Readiness engagement, which includes the AI readiness test and a concrete roadmap. We help you select the right models — whether Claude Opus 5 for complex reasoning or Haiku 4.5 for high-throughput queries — and stand up the eval harness before the first agent goes live.

If you’re in Houston with operational historian data, Boston with HIPAA pipelines, or Sydney with multi-tenant SaaS analytics, the architecture adapts. The principles — governed semantic layer, metric definition governance, eval harness — remain the same.

Summary and Next Steps

Agents don’t query your warehouse. They query your semantic layer — or they should, if you want answers you can trust. The Gartner projection that 60% of MCP-only agentic analytics efforts will fail by 2028 is a warning against skipping the architecture work. Text-to-SQL against a raw warehouse is a demo, not a product.

The path to production-grade agentic analytics runs through five disciplines:

1. Semantic layer deployment: A governed translation plane that maps business terms to physical data.
2. Metric definition governance: Version-controlled, stakeholder-signed metric artifacts that eliminate ambiguity.
3. Eval harness: Continuous accuracy measurement with human-in-the-loop validation and automated regression testing.
4. Model selection: Choosing the right model for the reasoning depth and latency profile your use case demands.
5. Architecture integration: Wiring the semantic layer into your existing BI stack — whether that’s Superset, dbt, or a custom embedded analytics platform.

PADISO, led by Kevin Kasaei, brings this architecture to mid-market brands, scale-ups, and PE portfolios across the US, Canada, and Australia. Whether you need fractional CTO leadership, a full venture architecture engagement, or a targeted AI automation build, the starting point is the same: a conversation about where your data stands today and where it needs to be to drive measurable AI ROI.

Next steps:

• Take the AI readiness test to benchmark your organization’s data and AI maturity.
• Explore our services to see how CTO as a Service, AI & Agents Automation, and Platform Design & Engineering fit together.
• If you’re a PE operating partner evaluating a roll-up or portfolio value-creation initiative, book a call directly. We’ll walk through the architecture and show you what a governed semantic layer can do for EBITDA.
• Read our case studies for concrete examples of AI transformation that delivered real numbers, not slideware.