Developing Models with BigQuery ML and AutoML

Building models with low-code tools means matching the business goal, data location, and latency need to the right training environment. BigQuery ML (opens in a new tab) trains with SQL where warehouse data already lives, Vertex AI AutoML automates architecture search and tuning, and custom training on Gemini Enterprise Agent Platform (opens in a new tab) handles everything else. Data flows from storage to training through the BigQuery Storage API, BigQuery I/O connector, or Cloud Storage sharded reads, and the correct choice preserves data locality while avoiding custom code when a predefined routine is enough.

1.1Choosing Between BigQuery ML, AutoML, and Custom Training

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. Selection follows a fixed 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 can also train AutoML tabular models from the BigQuery ML environment, so SQL workflows can invoke automated tabular search without moving data. For large tables, partitioning improves performance and cost, and materialized tables are preferred over views or subqueries for training speed.

1.2Mapping Business Problems to Model Types and Required Columns

Mapping business problems to low-code model types means selecting the modeling objective first and then supplying the columns that objective requires. Classification predicts a category label, regression predicts a numeric value, forecasting predicts a future value, and clustering assigns group membership without a label. Hyperparameter tuning is enabled with the NUM_TRIALS option, where available tuning arguments vary by model type.

• Classification and regression require INPUT_LABEL_COLS for the label column, using boosted trees or AutoML regression.
• Time-series forecasting requires TIME_SERIES_TIMESTAMP_COL for time points and TIME_SERIES_DATA_COL for values to forecast, using ARIMA_PLUS and related options.
• K-means clustering needs no label column and is used for segmentation.
• Matrix factorization for recommendation requires RATING_COL, USER_COL, and ITEM_COL.
• Dimensionality reduction with principal component analysis (PCA) and autoencoder compresses high-dimensional inputs for visualization or downstream modeling.

1.3Configuring AutoML Tabular and Forecasting Jobs

AutoML tabular and forecasting jobs bind a managed dataset to a business objective and then search, train, and ensemble candidates into one artifact. An AutoMLTabularTrainingJob uses target_column, optimization_prediction_type such as regression, and optimization_objective such as minimize-rmse, with built-in preprocessing handling transformations automatically. Binary classification needs less data than multi-class classification or regression, and the search considers neural networks and boosted trees before ensembling top performers.

Tabular Workflow for End-to-End AutoML exposes control at every step in order: data splitting, feature engineering, architecture search, model training, model ensembling, and model distillation. It supports multiple terabytes and up to 1000 columns, while Tabular Workflow for Forecasting supports up to 1 TB and 200 columns. Compute is capped by budget_milli_node_hours, splits use training_fraction_split, validation_fraction_split, and test_fraction_split such as 0.6, 0.2, and 0.2, and unstructured inputs should use files of at least 100 MB sharded between 100 and 10,000 shards.

Feature engineering in BigQuery DataFrames (opens in a new tab) and BigQuery ML keeps transformation logic inside the warehouse so raw tables flow directly to training without an external cluster. The TRANSFORM clause in CREATE MODEL defines custom preprocessing during training, and learned statistics are automatically reused during prediction with ML.PREDICT. Performance comes from minimizing repeated scans and changing defaults only where accuracy gains justify compute cost.

2.1Keeping Preprocessing Inside the Warehouse

Automatic preprocessing in BigQuery ML is the imputation and transformation that runs inside CREATE MODEL and is reapplied at prediction time. Because the logic lives with the model, training and serving stay consistent without a separate preprocessing service. Missing-data handling is deterministic by type: numeric NULL values are replaced with the column mean, one-hot and multi-hot NULL values map to an additional category while unseen prediction categories get weight 0, TIMESTAMP features mix mean Unix time with NULL categories, and each field of a STRUCT follows its type rule. Reusable supply through Knowledge Catalog discovery, BigQuery sharing, Agent Platform Feature Store, object tables, and Vector Search indexes avoids copying data for every model.

2.2Creating SQL Features with TRANSFORM and Manual Functions

The TRANSFORM execution model defines how custom SQL moves from training to prediction in BigQuery ML. Manual functions can be used inside TRANSFORM and outside it for ad-hoc preparation, and analytic functions save training-time statistics for prediction. Every ML analytic function requires an empty OVER() clause to signal collection across all rows, and ML.TRANSFORM returns the actual preprocessed rows that enter the model.

Temporal decomposition extracts hour, day-of-week, or Unix time for seasonality, polynomial expansion with ML.POLYNOMIAL_EXPAND lets linear models learn curves, and bucketization handles non-linearity. ML.BUCKETIZE uses caller-supplied boundaries while ML.QUANTILE_BUCKETIZE learns balanced-count buckets from training data. Categorical work uses ML.ONE_HOT_ENCODER, ML.MULTI_HOT_ENCODER, ML.LABEL_ENCODER, ML.HASH_BUCKETIZE for high-cardinality columns, and ML.FEATURE_CROSS to combine columns so the model learns joint effects such as device and hour-bucket together.

