How to Build a BigQuery AI Agent

BigQuery makes analytical queries fast and serverless, and it bills for the bytes each query scans. That pricing model turns an agent's SQL quality into a cost question as well as a correctness one: a generated query that forgets a partition filter answers a question about yesterday by scanning three years. This guide covers building an agent that is correct, permissioned, and affordable, drawing on FISTA Solutions' AI agents delivery on Google Cloud data. It complements how to build a text to sql agent and how to build a snowflake ai agent.

How should identity and access work?

Mode	Applies	Fits
Delegated user credentials	IAM plus row-level policies for that user	Interactive question answering
Scoped service account	IAM grants on specific datasets	Scheduled or background analysis
Broad service account	Everything it can reach	Nothing user-facing

Interactive agents should execute as the requesting user, so dataset and table IAM and row-level access policies apply and audit logs attribute the query to the person. Background agents use a service account with dataset-level grants limited to what the job needs.

Row-level access policies are the mechanism for restricting rows by principal, and they apply only when the query runs as that principal. A service account querying on everyone's behalf sees every row, which is a disclosure design rather than a convenience.

How is cost controlled?

By design, at three points. Before execution, every generated query is dry-run to estimate bytes scanned, and a hard limit refuses queries above a threshold, with the agent explaining why and offering a narrower alternative. During generation, the agent uses partition and clustering metadata to write queries that filter on partition columns and avoid selecting every column. After execution, bytes scanned and cost are recorded per question so the budget is visible.

Cached results and materialised views reduce repeated cost, and the agent should prefer them where the question allows. See ai cloud cost optimization.

What grounds SQL generation?

Metadata. Dataset and table descriptions, column descriptions, partition and clustering configuration, and INFORMATION_SCHEMA views that expose structure programmatically. Descriptions decide whether the agent chooses the right table among several similar ones; partition metadata decides whether the query it writes is affordable.

Most BigQuery projects have sparse descriptions. Enriching them for the datasets in scope is the preparation that determines quality, and it should include stating each table's grain and freshness, because an agent that joins a daily aggregate to an event-level table produces plausible nonsense.

How should SQL generation be constrained?

To defined tables and, where they exist, defined views that encode business logic. A generated query against a curated view that already applies the organisation's definition of an active customer is consistent with reports; a query that reconstructs that definition from raw tables usually is not.

Where a semantic layer exists, whether through a BI tool's model or through curated views, the agent should query through it. Where none exists, building curated views for the domain in scope is part of the project. See what is data lineage in ai.

How are questions translated?

Into SQL over curated tables with explicit filters, using metadata to choose, with clarification on ambiguity. The agent dry-runs, checks the byte estimate against the limit, executes, and returns the result with the SQL, the bytes scanned, the estimated cost, and the tables used.

Ambiguity is handled by asking. Date ranges, whether a metric is net or gross, and which of two similar tables applies are decisions to surface, because a wrong basis executed confidently is both a wrong answer and a wasted scan.

What should the agent return?

The result and its provenance: the exact SQL, the bytes scanned and cost, the tables and their freshness, and a console link where possible. That lets the user verify, adjust, and share, and it makes cost visible per question, which changes how people ask.

How is it evaluated?

Against real questions with SQL and answers the data team verified. Measure table and column selection, filter correctness, result match, and bytes scanned relative to an expert-written query for the same question, which catches expensive-but-correct SQL. Test as users with different row-level policies to confirm results differ correctly.

What about BigQuery's own AI features?

BigQuery ships AI-assisted SQL and analysis capability, and where it fits it should be used. A custom agent earns its place where the organisation needs its own presentation and integration, combined data from other systems, evaluation against its own reference questions, or cost and ambiguity handling that the platform feature does not provide.

What does the build sequence look like?

Two weeks on metadata enrichment and curated views for the domain in scope. One week on identity, IAM, and the dry-run cost gate. Two weeks on question translation with the data team testing. One week on presentation with provenance and cost. Then expansion dataset by dataset.

What goes wrong?

Broad service accounts. No dry-run gate, discovered on the invoice. Generated SQL without partition filters. Sparse descriptions. Queries against raw tables that contradict curated definitions. Silent disambiguation. And answers without cost, which teaches nobody what their questions cost.

How does the cost gate work in practice?

As a fixed sequence the agent cannot skip. Generate the SQL. Submit it as a dry run, which returns the bytes the query would scan without executing it and without charge. Compare against the limit configured for that user or use case. If under, execute and record actual bytes. If over, do not execute; instead explain the estimate, identify why it is large, usually a missing partition filter or a full-column select, and propose a narrower query.

Two refinements make this workable. Limits should vary by context, since an analyst investigating a problem legitimately needs more headroom than a dashboard-style question. And the agent should learn from the gate: queries that were narrowed and then accepted are examples of what affordable SQL looks like for that dataset, and they belong in the evaluation set.

How does this interact with slot-based pricing?

Differently but not less. Organisations on capacity-based pricing pay for slots rather than bytes, so an expensive query costs concurrency rather than money directly, degrading other workloads sharing the reservation. The dry-run gate remains valuable because bytes scanned still predicts slot consumption, and per-question monitoring shifts from cost to slot-milliseconds. The agent should still write partition-aware SQL; it is simply protecting other users' performance rather than the invoice.

How FISTA Solutions helps

FISTA Solutions builds BigQuery agents that execute as the requesting user with row-level security intact, ground in enriched metadata and curated views, dry-run every query against a byte limit, and return SQL and cost with every answer, through AI enablement, AI agents, and forward deployed engineers working with data teams. The record behind the approach is 150+ projects for 50+ companies with 99.9% uptime.

To give users conversational access to BigQuery without surprising finance, message FISTA on WhatsApp, or read how to build a text to sql agent.