Beyond Text-to-SQL: Building a Governed Agentic Data Stack
Why naive LLM database queries fail in enterprise production—and the 3-layer architecture required to fix them.
For the past two years, the enterprise AI playbook for data analytics has looked deceptively simple: dump a database schema into an LLM context window (or a vector database), give the model a SQL execution tool, and let users ask questions in natural language.
On paper, Text-to-SQL sounds revolutionary. In production, it breaks down quickly.
If you have ever tried to deploy a naive Text-to-SQL agent across a production data warehouse, you have likely encountered the same wall:
The LLM writes syntactically valid SQL that computes the wrong business metrics.
It joins tables using obsolete foreign keys deprecated six months ago.
It invents its own definition of “Monthly Active Users” or “Net Revenue,” yielding numbers that contradict executive finance dashboards.
In a recent technical breakdown, Anthropic revealed how they solved this internally using an Agentic Data Stack. Rather than treating data analytics as a pure LLM prompting challenge, they reframed it as an infrastructure and governance problem.
In this article, we will unpack why naive AI analytics fails in enterprise environments, analyze the three core layers of an Agentic Data Stack, and provide a blueprint for building a trustworthy AI analytics system using modern data stack tooling.
TL;DR / Key Takeaways
Naive Text-to-SQL fails due to three root causes: Entity Ambiguity (ambiguous business definitions), Schema Staleness (decaying data models), and Retrieval Failure (fetching the wrong tables).
The solution is an Agentic Data Stack composed of three distinct layers: Governed Data Foundations (Semantic Layers), Active Maintenance/CI, and Modular Retrieval Skills.
LLMs should query Semantic Layers (dbt, Cube, Looker), not raw database schemas. This constrains LLMs to human-governed metrics rather than raw, unvalidated SQL joins.
The Fallacy of Naive Text-to-SQL
Why does feeding raw DDL (Data Definition Language) or database schemas into an LLM fail when scaling beyond simple demos?
It boils down to three fundamental structural problems:
Figure 1: The failure pipeline of naive Text-to-SQL implementations in enterprise data warehouses.
1. Entity Ambiguity
Databases reflect storage optimization, not business logic. In a raw production warehouse, revenue might exist in five different places: stripe_charges, invoices, ledger_entries, mrr_analytics, or arr_snapshots. Without explicit semantic governance, an LLM cannot infer that Finance uses ledger_entries while Growth uses mrr_analytics.
2. Schema Staleness
Data engineering teams continuously rename columns, deprecate tables, and alter event schemas. An LLM prompt template or static vector database index quickly drifts out of sync with production data models, leading to silent query failures or inaccurate aggregations.
3. Retrieval Failure
As warehouse schemas grow to hundreds or thousands of tables, passing the entire schema into an LLM context window becomes cost-prohibitive or impossible. Traditional RAG (Retrieval-Augmented Generation) setups often fetch tables that look semantically relevant to the user’s prompt but lack necessary join keys or historical depth.
The Blueprint: The 3 Layers of an Agentic Data Stack
To transform AI agents from fragile SQL generators into trustworthy analytics partners, Anthropic structured their internal architecture into three distinct layers. Each layer targets a specific failure mode.
Figure 2: The 3-Layer Agentic Data Stack architecture.
Layer 1: Governed Data Foundations (Solving Entity Ambiguity)
The primary architectural insight is that LLMs should rarely query raw database tables directly. Instead, agents must query a Governed Semantic Layer.
In modern data infrastructure, this means placing tools like dbt Semantic Layer (MetricFlow), Cube, or Looker LookML between the database warehouse and the LLM agent.
# Example: Codifying business metrics in dbt MetricFlow instead of raw prompt templates
semantic_model:
name: orders
model: ref('fct_orders')
entities:
- name: order_id
type: primary
- name: customer_id
type: foreign
measures:
- name: total_revenue
expr: order_amount_usd
agg: sumHow it works: Instead of exposing 100 raw SQL tables, you expose pre-modeled semantic entities, dimensions, and measures (
metric: total_revenue,dimension: signup_cohort).Why it prevents failure: Complex join logic, timezone handling, and metric definitions are hardcoded inside version-controlled code by human data engineers. The LLM’s role shifts from synthesizing complex SQL joins from scratch to selecting the correct metric and filtering parameters.
Layer 2: Active Schema Validation & Freshness (Solving Staleness)
Data models rot over time unless actively governed. An agentic data stack requires continuous integration (CI) and dynamic metadata syncing for LLMs:
Automated Data Contracts: Schema modifications in CI/CD pipelines automatically fail if they break metadata endpoints consumed by active AI agents.
Deprecation Annotations: Schema objects explicitly label deprecated fields with direct instructions (e.g.,
DEPRECATED: Use reporting.fct_orders instead).Dynamic Metadata Syncing: Tool definitions provided to agents are fetched dynamically at runtime from data catalogs (such as Atlan or Select Star) rather than hardcoded in system prompts.
Layer 3: Modular Retrieval & Analysis Skills (Solving Retrieval Failure)
Rather than relying on a single mega-prompt that generates SQL and formats charts in one turn, the agent uses a multi-step loop with specialized execution tools (Skills):
Search Metadata Skill: Scans the catalog for metric definitions and dimensions relevant to the user request.
Query Metric API Skill: Executes a structured API call against the semantic layer (returning standardized JSON/CSV).
Validation & Reflection Skill: Inspects query results for unexpected anomalies (e.g., null values, negative revenue) before presenting the answer to the user.
Visualization Skill: Formats chart configurations or renders tabular summaries.
Comparing AI Analytics Architectures
Practical Engineering Checklist: Is Your Pipeline Agent-Ready?
Before building or deploying AI analytics agents in your organization, evaluate your infrastructure against this checklist:
Are core metrics codified in version control? Ensure key metrics (ARR, Churn, DAU) live in a single repository (e.g., dbt metrics, Cube, or LookML), not inside dashboard SQL queries.
Is there a metadata API? Can your LLM agent dynamically fetch metric definitions and column descriptions via a structured endpoint?
Are schema deprecations marked? Does your catalog explicitly flag legacy schemas so agents avoid querying them?
Are execution environments isolated? Are queries routed through read-only service accounts with strict memory and cost timeouts?
Is there a reflection/validation loop? Does the agent workflow verify outputs (checking for empty sets or mathematical impossibilities) before returning a response?
From Dashboard Builders to Skill Authors
The shift toward self-service AI analytics does not diminish the importance of data engineering—it redefines it.
Data engineers will spend less time building ad-hoc dashboards or responding to repetitive Slack inquiries about metric locations. Instead, the primary responsibility of data engineering shifts to authoring semantic models, enforcing data contracts, and designing agent skills.
When organizations build clean, governed interfaces, AI agents like Claude can reliably bridge natural language and database infrastructure—delivering rapid insights without sacrificing analytical precision.