2.3Selecting Features from Weights and Exported Artifacts

Automated feature selection in BigQuery ML uses saved model weights to rank which inputs most influence predictions. Linear and logistic regression expose coefficients through ML.WEIGHTS (opens in a new tab), k-means exposes centroids through ML.CENTROIDS, matrix factorization exposes user and item latent factors through ML.WEIGHTS, PCA exposes eigenvectors and variance through ML.PRINCIPAL_COMPONENTS and ML.PRINCIPAL_COMPONENT_INFO, and ARIMA_PLUS exposes trend coefficients through ML.ARIMA_COEFFICIENTS. High-magnitude coefficients and frequent split features are retained, while near-zero or unused features are candidates for removal.

Boosted tree, random forest, deep neural network (DNN), wide-and-deep, and AutoML Tables models have no in-warehouse weight function and require export for inspection:

1. Export: Run EXPORT MODEL from BigQuery ML to a Cloud Storage bucket.
2. Download: Download the model.bst file from the bucket locally.
3. Upload: Upload model.bst to the Files area of a Colab notebook.
4. Plot: Load with xgb.Booster and call xgb.plot_tree per iteration to render trees.

Exported trees use encoded identifiers such as f0, which map to real columns through assets/model_metadata.json, while categorical split values map by position to vocabulary files in assets/. There is no weight retrieval method for AutoML Tables models.

Prediction in BigQuery ML runs large language model inference, vector creation, and tabular scoring directly inside SQL workflows. BigQuery ML sends rows from tables or object tables to models hosted on Vertex AI, and predictions flow back as query result rows. This design unifies batch scoring, text generation, and vector creation without moving data into separate Python serving systems.

3.1Invoking Remote Models Through Secure Connections

A remote model in BigQuery ML is a model object that points to a Vertex AI foundation model or deployed endpoint. The remote model depends on a Cloud Resource connection, which provides a dedicated service account as the identity for calls from BigQuery to Vertex AI and Cloud Storage. The trust boundary sits between BigQuery on one side and Vertex AI APIs and Cloud Storage on the other, and the connection account must hold the Vertex AI User role plus read access to buckets used by object tables.

1. Create connection: Create a Cloud Resource connection in the same project and region as the dataset.
2. Grant access: Grant the connection service account the Vertex AI User role and bucket read access.
3. Create remote model: Run CREATE MODEL with REMOTE WITH CONNECTION naming the connection and Vertex AI ENDPOINT.
4. Query for inference: Call ML.PREDICT, ML.GENERATE_TEXT, or ML.GENERATE_EMBEDDING where BigQuery authenticates as the connection identity.

3.2Generating Text and Embeddings with Foundation Models

ML.GENERATE_TEXT (opens in a new tab) sends prompts to a large language model and returns generated text as structured rows. Input rows flow from a query with prompt columns into the remote text model, and output rows flow back with text, safety attributes, and metadata. Common practices include instructions plus few-shot examples, constraining output to JSON for parsing into STRUCT fields, and batching prompts for summarization, classification, extraction, and question answering. Choose this function when the output is human-readable language rather than a score or vector.

ML.GENERATE_EMBEDDING (opens in a new tab) converts text or multimodal content into dense floating-point vectors that preserve semantic distance. Source text flows to a text-embedding remote model and one vector flows back per row for storage, vector-search indexing, and joins to business data. Production pipelines normalize and chunk long documents, use the same embedding version for stored and query vectors, and save outputs with source keys. An object table maps Cloud Storage files to queryable rows without copying bytes, so URIs plus prompts feed directly into generative functions for classification, captioning, and metadata enrichment at scale.

3.3Running Batch Scoring and Handling Failures

Batch inference with ML.PREDICT, ML.FORECAST, ML.RECOMMEND, and ML.DETECT_ANOMALIES scores large stored tables in bulk. Inputs must provide the series and target structure the model expects, or validation fails for the missing series identifier. A quantile-loss forecast returns parallel arrays where predicted_Sales.quantile_values holds probability levels and predicted_Sales.quantile_predictions holds values, for example 0.1 mapping to 4484.04, 0.5 mapping to 5615.64 for point planning, and 0.9 mapping to 6853.29. Normalized global feature importance divides each aggregated attribution by total attribution to compare time, marketing, calendar, history, location, and item influence on one scale.

Row-level failures return NULL for that row rather than failing the whole query when a row exceeds quota such as RESOURCE_EXHAUSTED. The error ratio equals failed rows divided by total rows processed, the default failure threshold is 1.0, and lowering it such as to 0.2 makes the query fail with partial results. LIMIT is applied after the model processes a batch, so a LIMIT 10 query can show a higher NULL rate in the final rows than the overall error ratio.

