Why an AI agent's revenue query was $3.5 million wrong, and how AWS fixed it with dbt Semantic Layer

Neela Kulkarni, senior solutions architect at AWS, and Venkatesh Aravamudan, senior partner solutions architect at AWS, presented a governed architecture for letting AI agents query production data on Amazon Redshift, built around the dbt Semantic Layer and the Amazon Redshift MCP Server, without letting the agent write its own SQL.

Their starting point: an AI agent that writes its own SQL against production data is a liability. Wrong joins double-count rows silently, invented metric definitions mean "revenue" in the agent's query isn't the company's revenue, and wrong date grain or filters produce silent errors, all while the agent still returns an answer that looks confident either way.

They backed this up with research: Gartner puts the average annual cost of poor data quality at $12.9 million per organization, 73% of organizations name data quality as their top barrier to AI success, and Gartner projects more than 40% of agentic AI projects will be canceled by 2027, largely over governance gaps. They also cited dbt Labs and Harris Poll research finding that analysts lose more than 9 hours a week to this kind of friction, north of $21,000 per analyst per year.

To make the failure concrete, they walked through a real query: SELECT region, SUM(gross_amount) FROM fct_orders GROUP BY region returns $14.86 million, and it looks plausible. It's still wrong.

The actual revenue figure is $11.36 million, an overstatement of 30.8%, because gross_amount is a pre-discount, pre-refund number, refunds live in a table that never gets joined, cancelled and pending orders are still counted, and internal test accounts are flagged rather than excluded. Every one of those four rules lives only in a semantic layer, not in the schema, which is part of why the BEAVER benchmark, co-authored by AWS researchers, found state-of-the-art agents reaching only 10.8% accuracy on real enterprise data warehouse queries.

Their fix is a two-layer governed architecture built on the Model Context Protocol. The dbt MCP Server governs what gets queried: it resolves a business question against certified metric definitions using tools like list_metrics, get_dimensions, and query_metrics, and MetricFlow, not the language model, compiles the actual SQL. The Amazon Redshift MCP Server governs how that SQL executes, enforcing read-only, single-statement execution at three separate layers: the MCP server itself, IAM-scoped credentials limited to specific API actions, and database-level least-privilege grants.

Because an agent has to select from an enumerated list of pre-defined metrics rather than generating names or SQL from scratch, the presenters described fabricated metrics as structurally impossible under this design, not just discouraged by a prompt. Every answer also comes with a receipt: the metric name, the compiled SQL, the source model, the dbt test results, and a Redshift query ID traceable through SYS_QUERY_HISTORY.

In a live demo, the same governed path served two different audiences: a developer working in an agentic IDE who could see and approve the compiled SQL before it ran, and a business user in a conversational assistant who just got a plain-language, cited answer. They ran three scenarios: a straightforward Redshift-native revenue query, a blended query joining native Redshift tables with Apache Iceberg data in S3 Tables (with Redshift reading that Iceberg data in place through an external-catalog mount), and a data lake query that dbt executed directly on its own Athena and DuckDB adapters with no Redshift involved at all. The metric definitions and governance stayed identical across all three even as the execution engine underneath changed.

Their closing takeaways: governance has to be architectural rather than aspirational, because fabrication is a plumbing problem, not a prompting problem. Splitting what gets queried from how it executes means neither layer alone is sufficient on its own.

A priority chain, semantic layer first, then compiled SQL, then raw model discovery last, is what keeps an agent from improvising when a governed metric already exists. And the same governed path can serve a developer who wants to see the SQL and a business user who just wants an answer, without maintaining two separate systems of truth.

Watch every keynote and breakout from dbt Summit 2026 on demand. Watch dbt Summit 2026 on demand →