Data quality validation in ETL pipelines means building active checks into extraction, transformation, and load steps that verify data content — not just pipeline status. Five check categories cover what monitoring misses: null completeness, range and type validity, referential integrity, statistical baseline, and cardinality. Each belongs in a specific pipeline phase, with a defined response when it fails.
Pipeline status monitoring confirms operational facts: the job ran, the connection succeeded, the schedule was honored. Those are necessary things to know. What they don't confirm is whether the output is correct — whether the records that arrived at the destination are complete, accurate, and within the parameters the downstream consumers are counting on. That gap is where the failures that actually matter to the business live. The data observability framework establishes the argument for why monitoring and validation are different capabilities. This piece is the implementation side: the specific checks, where they run, and what happens when they fail.
Two caveats on scope. First, this framework addresses ETL pipelines where transformation runs in the pipeline engine, not post-load in a warehouse. Teams using ELT-first architectures with warehouse-native transformation can apply the extract-phase checks here directly; the transform-phase checks require adaptation to warehouse testing patterns. Second, the check categories below are not exhaustive for every conceivable data quality problem — they cover the five failure modes that standard pipeline status monitoring cannot detect, which is the relevant scope for this argument.
What Data Quality Validation in ETL Actually Is
Data quality validation is active assertion logic embedded in pipeline steps that compares data content against expected parameters — and routes records or halts execution when those parameters aren't met. It's not a separate validation tool that runs after the pipeline. It's not a dashboard that shows row counts. It's logic inside the pipeline that evaluates whether what the pipeline is processing is what it should be processing.
The distinction from monitoring matters practically. Monitoring is passive: it records what happened and alerts when defined system states are reached (connection failure, schedule miss, exception thrown). Validation is active: it defines what correct data looks like, tests each record or batch against that definition, and routes deviating records or batches to a response path before they continue.
Validation-as-a-separate-job — running a quality check script against the destination table after the load completes — is better than nothing, but it has a specific limitation: by the time the check runs, the wrong data is already in the destination. Downstream consumers who pull data on a schedule may have already read it. Reports may have already run against it. For regulated pipelines, incorrect data may have already been used in a process governed by accuracy requirements. Validation embedded in the pipeline catches problems before the destination write — or, in the soft-failure case, routes problematic records to quarantine rather than the production table.
The data quality and transformation layer is where these checks live operationally — inside the transformation steps that run before and during the load phase, with routing logic that determines what happens to records that fail.
The Five Check Categories That Cover What Monitoring Misses
Each of the following check categories addresses a distinct failure mode. They aren't interchangeable. A null completeness check doesn't catch distribution drift; a cardinality check doesn't catch type coercions. Building a complete validation layer means implementing all five, placed at the correct phase.
| Check Category | What It Detects | Pipeline Phase | Response Type | Missing = Risk |
|---|---|---|---|---|
| Null Completeness | Non-nullable fields containing null values; fields below a minimum population threshold | Extract | Hard or Soft (by field criticality) | Null values propagate silently to destination; aggregations produce incorrect totals on fields assumed complete |
| Range & Type Validity | Field values outside defined numeric bounds; values that violate expected data type | Extract or Transform | Hard or Soft (by field) | Out-of-bound values (negative revenue, impossible dates) pass into destination; type coercions produce wrong values silently |
| Referential Integrity | Foreign key values absent from the reference table at join time | Transform | Usually Soft — orphaned records quarantined | Orphaned records join to nulls or are silently dropped, producing understated totals and broken dimension lookups |
| Statistical Baseline | Field distributions (mean, null rate, unique count) deviating beyond defined thresholds from historical baseline | Transform | Soft — alert and flag; rarely hard-fail a full batch | Distribution drift produces plausible wrong statistics; impossible to detect without a stored baseline to compare against |
| Cardinality | Join or aggregation output row count outside the expected range for this source and window | Transform | Hard or Soft depending on deviation magnitude | Join fanout inflates metrics; wholesale data loss goes undetected if output count is never verified against expected range |
Statistical baseline checks warrant a specific note on setup cost. Unlike null completeness and range validity — which can be defined purely from schema knowledge — statistical baseline checks require an established baseline to compare against. That baseline is built from historical execution data: the mean and standard deviation of a field's values across the last N runs, or the expected null rate for a column given its source population. This means statistical baseline validation has a setup requirement that the other four categories don't: it can't be enabled on a new pipeline with zero execution history. It becomes available after a baseline accumulation period, typically 10–20 runs, and its thresholds require tuning as the source data's natural variance becomes understood.
Where in the ETL Pipeline Each Check Belongs
Check placement is a performance and effectiveness decision simultaneously. Running every check at every phase adds latency without improving coverage. Placing checks at the wrong phase means the check either can't execute (the required context doesn't exist yet) or runs redundantly on data that's already been validated. The principle is straightforward: run each check at the earliest phase where it can execute correctly.
Extract Phase — Run Structural Checks Before Transformation
Cheapest checks; earliest rejection; runs immediately after data is read from source
Null completeness checks and range-and-type validity checks both belong at extraction. At this phase, you have individual records from the source — no joins, no business logic applied yet — and rejecting a structurally invalid record here costs almost nothing compared to rejecting it after a complex transformation has executed on it. The extract phase is also the only place where you can verify the raw source data before transformation logic has had a chance to mask or coerce the problem.
A type coercion that silently converts a VARCHAR containing a non-numeric string to a zero in a numeric destination column will not be visible after the coercion has run. A range-and-type check at extraction sees the raw VARCHAR value and can flag the record before the coercion happens. This is the detection window. After extraction, the record looks clean.
Completeness checks also belong here: verify that the source batch contains records within the expected volume range before processing anything. A source that returns zero records, or 10 percent of the expected count, is a failure mode worth detecting before running the full transformation on an empty or truncated dataset.
Transform Phase — Run Semantic and Pattern Checks After Logic Executes
Most powerful checks; requires post-join, post-aggregation context; where statistical and cardinality validation runs
Referential integrity, statistical baseline, and cardinality checks require context that only exists after transformation has run. Referential integrity verification checks whether foreign keys in the transformed output resolve to values in the reference dimension table — a check that requires both the transformed join key and the reference table to be in scope. Statistical baseline comparison requires the aggregated field values from the current run to compare against stored baseline statistics. Cardinality verification requires the output row count from the join or aggregation, which doesn't exist until that step has executed.
Placing these checks at extraction would require either re-reading reference tables separately (for referential integrity) or checking against incomplete pre-aggregation statistics (for baseline and cardinality). Neither produces accurate results. The transform phase has the full context these checks need.
A practical sequencing note: within the transform phase, run referential integrity checks before statistical baseline checks. Referential integrity failures can affect the record count and distribution of the remaining records, which would produce false baseline anomalies if statistical checks run first.
Load Phase — Final Record Count and Schema Confirmation
Last safeguard before destination write commits; catches infrastructure-level discrepancies
Two checks belong at the load phase, immediately before the destination write commits. First: verify that the count of records prepared for load matches the count of records that were expected to load, based on what passed extraction and transformation validation. A discrepancy here indicates a load-layer failure — a buffer issue, a network truncation, a destination-side rejection that didn't surface as an exception. Second: confirm the destination schema still matches the expected structure. Destination schema changes — column additions, type alterations, dropped columns — don't always produce visible errors in the ETL layer. Explicit schema confirmation at load time catches them before wrong data writes to the wrong structure.
These are lightweight checks. They add minimal latency. Running them prevents a specific category of failures that the more complex transform-phase checks don't cover: failures that occur in the handoff between the ETL engine and the destination system, after all transformation validation has passed.
Hard Failures vs. Soft Failures: How Your Pipeline Should Respond
"Log and continue" is not a soft failure response. It's what happens when the failure routing decision was never made — and wrong records reach the destination with nothing to show for it.
Every validation check needs a defined response for when it fails. The choice between a hard failure (halt the pipeline, reject the batch) and a soft failure (quarantine failing records, continue with passing records) is a business decision that should be made explicitly during pipeline design. The default in most ETL platforms — log the event and continue — is neither; it produces wrong output with an informational log entry that nobody will read until something downstream breaks.
- All records in the run are rejected; the load does not execute
- Pipeline execution halts with a validation-failure status, not a generic error
- Alert fires to the named pipeline owner with check name, failure detail, and affected record count
- Execution log captures the check that failed, the failure condition, and input record count
- No partial output reaches the destination
- Use when: partial output is more dangerous than no output; downstream processes cannot tolerate missing fields or wrong values; the source data issue requires remediation before any load occurs
- Records failing the check route to a quarantine table or error channel with check name and failure reason attached
- Records passing the check continue through the pipeline to the destination
- Alert fires with failure count, check name, and quarantine location — not to a shared channel; to the named owner
- Execution log captures passing count, failing count, and quarantine destination
- Partial output reaches the destination on schedule
- Use when: downstream consumers can tolerate partial output; the error rate is expected and within an acceptable threshold; the failure is localized to specific records rather than systemic
The hard-vs-soft decision should be made per check type, not per pipeline. Cardinality checks that detect a 50 percent row count inflation warrant a hard failure — that's a systemic problem affecting every record in the output. Referential integrity checks that find 2 percent of records with orphaned foreign keys might warrant a soft failure with quarantine, because the 98 percent of valid records still need to reach the destination on schedule.
Documenting the decision explicitly also protects against the common failure mode where a soft-failure quarantine table accumulates months of rejected records that nobody monitors. If the quarantine exists, its alert needs a named owner and a defined review cadence. A quarantine table with no owner is the same as no quarantine — the failures are invisible to anyone who could act on them. The audit-ready operations framework covers how execution logging and alert routing connect to the formal review requirements that regulated pipelines face.
When Data Quality Checks Are Compliance Requirements
Three regulatory frameworks place specific technical obligations on pipelines that process regulated data — obligations that map directly to the check categories above, not to pipeline monitoring.
GDPR Article 5(1)(d)[1] states that personal data shall be "accurate and, where necessary, kept up to date; every reasonable step must be taken to ensure that personal data that are inaccurate, having regard to the purposes for which they are processed, are erased or rectified without delay." The "accurate" requirement is a processing obligation, not a reporting one. It applies throughout the data lifecycle — including at the point of extraction and transformation. Range-and-type validity checks and statistical baseline checks are the technical implementation of this obligation inside an ETL pipeline. A pipeline that loads personal data containing systematic range violations or distribution anomalies, without a detection mechanism, is processing inaccurate data without the "reasonable steps" Article 5(1)(d) requires.
HIPAA 45 CFR §164.312(c)(1)[2] requires covered entities to "implement policies and procedures to protect the integrity of electronic protected health information." Integrity protection is not limited to access control — it extends to preventing improper alteration during processing. ETL pipelines that process ePHI and lack null completeness, referential integrity, or cardinality validation have no technical control preventing the silent alteration of ePHI values through type coercion, orphaned joins, or join fanout. The five check categories collectively constitute the integrity-protection technical control that this provision requires.
NIST SP 800-53 Rev. 5, SI-7[3] (Software, Firmware, and Information Integrity) requires organizations to employ integrity verification tools to detect unauthorized changes to information. In an ETL context, SI-7 maps to statistical baseline and cardinality checks — the checks that detect when output has changed in ways that weren't authorized by a transformation rule change. A cardinality anomaly or a baseline distribution shift in a production pipeline represents a change to information integrity that SI-7 requires detection mechanisms to identify.
The connection runs in both directions. Implementing these checks satisfies technical requirements that regulatory frameworks specify. Failing to implement them creates a documented gap in technical controls that auditors examine when reviewing data processing practices. For pipelines handling personal data, validation isn't a quality aspiration — it's the engineering expression of a legal obligation. The data governance and compliance framework covers how these technical controls connect to the broader governance requirements that compliance-mandated pipelines must satisfy.
Frequently Asked Questions
Both. Different check categories belong in different phases based on the context each check requires to execute correctly. Null completeness and range-and-type validity checks belong at extraction — they run on raw source records before transformation and can reject structurally invalid data before transformation logic processes it. Referential integrity, statistical baseline, and cardinality checks belong at transformation — they require the join results, aggregated values, or transformed output that only exists after business logic has run.
Load-phase validation (record count verification and destination schema confirmation) runs last. The principle for deciding phase placement is simple: run each check at the earliest pipeline phase where it has the context to execute correctly. Running it earlier than that produces inaccurate results; running it later than necessary means transformation work is done on records that should have been rejected.
Two responses are valid: hard failure (halt the pipeline and reject the entire batch) or soft failure (quarantine the failing records and allow passing records to continue). Hard failure is appropriate when partial output is more dangerous than no output — for example, when a cardinality anomaly indicates a systemic problem affecting every record. Soft failure with quarantine is appropriate when the failure is localized, the error rate is within an acceptable threshold, and downstream consumers can tolerate a partial load.
"Log and continue" without quarantine is neither response. It allows records that failed a validation check to reach the destination unchanged, while producing an informational log entry that creates no operational consequence. The failing records become wrong data in the destination with no mechanism for tracking, reviewing, or remediating them. The soft-failure quarantine approach is preferable to "log and continue" in almost every case — it preserves the failing records for investigation while protecting the destination from wrong values.
A cardinality check verifies that a join or aggregation step produced an output row count within an expected range for this source and this run window. If a JOIN that normally produces 1:1 cardinality suddenly produces 1:N matches for a subset of records — because a uniqueness constraint was removed upstream or because a many-to-many relationship exists where a one-to-many relationship was assumed — the output row count inflates without throwing an exception, violating a schema rule, or producing nulls.
Any aggregate metric built on an inflated join is wrong by the fanout factor for the affected records. This is one of the silent failure modes that status monitoring cannot detect: the pipeline ran successfully, the row counts are plausible (within the outer limit of the source), and the output passes field-level validation. Without a cardinality check comparing the output count to a defined expected range, this failure reaches the destination undetected. The check should cover both inflation (join fanout) and deflation (data loss where fewer records than expected were produced).
Phase placement and risk prioritization together control the performance cost. Place null completeness and range-and-type checks at extraction, where they run on raw records before the transformation overhead. These checks are fast and can actually reduce overall pipeline runtime by rejecting invalid records early — records that would otherwise consume transformation resources before failing at a later stage.
Statistical baseline and cardinality checks run at transformation but should be scoped to high-impact columns and join outputs, not applied uniformly across every field. Validate the specific fields whose failure modes would produce wrong downstream metrics, not every column in every table. A practical starting point: apply statistical baseline checks to the columns that feed your most critical aggregations, and cardinality checks to every JOIN in the pipeline. These two checks together catch the most consequential silent failures without requiring comprehensive field-level statistical coverage across the entire dataset.
A null check is a field-level assertion: a specific column must not contain null values in any record of the batch. It fails on any single null in a field that has been defined as non-nullable. A completeness check is a volume-level assertion: the dataset must contain at least N records, or a specific column must have at least a defined minimum percentage of populated values. A completeness check catches batch-level data loss — a source returning zero records, or a population of records where 35 percent of a revenue field is null when the historical baseline is under 3 percent — that a null check alone would not detect.
Both belong in the null completeness check category. They address different failure modes: null checks catch individual field-integrity failures that could propagate through aggregations; completeness checks catch wholesale data loss or structural population failures that null checks miss because they operate record by record. For pipelines feeding reporting, both are required — a table with no nulls but only 30 percent of its expected row count is as wrong as a table with the right count but missing values in critical fields.
Validation built into the pipeline is validation that runs on every run, automatically, before wrong data reaches the destination. The alternative — post-load scripts, manual spot-checks, or waiting for downstream consumers to notice something is off — produces discovery timelines measured in hours or days, by which point the wrong numbers may have already been read, acted on, or reported. The five check categories above, placed at the correct phase and with defined failure routing, close the gap that monitoring leaves open.
