ExamFinch.com My Study

Study guide

11.1 Machine Learning with BigQuery ML

Practice this exam

Google Cloud Professional Data Engineer Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

0.1 About the Google Cloud Professional Data Engineer Exam 0.2 Study Strategy & Cloud Data Engineering Methodology

2 Chapter 1: Designing for Security and Compliance

1.1 IAM Roles, Service Accounts, and Least Privilege for Data 1.2 Data Encryption, Key Management, and Privacy with Cloud DLP 1.3 Compliance, Data Sovereignty, and Geographic Placement 1.4 Multi-Environment Architecture: Development, Staging, and Production

3 Chapter 2: Designing for Reliability and Resilience

2.1 High Availability, Disaster Recovery, and Failover Patterns 2.2 Fault Tolerance and ACID Guarantees in Distributed Systems 2.3 Data Validation, Cleansing, and Quality Assurance with Dataform 2.4 AI-Assisted Data Preparation and LLM Query Generation

4 Chapter 3: Architecture Patterns and Flexibility

3.1 Modern Data Architectures: Data Lake, Warehouse, Lakehouse, and Mesh 3.2 Data Migration Strategies and Hybrid/Multi-Cloud Integration 3.3 Database Migration Service and Heterogeneous Database Migrations

5 Chapter 4: Ingestion Architectures: Pub/Sub and Streaming Sources

4.1 Cloud Pub/Sub Core Architecture and Ingestion 4.2 Pub/Sub Advanced Patterns: Dead-Lettering, Ordering, and Schemas 4.3 Change Data Capture (CDC) with Datastream and Batch Ingestion

6 Chapter 5: Stream and Batch Processing with Apache Beam and Cloud Dataflow

5.1 Apache Beam Core Concepts and Pipeline Design 5.2 Advanced Windowing, Watermarks, and Triggers 5.3 Dataflow Pipeline Optimization, Scaling, and Templates

7 Chapter 6: Data Transformation with Dataproc, Data Fusion, and Workflow Orchestration

6.1 Apache Spark and Hadoop with Cloud Dataproc 6.2 Visual Data Pipelines with Cloud Data Fusion 6.3 Workflow Orchestration with Cloud Composer 6.4 AI Data Enrichment inside Data Pipelines

8 Chapter 7: Storage Service Selection and Cloud Storage Architecture

7.1 Google Cloud Storage Service Selection Decision Matrix 7.2 Cloud Storage Architecture, Classes, and Lifecycle Management

9 Chapter 8: Deep Dive into Google BigQuery Storage

8.1 BigQuery Storage Architecture and Data Organization 8.2 Partitioning and Clustering for Performance and Cost 8.3 BigLake and Hybrid Querying across Clouds 8.4 Data Warehouse Modeling: Star Schemas, Denormalization, and Slowly Changing Dimensions

10 Chapter 9: Operational and NoSQL Databases: Bigtable and Cloud Spanner

9.1 Cloud Bigtable Architecture and Row Key Design 9.2 Cloud Bigtable Schema Management and Performance Tuning 9.3 Cloud Spanner: Globally Scalable Relational Database

11 Chapter 10: BigQuery Query Performance and Optimization

10.1 BigQuery SQL Tuning and Performance Best Practices 10.2 BigQuery BI Engine and Materialized Views 10.3 Data Sharing and Analytics Hub 10.4 Preparing Data for Visualization and BI Tool Connectivity

12 Chapter 11: Machine Learning Readiness and Data Governance

11.1 Machine Learning with BigQuery ML 11.2 Feature Engineering and Data Preparation for AI/ML 11.3 Data Governance, Cataloging, and Discovery with Dataplex 11.4 Unstructured Data, Embeddings, and Retrieval-Augmented Generation

13 Chapter 12: Site Reliability Engineering and Monitoring for Data Systems

12.1 SRE Principles for Data: SLIs, SLOs, and SLAs 12.2 Observability: Cloud Monitoring, Cloud Logging, and Alerting 12.3 Troubleshooting Data Pipelines and Workload Failures

