ExamFinch.com My Study

Study guide

7.2 Creating, Training & Evaluating Models with BigQuery ML

Practice this exam

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.2 Creating, Training & Evaluating Models with BigQuery ML

Key Takeaways Explain This Topic AI

BigQuery ML democratizes machine learning by executing model training, evaluation, and batch prediction directly inside BigQuery using standard SQL.

In-warehouse ML eliminates latency, governance risks, and compute overhead associated with exporting massive datasets to external Python or Spark environments.

BQML supports diverse model types including Linear Regression (numeric), Logistic Regression (classification), Boosted Trees (non-linear tabular data), K-Means (unsupervised clustering), and ARIMA_PLUS (time-series forecasting).

The standard BQML workflow progresses methodically through CREATE MODEL, ML.EVALUATE to inspect performance metrics, and ML.PREDICT to generate inference results.

BigQuery ML automatically performs feature preprocessing—such as one-hot encoding categorical variables and standardizing numerical distributions—during model creation.

Last updated: October 2026

Creating, Training & Evaluating Models with BigQuery ML

Core Focus: BigQuery ML (BQML) enables data analysts and practitioners to build, evaluate, and operationalize machine learning models directly inside Google BigQuery using SQL. By eliminating data movement, BQML accelerates time-to-value while preserving enterprise data governance and security.

In traditional enterprise machine learning workflows, training a model requires multiple disparate steps: exporting data from the warehouse to Cloud Storage, provisioning dedicated compute clusters (such as Vertex AI Workbench, Dataproc, or local Python environments), writing feature engineering scripts with libraries like scikit-learn or pandas, training the model, and setting up dedicated microservice endpoints for prediction. This workflow incurs substantial network egress overhead, creates governance blind spots, and demands specialized data science skills. BigQuery ML changes this paradigm by bringing machine learning algorithms directly to the data warehouse engine.

The BigQuery ML Value Proposition

BigQuery ML leverages BigQuery's massively parallel Dremel execution slots to train and score models at scale without data movement.

Core Advantages of BQML

Zero Data Movement: Training and inference occur where the data already resides, eliminating network transfer latency and Cloud Storage egress costs.

Democratized Tooling: SQL analysts can build robust predictive pipelines without learning Python, R, TensorFlow, or PyTorch.

Built-in Governance and Lineage: Models are first-class BigQuery schema objects stored inside datasets, inheriting existing Google Cloud IAM policies, dataset access controls, and Cloud Audit Logging.

Automated Feature Preprocessing: BQML automatically performs one-hot encoding on categorical string columns, handles missing value imputation, and normalizes numerical features during training without requiring manual preprocessing code.

The Core BigQuery ML Workflow

The BQML operational lifecycle consists of four primary stages: [1. Prepare Data] ---> [2. CREATE MODEL] ---> [3. ML.EVALUATE] ---> [4. ML.PREDICT]

  • (Clean features via (Train in-warehouse (Inspect precision, (Generate batch
  • standard BigQuery SQL) using Dremel slots) recall, ROC AUC, RMSE) predictions)

Data Preparation: Use standard SQL views or tables to aggregate, clean, and select input features.

Model Training ( CREATE MODEL ): Define the model architecture, hyperparameters, and label column using the OPTIONS() clause, providing the training dataset via an AS SELECT statement.

Model Evaluation ( ML.EVALUATE ): Evaluate model performance on a held-out test dataset or let BigQuery evaluate on an automated data split to inspect metrics like accuracy, ROC AUC, or Root Mean Squared Error (RMSE).

Batch Prediction ( ML.PREDICT ): Pass new unlabelled feature records into ML.PREDICT() to generate predictions and probability scores in tabular format.

Supported Model Types for Data Practitioners

BigQuery ML supports a wide spectrum of machine learning tasks across supervised, unsupervised, and time-series domains:

1. Supervised Learning: Regression

LINEAR_REG (Linear Regression): Models a continuous numeric target variable (e.g., predicting customer lifetime value, home sale price, or transaction dollar volume).

BOOSTED_TREE_REGRESSOR / RANDOM_FOREST_REGRESSOR : Uses ensembles of decision trees (XGBoost engine under the hood) to capture complex non-linear relationships among tabular features for numeric predictions.

