The short answer. ETL data validation is the set of checks that run as data is extracted, transformed, and loaded, confirming it is complete, correct, and consistent before it reaches the target. Validating before the load, rather than after, means errors are caught while they are still cheap to fix and before they corrupt the destination system.

Every data pipeline makes a promise: what comes out is trustworthy. Validation is how that promise is kept. Without it, an ETL job will happily move broken data quickly and reliably, which is the worst of both worlds.

What ETL data validation is

ETL stands for extract, transform, load, the process of pulling data from a source, reshaping it, and writing it to a target. Validation is the checking layer around that process. It confirms that the data extracted matches the source, that transformations produced the intended result, and that what lands in the target is complete and correct. It is the difference between moving data and moving data you can trust.

The checks that matter

Completeness checks: did every record and every required field make it through, with nothing dropped?
Count reconciliation: does the number of records at the target match what left the source?
Format and validity checks: do values conform to the expected type, range, and pattern?
Business rule checks: does the data satisfy the rules the business actually cares about, not just technical formats?
Consistency checks: do related values still agree after transformation, keeping referential integrity intact?
Duplicate checks: did the process avoid introducing or carrying through duplicates?

Where to run validation

Good pipelines validate at more than one point. Check at extraction to confirm you pulled what you expected, during transformation to confirm the logic worked, and before load using a staging area so nothing questionable touches the target. A final reconciliation after load then proves the result. Validating only at the end tells you there is a problem; validating throughout tells you where it is.

Why pre-load beats post-load

Catching an error before the load is cheap: you fix it in staging and move on. Catching it after the load is expensive: the target is already wrong, downstream systems may have consumed the bad data, and unwinding it takes time and trust. Pre-load validation, backed by staging, keeps problems contained. It is the single most valuable habit in a reliable pipeline.

Automated validation beats manual checking

Manual spot-checks have their place, but they do not scale and they do not repeat reliably. A person sampling a few hundred rows from a load of millions will miss most problems, and will check different things each time. Automated validation, driven by defined rules, checks every record the same way every run. For any pipeline that runs more than once, and migrations run many times through mock loads, automation is the only approach that holds up. The goal is that a green result genuinely means the data is sound, not that nobody found the problem yet.

Validation in a migration context

Validation matters in any pipeline, but it is especially critical in a migration, where the target is a production system the business will depend on from day one. Here the sequence is unforgiving: extract and confirm you pulled what you expected, transform and confirm the mapping to the target model worked, stage and validate before the load so nothing questionable reaches the target, then reconcile after load to prove the result. Skipping any of these steps in a migration does not just risk a bad report; it risks launching a new core system on data no one can vouch for. That is why a mature migration treats validation as a gate at every stage, not a final check.

Good validation rules are business rules

The most common weakness in ETL validation is checking only what is easy to check. Confirming a field is not empty, or that a date is a valid date, catches formatting problems but misses the errors that actually hurt. The valuable checks encode business logic: that an order total equals the sum of its lines, that a customer marked active has a valid billing address, that a material has a unit of measure consistent with its category. These rules require understanding the business, not just the schema, which is why the best validation is designed with the people who own the process, not just the people who own the pipeline.

Writing rules this way has a second benefit: the rules become reusable knowledge. A rule that captures what a valid business partner looks like is useful during migration, during ongoing operation, and during any future integration. Validation, done well, is where an organisation's understanding of its own data gets written down.

What to do when validation fails

Validation is only half the job; the other half is what happens when a check fails. A mature pipeline does not simply stop and log an error. It routes the failing records somewhere useful, quarantines them so they do not contaminate the load, and gives the responsible team enough information to correct and resubmit them. In a migration, this often takes the shape of sending invalid records back to business owners for validation and correction, then reloading the corrected file. The aim is a clear, repeatable loop: catch, quarantine, correct, reload, so that failures become a managed workflow rather than a crisis.

Balancing thoroughness and speed

There is a natural tension in validation between checking everything and keeping pipelines fast. Validate too little and bad data slips through; validate too much and every run slows to a crawl. The resolution is not to treat all checks equally but to match rigour to risk. Critical fields and business-critical rules justify thorough, every-record validation. Lower-stakes data can be sampled or checked more lightly.

In a migration, where the target is a system the business will depend on, the balance tips firmly toward thoroughness, because the cost of a bad load dwarfs the cost of a slower one. In a high-frequency operational pipeline, a lighter touch on non-critical fields may be the right trade. The skill is deciding deliberately, rather than defaulting to either extreme by accident.

How deKorvai helps

deKorvai builds validation into its ETL flow rather than treating it as an afterthought. Data is profiled and validated against business and technical rules before it is loaded, staged so issues surface early, and reconciled against the source after load. For an S/4HANA migration, that means data is checked at every step on the way into the Migration Cockpit staging tables, not just when it arrives.

Key takeaways

Validation is the trust layer around extract, transform, load.
Check throughout, not just at the end, so you know where problems are.
Pre-load beats post-load: errors caught in staging never corrupt the target.
Reconcile after load to prove the result, not just assume it.

Frequently asked questions

What is ETL data validation?

It is the set of checks that run as data is extracted, transformed, and loaded, confirming the data is complete, correct, and consistent before and after it reaches the target. It is what makes a pipeline trustworthy rather than just fast.

What checks should ETL validation include?

Completeness, count reconciliation, format and validity, business rules, consistency and referential integrity, and duplicate checks. Together they confirm nothing was lost, corrupted, or duplicated in transit.

Should you validate before or after loading?

Both, but pre-load validation is the priority. Catching errors in staging before they reach the target keeps them cheap and contained; a post-load reconciliation then proves the result was correct.

Is ETL validation the same as data quality?

They overlap. ETL validation confirms data moved correctly through a pipeline; data quality is the broader, ongoing measure of whether data is fit for use. Validation is one place quality gets enforced.

ETL Data Validation: Catch Errors Before the Load, Not After