14 Chapter 13: Workload Automation, CI/CD, and Cost Optimization

13.1 CI/CD and Version Control for Data Pipelines 13.2 BigQuery Cost Management and Capacity Planning 13.3 Holistic Cost Optimization across GCP Data Services 13.4 Query Workload Management: Interactive vs. Batch Jobs, Queues, and Quotas

11.1 Machine Learning with BigQuery ML

Key Takeaways Explain This Topic AI

BigQuery ML democratizes predictive analytics by allowing data engineers and analysts to build, train, evaluate, and operationalize machine learning models directly using SQL on data stored in BigQuery, eliminating slow and costly data exports to external Python environments.

BigQuery ML supports a comprehensive suite of algorithms across supervised learning (LINEAR_REG, LOGISTIC_REG, BOOSTED_TREE_CLASSIFIER, BOOSTED_TREE_REGRESSOR, DNN_CLASSIFIER), unsupervised learning (KMEANS, PCA, AUTOENCODER), matrix factorization for collaborative filtering recommendation engines, and automated time-series forecasting with ARIMA_PLUS.

The model development lifecycle follows standardized SQL syntax: models are trained using CREATE OR REPLACE MODEL ... OPTIONS(...), validated with ML.EVALUATE against key performance metrics (ROC AUC, log loss, precision, recall, RMSE), inspected via ML.FEATURE_INFO, ML.WEIGHTS, and ML.GLOBAL_EXPLAIN, and deployed for high-throughput batch inference with ML.PREDICT.

ARIMA_PLUS automates end-to-end time-series forecasting by decomposing trends, detecting multiple seasonalities (daily, weekly, yearly), adjusting for global holiday calendars, imputing missing data points, and filtering historical step changes and spikes, while supporting thousands of concurrent time-series via time_series_id_col.

BigQuery ML integrates seamlessly with Google Cloud Vertex AI: models trained in BigQuery can be registered directly in Vertex AI Model Registry with a single SQL option, or exported to Cloud Storage as TensorFlow SavedModel artifacts for low-latency online serving on Vertex AI Endpoints.

Last updated: September 2026

11.1 Machine Learning with BigQuery ML

Important

For the Google Cloud Professional Data Engineer exam, you must understand when to leverage BigQuery ML versus when to export data to external training environments like Vertex AI Custom Training or Compute Engine. BigQuery ML is the optimal, cost-effective choice when your training data already resides in BigQuery, the team is fluent in SQL, the model architecture is standard (regression, boosted trees, k-means, ARIMA+), and you need to avoid the security risks, ETL complexity, and network egress costs associated with moving petabytes of enterprise data.

In conventional enterprise machine learning workflows, data preparation and model training are bifurcated. Data engineers build pipelines to extract structured data from data warehouses, serialize it to object storage (such as Cloud Storage), and hand it off to data science teams who ingest it into dedicated Python or R environments (such as Jupyter notebooks, Scikit-learn, XGBoost, or PyTorch on GPU-equipped virtual machines).

This traditional paradigm introduces severe architectural friction: it creates duplicate copies of sensitive data, introduces security and access control drift, requires substantial network bandwidth for data movement, and limits model operationalization to teams with specialized deep learning infrastructure skills. BigQuery ML fundamentally eliminates this friction by bringing machine learning compute directly to the data warehouse engine.

The BigQuery ML Paradigm: In-Warehouse Machine Learning

BigQuery ML enables data engineers to train, evaluate, and execute inference on machine learning models using standard GoogleSQL queries. Under the hood, BigQuery ML leverages the disaggregated compute architecture of BigQuery: Borg worker slots execute distributed machine learning algorithms directly against Capacitor columnar storage blocks in Colossus across Google's petabit-scale Jupiter network fabric. +-------------------------------------------------------------------------+

  • | Conventional Machine Learning ETL |
  • | [BigQuery] -> (Export to GCS) -> [Python / VM] -> [Trained Model] |
  • | ^ | |
  • | +------------ (Load Batch Predictions) <-------------+ |
  • +-------------------------------------------------------------------------+
  • vs
  • +-------------------------------------------------------------------------+
  • | BigQuery ML Native Workflow |
  • | [BigQuery Tables] <---> [Borg Slots: Distributed SQL ML] <---> Models |
  • | * Zero data movement * Borg slot parallelism * Unified IAM ACL |
  • +-------------------------------------------------------------------------+

