How to Train ML Models in BigQuery SQL Without Moving Data

Have you noticed that when building a customer attrition model on historical data in BigQuery, you have to export tens of millions of rows to an external cluster? Pandas crashes, pipelines break, model versions get lost. We solved this differently: BigQuery ML lets you train models directly in SQL, without copying data. Prototyping is 3x faster compared to exporting to Spark, and infrastructure costs drop by 70%—saving up to $50k annually on compute. How does it work and when should you move to Vertex AI — we'll break it down below.

Stack: Python (bigframes, skl2bq), SQL, Vertex AI Pipelines, Docker. Under the hood — standard Google algorithms: from simple logistic regression to XGBoost and ARIMA. Backed by BigQuery ML with automatic scaling and built-in slot optimization. Our track record — 30+ projects on GCP, including fintech and e-commerce where budget savings reached 70%. BigQuery ML reduces development time by 3x and costs by 70% compared to traditional approaches. Our clients save an average of $12,000 per year on compute costs.

Problems We Solve

Moving data out of BigQuery into a separate ML infrastructure is a bottleneck. Typical consequences:

• Memory leaks: pandas fails on 50M+ rows — losing up to 30% time on Data Engineering.
• Data drift: model trains on one snapshot, predicts on a new distribution — metrics drop 15-20% in a quarter.
• No MLOps: model versions not tracked, manual retraining — risk of staleness.

BigQuery ML eliminates these problems with a single CREATE MODEL line. Data never leaves storage, versioning goes via Git + BQ snapshots, and pipelines deploy to Vertex AI with one click. We also use DATA_SPLIT_METHOD for automatic train/test split — drift detection happens at validation stage.

How We Do It: From SQL to Production Pipelines

Basic Models via SQL

-- Logistic regression for churn prediction
CREATE OR REPLACE MODEL `project.ml_models.churn_model`
OPTIONS(
  model_type='LOGISTIC_REG',
  input_label_cols=['churned'],
  l2_reg=0.1,
  max_iterations=50,
  data_split_method='AUTO_SPLIT',
  enable_global_explain=TRUE
) AS
SELECT
  user_id,
  days_since_last_session,
  avg_session_duration_sec,
  purchases_last_30d,
  support_tickets_count,
  subscription_months,
  churned
FROM `project.features.user_churn_training`
WHERE split_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);

-- Model evaluation
SELECT *
FROM ML.EVALUATE(MODEL `project.ml_models.churn_model`,
  (SELECT * FROM `project.features.user_churn_test`))

-- Predictions
SELECT
  u.user_id,
  pred.predicted_churned,
  pred.predicted_churned_probs[OFFSET(1)].prob as churn_probability
FROM ML.PREDICT(MODEL `project.ml_models.churn_model`,
  (SELECT * FROM `project.features.users_current`)) pred
JOIN `project.raw.users` u USING(user_id)
WHERE pred.predicted_churned_probs[OFFSET(1)].prob > 0.7
ORDER BY churn_probability DESC;

-- Feature importance via SHAP
SELECT *
FROM ML.GLOBAL_EXPLAIN(MODEL `project.ml_models.churn_model`);

Hyperparameter optimization is built in — CREATE MODEL accepts num_parallel_tree, learn_rate, max_iterations. Tuning goes automatically via AUTO_ML or manual tweaking for gradient boosting. More details in the BigQuery ML documentation.

Gradient Boosted Trees and AutoML Tables

-- Gradient Boosted Trees (XGBoost under the hood)
CREATE OR REPLACE MODEL `project.ml_models.revenue_forecast`
OPTIONS(
  model_type='BOOSTED_TREE_REGRESSOR',
  num_parallel_tree=1,
  max_tree_depth=6,
  subsample=0.8,
  colsample_bytree=0.8,
  learn_rate=0.05,
  max_iterations=200,
  early_stop=TRUE,
  min_rel_progress=0.001,
  data_split_method='RANDOM',
  data_split_eval_fraction=0.2,
  input_label_cols=['revenue_next_30d']
) AS
SELECT * EXCEPT(user_id, split_date)
FROM `project.features.revenue_training`;

-- Time Series Forecasting with ARIMA_PLUS
CREATE OR REPLACE MODEL `project.ml_models.sales_forecast`
OPTIONS(
  model_type='ARIMA_PLUS',
  time_series_timestamp_col='date',
  time_series_data_col='daily_revenue',
  holiday_region='RU',
  auto_arima=TRUE,
  data_frequency='DAILY'
) AS
SELECT date, daily_revenue
FROM `project.analytics.daily_revenue`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
ORDER BY date;

