Building Models with BigQuery ML and AutoML

Evaluating problem requirements for low-code machine learning means matching business objectives, data modality, latency needs, and dataset size to the right training environment. BigQuery ML (opens in a new tab) trains natively in SQL where warehouse data already lives, Vertex AI AutoML (opens in a new tab) automates architecture search and tuning for supported data types, and custom training on Gemini Enterprise Agent Platform (Agent Platform) handles everything else. The correct choice preserves data locality, meets serving latency, and avoids custom code when a predefined routine is sufficient.

1.1Analyze Business Objectives, Data Modality, and Latency Requirements

Requirement analysis for low-code model selection evaluates four dimensions together before choosing a training environment. The business objective determines the model type, data modality and location determine feasibility, latency determines serving viability, and dataset size determines tooling. Data flows from operational sources into storage optimized for training, then flows from that storage to the selected training environment through the BigQuery Storage API, BigQuery I/O connector, or Cloud Storage sharded reads.

• Business objective defines the prediction target: category label for classification, numeric value for regression, future value for forecasting, or group assignment for clustering.
• Data modality and location define the source system: structured and semi-structured data materialized in BigQuery tables versus unstructured objects grouped in Cloud Storage buckets and directories.
• Latency requirement defines serving viability: AutoML for text, video, or tabular models requires tolerance for inference latencies greater than 100ms, while low-latency online serving depends on Vertex AI Feature Store (Feature Store) backed by BigQuery tables or views.
• Dataset size and processing pattern define tooling: small datasets or subsets can be explored in a Vertex AI Workbench (Workbench) instance, while large or distributed training and scheduled retraining depend on the managed training service.

1.2Map Business Problems to Low-Code Model Types

Mapping business problems to low-code model types means selecting the modeling objective first and then supplying the columns and options that objective requires. Model creation (opens in a new tab) in BigQuery ML exposes classification, regression, forecasting, clustering, recommendation, and dimensionality reduction, each with its own set of required columns. Automatic tuning works well unless manual tuning is explicitly needed.

• Classification and regression models require INPUT_LABEL_COLS identifying the column that contains the label data.
• Time-series forecasting models require TIME_SERIES_TIMESTAMP_COL for time points used in training and TIME_SERIES_DATA_COL for the data to forecast.
• K-means clustering assigns rows to clusters without a label column and is used for segmentation and grouping.
• Matrix factorization for recommendation requires RATING_COL for rating data, USER_COL for user data, and ITEM_COL for item data.
• Dimensionality reduction with principal component analysis (PCA) and autoencoder compresses high-dimensional inputs for visualization, denoising, or downstream modeling.

Hyperparameter tuning for BigQuery ML is enabled with the NUM_TRIALS option, where the available tuning arguments vary by model type.

1.3Compare BigQuery ML, AutoML, and Custom Training Environments

The three training environments differ by where data lives, how much automation they provide, and where models can serve. BigQuery ML keeps tabular workflows in the warehouse, AutoML automates model search for supported modalities, and custom training removes constraints on model type and platform consistency. ML pipelines (opens in a new tab) and managed Vertex AI pipelines carry data from storage to training to a versioned artifact without export when locality is preserved.

Selection therefore follows order: first confirm BigQuery locality and SQL fit for BigQuery ML, then confirm AutoML format and latency fit for AutoML, and only after both fail select custom training. BigQuery ML also supports training AutoML tabular models from the BigQuery ML environment, so a team can start with SQL-defined models and then invoke automated tabular search without moving data. For large tables, partitioning improves query performance and cost efficiency, and materialized tables are preferred over views or subqueries for training speed.

BigQuery ML (opens in a new tab) is a low-code environment that brings together data, infrastructure, and predefined model types into a single system. Training data stays in BigQuery and models are created with standard SQL, so no separate training cluster or export pipeline is required. Choose BigQuery ML when all of the training data is contained in BigQuery, the team is comfortable with SQL, and the set of models available in BigQuery ML matches the business problem.

