Text to SQL for analytics agents in production

Text to SQL is the step where a language model turns a business question into a SQL query. An analytics agent wraps that step in a workflow that plans the question, runs the query and describes the result, and in production most of the work sits around the SQL rather than in it.

We put four things between the question and the number. A metric layer owns every definition, so the model never invents what revenue means. Checks parse and price each query before it runs, and limits in the database hold when our own code is wrong. A final check matches every number in the answer to a cell in the result. Code runs the steps in a fixed order, and each step writes a record, so an answer can be explained and replayed later.

The question comes from current work on an analytics agent for a telecom marketing platform. Its data holds subscriber-level personal data across several jurisdictions, and every result has to be explainable and repeatable. In the current phase the agent proposes every action and a person approves it, so nothing here is a production result from that system. It gives this guide a set of constraints, and the sections below are organised around them.

The hard part is that an analytics agent fails quietly. A support agent that misreads a ticket usually writes a reply someone can see is wrong. An analytics agent that joins orders to line items before aggregating produces a revenue figure that is too high. It is formatted like every other revenue figure, with a chart to match. Nobody objects until the number reaches a board deck and disagrees with finance.

The unsafe queries are not all obvious either, and a DELETE statement is the easy case. A SELECT that scans three years of events because the model dropped a date filter is valid SQL and runs under a read-only role. It still costs real money and slows every dashboard sharing the warehouse.

This guide starts with what public text-to-SQL scores do and do not tell you, then where definitions should come from. It covers the checks on the SQL and on the answer, where judgment calls go, and how to show the agent works before business users rely on it. It ends with the cases where all of this is more than you need.

How accurate are text-to-SQL LLMs?

Read public scores as background on the model, not a forecast for your warehouse, because they measure the step that fails least in production: writing SQL that returns the same rows as a reference query, on a schema the model was handed. On 22 October 2026 the top execution accuracy on the BIRD test set was 82.95%, posted on 7 September 2026, against a human figure of 92.96%.

Expect the number to fall on your own schema, because enterprise schemas are larger and messier than any benchmark's. On Spider 2.0, built from real BigQuery and Snowflake workflows, o1-preview solved 21.3% of tasks in the original results, against 91.2% on Spider 1.0 and 73.0% on BIRD. Agents have climbed its leaderboard since, and the top Spider 2.0-Lite entry read 76.23% when we checked on 22 October 2026. That gap opens before any business definition comes into it.

No benchmark checks whether revenue meant net or gross, or whether the query should have run at all. None checks whether the paragraph describing the rows matches them, and those are the failures a finance lead finds. Pick the model on your own questions, and build the eval set from the questions the business already asks, which the last sections cover.

Which failures does the agent have to catch?

The worst output is a query that runs and returns a wrong number. Most agent failures end in an error, a refusal or a visibly odd action. Here the warehouse accepts the query, returns rows, and the model writes a fluent paragraph around them. The table lists the failures to design against and the check that should catch each one first.

Most rows in that table pass every check a general-purpose agent framework runs. The SQL is well formed, the tool call succeeded, and the answer reads well. We met the same shape on DocVerse, our ESG filings pipeline. Its design is built around one failure: a correct number under the wrong entity, period, unit or framework version. The value is real, the citation resolves, and everything downstream passes.

The fix there was to carry those four as fixed fields from parsing onward, never inferred when a question arrives. An analytics agent needs the same for its definitions and scope, which is the subject of the next section.

Where should the definitions come from?

From a governed layer the agent reads, never from the prompt. A metric layer, which some teams call a semantic layer, holds each metric's definition, its join path, its owner and its version in one place. The agent writes a plan that names metrics and dimensions from that layer. For questions the layer covers, the layer compiles the plan to SQL, so the model never writes the join logic behind a governed metric.

The plan is a small structured output: the metrics, the dimensions to group by, the filters, the time window and grain, and the SQL dialect, and code checks it against the layer before anything runs. Intake has already turned "last quarter" into explicit dates in the fiscal calendar, and a team's region name into the codes the warehouse uses.

