Study guide
1.1 BigQuery ML: Model Types, SQL Syntax and In-Database Evaluation
Google Cloud Professional Machine Learning Engineer Exam
Track your progress as you read
Finished sections get a check mark.
1 Introduction & Exam Overview
0.1 Exam Overview, Blueprint & Certification Logistics 0.2 Study Strategy, Scenario Mechanics & 8-Week Roadmap
2 Section 1: Architecting Low-Code AI Solutions (~13%)
1.1 BigQuery ML: Model Types, SQL Syntax and In-Database Evaluation 1.2 Feature Engineering and Feature Selection in BigQuery ML 1.3 Generating Predictions with BigQuery ML 1.4 AutoML on Agent Platform: Tabular, Image, Text and Video Training 1.5 Fine-Tuning Gemini Models from BigQuery 1.6 Model Garden: Evaluating and Selecting the Right Model 1.7 Industry-Specific AI APIs: Document AI, Vision, Translate and Speech 1.8 Generative Media and Models as a Service: Gemini, Imagen and Veo 1.9 Optimizing Gemini Applications for Cost, Latency and Availability
3 Section 2: Collaborating Within and Across Teams to Manage Data and Models (~16%)
2.1 Organizing and Exploring Tabular, Text, Image and Video Data 2.2 Choosing the Preprocessing Tool: BigQuery, Dataflow, Spark and In-Memory Python 2.3 Creating and Consolidating Features in Agent Platform Feature Store 2.4 Data Privacy, PII Handling and Sensitive Information 2.5 Notebook Environments: Collaboration and Security Best Practices 2.6 Prototyping in Workbench and Colab Enterprise with PyTorch, scikit-learn and JAX 2.7 Prototyping with Model Garden Foundational and Open Models in Notebooks 2.8 Choosing the Development and Experimentation Environment 2.9 Evaluating Generative Solutions: LLM-as-a-Judge and RAG Evaluation 2.10 Experiment Tracking, Artifact Lineage and Model Registry Governance
4 Section 3: Scaling Prototypes into ML Models (~21%)
3.1 Choosing the Model Type: ARIMA, DNN, LLM and Classical Baselines 3.2 Choosing the Product: AutoML, BigQuery ML, Custom Training and Pipelines 3.3 Choosing the Deployment Strategy at Design Time 3.4 Modeling Techniques for Interpretability Requirements 3.5 Organizing Training Data on Cloud Storage and BigQuery 3.6 Ingesting Structured and Unstructured Data into Training Pipelines 3.7 Training SDKs and Services: Custom Training, Kubeflow on GKE, AutoML and Tabular Workflows 3.8 Troubleshooting ML Model Training Failures 3.9 Hyperparameter Tuning, Bayesian Optimization and Vizier 3.10 Model Evaluation Metrics, Transfer Learning and Fine-Tuning 3.11 Evaluating Compute and Accelerator Options: CPU, GPU and TPU 3.12 Distributed Training Strategies, Hardware Acceleration and Performance Optimization
5 Section 4: Serving and Scaling Models (~20%)
4.1 Batch and Online Inference: Agent Platform, Model Garden, Cloud Run and GKE 4.2 Rollout Strategies: Canary, Traffic Splitting, Shadow and Blue-Green 4.3 Tuning Models for Production: Vector Search and Model Compression 4.4 Deploying Models to Public and Private Endpoints 4.5 Choosing Serving Hardware: CPU, GPU, TPU and Edge 4.6 Scaling the Serving Backend for Throughput
6 Section 5: Automating and Orchestrating ML Pipelines (~18%)
5.1 Validating Data and Models Inside Pipelines 5.2 Agent Platform Pipelines: Kubeflow Pipelines (KFP) and TFX Architecture 5.3 Reusable Pipeline Components, Artifacts and ML Metadata Lineage 5.4 Ray on Agent Platform for Distributed Python Workloads 5.5 MLOps CI/CD Automation, Retraining Triggers and Skew Prevention
7 Section 6: Monitoring AI Solutions (~13%)
6.1 Securing AI Systems: Exfiltration, Malicious Prompting and Model Armor 6.2 Responsible AI, Explainable AI, Fairness Metrics and Model Governance 6.3 Model Monitoring: Feature Skew and Drift Detection 6.4 Operational Troubleshooting, Performance Profiling and Cost Optimization 6.5 Monitoring, Testing and Evaluating Generative AI in Production
1.1 BigQuery ML: Model Types, SQL Syntax and In-Database Evaluation
Key Takeaways Explain This Topic AI
BigQuery ML (BQML) executes model training, evaluation, and batch inference directly inside Google Cloud's distributed SQL engine, eliminating the latency, infrastructure overhead, and security risks of data egress.
Supports a wide array of supervised architectures (LOGISTIC_REG, BOOSTED_TREE_CLASSIFIER, DNN_CLASSIFIER, RANDOM_FOREST), unsupervised models (KMEANS, PCA, AUTOENCODER), and collaborative filtering (MATRIX_FACTORIZATION).
ARIMA_PLUS automates enterprise time-series forecasting by performing automatic seasonality decomposition, holiday adjustments across regional calendars, trend change-point detection, and multi-series forecasting via time_series_id_col.
The TRANSFORM clause bundles feature preprocessing logic directly into the model graph, ensuring mathematical transformations are identically executed during ML.PREDICT to prevent train-serve skew.
Remote models enable SQL-driven invocation of external Vertex AI endpoints, Gemini foundation models (via ML.GENERATE_TEXT and ML.GENERATE_EMBEDDING), and Cloud Functions.
Last updated: September 2026
1.1 BigQuery ML: Model Types, SQL Syntax and In-Database Evaluation
For machine learning engineers operating on Google Cloud Platform, BigQuery ML (BQML) represents a transformative low-code paradigm. BQML allows data scientists and ML engineers to build, train, evaluate, and operationalize production-grade machine learning models directly within BigQuery using standard SQL dialects. By bringing machine learning compute to where petabyte-scale enterprise data already resides, BQML eliminates costly ETL pipelines, prevents data egress across network boundaries, simplifies access governance via Cloud IAM, and drastically accelerates time-to-market for production ML workflows.
1. Supported Model Architectures and Workloads
BigQuery ML supports a rich spectrum of supervised, unsupervised, time-series, and recommendation model architectures. Selecting the correct model_type option within the CREATE MODEL statement is a foundational skill tested heavily on the Professional Machine Learning Engineer exam. +---------------------------------------------------------------------------------------------------------+
- | BIGQUERY ML MODEL ECOSYSTEM |
- +------------------------------------+------------------------------------+-------------------------------+
- | SUPERVISED LEARNING | UNSUPERVISED LEARNING | TIME-SERIES & SPECIALIZED |
- +------------------------------------+------------------------------------+-------------------------------+
- | * LINEAR_REG / LOGISTIC_REG | * KMEANS | * ARIMA_PLUS |
- | * BOOSTED_TREE_CLASSIFIER/REG | * PCA (Dimensionality Reduction) | * MATRIX_FACTORIZATION |
- | * DNN_CLASSIFIER / DNN_REGRESSOR | * AUTOENCODER (Anomaly Detection) | * REMOTE (Vertex AI / Gemini) |
- | * RANDOM_FOREST_CLASSIFIER/REG | | * TENSORFLOW / ONNX (Import) |
- +------------------------------------+------------------------------------+-------------------------------+
- Supervised Models (Classification & Regression)
Linear & Logistic Regression ( LINEAR_REG , LOGISTIC_REG ) : Baseline models optimized for linear decision boundaries and fast, interpretable outputs. Supports L1 ( l1_reg ) and L2 ( l2_reg ) regularization, early stopping, and automatic class weighting ( auto_class_weights=TRUE ) for imbalanced datasets.
Boosted Trees ( BOOSTED_TREE_CLASSIFIER , BOOSTED_TREE_REGRESSOR ) : Leverages underlying XGBoost algorithms to construct ensembles of gradient-boosted decision trees. Excellent for non-linear tabular datasets, high-cardinality features, and competitive benchmark accuracy without requiring manual feature scaling.
Deep Neural Networks ( DNN_CLASSIFIER , DNN_REGRESSOR ) : Multi-layer perceptron (MLP) architectures with configurable hidden units (e.g., hidden_units=[128, 64, 32] ), dropout rates, and activation functions (ReLU, Sigmoid, Tanh). Useful for high-dimensional feature interactions.
Random Forests ( RANDOM_FOREST_CLASSIFIER , RANDOM_FOREST_REGRESSOR ) : Ensembles of bagging-based decision trees with subsampling and feature randomization, offering robust generalization and resistance to overfitting.
Unsupervised Models (Clustering & Dimensionality Reduction)
K-Means Clustering ( KMEANS ) : Partitions observations into k k k distinct clusters using spherical distance metrics. Supports automatic hyperparameter tuning for the optimal number of clusters ( num_clusters ) based on Davies-Bouldin index optimization.
Principal Component Analysis ( PCA ) : Performs linear dimensionality reduction while maximizing variance retention ( num_principal_components or pca_explained_variance_ratio ).
Autoencoders ( AUTOENCODER ) : Fully-connected bottleneck neural networks trained to reconstruct input vectors. Highly effective for unsupervised anomaly detection (e.g., high reconstruction error indicates outlier or fraudulent behavior).
Time-Series Forecasting: ARIMA_PLUS
ARIMA_PLUS is BigQuery ML's flagship time-series algorithm. Unlike standard statistical ARIMA, ARIMA_PLUS is an automated, production-grade forecasting pipeline that handles:
Decomposition : Automatic separation of trend, seasonal patterns (daily, weekly, yearly), and residual noise.
Holiday Effects : Incorporates national and regional holiday schedules via holiday_region (e.g., 'US' , 'GLOBAL' ).
Spike and Anomaly Cleansing : Automatically detects and imputes abrupt outliers and step changes in historical series.
Multi-Series Forecasting : By setting time_series_id_col , a single SQL query can train, evaluate, and forecast thousands of individual time-series (e.g., store-SKU combinations) simultaneously.
Recommendation Systems: Matrix Factorization
MATRIX_FACTORIZATION executes collaborative filtering for recommender systems. It factorizes a sparse user-item interaction matrix into low-rank dense user and item embedding factors ( num_factors=30 ). Supports both explicit ratings (1-5 star reviews) and implicit user actions (clicks, views, purchases) via feedback_type='implicit' .
2. BigQuery ML Model Families Comparison
Model Type ( model_type ) Learning Paradigm Primary Target Task Key Hyperparameters / Options Production Use Case
LOGISTIC_REG Supervised Binary / Multi-class Classification l1_reg , l2_reg , auto_class_weights Churn prediction, lead conversion
BOOSTED_TREE_CLASSIFIER Supervised Non-linear Classification max_tree_depth , subsample , num_parallel_tree Fraud detection, risk scoring
DNN_CLASSIFIER Supervised Complex Tabular Classification hidden_units , dropout , learn_rate Complex behavioral classification
ARIMA_PLUS Time-Series Uni/Multi-Variate Forecasting time_series_timestamp_col , time_series_id_col , holiday_region Retail demand, server telemetry forecasting
MATRIX_FACTORIZATION Supervised / Semi Collaborative Filtering user_col , item_col , feedback_type , num_factors Product/media recommendation engines
KMEANS Unsupervised Customer / Data Segmentation num_clusters , kmeans_init_method Market segmentation, cohort grouping
AUTOENCODER Unsupervised Anomaly & Outlier Detection hidden_units , activation_fn Industrial sensor failure, network intrusion
REMOTE Foundation / Custom LLM Prompting, External Inference remote_service_type , endpoint , connection Text summarization, sentiment, external models
- 3. SQL Syntax: Model Training, TRANSFORM & Feature Engineering
- The CREATE MODEL Syntax and Inline TRANSFORM Clause
A critical architectural capability of BigQuery ML is the TRANSFORM clause. When features require transformations (e.g., standard scaling, one-hot encoding, bucketization), implementing them in the SELECT query prior to model creation creates a severe risk of train-serve skew : downstream prediction pipelines must replicate exact statistical parameters (such as the training set mean and standard deviation).
By contrast, using the TRANSFORM clause encodes the mathematical transformations directly into the model object graph. During subsequent ML.PREDICT calls, raw features are automatically transformed using the statistical parameters computed during training. -- Production BQML Model Training with TRANSFORM clause
- CREATE OR REPLACE MODEL `prod_ml.customer_churn_xgboost`
- TRANSFORM(
- - Label must be passed through unchanged
- churn_label,
- - Numerical normalization with mean and stddev captured in model graph
- ML.STANDARD_SCALER(tenure_months) OVER() AS scaled_tenure,
- ML.STANDARD_SCALER(total_spend_usd) OVER() AS scaled_spend,
- - Categorical one-hot encoding with top-k category retention
- ML.ONE_HOT_ENCODER(contract_type, 'top_k', 5) OVER() AS encoded_contract,
- - Quantile-based bucketization for non-linear age distributions
- ML.QUANTILE_BUCKETIZER(customer_age, 5) OVER() AS age_bucket,
- - Text extraction for support tickets
- ML.NGRAMS(SPLIT(ticket_subject, ' '), [1, 2]) AS subject_ngrams
- )
- OPTIONS(
- model_type = 'BOOSTED_TREE_CLASSIFIER',
- input_label_cols = ['churn_label'],
- auto_class_weights = TRUE,
- max_tree_depth = 6,
- subsample = 0.85,
- early_stop = TRUE,
- min_rel_progress = 0.005,
- data_split_method = 'AUTO_SPLIT'
- ) AS
- SELECT
- churn_label,
- tenure_months,
- total_spend_usd,
- contract_type,
- customer_age,
- ticket_subject
- FROM `prod_data.customer_analytics_warehouse`;
- In-Database Evaluation and Diagnostic Functions
BigQuery ML provides a comprehensive suite of SQL evaluation functions that calculate industry-standard validation metrics without moving predictions out of BigQuery:
ML.EVALUATE : Computes overall performance metrics against a test dataset (e.g., Precision, Recall, Accuracy, F1-score, Log Loss, ROC-AUC for classification; RMSE, MAE, R-squared for regression). SELECT *
- FROM ML.EVALUATE(MODEL `prod_ml.customer_churn_xgboost`,
- (SELECT * FROM `prod_data.customer_holdout_test`));
ML.CONFUSION_MATRIX : Generates multi-class or binary confusion matrices across adjustable classification probability thresholds. SELECT *
- FROM ML.CONFUSION_MATRIX(MODEL `prod_ml.customer_churn_xgboost`,
- (SELECT * FROM `prod_data.customer_holdout_test`),
- STRUCT(0.65 AS threshold));
ML.ROC_CURVE : Evaluates true positive rates vs. false positive rates across continuous decision boundaries to determine optimal operational thresholds. SELECT *
- FROM ML.ROC_CURVE(MODEL `prod_ml.customer_churn_xgboost`,
- (SELECT * FROM `prod_data.customer_holdout_test`));
ML.FEATURE_INFO & ML.FEATURE_IMPORTANCE : Inspects min/max/mean/standard deviation of input features and outputs attribution scores (e.g., gain, cover, frequency for tree models) to verify feature relevance.
ML.GLOBAL_EXPLAIN : Computes model-wide feature attribution values based on Tree SHAP or Integrated Gradients to support explainability requirements.
4. Remote Models & Generative AI Integration
BigQuery ML extends beyond built-in algorithms by connecting to external services via Cloud Resource Connections: +-----------------------+ Cloud Resource Connection +----------------------------------+
- | BigQuery SQL | ================================> | Vertex AI Platform / API |
- | (ML.GENERATE_TEXT / | IAM Service Account Delegation | (Gemini 1.5 Pro, Text Embeddings |
- | ML.PREDICT Remote) | | or Custom Endpoint in Triton) |
- +-----------------------+ +----------------------------------+
- Defining Remote Models for Foundation Models
By establishing a BigQuery Cloud Resource Connection with delegated Vertex AI IAM permissions ( roles/aiplatform.user ), engineers can query foundation models (e.g., Gemini 1.5 Flash/Pro, Text Embedding Gecko) directly from SQL: -- Create a Remote Model referencing Vertex AI Gemini
- CREATE OR REPLACE MODEL `prod_ml.gemini_remote_model`
- REMOTE WITH CONNECTION `us.vertex_ai_connection`
- OPTIONS(ENDPOINT = 'gemini-1.5-pro');
- - Batch text generation and entity extraction in SQL
- SELECT
- customer_id,
- ml_generate_text_result['candidates'][0]['content']['parts'][0]['text'] AS parsed_sentiment
- FROM ML.GENERATE_TEXT(
- MODEL `prod_ml.gemini_remote_model`,
(SELECT customer_id, CONCAT('Classify the customer sentiment as POSITIVE, NEUTRAL, or NEGATIVE: ', feedback_text) AS prompt
- FROM `prod_data.user_feedback`),
- STRUCT(0.2 AS temperature, 256 AS max_output_tokens)
- );
- 5. Architectural Decision Matrix: BigQuery ML vs. Vertex AI Custom Training
A central theme in the GCP ML Engineer certification is discerning when to keep workloads inside BigQuery ML versus architecting custom training pipelines in Vertex AI:
Evaluation Criterion BigQuery ML (BQML) Vertex AI Custom Training
Data Location Data resides natively in BigQuery / BigLake Data scattered across GCS, databases, streaming sources
Required Model Architectures Standard tabular (Linear, Trees, DNN), ARIMA+, Matrix Factorization, KMeans Highly custom architectures (Transformers, PyTorch geometric, custom loss functions, multi-task heads)
Serving Latency & Protocol Batch SQL scoring ( ML.PREDICT ) or exported SavedModel Real-time low-latency online RPC/REST endpoints (<20ms SLA) via Vertex Prediction
Infrastructure Management Zero infrastructure management; auto-scaled by BigQuery slots Full control over GPU/TPU accelerators, CUDA libraries, custom Docker containers
Development Velocity Extremely high for SQL practitioners; rapid baseline creation Moderate to high; requires Python/C++ pipeline orchestration
Feature Store Integration Direct SQL tables, Views, and inline TRANSFORM Vertex AI Feature Store with online low-latency key-value lookups
Tip
Exam Strategy Rule of Thumb : If the business requirement involves tabular or time-series data already stored in BigQuery, requires batch predictions on millions of records, and can be solved using standard linear, tree-based, or ARIMA architectures, BigQuery ML is always the architecturally preferred, most cost-effective solution .