Why AI Agents Break on Dirty Data: The Semantic Layer Problem No One Warned You About

Most engineering teams evaluating agentic analytics assume their accuracy problems are model problems. If the agent gives a wrong answer, the instinct is to swap the LLM, tune the prompt, or add a retrieval layer. That instinct is almost always wrong. The failure point is not the model. It is the data infrastructure the model is querying against, and specifically the semantic layer that sits between natural language intent and the SQL that actually runs.

This matters because the cost of getting it wrong compounds. An agent that generates plausible but incorrect SQL does not fail loudly. It returns numbers that look reasonable, get copied into presentations, and inform decisions before anyone notices the metric definition was wrong.

Companion piece to our broader work on semantic layer architecture for AI agents. See The Semantic Layer Is the Agent: Why Your AI Automation Stack Is Only as Good as What Sits Beneath It for a deeper treatment of metadata governance and deterministic SQL generation patterns.

The Metric Consistency Problem

Enterprise BI environments accumulate definitions over years. Revenue in the finance dashboard is calculated differently from revenue in the sales dashboard. Churn in the customer success tool uses a different window than churn in the data warehouse. These inconsistencies are manageable when humans navigate them, because experienced analysts know which source to trust for which question.

An agent does not know that. It sees column names, table relationships, and whatever documentation exists in the schema. If "monthly active users" appears in three tables with three different join conditions, the agent will pick one. It will not flag the ambiguity. It will return a number with the same confidence regardless of which definition it used.

The practical consequence is that agentic analytics in organisations with ungoverned semantic layers produces answers that are locally coherent but globally inconsistent. The same question asked twice, routed through different tables, returns different figures. That is not a hallucination in the technical sense. It is an accuracy failure caused by under-specified data infrastructure.

Why Free-Text-to-SQL Accuracy Is Harder Than It Looks

Text-to-SQL benchmarks measure performance on clean, well-documented schemas with unambiguous column names and no competing definitions. Production databases are not like that. Column names are abbreviated, historical, or reused across contexts. Foreign keys are undocumented. Nullable fields carry implicit business logic that only exists in the heads of the analysts who built the pipeline.

Under those conditions, even strong models degrade. The model is not failing at language understanding. It is failing because the information required to generate correct SQL is not present in the schema it can see. Prompt engineering and few-shot examples help at the margins, but they do not solve the underlying problem of missing semantic context.

The architectural implication is that text-to-SQL accuracy is a function of schema quality, not just model capability. Teams that invest in annotating their semantic layer, documenting metric definitions, and surfacing business logic explicitly will see materially better agent accuracy than teams that deploy the same model against an undocumented warehouse.

Semantic Layers Were Not Designed for Autonomous Agents

Most semantic layers in production today were designed for human-facing BI tools. They encode dimension hierarchies, pre-aggregated metrics, and access controls in formats that work well when a human selects from a dropdown. They were not designed to be navigated by an agent that is constructing queries dynamically from natural language.

The Discovery Problem

An agent querying a semantic layer needs to understand what is available before it can decide what to retrieve. Most semantic layers do not expose their structure in a form that is easy for an LLM to traverse efficiently. The agent either receives too much context (the entire schema, which exceeds useful context windows) or too little (a summary that omits the distinctions that matter for accuracy).

The Verification Problem

Human analysts verify their queries before they trust the output. They check the row count, spot-check against known values, and apply domain knowledge to sanity-check the result. Agents do not do this by default. Without explicit verification steps built into the architecture, an agent will return the output of a syntactically valid but semantically incorrect query with no indication that anything is wrong.

This is the distinction between an agent that generates answers and one that generates verifiable answers. The architecture required to produce the latter is significantly more involved, and most off-the-shelf agentic analytics tooling does not implement it.

The Real Cost of Maintenance Over Time

Deploying an agentic analytics system is not a one-time engineering effort. Schemas evolve. Metrics are redefined. New data sources are added. Every change to the underlying data model is a potential accuracy regression for the agent, and without a process to propagate those changes into the semantic layer the agent relies on, accuracy degrades silently over time.

The maintenance cost is proportional to how much business logic lives outside the semantic layer. If metric definitions are encoded in SQL strings inside application code, or in undocumented transformation steps in a pipeline, the agent cannot access them. When those definitions change, the agent continues using the old version until someone notices the discrepancy and traces it back to the source.

Teams that have been through this once tend to arrive at the same conclusion: the semantic layer is the product, not the agent. Investing in a well-governed, machine-readable semantic layer with documented lineage reduces ongoing maintenance costs and is the prerequisite for any accuracy guarantee on the agent side.

Architectural Trade-offs Between Autonomous Query Generation and Verifiable Answers

There is a real trade-off between flexibility and verifiability in agentic analytics architecture. A fully autonomous agent that generates arbitrary SQL against a live warehouse is maximally flexible. It can answer questions the designers did not anticipate. It is also maximally difficult to audit, because every query is novel and the output cannot be validated against a known-good reference.

A more constrained architecture pre-defines a library of verified queries and uses the agent to select and parameterise from that library. This approach sacrifices some flexibility. It cannot answer questions that fall outside the library. What it gains is verifiability: every answer traces back to a query that has been reviewed and validated by a human.

The right choice depends on the use case and the tolerance for error. For operational decisions where accuracy is critical and the question space is bounded, the constrained architecture is almost always the correct one. For exploratory analysis where the cost of an occasional wrong answer is low, autonomous generation may be acceptable. Most teams benefit from implementing both modes and routing queries to the appropriate architecture based on question type and confidence threshold.