Every BI vendor and warehouse platform has shipped a “chat with your data” surface in the last eighteen months. The demo always works: a friendly question, a generated SQL statement, a clean chart, a sentence of commentary. The architecture diagram is a single arrow from a chat box to the warehouse. The pricing page hints at a future where business users no longer file ticket requests for analyst time. We’ve now stood up text-to-SQL agents against half a dozen production warehouses — Snowflake, BigQuery, Databricks, Redshift — across orgs ranging from 80-table greenfield to 12,000-table federated sprawl. The architectural realities diverge from the marketing claims in predictable ways. Knowing where the divergence sits is the difference between a project that earns trust and one that quietly dies after the steering-committee demo.
The schema breadth problem
A model can hold a few hundred tables and their columns in working context with reasonable fidelity. Beyond that, the model is reading a retrieval-shaped sketch of the schema and guessing the rest. Every real-world warehouse we’ve worked in has more than a few hundred tables once you count staging, intermediate, marts, snapshots, and the long tail of one-off extracts that nobody has the political capital to deprecate. The agent’s first task is therefore not “write SQL” but “find the right three tables out of twelve thousand,” and that task is a retrieval problem, not a generation problem.
Vendors solve retrieval with a vector index over schema documentation, table descriptions, and column comments. This works when those artifacts exist and are accurate. In production, table descriptions are missing, half-written, or last touched in 2021 by an engineer who has since left. The retrieval surface that the agent reasons over is a fiction. Its outputs reflect that fiction with the same confident tone they’d use on the truth.
The fix is not a bigger model. It is the unglamorous work of curating a semantic layer — a small set of certified tables and columns with verified definitions, ownership, and grain — and constraining the agent to that layer. Every successful production deployment we’ve seen converges on this. The marketing claim is “ask anything.” The architectural reality is “ask anything within the curated subset.”
The semantic context problem
SQL is the easy part. The hard part is the join logic, the filter conditions that distinguish active customers from churned ones, the date-grain alignment between an event stream and a snapshot table, the difference between gross revenue and net revenue at this specific company. None of this lives in the schema. It lives in the heads of the senior analysts and in the pull requests where the dbt models were merged.
Text-to-SQL agents that read only the schema produce SQL that compiles, runs, and returns numbers. The numbers are wrong in ways that are invisible without domain context. A common failure mode: the agent picks orders when the canonical revenue source is fct_orders_clean, joins on a customer ID that has two definitions (CRM-side vs warehouse-side), and reports a number that is plausibly close to the truth but off by 8%. Nobody catches it because nobody validates a chat answer the way they’d validate a finance-team dashboard.
Production deployments that work invest in encoding this semantic context as data — metric definitions in a metrics layer (Cube, dbt Semantic Layer, MetricFlow), curated joins in a relationship graph, and certified queries that the agent retrieves and adapts rather than generates from scratch. The agent’s job shifts from “write SQL” to “select the right certified query and parameterize it.” That shift is the architecture.
The verification problem
When an analyst writes SQL, they sanity-check the result before they ship it: spot-check a few rows, compare to a known monthly total, look at the row count against expectations. An agent does not. It returns the result of whatever query it generated and moves on. If the query was wrong, the user sees a wrong answer presented with full confidence and a polished chart.
The architectural fix is a verification layer between the agent and the user — automated checks that the result is consistent with known totals, that row counts are within expected ranges, that the query touched only certified tables, that the date filter resolved to a real date. Vendors are starting to ship this; most ship it as a checkbox in the demo and a TODO in the docs. Without the verification layer, the system is a hallucination machine for executives. With it, the system is a useful productivity tool whose answers earn trust over time.
The cost problem
A well-tuned agent issues five to fifteen warehouse queries per user question — schema retrieval, candidate generation, validation, refinement, final result. At Snowflake compute prices, a power user running a hundred questions a day in a warehouse with cold caches can light five figures of monthly compute on fire before anyone notices. The marketing claim is “self-service unlocked.” The CFO’s read of the bill is “what changed.” Caching, query budgets per user, materialization of common patterns, and a hard cap on retry depth are the architectural countermeasures. None of them appear in the demo.
What to build instead of buying the demo
Constrain scope to a curated subset. Encode semantic context as data, not as prose. Wrap the agent in a verification layer. Cap the cost. Measure trust through follow-up question rate and analyst-overrule rate, not through demo applause. Done in that order, text-to-SQL is a real productivity gain on a narrow surface. Done in the order the marketing implies, it is a budget line item with no defenders by quarter three.