2.1Identify BigQuery ML Model Types for Business Problems

BigQuery ML model types map directly to enterprise problem patterns for classification, regression, forecasting, and clustering. Each model type has a different set of options, and automatic tuning works well in most cases before considering manual tuning. Choosing a training method (opens in a new tab) depends on where data lives, what problem is solved, and how much control is needed.

• Classification and regression use boosted-tree models and AutoML regression for predicting labels or numeric values from tabular inputs.
• K-means clustering groups rows without a label, such as customer or behavior segmentation.
• Matrix factorization powers user-item recommendation from rating interactions.
• Principal component analysis (PCA) and autoencoder compress features while preserving structure for dimensionality reduction.
• Time-series forecasting with ARIMA_PLUS and related options predicts future values from timestamped history.

2.2Create Models with SQL Statements and Console Workflow

Creating a model in the console (opens in a new tab) produces a CREATE MODEL statement and a stored model object in a BigQuery dataset. The workflow moves training data from a BigQuery table, view, or saved query into the new model, with the modeling objective controlling which model types can be selected.

1. Open BigQuery and select dataset: Go to the BigQuery page, open Explorer, click Datasets, and then click the target dataset.
2. Start creation: Click View actions next to the dataset and click Create BQML Model, then enter a Model name and optionally save the CREATE MODEL statement as a saved query with a query name and region.
3. Select method and objective: Select Train a Model in BigQuery as the Creation method, then select a modeling objective and continue to Model options to select a model type.
4. Select training data: Choose Table/View and select project, dataset, and table or view, or choose Query and select a saved query as the source of training rows.
5. Select inputs and required columns: Choose Selected input label columns, then supply required columns for the model family.
6. Set optional tuning and create: Specify optional model tuning arguments and hyperparameter tuning if shown, click Create model, and when creation completes click Go to model to view model details.

AutoML tabular models can also be trained from the BigQuery ML environment, which lets SQL-based workflows use automated tabular modeling without moving data.

2.3Define Required Columns, TRANSFORM Preprocessing, and Tuning Options

Required columns in BigQuery ML identify which input fields play the label, user, item, rating, timestamp, and data roles during training. The required roles change by model family, while additional tuning arguments vary by the specific model type being created. Model options (opens in a new tab) control the algorithm behavior, and the TRANSFORM clause applies built-in BigQuery preprocessing where the data already resides.

• Classification and regression models require INPUT_LABEL_COLS, the column that contains the label data.
• Matrix factorization models require RATING_COL for rating data, USER_COL for user data, and ITEM_COL for item data, so user-to-item interactions flow from the training table into the recommendation model.
• Time-series forecasting models require TIME_SERIES_TIMESTAMP_COL for the time points used in training and TIME_SERIES_DATA_COL for the data to forecast, so ordered timestamps drive the forecast horizon.

Optional tuning arguments adjust the selected model type, and the NUM_TRIALS option enables hyperparameter tuning for model types that expose it. For large tables, partitioning improves performance and cost, and materialized tables are preferred over views or subqueries (opens in a new tab) for training speed.

2.4Evaluate Models with ML Functions and Production Thresholds

Model evaluation in BigQuery ML uses SQL evaluation functions to check aggregate metrics and diagnostic curves before promotion to production. ML.EVALUATE returns aggregate performance metrics such as accuracy, precision, and recall on held-out test data, ML.CONFUSION_MATRIX breaks classification predictions into true and false positives and negatives, and ML.ROC_CURVE traces tradeoff between true-positive and false-positive rates. Models are evaluated by comparing these metrics on test datasets, and by comparing performance across data subsets, model versions, and model architectures to confirm alignment with production thresholds.

