Study guide

7.4 Using Pretrained Gemini Models from BigQuery with Remote Connections

Google Cloud Associate Data Practitioner Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

About the Associate Data Practitioner Exam

2 Chapter 1: Data Processing Paradigms & Ingestion Strategies

1.1 ETL, ELT, and ETLT Transformation Paradigms 1.2 Data Transfer and Migration Tools 1.3 Data Quality, Cleaning, and Validation

3 Chapter 2: Data Formats & Storage Solution Selection

2.1 File Formats & Serialization Characteristics 2.2 Cloud Storage Buckets, Location Types & Storage Classes 2.3 Selecting Google Cloud Databases and Storage Services

4 Chapter 3: Loading Data & Modern Ingestion Architectures

3.1 Batch Loading Data into BigQuery 3.2 Streaming Ingestion & Deduplication Patterns 3.3 BigQuery External Tables, BigLake & Federated Queries 3.4 Schema Definition & Evolution for Loaded Data

5 Chapter 4: BigQuery SQL Analytics & Query Foundations

4.1 BigQuery SQL Syntax & Analytical Functions 4.2 Complex Data Types: Arrays, Structs, and JSON 4.3 Answering Business Questions with BigQuery: Worked Analyses

6 Chapter 5: BigQuery Performance Optimization & Cost Governance

5.1 Table Partitioning Strategies 5.2 Table Clustering & Data Skipping 5.3 Query Optimization, Execution Plans, and Cost Controls

7 Chapter 6: Interactive Analysis, Notebooks & Visual Presentation

6.1 Exploratory Analysis with Jupyter & Colab Enterprise 6.2 Looker Studio (Data Studio) Reporting & Dashboard Design 6.3 Enterprise BI with Looker & LookML Foundations 6.4 Building, Modifying & Sharing Looker Dashboards

8 Chapter 7: Machine Learning with BigQuery ML & Vertex AI

7.1 Planning an ML Project: BigQuery ML, AutoML, or Pretrained Models? 7.2 Creating, Training & Evaluating Models with BigQuery ML 7.3 Running Inference and Organizing Models in Model Registry 7.4 Using Pretrained Gemini Models from BigQuery with Remote Connections

9 Chapter 8: Pipeline Engines & Transformation Services

8.1 Transformation Engine Selection Matrix 8.2 Apache Beam & Cloud Dataflow Core Concepts 8.3 Stream Processing: Windowing, Watermarks, and Triggers

10 Chapter 9: Workflow Orchestration, Automation & Monitoring

9.1 SQL Pipelines with Dataform & Scheduled Queries 9.2 Workflow Orchestration with Cloud Composer & Workflows 9.3 Event-Driven Ingestion: Pub/Sub to BigQuery & Eventarc Triggers 9.4 Monitoring Pipelines: Dataflow Job UI, Cloud Logging & Cloud Monitoring

11 Chapter 10: Access Control, Governance & Data Sharing

10.1 IAM Principles & Role Hierarchies for Data Services 10.2 Cloud Storage Security & Access Controls 10.3 Fine-Grained Security: Column-Level & Row-Level Controls 10.4 Secure Data Sharing with Analytics Hub (BigQuery Sharing)

12 Chapter 11: Lifecycle Management, Reliability & Compliance

11.1 Object Lifecycle Management & Storage Tiering 11.2 High Availability & Disaster Recovery Architectures 11.3 Data Retention, Time Travel, and Point-in-Time Recovery 11.4 Encryption Key Management & Privacy Compliance

7.4 Using Pretrained Gemini Models from BigQuery with Remote Connections

Key Takeaways Explain This Topic AI

Using an LLM from BigQuery takes four steps: create a Cloud resource connection, grant its service account access, create a remote model, and call it from SQL.

The connection's service account needs the Agent Platform User role (roles/aiplatform.user, formerly Vertex AI User); without it, calls fail with a permission error naming the bqcx service account.

CREATE MODEL ... REMOTE WITH CONNECTION ... OPTIONS (ENDPOINT = 'gemini-3.5-flash') creates a remote model; creating it processes no bytes and incurs no BigQuery charge.

AI.GENERATE_TEXT, the preferred successor to ML.GENERATE_TEXT, is a table function that takes a prompt column and returns one response per input row.

Gemini model versions retire on a schedule (gemini-2.5-flash retires October 20, 2026), so check supported models when creating remote models.

Last updated: October 2026

7.4 Using Pretrained Gemini Models from BigQuery with Remote Connections

Core Focus: The exam guide asks you to "use pretrained Google large language models (LLMs) using remote connection in BigQuery." In practice that means four steps: create a Cloud resource connection, grant its service account access to the model, create a remote model over a Gemini endpoint, and call it from SQL with generative AI functions. No data leaves BigQuery for an external system you have to manage, and no model training is needed.

