top of page

How to use BigQuery conversational analytics and check the SQL

2 hours ago
10 min read
Cover with the title How to use BigQuery conversational analytics and check the SQL on a teal data skyline. Data Studios original cover

Conversational analytics in BigQuery lets an analyst type a question such as "compare completed revenue by region for October 2026" in the Google Cloud console and get back a written answer, a result table, often a chart, and the SQL that produced them. Google's documentation describes the feature as a way to chat with data agents or data sources in natural language, powered by Gemini for Google Cloud, and every answer runs real SQL or GQL jobs against your tables. Those jobs are billed at normal BigQuery compute rates.


The practical risk sits in the gap between a fluent answer and a correct one. A question that sounds precise to a person still leaves room for the model to include cancelled orders, read "October" with the wrong date boundary, or count orders where you meant customers. Google's own page warns that Gemini output can be plausible but factually incorrect and asks users to validate it. The workflow below starts from a small illustrative table, computes the correct answer in Python before the question is asked, and then shows where in the console to compare that answer with the SQL BigQuery generated.


All documentation and pricing facts below were checked on Google Cloud's pages on November 16, 2026. The conversational analytics overview was last updated on November 12, 2026, and the conversations page on November 7, 2026; neither page carries a Preview label.


··········


BIGQUERY CONVERSATIONAL ANALYTICS: DIRECT CONVERSATIONS AND DATA AGENTS.


BigQuery offers two ways to ask questions. A direct conversation points at one or more tables, views, datasets or projects with no extra context. You can start one from the BigQuery Agents page (Conversations tab, then New conversation, then the Knowledge sources tab, select the sources and click Chat) or from BigQuery Studio by opening a table from the Explorer pane and clicking Chat. Google states that direct conversations work without a data agent's context and processing instructions, so their results "can be less accurate."


A data agent is a saved configuration built on top of the same engine. It combines knowledge sources (tables, views, graphs and UDFs, up to 100 per agent) with customized table and field descriptions, agent instructions, verified queries and glossary terms. Google lists the order of precedence when these overlap: verified queries first, then glossaries, then agent instructions. Agents are opened from the Agent Catalog tab, and each question can run in Fast mode (the default) or Thinking mode.


Every answer contains a text summary, result rows, a chart where one fits, a Show thinking control that expands the agent's steps, and a "How was this calculated?" section. That last section holds the generated query and its result and offers to open the query in the query editor. For an analyst who needs to sign off on a number, it is the most useful element of the response.


........


Element

Direct conversation

Data agent

Where to start

Agents page or table Chat button

Agent Catalog tab

Business context

None beyond table metadata

Instructions, glossary, custom descriptions

Verified queries

No

Yes, including parameterized queries

Accuracy note in docs

Results can be less accurate

Context shapes answers

Sources limit

Selected tables, datasets or projects

Up to 100 knowledge sources


........


Agent locations are fixed when the agent is saved. The documented options are the US and EU multi-regions, Tokyo, Montréal, Toronto, Northern Virginia and Global, and agents created before June 4, 2026 sit in the global location.


··········


IAM ROLES AND COSTS FOR BIGQUERY CONVERSATIONAL ANALYTICS.


Access is controlled by Gemini Data Analytics roles plus the usual BigQuery data roles, and a missing data role surfaces as a "Schema_Resolution: Access Denied" message in the chat. The roles below come from Google's pages on creating conversations and data agents.


........


Task

Role documented by Google

Direct conversation

Gemini Data Analytics Stateless Chat User

Chat with a data agent

Data Agent User plus Gemini for Google Cloud User

Create a data agent

Gemini Data Analytics Data Agent Creator

Edit, share or delete an agent

Data Agent Owner (or Editor to edit)

Read the source tables

BigQuery Data Viewer on the table or dataset

Run the generated SQL

BigQuery Job User on the project

Converse with a dataset or project

Dataplex Catalog Viewer


........


Column-level security and masking still apply: the documentation lists the Fine-Grained Reader and Masked Reader roles for those cases, and states that agents can only access data the user is already allowed to see.


Costs come from two places. The BigQuery queries that run while you build agents or hold conversations are billed at BigQuery compute pricing. On Google Cloud's BigQuery pricing page, on-demand compute is listed at $6.25 per TiB processed after the first 1 TiB per month, which is free, with a minimum of 10 MB processed per table referenced and per query. The Data Cloud agents pricing page lists the Conversational Analytics Agent among agents billed at $3 per million input data tokens and $20 per million output data tokens, with a free trial of all Data Cloud agents through December 31, 2026. That page does not explain how tokens map to a single BigQuery console question. A fuller breakdown of the token charges sits in the Gemini in BigQuery pricing reference.


··········


WORKED EXAMPLE: AN ILLUSTRATIVE ORDERS TABLE IN BIGQUERY.


