BigQuery ML Batch Inference and Forecasting

Implementing prediction mechanisms with BigQuery ML (opens in a new tab) means training, evaluating, and running inference directly inside BigQuery without moving data to another system. Data flows from BigQuery tables into the trained model, and prediction results flow back into BigQuery tables where they can be queried with GoogleSQL. You can work with BigQuery ML through the Google Cloud console, the bq command-line tool, the REST API, or Colab Enterprise notebooks, so analysts can use existing SQL tools and skills.

1.1Batch Inference Inside BigQuery ML

Batch inference inside BigQuery ML is the process of generating predictions for many rows at once where the data already lives. You can get inferences in two ways: request batch inferences directly from the model in BigQuery ML, or register the models directly with Model Registry (opens in a new tab) without exporting them from BigQuery ML or importing them into Model Registry. Tabular forecasting models support batch inference only, so they produce future values for a whole input set rather than one interactive result at a time. The batch job and the BigQuery tables it reads and writes must be in the same region, and the service account running the job depends on BigQuery permissions such as the BigQuery User role.

A typical batch workflow moves in a fixed order from preparation to output delivery:

1. Prepare inputs: Input data stays in a BigQuery table, with required request structure and extra columns carried through to the output.
2. Submit the batch job: The job is created through the console, SDK, or REST API and points to the trained model and the input table.
3. Monitor the job: Job status and progress are tracked until the run finishes, with incomplete jobs able to be resumed.
4. Retrieve batch output: Prediction results are written to an output dataset for further querying, alongside a separate error record when a row cannot be scored.

1.2Prediction Tasks Supported by BigQuery ML

Prediction tasks supported by BigQuery ML are the kinds of questions a trained model can answer with new data, including forecasting, anomaly detection, classification, and regression. Forecasting predicts future values in time-series data using models such as TimesFM, ARIMA_PLUS, and ARIMA_PLUS_XREG. Anomaly detection flags unusual points in that same time-series data, while classification assigns a category and regression predicts a numeric value. Explainable AI helps explain why a predictive model produced a result, and contribution analysis helps explain changes to key metrics in multi-dimensional data.

BigQuery ML also supports related analysis tasks that feed into predictions:

• Time series forecasting for future trends and demand
• Anomaly detection for unusual events in time series
• Classification for assigning labels or categories
• Regression for predicting continuous numbers
• Clustering for grouping similar rows
• Dimensionality reduction for simplifying many features
• Recommendations for suggesting relevant items

Because inference runs with GoogleSQL queries and notebooks let you use SQL and Python interchangeably, you can combine model outputs with AI or ML Python libraries in the same workflow.

1.3Validating Predictions Against Your Data

Validating predictions against your data is the step where prediction quality is checked by comparing model outputs against known data. The BigqueryEvaluateModelJobOp (opens in a new tab) operator runs evaluation as a pipeline task, while related operators expose details such as confusion matrices, centroids, feature information, feature importance, advanced weights, and ARIMA coefficients and evaluation results. For classification, output includes predicted classes with confidence scores, and for regression, output includes the predicted value with lower and upper bounds. Feature attributions can also be returned to show how much each input feature contributed to a specific prediction.

1.4Using Predictions in Broader Analysis Workflows

Using predictions in broader analysis workflows means storing predictions in BigQuery so later queries, dashboards, and pipelines can use them like any other table. Pipeline components orchestrate each operation as a repeatable task, where a downstream analysis step depends on the prediction step finishing first. The control plane for these workflows is the Google Cloud SDK set of BigQuery ML operators, including tasks for creating, evaluating, forecasting, detecting anomalies, explaining predictions and forecasts, and exporting or dropping models. Because predictions remain in BigQuery, data flows from the source table to the model to the predictions table to the next analysis query without leaving the warehouse trust boundary.

Prediction output is split so successful results and problems stay separate:

Performing time-series forecasting and anomaly detection with BigQuery ML (opens in a new tab) means running batch inference tasks directly where timestamped data lives using SQL. Forecasting generates future values from ordered historical data, while anomaly detection flags values that deviate from the expected baseline. Both tasks use specialized time-series models and SQL functions, so no data movement or separate prediction service is required.

2.1Time-Series Forecasting with BigQuery ML

Time-series forecasting with BigQuery ML predicts future values from timestamped historical data using three supported models: TimesFM, ARIMA_PLUS, and ARIMA_PLUS_XREG. Data flows from BigQuery tables to the trained model through a SQL inference query, and forecast results flow back into BigQuery as a new result set. The workflow depends on the model staying with the data, which lets practitioners train, evaluate, and run inference from the console, bq tool, REST API, or notebooks. Forecasting queries configure temporal scope with parameters such as forecast horizon, which controls how many future points are generated, and confidence level, which controls the prediction interval around each forecasted value. Choose ARIMA_PLUS_XREG when the forecast depends on external regressors, and choose TimesFM or ARIMA_PLUS when the forecast depends only on the history of the time series itself.

2.2Unsupervised Anomaly Detection

Unsupervised anomaly detection with BigQuery ML identifies outliers without labeled examples of what counts as anomalous. Unsupervised detection is the right choice when the definition of anomalous is uncertain or no labeled training data exists, and it can run against training data or new serving data. For time-series data, the detection function depends on the model type, so the model and function must be matched correctly.

For independent and identically distributed random variables (IID), which are non-time-series rows treated as independent observations, ML.DETECT_ANOMALIES also detects anomalies but by a different mechanism:

• K-means detects anomalies based on the shortest distance among the normalized distances from the input data to each cluster centroid.
• Autoencoder detects anomalies based on reconstruction loss in terms of mean squared error.
• PCA detects anomalies based upon reconstruction loss in terms of mean squared error.

2.3Supervised Anomaly Detection with Labeled Data

Supervised anomaly detection with BigQuery ML treats anomaly detection as prediction with the ML.PREDICT function. Supervised detection applies when labeled data already identifies which rows are anomalies, so the model learns that mapping during training and then scores new serving data. Default settings in CREATE MODEL and the inference functions allow a basic model to be created and used without deep ML knowledge. Choose supervised detection with ML.PREDICT when labels exist, and choose unsupervised detection with AI.DETECT_ANOMALIES or ML.DETECT_ANOMALIES when labels do not exist.

The ML.PREDICT function works with the following supervised models:

• Linear and logistic regression models
• Boosted trees models
• Random forest models
• Deep neural network (DNN) models
• Wide and Deep models
• AutoML models

Executing inference with a pre-trained BigQuery ML (opens in a new tab) model means applying a model that is already trained to new data to generate predictions, forecasts, or anomaly results. BigQuery ML performs batch inference inside BigQuery, so large sets of new rows are scored at once without moving data to another system. A trained model stays in a BigQuery dataset and reads input rows from a table or file, then results flow from the model to an output table or file for review and comparison with actual outcomes.

3.1How Batch Inference Uses Trained Models

Batch inference with trained models is the process of scoring many new rows in one job using a trained BigQuery ML model. The model depends on new input data that has the same features used during training, and the inference job produces an output that adds a predicted value, forecasted value, or anomaly flag to each input row. Because the work runs where the data already lives, teams avoid export and import steps and keep one consistent copy of the data. You can get inferences in two ways: request batch inferences directly from the model in BigQuery ML, or register the models directly with Model Registry (opens in a new tab) without exporting them or importing them. Direct inference in BigQuery ML is the simplest path when scoring stays in SQL, while Model Registry is useful when the same model must also be managed alongside models outside BigQuery.

3.2Generate Predictions, Forecasts, and Anomaly Results

