Train and Evaluate Models with BigQuery ML

Identify ML use cases for developing models by using BigQuery ML and AutoML means matching the data shape and team skills to the right Google Cloud training path. BigQuery ML (opens in a new tab) trains and predicts with SQL where large structured tables already live, while AutoML trains on labeled images, text, and other unstructured data without custom model code. Data flows from BigQuery tables directly to a BigQuery ML model and back to BigQuery tables, while data flows from labeled examples to an AutoML model that learns only from those examples.

1.1When BigQuery ML Fits SQL Workflows and Large Tables

BigQuery ML fits SQL workflows and large tables because training, evaluation, and prediction all run inside BigQuery with GoogleSQL. An analyst who can write SELECT statements can train a model without notebooks, clusters, or deployment servers, and BigQuery handles storage and compute scaling where the data already lives. Choose BigQuery ML when labeled history exists in the warehouse and results need to flow back to tables for dashboards and reports.

Three common patterns share the same flow where source data stays in BigQuery, the model learns inside BigQuery, and results flow back to tables:

1.2When AutoML Fits Unstructured Labeling Tasks

AutoML for unstructured data fits labeling tasks where each image or text item needs a predicted class or detected object. The AutoML model cannot understand label names on its own, so labels such as door and door_with_knob look unrelated unless training images show the visual difference. Success depends on supplying many representative examples, such as wider-angle images, higher or lower resolution images, and different points of view.

Choose AutoML when the team can supply labeled examples but does not want to write feature engineering or model-selection code. The team focuses on data quality and review while the platform handles training. Provide explicit examples for similar labels and remove classes with very few examples, because too few examples prevent consistent predictions.

1.3Evaluating and Iterating on AutoML Models

Evaluation in AutoML is the review of precision, recall, and confusion details on held-out data to decide what to fix next. Low precision and recall can indicate too little data or inconsistent labels, while perfect precision and recall can indicate test data that is too easy and may not generalize. A confusion matrix shows when the model confuses two labels by predicting one label much more often than the true label, which points to mislabeled examples or overlapping definitions.

Iteration in AutoML is an ordered cycle that learns from results before spending time on retraining:

1. Review evaluation metrics: Use precision, recall, and the confusion matrix to find weak classes.
2. Update data or setup: Add, remove, relabel, or re-split examples and adjust the training budget.
3. Train a new model: Create a new model with the revised data and settings.
4. Compare models: Compare metrics between the existing model and the new model to confirm improvement.

Detailed slices can be retrieved through ModelServiceClient, which depends on a regional API endpoint. The client is created once with the endpoint and reused, and each request addresses a model by projects, locations, and model ID. Controlling splits and time also matters: Agent Platform assigns items randomly by default, so near-duplicates can cause overfitting, and increasing maximum node hours can improve quality when the first run used a short budget.

Use of pretrained Google large language models (LLMs) using a remote connection in BigQuery means letting SQL queries call models hosted outside BigQuery. A Cloud resource connection (opens in a new tab) authorizes BigQuery to send requests to Vertex AI, and a remote model resource points to a foundation model such as Gemini. Data flows from BigQuery tables to the remote endpoint for inference in Vertex AI, and results flow back into query results or tables.

2.1Connection Types and Authentication for Remote Access

A BigQuery connection provides authorized access to an external system without sending credentials as cleartext. The connection definition is encrypted and stored in the BigQuery connection service, and owners share it by granting BigQuery connection Identity and Access Management (IAM) roles. BigQuery holds the trust boundary between the user on one side and the external system on the other.

Connection type controls the destination that traffic flows from BigQuery toward:

• Amazon Simple Storage Service for AWS data through BigQuery Omni
• Azure Blob Storage for Azure data through BigQuery Omni
• Apache Spark for Python procedures on Managed Service for Apache Spark
• Spanner, Cloud SQL, AlloyDB for PostgreSQL, and SAP Datasphere for federated database queries
• Google Cloud resources for Vertex AI remote models, remote functions, and BigLake

