Data Validation in ETL Pipelines: Catching Bad Data Before It Reaches a Model

Why models fail quietly on bad input, not loudly

A model trained on clean, curated data will not raise an alarm when it is later fed a sensor reading that is physically impossible, a duplicated row counted twice, or a missing value silently interpreted as zero — it will simply produce a prediction, and that prediction will be wrong in a way that is easy to miss until it causes a downstream problem. This is what makes data validation, done before data ever reaches model training or inference, a distinct discipline from model evaluation: model evaluation checks whether the model is good at its job, while data validation checks whether the model is even being given the inputs it was designed to handle.

Missing values, duplicates, and impossible readings

Real-world data, particularly from sensors and logs, is reliably imperfect in a few recurring ways. Missing values need a deliberate strategy rather than a default — filling with the column mean or median is reasonable for values that fluctuate around a stable baseline, forward-filling (carrying the last known value forward) suits slowly-changing signals like temperature, and dropping rows outright is appropriate when a field is critical and cannot be reasonably imputed. Duplicate records, often produced by retried API calls or overlapping data pulls, need an explicit definition of what makes two rows "the same" — commonly a combination of an entity identifier and a timestamp — before they can be reliably detected and removed, keeping only the most recent copy.

Physically impossible readings are a distinct category worth checking for explicitly, separate from statistical outlier detection: a temperature reading below absolute zero for the sensor type, a negative voltage where only positive values are physically meaningful, a duration longer than the time window the data covers. These are not borderline cases needing a judgment call — they are hard validity constraints that should be enforced directly, for example filtering rows outside a known physically plausible range for a given field, before any statistical anomaly detection is applied to the remaining data.

IQR-based outlier filtering as a first-pass filter

Separate from hard physical-validity checks, a simple statistical method for flagging unusually extreme but not necessarily impossible values is the interquartile range (IQR) rule: compute the 25th percentile (Q1) and 75th percentile (Q3) of a column, take their difference as the IQR, and treat any value more than 1.5 times the IQR below Q1 or above Q3 as an outlier candidate. This is a lightweight, distribution-agnostic check — it does not assume the data follows any particular shape — and is commonly used as an early filtering pass before data reaches more sophisticated anomaly-detection models, catching gross data-quality problems (a decimal point error, a unit mismatch) rather than the subtler behavioural anomalies a dedicated anomaly-detection model is meant to find.

It is worth being explicit that an IQR-flagged value is not automatically wrong — it might be a genuine, correctly-recorded extreme event — so IQR filtering in a data-cleaning context is usually applied conservatively, or the flagged rows are set aside for review rather than deleted outright, especially when the extreme values might themselves be exactly the anomalies a downstream model is meant to detect.

Automating validation with explicit expectations

Manually re-checking data quality by eye does not scale once a pipeline runs on a schedule against continuously arriving data. The pattern that scales is to encode data-quality rules as explicit, automatically-checked expectations — a null-count threshold per column, an allowed numeric range, a uniqueness constraint on a key field — that run automatically every time the pipeline executes, failing loudly and stopping the pipeline (or at minimum alerting a human) when a rule is violated, rather than silently passing bad data downstream. Tools purpose-built for this, such as Great Expectations, formalise this pattern, letting a team define a suite of expectations once and get an automated data-quality report on every run, with a clear record of what passed, what failed, and when the failure started.

The value of this approach is less in catching any single dramatic failure and more in catching the slow, easy-to-miss ones: a sensor that starts silently reporting flat values because it has failed, an upstream schema change that adds a new column or renames an old one, a null rate that creeps upward over several weeks without ever crossing an obvious threshold on any single day. Logging summary statistics after each pipeline stage — row counts, null counts, min and max values — turns this kind of gradual drift from invisible into something that shows up clearly on a trend line.

Batch vs streaming: choosing how often to validate and load

ETL pipelines run in one of two broad modes, and the choice affects how validation fits in. Batch processing collects data over a period — commonly once a day — and processes it all at once; it is well suited to historical data used for model training, where a delay of hours is irrelevant to correctness, and it is simpler to build, monitor, and re-run than a streaming system if something goes wrong. Streaming processing, using tools such as Kafka paired with a stream-processing engine like Spark Streaming, processes data continuously as it arrives, which is necessary for real-time monitoring and alerting use cases — detecting an equipment fault within seconds rather than finding it in tomorrow's batch run — at the cost of meaningfully higher operational complexity.

Validation logic itself does not fundamentally differ between the two modes — the same null checks, range checks, and duplicate checks apply — but the consequence of a validation failure does: a batch pipeline can reasonably pause and wait for a person to investigate a failed validation before the day's data ever reaches a model, since the next batch is hours away regardless, whereas a streaming pipeline validating each record or micro-batch as it arrives usually needs an automated fallback — routing failing records to a separate holding location for later review rather than blocking the entire live stream — since pausing a real-time feed to wait for human intervention defeats the purpose of processing it in real time to begin with.