By executing model training within BigQuery's managed boundary, organizations gain several key operational advantages:

Zero Data Movement: Training occurs in place. Terabytes or petabytes of data never leave BigQuery, eliminating network egress fees, pipeline failure points, and data synchronization overhead.

Unified Security and Governance: BigQuery ML inherits all existing dataset access controls, Cloud IAM policies, Customer-Managed Encryption Keys (CMEK), and column-level policy tags.

Elastic Distributed Compute: BigQuery automatically parallelizes model training across hundreds or thousands of Borg slots, scaling linear algebra operations and tree splits without manual cluster provisioning or GPU driver configuration.

Rapid Time-to-Value: Existing data analysts and SQL developers can prototype, iterate, and deploy production-grade models without mastering external machine learning frameworks.

BigQuery ML Compute Governance and Slot Reservations

BigQuery ML queries consume computational capacity based on the project's billing and slot reservation model:

On-Demand Billing: Training queries consume slots from BigQuery's shared pool and are billed per terabyte of data scanned during model training iterations (with specialized pricing tiers depending on algorithm complexity).

Capacity Reservations (BigQuery Editions): In Enterprise and Enterprise Plus editions, organizations allocate dedicated slot reservations for machine learning workloads. Administrators can configure custom reservation assignments targeting the ML_EXTERNAL job type, guaranteeing that long-running iterative training jobs do not starve critical real-time BI dashboard query slots of compute capacity.

Supported Model Families and Algorithms

BigQuery ML supports a rich spectrum of algorithms spanning supervised learning, unsupervised clustering, time-series forecasting, recommendation engines, and imported pre-trained models.

1. Supervised Learning Models

Supervised models learn functional mappings from labeled training data:

Linear Regression ( LINEAR_REG ): Predicts continuous numerical targets (e.g., forecasting revenue, temperature, or transaction values). Employs L1 (Lasso) and L2 (Ridge) regularization to control overfitting. Engineers configure optimize_strategy between AUTO , BATCH_GRADIENT_DESCENT , or NORMAL_EQUATION depending on dataset dimensionality.

Logistic Regression ( LOGISTIC_REG ): Predicts discrete categorical outcomes. Supports both binary classification (e.g., customer churn: TRUE or FALSE ) and multiclass classification (e.g., support ticket routing into categories) using multinomial logistic regression with softmax cross-entropy loss. Includes auto_class_weights = TRUE to balance class representation automatically.

Boosted Decision Trees ( BOOSTED_TREE_CLASSIFIER & BOOSTED_TREE_REGRESSOR ): Built upon Google's distributed gradient-boosted decision tree framework (compatible with XGBoost). Exceptional for tabular datasets with complex non-linear feature interactions, mixed data types, and non-normalized numerical distributions. Configurable with max_tree_depth , learn_rate , subsample , and colsample_bytree .

Random Forest ( RANDOM_FOREST_CLASSIFIER & RANDOM_FOREST_REGRESSOR ): Ensembles multiple bagging decision trees to reduce variance, ideal for noisy tabular data without extensive hyperparameter tuning.

Deep Neural Networks ( DNN_CLASSIFIER & DNN_REGRESSOR ): Fully connected feedforward neural networks for multi-layered non-linear modeling on wide tabular schemas. Supports custom architectures via hidden_units = [128, 64, 32] , dropout = 0.2 , and standard optimizers ( ADAGRAD , ADAM , FRL ).

AutoML Tables ( AUTOML_CLASSIFIER & AUTOML_REGRESSOR ): Connects BigQuery directly to Vertex AI AutoML, automatically performing feature selection, architecture search, and hyperparameter tuning to construct an ensemble model without manual parameter tweaking, bounded by a specified budget_hours parameter.

