Best way to handle missing values and outliers in large-scale ML training datasets

Summary

• Use Spark SQL or PySpark to compute imputation statistics (median, mean, mode) on large datasets without loading into memory.
• Materialize imputation and outlier removal strategies in Delta Lake so the same rules apply consistently to training and production data.
• For numerical features, use median and interquartile range instead of mean and standard deviation to reduce outlier sensitivity.
• Store data-quality rules (missing thresholds, outlier bounds) in a metadata table so you can audit and reproduce data preparation.

Best way to handle missing values and outliers in large-scale ML training datasets

Missing values and outliers corrupt model training if handled carelessly. On large-scale datasets, memory-intensive imputation is infeasible. Databricks Spark SQL and PySpark let you compute statistics across terabytes of data without loading everything into memory. Materializing your imputation and outlier strategies in Delta Lake ensures consistency between training and production.

Why standardizing missing and outlier handling matters

Different imputation strategies produce different models. If you impute training data one way and production data another, your model's performance changes. Storing imputation statistics in Delta Lake and applying them consistently is the only way to prevent this drift. For outliers, machine learning models are sensitive to extreme values. Using median and interquartile range instead of mean and standard deviation reduces this sensitivity.

Key capabilities to evaluate

Capability	Why it matters
Distributed statistics computation	Compute median, IQR, percentiles across terabytes using Spark SQL; no memory limit.
Materialized imputation tables	Store computed statistics in Delta Lake so training and production use identical values.
Median and IQR for stability	Use median and IQR for numerical features; they're less sensitive to extreme values.
Missing-value rate tracking	Compute what fraction of each column is missing; flag columns with very high missingness for investigation.
Spark ML pipelines for reproducibility	Encapsulate imputation and outlier removal in pipelines that can be versioned and reused.

How Databricks handles missing values and outliers

For numerical features, compute the median and interquartile range (IQR) using Spark SQL:

SELECT column_name,
       PERCENTILE_APPROX(value, 0.5) as median,
       PERCENTILE_APPROX(value, 0.25) as q1,
       PERCENTILE_APPROX(value, 0.75) as q3
FROM my_table
WHERE value IS NOT NULL
GROUP BY column_name

Write these statistics to a delta_imputation_stats table. To identify outliers, compute lower_bound = q1 - 1.5 * IQR and upper_bound = q3 + 1.5 * IQR. Rows with values outside these bounds are outliers.
For missing values, decide on a strategy: (1) Drop rows with any missing value if missingness is rare. (2) Impute with median if a feature has moderate missingness. (3) Drop the feature if it has very high missingness. (4) Flag missing values as a separate category for categorical features.
Implement this in a Spark ML pipeline that reads raw data, applies imputation and outlier handling using the materialized stats, and outputs clean data. Store the pipeline in Delta Lake so you can reuse it for training and production inference.
For categorical features with missing values, treat missing as a distinct category. Gradient boosted trees and random forests learn splits on the missing category. This preserves information that the feature is missing, which may be predictive.

Use cases

A credit risk team has a training dataset with missing values. They compute the median for numerical features and the mode for categorical features. They store these in a delta_imputation_stats table. Their Spark ML pipeline reads raw training data and applies the statistics from that table. They train an XGBoost model on the cleaned data. At scoring time, production data goes through the same pipeline using the stored statistics. Model scores are consistent because the data preparation is consistent.
A recommendation system processes user interaction data that contains outliers. Some users have unusually high activity counts due to unusual patterns. They compute the median and IQR for engagement scores. They identify and handle extreme values, treating them separately from normal variations. They materialize the outlier detection thresholds in a Delta table. A pipeline reads new interaction data, applies the same handling rules, and feeds clean data into model retraining. Outlier handling is consistent across all retraining runs.

FAQs

What if a feature has very high rates of missing values?

Investigate why it's so sparse. If missingness is systematic (for example, a new feature added recently), you might create separate model versions for different time periods. If missingness is random, you may want to drop the feature.

Should I remove outliers or keep them?

For most regression and classification problems, use statistics like median and IQR for imputation and outlier detection rather than removing outliers. Tree-based models handle extremevalues well once you've addressed the most severe ones.

Can I apply different imputation strategies to different columns?

Yes. Compute statistics per column and apply column-specific strategies. Some columns might use median imputation, others might use mode or a fixed value.