No connection is required for Bigtable and Google Drive. Authentication for a Cloud resource connection uses a system service account that BigQuery creates automatically with the connection. The administrator copies the service account ID from connection info and grants it permission to use Vertex AI, and grants users IAM permission to use the connection.

2.2Steps to Create a Cloud Resource Connection

Creation of a Cloud resource connection establishes the identity and location that later SQL statements reuse. The connection and the dataset that will hold the remote model should be in the same location, such as multi-region US, so BigQuery and Vertex AI work together efficiently.

1. Select the Cloud resource type: Add a connection for Vertex AI remote models, remote functions and BigLake, or run bq mk --connection with location and connection ID.
2. Create the connection in a location: Enter a connection ID and confirm the location so BigQuery generates the service account.
3. Copy the service account ID: Open connection details and copy the service account that will call Vertex AI.
4. Use the connection in BigQuery ML: Reference the full connection path in a CREATE MODEL statement to create a remote model.

2.3Defining a Remote Model with SQL

A remote model definition registers a Vertex AI foundation model as a BigQuery model resource without training data in BigQuery. The basic form is CREATE OR REPLACE MODEL PROJECT_ID.DATASET_ID.MODEL_NAME REMOTE WITH CONNECTION CONNECTION_PATH OPTIONS (ENDPOINT = 'ENDPOINT'). BigQuery stores the model name in a dataset, uses the connection to reach Vertex AI, and uses ENDPOINT to select the model to call. Use DEFAULT when a default connection is configured, or use the fully qualified path PROJECT_ID.REGION.CONNECTION_ID.

Location and endpoint rules control where the pieces can live. The dataset that contains the remote model must be in the same location as the connection, and the Vertex AI model named in ENDPOINT must be available in that location. In a multi-region US or EU dataset, the endpoint must be in a region inside that multi-region, although a global endpoint for supported Gemini models can improve availability.

Remote models over Cloud AI services use REMOTE_SERVICE_TYPE, such as CLOUD_AI_TRANSLATE_V3, instead of a foundation-model endpoint name.

2.4Generative and Task-Specific AI Functions in SQL

Generative AI functions in BigQuery are SQL functions that call Gemini and partner models to work with text directly in queries. These functions support content generation, summarization, structured extraction, classification, embedding generation, and enrichment. Text flows from a BigQuery row into the function call, and the result flows back as a new column in the same query result.

Task-specific AI functions use Cloud AI APIs for specialized work such as natural language processing, translation, document processing, audio transcription, and computer vision. They let teams enrich BigQuery data in place without building a custom model.

A standard ML project on Google Cloud moves in order from data collection to training to evaluation to prediction. Raw data flows from source systems into storage, clean tables flow into training, a trained model flows into evaluation, and only a model with acceptable quality flows into prediction for business use. BigQuery ML (opens in a new tab) supports this lifecycle with SQL so training data does not move to a separate environment.

3.1Archiving, Cleansing, and Storing Training Data

Storage for ML collection depends on data shape and readiness. Cloud Storage archives original raw files such as images, video, exports, and backups as the durable record. BigQuery stores structured, training-ready tables that can be queried with SQL and used directly for training. Dataflow is the pipeline service that reads raw records, applies cleansing logic, and writes clean rows to BigQuery.

Common Dataflow preparation work includes input and output handling, filtering bad or duplicate records, Python field mapping to standardize columns, and SQL transforms to join and aggregate features. Data flows from the source to each pipeline step in order, and only after cleansing does clean data flow to BigQuery. Keeping the raw copy in Cloud Storage separate from the prepared copy in BigQuery protects the project, because the team can fix pipeline logic and rebuild tables from the archive.

Continuous collection keeps BigQuery fresh through streaming from Pub/Sub, change capture from MySQL, and near real-time movement from Datastream through Dataflow templates.

3.2Training, Evaluating, and Predicting in BigQuery ML

