How to Build a Text-to-SQL Agent Users Can Trust

Every data team has seen the demo: type a question, get a chart. Every data team that shipped one has also seen the aftermath: two executives with different revenue numbers from the same "AI." The problem was never generating SQL; it was generating the right SQL against a schema built for engineers, not questions. This playbook builds a text-to-SQL agent that business users can trust, by putting certified definitions between the model and the tables and by making every answer inspectable. It implements the data agents and text-to-SQL whitepaper and pairs with the database integration in how to build an MCP server for Postgres and the warehouse build in how to build a Snowflake AI agent.

Step 1: What is the agent's role?

Element	Definition
Purpose	Answer business questions from certified definitions with verifiable, permission-scoped results
Scope	Questions over the semantic layer for the target domain; breakdowns; follow-ups
Non-scope	Raw-table access; writes; uncertified definitions; causal claims
Owner	Head of analytics or the domain's data product owner

Step 2: Why does the semantic layer come first?

Because it is where "revenue" becomes one formula, "active customer" becomes one filter, and joins and grain are fixed. For the first domain: define entities, metrics with formulas, dimensions, joins, and grain; mark PII and certification status; expose as views in a dedicated schema; have metric owners certify. Without this, the agent iterates over ambiguity. The strategic case is in why AI agents need a semantic layer.

Step 3: How does interpretation work?

The agent parses the question into entities, metrics, dimensions, filters, and time; maps each to semantic objects; applies certified defaults and records them; asks a clarifying question when mapping is ambiguous; refuses when the question is outside the layer's coverage. Interpretation is a separate, scored step, because most wrong answers are wrong interpretations.

Step 4: How is the query generated and validated?

Stage	Control
Generate	From semantic objects, using templates where possible; SQL against semantic views otherwise
Static validation	Allowlisted objects only; no write statements; no cross-schema references
Limits	Row limit; statement timeout; cost-estimate threshold
Dry run	Plan check on the warehouse where supported
Execute	Under the asking user's identity; query tagged with agent and user

Validation rejects rather than repairs unsafe queries; the agent reports why and offers a narrower question.

Step 5: How is execution scoped to the user?

The agent connects as the asking user or exchanges the user's token for a session under their role, so row- and column-level security apply exactly as in a query tool. The agent's own identity has no grants on data. Per-user budgets cap spend. The model is in the agent identity and access control whitepaper.

Step 6: What does the explanation include?

The interpreted question in business terms, the metric definitions used, the query, the sources, the data's last load time, and caveats such as defaults applied or partial coverage. The explanation is what lets a user catch a misinterpretation in seconds.

Step 7: How does the agent handle follow-ups and outputs?

Follow-ups reuse the interpretation context ("now by region", "same for last year"); breakdowns and charts are read-only outputs; scheduled reports require an owner and a budget; write-backs to other systems are consequential and go through the standard tool-layer gates.

Step 8: What is the evaluation set?

Real questions from the domain with verified answers and semantic objects, by difficulty, including ambiguous and out-of-scope cases and restricted-field requests. Score interpretation, query correctness, answer correctness, and refusal correctness separately. Gate every change to prompts, models, the semantic layer, or the pipeline. Sample production answers weekly for analyst review; corrections join the set. The method is evaluation-driven development.

Step 9: Rollout

1. Analytics team only; explain stage prominent; accuracy measured.
2. Business users in the first domain with feedback capture.
3. Follow-ups, breakdowns, charts.
4. Scheduled reports with owners.
5. Next domain, growing the semantic layer from real questions.

What does the agent look like in daily operation?

A marketing lead asks how many new customers came from paid channels last month. The agent maps "new customers" to the certified acquisition metric, "paid channels" to the channel dimension's paid group, applies the calendar-month default and states it, generates the query over the semantic view, validates it, runs it under the lead's role, and returns the number with the definition, the query, and the load time. A follow-up "by campaign" reuses the context. A question about "profitable customers" has no certified definition; the agent says so and offers the two metrics that exist. A request for customer emails is refused under the masking policy. An analyst reviewing the weekly sample flags one interpretation; the corrected case joins the golden set and the metric gains a clarifying note in the semantic layer.

How does this fit the wider data platform?

The text-to-SQL agent is the first consumer of a semantic layer that dashboards and notebooks also use, and it runs on the same gateway, identity, and evaluation foundation as every other agent. Its query tags feed warehouse cost reporting; its refusals feed the semantic-layer backlog; its golden set becomes the vendor-neutral benchmark for model selection. Treating it as a data-platform product rather than a chat feature is what keeps the numbers consistent across every surface, as described in the enterprise RAG reference architecture for unstructured content and its structured counterpart here.

What are the common mistakes?

1. Raw tables instead of certified views.
2. Guessing instead of clarifying.
3. No explanation stage.
4. Shared service account for execution.
5. Aggregate scoring that hides interpretation errors.
6. Freshness omitted, so correct queries give stale answers.

How does FISTA Solutions help?

FISTA Solutions builds text-to-SQL agents on certified semantic layers as part of its AI agents practice, with forward deployed engineers inside the data team to certify definitions and build the golden set, on the platform its AI enablement practice establishes. FISTA has delivered 150+ projects for 50+ companies across 12+ countries.

To build a trustworthy data agent for one domain, message FISTA on WhatsApp, or read how to build an AI analyst agent for the next capability up.