2. Unsupervised Learning Models

Unsupervised models discover latent patterns without explicit target labels:

K-Means Clustering ( KMEANS ): Partitions observations into k k k distinct geometric clusters based on Euclidean or Cosine distance ( distance_type ). Features centroid initialization options ( KMEANS++ , RANDOM , CUSTOM ) and automated normalization ( standardize_features = TRUE ). Extensively used for customer segmentation, geospatial anomaly clustering, and cohort analysis.

Principal Component Analysis ( PCA ): Linearly projects high-dimensional feature spaces onto orthogonal principal components, reducing dimensionality while preserving maximum variance. Useful for data compression and multicollinearity reduction.

Autoencoders ( AUTOENCODER ): Neural networks trained to compress input data into a low-dimensional bottleneck representation and reconstruct the original input. Exceptional for unsupervised anomaly detection: transactions or machine sensor readings with high reconstruction error are flagged as anomalous outliers.

3. Time-Series Forecasting ( ARIMA_PLUS )

The ARIMA_PLUS model type provides an automated, enterprise-grade time-series forecasting engine. Unlike standard statistical ARIMA models that require tedious manual parameter identification ( p , d , q p, d, q p , d , q ), ARIMA_PLUS orchestrates an automated multi-stage pipeline:

Timestamp Cleansing & Resampling: Detects irregular time intervals, handles missing timestamps via forward-filling or interpolation, and aggregates duplicate timestamps.

Spike and Step Anomaly Detection: Identifies transient spikes and structural level shifts, adjusting baseline values so historical anomalies do not distort future projections.

Decomposition: Automatically decomposes the time-series into trend, weekly seasonality, and yearly seasonality components.

Holiday Effect Adjustment: Automatically incorporates public and cultural holidays across dozens of global jurisdictions via holiday_region , capturing anticipated shopping spikes or business slowdowns.

Auto-ARIMA Optimization: Fits hundreds of candidate ARIMA models and selects the optimal hyperparameters based on Akaike Information Criterion (AIC).

Multiple Time-Series Modeling ( time_series_id_col ): Fits thousands of independent time-series models concurrently in a single SQL statement by partitioning models across unique product SKUs, store IDs, or sensor channels.

4. Collaborative Filtering Recommendation Systems ( MATRIX_FACTORIZATION )

Designed specifically for recommendation engines (such as e-commerce product recommendations or media streaming suggestions). The algorithm decomposes a sparse user-item interaction matrix into low-rank dense user and item latent factor embeddings using Weighted Alternating Least Squares (WALS). It supports both explicit feedback (e.g., 1-5 star user ratings) and implicit feedback (e.g., clicks, watch durations, purchase frequencies).

5. Custom Model Import and Foundation LLM Integration

TensorFlow / ONNX / TFLite Import: Data engineers can import pre-trained models trained outside BigQuery using CREATE MODEL ... OPTIONS(model_type='TENSORFLOW', model_path='gs://bucket/model/*') . This allows high-throughput, distributed in-warehouse inference on complex models without running external prediction microservices.

Foundation Models and Generative AI: BigQuery ML integrates with Google Cloud Vertex AI foundation models via Cloud Resource Connections ( REMOTE models). Using SQL functions like ML.GENERATE_TEXT and ML.GENERATE_EMBEDDING , engineers can generate embeddings for semantic vector search or summarize text columns at petabyte scale directly within BigQuery.

BigQuery ML SQL Syntax and Lifecycle Operations

The BigQuery ML lifecycle consists of four primary stages: training, evaluation, explainability inspection, and batch inference. -- Step 1: Train a Boosted Tree Classification Model

  • CREATE OR REPLACE MODEL `retail_analytics.customer_churn_xgb`
  • OPTIONS (
  • model_type = 'BOOSTED_TREE_CLASSIFIER',
  • input_label_cols = ['has_churned'],
  • max_iterations = 50,
  • learn_rate = 0.1,
  • subsample = 0.85,
  • auto_class_weights = TRUE,
  • data_split_method = 'AUTO_SPLIT'
  • ) AS
  • SELECT
  • tenure_months,
  • monthly_charges,
  • total_charges,
  • contract_type,
  • payment_method,
  • tech_support_calls,
  • has_churned
  • FROM `retail_analytics.customer_features`;
  • Automated Hyperparameter Tuning ( num_trials )