The BigQuery ML lifecycle turns prepared tables into trusted predictions in three dependent steps. Training data flows from BigQuery tables into the training query, and the trained model is stored in a dataset like a table or view.

1. Train: Run a CREATE MODEL query that reads training rows and writes a trained model.
2. Evaluate: Run ML.EVALUATE with the model and held-out test data to measure quality.
3. Predict: Run ML.PREDICT with the model and new data to generate predictions.

Training can specify binary logistic regression to predict whether a website visitor will transact, using the Google Analytics sample dataset. Evaluation compares predictions to known outcomes: accuracy measures the share of correct categories where higher is better, while mean squared error measures the average squared difference for numbers where lower is better. Prediction output adds predicted columns to each input row, and the same pattern works for imported TensorFlow and XGBoost models. When predictions are needed outside BigQuery, the model can be exported to Cloud Storage or registered to Model Registry for online prediction.

3.3Governing and Reusing Datasets and Features

Centralized governance makes collected datasets discoverable and reusable without moving all data into one place. Knowledge Catalog unifies distributed data into logical lakes and zones, controls access through groups and roles, improves quality with auto data quality, and manages metadata as a universal catalog with search. BigQuery sharing publishes curated assets as listings, supports clean rooms for sensitive data, and grants view and publish access.

Reuse avoids rebuilding data for each model. Data products are published for discovery through Knowledge Catalog and sharing, Agent Platform datasets are managed centrally to reduce duplication, and Agent Platform Feature Store stores and serves the same features for online and offline use across projects and models. The reuse layer depends on the collection layer, because only governed and discoverable datasets can be safely shared.

Execution of SQL to create, train, and evaluate models using BigQuery ML (opens in a new tab) means using CREATE MODEL and evaluation functions instead of Python or Java. Training data stays in BigQuery tables or views, and the trained model is stored in a dataset like a table or view. Later prediction queries read new rows and pass them through the stored model object.

4.1Workflow and Syntax of CREATE MODEL

The CREATE MODEL workflow moves data from a training query to a stored model and then to predictions and explanations. Default settings handle much of the work, while options tune the result.

1. Create the model: Name the model, set OPTIONS such as MODEL_TYPE, and supply training rows with AS SELECT.
2. Preprocess features: BigQuery ML applies automatic preprocessing, plus manual logic in the TRANSFORM clause.
3. Refine the model: Tune hyperparameters such as NUM_TRIALS to better fit the data.
4. Evaluate the model: Check performance outside the training set and compare models.
5. Perform inference: Score new data with functions such as ML.PREDICT.
6. Explain the model: Review how features influenced predictions.
7. Inspect weights: Learn about coefficients, cluster centers, or components.

A statement names the destination as project.dataset.model, sets MODEL_TYPE in OPTIONS, and ends with the training query. Classification and regression models use INPUT_LABEL_COLS for the label column, matrix factorization uses USER_COL, ITEM_COL, and RATING_COL, and time-series models use TIME_SERIES_TIMESTAMP_COL and TIME_SERIES_DATA_COL. Split options such as DATA_SPLIT_METHOD, DATA_SPLIT_EVAL_FRACTION, and DATA_SPLIT_COL control which rows are reserved for evaluation.

4.2Internally Trained Versus External, Remote, and Imported Models

Internally trained models train entirely inside BigQuery and support a dry run to estimate bytes processed. These models include linear regression for numbers, logistic regression for categories, k-means for segments, matrix factorization for recommendations, principal component analysis (PCA) for dimensionality reduction, contribution analysis for metric drivers, and ARIMA_PLUS models for forecasting and anomaly detection. Choose an internally trained model when fast SQL training without an external service is the goal.

4.3Linear Regression, Logistic Regression, and K-Means Choices

Linear regression, logistic regression, and k-means differ by the question they answer and whether they need a label. All three train on a BigQuery table, view, or query and save the model for later evaluation.

4.4Measuring Quality with ML.EVALUATE and ML.METRICS

