Generating Predictions with BigQuery ML

Integration of generative AI, embeddings, and remote model invocations in BigQuery (opens in a new tab) ML means running large language model inference directly inside SQL workflows. BigQuery ML sends rows from BigQuery 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.

1.1Remote Models and Secure Cloud Resource Connections

A remote model in BigQuery ML is a model object that points to a model hosted outside BigQuery, such as a Vertex AI foundation model or a deployed Vertex AI endpoint. The remote model depends on a Cloud Resource connection, which provides a dedicated Google Cloud 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 side, and the connection service account must have permission on the Vertex AI side to invoke predictions.

Creating a secure invocation path follows an ordered dependency chain:

1. Create connection: Create a Cloud Resource connection in the same project and region as the BigQuery dataset.
2. Grant access: Grant the connection service account the Vertex AI User role and read access to Cloud Storage buckets used by object tables.
3. Create remote model: Run CREATE MODEL with REMOTE WITH CONNECTION that names the connection and the Vertex AI ENDPOINT or foundation model identifier.
4. Query for inference: Call ML.PREDICT, ML.GENERATE_TEXT, or ML.GENERATE_EMBEDDING against the remote model, where BigQuery authenticates to Vertex AI as the connection identity.

Because every call carries the connection identity, auditing and access control stay centralized while SQL users invoke powerful models with a table function.

1.2ML.GENERATE_TEXT for Generative Inference in SQL

ML.GENERATE_TEXT is the BigQuery ML table function that sends prompts to a large language model and returns generated text as structured rows. Input rows flow from a query expression with prompt columns and optional parameters into the remote text model, and output rows flow back with generated text, safety attributes, and model metadata. The function supports multimodal prompts when the remote model accepts text plus images, audio, or video references from object tables.

Expert workflows control quality and downstream use through prompt design and structured parsing. Common practices include giving instructions plus few-shot examples in the prompt column, constraining output to JSON for parsing into STRUCT fields, and batching prompts as a table scan for summarization, classification, extraction, and question answering. Choose ML.GENERATE_TEXT when the output is human-readable or semi-structured language rather than a numeric score or vector.

1.3ML.GENERATE_EMBEDDING for Vector Search and Semantic Insights

ML.GENERATE_EMBEDDING is the BigQuery ML table function that converts text or multimodal content into dense vector embeddings for similarity comparison. Source text flows from a table column to a text-embedding remote model, and a floating-point array flows back per row that preserves semantic distance between concepts. Embeddings generated in BigQuery can be stored in a table, indexed with vector search, and joined to business data for recommendation and deduplication.

Production embedding pipelines depend on consistent preprocessing and model versioning. Text is normalized and chunked before embedding so long documents become multiple comparable vectors, the same embedding model version is used for both stored and query vectors, and output embeddings are saved with source keys for traceability. Choose ML.GENERATE_EMBEDDING when the goal is mathematical comparison of meaning across documents, images, or product descriptions rather than generating new prose.

1.4Object Tables for Unstructured and Multimodal Data

An object table is a BigQuery table that maps files in Cloud Storage to queryable rows without copying file bytes into BigQuery storage. The object table depends on a Cloud Resource connection for access, and each row represents one object with columns for URI, content type, size, and generation. Queries over the object table feed URIs and prompts directly into ML.GENERATE_TEXT and ML.GENERATE_EMBEDDING, so images, PDFs, and audio files become inputs to foundation-model inference inside SQL.

This pattern turns unstructured files into structured insights in one statement. A query first scans the object table to filter files by type or prefix, then passes the selected URIs plus an instruction prompt to the generative function, then parses the returned text into typed columns for analytics. Because only references and results move through BigQuery while model execution stays in Vertex AI, the approach scales to millions of objects for classification, captioning, transcription summaries, and metadata enrichment.

1.5Orchestrating Batch, Remote, and Generative Prediction Workflows

Orchestration of batch, remote, and generative prediction workflows divides work by latency, customization, and data shape across BigQuery ML and Vertex AI. A typical rapid-prototyping flow first creates a training dataset shared between BigQuery and Vertex AI, then trains competing BigQuery ML and AutoML models, then extracts evaluation metrics from both models, then selects the best model, then deploys it to a Vertex AI endpoint, and only after deployment tests serving with live prediction requests. Generative and embedding steps can run before or after this loop to prepare unstructured inputs or explain outputs.

The three inference paths differ in function and selection criteria:

Batch inference in BigQuery (opens in a new tab) ML is the execution of SQL inference functions over large stored tables to generate predictions in bulk. ML.PREDICT, ML.FORECAST, ML.RECOMMEND, and ML.DETECT_ANOMALIES take data from an input table or query and return a result table where each input row or series is paired with predicted values or scores. Batch execution is used for tabular scoring, multi-series forecasting, recommendation generation, and anomaly detection when predictions must be saved for downstream analysis.

2.1Batch Inference Inputs, Series Identifiers, and Validation

Batch inference inputs in BigQuery ML must provide the series and target structure that the trained model expects, or validation will fail for the missing series. In the liquor-sales example, store_name is the series identifier and sale_dollars is the forecast target across four stores in Ida Grove. Three stores including Ida Grove Food Pride, Casey's General Store #3757, and Brew Ida Grove request inferences, while Discount Liquors of Ida Grove requests no forecast. The inference job therefore returns prediction rows for the requested series and generates a validation error for Discount Liquors of Ida Grove because no forecast was requested for that series identifier.

2.2Forecast Output Schemas Including Quantile-Loss Models

Forecast output schemas in BigQuery ML return one row per series and horizon step with a predicted-value struct that varies by loss type. A standard forecast returns a single predicted value per row, while a quantile-loss optimized model returns both probability levels and their corresponding values. In the 14-day store-sales example, the output column predicted_Sales.quantile_values holds the quantile levels and predicted_Sales.quantile_predictions holds the parallel array of sales values. The paired arrays preserve the full predictive distribution for planning and risk analysis.

The first row shows how the parallel arrays map probabilities to sales amounts:

Downstream queries can read the median value of 5615.64 for point planning and the outer quantiles for interval and risk analysis.

2.3Normalized Feature Importance for Interpreting Batch Predictions

Normalized global feature importance in BigQuery ML explains which input attributes drive batch predictions by dividing each aggregated attribution by total attribution. The query reads from attributions_aggregated_with_total and computes ROUND(date / total, 2), ROUND(advertisement / total, 2), ROUND(holiday / total, 2), ROUND(sales / total, 2), ROUND(store / total, 2), and ROUND(product / total, 2). Each division converts a raw attribution sum into a proportion of total influence, and rounding to two decimals makes the contributions directly comparable. The result shows the relative weight of time, marketing, calendar, history, location, and item features on the same scale.

2.4Handling Row-Level Failures and Thresholds in Large Batch Queries

Row-level failures in large batch inference occur when a single row exceeds quota or limits such as RESOURCE_EXHAUSTED, and the function returns NULL for that row rather than failing the whole query. The error ratio is calculated as the number of failed rows divided by the total number of rows processed by the model, and a failure threshold allows the query to succeed despite partial failures. The default threshold value is 1.0, and lowering it to a smaller value such as 0.2 makes the query fail instead of succeeding with partial results. When a query succeeds with partial failures, BigQuery returns a warning and records details in the Gen AI function errors field in the Job information tab.

Serving architecture in BigQuery ML (opens in a new tab) is the choice between scoring data where the data already lives and exporting a model to serve predictions with low latency elsewhere. BigQuery ML keeps data, infrastructure, and predefined model types in one system and scores with SQL, while Vertex AI Endpoints serve exported artifacts outside the warehouse for real-time requests. The export path moves a trained model to Cloud Storage in a standard format such as TensorFlow SavedModel or ONNX, and the import path loads those same artifacts back into BigQuery ML for in-database inference.

3.1In-Database Batch Scoring in BigQuery ML

In-database batch scoring means the query engine applies the model to BigQuery tables without moving rows to an external service. Data flows from BigQuery tables or object tables into an ML.PREDICT query, and prediction results flow back into a BigQuery table for downstream analysis. This pattern depends on the training data and scoring data both residing in BigQuery and on the problem matching a model type that BigQuery ML supports. Choose in-database scoring when the workload is large-scale batch scoring in SQL, when data gravity favors BigQuery, and when operational simplicity matters more than single-request latency.

3.2Real-Time Inference with Vertex AI Endpoints

Real-time inference on Vertex AI Endpoints means a persistent serving deployment receives one request at a time and returns a prediction with low latency. Traffic flows from the client application to the endpoint, the endpoint loads the model artifact from Cloud Storage, and the response flows back to the client without running a BigQuery query. This pattern depends on an online serving stack with provisioned machines and on features that can be supplied at request time, often through Vertex AI Feature Store with an online store backed by BigQuery tables or views. Choose Vertex AI Endpoints when single-request latency must stay low, when the application needs serverless autoscaling outside the warehouse, or when the model type or custom code cannot run inside BigQuery ML.

3.3Exporting and Loading Standard Model Artifacts Through Cloud Storage