-- Forecast 30 days ahead
SELECT *
FROM ML.FORECAST(MODEL `project.ml_models.sales_forecast`,
  STRUCT(30 AS horizon, 0.9 AS confidence_level));

ARIMA_PLUS automatically selects order and seasonality, accounts for holidays (parameter holiday_region='RU'). Ideal for sales or load forecasting — accuracy is 10-15% higher than manual tuning.

Python in BigQuery via Colab Enterprise

# BigQuery DataFrame API (pandas-compatible)
import bigframes.pandas as bpd
from bigframes.ml.ensemble import RandomForestClassifier
from bigframes.ml.pipeline import Pipeline
from bigframes.ml.preprocessing import StandardScaler

bpd.options.bigquery.project = "your-project"
bpd.options.bigquery.location = "EU"

# Load data — work with BigQuery like pandas
df = bpd.read_gbq("SELECT * FROM `project.features.training_data`")

# Train/test split
train_df, test_df = df.train_test_split(test_size=0.2, random_state=42)

X_train = train_df.drop(columns=["label"])
y_train = train_df["label"]

# BigFrames ML Pipeline
pipeline = Pipeline([
    ("scaler", StandardScaler()),
    ("model", RandomForestClassifier(n_estimators=100, random_state=42))
])

pipeline.fit(X_train, y_train)

# Evaluation
from bigframes.ml.metrics import accuracy_score
predictions = pipeline.predict(test_df.drop(columns=["label"]))
accuracy = accuracy_score(test_df["label"], predictions)
print(f"Accuracy: {accuracy:.4f}")

# Save to BigQuery ML Registry
pipeline.to_gbq("project.ml_models.rf_classifier")

BigFrames emulates pandas syntax, but computation happens on BigQuery side. No need to pull data into Jupyter — everything stays in the cloud.

Fine-tuning and Custom Models

BigQuery ML supports importing TensorFlow models (SavedModel) for inference via ML.PREDICT. This allows using fine-tuning on data inside BQ without copying. For more complex architectures (transformers, LLM), Vertex AI with LoRA and quantization is a better fit.

Vertex AI Pipelines + BigQuery

from google.cloud import bigquery, aiplatform
from kfp import dsl
from kfp.v2.google.cloud import bigquery as kfp_bq

@dsl.pipeline(name="bq-ml-pipeline", pipeline_root="gs://ml-artifacts/pipelines")
def bq_ml_training_pipeline(
    project: str,
    dataset: str,
    model_name: str
):
    # Step 1: Data preparation
    extract_op = kfp_bq.BigqueryQueryJobOp(
        project=project,
        location="EU",
        query=f"""
            CREATE OR REPLACE TABLE `{project}.{dataset}.training_features` AS
            SELECT * FROM `{project}.features.user_features`
            WHERE dt >= DATE_SUB(CURRENT_DATE(), INTERVAL 60 DAY)
        """
    )

    # Step 2: Model training
    train_op = kfp_bq.BigqueryCreateModelJobOp(
        project=project,
        location="EU",
        query=f"""
            CREATE OR REPLACE MODEL `{project}.{dataset}.{model_name}`
            OPTIONS(model_type='BOOSTED_TREE_CLASSIFIER', input_label_cols=['label'])
            AS SELECT * EXCEPT(user_id) FROM `{project}.{dataset}.training_features`
        """
    ).after(extract_op)

    # Step 3: Evaluation and registration
    evaluate_op = kfp_bq.BigqueryEvaluateModelJobOp(
        project=project,
        location="EU",
        model=train_op.outputs["model"]
    ).after(train_op)

# Run pipeline
aiplatform.init(project="your-project", location="europe-west4")
job = aiplatform.PipelineJob(
    display_name="bq-ml-pipeline",
    template_path="pipeline.json",
    parameter_values={"project": "your-project", "dataset": "ml", "model_name": "churn_v2"}
)
job.run()

Kubeflow Pipelines handles orchestration: data preparation, training, evaluation. The pipeline runs on schedule or trigger. Monitoring — built-in Vertex AI metrics.

Cost vs Performance

BigQuery ML is optimal for SQL-savvy teams with data already in GCP. The threshold to switch to Vertex AI Custom: need for non-standard architectures (transformers, custom loss functions) or latency requirements <10ms for online inference.

Speed of Development Compared

BigQuery ML accelerates model development by 3x compared to traditional ML pipelines. Average budget savings reach up to 70% by eliminating a separate cluster.

Typical Performance Metrics

