BigQuery AI and ML Workflows: End-to-End Model Training and Serving Without Data Movement

Google BigQuery enables you to build, train, evaluate, and serve machine learning models entirely within the data warehouse using BigFrames, BQ ML, and optional Vertex AI integration—eliminating data export and reducing pipeline complexity.

The google/skills repository documents production-ready patterns for BigQuery AI and ML workflows that keep computation serverless and scalable. These skills demonstrate how to leverage BigFrames (a pandas-compatible API), BigQuery ML (native SQL extensions), and BigFrames ML (Python wrappers with scikit-learn-like interfaces) to execute complete ML pipelines without moving data out of BigQuery.

BigFrames: Python-First Data Preparation at Scale

BigFrames provides a familiar pandas-style interface that lazily evaluates DataFrame operations and translates them into BigQuery SQL. This approach eliminates memory constraints on local machines and minimizes data transfer costs.

Enabling Partial Ordering for Performance

In skills/cloud/bigquery-bigframes/SKILL.md, the repository recommends enabling partial ordering to optimize execution plans for large-scale transformations:

import bigframes.pandas as bpd

# Enable partial ordering for faster execution

bpd.options.bigquery.ordering_mode = 'partial'

# Load a public dataset lazily—no data is fetched yet

df = bpd.read_gbq("bigquery-public-data.ml_datasets.penguins")

# Preview first 5 rows without materializing the entire table

df.peek(5)

The peek() method is particularly valuable for BigQuery AI and ML workflows because it allows iterative data exploration without triggering full table scans. This pattern appears throughout the BigFrames skill documentation as a best practice for cost-conscious development.

BigQuery ML (BQ ML): Native SQL Model Training

BigQuery ML exposes ML operations through SQL extensions including CREATE MODEL, ML.PREDICT, ML.EVALUATE, and ML.EXPLAIN_PREDICT. According to the source code analysis in skills/cloud/cloud-logging-query-generation/references/query_bigquery.md, these operations generate distinct audit log events that you can monitor through Cloud Logging.

Direct SQL Model Creation

For teams preferring SQL-first workflows, BQ ML supports multiple model types including linear regression, logistic regression, k-means clustering, matrix factorization, and TensorFlow models:

CREATE OR REPLACE MODEL `my_project.my_dataset.linear_model`
OPTIONS(model_type='linear_reg') AS
SELECT
  feature1,
  feature2,
  target
FROM `my_project.my_dataset.training_table`;

The model artifact persists directly in BigQuery as a dataset object, enabling immediate prediction via ML.PREDICT without additional infrastructure.

BigFrames ML: Scikit-Learn Compatibility with BigQuery Scaling

BigFrames ML (bigframes.bigquery.ml) bridges Python-centric development with BigQuery's distributed execution engine. This module exposes estimators that mimic scikit-learn APIs while compiling to BQ ML operations under the hood.

Training Logistic Regression with BigFrames ML

The reference notebook at skills/cloud/bigquery-bigframes/references/logistic_regression.md demonstrates the complete workflow:

import bigframes.pandas as bpd
from bigframes.bigquery.ml import LogisticRegression

# Load and split data—all operations remain lazy until .fit() or .evaluate()

df = bpd.read_gbq("my_project.my_dataset.my_table")
train, test = df.random_split([0.8, 0.2], seed=42)

# Define estimator—parameters map to BQ ML OPTIONS

model = LogisticRegression(label_col="target")

# Training executes in BigQuery's distributed engine

model.fit(train)

# Evaluation returns metrics as a BigFrames DataFrame

metrics = model.evaluate(test)
print(metrics)

# Persist model to BigQuery dataset for production use

model.to_gbq()

The linear_regression.md reference provides an analogous pattern for regression tasks. Both workflows emphasize that no data leaves BigQuery during training—the fit() call generates and executes SQL, and to_gbq() registers the resulting model artifact.

Vertex AI Integration for Advanced Serving

While BigQuery ML supports direct batch prediction via SQL, some BigQuery AI and ML workflows require low-latency online serving or custom deployment configurations. The google/skills repository includes patterns for forwarding models to Vertex AI.

In skills/cloud/agent-platform-inference/scripts/gemini_vertexai_sdk.py, the skill demonstrates how to invoke Vertex AI SDKs from pipeline scripts. This enables:

• Online prediction endpoints for real-time inference
• Custom container deployment for models requiring specialized preprocessing
• Hyperparameter tuning through Vertex AI Hyperparameter Tuning service
• Model monitoring with drift detection and explainability features

The bridge from BigQuery ML to Vertex AI typically involves exporting the BQ ML model (for supported types) or using Vertex AI's BigQuery connector for feature serving.

Observability and Cost Monitoring

Production BigQuery AI and ML workflows require visibility into job execution, quota consumption, and cost attribution. The skills/cloud/cloud-logging-query-generation/SKILL.md module provides pre-built queries for this purpose.

Querying BigQuery ML Audit Logs

For tracking model training jobs and predictions, use this pattern from skills/cloud/cloud-logging-query-generation/references/query_bigquery.md:

SELECT
  timestamp,
  protoPayload.methodName,
  jsonPayload.jobId,
  jsonPayload.jobState
FROM `bigquery_project`
WHERE resource.type = "bigquery_job"
  AND protoPayload.methodName = "google.cloud.bigquery.v2.JobService.InsertJob";

Additional queries in the same file cover dataset modifications, table access patterns, and ML-specific job metadata. These logs are essential for debugging failed training jobs, optimizing slot allocation, and enforcing data governance policies.

End-to-End Workflow Summary

A complete BigQuery AI and ML workflow in the google/skills repository follows this sequence:

1. Ingest — Load data via streaming, Cloud Storage, or Data Transfer Service
2. Explore — Use BigFrames with peek() and partial ordering for iterative analysis
3. Prepare — Apply transformations using pandas-compatible methods (all lazy-evaluated)
4. Train — Choose BigFrames ML estimators or BQ ML SQL for model creation
5. Evaluate — Generate metrics with ML.EVALUATE or estimator.evaluate()
6. Persist — Store models via model.to_gbq() or CREATE MODEL persistence
7. Serve — Query predictions with ML.PREDICT or export to Vertex AI
8. Monitor — Apply Cloud Logging queries for operational visibility

Summary

• BigFrames (bigquery-bigframes/SKILL.md) enables pandas-style data manipulation that compiles to BigQuery SQL, with partial ordering optimization for performance.
• BigFrames ML (bigframes.bigquery.ml) provides scikit-learn-compatible estimators that execute as BQ ML jobs, preserving serverless scaling while maintaining Python ergonomics.
• BQ ML SQL offers direct model creation and prediction for SQL-native teams, with full audit logging support documented in cloud-logging-query-generation/references/query_bigquery.md.
• Vertex AI integration (via agent-platform-inference/scripts/gemini_vertexai_sdk.py) extends BigQuery-trained models to online serving and advanced MLOps features.
• Cloud Logging queries enable cost tracking, job debugging, and compliance auditing for all ML operations in BigQuery.

Frequently Asked Questions

What is the difference between BigFrames and BigFrames ML?

BigFrames is a general-purpose DataFrame API for data manipulation, while BigFrames ML is a specialized submodule (bigframes.bigquery.ml) that wraps BigQuery ML operations in scikit-learn-style estimators. You use BigFrames for feature engineering and exploration, and BigFrames ML for model training and evaluation—both keep execution inside BigQuery.

Can I use BigQuery ML without knowing SQL?

Yes. BigFrames ML allows you to write Python code exclusively. The LogisticRegression, LinearRegression, and other estimators compile to CREATE MODEL statements automatically. However, understanding the generated SQL helps with debugging and cost optimization.

How do I monitor costs for BigQuery ML training jobs?

Use the Cloud Logging queries in skills/cloud/cloud-logging-query-generation/SKILL.md to extract job metadata including bytes processed and slot milliseconds. Filter protoPayload.methodName for jobservice.insertjob events and join with BigQuery's INFORMATION_SCHEMA.JOBS_BY_PROJECT for detailed cost attribution.

When should I export a BigQuery ML model to Vertex AI?

Export to Vertex AI when you need sub-second latency for online predictions, custom preprocessing logic, or integration with Vertex AI Pipelines for MLOps automation. For batch scoring and analytical use cases, ML.PREDICT in BigQuery remains simpler and more cost-effective.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}