1.5 Fine-Tuning Gemini Models from BigQuery

1.5 Fine-Tuning Gemini Models from BigQuery

Blueprint reference: Section 1.1, "Fine-tuning Gemini models using BigQuery."

This is one of the newest bullets on the exam guide and one of the least covered by older study material, which makes it disproportionately valuable. The scenario it tests is specific: an organization's labelled examples already live in BigQuery, and the question is whether they can improve a Gemini model without building a separate training pipeline. They can.

The Connection Is the Prerequisite

Nothing in this topic works without a BigQuery Cloud resource connection. The connection has its own service account, and that service account must be granted the Vertex AI User role on the project hosting the model. Every "the query fails with a permission error" distractor in this area resolves to this one grant.

-- Created once per project/region, then reused by every remote model and AI function
CREATE MODEL `analytics.gemini_support`
REMOTE WITH CONNECTION `us.gemini_conn`
OPTIONS (endpoint = 'gemini-2.5-flash');

The resulting model object is a pointer, not a copy. No weights land in BigQuery. When you call an AI function against it, BigQuery sends the rows to the Agent Platform endpoint and writes the responses back into the result set. Data residency follows the connection's region, which is why the connection region and the dataset region must match.

Supervised Tuning Driven from a BigQuery Table

Supervised tuning teaches a model a behaviour — a format, a tone, a taxonomy, a domain-specific style of reasoning — from labelled examples. In BigQuery, those examples are just rows: one column holding the prompt, one holding the desired response.

CREATE OR REPLACE MODEL `analytics.gemini_ticket_router`
REMOTE WITH CONNECTION `us.gemini_conn`
OPTIONS (
  endpoint = 'gemini-2.5-flash',
  max_iterations = 10,
  prompt_col = 'ticket_text',
  input_label_cols = ['routing_label']
) AS
SELECT ticket_text, routing_label
FROM `analytics.labelled_tickets`
WHERE split = 'train';

The tuning job runs on Agent Platform; BigQuery is the source of the training rows and the control surface. Practical points the exam likes:

• Example count matters more than epochs. A few hundred high-quality, consistently-labelled examples usually beat thousands of noisy ones. Inconsistent labelling is the most common cause of a tuned model that is worse than the base model.
• Hold out a validation split. Keep a split = 'eval' partition and score the tuned model against the base model on it. "Tuned" is not a synonym for "better."
• A tuned model is a new endpoint. It has its own version and its own cost profile, and it does not automatically inherit later base-model upgrades.

Tuning Versus Prompting Versus Retrieval

This decision is the real exam content. Get it wrong and every downstream answer is wrong.

The single most-tested confusion is tuning versus grounding. If the scenario mentions a knowledge base, a document corpus, changing prices, or "the model gives outdated answers," the answer is retrieval. Fine-tuning bakes a snapshot of facts into weights that cannot be updated without retraining, which is exactly the wrong architecture for changing data.

Generative AI as SQL

Beyond tuning, BigQuery exposes generative inference through a family of functions that operate row-wise over warehouse tables. These turn unstructured columns into analyzable ones without an external pipeline.

AI.GENERATE_TABLE is the one to remember for structured extraction, because it constrains output to a declared schema rather than asking the model to emit JSON and hoping it parses.

SELECT counterparty, effective_date, auto_renew
FROM AI.GENERATE_TABLE(
  MODEL `analytics.gemini_support`,
  TABLE `legal.contracts`,
  STRUCT('counterparty STRING, '
         'effective_date STRING OPTIONS(description = "ISO 8601 date"), '
         'auto_renew BOOL' AS output_schema)
);

The declared fields become real columns of the result, so nothing needs parsing downstream. output_schema is a single string listing name TYPE pairs, each optionally carrying an OPTIONS(description = ...) clause that tells the model what the field means — descriptions are the cheapest accuracy improvement available in the function. Supported types are STRING, INT64, FLOAT64, BOOL, ARRAY, and STRUCT, which is why a date is requested as a described STRING and cast afterwards rather than declared as DATE.

AI.COUNT_TOKENS deserves a habit: generative functions bill per token across every row, so a careless AI.GENERATE over a hundred-million-row table is an expensive mistake that a token estimate on a LIMIT 1000 sample would have caught.

Exam Traps

• Tuning to add knowledge. Wrong for anything that changes; use retrieval.
• Missing IAM on the connection service account. Vertex AI User is the grant.
• Assuming the tuned model auto-upgrades. It is pinned to the base version it was tuned from.
• Running generative functions over an unfiltered fact table. Sample and estimate tokens first.
• Region mismatch between the connection and the dataset.