What You Can Do with an LLM in SQL

  • Task Example prompt over a table column
  • Classify "Label this support ticket as BILLING, TECHNICAL, or ACCOUNT."
  • Extract "Return the invoice number and total amount from this text as JSON."
  • Summarize "Summarize this customer review in one sentence."
  • Analyze sentiment "Is this review POSITIVE, NEUTRAL, or NEGATIVE?"
  • Translate "Translate this product description into Spanish."
  • Embed for search Generate vector embeddings, then find similar rows with VECTOR_SEARCH

Combined with object tables (Section 3.3), the same approach works on PDFs and images stored in Cloud Storage.

Step 1: Create a Cloud Resource Connection

A Cloud resource connection gives BigQuery its own service account for calling other Google Cloud services: bq mk --connection \

  • -location=US \
  • -project_id=my-project \
  • -connection_type=CLOUD_RESOURCE \
  • gemini_conn
  • # Show the connection, including its service account ID
  • bq show --connection my-project.us.gemini_conn

The connection belongs to a location. The dataset that will hold the remote model must be in the same location as the connection.

Step 2: Grant the Connection's Service Account Access

The connection's service account (it looks like bqcx-...@gcp-sa-bigquery-condel.iam.gserviceaccount.com ) needs the Agent Platform User role, roles/aiplatform.user , which was called Vertex AI User before the April 2026 rename. Grant it in the project where the remote model is created when you reference the model by name. Missing this grant produces an error saying the bqcx-... account does not have permission to access the resource.

  • Step 3: Create the Remote Model CREATE OR REPLACE MODEL `support.gemini_flash`
  • REMOTE WITH CONNECTION `my-project.us.gemini_conn`
  • OPTIONS (ENDPOINT = 'gemini-3.5-flash');

ENDPOINT names a supported Gemini model (or a full endpoint URL). Model versions retire on a published schedule (for example, gemini-2.5-flash retires on October 20, 2026), so check the supported-model list when you create or update a remote model.

REMOTE WITH CONNECTION DEFAULT uses the project's default connection instead of naming one.

Creating a remote model processes no bytes and incurs no BigQuery charge. You pay for model usage when you call it, plus normal BigQuery processing.

Remote models can also point to Anthropic Claude, Llama, and Mistral models from Model Garden, or to models you deployed to your own endpoint.

Step 4: Call the Model with SQL

AI.GENERATE_TEXT is a table function : you pass it a model and a table or query that produces a column named prompt , and it returns one row per input row with the model's response alongside your other columns. It is the preferred successor to ML.GENERATE_TEXT , which still works and differs mainly in output column names. SELECT *

  • FROM AI.GENERATE_TEXT(
  • MODEL `support.gemini_flash`,
  • (
  • SELECT
  • ticket_id,
  • CONCAT(
  • 'Classify the sentiment of this support ticket as POSITIVE, NEUTRAL, or NEGATIVE. ',
  • 'Reply with one word. Ticket: ', body
  • ) AS prompt
  • FROM `support.tickets`
  • WHERE created_date = CURRENT_DATE()
  • ),
  • STRUCT(0.0 AS temperature, 10 AS max_output_tokens)
  • );

A low temperature makes classification and extraction answers more consistent.

max_output_tokens caps response length and cost.

Test on a small WHERE or LIMIT subset before running the prompt over millions of rows.

BigQuery also offers row-level AI functions, such as AI.GENERATE , AI.GENERATE_BOOL , AI.GENERATE_INT , and AI.GENERATE_DOUBLE , that return typed values for use directly in a SELECT or WHERE , and AI.GENERATE_TABLE for structured output. For semantic search, AI.GENERATE_EMBEDDING (successor to ML.GENERATE_EMBEDDING ) creates embeddings from a remote embedding model, and VECTOR_SEARCH finds the nearest rows.

Choosing an LLM Approach

  • Requirement Best fit
  • Apply an LLM to rows already in BigQuery with SQL Remote model plus AI.GENERATE_TEXT

Translate or analyze text with a task-specific API Remote models over Cloud AI APIs (for example ML.TRANSLATE , ML.UNDERSTAND_TEXT )

  • Build a chat app or agent Agent Platform (Vertex AI) and the Gemini API directly
  • Teach the model a narrow company-specific style Tuning in Agent Platform, which is beyond the associate scope
  • Common Exam Traps

Forgetting the IAM grant: The connection's service account, not the user running the query, must have the Agent Platform (Vertex AI) User role.

Location mismatch: The connection, the dataset holding the remote model, and the data must line up by location.

Calling a table function like a scalar: AI.GENERATE_TEXT and ML.GENERATE_TEXT go in the FROM clause; you cannot wrap them around a column in the SELECT list.

Training a custom model for a generic language task: Sentiment, summarization, and extraction usually need only a pretrained model and a good prompt.

Expecting identical answers every run: LLM output can vary; lower the temperature and validate outputs before using them in reports.