Build an AI Data Analyst Agent: Schema Grounding, SQL Sandboxing and the Rails That Stop a Wrong Number (2026)

What is an AI data analyst agent, architecturally?

A semantic grounding layer, a SQL generation step, a static validator, a sandboxed executor, a result verifier and a deterministic chart renderer, with a provenance card attached to every output. The model writes SQL. Everything else exists to make sure the SQL it wrote is safe to run and the number it returned is safe to quote.

The framing that gets this wrong is calling it chat with your data. That implies the hard part is the conversation, when the hard part is that a warehouse has a thousand columns, four tables that all look like they contain revenue, three definitions of an active user, and a fact table whose grain is not what its name suggests. A model asked to write SQL against that produces a query that runs, returns a number, and is wrong in a way nobody catches until a quarterly review.

The benchmark evidence is unusually clear and worth putting in front of anyone who thinks this is solved. On BIRD, execution accuracy sits around 73% under a strict exact-match standard. On Spider 2.0 (632 tasks built from real enterprise databases, many with over a thousand columns, on BigQuery and Snowflake) GPT-4o solved 10.1% against 86.6% on the original Spider, o1-preview reached 17.1%, and the peak reported figures were around 23.8% on Spider 2.0-snow and 23.4% on Spider 2.0-lite. A system architected on the assumption that the model gets the join right is architected against the evidence.

That is not an argument against building this. It is an argument for where the engineering goes. Every point of accuracy in a real deployment comes from narrowing what the model has to figure out: a curated semantic layer instead of a raw schema, certified metric definitions instead of ad-hoc aggregation, a small set of approved joins instead of the full graph, and a refusal path for questions outside the covered surface. The model then writes simple SQL over a well-described subset, a task it is genuinely good at.

This is a reference design: how I would build it and what the arithmetic says it costs. It draws on architecting AccioMatrix, an event-driven platform orchestrating multiple LLMs, agents and MCP tooling for 20+ enterprise clients, and on cutting a $200K-plus annual cloud bill by more than 70%, which is where my instinct for hard spend caps on anything that queries at scale comes from. I have not shipped a natural-language analytics product in production, and every figure below is modelled with its inputs printed.

What does the full architecture look like?

Twelve components in four columns: the surface and the router, grounding and generation, validation and sandboxed execution, and the warehouse plus rendering and provenance. The unusual feature: two independent gates sit between the model and the data, a static one before execution and a semantic one after it, because a query can be perfectly safe and completely wrong.

Start with the router, the cheapest accuracy win in the system. A large share of real questions ask for a metric that is already defined: monthly recurring revenue, weekly active users, gross margin by region. Answer those by parameterising a certified query, not by generating SQL from scratch. If the agent generates its own revenue calculation, it eventually produces a number that differs from the dashboard by 3%, and every subsequent conversation in that organisation is about which number is right rather than what the number means.

Semantic grounding is where the accuracy comes from. Do not put the schema in the prompt. Put a curated model in the prompt: the twenty certified tables, each with a described grain, the columns safe to filter on with their real meanings, the approved joins with their cardinality, the metrics with their canonical SQL, and the known traps: the deprecated table that still has rows, the column whose name says amount but whose unit is minor currency. Retrieve the relevant slice per question and cache the stable part of the prefix so it bills at roughly a tenth of input price.

The validation and execution column is deliberately paranoid, and it should be. The static validator parses the generated SQL into a syntax tree and rejects anything that is not a single SELECT over allowlisted objects, using a real parser rather than a regex and certainly not a line in the system prompt asking the model not to write DDL. Then the executor runs a dry run for an estimated byte scan, enforces a hard cap, applies a statement timeout and a row limit, and connects as a role with no write privileges anywhere. Defence in depth, because the model is untrusted input by construction: if a user can type a question, a user can attempt a prompt injection.

Everything after execution is about trust rather than correctness. The result verifier applies deterministic sanity checks. The chart specification is derived from column types by rules, so the model never draws an axis. And the provenance card (the SQL, the filters applied, the row count, the freshness of the underlying tables, the run id) appears next to the number rather than behind a tooltip. That last decision makes the output quotable, because it lets a reader check the thing instead of trusting it. The general layering argument is in the AI product architecture reference design.

What happens when someone asks a question, end to end?

Route, ground, generate SQL, parse it into a syntax tree and reject anything unsafe, dry-run for a byte estimate, execute under a read-only role with a hard cap, verify the result, derive a chart specification, write a short narrative and attach a provenance card. About eight seconds, three model calls, roughly five cents.

