The hard part of a data analysis AI agent is the schema, not the model

The short version

• An AI agent for data analysis is a loop: write a query, run it, read the result, decide what to ask next.
• The limiting factor is almost never the model. It is whether the schema records which table is canonical and what each metric means.
• A text to SQL demo works on six tidy tables. A real warehouse has hundreds, half of them deprecated, and the agent cannot tell which is which.
• An agent that cannot say 'I cannot answer that from these tables' will produce a number instead.
• Grade the result the query returns, not the SQL the model wrote. Two queries that look nothing alike can both be right.

What does an AI agent for data analysis actually do?

It writes a query, runs it, reads what came back, and decides what to ask next. That loop is the difference between an agent and a text to SQL box. A single-shot generator hands you a query and hopes. An agent gets the error message, the empty result set or the number that looks wrong, and takes another step.

In practice it is given a few tools and left to choose between them: list the tables, read a schema, run SQL, sometimes run Python over the result, sometimes look up how a metric is defined. Most first queries fail on a wrong column name or a syntax quirk of the warehouse. Recovering from that is mechanical, and it is a large part of the value. The rest is multi-step work: get the monthly totals, notice one month looks short, check whether that month is complete, then answer.

What it is not is a replacement for a number you look at every morning. AI agents for data analysis get sold as a stand-in for the analyst, and the useful version is narrower than that. A figure on a daily dashboard should come from a query a person wrote once, reviewed, and can diff. Regenerating it from a model on every page load adds variance to something whose whole job is to be stable. Agents are for the questions nobody has written a query for yet.

Why does it work in the demo and fail on your warehouse?

Because the demo schema is honest and yours is not. Six tables, one row per thing, names that mean what they say, no deleted rows, no test data. Your warehouse has hundreds of tables, several of which look like the right one.

Here is what a model cannot infer from column names: that orders with a non-null deleted_at do not count, that a few hundred accounts are internal test accounts, that revenue_gross is before refunds and nobody uses it, that the staging copy of the customers table is two days stale, that amounts in one table are in cents and in another in pounds, that status has eleven values of which three mean cancelled. The agent picks the column whose name best matches the question. That is the mechanism, and it is doing exactly what it was built to do.

The failure is quiet. A wrong number has the same shape as a right one: no stack trace, no empty result, just a figure with a confident sentence around it. Most teams find out weeks later, when somebody who knows the data looks at a slide and says that number is far too high.

What do you have to build before an agent can be trusted with your data?

A small, deliberately boring surface for it to query. Not the warehouse. The useful way to think about it: you are not giving the agent access to data, you are writing down the things your organisation already knows and never recorded anywhere a machine can read.

That means curated views, agreed metric definitions, and filters applied before the agent ever sees a row. It is unglamorous and it is the project. Wiring up the agent takes days. This takes weeks, and it is the same work that makes your existing reporting agree with itself.

What to put in place first

• •A handful of curated views rather than the whole warehouse. Ten to twenty usually covers the questions people actually ask.
• •Joins done inside the views, so the agent never has to guess a join key.
• •Column names in business language, and exactly one column per concept. If two columns could both mean revenue, rename one or drop it.
• •One written definition per metric, stored where the agent can read it, and used by the dashboards too.
• •Test accounts, soft-deleted rows and internal users filtered in the view, not left to the agent to remember.
• •A read-only role, a default row limit, a query timeout, and a cost ceiling per session that stops the loop rather than logging a warning.
• •Permissions resolved before the query runs, so the agent cannot read a table the person asking could not open themselves.

How do you know whether the answers are right?

You build a set of questions whose answers you already know, and you compare the results, not the SQL. Two queries that look nothing alike can both be correct, so grading generated SQL against a reference query penalises the agent for being different rather than for being wrong.

Fifty questions is enough to start. Write the expected answer once with somebody who knows the data, store it, run the set nightly, compare numbers with a tolerance. Include three categories teams forget: questions the exposed tables cannot answer, questions that stay ambiguous until somebody picks a definition, and questions where the honest answer is that last month is not loaded yet. For all three, the correct behaviour is not a number.

Then track the operational figures: how often the first query errors, how many steps a question takes, what a question costs, and how often the agent declines. A refusal rate of zero is a red flag, not a win. Re-run the set after every schema change, because the day somebody renames a column upstream your curated view breaks and the agent carries on answering.

Which pattern fits the question you have?

Which questions should you not give to a data analysis AI agent?

Three kinds: numbers that leave the building unchecked, metrics your own teams disagree about, and questions about cause.

Anything heading for a filing, a board pack or an investor update needs a named human who owns the figure, and an agent does not change that. Contested metrics are worse than they look. If finance and sales do not agree on what counts as an 'active customer', the agent will silently pick one definition and the argument will move from the definition to the tool. Settle the definition first and the agent becomes useful the same week.

Causal questions are the common trap. 'Why did churn rise last month' is not a question about the churn table. The answer is usually a price change, an outage, a competitor or a billing bug, and none of those are columns. An agent can tell you which segment moved, which is genuinely useful, and it should stop there.

There is also the case where the honest answer is to build nothing. If the questions do not repeat, an analyst answering them in ten minutes each is cheaper than weeks of schema work. Count the questions before you count the savings.

Where does the money actually go?

Into the warehouse, not the model. Tokens for a query-writing loop are small next to the scans those queries trigger.

A loop that retries four times against a wide table pays for four scans of it. The guards are ordinary: a default row limit, pre-aggregated views so common questions never touch raw events, caching keyed on the exact query text, a byte or spend ceiling per session, and schema information cached instead of re-read on every turn. Exploring against a sample and running only the final query in full is usually the single biggest saving.

The larger cost is the curation work in front of all this, and the reason it is worth doing is that none of it is agent-specific. Clear views, one definition per metric and filters applied in one place make every dashboard and every scheduled report more correct as well.

Do the adoption surveys tell you whether to do this?

Less than the headlines suggest, because the big surveys measure different populations by different methods. Eurostat surveyed 157,000 of the EU's 1.53 million enterprises with 10 or more employees in the first months of 2025, and defines a small enterprise there as 10 to 49 employees. The Federal Reserve Banks' 2026 report on employer firms draws on 6,525 responses from firms with 1 to 499 employees, fielded in autumn 2025, and states openly that it is a nationwide convenience sample rather than a random one. The SBE Council polled 517 employers with 2 to 99 employees in February 2026, with a credibility interval of plus or minus 4.4 percentage points.

Three definitions of 'small', three sampling frames, three dates. Figures from one are not interchangeable with figures from another, and lifting a percentage from one survey to compare against another is the most common mistake in this genre. None of them tells you whether a query agent will work on your schema, which is the only question that decides the outcome.

The figure worth holding loosely is about the work rather than the adoption. A survey commissioned by Intuit QuickBooks of 630 owners and executives at US businesses with 10 to 99 employees put manual data entry and reconciling data across apps at roughly 25 hours, with the two activities counted together as one number and reported as a weighted median. It was commissioned by a company selling software that removes that work, and the page carries no publication date. Treat it as a direction of travel, not a measurement.