2. Supervised Learning: Classification

LOGISTIC_REG (Logistic Regression): Predicts qualitative categorical outcomes. Supports binary classification (e.g., churn: TRUE/FALSE) and multiclass classification (e.g., support ticket priority: Low, Medium, High).

BOOSTED_TREE_CLASSIFIER : Highly effective for tabular classification problems where tree-based feature interactions outperform linear boundaries.

3. Unsupervised Learning: Clustering

KMEANS (K-Means Clustering): Partitions unlabeled data into k distinct clusters based on Euclidean distance among features. Common applications include customer segmentation, behavioral cohorting, and anomaly detection (identifying records located far from cluster centroids).

4. Time-Series Forecasting: ARIMA_PLUS

ARIMA_PLUS : A fully managed, automated time-series forecasting model based on the statistical ARIMA algorithm. It automatically decomposes time-series data into trend and seasonal components, adjusts for holiday effects, detects and removes anomalies/spikes, and imputes missing time points.

SQL Syntax Breakdown: Creating a Supervised Classification Model

The following SQL example trains a binary logistic regression model to predict customer churn: CREATE OR REPLACE MODEL `customer_analytics.churn_prediction_model`

  • OPTIONS(
  • - Specify model architecture
  • model_type = 'logistic_reg',
  • - Specify target label column
  • input_label_cols = ['has_churned'],
  • - AUTO_SPLIT sizes the evaluation split from the row count (see below)
  • data_split_method = 'AUTO_SPLIT',
  • - Automatically balance class weights if churned users are a small minority
  • auto_class_weights = TRUE
  • ) AS
  • SELECT
  • tenure_months,
  • monthly_spend,
  • contract_type,
  • payment_method,
  • support_tickets_count,
  • has_churned
  • FROM `customer_analytics.customer_features`
  • WHERE account_status != 'SUSPENDED';
  • Key Options Parameters

model_type : Required string specifying the algorithm ( 'logistic_reg' , 'linear_reg' , 'kmeans' , 'arima_plus' , 'boosted_tree_classifier' ).

input_label_cols : Array containing the name of the target column being predicted. If omitted, BQML assumes the label column is named label .

auto_class_weights : When set to TRUE , automatically balances class weights inversely proportional to class frequencies, addressing severe class imbalances (common in fraud detection or churn).

data_split_method : Controls how BigQuery splits input data into training and evaluation sets ( 'AUTO_SPLIT' , 'RANDOM' , 'CUSTOM' , 'SEQ' , or 'NO_SPLIT' ). With the default AUTO_SPLIT , fewer than 500 rows are not split, 500 to 50,000 rows hold out 20% for evaluation, and larger inputs hold out 10,000 rows.

Model Evaluation: Understanding Performance Metrics

Once training completes, data practitioners must evaluate the model's predictive validity using ML.EVALUATE() : SELECT *

  • FROM ML.EVALUATE(
  • MODEL `customer_analytics.churn_prediction_model`,
  • (SELECT * FROM `customer_analytics.customer_test_features`)
  • );

If the second argument (the evaluation dataset) is omitted, BigQuery evaluates the model against the validation split automatically held out during training.

Classification Metrics

Accuracy: The proportion of total correct predictions (positive and negative) over total instances. Can be deceptive in imbalanced datasets (e.g., 99% accuracy on fraud when only 1% of transactions are fraudulent).

Precision: True Positives / (True Positives + False Positives). Measures exactness: of all customers predicted to churn, what percentage actually churned? High precision avoids wasting retention incentives on non-churners.

Recall (Sensitivity): True Positives / (True Positives + False Negatives). Measures completeness: of all customers who actually churned, what percentage did the model catch? High recall is critical when missing a positive case is costly (e.g., fraud or terminal disease detection).

ROC AUC (Area Under the Receiver Operating Characteristic Curve): Measures the model's ability to discriminate between positive and negative classes across all possible decision thresholds. A score of 0.5 represents random guessing, while 1.0 represents perfect classification.

Log Loss: Penalizes confident incorrect predictions; lower values indicate better calibration.

Regression Metrics

Mean Absolute Error (MAE): The average absolute difference between predicted and actual values in the original target units.

