Common Pitfalls When Training BigQuery ML Models: 10 Critical Errors to Avoid

The most frequent mistakes when training BigQuery ML models involve data leakage from target columns, mismatched data types, unhandled NULL values, and reliance on default hyperparameters, all of which degrade model performance and inflate costs unless addressed using the defensive SQL patterns demonstrated in the DataTalksClub/data-engineering-zoomcamp repository.

BigQuery ML streamlines machine learning by allowing CREATE MODEL statements directly inside SQL, yet this convenience often conceals classic modeling errors that can invalidate results or generate unexpected query charges. This article analyzes the ten most common pitfalls found in the 03-data-warehouse/big_query_ml.sql script from the DataTalksClub/data-engineering-zoomcamp repository, providing specific line references and mitigation strategies to ensure robust, cost-effective model training.

Data Leakage and Target Column Contamination

Data leakage occurs when the target label or future information contaminates the feature set, allowing the model to "cheat" during training. In 03-data-warehouse/big_query_ml.sql, the script explicitly isolates the label by specifying input_label_cols=['tip_amount'] at lines 22-27 while ensuring this column is excluded from the feature SELECT statement.

Always audit your feature queries to confirm the label column is not included in SELECT * operations. When preparing training data, explicitly list feature columns rather than using wildcards to prevent accidental leakage.

Mismatched Data Types and Schema Errors

BigQuery ML expects specific data formats for different algorithms; feeding STRING values into numerical models or casting errors can cause silent failures. The repository demonstrates defensive casting at lines 16-18, converting categorical IDs like PULocationID to STRING types to prevent unintended numeric treatment.

Validate your schema using INFORMATION_SCHEMA.COLUMNS before model creation. Explicitly cast categorical variables to STRING and ensure numerical features are not accidentally stored as text, which forces the optimizer to treat them as high-cardinality categorical data.

Unhandled NULL Values and Missing Data

Many BigQuery ML algorithms ignore NULL rows entirely, silently shrinking your effective training set and introducing bias. The workflow in big_query_ml.sql filters rows where tip_amount IS NOT NULL before training at lines 32-33, 44-49, and 58-63.

Always inspect your data for missing values before calling CREATE MODEL. Either filter out incomplete rows with explicit WHERE clauses or impute missing values using IFNULL() functions to maintain dataset integrity.

Train/Test Split Imbalances

Relying on AUTO_SPLIT can produce skewed distributions, particularly with rare classes or temporal outliers. While the example uses DATA_SPLIT_METHOD='AUTO_SPLIT' at lines 24-26, production workflows should consider RANDOM splits with explicit train_fraction parameters to ensure representative sampling.

For time-series data, avoid random splits that leak future information; instead, use SEQ or custom WHERE clauses to enforce temporal boundaries between training and evaluation sets.

Ignoring Feature Importance

Deploying models without reviewing feature contribution masks noisy or irrelevant inputs that degrade generalization. After training, the script executes ML.FEATURE_INFO at lines 35-36 to inspect column contributions.

Review the feature importance output to identify low-information columns. Removing variables with negligible contribution reduces model complexity and inference costs while improving predictive stability.

Over-Reliance on Default Hyperparameters

Default regularization strengths rarely optimize for your specific data distribution, leading to underfitting or overfitting. The repository demonstrates hyperparameter tuning at lines 80-88, utilizing num_trials, l1_reg, and l2_reg with MAX_PARALLEL_TRIALS to accelerate search.

Use the hparam_range() and hparam_candidates() functions to define search spaces. Monitor ML.HYPERPARAMETER_TUNING results to select optimal regularization values rather than accepting library defaults.

Feature Scaling and Normalization

Linear models assume comparable feature scales; large numeric ranges can dominate the loss function and obscure patterns in smaller variables. While big_query_ml.sql focuses on data cleaning, the README emphasizes converting units (e.g., miles to kilometers) or applying NORMALIZE preprocessing functions before training.

Standardize or normalize numerical features when ranges vary by orders of magnitude. This prevents high-magnitude columns like trip_distance from drowning out subtle signals in passenger_count.

Query Cost Management and Partitioning

CREATE MODEL performs full table scans each execution, which can generate substantial costs on large datasets. The repository utilizes the partitioned table yellow_tripdata_partitioned and applies WHERE clauses to limit the training window.

Always partition your source tables by date or region. Add explicit WHERE filters to training queries to scan only relevant partitions, and monitor bytes processed in the query validator before executing expensive model creation statements.

Model Versioning and Reproducibility

Using CREATE OR REPLACE MODEL destroys previous versions, eliminating rollback capabilities and experiment tracking. Avoid destructive updates by appending version suffixes (e.g., tip_model_v1) or using CREATE MODEL without REPLACE.

Store training parameters and feature schemas in separate metadata tables. This practice enables reproducibility and A/B testing between model iterations without losing historical artifacts.

Misinterpretation of Evaluation Metrics

ML.EVALUATE returns R², MAE, and RMSE, but high scores on skewed targets can be misleading. The script examines the full metric table at lines 38-43, comparing results against baseline predictors before accepting model performance.

Always evaluate metrics in context; compare your model's performance against a simple baseline like the mean predictor. Inspect residual distributions and confusion matrices to ensure your metrics reflect real-world utility rather than statistical artifacts.

