Data Agents and Text-to-SQL: A Whitepaper

Every data team has heard the request: "Can we just let people ask the warehouse questions in plain English?" The technology to generate SQL from natural language has existed for years and improved sharply with current models. The problem was never generating a query; it was generating the right query against a schema with fourteen revenue columns, three definitions of customer, and joins that only two analysts understand. Confident wrong answers at scale are worse than no answers.

This whitepaper sets out the architecture FISTA Solutions uses for governed data agents. It is written for heads of data, analytics engineering leads, data governance owners, and the business leaders who want self-service without the risk. It covers why naive text-to-SQL fails, the role of the semantic layer, the query pipeline, permissions and privacy, evaluation, observability, and a rollout path. It complements the hands-on guides how to build a text-to-SQL agent and how to build an AI analyst agent.

Why does naive text-to-SQL fail?

Failure	Cause	Consequence
Wrong metric	Multiple columns or formulas could mean "revenue"	Two people ask the same question and get different numbers
Wrong grain	Joins multiply rows; aggregates double-count	Totals that are wrong by a plausible margin
Wrong filter	Business terms such as "active customer" have no schema equivalent	Silent inclusion or exclusion of records
Wrong time logic	Fiscal calendars, time zones, late-arriving data	Period comparisons that do not match reports
Unsafe query	Full scans, cartesian joins, unbounded results	Cost spikes and platform contention
Over-exposure	Agent runs under a broad service account	Users see data they are not entitled to

Each failure is structural, not a model weakness, which is why the fix is architectural. Better prompting reduces the rate; it does not remove the cause.

What does the semantic layer do?

The semantic layer is a governed model of the business: entities (customer, order, product), metrics (net revenue, active customers, gross margin) with exact definitions, dimensions (region, segment, period), and the joins and grain rules that connect them. It exists between raw tables and every consumer, including dashboards, notebooks, and agents.

For a data agent the semantic layer does four things:

1. Removes ambiguity. "Revenue by region last quarter" maps to one metric, one dimension, and one time rule.
2. Constrains generation. The agent composes queries from defined metrics and dimensions, or generates SQL against semantic views, rather than roaming raw tables.
3. Carries governance. PII classifications, access rules, and certified-versus-experimental status live with the definitions.
4. Makes answers consistent with the reports leadership already trusts, because both draw from the same definitions.

Organizations without a semantic layer should build one for the first target domain before deploying the agent; the agent's questions are an excellent way to discover which definitions matter. The strategic argument is in why AI agents need a semantic layer.

What does the query pipeline look like?

Stage	What happens	Control
Interpret	Parse the question into entities, metrics, dimensions, filters, and time; ask a clarifying question when ambiguous	Ambiguity threshold; clarification instead of guessing
Plan	Map to semantic-layer objects; reject requests outside the layer's coverage	Coverage check; graceful refusal
Generate	Produce the query from semantic objects or against semantic views	Templates and grammar constraints where possible
Validate	Static checks: allowed objects only, no unbounded scans, result limits, cost estimate	Hard blocks on unsafe patterns
Execute	Run under the asking user's identity with the platform's security enforced	Never a shared service account
Explain	Return the interpreted question, the query, sources, freshness, and caveats	Every answer shows its work
Learn	Capture corrections and unanswered questions	Feed the golden set and the semantic-layer backlog

The explain stage is what makes the system trustworthy in practice. An answer that shows the interpreted question and the metric definition lets a business user catch a misinterpretation in seconds, which is far better than a hidden error discovered in a board deck.

How should the agent handle ambiguity?

The difference between a trustworthy data agent and a dangerous one is what it does when a question is unclear. The rule is clarify or state the interpretation; never silently guess.

Question	Ambiguity	Correct behavior
"What was revenue last month?"	Gross or net; booked or recognized; calendar or fiscal month	Use the certified default metric, state it in the answer, offer the alternatives
"How many customers do we have?"	Active, total, paying, by account or by contact	Ask which definition, listing the certified options
"Show sales by region"	Which regional hierarchy; which period	Apply defaults, state them, and make the period explicit
"Why did churn go up?"	Causal question over observational data	Return the churn trend with decomposition by segment and note that causes are not established
"Give me the customer list with emails"	Restricted field	Refuse the restricted column, explain the policy, and return the permitted fields

