Data agents: text-to-SQL and analytics

Why it matters

"What was revenue by region last quarter" is a sentence a model can turn into SQL in a second. Whether the SQL means what the asker meant is the whole problem. A data agent sits between people who know the question and a warehouse that knows the tables, and its failure is unusual: the query runs, the number is plausible, and it is wrong. Nothing errors. Interviewers ask about text-to-SQL because it looks solved and is not, and a candidate who talks only about generation accuracy has missed where the risk lives.

The idea

Schema grounding

The model needs to know which tables exist, what the columns mean, how the tables join and what the business words map to. A raw schema dump gives it names, not meaning: amt, status_cd, three tables that could each be "orders". Grounding adds descriptions, sample values, correct join paths and business definitions: revenue excludes refunds and tax; an active customer ordered in the last 90 days. For a large warehouse the schema is retrieved, not pasted: the relevant tables, found by search over their descriptions, so the context holds twenty tables rather than two thousand.

Semantic layers and curated views

The real fix is to stop asking the model to rediscover the business on every query. A semantic layer defines metrics and dimensions once (revenue, region, quarter) over curated views with the joins and filters already applied, and the model generates against that vocabulary rather than raw tables. Ambiguity drops because there is one definition of revenue; permissions and performance are handled in the view. It costs analyst time and covers only what someone defined, so questions outside it fall back to raw SQL or a sandbox, with more caution.

The silent wrong answer

Valid SQL, wrong meaning: a join that doubles rows, a filter on created date instead of paid date, a currency column summed across currencies, a left join written as inner that drops the regions with no sales. The controls are not better generation; they are visibility and checks. Show the query beside the answer, every time, so a reader who knows SQL can see the filter. Row counts and the tables touched, so a tenfold jump is noticed. Sanity checks in code: totals reconcile to a known dashboard figure, no negative counts, the date range matches the question. Golden questions: a set with known answers, run on every prompt or model change as the regression suite. And confidence calibrated to the checks: an answer that failed reconciliation says so.

question   revenue by region, last quarter
sql        SELECT region, SUM(net_revenue) ... WHERE paid_at >= '2026-07-01' ...
rows       6     tables  fct_orders, dim_region     runtime 1.8 s
check      total 4.21M matches the finance dashboard (4.21M)

Run as the user

The model must never hold a credential broader than the person asking. The query executes under the asker's own database role, so row-level security and column masking apply exactly as they would in a BI tool; the agent cannot be talked into a table the user cannot see, by injection or by accident. A service account with a read-all role is the common shortcut and the common breach.

Cost and timeouts

On a large warehouse a careless query scans terabytes. The agent gets a statement timeout, a bytes-scanned or cost cap, a required partition filter where the warehouse supports one, and a dry run that estimates cost before execution. Results are capped in rows and the model sees a sample plus aggregates, never a million rows in context.

When to give a sandbox instead

For analysis beyond a query, cohorts, forecasts, charts, a sandbox with a dataframe library beats ever-longer SQL: the agent pulls an aggregate through the same run-as-user path, then computes and plots in code. The Code execution and sandboxes lesson covers containment; the data agent adds that the sandbox only ever sees what the user's role allowed.