The table below is illustrative and was built for this exercise. It holds twelve orders in a table called shop.sales.orders, with two rows placed just outside October 2026 and two rows whose status is something other than completed. Those four rows are the traps a generated query has to handle.


........


order_id

order_date

customer_id

region

status

revenue_usd

A-1001

2026-09-30

C-01

East

completed

420.00

A-1002

2026-10-01

C-02

East

completed

310.50

A-1003

2026-10-03

C-03

West

completed

880.00

A-1004

2026-10-07

C-01

East

cancelled

260.00

A-1005

2026-10-12

C-04

West

completed

145.25

A-1006

2026-10-15

C-05

Central

completed

530.00

A-1007

2026-10-18

C-02

East

completed

199.99

A-1008

2026-10-22

C-06

Central

refunded

350.00

A-1009

2026-10-25

C-03

West

completed

640.00

A-1010

2026-10-28

C-07

Central

completed

275.40

A-1011

2026-10-31

C-08

East

completed

410.00

A-1012

2026-11-01

C-04

West

completed

720.00


........


Google's guidance on effective questions recommends stating exact date ranges, naming the aggregation and the grouping, and using words such as "compare" when a bar chart is wanted. A question written to that standard looks like this:


Compare revenue_usd by region for orders with status 'completed'
and order_date from 2026-10-01 to 2026-10-31 inclusive.
Return region, number of completed orders, number of distinct
customers and total revenue, sorted by revenue from highest to lowest.

A follow-up should restate what changes, as the same guidance suggests:


Using the same filters as the previous answer, show only the West
region and give the total completed revenue for October 2026.

··········


THE CORRECT RESULT AND THE SQL TO COMPARE AGAINST.


The expected answer was computed in Python with pandas on the illustrative rows, and the SQL below was executed locally in DuckDB on the same rows with identical output. Only the eight completed orders dated October 1 to October 31 count.


........


Region

Completed orders

Distinct customers

Revenue (USD)

Share

West

3

2

1,665.25

49.1%

East

3

2

920.49

27.1%

Central

2

2

805.40

23.8%

Total

8

6

3,391.14

100.0%


........


SELECT
  region,
  COUNT(*) AS completed_orders,
  COUNT(DISTINCT customer_id) AS customers,
  ROUND(SUM(revenue_usd), 2) AS revenue_usd
FROM `shop.sales.orders`
WHERE status = 'completed'
  AND order_date >= DATE '2026-10-01'
  AND order_date < DATE '2026-11-01'
GROUP BY region
ORDER BY revenue_usd DESC;

The half-open date range (greater than or equal to October 1, less than November 1) keeps the logic correct even if order_date is later changed to a TIMESTAMP, where a BETWEEN filter ending on October 31 would drop everything after midnight on the last day. The distinct customer total is 6, equal to the sum of the regional figures because each customer in the sample buys in one region only. With real data the overall distinct count can be lower than the regional sum, so it should be computed directly and never added up from the regional rows.


Each typical error moves the total by an amount you can recognize once the correct figure is known.


........


Error in the generated SQL

Total it returns (USD)

Difference

Correct filters

3,391.14

0.00

Status filter missing

4,001.14

+610.00

Date filter missing

4,531.14

+1,140.00

Row count used as customers

8 customers

+2


........


When the status filter is missing, East rises to 1,180.49 and Central to 1,155.40, so the ranking stays the same while the totals are wrong. A ranking that looks plausible is therefore weak evidence; the totals and the WHERE clause are what need checking.


··········


CHECKING THE SQL BEHIND A CONVERSATIONAL ANALYTICS ANSWER.


Open "How was this calculated?" on every answer that will leave your screen and read the generated query against four points: the table referenced, the WHERE clause (status values and date boundaries), the aggregation (SUM versus COUNT versus COUNT DISTINCT) and the GROUP BY columns. Use the option to open the query in the query editor, run it there, and compare the bytes processed with what you expect for the columns read. The guide to writing SQL with Gemini in BigQuery covers how to read the bytes estimate before running a query.


BigQuery labels the jobs that conversational analytics runs, which gives administrators an audit trail. The documented labels are ca-bq-job, data-agent-id and conversation-id, visible in the Labels section of the query job details pane. Filtering job history on those labels shows the queries and bytes scanned per agent or conversation.


The known limitations page of the Conversational Analytics API adds several boundaries that affect checking. BigQuery queries are limited to 500 GB of bytes processed, failed queries may be retried up to three times with a corrected query, flexible column names are not supported, structs occasionally fail, and the maximum output is 8,192 tokens. Questions that need forecasting or advanced statistics such as correlation and anomaly detection are listed as not yet supported, so those answers should not be taken from the chat at all.


Exports also have limits. The Download button on a data agent answer produces a PDF with up to 20 rows per table, a detailed PDF with up to 1,000 rows, or a CSV with up to 5,000 rows, so a result that must be complete should be pulled with the verified SQL in the query editor instead.