3.4Choosing Serving Architectures and Moving Artifacts

Serving architecture is the choice between scoring where data lives and exporting for low-latency serving elsewhere. In-database scoring applies the model to BigQuery tables with ML.PREDICT and writes results back to BigQuery, which fits large-scale batch scoring when data gravity favors BigQuery. Real-time inference on Vertex AI Endpoints receives one request at a time from the client, loads the artifact from Cloud Storage, and returns without a BigQuery query, often using Vertex AI Feature Store with BigQuery tables or views as the offline store. A TensorFlow export consists of saved_model.pb plus a variables folder stored as gs://bucket_name/[folder_name/]*, while PyTorch uses ONNX format; loading into BigQuery ML requires model_type = 'TENSORFLOW' or 'ONNX' with model_path ending in /*. Image inputs add a dependency where object-table bytes flow through ML.DECODE_IMAGE, then ML.CONVERT_COLOR_SPACE and ML.CONVERT_IMAGE_TYPE, and only then into ML.PREDICT.

Training with AutoML on Gemini Enterprise Agent Platform (opens in a new tab) automates architecture search, tuning, and provisioning for tabular, forecasting, image, text, and video problems. The AutoML TrainingPipeline resource always executes training and can optionally export data from a Dataset, upload the resulting Model, and evaluate the Model. Data flows from the Dataset into training, from training to Model Registry for versioning, and from the registry to an endpoint or batch service for inference.

4.1Configuring Data Sources and Pipeline Stages

Data source configuration defines which managed data becomes training input and how it is reused. The same managed dataset can train both custom and AutoML models to compare performance under identical inputs, with Cloud Storage for objects, BigQuery for tabular analysis, and high-performance file systems such as NetApp Volumes or Filestore for shared files. Agent Platform Pipelines orchestrates stages where each container component passes artifacts to the next, with metadata linking every step.

1. StatsAndExampleGenOp: Profile schemas and detect anomalies from the Dataset export.
2. TransformOp: Materialize transformed features for model consumption.
3. SplitMaterializedDataOp: Create training, validation, and test splits.
4. TrainingConfiguratorAndValidatorOp: Validate configuration before expensive tuning.
5. Stage1TunerOp and CvTrainerOp: Search architectures and train with cross-validation.
6. EnsembleOp and FinalizerOp: Combine candidates and validate serving infrastructure.

For forecasting, ProphetTrainerOp and the AutoML forecasting, image, and tabular job operators create the corresponding model resources. Preprocessing can use Dataflow for Beam, Managed Service for Apache Spark, BigQuery for SQL, and TensorFlow Data Validation (TFDV) for schema checks.

4.2Selecting Objectives and Tuning Strategies

Model objective selection matches task complexity to training mode. AutoML handles simpler tabular, image, and forecasting tasks without custom code, custom training handles complex or generative architectures, and Model Garden provides foundation models for fast starts or fine-tuning.

Hyperparameter tuning tests multiple trials across a user-defined search space, with Stage1TunerOp searching and CvTrainerOp cross-validating. Training code must load from Google Cloud services, use platform environment variables, export to the designated location, and resume after VM restarts. A custom job first provisions one VM or many VMs for distributed training, then runs the container, and only after completion deletes the VMs.

4.3Versioning, Deploying, and Monitoring Models

Version control keeps code, data, features, and binaries reproducible and rollback-ready. Model Registry versions model binaries and metadata, Artifact Registry versions containers and pipeline components, and Feature Store versions feature data with BigQuery as the offline store. Because BigQuery retains historical features, a deployed version can be joined back to point-in-time training features, with catalog pointers tying dataset, container, and model versions together.

Automated deployment uses Agent Platform Pipelines with scheduled runs, execution metrics, reusable templates, and Cloud Scheduler, plus Cloud Build validation including stress tests with Locust, k6, or JMeter, bulk evaluation in BigQuery, and Dataflow feature validation. After deployment, Model Monitoring detects drift, performance loss, and anomalies, including quality and safety shifts for generative systems, and versioned lineage enables rollback or retraining.

Fine-tuning with Gemini in BigQuery (opens in a new tab) adapts a Gemini foundation model to an enterprise task with supervised tuning. The job reads a labeled JSONL dataset from Cloud Storage, learns adapter parameters on the frozen base model, and outputs a tuned model with its own resource, endpoint, and checkpoints. In-database ML eliminates movement for SQL teams, while the managed pipeline supports versioning, evaluation, and deployment through Model Registry.

5.1Preparing Training Data Inside BigQuery

Preparing training data for fine-tuning means building validated prompt-response pairs inside BigQuery where the job will read them. Data flows from operational sources into BigQuery materialized tables, where built-in transformations preprocess it before CREATE MODEL runs. For maximum speed, store training data as materialized partitioned tables rather than views or subqueries, and read through the BigQuery Storage API with a managed tabular dataset. Feature history can sit in BigQuery tables or views behind Feature Store for offline reuse.