{
  "metrics": ["net_revenue"],
  "dimensions": ["region"],
  "filters": [{ "field": "order_status", "op": "in", "values": ["completed"] }],
  "time": { "field": "order_date", "from": "2026-07-01", "to": "2026-09-30", "grain": "month" },
  "dialect": "snowflake"
}

Every later check compares against this plan, so the parser confirms the SQL uses the plan's tables, and the answer check confirms the answer states the plan's time window. A reviewer reading a wrong answer can see whether the plan was wrong or the SQL drifted from a correct plan. Those are two different fixes.

When a question could mean two governed metrics, such as bookings or recognised revenue, the agent asks which one and records the answer for that team. A guess would give a number that is right for one reading and wrong for the other, with nothing in the answer to say which reading was used.

Questions outside the layer still come up, and for those the model writes SQL directly. Give it a narrow slice of the schema for that question, never the whole catalogue, and lock the dialect. Constrained decoding can force the plan into its schema, but it cannot make free SQL correct. Free SQL goes through the same checks and stays under closer review.

Teams with no metric layer yet will find the agent building one in its prompts, one inconsistent definition at a time. Our guide on a semantic layer for AI agents covers defining terms once, with an owner and a version.

This figure is a framework of the parts this guide covers. It shows no product's internals and carries no measured values.

What should check the SQL before it runs?

Two checks, because they answer different questions. The DevOps agent we built for live AWS accounts rests on one observation. Validating a template against a schema tells you it is well formed, but only the account tells you it will apply. SQL splits the same way: a parser tells you what the query does, and only the warehouse can tell you how much it will read.

Parse the SQL into a tree#

A string check cannot tell you what a query does. A regular expression looking for DELETE misses a keyword hidden in a comment and flags a column named deleted_at. Use a real parser for the locked dialect, such as sqlglot in Python, which gives code a tree of statements, tables, columns and joins to check. The table lists the checks to run on that tree and what each one stops.

The parser may make mechanical fixes, such as adding a missing LIMIT or qualifying a table with its schema. It never rewrites meaning, because a changed join key or an added filter would mean the query that ran was not the one the model reasoned about. The answer would then describe a query nobody wrote. The sketch below shows the shape of the check, and it is schematic: a production version also resolves aliases and subqueries before checking columns.

import sqlglot
from sqlglot import exp
 
WRITE_NODES = (exp.Insert, exp.Update, exp.Delete, exp.Merge, exp.Create, exp.Drop, exp.Command)
MAX_ROWS = 1000  # a named constant, reviewed like code
 
def check_sql(sql: str, dialect: str, allow: Allowlist) -> Verdict:
    try:
        statements = sqlglot.parse(sql, read=dialect)
    except sqlglot.errors.ParseError as e:
        return Verdict.reject("parse", hard=False, detail=str(e))
    if len(statements) != 1:
        return Verdict.reject("multi_statement", hard=True)
    tree = statements[0]
    if not isinstance(tree, exp.Select) or any(tree.find_all(*WRITE_NODES)):
        return Verdict.reject("not_select", hard=True)
    for table in tree.find_all(exp.Table):
        if not allow.table(table.db, table.name):
            return Verdict.reject("table_not_allowed", hard=True, detail=table.sql())
    for join in tree.find_all(exp.Join):
        if not join.args.get("on") or not allow.join_keys(join):
            return Verdict.reject("join_keys", hard=False, detail=join.sql())
    if not tree.args.get("limit"):
        tree = tree.limit(MAX_ROWS)
    return Verdict.ok(tree.sql(dialect=dialect))

Each rejection carries a hard or soft flag. A write, a second statement or a table off the allowlist is a hard fail, and code denies it without asking anything. A join on an undeclared key is a soft fail, because some unusual joins are right, and a later section covers who decides those. A soft rejection goes back to the model with the failed check named, and code caps the retries.