ML.EVALUATE is a SQL table function that takes a stored model and an evaluation dataset and returns one summary row of statistics. Data flows from the model object and from the evaluation table into the function, and the function matches each prediction to its true label. Calling ML.EVALUATE with no input data returns metrics stored during CREATE MODEL, while calling it with input data scores the model on the new table or query. The second pattern tests new data and supports fair comparison when two models are scored on the same data.

Automatic evaluation during CREATE MODEL depends on model type. K-means, PCA, autoencoder, and ARIMA_PLUS models use all input data, while linear and logistic regression, boosted tree, random forest, deep neural network, Wide-and-deep, and matrix factorization models use the split defined by data-split options. Classification quality uses accuracy, precision, and recall where higher is better, while regression error uses mean squared error where lower is better. ML.METRICS differs because it does not depend on a stored model; predictions flow directly from any query with actual and predicted columns into the function.

Inference using BigQuery ML (opens in a new tab) models means applying an already trained model to new data to generate predictions, forecasts, or anomaly flags. Batch inference scores many rows at once where the data already lives, so data flows from BigQuery tables into the model and results flow back to BigQuery tables. Teams can work through the console, bq tool, REST API, or Colab Enterprise notebooks that mix SQL and Python.

5.1Batch Inference Jobs Inside BigQuery

Batch inference inside BigQuery ML produces predictions for a whole input set rather than one interactive result. Tabular forecasting models support batch inference only. The batch job and the BigQuery tables it reads and writes must be in the same region, and the service account depends on roles such as BigQuery User. You can request batch inference directly from the model or register the model with Model Registry without exporting and re-importing it.

A typical batch workflow preserves order from preparation to delivery:

1. Prepare inputs: Keep input data in a BigQuery table and carry extra columns through to output.
2. Submit the batch job: Point the job to the trained model and input table through the console, SDK, or REST API.
3. Monitor the job: Track status until the run finishes, resuming incomplete jobs when needed.
4. Retrieve output: Query the predictions table and the separate error table for rows that could not be scored.

Prediction tasks include forecasting future time-series values, detecting anomalies, classifying categories, predicting numbers, clustering rows, reducing dimensions, and recommending items. Output is split so successful results and problems stay separate: the predictions table holds one scored row per input with classes, values, or bounds, while the errors table holds error codes tied to input rows.

5.2Time-Series Forecasting and Anomaly Detection

Time-series forecasting with BigQuery ML predicts future values from ordered history using TimesFM, ARIMA_PLUS, and ARIMA_PLUS_XREG. Forecast results flow back into BigQuery as a new result set. Queries configure horizon for how many future points to generate and confidence level for the interval around each value. Choose ARIMA_PLUS_XREG when external regressors matter, and choose TimesFM or ARIMA_PLUS when only the series history matters.

Anomaly detection flags values that break from the expected baseline. Supervised detection with ML.PREDICT applies when labeled examples identify anomalies during training and new serving data is scored, using linear and logistic regression, boosted trees, random forest, deep neural network, Wide-and-deep, and AutoML models. Unsupervised detection applies when no labels exist:

For non-time-series independent rows, ML.DETECT_ANOMALIES uses k-means distance to centroids, autoencoder reconstruction loss, or PCA reconstruction loss.

5.3Supplying New Data and Validating Results

Supplying new data for batch inference means providing BigQuery tables or comma-separated values (CSV) objects in Cloud Storage. The output format does not need to match the input, so a job can read BigQuery and write CSV or read CSV and write BigQuery. BigQuery source tables must be no larger than 100 GB, and CSV sources must have a header row, enclose strings in double quotes, and meet limits of 10 GB per object and 100 GB total. A forecast starts at the first null target value in time order, and nulls must be continuous within a series.

Evaluation against actual outcomes compares predictions to known labels to judge trust before wider use. For classification, output includes predicted classes with confidence scores; for regression, it includes predicted values with lower and upper bounds. Feature attributions can show each feature's contribution when the destination is BigQuery or JSONL, but not for CSV output. Pipeline operators such as BigqueryEvaluateModelJobOp, BigqueryForecastModelJobOp, and anomaly tasks orchestrate these steps, where downstream analysis depends on prediction finishing first.