Data quality for generative tuning covers accuracy, relevance, diversity of phrasing, and alignment of labels with quality, relevance, and safety, because the tuned model reproduces judgments in those labels. Validate schemas with TensorFlow Data Validation (TFDV) and BigQuery quality scans, create text data through the Agent Platform console for classification, extraction, and sentiment tasks, and use synthetic data from Agent Platform or BigQuery DataFrames when real data is insufficient. Managed datasets link a snapshot to the tuned model with train, test, and validation splits and descriptive statistics.

5.2Configuring and Running Supervised Tuning Jobs

Supported base models for supervised tuning are Gemini 3.5 Flash, Gemini 3.1 Flash-Lite, Gemini 2.5 Pro, Gemini 2.5 Flash, and Gemini 2.5 Flash-Lite. Each job selects one base model, a tuned display name up to 128 characters, a tuning region, and training plus optional validation datasets. User data stays in the tuning region while compute can offload to other US or EU regions, and thinking-capable models should use thinking budget 0 for Gemini 2.5 or MINIMAL for Gemini 3 and higher because tuning omits thinking.

Execution follows the same order in every interface: configure model details, configure tuning settings, attach datasets, and start the pipeline. Console creation uses Agent Platform Studio with Create tuned model, code uses client.tunings.tune, REST uses tuningJobs.create, and Colab Enterprise uses Tuning cells. Jobs expose JOB_STATE_PENDING and JOB_STATE_RUNNING until completion, then tuned_model.model, tuned_model.endpoint, checkpoints with ID, epoch, and step, and experiment records. Limits include 131,072 input and output tokens per example, 1 GB JSONL size, 10M text-only or 300K multimodal examples, validation capped at 5000 examples, tuning in us-central1 and europe-west4, and no customer-managed encryption keys (CMEK).

5.3Registering, Evaluating, and Deploying Tuned Models

Vertex AI Model Registry manages BigQuery ML models alongside Agent Platform models for versioning, evaluation, and online prediction without a serving container. Registration needs aiplatform.googleapis.com enabled via Owner or Service Usage Admin plus the Agent Platform Administrator role, and appears with Source showing BigQuery ML. CREATE MODEL supports MODEL_REGISTRY to auto-register, VERTEX_AI_MODEL_ID to set the registry ID, and VERTEX_AI_MODEL_VERSION_ALIASES to manage versions. Each BigQuery ML model can register to only one model ID, so iterations use aliases under that ID.

Comparison starts from business KPIs and tests tuning from three angles: baseline aggregates such as accuracy, precision, recall, loss, perplexity, and generative quality versus the base model; data slices for safety, relevance, and quality; and versions, hyperparameters, datasets, and architectures to choose the promotion candidate. Vertex AI TensorBoard tracks loss, accuracy, graphs, and embeddings, Experiments with Vertex ML Metadata links parameters, metrics, datasets, and artifacts, and Workbench with What-If Tool (WIT), Language Interpretability Tool (LIT), and Vertex Explainable AI attributions explains predictions. Deployment serves batch SQL inference in BigQuery ML or online endpoints from the registry, with Pipelines, Cloud Build CI/CD, stress testing, Cloud Monitoring, and Model Monitoring for drift and quality shifts keeping releases consistent and rollback-ready.

Key Facts

• BigQuery ML trains with SQL where data lives and requires data in BigQuery, SQL builders, and a supported model type, while AutoML for text, video, or tabular models requires tolerance for greater than 100ms latency.
• BigQuery ML uses INPUT_LABEL_COLS for classification and regression, TIME_SERIES_TIMESTAMP_COL and TIME_SERIES_DATA_COL for forecasting, RATING_COL, USER_COL, and ITEM_COL for recommendation, and NUM_TRIALS for hyperparameter tuning.
• The TRANSFORM clause defines preprocessing that is automatically reused at prediction time, ML analytic functions require an empty OVER() clause, and ML.WEIGHTS serves linear models while boosted trees and DNNs require EXPORT MODEL to Cloud Storage for XGBoost or TensorFlow inspection.
• A remote model invokes a Vertex AI model through a Cloud Resource connection with the Vertex AI User role, where ML.GENERATE_TEXT returns language, ML.GENERATE_EMBEDDING returns vectors, and row failures return NULL with a default threshold of 1.0.
• Supervised Gemini tuning supports Gemini 3.5 Flash, Gemini 3.1 Flash-Lite, Gemini 2.5 Pro, Gemini 2.5 Flash, and Gemini 2.5 Flash-Lite, with limits of 131,072 tokens per example and 1 GB JSONL, and each BigQuery ML model registers to only one Model Registry ID.