Professional Machine Learning Engineer

Evaluate Serving Architectures and Model Export for Online Inference

Exporting Models for Vertex AI Serving

Exporting a model from BigQuery ML saves trained model artifacts to a Google Cloud Storage (GCS) bucket in standard file formats like TensorFlow SavedModel, ONNX, or XGBoost. This export step decouples the model artifact from the BigQuery compute engine so external serving systems can run it. Once stored in Cloud Storage, the model files can be uploaded to the Vertex AI Model Registry and deployed directly to a Vertex AI Endpoint. Teams choose model export when an application requires a dedicated, autoscaling infrastructure that delivers lower latency guarantees than standard database SQL queries can offer.

Choosing Between Batch and Real-Time Inference

The choice between in-database batch scoring and real-time inference depends primarily on application latency requirements and data location. Batch scoring executes inside BigQuery using SQL functions like ML.PREDICT, processing large datasets directly where they are stored over minutes or hours. In contrast, real-time online inference deploys the model to a Vertex AI Endpoint to answer single incoming HTTP requests with sub-second response times. Choose batch scoring when data is already stored in BigQuery and predictions are not immediately time-sensitive; choose Vertex AI online inference for live, interactive applications that need immediate predictions.

Model Registration and Deployment Patterns

BigQuery ML supports direct model registration with the Vertex AI Model Registry without requiring a manual export step to Cloud Storage. This direct registration links the database model to a Vertex AI model resource, providing centralized versioning, evaluation metrics, and one-click endpoint deployment. Alternatively, engineers can use a manual export pattern to download the artifact to Cloud Storage and configure custom container environments or deploy outside Google Cloud. When deploying to a Vertex AI Endpoint, the user configures specific compute machine types, optional graphics processing units (GPUs), and autoscaling parameters to match expected traffic.

Supported Model Types and Export Formats

Model export capabilities in BigQuery ML depend on the underlying algorithm and training framework. Standard internal supervised algorithms like linear regression, logistic regression, and deep neural networks export into TensorFlow SavedModel format, while tree-based models export into XGBoost formats. Certain analytical models, such as matrix factorization and ARIMA_PLUS time-series models, have architectural limitations that restrict export for standard online serving. Engineers must verify the compatibility of their specific model architecture with target export formats like ONNX before finalizing a deployment pipeline.

Integration with Feature Serving for Inference

Feature pipelines must deliver identical data transformations during both training and online inference to avoid training-serving skew. When a BigQuery ML model is trained with a TRANSFORM clause, the data preprocessing logic is packaged directly inside the exported model artifact and applied automatically during inference on Vertex AI. For external feature retrieval, Vertex AI Feature Store can serve low-latency online feature values derived from upstream BigQuery tables. This integration ensures that live incoming requests receive consistent feature transformations without requiring client applications to rewrite transformation code.

---

Execute Batch Inference and Specialized Analytical Model Predictions

Preparing Data for Batch Prediction

Preparing data for BigQuery ML batch prediction requires establishing static data boundaries to ensure reproducibility and auditability. Creating a BigQuery table snapshot of source tables protects the inference process from unexpected modifications during query execution. Direct queries against continuously changing production tables can read different records across runs, leading to inconsistent prediction results. Organizations should isolate inference outputs in dedicated target datasets and maintain data lineage tracking from source snapshot to final prediction.

Core Prediction Functions

BigQuery ML offers specialized SQL inference functions tailored to distinct machine learning tasks. The ML.PREDICT function scores standard supervised learning models, including classification and regression algorithms, against tabular feature columns. The ML.FORECAST function evaluates time-series models to project future intervals alongside upper and lower confidence boundaries. The ML.RECOMMEND function evaluates matrix factorization models to produce ranked recommendation scores for user and item pairings. The ML.DETECT_ANOMALIES function scores dataset records against baseline statistical distributions using configurable sensitivity thresholds to identify outliers.

Output Schema and Configuration

The structure of BigQuery ML prediction outputs matches the mathematical objective of the trained model. Supervised classification models output predicted class labels along with probability arrays for each potential class, whereas regression models output a single continuous numerical value. Time-series forecast outputs include time stamps, point forecasts, and prediction intervals defined by prediction confidence parameters. Engineers can apply custom decision thresholds in prediction queries to balance business-specific trade-offs, such as lowering a detection threshold to catch more potential fraud events.

Batch Execution Optimization

Optimizing batch prediction across massive tables requires efficient data storage patterns and parallel execution management. Partitioning source tables by date or integer ranges minimizes the total bytes scanned by prediction queries, reducing execution time and slot utilization. For exceptionally large tables, splitting prediction workloads into distinct parallel queries improves throughput across BigQuery compute slots. Prediction jobs write results to destination tables using the Storage Write API, which ensures high-performance, stream-lined writes with exactly-once record delivery semantics.