The step most implementations omit is the dry run, and it is free on BigQuery. A dry run returns the estimated bytes the query will process without executing it, so you can reject a query that would scan four terabytes before it costs anything. Enforcing maximum_bytes_billed does the same thing server-side, failing the query without incurring a charge. Both belong in the executor. A single unbounded SELECT against a large partitioned table is a $25 question, and the failure is not a model failure, it is a missing WHERE clause on a date partition.

The repair loop is capped at one retry and the retry must mutate the context. Sending the same request again after a SQL error reproduces the same error at low temperature and charges you twice. The retry has to append the exact database error, the offending identifier and the relevant slice of the curated schema, then take a fresh turn. If the second attempt fails, refuse and say why. An unbounded repair loop against a warehouse is the one place in this product where a bug can generate real money, because each attempt burns both tokens and scanned bytes.

Result verification runs before anything is rendered and it is mostly arithmetic. Zero rows returned is not zero. It is an unanswered question, and presenting an empty result as a value of zero is the single most dangerous non-error in the product. A result whose magnitude is far outside the historical range for that metric is flagged rather than charted. A result whose applied filters do not match the filters the question asked for is flagged. Only the last needs a model, and it runs on a cheap one.

Finally, the chart is a specification, not a picture. Given a result with one date column and one numeric column, a line chart with the date on x, formatted in the metric's unit, is a deterministic rule. Letting a model choose axes and labels introduces a class of error (a truncated y-axis, an invented unit, a mislabelled series) that is invisible to every check you have and highly visible in a board deck.

import { Parser } from "node-sql-parser";
import { BigQuery } from "@google-cloud/bigquery";

const parser = new Parser();

const BANNED_FUNCTIONS = new Set([
  "external_query", "net.host", "session_user", "current_user",
]);

export type Verdict =
  | { ok: true; tables: string[] }
  | { ok: false; reason: string; detail: string };

export function validateSql(sql: string, allowlist: Set<string>): Verdict {
  let ast;
  try {
    ast = parser.astify(sql, { database: "bigquery" });
  } catch (e) {
    return { ok: false, reason: "unparseable", detail: String(e) };
  }

  const statements = Array.isArray(ast) ? ast : [ast];
  if (statements.length !== 1) {
    return { ok: false, reason: "multiple_statements", detail: String(statements.length) };
  }
  if (statements[0].type !== "select") {
    return { ok: false, reason: "not_a_select", detail: statements[0].type };
  }

  // parser.tableList returns entries shaped "select::db::table"
  const { tableList } = parser.parse(sql, { database: "bigquery" });
  const tables = tableList.map((t: string) => t.split("::").slice(1).join("."));
  for (const t of tables) {
    if (!allowlist.has(t)) {
      return { ok: false, reason: "table_not_allowlisted", detail: t };
    }
  }

  const lowered = sql.toLowerCase();
  for (const fn of BANNED_FUNCTIONS) {
    if (lowered.includes(fn)) {
      return { ok: false, reason: "banned_function", detail: fn };
    }
  }

  return { ok: true, tables };
}

const MAX_BYTES = 10 * 1024 ** 3;   // 10 GiB -> at most ~$0.061 at $6.25/TiB
const TIMEOUT_MS = 30_000;
const MAX_ROWS = 5_000;

export async function execute(sql: string, allowlist: Set<string>) {
  const verdict = validateSql(sql, allowlist);
  if (!verdict.ok) return { status: "rejected" as const, verdict };

  // read-only service account: no write grants on any dataset
  const bq = new BigQuery({ projectId: process.env.ANALYTICS_PROJECT });

  // free: returns the byte estimate without running the query
  const [dry] = await bq.createQueryJob({ query: sql, dryRun: true });
  const estimated = Number(dry.metadata.statistics.totalBytesProcessed);
  if (estimated > MAX_BYTES) {
    return {
      status: "rejected" as const,
      verdict: { ok: false as const, reason: "byte_cap_exceeded", detail: String(estimated) },
    };
  }

  const [rows] = await bq.query({
    query: sql,
    maximumBytesBilled: String(MAX_BYTES), // warehouse-side belt to the dry-run braces
    jobTimeoutMs: TIMEOUT_MS,
    maxResults: MAX_ROWS,
  });

  return { status: "ok" as const, rows, estimatedBytes: estimated };
}

How do you stop it hallucinating a number into a board deck?

By making the number checkable rather than trusting it. Certified metrics carry canonical SQL so the agent agrees with the dashboard by construction. Deterministic verification catches empty results, implausible magnitudes and mismatched filters. And a provenance card puts the SQL, the filters, the row count and the data freshness beside the number so a reader can audit it in five seconds.