Complete Training Workflow Implementation

The following SQL patterns from 03-data-warehouse/big_query_ml.sql demonstrate a defensive workflow that avoids the pitfalls above.

Create a Preprocessed Feature Table

Isolate feature engineering from model training to enable versioning and debugging:

CREATE OR REPLACE TABLE `myproject.dataset.taxi_ml` AS
SELECT
  passenger_count,
  trip_distance,
  CAST(PULocationID AS STRING) AS pu_location,
  CAST(DOLocationID AS STRING) AS do_location,
  CAST(payment_type AS STRING) AS payment_type,
  fare_amount,
  tolls_amount,
  tip_amount
FROM `myproject.dataset.yellow_tripdata_partitioned`
WHERE fare_amount != 0
  AND tip_amount IS NOT NULL;

Reference: Lines 5-19 in [big_query_ml.sql](https://github.com/DataTalksClub/data-engineering-zoomcamp/blob/main/03-data-warehouse/big_query_ml.sql#L5-L19)

Train a Baseline Linear Regression Model

Establish a performance floor before attempting complex optimization:

CREATE OR REPLACE MODEL `myproject.dataset.tip_model`
OPTIONS (
  model_type = 'linear_reg',
  input_label_cols = ['tip_amount'],
  data_split_method = 'AUTO_SPLIT'
) AS
SELECT * FROM `myproject.dataset.taxi_ml`;

Reference: Lines 22-27 in [big_query_ml.sql](https://github.com/DataTalksClub/data-engineering-zoomcamp/blob/main/03-data-warehouse/big_query_ml.sql#L22-L27)

Execute Hyperparameter Tuning

Optimize regularization parameters to prevent overfitting:

CREATE OR REPLACE MODEL `myproject.dataset.tip_hyperparam_model`
OPTIONS (
  model_type = 'linear_reg',
  input_label_cols = ['tip_amount'],
  data_split_method = 'AUTO_SPLIT',
  num_trials = 5,
  max_parallel_trials = 2,
  l1_reg = hparam_range(0, 20),
  l2_reg = hparam_candidates([0, 0.1, 1, 10])
) AS
SELECT * FROM `myproject.dataset.taxi_ml`;

Reference: Lines 80-88 in [big_query_ml.sql](https://github.com/DataTalksClub/data-engineering-zoomcamp/blob/main/03-data-warehouse/big_query_ml.sql#L80-L88)

Evaluate Model Performance

Inspect metrics to validate against baseline expectations:

SELECT *
FROM ML.EVALUATE(
  MODEL `myproject.dataset.tip_model`,
  (SELECT * FROM `myproject.dataset.taxi_ml`)
);

Reference: Lines 38-43 in [big_query_ml.sql](https://github.com/DataTalksClub/data-engineering-zoomcamp/blob/main/03-data-warehouse/big_query_ml.sql#L38-L43)

Analyze Feature Contribution

Identify which variables drive predictions:

SELECT *
FROM ML.FEATURE_INFO(MODEL `myproject.dataset.tip_model`);

Reference: Lines 35-36 in [big_query_ml.sql](https://github.com/DataTalksClub/data-engineering-zoomcamp/blob/main/03-data-warehouse/big_query_ml.sql#L35-L36)

Summary

• Exclude target columns from feature sets by explicitly listing inputs and using input_label_cols to prevent data leakage.
• Validate data types with explicit casting and INFORMATION_SCHEMA checks to avoid silent conversion errors.
• Filter NULL values before training or impute them to prevent biased sampling.
• Control data splits explicitly rather than relying solely on AUTO_SPLIT for time-series or imbalanced data.
• Review feature importance using ML.FEATURE_INFO to eliminate noise and reduce costs.
• Tune hyperparameters with num_trials and regularization ranges instead of accepting defaults.
• Partition source tables and add WHERE clauses to CREATE MODEL queries to manage scan costs.
• Version model names and store metadata separately to maintain reproducibility when iterating.

Frequently Asked Questions

How do I prevent data leakage when using SELECT * in BigQuery ML?

Explicitly list your feature columns in the SELECT statement rather than using wildcards, and specify the target column separately in the input_label_cols option. In the DataTalksClub repository, the tip_amount label is isolated at lines 22-27 of big_query_ml.sql while features are explicitly enumerated in the preprocessing table creation.

Why does my BigQuery ML model perform poorly on categorical string columns?

BigQuery ML treats STRING columns as categorical features with high cardinality, which can overwhelm linear models if the strings represent numerical IDs. Cast categorical identifiers to STRING intentionally for one-hot encoding, but ensure truly numerical features are stored as numeric types, as demonstrated by the explicit casting at lines 16-18.

What is the best way to manage costs when training models on large tables?

Partition your source tables by date or region and include explicit WHERE clauses in your CREATE MODEL statement to limit scanned data. The repository uses yellow_tripdata_partitioned to restrict scans, and you should always check the query validator for bytes processed before executing model creation on large datasets.

How do I interpret evaluation metrics from ML.EVALUATE correctly?

Compare the returned R², MAE, or RMSE values against a simple baseline predictor, such as always guessing the mean target value. High R² scores can be misleading on skewed targets, so examine the full metric table as shown at lines 38-43 and consider business context before deploying the model.

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