Most bad queries should die in that loop before the warehouse starts anything, the way most errors die in the DevOps agent's validation loop before AWS is asked to start anything. There, template errors before deploy fell from around 10% to 4% after the loop went in. That is an internal measurement. Keep the rejections as well. On the DevOps agent the validation failures are the system's own record of what the model gets wrong, and they feed improvements to the loop rather than to the prompt.

Ask the warehouse for its plan#

The parser cannot know that a well-formed query over one table will read four terabytes. That depends on data volumes, how tables are split into partitions, and statistics only the warehouse holds. A plan check asks the warehouse how it would run the query and blocks it if the plan is too large. Each warehouse exposes the plan differently.

The ANALYZE warning matters, because in PostgreSQL EXPLAIN ANALYZE executes the statement to measure it. A check built with it runs every query it was meant to screen, including the ones it then rejects. Test the check against a query that would fail loudly if it ran, and confirm it never does.

From the plan, the check compares estimated bytes or cost with a budget per question and looks for full scans of large tables. It also compares a join's estimated output with its inputs, which is how repeated rows show up in a plan. Each budget has two lines in code, and a query over the hard line is denied. Between a lower warning line and the hard line, the query waits for a judgment, which the section on judgment calls covers.

Estimates are wrong in both directions. Stale statistics make PostgreSQL guess low. BigQuery documents its byte estimate as an upper bound, and warns that a query on a clustered table can fail a byte cap it would not have exceeded, so the check can block a query that would have fitted. Treat the plan check as an early stop for most runaway queries and keep the database's own limits as the guarantee.

Which limits belong in the database?

Every limit the database can enforce, because those still hold when the agent's code has a bug. The parser and the plan check are our code, and our code can be wrong. A read-only role with grants only on reviewed views stops a write even if a parser upgrade changes how it classifies a statement. The table sets out what to configure before the agent runs a single query, and what each warehouse offers, as documented on 22 October 2026.

Where the warehouse has no byte cap, the timeout and the plan check carry more of the weight. Size the agent's compute so its worst query cannot hurt anyone else. Pin the connection to one warehouse account as well, so a prompt cannot point the agent at a different target.

Queries run with the asking user's access, never through a shared identity that sees everything. On the telecom engagement the analytics agent reads through a data layer that applies the client's access rules. It sees only the tables, columns and rows the asking user is entitled to, and personal data is masked, aggregated or excluded by policy.

The engagement document puts the rule plainly: prompts are a user-interface layer, and policy enforcement never depends on them. Our guide on AI agent permissions covers binding each query to the user and the task.

A new kind of question can also run for a while in dry-run mode, with every check active and execution switched off. The logs then show what the parser and the plan check would have done, with no risk that any of it ran.

What should check the answer before anyone reads it?

Rows are not an answer, because the model still has to describe them. In that step it can round a number differently, state a time window the query did not use, or describe a trend the chart does not show. The answer check runs after the results and before the reader, in two layers.

The first layer runs in code and matches every number in the draft to a cell in the result, or to a value code computed from cells. We block an answer that holds a number with no source rather than flag it, because a flagged number still reaches the reader and can end up in a deck. Code computes every derived figure, such as a percentage change, because arithmetic has one right answer and a model can get it wrong.

The chart is drawn from the result, never from numbers the model typed, so the chart and the table cannot disagree. An empty result gets a fixed message stating that no rows matched, so the model cannot present it as zero, and a reader can tell an empty join from a real zero.

The second layer judges meaning, which code cannot do alone. Does the draft claim a cause, a trend or a comparison the rows do not show? Does the stated period, region and segment match the SQL that ran? Does it answer the question asked, or a nearby one the metric layer happened to cover? These are yes-or-no judgments, and the section on judgment calls covers who makes them.

Every answer that passes carries its evidence, which on the telecom engagement means the query that ran, the tables, the filters and the assumptions. The query and its parameters are captured so the answer can be reproduced. DocVerse enforces the same rule at generation: a conclusion without citations is not presented as a finding, even when it is probably right. Groundedness then stops being a score on a dashboard and becomes a condition for sending the answer.

When the draft cannot be grounded, the agent abstains instead of sending its best guess. The abstention names what is missing, such as a metric the layer does not define or a period the data does not cover, which gives the reader something to act on.

