How to write SQL with Gemini in BigQuery and check the bytes it scans
Gemini in BigQuery can turn a plain-English request into GoogleSQL, explain an existing query and suggest fixes for errors, all inside the BigQuery Studio query editor. Google lists SQL code generation and SQL explanation as generally available, and its Gemini for Google Cloud pricing page states that these core code-assist features carry no additional cost on the Standard, Enterprise and Enterprise Plus editions and on on-demand compute. The query that Gemini writes is billed like any other query, though, and on on-demand pricing the bill depends on the bytes it reads.
That makes two checks part of every Gemini-assisted query: whether the result is correct, and whether the query reads only the data it needs. Google's documentation warns that Gemini can produce output that seems plausible but is factually incorrect, and that it may suggest different syntax for the same prompt. The BigQuery console already gives the tools for both checks: the query validator and dry run show the bytes a query will process before it runs, and the Maximum bytes billed setting makes an oversized query fail without a charge.
The steps below use an illustrative orders table, a prompt for the SQL generation tool, the result the query must return, and a cost example computed for a dashboard workload. All documentation and pricing facts were checked on Google Cloud's official pages on November 9, 2026.
··········
GEMINI IN BIGQUERY SQL FEATURES AND THEIR STATUS.
The BigQuery documentation page "Write queries with Gemini assistance" describes several ways to get SQL from Gemini. The SQL generation tool sits next to the query editor and opens a Generate SQL with Gemini dialog where you type a prompt, click Generate and then insert, edit or refine the result, with a Show diff option when refining. In a new SQL query tab you can also write a comment that starts with a number sign, press Enter and accept the suggestion with Tab. To understand a query someone else wrote, you highlight it, click the floating Gemini button and choose Explain this query; the explanation appears in the Cloud panel.
Not every option has the same status. The table summarizes what Google's pricing page and the documentation list as of November 9, 2026.
........
Feature | Status listed by Google | Where it appears |
SQL code generation | GA | SQL generation tool, comment prompts |
SQL explanation | GA | Gemini button, Explain this query |
SQL completion | Preview | Suggestions while typing |
Convert comments to SQL | Preview | Gemini button on a highlighted comment |
Fix and explain SQL errors | Preview | Refine, Fix it, Gemini suggested fixes |
SQL generation in Gemini Cloud Assist | Preview | Gemini AI chat panel |
........
Google describes Preview features as offered "as is" with limited support, so a team that standardizes on Gemini for SQL work should build its routine around the GA generation and explanation features.
··········
GEMINI IN BIGQUERY REQUIREMENTS, ROLES AND DATA HANDLING.
Setup starts in BigQuery Studio: hovering over the Gemini icon prompts you to enable the required APIs, and the setup page then asks you to grant principals the BigQuery Studio User or BigQuery Studio Admin role. The query-writing page lists the Gemini for Google Cloud User role (roles/cloudaicompanion.user) on the project, with permissions such as cloudaicompanion.instances.generateCode and cloudaicompanion.instances.completeCode, and notes that the same permissions can come from custom roles. Individual features are switched on or off from the Gemini control in the BigQuery toolbar, and an administrator can also remove access by revoking the cloudaicompanion permissions.
Three documented limits shape how the tool is used. Prompts are supported in English only. Suggestions draw on metadata from recently queried tables, and Google recommends naming tables in backticks, adding column descriptions to schemas and explaining unclear column names or business terms in the prompt. Gemini in BigQuery also does not support the same compliance and security offerings as BigQuery itself, so Google advises using it only in projects that do not require the unsupported offerings.
On data use, the overview page states that Gemini in BigQuery does not use your data to train or fine-tune its models, and that Gemini for Google Cloud does not use prompts or responses to train its models without express permission. Enabling Gemini grants it access to your tables and query history so that it can ground its suggestions.
··········
WORKED EXAMPLE: A REVENUE QUERY WRITTEN WITH GEMINI.
The illustrative table below stands in for a production table, `my_project.sales.orders`, partitioned by order_date. It includes the details that commonly break generated SQL: a refunded and a cancelled order, an order with no region, and orders just outside the reporting month.
........
order_id | order_date | region | status | amount |
1001 | 2026-09-30 | West | completed | 120.00 |
1002 | 2026-10-01 | West | completed | 250.00 |
1003 | 2026-10-03 | East | completed | 90.00 |
1004 | 2026-10-05 | East | refunded | 300.00 |
1005 | 2026-10-09 | Central | completed | 175.50 |
1006 | 2026-10-12 | West | cancelled | 80.00 |
1007 | 2026-10-15 | East | completed | 410.00 |
1008 | 2026-10-18 | NULL | completed | 60.00 |
1009 | 2026-10-22 | Central | completed | 225.00 |
1010 | 2026-10-27 | West | completed | 140.00 |
1011 | 2026-10-31 | East | completed | 95.50 |
1012 | 2026-11-01 | Central | completed | 200.00 |
........
A prompt for the SQL generation tool should name the table in backticks, define every business rule and say how to treat missing values, because each rule left implicit is a decision Gemini makes on its own.
Using `my_project.sales.orders`, return revenue, number of orders and average
order value by region for October 2026 (order_date from 2026-10-01 to
2026-10-31). Count only orders with status = 'completed'. Keep orders with a
NULL region as their own row. Select only the columns the query needs and do
not use SELECT *. Sort by revenue, highest first.The query below is the correct answer to that prompt in GoogleSQL. Its logic was executed locally against the illustrative rows before publication, so you can compare whatever Gemini inserts into the editor with it line by line.
SELECT
region,
COUNT(DISTINCT order_id) AS orders,
ROUND(SUM(amount), 2) AS revenue,
ROUND(SUM(amount) / COUNT(DISTINCT order_id), 2) AS avg_order_value
FROM `my_project.sales.orders`
WHERE order_date BETWEEN DATE '2026-10-01' AND DATE '2026-10-31'
AND status = 'completed'
GROUP BY region
ORDER BY revenue DESC;The filter on order_date does two jobs. It limits the result to the reporting month, and because order_date is the partitioning column, it lets BigQuery read only the October partitions; Google's documentation describes this as pruning and states that filtering partitions reduces costs.
··········
[[ADPLUS_300x250_1]]
THE CORRECT RESULT TO CHECK GEMINI'S QUERY AGAINST.
Eight completed orders fall inside October, for total revenue of 1,446.00. The expected output, computed in Python from the illustrative table, is below.
........
region | orders | revenue | avg_order_value |
East | 3 | 595.50 | 198.50 |
Central | 2 | 400.50 | 200.25 |
West | 2 | 390.00 | 195.00 |
NULL | 1 | 60.00 | 60.00 |
........
Two errors are easy to spot against these numbers. If the generated query has no status filter, the refunded and cancelled orders are counted: East rises to 895.50 from four orders, West to 470.00 from three orders, and the October total becomes 1,826.00 across ten orders. If the query adds a condition such as region IS NOT NULL, or joins to a region lookup table with an inner join, the 60.00 order disappears and the regional rows no longer add up to the monthly total. Both versions run without errors, which is why a reconciliation against a known total is more reliable than reading the SQL for plausibility. Running Explain this query on the generated SQL gives a second, plain-language description of its filters that can be compared with the rules in the prompt.
The same review habits apply to SQL from any assistant, and the site's guide on writing SQL with Claude and catching fan-out and date errors covers join duplication and date-boundary mistakes in more depth. For background on how chat assistants translate questions into queries, see how ChatGPT writes SQL queries from natural-language prompts.
··········
CHECKING BYTES PROCESSED BEFORE RUNNING GEMINI'S SQL.
BigQuery's on-demand compute is listed on Google's pricing page at $6.25 per TiB, with the first 1 TiB of query data processed each month free. Charges are based on the data in the columns a query selects, rounded up to the nearest MB, with a minimum of 10 MB per table referenced and 10 MB per query. Google states that an explicit LIMIT does not reduce the charge on a non-clustered table, that queries returning an error are not charged, and that results served from the cache are not charged.
Before running any generated query, look at the validator in the query editor: when the query is valid, a check mark appears with the amount of data the query will process. The same estimate is available with the bq command-line tool's --dry_run flag, and Google states that you are not charged for a dry run. Google documents two exceptions. On tables protected by row-level security a dry run always returns 0 bytes, and the estimate for a clustered table is an upper bound.
For a hard stop, set Maximum bytes billed under Query settings, Advanced options. Google's documentation states that if the estimated bytes exceed the limit, the query fails without incurring a charge. On clustered tables the limit can make a query fail even when the actual bytes billed would have stayed under it.
··········
BIGQUERY ON-DEMAND COST OF THREE VERSIONS OF THE SAME QUERY.
The cost example uses illustrative assumptions: the orders table holds 800 GiB over 730 daily partitions with data spread evenly, the five columns the query uses hold 25% of the table's bytes, and a dashboard runs the query 20 times a day for 30 days without cache hits. The price is the $6.25 per TiB on-demand rate listed on November 9, 2026; every figure was computed in Python.
........
Version of the query | Data per run | Cost per run | Data per month | Monthly cost before free tier |
All columns, no date filter | 800 GiB | $4.88 | 468.75 TiB | $2,929.69 |
Five columns, no date filter | 200 GiB | $1.22 | 117.19 TiB | $732.42 |
Five columns, October partitions only | 8.49 GiB | $0.05 | 4.98 TiB | $31.10 |
........
If the 1 TiB monthly free allowance is not consumed by other queries on the billing account, the third version costs $24.85 a month. The gap between the first and third rows comes entirely from projection and partition pruning, both of which Gemini can get right or wrong depending on the prompt. Real tables are rarely uniform, so replace these assumptions with the validator figure for your own query. Teams that run graph workloads on the same pricing model can read the site's note on BigQuery Graph GQL queries on on-demand pricing, and analysts who would rather ask questions from a spreadsheet can compare this workflow with conversational analytics in Connected Sheets.
··········
A REVIEW ROUTINE FOR SQL GENERATED BY GEMINI IN BIGQUERY.
A short, repeatable routine catches most problems before a query reaches a dashboard or a scheduled job. First, read the table sources: the generation dialog offers an Edit Table Sources option, and the query should reference only the tables you named. Second, check every business rule from the prompt against the WHERE clause, especially status values, date boundaries and the treatment of NULL keys. Third, read the validator estimate and compare it with what the table's size and partitioning suggest; a full-table figure on a partitioned table means the date filter is missing or does not use the partitioning column.
Fourth, run the query on a period whose totals you already know, such as a closed month that finance has reconciled, and confirm that the regional rows add up to the known total. Fifth, keep a Maximum bytes billed value on exploratory work so that a mistaken query fails at no cost. Queries that feed reports used by other people should be reviewed by the analyst who owns the metric definition before they are saved or scheduled, because Gemini has no access to the business decisions behind a metric unless the prompt or the schema descriptions state them.
··········
GEMINI IN BIGQUERY FOR SQL: WHAT GOOGLE CONFIRMS AND WHAT YOU CONTROL.
Google's documentation and pricing pages confirm that SQL generation and explanation in Gemini in BigQuery are generally available, that these code-assist features have no additional cost across all BigQuery editions and on-demand compute, that prompts must be in English, and that Gemini does not use customer data, prompts or responses to train its models without express permission. SQL completion, comment conversion, error fixing and Cloud Assist SQL generation remain in Preview.
What Google does not control is the correctness and cost of each generated query. The bytes a query reads are billed at the on-demand rate whether a person or Gemini wrote it, so the validator estimate, partition filters on the partitioning column and a Maximum bytes billed limit belong in every Gemini-assisted workflow. Compliance requirements also need a project-level decision, since Gemini in BigQuery does not carry all of BigQuery's compliance offerings, and access is governed by the roles and toolbar settings an administrator assigns.
··········
FOLLOW US FOR MORE.
·····
DATA STUDIOS
·····
datastudios.org
