Machine Learning on Google BigQuery ML: SQL-First Models at Warehouse Scale

When Google introduced BigQuery ML in 2018, my first reaction was skepticism. SQL for machine learning? It felt like a gimmick—something that would appeal to analysts who wanted to say they did ML without actually learning the craft. After nine years of building production ML systems, I have eaten those words completely. BigQuery ML has become one of the most powerful tools in my arsenal, not because it replaces TensorFlow or PyTorch, but because it solves a fundamentally different problem: making machine learning accessible at the exact point where the data lives, at warehouse scale, with minimal infrastructure overhead.

This is a practitioner’s deep dive into BigQuery ML—how to use it effectively, where it fits in a production ML architecture, and the hard lessons I have learned deploying it for real business problems.

The Core Insight: Data Gravity

The traditional ML workflow has a data gravity problem. Your data lives in a warehouse (BigQuery, Snowflake, Redshift). Your model training happens on a separate compute platform (Vertex AI, SageMaker, a Kubernetes cluster). Between these two worlds lies an expensive, error-prone, and slow data pipeline: extract from the warehouse, transform into training format, load into the training environment, train, then push predictions back.

BigQuery ML eliminates this pipeline entirely. You write a CREATE MODEL statement in SQL, BigQuery pulls the training data directly from its own storage, trains the model on its internal infrastructure, and stores the model alongside your data. Predictions are a ML.PREDICT function call away—no data movement, no separate infrastructure, no serialization headaches.

This is not just convenient—it is architecturally significant. Data gravity means that the closer your compute is to your data, the faster, cheaper, and more reliable your system will be.

SQL Models: What You Can Build

BigQuery ML supports a surprisingly broad range of model types, all created through SQL DDL statements.

Linear and Logistic Regression

CREATE OR REPLACE MODEL `project.dataset.churn_model`
OPTIONS(
    model_type='LOGISTIC_REG',
    input_label_cols=['churned'],
    l2_reg=0.01,
    max_iterations=20,
    learn_rate_strategy='LINE_SEARCH',
    early_stop=true,
    min_rel_progress=0.001,
    data_split_method='AUTO_SPLIT'
) AS
SELECT
    tenure_months,
    monthly_charges,
    total_charges,
    contract_type,
    payment_method,
    senior_citizen,
    partner,
    dependents,
    churned
FROM `project.dataset.training_data`
WHERE split_col = 'TRAIN';

For many business problems—churn prediction, lead scoring, fraud flagging—logistic regression is the right model. It is interpretable, fast to train, and often competitive with more complex approaches when you have good features. BigQuery ML trains these models on the full dataset without any sampling, which matters when your training data is billions of rows.

Boosted Decision Trees (XGBoost)

CREATE OR REPLACE MODEL `project.dataset.revenue_model`
OPTIONS(
    model_type='BOOSTED_TREE_REGRESSOR',
    input_label_cols=['revenue'],
    num_parallel_tree=4,
    max_tree_depth=6,
    subsample=0.8,
    min_tree_child_weight=10,
    colsample_bytree=0.8,
    l2_reg=1.0,
    l1_reg=0.0,
    early_stop=true,
    max_iterations=100,
    learn_rate=0.1
) AS
SELECT * EXCEPT(revenue, split_col)
FROM `project.dataset.training_data`
WHERE split_col = 'TRAIN';

BigQuery ML’s boosted tree implementation is XGBoost under the hood. It handles missing values natively, supports feature importance analysis, and—crucially—can train on datasets that would choke most single-machine XGBoost implementations. I have trained boosted tree models on 500M+ rows in BigQuery ML that would have required distributed XGBoost clusters otherwise.

Deep Neural Networks

CREATE OR REPLACE MODEL `project.dataset.dnn_model`
OPTIONS(
    model_type='DNN_CLASSIFIER',
    input_label_cols=['category'],
    hidden_units=[512, 258, 128],
    dropout=0.3,
    batch_size=1024,
    learn_rate=0.001,
    max_iterations=50,
    early_stop=true,
    activation_fn='RELU'
) AS
SELECT
    embedding_features,
    numeric_features,
    category