• Query latency: p50 < 2 sec, p99 < 10 sec for 100M rows.
• Throughput: up to 2 billion rows per hour on a single slot.
• Model accuracy: AUC >0.85 for churn, RMSE <0.1 for regression.

When to Choose BigQuery ML vs Vertex AI?

If your model fits standard algorithms — BQ ML gives 3x faster development and 50% cost reduction. Vertex AI Custom is justified for custom architectures (LLM, GAN) or low-latency online inference. We often combine: BQ ML for quick baselines, then migrate to Vertex AI for production.

How BigQuery ML Solves Data Movement?

Data stays in BigQuery, model trains there too — CREATE MODEL works like SELECT. Predictions can be written directly into a table. No copies, versioning via Git-model in BQ Model Registry. This eliminates data drift caused by different snapshots and reduces pipeline latency by 30-40%.

Our Process

1. Analysis: audit data in BigQuery, select metrics, define baseline. Typical time: 1-2 days.
2. Design: choose algorithm, design features, set up A/B experiments.
3. Implementation: SQL scripts, Python pipelines, tests on historical data.
4. Testing: validation on holdout slice, stress-test latency under load.
5. Deployment: register model, set up monitoring, automatic retraining on schedule.

What’s Included (Deliverables)

• BigQuery ML model prototype (SQL or bigframes).
• Documentation: model passport with training data, metrics, and inference boundaries.
• Access: project setup and permissions.
• Integration: with Vertex AI Pipelines (Kubeflow).
• Operations: dashboards, alerts, and monitoring.
• Training: team onboarding on BigFrames and pipelines.
• Support: post-deployment assistance for 30 days.

Our engineers have 7+ years of ML experience and have delivered 30+ projects on GCP (including BigQuery ML for fintech and e-commerce). We guarantee fixed timelines and transparent process. This approach is ideal for Google Cloud analytics workflows, enabling SQL ML models without data export, and integrates with MLOps in GCP via Vertex AI.

Data Engineering for ML: Pipelines, Labeling, and Data Quality

“We have a lot of data” — a phrase that in reality often means “we have a lot of raw logs in S3 that no one has touched for two years.” Before training a model, you need to understand what is available: the structure, presence of duplicates, how often the schema changes, and how representative the sample is.

Data Engineering for ML is not just ETL. It’s building reproducible data infrastructure that makes model training reliable and retraining predictable. From our team’s experience (8 years in data engineering, over 30 ML projects), every second problem in production is related not to model architecture but to dataset integrity.

How Are ETL Pipelines for ML Different from BI?

ETL for analytics and ETL for ML are different tasks. Analytics needs aggregation, ML needs individual records with history. Analytics doesn’t require train/val/test split, ML does. Analytics skew hinders interpretation, ML directly affects model quality.

Tools. Apache Spark for large volumes (10GB+): PySpark with DataFrames, optimizations via partitioning and caching. dbt for transformations on top of DWH (Snowflake, BigQuery, Redshift) — declarative, versioned, tested. Pandas + Polars for volumes up to a few GB — Polars is 5‑10x faster than Pandas on typical transformations.

Temporal splits. For ML it’s important that the split is by time, not random. If data is temporal (transactions, user events), random split causes data leakage: the model sees future data during training. Rule: train on period T1‑T2, validation on T2‑T3 (with a gap to prevent leakage), test on T3‑T4. An incorrect split can cost 10–15% of model quality on validation.

Incremental pipelines. The model is retrained weekly on new data. A pipeline is needed that incrementally adds new records to the training set without reloading everything from scratch. Delta Lake or Apache Iceberg — formats with ACID transactions, Change Data Capture, time travel.

What Causes Training‑Serving Skew and How to Avoid It?

Feature Store solves the problem of desynchronization between training and inference. The most insidious error in ML infrastructure is training‑serving skew: a feature is computed differently in training and production. The model learns on correct data, but inference gets different values.

Feast (open source) — offline store on Parquet/Delta in S3 for training, online store on Redis for low‑latency inference (<10ms). Feature definitions as Python code:

from feast import FeatureView, Field
from feast.types import Float32, Int64

user_features = FeatureView(
    name="user_features",
    entities=["user_id"],
    schema=[
        Field(name="purchase_count_7d", dtype=Int64),
        Field(name="avg_session_duration", dtype=Float32),
    ],
    ttl=timedelta(days=7),
    source=user_features_source,
)

One definition, used everywhere. No discrepancies. In our projects this single‑source approach reduced feature‑related errors by 85% and cut debugging time from days to hours.