AutoML on Vertex AI (opens in a new tab) is a managed training system that automates architecture selection, hyperparameter tuning, and machine provisioning for common tabular, forecasting, image, text, and video problems. A training job defined with the Vertex AI SDK for Python reads from a managed dataset, searches and ensembles candidate models, and returns a trained model artifact that flows to the Model Registry for serving from Google Cloud or deployment to an external device. Use AutoML when data and objectives match a supported format and, for text, video, or tabular models, when inference latency greater than 100ms is tolerable.

3.1Configure Tabular Training Jobs for Classification and Regression

AutoMLTabularTrainingJob is the SDK class that creates, trains, and returns an AutoML tabular model for binary classification, multi-class classification, and regression. The job configuration binds the input TabularDataset to a business objective through target_column, optimization_prediction_type such as regression, and optimization_objective such as minimize-rmse. The run() call then executes the full lifecycle where data flows from the dataset to distributed training to the returned model object, with built-in preprocessing handling feature transformations automatically.

Binary classification predicts one of two classes for yes-or-no questions, multi-class classification predicts one of three or more discrete classes for categorization, and regression predicts a continuous value such as next-month spend. Binary classification generally requires less data than the other types, while all three share the same managed pattern where Vertex AI chooses model type, parameters, and hardware. The search considers neural networks and boosted trees, trains multiple models with multiple hyperparameter sets, and creates one final model from an ensemble of the top performers. Tabular data (opens in a new tab) for these jobs should use materialized BigQuery tables for fastest input, while unstructured inputs should use large sharded Cloud Storage containers.

3.2Choose Forecasting Methods for Time-Series Workloads

Forecasting jobs predict a sequence of future values, such as daily product demand for the next three months, and Vertex AI provides four distinct training methods for this task. AutoMLForecastingTrainingJob uses the AutoML method and is the best choice for most forecasting use cases because it explores the largest search space. The specialized alternatives trade search breadth for speed, interpretability, or long-horizon efficiency when the use case benefits from a specific architecture.

The forecasting pipeline follows the same order as tabular classification and regression: first the time-series dataset is split and featurized, then multiple models are trained with different hyperparameters, then the top models are ensembled into one artifact. AutoMLImageTrainingJob follows the same create-train-return pattern for unstructured image data, and AutoML tabular models can also be launched from the BigQuery ML (opens in a new tab) environment when source data already lives in BigQuery.

3.3Control End-to-End Workflows with Tabular Workflows

Tabular Workflow for End-to-End AutoML is a complete AutoML pipeline that exposes control at every step instead of only at the whole-pipeline level. The workflow decomposes training into an ordered sequence where data flows from one managed component to the next and each component writes metadata that the next component depends on. The pipeline depends on its managed dataset, and the final ensemble depends on the outputs of architecture search and training.

1. Data splitting: Divide rows into train, validation, and test sets for unbiased evaluation.
2. Feature engineering: Apply built-in transformations to tabular columns.
3. Architecture search: Evaluate neural network and boosted-tree candidates.
4. Model training: Train candidates with different hyperparameters and hardware.
5. Model ensembling: Combine top models into a single final model.
6. Model distillation: Compress the ensemble to reduce size and latency.

Tabular Workflow for End-to-End AutoML supports large datasets that are multiple terabytes in size with up to 1000 columns, while Tabular Workflow for Forecasting supports datasets up to 1 TB with up to 200 columns. Each component can be inspected in the pipelines graph interface to see transformed tables, evaluated architectures, logs, and status, which gives transparency that the fully managed AutoML API does not expose. Choose the fully managed API when Vertex AI should choose hardware and parameters automatically, and choose Tabular Workflows when stability, speed, or serving cost must be tuned step by step.

3.4Manage Data Splits, Lineage, Budgets, and Search Controls

Managed datasets on Vertex AI create a tracked link between data and the custom-trained or AutoML model through Vertex ML Metadata. A managed dataset provides descriptive statistics and supports automatic or manual splitting into train, validation, and test sets, so parameters, metrics, and dataset and model artifacts stay linked for audit and comparison. Use managed datasets when lineage between data and model is critical, and manage splits in training code only when finer control is required.