FROM `project.dataset.training_data`;

BigQuery ML supports DNN classifiers and regressors, as well as Wide & Deep models for recommendation systems. While they lack the architectural flexibility of frameworks like PyTorch, they cover the vast majority of production use cases where a standard feedforward network is sufficient.

Time Series Forecasting

CREATE OR REPLACE MODEL `project.dataset.sales_forecast`
OPTIONS(
    model_type='ARIMA_PLUS',
    time_series_timestamp_col='date',
    time_series_data_col='daily_sales',
    time_series_id_col='product_id',
    auto_arima=true,
    holiday_region='US',
    clean_spikes_and_dips=true,
    adjust_step_changes=true,
    data_frequency='DAILY'
) AS
SELECT date, product_id, daily_sales
FROM `project.dataset.daily_sales`;

The ARIMA_PLUS model is one of BigQuery ML’s most impressive offerings. It automatically handles seasonality, trend, holiday effects, spike and dip detection, and step change adjustment. For forecasting thousands of time series (one per product, per store, per SKU), it is remarkably effective—often outperforming custom Prophet implementations with far less engineering effort.

I have used this for demand forecasting across 50,000+ SKUs, where building and maintaining individual Prophet or ARIMA models would have been a full-time engineering project.

Matrix Factorization for Recommendations

CREATE OR REPLACE MODEL `project.dataset.recommendation_model`
OPTIONS(
    model_type='MATRIX_FACTORIZATION',
    feedback_type='IMPLICIT',
    num_factors=50,
    regularization_type='l2',
    l2_reg=0.5,
    max_iterations=20
) AS
SELECT user_id, item_id, interaction_count AS rating
FROM `project.dataset.user_interactions`;

Vertex AI Integration: Bridging SQL and Custom ML

BigQuery ML models are not isolated—they integrate deeply with Vertex AI, Google Cloud’s unified ML platform. This integration creates a powerful workflow where BigQuery ML handles data-heavy tasks and Vertex AI handles custom model training.

Exporting to Vertex AI

Models trained in BigQuery ML can be exported to Vertex AI Model Registry:

-- Export model to Vertex AI
EXPORT MODEL `project.dataset.churn_model`
OPTIONS(uri='gs://model-artifacts/churn_model/');

Once in Vertex AI, you get access to endpoint deployment, A/B testing, model monitoring, and the full MLOps toolchain. This means you can prototype and validate a model in BigQuery ML (fast iteration, no infrastructure), then graduate it to a full Vertex AI deployment for production serving.

BigQuery as Feature Store

Even when you train custom models in Vertex AI, BigQuery serves as an excellent feature store:

-- Materialize features for training
CREATE OR REPLACE TABLE `project.dataset.training_features` AS
SELECT
    user_id,
    -- Aggregated features
    COUNTIF(event_type = 'purchase') AS purchase_count_30d,
    SUMIF(event_type = 'purchase', amount) AS total_spend_30d,
    AVGIF(event_type = 'purchase', amount) AS avg_order_value,
    -- Behavioral features
    TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), MAX(event_timestamp), HOUR) AS hours_since_last_event,
    COUNT(DISTINCT DATE(event_timestamp)) AS active_days_30d,
    -- Categorical features
    ANY_VALUE(device_type) AS primary_device,
    ANY_VALUE(country) AS country
FROM `project.dataset.user_events`
WHERE event_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY user_id;

This SQL-based feature engineering runs on BigQuery’s infrastructure—scalable, governed, and accessible to both data engineers and data scientists. The resulting table can be used directly for BigQuery ML training or exported for Vertex AI custom training.

Remote Model Invocation

One of the most powerful recent additions is the ability to invoke Vertex AI endpoints from BigQuery SQL:

-- Invoke a custom Vertex AI model from BigQuery
SELECT *
FROM ML.PREDICT(
    MODEL `project.dataset.vertex_endpoint_model`,
    (SELECT * FROM `project.dataset.new_data`)
);

This means your entire prediction pipeline—from raw data to features to model inference—can be expressed as a single SQL statement, running entirely within BigQuery’s infrastructure.

Feature Engineering at Scale

Feature engineering in BigQuery ML benefits from BigQuery’s massive parallelism. Here are patterns I use repeatedly:

Temporal Features

SELECT
    user_id,
    -- Recency
    TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), MAX(event_time), HOUR) AS hours_since_last,
    -- Frequency
    COUNT(*) AS event_count_7d,
    COUNT(DISTINCT DATE(event_time)) AS active_days_7d,
    -- Trend
    COUNTIF(event_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)) -
    COUNTIF(event_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY)
        AND event_time < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)) AS daily_count_delta,
    -- Monetization
    SUMIF(event_type = 'purchase', amount) AS total_revenue,
    MAXIF(event_type = 'purchase', amount) AS max_order_value
FROM events
WHERE event_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY user_id;

Window-Based Features

SELECT
    user_id,
    event_time,
    amount,
    -- Rolling statistics
    AVG(amount) OVER w7d AS avg_7d,
    STDDEV(amount) OVER w7d AS stddev_7d,
    MAX(amount) OVER w7d AS max_7d,
    -- Rank within cohort
    PERCENT_RANK() OVER (PARTITION BY cohort_id ORDER BY amount) AS amount_percentile,
    -- Sequential patterns
    LAG(amount) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_amount,
    amount - LAG(amount) OVER (PARTITION BY user_id ORDER BY event_time) AS amount_delta
FROM events
WINDOW w7d AS (
    PARTITION BY user_id
    ORDER BY event_time
    RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW
);

Text Feature Extraction

BigQuery ML supports text embedding generation:

SELECT
    text_content,
    ml_generate_embedding_result AS text_embedding
FROM ML.GENERATE_EMBEDDING(
    MODEL `project.dataset.text_embedding_model`,
    (SELECT text_content FROM `project.dataset.documents`)
);

This generates vector embeddings at warehouse scale—millions of documents processed in minutes—without any Python code or GPU infrastructure.

Real-Time Predictions

BigQuery ML supports real-time predictions through several mechanisms:

Batch Predictions

For scheduled scoring (e.g., daily churn scores, weekly recommendations):

-- Daily churn scoring
CREATE OR REPLACE TABLE `project.dataset.daily_churn_scores` AS
SELECT
    user_id,
    predicted_churned AS churn_probability,
    predicted_churned_probs[OFFSET(1)].prob AS churn_prob_value
FROM ML.PREDICT(
    MODEL `project.dataset.churn_model`,
    (SELECT * FROM `project.dataset.current_user_features`)
);

Streaming Predictions

For near-real-time use cases, BigQuery’s streaming insert API combined with scheduled queries provides sub-minute prediction latency:

Application events stream into BigQuery via Pub/Sub → Dataflow → BigQuery streaming insert.
A scheduled query (running every 1-5 minutes) applies the model to new data.
Predictions are written to a serving table that the application queries.

BigQuery BI Engine for Dashboard Latency

When predictions need to serve dashboards with sub-second latency, BigQuery BI Engine provides in-memory caching that brings query response times down to tens of milliseconds.

Production Architecture Patterns

Pattern 1: BigQuery ML as Prototyping Gateway

Prototype in BigQuery ML: Train initial models with SQL, evaluate with built-in metrics, iterate quickly.
Validate: Compare BigQuery ML model against custom models trained on sampled data.
Productionize: If the model is sufficient, deploy via BigQuery ML scheduled queries. If custom architecture is needed, export to Vertex AI.

Pattern 2: Hybrid Training