Personal data in results and logs#

Code sits between the warehouse and the model and decides what the model may read. The model does not need ten thousand rows to describe a trend. Code aggregates or samples the result, masks columns tagged as personal data, and passes a summary with row counts, ranges and null counts. Keeping raw rows out of the model's context limits what a logged completion or a leaked prompt can expose.

Tagging depends on a catalogue that often does not exist yet, or on column names like notes and col_12 that hide what the values hold. A separate discovery job can propose tags from a read-only sample with a hard row limit per column. It checks the values against patterns for emails and phone numbers, and uses a small language model where the name is opaque or the values are free text.

The samples are personal data themselves, so the job keeps raw values out of its logs and stays off the question path. Its suggestions change nothing until a data owner accepts them into the catalogue.

The logs need the same care, because a trace that stores raw rows turns the audit system into a second copy of the data it protects, open to everyone who can read logs. Storing the shape, ranges and counts keeps the trace useful for review without widening access to the data itself.

Where do the judgment calls go?

Between the hard fails and the clean passes sits a middle band. It holds a join on an undeclared key, a scan between the warning line and the hard budget, and a draft whose claim the rows may not support. Code holds each one, and either a person or a decision model answers.

Route to a person where their knowledge changes the outcome. People stay off routine queries the checks already cover, because an analyst approving every safe SELECT learns to click approve without reading. We met the same problem on the DevOps agent, where the human gate is an architecture diagram rather than a template diff, because engineers do not read raw diffs carefully at the tenth change of the week. Show the reviewer the plan and the finding in the form they will actually read.

FinSight, our fund-operations reconciliation build, built and tested end to end and not yet in production, places approval by the same rule. Access and approval sit on exception resolution, because that is the one step where human judgment enters the data. Each review here goes back into the trace with its reason, and reviewed cases become eval cases.

A decision model can take some of the middle band. Jev, TypeSafe's decision model, in early access since September 2026, takes a state and a typed question (yes or no, a choice from fixed options, or a score on ordered levels) and returns probabilities instead of text. That shape suits the middle band, because code can put a threshold on a probability and cannot put one on a paragraph. TypeSafe calls the probabilities calibrated.

Our guide on introducing Jev into agentic workflows covers the order to add it in.

Whichever answers the middle band, hard fails stay in code and never reach a judge. The user's question, the plan and the SQL go into the judge as named fields, never into its question text. Text in a question, or in a table the agent reads, can carry prompt injection aimed at the judge. A threshold on a probability is still a number someone owns, and it changes through code review.

How should the steps run?

As a workflow with bounded steps, with the model free only inside each step. A free loop, where the model picks the next tool at every turn, is good at open exploration and poor at producing the same answer twice. The same question asked twice should run the same plan, pass the same checks and return the same number. The outer shape is therefore fixed code, the plan-and-execute pattern applied to analysis. The DevOps agent turns each request into a structured spec and a fixed chain of tool calls for the same reason, because a reviewer can only build judgment about output whose shape is stable.

The planner writes the whole plan before any step runs, and code checks it against the metric layer and the budgets. A plan that needs twelve queries for a simple lookup is rejected before it spends anything. When a step's result changes what the rest of the plan should do, the model writes a revised plan and code checks it again. It does not improvise the next query mid-run.

Each result is stored under an ID that later steps read, so a follow-up such as "and for enterprise only" inherits the earlier scope from state rather than from the model rereading the transcript. The answer states its full scope, so a filter carried over from three turns ago stays visible to the reader.

Retries are typed. On the DevOps agent, retries sit in two places because they recover from different things: a malformed template inside the validation loop, and a transient provider failure around execution. Here a parse failure or soft rejection goes back to the model with the check named. A warehouse timeout gets one narrower attempt if the plan allows it, and a hard fail is never retried.

Every loop has limits in code on steps, retries, bytes and wall-clock time. An exhausted limit ends in a partial answer that names the failed step, or an escalation with the plan attached, never a guess.