··········


TURNING CHECKED ANSWERS INTO A BIGQUERY DATA AGENT.


Once a query has been checked, a data agent can reuse it so that the next person asking the same question gets the approved logic. In the agent editor, the Verified Queries section accepts your own query (Add query, enter a Question, Generate SQL, edit, Run, then Add) or suggestions reviewed with Review suggestions. Parameterized questions use @ placeholders, and Manage query parameters sets each parameter's name, type, default value and description. A verified query for the follow-up question in the example could look like this:


SELECT
  ROUND(SUM(revenue_usd), 2) AS revenue_usd
FROM `shop.sales.orders`
WHERE status = 'completed'
  AND region = @region
  AND order_date >= @month_start
  AND order_date < DATE_ADD(@month_start, INTERVAL 1 MONTH);

Run locally with region set to West and month_start set to October 1, 2026, this query returns 1,665.25, matching the West row of the expected result. The glossary is the right place for a definition such as "revenue means revenue_usd on completed orders only", with synonyms like sales or bookings, and Google advises against defining the same term both in Knowledge Catalog and in the BigQuery custom glossary.


Before publishing, test the agent in the Preview section with the questions your team actually asks, including month boundaries and order status. Sharing then assigns the Data Agent User, Editor or Viewer role to each principal.


··········


COST CONTROL FOR CONVERSATIONAL ANALYTICS ON ON-DEMAND PRICING.


The agent settings include Maximum bytes billed, which must be at least 10,485,760 bytes (10 MB); left blank, it defaults to the project's query usage per day quota. Setting it per agent is the simplest guard against a question that triggers a full scan of a large table. Google's conversations page also names large tables, joins and frequent AI function calls as factors that raise query cost.


An illustrative sizing for a ten-person analytics team, computed in Python, shows how the BigQuery side adds up on on-demand pricing. The usage figures are assumptions for the example; the rates are Google's published prices.


........


Item

Illustrative value

Questions per month (10 analysts x 20 per day x 21 days)

4,200

Data scanned per question (assumption)

1.5 GiB

Data scanned per month

6,300 GiB (6.152 TiB)

Billable after 1 TiB free tier

5.152 TiB

BigQuery on-demand cost at $6.25 per TiB

$32.20 per month

Agent tokens per question (assumption)

20,000 input, 1,000 output

Token cost per question at $3 and $20 per million

$0.08

Token cost per month after the free trial

$336.00


........


Google does not publish a typical token count per BigQuery question, so the token rows show the formula at work with an assumed volume. The query bill stays small here because each question scans little data; wider scans raise the BigQuery line in direct proportion. The BigQuery pricing reference compares on-demand billing with editions for teams that already run reservations.


··········


DATA GOVERNANCE AND PROMPT ANALYTICS IN BIGQUERY CONVERSATIONAL ANALYTICS.


Google's documentation states that conversational analytics cannot perform write operations, cannot run DML queries and cannot execute remote functions. It respects VPC Service Controls, and conversation history is shared only with the user who created it. Conversations are deleted automatically after 180 days without updates, and conversations built on query results depend on cached results that typically persist for 24 hours.


Prompt analytics is on by default: Google analyzes anonymized prompt data to improve the product and states that this data is not used to train or fine-tune foundation models. Organizations that want to opt out must submit Google's opt-out form before November 23, 2026; from that date the documented route is the constraints/gcp.disablePromptAnalytics organization policy constraint. The same conversational engine also runs inside Google Sheets, described in the article on conversational analytics in Connected Sheets, where the same validation habits apply.


··········


BIGQUERY CONVERSATIONAL ANALYTICS: WHAT DEPENDS ON YOUR PROJECT.


Google's documentation confirms the core behavior: answers are produced by SQL or GQL jobs that run with the user's own permissions, the generated query is visible under "How was this calculated?", jobs carry labels for auditing, and data agents can store verified and parameterized queries that take precedence over instructions. BigQuery compute is billed at the project's normal rates, and the conversational analytics page states that GQL queries need an Enterprise or Enterprise Plus edition reservation and fail in on-demand projects.


What varies by project is configuration: roles granted per user, an agent location that cannot be changed after saving, the per-agent Maximum bytes billed, and the November 23, 2026 prompt analytics opt-out deadline. The Data Cloud agents free trial runs through December 31, 2026, and Google has not published how many data tokens a typical BigQuery question consumes.


For analysts the working rule is concrete. Compute or know the expected figure for at least a sample question, read the WHERE clause and aggregation of every generated query that feeds a report, and move each checked query into a verified query so the agent reuses it. In the illustrative table, a missing status filter added $610.00 to a $3,391.14 total while leaving the regional ranking unchanged, which is the kind of error only a check against the SQL will catch.


··········


FOLLOW US FOR MORE.


·····


DATA STUDIOS


·····


datastudios.org


bottom of page