Feature engineering in BigQuery: Use SQL for data-heavy feature computation.
Training on Vertex AI: Train custom deep learning models using BigQuery as the data source.
Serving from BigQuery: Store predictions in BigQuery tables, serve via BI Engine or Looker.

Pattern 3: Ensemble Scoring

-- Combine BigQuery ML model with external model
SELECT
    user_id,
    0.6 * bq_churn_prob + 0.4 * vertex_churn_prob AS ensemble_churn_prob
FROM (
    SELECT
        a.user_id,
        a.churn_prob AS bq_churn_prob,
        b.churn_prob AS vertex_churn_prob
    FROM ML.PREDICT(MODEL `project.dataset.bq_model`, ...) a
    JOIN ML.PREDICT(MODEL `project.dataset.vertex_model`, ...) b
    USING (user_id)
);

Evaluation and Monitoring

BigQuery ML provides built-in evaluation metrics:

-- Binary classification metrics
SELECT *
FROM ML.EVALUATE(
    MODEL `project.dataset.churn_model`,
    (SELECT * FROM `project.dataset.test_data`)
);

-- Confusion matrix
SELECT *
FROM ML.CONFUSION_MATRIX(
    MODEL `project.dataset.churn_model`,
    (SELECT * FROM `project.dataset.test_data`)
);

-- ROC curve
SELECT *
FROM ML.ROC_CURVE(
    MODEL `project.dataset.churn_model`,
    (SELECT * FROM `project.dataset.test_data`)
);

-- Feature importance
SELECT *
FROM ML.FEATURE_IMPORTANCE(MODEL `project.dataset.churn_model`)
ORDER BY importance_weight DESC;

For ongoing monitoring, I set up scheduled queries that compute evaluation metrics on a rolling window of recent data and alert when performance degrades beyond thresholds.

Cost Considerations

BigQuery ML pricing is based on the amount of data processed during training and prediction. Key cost optimization strategies:

Use data_split_method='AUTO_SPLIT': Avoids manual train/test splitting and its associated data duplication costs.
Leverage clustering and partitioning: Well-organized tables reduce the amount of data scanned.
Batch predictions over streaming: Batch predictions are more cost-effective than streaming predictions for most use cases.
Monitor slot usage: BigQuery ML uses BigQuery slots. In slot-based pricing models, ML workloads can compete with analytical queries for capacity.

Limitations and Honest Assessment

BigQuery ML is not the right tool for every ML problem:

Custom architectures: You cannot define custom neural network architectures, loss functions, or training loops. If you need a transformer with a specialized attention mechanism, use PyTorch/TensorFlow.
Computer vision and NLP: While BigQuery ML supports text embeddings, it is not designed for image classification, object detection, or complex NLP tasks.
Real-time feature computation: BigQuery is batch-oriented. For sub-second feature computation (e.g., real-time fraud scoring during a transaction), you need a dedicated feature store or streaming infrastructure.
Experiment tracking: BigQuery ML lacks built-in experiment tracking. You need to build your own comparison infrastructure or integrate with Vertex AI Experiments.

Conclusion

BigQuery ML occupies a unique and valuable position in the ML ecosystem. It is not trying to replace PyTorch or compete with Vertex AI’s custom training capabilities. Instead, it solves the data problem in machine learning—the friction of moving data between systems, the cost of maintaining separate training infrastructure, and the complexity of keeping features consistent between training and serving.

For the majority of business ML problems—classification, regression, forecasting, recommendations—BigQuery ML provides a production-ready solution with minimal infrastructure overhead. The key to using it effectively is understanding where it fits: as the SQL-native, data-gravity-respecting layer in a broader ML architecture that may also include custom models, real-time feature stores, and specialized serving infrastructure.

After nine years in this field, I have learned that the best ML system is the one that actually gets deployed. BigQuery ML gets deployed, because it eliminates the barriers that prevent good models from reaching production.