Model export through Cloud Storage packages a trained model as portable files that both BigQuery ML and Vertex AI can consume. A TensorFlow export consists of a saved_model.pb file plus a variables folder, while a PyTorch export uses a model in ONNX format, and both are stored under a Cloud Storage path in the form gs://bucket_name/[folder_name/]*. The bucket acts as the trust and ownership boundary between training, BigQuery ML, and Vertex AI serving, so Identity and Access Management (IAM) on the project and bucket controls which service can read the artifacts.

Preparing artifacts for serving follows an ordered workflow:

1. Save the model locally: Save a custom model on the local machine, or download a model from TensorFlow Hub to the local machine.
2. Create a Cloud Storage bucket: Create the bucket when one does not already exist to hold the artifacts.
3. Upload artifacts to the bucket: Upload saved_model.pb with its variables folder for TensorFlow, or the ONNX file for ONNX, to the bucket folder.

Loading those artifacts into BigQuery ML uses a CREATE MODEL statement that binds the artifact location to a BigQuery ML model object. The statement declares model_type as TENSORFLOW or ONNX and sets model_path to the bucket path with a trailing wildcard, for example gs://my_bucket/my_model_folder/*. First the model object is created in PROJECT_ID.DATASET_ID.MODEL_NAME, then BigQuery ML resolves model_path to the files in Cloud Storage, and only after that resolution can ML.PREDICT reference the model for in-database scoring.

3.4Verifying Imported Models and Image Inputs Before Serving

Verification of imported models means checking that an imported model exposes compatible inputs and outputs before it serves predictions reliably. BigQuery ML surfaces this contract on the model Schema tab, where the Labels section identifies the fields output by the model and the Features section identifies the fields that must be input to the model. For TensorFlow models that need shape details, the saved_model_cli show command inspects the SavedModel after installing TensorFlow. Image models add a preprocessing dependency because raw bytes from an object table must become numeric arrays before prediction.

Image preprocessing for BigQuery ML uses three related functions:

• ML.DECODE_IMAGE converts image bytes to a multi-dimensional ARRAY that can feed ML.PREDICT directly or be materialized to a table column as decoded_image.
• ML.CONVERT_COLOR_SPACE converts images with an RGB color space to a different color space expected by the model.
• ML.CONVERT_IMAGE_TYPE converts pixel values output by ML.DECODE_IMAGE from floating point numbers to integers with range [0, 255).

Data flows from the object table through ML.DECODE_IMAGE and then through the color and type conversions into ML.PREDICT, so a mismatch in Features versus preprocessing causes serving-time failures even when the artifact loaded successfully.

3.5Choosing Between BigQuery ML, AutoML, and Custom Training for Serving

The serving decision for machine learning on Google Cloud starts from where the data lives, what the team can operate, and what latency the application tolerates. BigQuery ML unifies data, infrastructure, and predefined model types for SQL-based workflows, AutoML automates architecture choice and tuning for common problems, and Vertex AI custom training runs user-owned routines on serverless architecture. Unstructured inputs reinforce the same boundary between warehouse storage and serving storage.

The three environments differ in what they provide and when to choose them:

Unstructured data for these environments is stored in large container formats on Cloud Storage, using sharded TFRecord files for TensorFlow or Avro files for other frameworks. Combining many small media files into large files improves read and write throughput, aiming for files of at least 100MB and between 100 and 10,000 shards, with buckets and directories grouping shards for management. Structured data and materialized intermediate tables stay in BigQuery for training and batch scoring speed, and only the features needed for low-latency serving are replicated to the online store.

Key Facts

• A remote model in BigQuery ML invokes a Vertex AI foundation model or endpoint through a Cloud Resource connection, and the connection service account must be granted the Vertex AI User role.
• ML.GENERATE_TEXT in BigQuery ML returns generated language from prompts, while ML.GENERATE_EMBEDDING returns floating-point vectors for semantic search, clustering, and retrieval.
• A quantile-loss forecast in BigQuery ML returns parallel arrays where predicted_Sales.quantile_values holds probability levels and predicted_Sales.quantile_predictions holds sales values such as 4484.04 at 0.1, 5615.64 at 0.5, and 6853.29 at 0.9.
• Row-level batch inference failures return NULL for the failed row, the default failure threshold is 1.0, and LIMIT is applied after the model processes a batch of rows.
• A TensorFlow model exported for BigQuery ML and Vertex AI serving consists of saved_model.pb plus a variables folder, and loading it into BigQuery ML requires model_type = 'TENSORFLOW' with model_path ending in /*.