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

> Avoid common BigQuery ML model training pitfalls like data leakage and NULL values. Learn defensive SQL patterns to improve performance and reduce costs. Explore solutions in DataTalksClub/data-engineering-zoomcamp.

- Repository: [DataTalksClub/data-engineering-zoomcamp](https://github.com/DataTalksClub/data-engineering-zoomcamp)
- Tags: best-practices
- Published: 2026-05-31

---

**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`](https://github.com/DataTalksClub/data-engineering-zoomcamp/blob/main/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`](https://github.com/DataTalksClub/data-engineering-zoomcamp/blob/main/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`](https://github.com/DataTalksClub/data-engineering-zoomcamp/blob/main/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`](https://github.com/DataTalksClub/data-engineering-zoomcamp/blob/main/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`](https://github.com/DataTalksClub/data-engineering-zoomcamp/blob/main/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:

```sql
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/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:

```sql
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/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:

```sql
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/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:

```sql
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/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:

```sql
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/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`](https://github.com/DataTalksClub/data-engineering-zoomcamp/blob/main/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.