What is data validation? Types, rules and techniques

Data validation is the process of checking that data satisfies a set of defined rules before it is accepted, stored or reported. A validation rule states a condition the data must meet: a date must fall within an open period, an amount must be positive, a supplier code must exist in the supplier master.

The word “defined” carries the weight. Validation is only as good as the rules written down, and the most common failure is not a rule that runs incorrectly but a rule that was never written. Data that passes validation is not necessarily correct; it is data that nobody has said anything specific about, beyond the conditions someone thought to check.

Definition card explaining data validation as checking that data satisfies rules defined in advance before it is accepted, stored or reported, with three points: validation is only as good as the rules written down, passing validation is not the same as being correct, and the cost of invalid data rises the further it travels from the point of entry.

Invalid data becomes more expensive the further it travels. Caught at entry it is a corrected keystroke. Caught in a report it is an investigation. Caught by an auditor it is a restatement.

The main types of data validation

Type	What it checks	Example
Type and format	The value is the right shape	A date is a date, not free text
Range and constraint	The value falls within bounds	An invoice amount is above zero
Consistency and cross-field	Two fields agree	End date is after start date
Uniqueness and referential	The value exists, or exists once	The cost centre exists in the master
Completeness and presence	A required value is there	Every invoice line carries a tax code

Most real problems are caught by the middle three. Type checks are usually enforced by the database anyway, and completeness checks catch obvious gaps. Cross-field and referential checks are where the interesting failures live, because they involve relationships no single field can police.

Where data validation happens

Validation is not a single step and should run at more than one point.

At the point of entry, where a user or interface first supplies the value. This is the cheapest place to catch a problem and the only place where the person who can correct it is present.

In the spreadsheet layer, where a great deal of business data is still assembled before it reaches a system of record.

During extraction and loading, where data crosses between systems. This is where structural problems surface, since the source and target rarely enforce identical rules.

At the reporting layer, as a final check that totals reconcile and expected volumes arrived. It cannot fix anything, but it prevents a wrong figure being acted on.

Diagram of the four points where data validation should run: at the point of entry where correction is cheapest, in the spreadsheet layer where business data is assembled, during extraction and loading where source and target enforce different rules, and at the reporting layer as a final check that totals reconcile and expected volumes arrived

Data validation in spreadsheets

Spreadsheets provide real validation features and they are worth using. Dropdown lists restrict a cell to an approved set of values, which prevents the variant spellings that make later matching impossible. Input messages explain what is expected before the user types, and error alerts intervene when the rule is broken, either blocking the entry or warning about it.

The limitation is scale and durability rather than capability. Validation rules attached to cells are lost when data is pasted in, are not applied to rows added below the defined range, and travel unevenly when the file is copied. A spreadsheet used by three people for one quarter is fine. The same file two years later, with forty contributors and no owner, is validating almost nothing. Where spreadsheets remain the working surface, Orbit Analytics connects them to a governed source through Excel reporting, which keeps the rules with the data rather than with the file.

How to write a good validation rule

State the condition, not the symptom. “Amount must be greater than zero” is a rule. “Report totals look wrong” is an observation that needs one.
Decide between reject, warn and quarantine. Rejecting stops the record entering. Warning lets it through with a flag. Quarantining holds it for review. Choosing deliberately is what separates a validation framework from scattered checks, and defaulting to warn means everything eventually passes.
Make the error message actionable. “Validation failed on row 4,217” sends someone hunting. “Cost centre 4412 does not exist in the cost centre master” tells them what to fix.
Version the rule with the data model. Rules encode assumptions about structure. When the structure changes and the rule does not, it either fails constantly or silently stops testing anything.

Data validation in ERP reporting

The highest-value checks in an ERP reporting context are not field-level at all. They are reconciliation checks that confirm a whole extract is complete and correct.

Row counts and control totals are the two that matter most. If a nightly extract from Oracle E-Business Suite pulled 48,000 invoice lines yesterday and 12,000 today, something failed, even though every one of those 12,000 rows may be individually valid. Comparing a summed amount against the source system’s own total catches the same class of problem from another angle.

Silent failures in scheduled refreshes are the specific risk. A refresh that errors is visible. A refresh that completes against a partial dataset is not, and every report built on it looks entirely normal. Orbit Analytics addresses this at the point of extraction, with its data pipeline applying reconciliation checks as data is pulled from Oracle sources, so an incomplete load is flagged rather than published. The transform stage of any ETL process is where these rules naturally sit.

Common data validation mistakes

Validating only on entry assumes data never changes afterwards. It changes constantly, through bulk updates, interfaces and corrections that bypass the entry form entirely.

Silent coercion instead of rejection is the most damaging. A system that receives an invalid date and quietly substitutes a default has converted a visible error into an invisible one, and the substituted value now looks like real data.

Rules that live in code nobody owns decay. The rule is correct when written, the business changes, and nobody knows the rule exists to revisit it.

Treating warnings as noise is the organizational failure. A validation report producing four hundred warnings every day trains everyone to ignore it, which means the one genuine warning is ignored too. Fewer, sharper rules beat comprehensive ones nobody reads.

Data validation vs. data verification vs. data cleansing

The three describe different activities at different points in a data’s life, and using them interchangeably makes conversations about data quality confusing.

Validation checks against rules. It asks whether the value satisfies conditions defined in advance. It happens before data is accepted and needs no external reference beyond the rules themselves.

Verification checks against a source of truth. It asks whether the value is actually correct by comparing it with an authority: confirming a bank account against a bank record, or a total against the originating system. Validation can confirm a date is well-formed; only verification can confirm it is the right date.

Cleansing repairs what is already wrong. It works on data that has been accepted and found defective, standardizing formats, removing duplicates and filling gaps. Cleansing is remedial, and a pipeline needing continuous cleansing usually has a validation problem upstream.

The three work together in sequence: validate at the boundary, verify what matters against an authority, and cleanse what got through before it reaches a report.

Comparison of data validation, verification and cleansing: validation checks data against rules defined in advance and runs at the boundary before data is accepted, verification checks values against an authoritative external source of truth, and cleansing repairs data that was already accepted and found defective, which is remedial work performed after the fact.

Frequently Asked Questions

Q1. What is data validation?

It is checking that data meets a set of rules defined in advance before it is accepted, stored or reported, covering conditions such as type, range, cross-field consistency and referential existence.

Q2. What are the main types of data validation?

Type and format checks, range and constraint checks, consistency and cross-field checks, uniqueness and referential checks, and completeness checks.

Q3. What is the difference between data validation and data verification?

Validation checks a value against predefined rules. Verification checks it against an authoritative source. A well-formed date passes validation; only verification confirms it is the correct date.

Q4. Where should data validation run in a data pipeline?

At more than one point: at entry, during extraction and loading, and at the reporting layer as a reconciliation check. Relying on entry alone misses everything that changes afterwards.

Q5. What happens when data fails validation?

It is rejected, flagged with a warning, or quarantined for review. Which of the three applies should be a deliberate decision per rule rather than a system default.

Q6. What is a control total check?

It compares a summed value or row count in the target against the same figure in the source. It catches incomplete loads that field-level validation cannot, because every individual row may be valid while the set is short.

An extract that completes against partial data is the failure mode field-level checks never catch. Orbit Analytics applies reconciliation checks as data is pulled from Oracle EBS and Fusion Cloud, so an incomplete load is flagged before anyone reports on it. Request a demo to see it on your own pipelines.