SQL-based inference in BigQuery ML uses GoogleSQL queries to apply a trained model to new data for common predictive tasks. The same workflow covers classification and regression, time-series forecasting, and time-series anomaly detection, with different model types producing different result shapes. Time-series forecasting and anomaly detection use TimesFM, ARIMA_PLUS, and ARIMA_PLUS_XREG models. Pipeline components orchestrate these same operations outside interactive SQL, including BigqueryForecastModelJobOp (opens in a new tab) for forecasts and BigqueryDetectAnomaliesModelJobOp for anomalies.

The three main inference results differ by question answered and output produced:

3.3Supply New Data and Choose an Output

Supplying new data for batch inference means providing input from BigQuery tables or from comma-separated values (CSV) objects stored in Cloud Storage. The output format does not need to match the input format, so a job can read a BigQuery table and write forecasting results to CSV in Cloud Storage, or read CSV files and write results to BigQuery. BigQuery source tables must be no larger than 100 GB, and a table in a different project requires granting the BigQuery Data Editor role to the service account. Time-series forecasts have an additional ordering rule where the forecast starts on the first row of a time series, ordered by time, with a null value in the target column, and null values must be continuous within that series.

CSV input in Cloud Storage has strict formatting and size rules. Each CSV source must meet the following requirements:

• Include a header row with column names as the first row.
• Be no larger than 10 GB per object, with up to 100 GB total across multiple files.
• Enclose all strings in double quotation marks.
• Grant the Storage Object Creator role to the service account when the bucket is in a different project.

For CSV files an empty string is treated as null, while BigQuery supports null values natively.

3.4Evaluate Predictions Against Actual Outcomes

Evaluation against actual outcomes is the step that compares predicted results against known true labels or values to judge model quality. An evaluation query runs the trained BigQuery ML model against a holdout or new dataset where the true label or value is known, then returns quality measures instead of only predictions. This separation matters because inference produces decisions for new rows, while evaluation measures how trustworthy those decisions are before wider use. BigQuery ML supports explanation alongside scoring to help analysts understand results, including explainable AI, feature attributions, and contribution analysis for changes in key metrics.

You can enable feature attributions when the output destination is BigQuery or JSONL on Cloud Storage, but feature attributions are not supported for CSV output on Cloud Storage.

3.5Run Inference from SQL Tools and the Console

Running inference from SQL tools and the console means starting the same BigQuery ML batch job from four interfaces that share one pattern. These interfaces let SQL practitioners stay in familiar tools while controlling model version, source, output, and options. A large batch job can take time to complete, so jobs are monitored after submission until output is delivered.

The four interfaces are:

• Google Cloud console for interactive queries and batch inference pages.
• bq command-line tool for scripted dataset and job operations.
• REST API for programmatic job control.
• Colab Enterprise notebooks for workflows that mix SQL and Python and use Python AI libraries.

A console batch inference follows an ordered flow from model selection to output delivery:

1. Open Batch inferences: Go to the Batch inferences page in the Agent Platform section and click Create.
2. Select model and version: Enter a name for the batch inference and choose the model name and version.
3. Select source: Choose CSV on Cloud Storage or a table in BigQuery, then supply the bucket location or the project, dataset, and table or view.
4. Select output: Choose CSV or BigQuery and supply the destination bucket or project and dataset path, such as bq://projectid.datasetid.
5. Add options: Optionally enable feature attributions and model monitoring analysis before submitting.

Key Facts

• BigQuery ML runs batch inference inside BigQuery, so data flows from BigQuery tables to the trained model and prediction results flow back to BigQuery tables.
• The batch job and its BigQuery input and output tables must be in the same region.
• TimesFM uses AI.DETECT_ANOMALIES for time-series anomaly detection, while ARIMA_PLUS and ARIMA_PLUS_XREG use ML.DETECT_ANOMALIES.
• A time-series forecast starts at the first null target value in time order, and null values must be continuous within that series.
• BigQuery source tables for batch inference must be no larger than 100 GB, while CSV sources in Cloud Storage must be no larger than 10 GB per object and 100 GB total.