Empty results deserve special handling because they are the most dangerous output in the product. A query that returns no rows because the region name was spelled differently in the warehouse looks, downstream, exactly like a query that returns no rows because the value is genuinely zero. Render those two differently and force the distinction: an empty result is a failed question, and the correct output is no rows matched these filters, here are the distinct values that do exist in that column. That single behaviour prevents more bad decisions than any accuracy improvement you can buy.

Magnitude checking is the second cheap win. Store, for each certified metric, the trailing range of values it has taken at the relevant grain. A result well outside that range is not necessarily wrong (quarters do end, campaigns do land) but it is worth flagging, and the flag costs nothing. Present it as this is 4.1x the trailing twelve-week range for this metric rather than blocking it, because the flag is information for the reader, not a verdict.

The third rail is refusal, and it needs teeth. When the question asks for a metric that is not defined, say so and offer the closest certified metric with its definition, rather than construct a reasonable-looking aggregation. Organisations that let an agent invent metric definitions get a proliferation of subtly different revenue numbers, and the cost is not measured in dollars per query. It is measured in the meetings spent reconciling figures.

None of this requires a smarter model. It requires a metric catalogue, four deterministic checks and a UI decision about where provenance lives. Notice the shape: the same discipline as citation spans in a support answer and quoted spans in a research brief. In every one of these products the design move is the same: make the output carry the evidence that lets someone else check it, and delete anything that cannot.

metric: net_revenue
label: "Net revenue"
owner: "finance-analytics@example.com"
certified: true
certified_at: "2026-08-04"

grain: "one row per order line, deduplicated on order_line_id"
unit: "USD, minor units (cents) in the column, divided by 100 in the metric"
timezone: "UTC. Finance reports in UTC; product analytics uses local. Do not mix."

sql: |
  SELECT SUM(gross_amount_minor - discount_minor - refund_minor) / 100.0
  FROM analytics.fct_order_line
  WHERE order_status IN ('completed', 'partially_refunded')

dimensions:
  - name: region
    column: analytics.dim_customer.region_code
    join: "fct_order_line.customer_key = dim_customer.customer_key (many-to-one)"
  - name: product_line
    column: analytics.dim_product.product_line
    join: "fct_order_line.product_key = dim_product.product_key (many-to-one)"

time_column: analytics.fct_order_line.completed_at
partition_column: analytics.fct_order_line.completed_date   # ALWAYS filter this

traps:
  - "analytics.revenue_daily is DEPRECATED but still receives rows from a legacy job.
     It excludes refunds. Never use it for a revenue question."
  - "gross_amount_minor is in MINOR units. A query that forgets /100 returns a number
     that is 100x too large and still looks like money."
  - "Orders in status 'pending_capture' are not revenue. They are in the fact table."
  - "Querying without a completed_date filter scans the full partition set:
     roughly 4 TiB, about $25 on BigQuery on-demand, for one question."

plausible_range:
  grain: "weekly, all regions"
  p05: 780000
  p95: 1420000
  note: "Trailing 52 weeks. Flag, do not block, results outside this band."

refuse_if:
  - "The question asks for 'revenue' without qualifying gross or net AND the answer
     would differ by more than 2%. Ask which one instead of guessing."

What does one question cost?

About five cents on the model here, against roughly $25 of analyst time for the same ad-hoc question. The variance is the real story: the same architecture without a byte cap can produce a $25 warehouse charge on a single query, five hundred times the intended cost, and it arrives with no error and no alert.

The composition is unusual for an AI product. SQL generation is $0.0140, the narrative is $0.0180, verification is $0.0040, grounding retrieval is $0.0002, the amortised repair retry is $0.0064, and warehouse execution at 1.2 GiB scanned is $0.0073 at BigQuery's on-demand rate of $6.25 per TiB. Two thirds of the bill is models and a third is the warehouse. But the warehouse third is the only line with unbounded upside, because tokens are capped by your context window and scanned bytes are capped by nothing except the cap you set.

That asymmetry should drive the design. A missing partition filter on a large fact table is not exotic; it is what happens when a model writes a WHERE clause on completed_at instead of completed_date. Modelled against a 4 TiB partition set, that one query is $25. Enforce the cap in two places, a dry-run check in your executor and maximum_bytes_billed on the job (which BigQuery documents as rejecting the query before it incurs charge), and the same mistake becomes a rejection costing nothing.

Against the human baseline the case is straightforward. An ad-hoc question routed to a data analyst takes roughly 25 minutes including context switching and clarification, which at $60/hour fully loaded is about $25. The agent answers in eight seconds for five cents. But the honest comparison includes two things the arithmetic omits: the analyst is usually right and the agent sometimes is not, and the analyst's real cost is the two-day queue delay rather than the twenty-five minutes. The value here is latency and volume, not headcount.