Custom split strategies are configured directly on the training job with training_fraction_split, validation_fraction_split, and test_fraction_split, for example 0.6, 0.2, and 0.2 for a regression job. The training fraction feeds architecture search and parameter fitting, the validation fraction guides hyperparameter selection and early stopping, and the test fraction is held out for final aggregate metrics such as accuracy, precision, and recall. Training compute budget is controlled with budget_milli_node_hours, such as 1000 milli-node-hours, which caps the total distributed compute that architecture search and tuning can consume. The disable_early_stopping flag controls whether unpromising trials stop early, so leaving it as False preserves the default tradeoff of saving compute without sacrificing the best candidates. Additional controls include limiting architecture search space, manually selecting hardware, and reducing ensemble size or applying distillation to lower latency. For unstructured data, store inputs in large sharded containers (opens in a new tab) such as sharded TFRecord files for TensorFlow or Avro files for other frameworks, with files of at least 100 MB grouped in buckets and directories and sharded between 100 and 10,000 shards.

3.5Validate with Explainability and Deploy Artifacts for Prediction

Validation on Vertex AI combines aggregate metrics, experiment comparison, and feature-level explanations before any artifact is promoted. Vertex AI TensorBoard tracks loss and accuracy over time, visualizes the model graph, and projects embeddings to compare runs by hyperparameters, while Experiments integrates with Vertex ML Metadata to log linkage across parameters, metrics, and dataset and model artifacts. Vertex AI Workbench instances provide the interactive environment for this analysis, including scikit-learn libraries for slice-based checks of accuracy, precision, and recall across data subsets, model versions, and architectures.

Vertex Explainable AI adds feature attributions that quantify the importance of each input feature to a specific prediction, which helps confirm model behavior and build trust. The service details why a tabular or image model generated a prediction by ranking contributing features for each request. Workbench extends this with the What-If Tool (WIT) for interactive bias analysis and the Language Interpretability Tool (LIT) for visual exploration of natural language model behavior.

A deployed AutoML artifact is the versioned model stored in the Model Registry (opens in a new tab) that serves prediction traffic from Google Cloud or from an external edge device. The deployment flow moves in order from the training job to the registry to serving, where the registry owns versioning and the serving infrastructure owns autoscaling and monitoring. The same artifact can support batch prediction for large offline scoring and real-time prediction for latency-sensitive requests, with text, video, and tabular AutoML models requiring tolerance for latency greater than 100ms. After deployment, Model Monitoring tracks prediction drift and performance, and retraining is productionized through the managed training service or PipelineJob so new data flows from BigQuery, Cloud Storage, or Vertex AI Feature Store into a new training run and then to an updated registry version.

Key Facts

• BigQuery ML trains natively in SQL where tabular data already lives and requires that all data is in BigQuery, builders use SQL, and an available model type matches the problem.
• AutoML for text, video, or tabular models requires tolerance for inference latency greater than 100ms and automates architecture search, hyperparameter tuning, and machine provisioning.
• BigQuery ML uses INPUT_LABEL_COLS for classification and regression labels, TIME_SERIES_TIMESTAMP_COL and TIME_SERIES_DATA_COL for forecasting, RATING_COL, USER_COL, and ITEM_COL for matrix factorization, and NUM_TRIALS to enable hyperparameter tuning.
• Tabular Workflow for End-to-End AutoML supports multiple terabytes and up to 1000 columns and runs in order through splitting, feature engineering, architecture search, training, ensembling, and distillation.
• Training compute is capped by budget_milli_node_hours, custom splits use training_fraction_split, validation_fraction_split, and test_fraction_split, and unstructured training data should use files of at least 100 MB sharded between 100 and 10,000 shards.