Does text-to-SQL actually work in production?
Not safely on its own. Modern LLMs can translate plain English into runnable SQL, but "runnable" is not "correct." On public benchmarks like BIRD and Spider 2.0, leading models still fall well short of human execution accuracy against realistic enterprise schemas. In production, a query that runs but joins at the wrong grain returns a confident, wrong number.
- ▸The demo lies: a clean two-table example hides the reality of 400-table warehouses, cryptic column names, and business logic that lives in tribal knowledge.
- ▸Silent failure is the danger: SQL errors are easy to catch; a syntactically valid query that double-counts revenue via a fan-out join is not.
- ▸Non-determinism compounds it: the same question can produce different SQL across runs, so you cannot certify a number for a board deck or a regulator.
Text-to-SQL is a genuinely useful capability. The mistake is wiring the model directly to your warehouse and treating its output as an answer instead of a draft.
Why does an LLM generate wrong SQL even when the query runs?
Because the model is guessing at business semantics the schema never encodes. Table and column names rarely define what "active customer" or "net revenue" mean, how to handle late-arriving refunds, or which of five date columns is authoritative. The LLM fills those gaps with plausible-but-wrong assumptions that never throw an error.
Common, dangerous failure modes:
- ▸Fan-out and fan-trap joins: joining a one-to-many relationship inflates sums; the query runs and returns numbers that are simply too big.
- ▸Wrong grain: aggregating at order-line level when the metric is defined per order.
- ▸Ambiguous metrics: "revenue" might mean gross, net, booked, or recognized, and the model picks one silently.
- ▸Hallucinated columns and stale logic: inventing a column that once existed, or ignoring a soft-delete flag so deleted rows leak into totals.
- ▸Timezone and filter drift: off-by-one day boundaries and missing status filters.
None of these fail loudly. That is exactly why a human reviewing only the answer, not the SQL, will ship the mistake. Grounding retrieval helps, and the same discipline we describe in RAG in Production: The Retrieval Engineering Nobody Demos applies here: the model is only as good as the context and constraints you give it.
What is a semantic layer, and how does it change the problem?
A semantic layer is a governed, code-defined model of your metrics, dimensions, joins, and grain, sitting between raw tables and any consumer. Instead of the LLM inventing SQL, it selects from pre-defined, tested metrics, and the layer compiles those selections into correct SQL every time. Tools include the dbt Semantic Layer (MetricFlow), Cube, and LookML.
What the semantic layer pins down once, for everyone:
- ▸Metric definitions: net_revenue is defined in one place, versioned in Git, and reviewed like any other code.
- ▸Join paths and grain: relationships and aggregation levels are declared, so fan-out joins become structurally impossible.
- ▸Dimensions and time: canonical date logic, timezones, and allowed groupings.
- ▸Access policy hooks: identity and attributes flow through to enforce security, covered below.
This turns an open-ended "write SQL" task into a constrained "choose measures and dimensions" task, which is dramatically easier for a model to get right and far easier for you to test. It is the same principle behind treating metric definitions as a single source of truth in Data Governance Engineers Will Actually Use.
Should the agent write SQL or call a governed metrics API?
Call the metrics API. The stronger 2026 pattern is text-to-semantic, not text-to-SQL: the agent emits a structured query (measures, dimensions, filters, time grain) against the semantic layer, and the layer deterministically compiles safe SQL. Expose that layer to the agent as a single tool, never the raw database connection.
- ▸Constrained output space: the model chooses from an allowlist of measures and dimensions, so it cannot reference a table it should not touch.
- ▸Deterministic compilation: identical structured queries produce identical SQL, which makes results reproducible and certifiable.
- ▸Tool-scoped access: expose the semantic layer via an MCP server or internal API; Cube and dbt both ship semantic-layer interfaces designed for this.
- ▸Validation is trivial: you can reject any structured query that references an unknown field before a single row is read.
Scoping the agent to one narrow, governed tool instead of a broad database credential is the blast-radius discipline we cover in Least Privilege for AI Agents: Scoping Tools, Tokens, and Blast Radius.
How do you enforce row-level security before the LLM sees data?
Enforce it in the data layer, keyed to the end user's identity, never in the prompt. Prompts are advisory and bypassable; row access policies are not. Pass the authenticated user's identity and attributes through the semantic layer to the warehouse, and let the warehouse filter rows before results ever reach the model.
- ▸Warehouse-native controls: Snowflake row access policies and dynamic data masking, BigQuery row-level security and authorized views, and PostgreSQL row-level security (RLS) all filter at query time.
- ▸Identity propagation: the agent runs queries as, or on behalf of, the requesting user, so a sales rep and a CFO asking the same question get different, correctly scoped rows.
- ▸Column masking: mask PII such as emails and national IDs so even a correct query cannot exfiltrate sensitive fields into the model or the transcript.
- ▸Never trust prompt-level filters: "only show this user's region" in a system prompt is defeated by the first clever question.
The point is defense in depth: even a perfectly jailbroken model can only ever see rows the database already permits for that user.
What guardrails keep a text-to-SQL agent from causing damage?
Least privilege plus hard execution limits. The agent's database role should be read-only, scoped to specific schemas, and rate-limited, so the worst case is a slow SELECT, not a dropped table or a runaway bill. Guardrails belong in infrastructure, not in polite instructions to the model.
- ▸Read-only, allowlisted role: grant SELECT on approved views only; deny DDL, DML, and system schemas outright.
- ▸Statement and cost limits: enforce query timeouts, byte-scanned or slot caps, and mandatory LIMIT clauses to contain expensive scans.
- ▸SQL validation: if you must allow generated SQL, parse it and reject anything that is not a single SELECT; block multi-statement payloads and comment-based injection.
- ▸Human-in-the-loop for high stakes: require confirmation before a result feeds a report, a payout, or an external action.
- ▸Full audit trail: log the question, structured query, compiled SQL, user identity, and rows returned.
Treating hostile input as the default, the same posture as Prompt Injection Defense in Depth: Assume the Text Is Hostile, is what keeps a helpful analytics agent from becoming an exfiltration tool.
How do you test and monitor a natural-language analytics agent?
Treat it like any data pipeline: golden datasets, execution-match evals, and full tracing. Maintain a suite of question-to-expected-result pairs and run them in CI on every change to prompts, models, or the semantic layer. Score on whether the returned numbers match, not on whether the prose sounds right.
- ▸Golden query sets: curate real business questions with verified correct answers and assert exact execution match.
- ▸Regression gates: block deploys when accuracy on the golden set drops, because models and metric definitions both drift.
- ▸Tracing: capture the full chain (question, structured query, SQL, latency, rows) so you can debug the actual failure instead of guessing.
- ▸Production sampling: review a percentage of live answers and feed corrections back into the eval set.
You cannot certify what you cannot reproduce and trace, the discipline we lay out in LLM Observability: You Cannot Debug What You Did Not Trace.
How TuniCyberLabs helps
We design governed natural-language analytics you can actually certify: a dbt or Cube semantic layer as the single source of truth, warehouse-native row-level security and masking, an agent scoped to a metrics tool instead of your raw database, and eval suites wired into CI. The result is answers a CFO or auditor can trust, not a demo that impresses until the first wrong number.
Ready to put a semantic layer in front of your agent? Talk to our AI and data engineering team.