Defaults must be certified by the metric owner, and every answer must repeat the interpretation it used. This turns ambiguity into a visible, correctable choice rather than a hidden error.

What about follow-up analysis and scheduled outputs?

Once single questions are reliable, users ask for more: a chart, a breakdown, a weekly report, a note in the CRM. Each is an action with its own permission. Chart and breakdown generation are read-only and can flow freely. Scheduled reports create ongoing cost and should require an owner and a budget. Write-backs to other systems, such as flagging an account in the CRM, are consequential and should go through the same scoped-tool and approval model as any other AI agent action.

How are permissions and privacy enforced?

Three rules are non-negotiable.

• User identity on every query. The agent executes as the asking user, with the data platform's row- and column-level security applied. Delegated identity flows are described in the agent identity and access control whitepaper.
• PII policy at the semantic layer. Restricted fields are masked, aggregated, or excluded by definition, so no query can expose them regardless of how it was phrased.
• Audit logging. Every question, interpretation, query, and result summary is logged with the user identity, so access reviews and investigations have the evidence they need.

Regulated data adds obligations on retention, cross-border movement, and purpose limitation; treat this whitepaper as general guidance, not legal advice, and involve privacy counsel. The broader privacy framework is in AI data privacy compliance.

How should data agents be evaluated?

Data agents are evaluated on answer correctness, which is measurable because the right answer to a business question is a number or a table that experts can verify.

1. Build a golden set of real questions from the target domain, each with a verified answer and the semantic objects it should use, covering easy, ambiguous, and out-of-scope cases.
2. Score interpretation (did it map to the right metric and dimensions), query correctness (does the query compute what was intended), and answer correctness (does the number match), separately, so failures are diagnosable.
3. Gate every change to prompts, models, the semantic layer, or the pipeline on regression against the set.
4. Sample production answers for expert review and add corrected cases to the set.

The method is the data-domain application of evaluation-driven development.

What observability and cost controls are needed?

Data agents can generate expensive queries. Track query cost and latency per question, enforce per-user and per-agent budgets, cache repeated questions where freshness allows, and alert on unusual patterns such as repeated scans of large tables. Freshness matters too: answers should state when the underlying data was last loaded, because a correct query over stale data is still a wrong answer for the person asking.

What is the rollout path?

1. Pick one domain with heavy question volume and a willing owner, such as sales performance or support operations.
2. Build or extend the semantic layer for that domain's top questions; certify the definitions with finance or the relevant owner.
3. Assemble the golden set from real questions the analytics team already answers.
4. Deploy the agent to the analytics team first, with the explain stage prominent, and measure accuracy.
5. Open to business users in that domain with feedback capture.
6. Expand domain by domain, growing the semantic layer from the questions people actually ask.

The analytics engineering work in steps 2 and 3 is where a forward deployed engineer with data experience is most effective, because the definitions have to be negotiated with the business, not just coded.

What are the failure modes?

1. Agent over raw tables. Inconsistent numbers erode trust within weeks.
2. Shared service account. The agent becomes a data-exfiltration path.
3. No explain stage. Errors are invisible until they are costly.
4. Guessing instead of clarifying. Ambiguous questions get confident answers.
5. No golden set. Quality is an opinion and every change is a gamble.
6. Freshness ignored. Correct queries, stale answers.

How does FISTA Solutions help?

FISTA Solutions builds governed data agents as part of its AI agents practice, with AI enablement engagements that establish the semantic layer, the permission model, and the evaluation loop, and forward deployed engineers who work inside your data team to certify definitions and ship the first domain. FISTA has delivered 150+ projects for 50+ companies across 12+ countries with 99.9% uptime.

If your organization wants self-service answers without self-service errors, talk to FISTA on WhatsApp about a scoped data-agent assessment, or read how to build a text-to-SQL agent next.