Using BigQuery ML for Machine Learning Workflows: A Complete SQL-First Guide

BigQuery ML enables you to build, train, and deploy machine learning models entirely within SQL, eliminating data movement while leveraging serverless Vertex AI integration for predictive and generative workloads.

BigQuery ML (BQ ML) integrates machine learning capabilities directly into Google Cloud's data warehouse, allowing data teams to execute end-to-end ML workflows without extracting data to external notebooks or training pipelines. According to the google/skills repository, this SQL-first approach combines BigQuery's serverless architecture with Vertex AI's managed models to streamline everything from time-series forecasting to large-language-model generation.

BigQuery ML Architecture and Core Concepts

The BigQuery ML architecture is built on a tight integration between BigQuery and Vertex AI, documented in skills/cloud/bigquery-ai-ml/SKILL.md. Unlike traditional ML pipelines that require separate infrastructure for training and inference, BQ ML treats models as first-class entities within the data warehouse. When you create a custom model using CREATE MODEL ... OPTIONS (model_type='linear_reg'), the model is stored as a native BigQuery object and executed on Google’s managed TPU/CPU clusters without provisioning any infrastructure.

Key Capabilities for ML Workflows

In-SQL Model Execution

BigQuery ML provides built-in SQL functions that handle training, inference, and evaluation in single statements. Core functions include:

• AI.FORECAST – Time-series prediction using pre-trained models like TimesFM
• AI.KEY_DRIVERS – Feature importance analysis for custom models
• AI.DETECT_ANOMALIES – Outlier detection in tabular data
• AI.GENERATE – Text generation via Vertex AI LLMs

These functions allow you to run complex ML operations without leaving the SQL environment.

Native Model Hosting

Custom models created with CREATE MODEL statements are persisted as first-class BigQuery entities. The system handles versioning, metadata management, and compute allocation automatically. This native integration means ML models inherit the same IAM permissions and security controls as your datasets and tables.

Vertex AI Remote Models

Through the REMOTE_MODELS reference, you can invoke pre-trained Vertex AI models—including Gemini—directly from SQL queries. This capability, detailed in skills/cloud/bigquery-ai-ml/references/remote_models.md, enables embedding generation and large-language-model inference without managing API endpoints or authentication tokens manually.

Serverless Scaling

BigQuery automatically scales compute resources based on query load, so ML workloads inherit the same elastic performance characteristics as analytical queries. You pay only for the data processed and compute consumed during model training or inference, with no idle cluster costs.

End-to-End Workflow Implementation

A typical BigQuery ML workflow follows five distinct steps that keep all processing inside the data warehouse:

1. Prepare data – Load or query clean time-series or feature tables directly in BigQuery.

2. Choose a function – Select the appropriate AI function (e.g., AI.FORECAST for demand prediction).

3. Run the function – Execute a single SQL statement that trains (if necessary) and returns predictions.

4. Consume results – Materialize predictions into tables, feed them to downstream models, or visualize them in BI tools like Looker.

5. Iterate – Adjust hyperparameters via function options (model, horizon, confidence_level) and rerun queries without modifying infrastructure.

Practical BigQuery ML Code Examples

Time-Series Forecasting with AI.FORECAST

The AI.FORECAST function, documented in skills/cloud/bigquery-ai-ml/references/ai_forecast.md, leverages the TimesFM foundation model for predictive analytics:

WITH
  citibike_trips AS (
    SELECT
      EXTRACT(DATE FROM starttime) AS date,
      usertype,
      COUNT(*) AS num_trips
    FROM `bigquery-public-data.new_york.citibike_trips`
    GROUP BY date, usertype
  )
SELECT *
FROM AI.FORECAST(
  TABLE citibike_trips,
  data_col => 'num_trips',
  timestamp_col => 'date',
  id_cols => ['usertype'],
  horizon => 30,
  output_historical_time_series => TRUE);

This query forecasts 30 days of trip volume by user type while outputting the historical context for validation.

Feature Importance with AI.KEY_DRIVERS

Identify influential features in your custom models using the AI.KEY_DRIVERS function:

CREATE OR REPLACE MODEL `my_dataset.sales_model`
OPTIONS (model_type='linear_reg');

SELECT *
FROM AI.KEY_DRIVERS(
  MODEL `my_dataset.sales_model`,
  input_data => (SELECT * FROM `my_dataset.sales_features`),
  target_col => 'sales',
  feature_cols => ['ad_spend', 'price', 'season']);

This analysis quantifies how advertising spend, pricing, and seasonality drive sales outcomes.

Generative AI with AI.GENERATE

Access Gemini models for text generation without leaving SQL:

SELECT *
FROM AI.GENERATE(
  model => 'gemini-1.5-pro',
  prompt => 'Summarize the quarterly sales performance for the East region.',
  max_output_tokens => 200);

This pattern connects to Vertex AI remote models for natural language processing tasks.

Essential Resources in the google/skills Repository

The google/skills repository provides comprehensive documentation for implementing these workflows:

• skills/cloud/bigquery-ai-ml/SKILL.md – Central catalog of AI functions, usage patterns, and prerequisites for ML workflows.

• skills/cloud/bigquery-ai-ml/references/ai_forecast.md – Complete syntax reference, argument specifications, and advanced examples for time-series forecasting.

• skills/cloud/bigquery-ai-ml/references/remote_models.md – Guidance for configuring and invoking Vertex AI remote models (including Gemini) from SQL.

• skills/cloud/bigquery-basics/SKILL.md – Foundation documentation covering dataset creation, table management, and client-library integration required for ML data preparation.

• skills/cloud/bigquery-basics/references/core-concepts.md – Underlying architecture details about BigQuery storage types and analytical workflow fundamentals.

Summary

• SQL-native execution eliminates ETL complexity by running training and inference directly where data resides.
• Vertex AI integration provides access to both custom models and pre-trained LLMs through standardized SQL functions.
• Serverless architecture automatically scales compute resources for ML workloads without cluster management.
• Comprehensive function library supports predictive analytics (AI.FORECAST, AI.KEY_DRIVERS) and generative AI (AI.GENERATE) use cases.
• Unified security model applies dataset-level IAM controls to ML models and predictions.

Frequently Asked Questions

What types of machine learning tasks can I perform with BigQuery ML?

BigQuery ML supports both predictive and generative workloads. You can perform time-series forecasting with AI.FORECAST, anomaly detection with AI.DETECT_ANOMALIES, feature analysis with AI.KEY_DRIVERS, and text generation with AI.GENERATE. Custom models support traditional regression and classification tasks through the CREATE MODEL syntax.

Do I need to export data from BigQuery to train ML models?

No. BigQuery ML executes entirely within the data warehouse, eliminating the need to extract data to external notebooks or training pipelines. Models train and run inference on BigQuery's managed compute infrastructure while accessing data directly from your datasets.

How do I access Vertex AI models like Gemini from BigQuery SQL?

You can access Vertex AI models using the REMOTE_MODELS reference or dedicated functions like AI.GENERATE. As documented in skills/cloud/bigquery-ai-ml/references/remote_models.md, these interfaces allow you to call pre-trained models including Gemini for embedding generation and text completion without managing API endpoints.

What infrastructure do I need to provision for BigQuery ML workloads?

You do not need to provision any infrastructure. BigQuery ML runs on Google's managed TPU and CPU clusters, automatically scaling compute resources based on query complexity and data volume. This serverless model means you only pay for the processing time consumed by your ML queries.

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