Output Destination Setup

Inference outputs must be stored in a destination BigQuery dataset located within the same Google Cloud project and geographic region as the trained model resource. Cross-region prediction queries are not supported because the model and data compute layers must reside in the same physical boundary. Administrative policies must grant the executing identity write permissions on the target dataset before running batch jobs. After tables are written, administrators can apply row-level security and column-level security policies to restrict sensitive prediction results to authorized users.

---

Integrate Generative AI, Embeddings, and Remote Model Invocations

Remote Model Invocations and Cloud Resource Connections

BigQuery ML accesses foundation models and external Vertex AI services through a dedicated Cloud resource connection. Creating this connection provisions an underlying Google Cloud managed service account dedicated to the database connection resource. An administrator grants this service account the required Identity and Access Management (IAM) roles, such as the Vertex AI User role, to authorize model invocation requests. Users define a remote model inside BigQuery using a CREATE MODEL statement paired with the REMOTE WITH CONNECTION clause pointing to the configured connection resource.

Text Generation and Multimodal Inference with ML.GENERATE_TEXT

The ML.GENERATE_TEXT function allows SQL queries to send prompts directly to remote large language models (LLMs) hosted on Vertex AI. The SQL engine packages prompt strings, media references, and runtime hyperparameters—such as temperature and maximum output tokens—into batch inference requests sent to the remote endpoint. The foundation model processes the batch and returns structured responses, token counts, and completion statuses back into query output columns. Organizations use ML.GENERATE_TEXT to run tasks like text summarization, sentiment scoring, and automated classification over millions of rows without exporting data to separate application tiers.

Vector Generation with ML.GENERATE_EMBEDDING

The ML.GENERATE_EMBEDDING function converts text strings and multimodal content into dense, high-dimensional numerical vectors within BigQuery. The function routes input values through a remote embedding model, receiving an array of floating-point values representing the semantic meaning of each record. These vector arrays are stored directly in table columns alongside standard structured relational data. The resulting embeddings enable downstream workflows like retrieval-augmented generation (RAG), semantic vector search, and clustering directly inside the data warehouse.

Processing Unstructured Data Using BigQuery Object Tables

BigQuery object tables create a structured, read-only SQL table interface over unstructured files stored in Cloud Storage buckets. The object table exposes file metadata, access paths, and uniform resource identifiers (URIs) for items like images, audio files, and documents. When used with ML.GENERATE_TEXT or ML.GENERATE_EMBEDDING, the object table passes file URIs to remote multimodal models for direct visual and audio processing. This design allows analytical models to inspect and analyze massive unstructured media archives without copying raw binary files directly into database tables.

---

Key Facts

• Exporting a BigQuery ML model writes portable model artifacts (such as TensorFlow SavedModel, XGBoost, or ONNX) to Google Cloud Storage for deployment on Vertex AI Endpoints.
• Preprocessing logic defined in a model's TRANSFORM clause is embedded into exported artifacts, ensuring consistent feature processing between training and online serving.
• Dedicated SQL inference functions serve distinct modeling tasks: ML.PREDICT handles supervised scoring, ML.FORECAST generates time-series forecasts with confidence intervals, ML.RECOMMEND provides ranked collaborative filtering, and ML.DETECT_ANOMALIES flags outliers.
• A Cloud resource connection creates a dedicated service account that requires appropriate Identity and Access Management roles (such as Vertex AI User) to execute remote foundation model queries via ML.GENERATE_TEXT and ML.GENERATE_EMBEDDING.
• BigQuery object tables expose read-only metadata and Cloud Storage uniform resource identifiers (URIs), allowing remote multimodal models to process unstructured data without ingesting raw files into database storage.

---

Frequently Asked Questions

When should an engineer use Vertex AI online inference instead of BigQuery batch inference?

Choose Vertex AI online inference when an application requires sub-second, real-time responses to individual HTTP requests. Choose BigQuery batch inference when processing massive amounts of data already stored in the warehouse and latencies of minutes or hours are acceptable.

Why does BigQuery ML require a Cloud resource connection for remote models?

A Cloud resource connection provides a managed service account that securely authenticates BigQuery to external Vertex AI endpoints. This delegation ensures BigQuery queries can invoke foundation models using Identity and Access Management permissions without requiring hardcoded API credentials.

What is the advantage of using BigQuery object tables with multimodal generative models?

BigQuery object tables allow SQL queries to access unstructured files stored in Cloud Storage via metadata and uniform resource identifiers (URIs). This enables remote multimodal models to analyze documents, images, and audio directly without requiring teams to duplicate raw files inside database tables.