Gemini in BigQuery

On Wednesday, October 28, Ravi does something unusual: he asks Mateo not to write any SQL himself. Instead, Mateo is to answer three of Lena's questions using Gemini in BigQuery, the AI assistance built into BigQuery Studio, and then check every answer as if a new colleague had written it. The point of the session is not that the assistant is good or bad, but that an analyst remains responsible for every number, whoever or whatever drafted the query.

What Gemini in BigQuery does

Gemini in BigQuery is part of Gemini for Google Cloud. Once an administrator enables it for a project and grants the right roles, it adds assistance at several points in an analyst's work. Google's documentation lists the features, some generally available and some in preview, and the list changes often.

Two practical facts belong in any exam answer about these features. First, they must be set up: the Gemini for Google Cloud API is enabled, and users need roles that permit Gemini assistance, so a user who sees no assistant icon may simply lack the setting or role. Second, Gemini works from metadata and the data it is allowed to read: table and column names, descriptions and, for some features, query history and profiles. Clear names and descriptions, the habits from lesson 5, make its suggestions noticeably better.

Question one: generating SQL from a prompt

Mateo's first question from Lena is: "What were net store sales by region in the first half of October, and how did that compare with the same period last year?" He types it as a prompt in the editor, with the curated dataset selected. Gemini produces a query within seconds.

-- Generated from: net store sales by region, Oct 1-15 2026 vs same period 2025
SELECT
  st.region,
  SUM(CASE WHEN s.sale_date BETWEEN '2026-10-01' AND '2026-10-15' THEN s.net_sales END) AS sales_2026,
  SUM(CASE WHEN s.sale_date BETWEEN '2025-10-01' AND '2025-10-15' THEN s.net_sales END) AS sales_2025
FROM `bl-data-prod.curated.sales` AS s
JOIN `bl-data-prod.reference.stores` AS st ON s.store_id = st.store_id
GROUP BY st.region;

The query looks right, and it would run. Ravi asks Mateo to review it with a checklist, the same one the team uses for any query a person writes.

Two findings change the answer. Without the channel filter, the numbers include web orders, so every region looks larger than Lena's store figures. Without a date filter, the query scans the whole table instead of two short ranges. Mateo adds WHERE s.channel = "store" AND (s.sale_date BETWEEN ... OR s.sale_date BETWEEN ...), the dry run estimate falls from about 6 GiB to under 100 MiB, and the totals now match v_store_daily for the same dates. The weekday difference does not need a code change, but it goes into the note to Lena.

Question two: explaining an inherited query

The second task is a 90-line query from a former team member that calculates "comparable store sales", the growth in sales from stores that were open in both periods. Nobody remembers exactly how it decides which stores count. Mateo selects the query and asks Gemini to explain it. The explanation is clear and mostly right: it describes the CTEs, the join to stores, and the comparison of two years. It also says the query "includes stores open for at least 12 months", but when Mateo reads the filter himself, it checks open_date <= "2025-01-01", a fixed date that someone forgot to update. Stores opened in 2025 are wrongly excluded from this year's figures. An explanation tool helps you read faster; it does not replace reading.

Question three: exploring in data canvas

Lena's third question is open-ended: "Is anything unusual happening with returns this autumn?" This is where data canvas shines. Mateo starts a canvas, searches for tables about returns, and adds curated.returns_by_product_day from GCP-220's pipeline. Each step is a node in a graph: a prompt such as "returns per day by category since August" produces a SQL node, and "chart this as lines" produces a chart node. He branches the graph to try a second view by region without losing the first. The canvas also suggests a summary in plain language beneath each chart.

The canvas surfaces a clear pattern: returns of footwear jumped in the second week of October and stayed high. Gemini's summary attributes the rise to "seasonal demand". Mateo checks the detail: almost all the extra returns are one boot model whose size chart on the website changed on October 6. The pattern is real; the suggested explanation is a guess. He writes the finding up with the evidence, and the merchandising team corrects the size chart that afternoon.

Checking generated SQL automatically

For queries that will run every day, Ravi adds one more safety net: compare the result of a generated or edited query with a trusted reference on a small sample before switching. The SQLite example below compares the original generated query and the corrected one on a handful of rows, alongside the trusted figure from the shared view.

import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE sales (store_id INT, channel TEXT, sale_date TEXT, net_sales REAL);
INSERT INTO sales VALUES
 (12, 'store', '2026-10-03', 900), (12, 'web', '2026-10-03', 250),
 (15, 'store', '2026-10-09', 1200), (0, 'web', '2026-10-10', 400),
 (12, 'store', '2025-10-04', 850), (15, 'store', '2025-10-08', 1100);
CREATE TABLE stores (store_id INT, region TEXT);
INSERT INTO stores VALUES (0, 'Online'), (12, 'East'), (15, 'East');
""")
generated = """SELECT st.region, SUM(net_sales) FROM sales s JOIN stores st USING (store_id)
               WHERE sale_date BETWEEN '2026-10-01' AND '2026-10-15' GROUP BY st.region"""
corrected = generated.replace("WHERE", "WHERE channel = 'store' AND")
trusted = {"East": 2100.0}   # from the shared view for the same dates

for name, sql in [("generated", generated), ("corrected", corrected)]:
    result = dict(db.execute(sql).fetchall())
    status = "matches" if result == trusted else "differs"
    print(f"{name:9} {result}  {status} the trusted figure")

generated {'East': 2350.0, 'Online': 400.0}  differs the trusted figure
corrected {'East': 2100.0}  matches the trusted figure

Governance, privacy and cost

Organizations often ask what happens to their data when Gemini is used. Google states that Gemini for Google Cloud does not use customers' prompts or data to train its models, and that data stays within the protections of the customer's project; an administrator should still confirm the current terms and the compliance programs that cover the feature before enabling it for sensitive data, because Google notes that some compliance offerings are not supported. Gemini respects the user's own permissions: it cannot query a table the user cannot read. Pricing is part of Gemini for Google Cloud, and Google's packaging of these features has changed more than once, so check the current pricing page rather than relying on a course.

There is also a cost risk that is easy to miss. Generated queries are written to be correct, not cheap, and as the first example showed, they may omit partition filters. Under on-demand pricing, a habit of running generated SQL without reading the bytes estimate can be expensive. The maximum bytes billed setting and custom quotas from lesson 4 apply to generated queries just as to hand-written ones.

Ravi ends the session by asking Mateo what he would tell a new analyst about the assistant. Mateo's answer goes into the team's onboarding notes: use it to get started faster and to read unfamiliar SQL, write a clear prompt that names the tables, the period and the definition you want, and never send a number onward that you have not checked yourself. The assistant changes how quickly an analyst drafts work, not who is accountable for it.