Study guide

7.1 Planning an ML Project: BigQuery ML, AutoML, or Pretrained Models?

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.1 Planning an ML Project: BigQuery ML, AutoML, or Pretrained Models?

Key Takeaways Explain This Topic AI

ML fits when there is a learnable pattern, historical labeled outcomes for supervised problems, and a decision the prediction will change; otherwise use a SQL rule.

Common task types map to BigQuery ML models: classification (LOGISTIC_REG, BOOSTED_TREE_CLASSIFIER), regression (LINEAR_REG), forecasting (ARIMA_PLUS), clustering (KMEANS), and recommendation (MATRIX_FACTORIZATION).

A standard ML project moves through framing, data collection and preparation, splitting, training, evaluation, prediction, and monitoring.

BigQuery ML AutoML (AUTOML_CLASSIFIER or AUTOML_REGRESSOR) automates feature engineering and model search within a BUDGET_HOURS budget of 1.0 to 72.0 hours (default 1.0).

Data leakage, such as using a refund date to predict returns, inflates evaluation scores and makes production predictions useless.

Last updated: October 2026

7.1 Planning an ML Project: BigQuery ML, AutoML, or Pretrained Models?

Core Focus: Exam section 2.3 starts before any SQL is written: identify which business problems suit machine learning, plan a standard ML project (data collection, training, evaluation, prediction), and choose between BigQuery ML, AutoML, and pretrained models. The rest of this chapter then covers training (7.2), inference and Model Registry (7.3), and large language models (7.4).

Vertex AI, the platform behind AutoML and Model Registry, was renamed Gemini Enterprise Agent Platform in April 2026. The exam guide uses the older names.

Is Machine Learning the Right Tool?

ML fits when all of these are true:

There is a pattern to learn that is hard to write as fixed rules (which customers will cancel, what demand will be next month).

There is historical data with the outcome , called the label , for supervised problems (past customers marked "churned" or "stayed").

A prediction changes a decision (who gets a retention offer, how much stock to order).

If a simple SQL rule answers the question ("flag orders over $10,000 for review"), use the rule. The exam rewards the simplest solution that meets the requirement.

Matching Business Problems to ML Task Types

Business question ML task Label BigQuery ML model types

Will this customer churn? Is this transaction fraud? Classification A category (yes/no, tiers) LOGISTIC_REG , BOOSTED_TREE_CLASSIFIER , RANDOM_FOREST_CLASSIFIER , DNN_CLASSIFIER , AUTOML_CLASSIFIER

How much will this customer spend? Regression A number LINEAR_REG , BOOSTED_TREE_REGRESSOR , DNN_REGRESSOR , AUTOML_REGRESSOR

How many units will we sell each day next month? Time-series forecasting Past values over time ARIMA_PLUS , ARIMA_PLUS_XREG

  • Which customers behave alike? Clustering (unsupervised) None KMEANS
  • Which products should we recommend? Recommendation User-item interactions MATRIX_FACTORIZATION

Which readings are unusual? Anomaly detection None (or past values) ML.DETECT_ANOMALIES with ARIMA_PLUS , KMEANS , or an autoencoder

Summarize, classify, or extract from text and documents Generative AI None (pretrained) Remote models over Gemini (Section 7.4)

The Standard ML Project Lifecycle

The exam guide lists the core stages as data collection, model training, model evaluation, and prediction. A production project wraps them with framing at the start and monitoring at the end:

Stage Key decisions Where it happens on Google Cloud

1. Frame the problem Define the label, the prediction moment, and the success metric (for example, recall above 80% on churners) Workshop with the business owner

2. Collect and prepare data Join sources, clean values, engineer features, avoid leakage BigQuery SQL, Dataform, Dataflow

3. Split the data Hold out evaluation data; for time-based problems, split by time data_split_method in CREATE MODEL

4. Train Choose a model type and options CREATE MODEL in BigQuery ML, AutoML, or custom training

5. Evaluate Compare metrics on held-out data with the success metric and a simple baseline ML.EVALUATE , ML.CONFUSION_MATRIX , ML.ROC_CURVE

6. Predict (inference) Batch scoring in SQL, or online serving from an endpoint ML.PREDICT , Model Registry and endpoints (Section 7.3)

7. Monitor and retrain Watch accuracy and input drift; retrain on fresh data Scheduled retraining, model monitoring

Data Leakage

Leakage happens when a training feature contains information that would not exist at prediction time. Predicting whether an order will be returned using refund_issued_date produces excellent evaluation scores and useless real predictions, because a refund date exists only after the return. Build features only from data available at the moment you would make the prediction.

Feature Preparation in BigQuery ML

BigQuery ML automatically one-hot encodes strings, standardizes numbers, and imputes missing values for many model types. For custom logic, the TRANSFORM clause stores preprocessing with the model, so the same steps run again at prediction time: CREATE OR REPLACE MODEL `customer_analytics.churn_model`

  • TRANSFORM (
  • ML.QUANTILE_BUCKETIZE(tenure_months, 5) OVER () AS tenure_bucket,
  • monthly_spend,
  • contract_type,
  • has_churned
  • )
  • OPTIONS (model_type = 'logistic_reg', input_label_cols = ['has_churned']) AS
  • SELECT tenure_months, monthly_spend, contract_type, has_churned
  • FROM `customer_analytics.customer_features`;
  • Choosing the Approach: BigQuery ML, AutoML, Pretrained, or Custom

Approach What you provide Strengths Choose it when

BigQuery ML built-in models SQL and a model type No data movement; SQL skills only; fast iteration Tabular data already in BigQuery and a standard task (classification, regression, forecasting, clustering)

AutoML through BigQuery ML ( AUTOML_CLASSIFIER , AUTOML_REGRESSOR ) SQL, a label, and a training budget ( BUDGET_HOURS , 1.0 to 72.0, default 1.0) Automatic feature engineering, model search, and hyperparameter tuning on tabular data You want the best tabular model with little ML expertise and can accept longer training

AutoML in Vertex AI (Agent Platform) A labeled dataset (tabular, image, or video) in the console No-code training for data types BigQuery ML does not train on, such as labeled images "Classify product photos into categories without writing model code"

Pretrained models and APIs (Gemini, Vision, Translation, Natural Language) Prompts or API calls; no training Immediate results for language, documents, and images The task is generic (summarize, translate, extract, classify text)

Custom training (Vertex AI / Agent Platform) Your own TensorFlow, PyTorch, or scikit-learn code Full control, GPUs and TPUs, any architecture Specialized models that no managed option covers -- AutoML from SQL: Google searches models and tunes them within the budget

  • CREATE OR REPLACE MODEL `customer_analytics.churn_automl`
  • OPTIONS (
  • model_type = 'AUTOML_CLASSIFIER',
  • input_label_cols = ['has_churned'],
  • budget_hours = 1.0
  • ) AS
  • SELECT * EXCEPT (customer_id)
  • FROM `customer_analytics.customer_features`;
  • Common Exam Traps

Exporting tabular data to train elsewhere: For standard models on data already in BigQuery, BigQuery ML avoids data movement and infrastructure.

Training on images with BigQuery ML clustering: Labeled image classification calls for AutoML or a pretrained vision model, not KMEANS on raw bytes.

Evaluating on training data: Always judge a model on held-out data; a perfect training score often means overfitting or leakage.

Using ML when a rule works: Fixed business thresholds belong in SQL, not in a model.

Ignoring the label: Supervised models need historical outcomes. With no labels, think clustering, anomaly detection, or a pretrained model.