BigQuery ML supports automated hyperparameter tuning directly in SQL by configuring num_trials , max_parallel_trials , and hparam_tuning_algorithm = 'VIZIER' . Engineers specify search ranges (e.g., learn_rate = HPARAM_RANGE(0.01, 0.2) ), allowing BigQuery to optimize tree architectures autonomously without external tuning scripts.

Evaluating Model Quality ( ML.EVALUATE )

Once training completes, data engineers evaluate the model's predictive performance against unseen validation data using ML.EVALUATE : -- Step 2: Evaluate Model Metrics

  • SELECT *
  • FROM ML.EVALUATE(
  • MODEL `retail_analytics.customer_churn_xgb`,
  • (
  • SELECT * FROM `retail_analytics.customer_holdout_test`
  • )
  • );

ML.EVALUATE automatically returns domain-relevant metrics depending on the model type:

Classification: Area Under the ROC Curve (ROC AUC), Precision, Recall, F1-Score, Accuracy, and Log Loss.

Regression: Mean Absolute Error (MAE), Mean Squared Error (MSE), Root Mean Squared Error (RMSE), Mean Absolute Percentage Error (MAPE), and R-squared ( R 2 R^2 R 2 ).

K-Means: Davies-Bouldin Index (measuring cluster separation and compactness) and Mean Squared Distance.

Time-Series ( ML.ARIMA_EVALUATE ): AIC, variance, and seasonal components for fitted ARIMA models.

Model Explainability and Inspection ( ML.WEIGHTS and ML.GLOBAL_EXPLAIN )

Understanding why a model makes specific predictions is essential for regulatory compliance and debugging:

ML.FEATURE_INFO : Returns summary statistics (min, max, mean, stddev, null count) for every input feature seen during training.

ML.WEIGHTS : For linear and logistic regression models, outputs the numerical coefficient assigned to each feature, indicating the direction and magnitude of feature impact.

ML.GLOBAL_EXPLAIN & ML.EXPLAIN_PREDICT : Uses SHAP (SHapley Additive exPlanations) values to calculate the global feature importance rankings across boosted trees and deep neural networks, as well as per-row local feature attributions explaining individual prediction scores.

Batch Inference ( ML.PREDICT )

Generating predictions at scale is executed with ML.PREDICT , which joins the trained model with a target input table and executes parallelized vectorized inference across Borg slots: -- Step 3: High-throughput Distributed Batch Inference

  • SELECT
  • customer_id,
  • predicted_has_churned,
  • predicted_has_churned_probs[OFFSET(0)].prob AS churn_probability
  • FROM ML.PREDICT(
  • MODEL `retail_analytics.customer_churn_xgb`,
  • (
  • SELECT * FROM `retail_analytics.active_subscribers`
  • )
  • );
  • BigQuery ML Model Types and Selection Matrix

Model Family SQL Model Type ( model_type ) Common Enterprise Use Cases Key Hyperparameters & Options Evaluation & Inspection Functions

Linear Regression LINEAR_REG Continuous metric forecasting, sales volume, lifetime value estimation l1_reg , l2_reg , max_iterations , optimize_strategy ML.EVALUATE (RMSE, MAE, R²), ML.WEIGHTS , ML.FEATURE_INFO

Logistic Regression LOGISTIC_REG Binary/multiclass classification, customer churn, fraud detection auto_class_weights , l1_reg , l2_reg , class_weights ML.EVALUATE (ROC AUC, Log Loss, F1), ML.CONFUSION_MATRIX , ML.ROC_CURVE