Other agents reach the analytics agent as well as people. Expose typed tools through MCP, such as asking a metric question or running a saved analysis, and run each call as the calling user. Saved analyses can also become playbooks, which our guide on agent playbooks as MCP tools covers.

We do not give an analytics agent a tool that runs arbitrary SQL, because it would let a caller skip the plan, the metric layer and the answer check. Our guide on designing MCP tools sets out why such a tool is one tool in name and every operation in practice.

How do you explain an answer a month later?

Record one trace per question, linking the question, the plan with its definition versions, the SQL as executed and the plan estimates. It also holds each check's decision and reason, the query ID, a summary of the rows, and the answer with its citations. When a finance lead challenges a figure, that trace answers, and a rerun answers less because the data or the definitions may have changed since. The telecom engagement adds who asked and what the person approved to the same audit trail, because in its current phase every action has an approval attached.

Every check writes a record when it approves as well as when it denies, because a log of denials alone cannot show you a check that never fires. Our guide on building an AI audit trail covers linking those records to the decision each answer served.

A replay reruns a past question with the model, prompts, definitions, allowlist and thresholds pinned. Data can be pinned only within each warehouse's window. Snowflake Time Travel defaults to 1 day and allows up to 90 days for permanent objects on Enterprise Edition. BigQuery's FOR SYSTEM_TIME AS OF reaches back over a time travel window of 7 days by default, configurable down to 2.

A replay that differs from the original then has a cause you can name. Replaying a sample of past questions against a new prompt or model also catches regressions a fixed eval set misses.

How do you know it works before people rely on it?

Score the answer a metric owner would accept, not whether the SQL matches a reference query. Two different queries can both be right, and a query that matches the reference can still be wrong if the reference encodes an outdated definition. Build the set from real questions, taken from the requests analysts already get. Each case carries the answer the metric owner would give, the tolerance they accept, and the cases where the right behaviour is to abstain or ask. Our guide on LLM evaluation covers building a set from real failures.

Those firing rates need reading as well as counting. The FinSight design watches break rate per custodian, because a custodian that changes its format shows up there before anyone reads a release note. A sudden drop in breaks is as suspicious as a spike. A falling block rate here reads the same way: the model may be writing better SQL, or a parser change may have stopped detecting a pattern. Sampling blocked and allowed queries on a schedule separates the two.

Release one question class at a time, such as revenue by region from the metric layer, or ad hoc funnel questions that need free SQL. Shadow comes first: the agent answers beside the analysts, nothing reaches business users, and every disagreement becomes an eval case. Then business users see answers with their SQL while the middle band goes to a person. Only metric-layer questions earn a path with no one at run time.

The telecom engagement is at the start of that order. Every action is approved by a person, and the baseline measures are still being set, including analytics turnaround and the rate at which people correct the agent. Our guide on AI agent governance covers how autonomy is granted one action class at a time.

When can an analytics agent skip most of these checks?

When an analyst reads every query before anyone acts on the answer. An internal assistant for the data team needs a read-only role on reviewed views and a statement timeout. It also needs a byte cap where the warehouse has one, and the parser. The analyst is the answer check, and the metric layer can wait until business users ask questions directly.

A small PostgreSQL database with a few tables needs less of the plan check, because the timeout bounds the worst query well enough. A warehouse where one query can scan terabytes needs the plan check and a byte cap from the first day.

Once people outside the data team act on the numbers, with no analyst between the agent and the reader, every part of this guide applies. Start with the metric layer, because a wrong definition is the failure nobody downstream can see.

Common questions

What is text to SQL?#

Text to SQL is a language model turning a question in plain words into a SQL query against a database. On its own it returns a query. An analytics agent adds a plan against governed metric definitions, checks on the SQL before it runs, limits in the database, and a check that the answer matches the rows, which is where most production failures are caught.

What is an analytics agent?#

An analytics agent answers business questions by planning, writing and running queries against a data warehouse and describing the results. In production it is a fixed workflow that plans against governed metric definitions, generates SQL, checks it, runs it under database limits, and checks that the answer matches the result before anyone reads it.

