Favorita: BQML Models in dbt

Train, predict, evaluate, and explain a forecasting model entirely inside BigQuery using BQML — orchestrated as dbt models. No separate ML service, no data leaving the warehouse. The fastest path from clean tables to predictions.

Primary Outcome

A forecasting model trained, scored, and explained inside BigQuery, managed as version-controlled dbt models.

Solution

BigQuery ML models orchestrated as dbt models, so training, prediction, evaluation, and explanation all run inside the warehouse under version control.

Deliverables

  • A bqml_model_train model that creates the BQML model from feature tables

  • A bqml_model_predict model that scores new data

  • A bqml_model_evaluate model that reports forecasting metrics

  • A bqml_model_explain model for feature attribution

  • Macros that assemble feature and prediction columns

Strategic Context

BQML is the most underrated forecasting tool for warehouse-native teams. It trades some flexibility for a massive drop in operational overhead — no serving infra, no data movement, no separate pipeline. For a first model, that trade is almost always worth it.

Technical Architecture

BQML is the most underrated forecasting tool for warehouse-native teams. It trades some flexibility for a massive drop in operational overhead — no serving infra, no data movement, no separate pipeline. For a first model, that trade is almost always worth it.

Problem Statement

Standing up a separate ML service for a first forecasting model is heavy — serving infra, data movement, and a second pipeline to maintain — when the data already lives in the warehouse.

Links

What's Included

The dbt/models/marts/ml_models directory: bqml_model_train, _predict, _evaluate, and _explain models, plus the get_bqml_feature_columns and get_bqml_prediction_features macros that build the feature lists.

FAQs

When should I use BQML vs Vertex?

BQML for a fast, low-ops warehouse-native baseline; Vertex when you need custom models, richer features, or managed serving.

Is the model version-controlled?

The SQL that creates and scores it is. The trained model object lives in BigQuery, recreated on each train run.

Tech Stack

Tool 1

Tool 4

Tool 4

Tool 3

Tool 2

Tool 4

Primary Outcome

A forecasting model trained, scored, and explained inside BigQuery, managed as version-controlled dbt models.

Problem Statement

Standing up a separate ML service for a first forecasting model is heavy — serving infra, data movement, and a second pipeline to maintain — when the data already lives in the warehouse.

Solution

BigQuery ML models orchestrated as dbt models, so training, prediction, evaluation, and explanation all run inside the warehouse under version control.

Links

Deliverables

  • A bqml_model_train model that creates the BQML model from feature tables

  • A bqml_model_predict model that scores new data

  • A bqml_model_evaluate model that reports forecasting metrics

  • A bqml_model_explain model for feature attribution

  • Macros that assemble feature and prediction columns

What's Included

The dbt/models/marts/ml_models directory: bqml_model_train, _predict, _evaluate, and _explain models, plus the get_bqml_feature_columns and get_bqml_prediction_features macros that build the feature lists.

Strategic Context

BQML is the most underrated forecasting tool for warehouse-native teams. It trades some flexibility for a massive drop in operational overhead — no serving infra, no data movement, no separate pipeline. For a first model, that trade is almost always worth it.

FAQs

When should I use BQML vs Vertex?

BQML for a fast, low-ops warehouse-native baseline; Vertex when you need custom models, richer features, or managed serving.

Is the model version-controlled?

The SQL that creates and scores it is. The trained model object lives in BigQuery, recreated on each train run.

Technical Architecture

BQML is the most underrated forecasting tool for warehouse-native teams. It trades some flexibility for a massive drop in operational overhead — no serving infra, no data movement, no separate pipeline. For a first model, that trade is almost always worth it.

Tech Stack

Tool 1

Tool 4

Tool 4

Tool 3

Tool 2

Tool 4