Mean Squared Error (MSE): The average of the squared errors, penalizing larger deviations more severely.

Root Mean Squared Error (RMSE): The square root of MSE, restoring the metric to the scale of the original target variable.

R-Squared (R²): The proportion of variance in the dependent variable explained by the model features (values closer to 1.0 indicate strong explanatory power).

Generating Batch Predictions: ML.PREDICT

To apply a trained model to new records, invoke ML.PREDICT() : -- has_churned is a BOOL label, so each probs array holds labels TRUE and FALSE

  • SELECT
  • customer_id,
  • predicted_has_churned,
  • (SELECT prob FROM UNNEST(predicted_has_churned_probs) WHERE label = TRUE) AS churn_probability
  • FROM ML.PREDICT(
  • MODEL `customer_analytics.churn_prediction_model`,
  • (SELECT * FROM `customer_analytics.active_customers`)
  • )
  • ORDER BY churn_probability DESC;
  • Prediction Output Schema

predicted_<label_column_name> : Contains the predicted class label (for classification) or predicted numeric value (for regression).

predicted_<label_column_name>_probs : For classification models, an ARRAY<STRUCT<label STRING, prob FLOAT64>> containing the predicted probability for every possible class, enabling custom thresholding.

More Evaluation and Inspection Functions

ML.EVALUATE returns summary metrics, but the exam also expects you to recognize the companion functions:

Function What it returns Typical use

ML.CONFUSION_MATRIX Counts of true and false positives and negatives at a threshold Explain precision and recall trade-offs to stakeholders

ML.ROC_CURVE True positive rate and false positive rate across thresholds Pick a decision threshold for a binary classifier

  • ML.TRAINING_INFO Loss per training iteration Spot overfitting or a model that stopped improving
  • ML.FEATURE_INFO Statistics for each input column Check data quality before trusting a model

ML.GLOBAL_EXPLAIN / ML.EXPLAIN_PREDICT Feature attributions overall or per prediction Explain which inputs drive the prediction -- Confusion matrix at a 0.6 threshold on held-out data

  • SELECT *
  • FROM ML.CONFUSION_MATRIX(
  • MODEL `customer_analytics.churn_prediction_model`,
  • (SELECT * FROM `customer_analytics.customer_test_features`),
  • STRUCT(0.6 AS threshold)
  • );
  • Choosing the Right Metric

Imbalanced classes (fraud, churn, rare defects): accuracy misleads; compare precision, recall, and ROC AUC.

False negatives are costly (missing fraud or a sick patient): favor recall .

False positives are costly (sending expensive offers or blocking good customers): favor precision .

Regression in business units (dollars, days): MAE and RMSE speak the stakeholder's language; RMSE punishes large misses more.

Common Exam Traps & Practitioner Scenarios

Exam Tip: Whenever an exam scenario describes structured tabular data residing in BigQuery that requires standard classification, regression, or forecasting, BigQuery ML is almost always the correct answer . Avoid selecting external pipelines that export data to Cloud Storage and provision Compute Engine VMs or Dataproc clusters.

Trap 1: Forgetting input_label_cols for Supervised Models

The Trap: An engineer attempts to train a logistic_reg model without specifying input_label_cols in OPTIONS() , while the target column in the SELECT query is named churn_flag .

The Reality: By default, BQML looks for a column named exactly label . If your target column is named anything else, omitting input_label_cols = ['churn_flag'] causes a compilation error.

Trap 2: Exporting BigQuery Data for Standard Tabular Models

The Trap: An architecture question asks for the fastest, most cost-effective way to train a linear regression model on 500 GB of sales data in BigQuery, proposing an ETL pipeline to export data to Cloud Storage and run scikit-learn on a Compute Engine VM.

The Reality: This introduces needless network egress, infrastructure provisioning, and maintenance. BigQuery ML trains linear regression directly in SQL in minutes without data movement.

Trap 3: Passing the Label Column into ML.PREDICT

The Trap: Assuming that ML.PREDICT requires the true label column in its input table.

The Reality: ML.PREDICT is used for inference on new, unlabelled records. Passing the target label column is not required (and will simply be ignored or overwritten by the predicted output column).