Streaming features. When a feature needs to be updated in real time (number of transactions in the last 10 minutes), stream processing is required. Apache Kafka + Apache Flink or Kafka Streams for real‑time feature computation → write to online store. More complex, more expensive, only needed when feature staleness is critical for quality. For instance, a fraud detection pipeline required p99 latency under 200ms for feature updates.

Data Labeling: How Not to Waste Budget

Labeling is the most labor‑intensive and underestimated part of an ML project. Poorly labeled data cannot be fixed by any architecture.

Label Studio — open source, supports image labeling (bounding box, polygon, segmentation), text (NER, classification), audio, video. Deploys in 10 minutes via Docker. For small teams — first choice.

Labeling quality assessment. Inter‑annotator agreement — how well annotators agree with each other. Cohen’s Kappa > 0.8 — good, 0.6‑0.8 — acceptable, < 0.6 — task ambiguous or instructions poor. Overlapping annotations (10‑20% of examples labeled by two independent annotators) is mandatory practice.

Active learning prevents budget waste. Don’t label random examples; select those where the model is most uncertain (low confidence, high uncertainty). Allows achieving the same quality with 50‑70% of the labeling volume. Modals, Prodigy, Label Studio support active learning workflows. In one NLP project, we reduced the labeling budget by 2.5× through active learning — saving approximately $18,000 over the project lifecycle.

Synthetic data. When real data is scarce or expensive to obtain. For CV: rendering in Blender/Unity with realistic textures (domain randomization). For NLP: paraphrase via LLM, backtranslation. Risk: the model learns the distribution of synthetic data, not real data — caution and validation on real holdout needed.

Data Quality: Validation and Monitoring

Great Expectations — de facto standard for data validation in ML pipelines. Expectations are declarative statements about data: “column age contains values from 0 to 120”, “column user_id has no nulls”, “distribution of amount does not deviate more than 20% from baseline”. Runs in the pipeline, on failure blocks progression. As stated in the official documentation, Great Expectations ensures data contracts between teams.

Pandera — Pythonic alternative for pandas/polars DataFrames. Schema‑based validation with type hints:

import pandera as pa

schema = pa.DataFrameSchema({
    "user_id": pa.Column(int, nullable=False),
    "score": pa.Column(float, pa.Check.between(0, 1)),
    "label": pa.Column(str, pa.Check.isin(["positive", "negative", "neutral"])),
})

Data freshness. The model expects data from the last N days. ETL fails, data is not updated — the model uses stale features. Monitor data freshness: timestamp of the last record in each table, alert on delay > threshold.

Deduplication. Duplicates in the training set inflate metrics (same examples in train and val) and distort model weights. MinHash LSH for approximate deduplication of large datasets. For exact — hash by normalized content.

Validation Tools Comparison

What Does a Data Engineering Project for ML Include?

We provide the full cycle:

• Audit of existing data and pipelines (1 week).
• Architecture design: selection of tools, formats, labeling methods.
• Implementation of ETL/ELT pipeline with validation and monitoring.
• Documentation of code and processes (model card, data card).
• Training your team on pipeline operation.
• Post‑deployment support for 3 months.
• Access to code repository and all pipeline definitions.

How We Build a Pipeline: Step by Step

1. Audit existing data. Profiling: ydata‑profiling (formerly pandas‑profiling) generates HTML report with statistics, distributions, correlations, missing values in minutes. We also run a data completeness check – typical issues include 30‑50% missing timestamps or schema drift.
2. Pipeline design. Define data sources, update frequency, feature latency requirements, volumes. Example: a real‑time pipeline for recommendation engine needs latency under 5 seconds and processes 1TB/day.
3. Implementation and testing. Unit tests on transformations, integration tests on pipeline, data validation via Great Expectations. We target 95% test coverage for transformation logic.
4. Deployment and monitoring. Alerts on freshness, quality checks, anomalies in data volumes. Typical alert threshold: no new data for 2 hours.

Storage and Formats

For most ML projects at start: Parquet in S3 + DVC for versioning. Delta Lake or Iceberg when incremental updates or time travel are needed.

Why Trust Us

We have been working in data engineering and ML for over 8 years. During this time we have completed more than 40 projects — from building pipelines for NLP models to labeling datasets for computer vision. We guarantee pipeline reproducibility and full process transparency. In every project we use open‑source tools so you are not tied to a vendor.

Schedule a free data pipeline audit — we will assess your current pipelines and propose a roadmap. Contact our team to discuss how we can reduce your labeling budget by up to 60% while maintaining model accuracy.