The narrative line deserves scrutiny. At $0.0180 it is the single largest model cost, and the component most likely to assert something the query cannot support: a cause, a trend, a comparison to a period that was not queried. Many teams would be better served shipping the number, the chart and the provenance card with no prose at all, which removes 36% of the cost and an entire class of error at once. The routing and caching machinery that makes the rest cheap is covered in LLM routing and caching, cost per request.

What breaks in production, and how would you know?

Ten things, and almost all of them return a number. That is the defining property of this product: unlike a support agent, which can fail visibly, a data analyst agent fails by producing a well-formatted, correctly charted, confidently narrated figure that is wrong. No error rate moves and no dashboard turns red.

The two worst are grain errors and silent filter mismatches. A join at the wrong cardinality double-counts and returns a larger number that looks like good news, the direction of error least likely to be questioned. A filter that does not match the question (last quarter read as the last 90 days rather than the fiscal quarter) returns a number close enough to be plausible and different enough to matter. Both are caught by the same discipline: document the grain in the semantic layer, restate the applied filters in the provenance card, and make the restatement prominent enough that a reader notices when it is wrong.

The cost failure is the one with a number attached. An unbounded scan on BigQuery on-demand at $6.25 per TiB turns a five-cent question into a $25 one, and an agent looping repair attempts against a large table can do that repeatedly. Three defences: cap the repair loop at one attempt, enforce maximum_bytes_billed so the warehouse rejects the job before charging, and set a per-user daily quota so a single enthusiastic exploration cannot consume a month of budget. The quota is the one teams skip and the one that turns an incident into an inconvenience.

Then the failure that is organisational rather than technical. If the agent computes revenue its own way and finance computes it another, the organisation now has two numbers, and the cost is measured in meetings rather than dollars. This is why certified metrics with named owners are the first rail rather than a later refinement: the agent should be structurally incapable of disagreeing with the dashboard on a metric that has a definition.

Four signals catch most of the rest: the share of questions answered from certified metrics, the rejection rate at the static validator broken out by reason, bytes scanned per question at p95 and p99, and the human correction rate on answers that were charted. The last requires a thumbs-down with a reason, which costs one button and is the only direct measure of whether the numbers are right. Everything else tells you the system is running, not that it is correct.

“Unlike almost any other AI product, this one does not fail visibly. It fails by returning a well-formatted, correctly charted, confidently narrated number that is wrong, which is why every rail in the design is about making the number checkable rather than making the model smarter.”— The design premise of a data analyst agent

What does it take to build, and what should you skip?

About eight engineer-weeks for a two-person team plus two to four weeks of an analytics engineer's time on the semantic layer, which runs in parallel and is the real critical path. Skip multi-agent, skip fine-tuning, skip write access, skip open-ended exploration in v1, and skip any promise of full warehouse coverage.

Week one is the journal and the sandbox: a read-only role, a dedicated warehouse or virtual warehouse with its own quota, the dry-run path, the byte cap and the query journal recording SQL, bytes, cost and verdict for every attempt. Building the cage before the animal is the correct order and is nearly always reversed, because generating SQL is the fun part and it demos in an afternoon.

Weeks two and three are the static validator and the semantic-layer plumbing: AST parsing, the allowlist, retrieval over certified entities and cache-friendly prompt assembly. In parallel, an analytics engineer writes the metric definitions, and that work (twenty to forty metrics with grain, units, joins, traps and plausible ranges) is what determines whether the product works. No model substitutes for it, and no engineer without domain knowledge can write it.

Week four is generation with structured output and stated assumptions. Week five is the verification suite: zero-row handling, magnitude checks, filter-intent alignment and the provenance assembly. Week six is charts, as deterministic specifications driven by result shape and column types. Weeks seven and eight are evaluation: a golden set of a few hundred real questions with known-correct answers, the only way to know your accuracy on your warehouse, and worth more than any benchmark number you will read.

On skipping: this is not a multi-agent problem. A planner, a SQL writer and a critic looks appealing and mostly multiplies tokens; the reasoning is in multi-agent versus single agent. Do not give it write access, ever: no CREATE TABLE for scratch work, no materialised results, no temporary tables in a shared dataset. And do not promise coverage of the whole warehouse. Ship twenty certified metrics and four approved dimensions, be visibly excellent inside that boundary and visibly honest outside it, and expand from evidence. For the general scoping discipline, building an MVP in days with AI covers it, and the ongoing architecture work is what AI product development is for.