Is it safe to let an AI agent run SQL against a production warehouse?#

It can be, if the safety does not depend on the model. The agent connects with a read-only role limited to reviewed views, and every query is parsed and rejected unless it is one allowlisted SELECT. A plan or dry-run check blocks large scans, and the warehouse enforces a timeout and, where it offers one, a byte cap. Each layer assumes the one above it can fail.

Do we need a semantic layer before building an analytics agent?#

For questions about core metrics, yes, or something that does the same job. Without governed definitions the agent invents one in each prompt, and two answers about revenue can disagree. An existing semantic layer is a good source, and questions outside it can still use free SQL, with closer review and the same checks.

Why is a read-only database role not enough?#

A read-only role stops writes, and most analytics failures are not writes. A read-only query can scan a whole table of events, repeat rows through a bad join, return personal data, or produce a number from the wrong definition. The parser, the plan check, the metric layer and the answer check catch those, and the role remains the backstop.

Does an EXPLAIN check catch every expensive query?#

No. EXPLAIN returns estimates, and stale statistics can make a costly query look small. BigQuery documents its estimate as an upper bound, and on clustered tables that can block a query that would have fitted. The statement timeout, and a byte cap where the warehouse has one, catch what the estimate misses. In PostgreSQL, never use EXPLAIN ANALYZE in the check, because it executes the query.

How accurate are text-to-SQL LLMs?#

On public benchmarks, close to people on clean schemas and well below on enterprise ones. On 22 October 2026 BIRD's top test score was 82.95% against a human 92.96%. On Spider 2.0's real BigQuery and Snowflake workflows, o1-preview solved 21.3% of tasks when the benchmark came out. None of these measure business definitions or whether the answer describes the rows correctly.

How do we measure whether an analytics agent is accurate?#

Compare its answers with the number the metric owner would give, on real questions, within the tolerance they accept. Track whether it used the right definition, whether it abstains on ambiguous questions, how often each check fires, queries blocked wrongly, and wrong answers that passed every check. SQL exact match is a poor measure, because different queries can be correct.

Where does Jev fit in an analytics agent?#

Jev is TypeSafe's decision model, which returns probabilities for typed questions instead of text. It can answer the judgment calls between a clean pass and a hard fail, such as whether an unusual join is likely to repeat rows, and code turns its answer into deny, escalate or allow. Hard failures stay in code and never reach it. Bigcircle has not run it on a client's analytics agent.

Further reading

• How to introduce TypeSafe's Jev into agentic workflows. The order to add a decision model to an agent's checks.
• Shared business context for every AI agent. Governed metric definitions with owners and versions.
• How to set AI agent permissions and access control. Binding each query to the asking user and task.
• Rolling out AI agent autonomy. The release model the question classes follow.
• How to build LLM evals. Building the eval set from real failures.
• How to build an AI audit trail. Where each check's decision record belongs.
• MCP prompts vs tools for agent playbooks. Publishing saved analyses as tools other agents can search.
• How to design MCP tools for agents. Why a tool that runs any SQL is every operation in practice.
• BIRD, text-to-SQL benchmark and leaderboard, read 22 October 2026. Execution accuracy on 95 databases, with the human baseline.
• Lei et al., Spider 2.0: evaluating language models on real-world enterprise text-to-SQL workflows, ICLR 2025 version. The 21.3% figure for o1-preview.
• Spider 2.0, leaderboard, read 22 October 2026.
• sqlglot, SQL parser and transpiler. The parser in the sketch.
• PostgreSQL, EXPLAIN. Output formats, and the ANALYZE option that executes the statement.
• Google Cloud, BigQuery cost controls. Dry runs, maximum bytes billed, and the clustered-table upper bound.
• Snowflake, EXPLAIN and Time Travel. Partition estimates per operation, and querying a table as it stood.
• Google Cloud, BigQuery time travel and accessing historical data. The window, and FOR SYSTEM_TIME AS OF.
• TypeSafe, Introducing System One models and Jev, 15 September 2026, and the question types.