Boosted Trees BOOSTED_TREE_CLASSIFIER , BOOSTED_TREE_REGRESSOR Complex tabular prediction, risk scoring, non-linear feature relationships max_tree_depth , learn_rate , subsample , num_parallel_tree ML.EVALUATE , ML.GLOBAL_EXPLAIN , ML.FEATURE_IMPORTANCE

Deep Neural Nets DNN_CLASSIFIER , DNN_REGRESSOR High-cardinality multi-layer representation learning hidden_units , dropout , learn_rate , optimizer ML.EVALUATE (Loss, Accuracy, RMSE), ML.PREDICT

AutoML Tables AUTOML_CLASSIFIER , AUTOML_REGRESSOR Automated state-of-the-art model search with zero tuning budget_hours , optimization_objective ML.EVALUATE , ML.GLOBAL_EXPLAIN , Vertex AI Model Registry

Time-Series ARIMA_PLUS Demand forecasting, server capacity planning, automated anomaly detection time_series_timestamp_col , time_series_data_col , holiday_region , time_series_id_col ML.ARIMA_EVALUATE , ML.ARIMA_COEFFICIENTS , ML.DETECT_ANOMALIES

Clustering KMEANS Customer segmentation, behavioral cohort grouping, geographic routing num_clusters , distance_type (EUCLIDEAN, COSINE), standardize_features ML.EVALUATE (Davies-Bouldin), ML.CENTROIDS , ML.PREDICT

Dimensionality PCA , AUTOENCODER Dimensionality reduction, noise filtering, unsupervised fraud anomaly detection num_principal_components (PCA), hidden_units (Autoencoder) ML.PRINCIPAL_COMPONENTS , ML.RECONSTRUCTION_LOSS

Recommendations MATRIX_FACTORIZATION Collaborative filtering, personalized product recommendations user_col , item_col , rating_col , num_factors , feedback_type ML.EVALUATE , ML.RECOMMEND

External Models TENSORFLOW , ONNX , TFLITE In-warehouse scoring of custom pre-trained neural networks model_path = 'gs://...' ML.PREDICT , ML.FEATURE_INFO

Vertex AI Integration and Serving Patterns

While BigQuery ML excels at massive, high-throughput distributed batch inference directly in the data warehouse, enterprise systems often require low-latency, real-time online predictions (sub-50 millisecond response times over REST or gRPC APIs) for web applications and transactional microservices.

BigQuery ML natively bridges this gap through two architectural integration patterns with Google Cloud Vertex AI :

1. Vertex AI Model Registry Integration

You can automatically register BigQuery ML models into the centralized Vertex AI Model Registry upon training completion by specifying the model_registry option: CREATE OR REPLACE MODEL `retail_analytics.churn_model`

  • OPTIONS (
  • model_type = 'BOOSTED_TREE_CLASSIFIER',
  • input_label_cols = ['churn'],
  • model_registry = 'VERTEX_AI',
  • vertex_ai_model_id = 'customer_churn_prod'
  • ) AS
  • SELECT * FROM `retail_analytics.training_data`;

Registering in Vertex AI Model Registry enables unified model governance, versioning, automated evaluation comparisons, model cards, and direct one-click deployment to managed Vertex AI online serving endpoints.

2. Exporting TensorFlow SavedModel to Cloud Storage

For high-concurrency microservices, BigQuery ML models (including linear models, boosted trees, deep neural networks, and matrix factorization) can be exported as a standard TensorFlow SavedModel artifact directly into a Cloud Storage bucket: EXPORT MODEL `retail_analytics.customer_churn_xgb`

TO 'gs://prod-ml-artifacts-bucket/models/churn_xgb_v1/';

Once exported to Cloud Storage, the SavedModel contains both the network weights and the embedded transformation graph. It can be deployed to a Vertex AI Endpoint , loaded into a custom Triton Inference Server, or containerized within Google Kubernetes Engine (GKE) to serve thousands of real-time transactional requests per second without incurring any BigQuery query slot overhead.

Loading diagram... End-to-End BigQuery ML Lifecycle: In-Warehouse Training, Evaluation, Batch Inference, and Vertex AI Serving