Organization of models in Model Registry (opens in a new tab) means keeping models, versions, metadata, and deployment history in one central place. A single registered model can hold multiple versions, each with its own version ID, files, and evaluation results. The registry connects stored metadata to actions such as organizing, evaluating, deploying to an endpoint for online inference, creating batch inferences, and viewing version details.

6.1Registration and Metadata for BigQuery ML Models

Registration is the flow that copies a BigQuery ML model's reference and metadata into Model Registry for versioning and deployment alongside other models. Data flows from the BigQuery ML model to the registry entry, and the registry then manages downstream actions. The ordered flow is to create the model in BigQuery ML with SQL, choose which models to register, and then manage and deploy the registered model.

Registration and deployment limits depend on model type:

Complete metadata supports reproducibility and compliance by showing which version produced which result and what was evaluated and deployed. Agent Platform ML Metadata records lineage as a graph where artifacts and executions are nodes, events are edges linking inputs and outputs, and contexts group related work. Lineage includes training and evaluation data, hyperparameters, code, metrics such as accuracy, and descendant artifacts such as batch results.

6.2Aliases That Organize Releases

A model version alias is a mutable name that points to one specific version inside a single model resource. Model Registry looks up the alias and returns the version it currently points to, so applications fetch by alias name instead of version ID. Aliases work like Docker tags or branch references where the name stays stable while the underlying version moves.

The default alias is used when a request does not specify a version. Model Registry assigns it to the first version of a new model, and exactly one version must carry it at all times. In the console, the Alias column shows which version is stable for deployment, and Set as default moves the alias after opening the model, choosing the version, and confirming. Custom aliases such as staging and production label lifecycle stages, so the same reference keeps working after the underlying version changes. Moving an alias promotes a model: the team validates a newer version, reassigns the alias, and alias-based calls then resolve to the new version. Reusing an alias automatically removes it from the old version.

Alias names must be unique by model, non-numerical, and match [a-z][a-z0-9-]{0,126}[a-z0-9]. An alias is different from a label, which is separate metadata.

6.3Copying, Deleting, and Monitoring Versions

Copying moves a chosen version from a source location to a destination project or region, where it appears as a new model or new version. Data flows from the source model resource to the destination registry. In the console, open Model Registry, choose Copy model to another project or region, select the version and destination such as us-central1, and confirm target type and encryption with a Google-owned or Cloud KMS key.

| Copy option | Result in destination | | --- | --- | --- | | Copy as new model | Creates a separate model entry with its own version history | | Copy as new version | Adds the copied files as a new version under an existing model |

Choose a new model for an independent use case and a new version to keep iterations together. When a copy omits VERSION_ID, the default version is copied, and a first copy to a new region receives the default alias. Deletion enforces dependencies: a deployed model must first be undeployed, a registered BigQuery ML model must first be deleted from BigQuery ML, and deleting a parent model deletes all versions and evaluations. A model monitor depends on one fixed version and cannot be switched, so watching a new version requires a new monitor.

Key Facts

• BigQuery ML trains with SQL where large tables already live and returns predictions to BigQuery tables, supporting clustering for segmentation, classification for churn, and regression and time-series models for forecasting.
• AutoML fits unstructured labeling tasks and improves through review of precision, recall, and confusion matrix, followed by data fixes, retraining, and model comparison.
• A Cloud resource connection lets BigQuery call Vertex AI LLMs through a system service account, and a remote model defined with REMOTE WITH CONNECTION and ENDPOINT requires matching dataset and connection locations.
• BigQuery ML follows train with CREATE MODEL, evaluate with ML.EVALUATE, and predict with ML.PREDICT, while ML.METRICS scores actual and predicted columns without a stored model.
• A Model Registry alias such as staging or production points to one version at a time, remote models cannot be registered, and a model monitor stays tied to its original version.