Types of Data Validation Explained

Different validation types catch different failures

A single validation rule cannot detect every data quality problem because bad data fails in different ways. A value can be present but the wrong type. It can be numeric but outside the allowed range. It can match a pattern but not exist in the reference table. It can pass every technical check and still break a business rule.

That is why production ETL processes use several data validation types together. Each rule answers one question. Is the value present? Is it the right type? Does it fit the target length? Does it match the expected format? Is it unique? Does it point to valid reference data? Does the row make business sense?

This article focuses on choosing and comparing validation types. If you need the broader definition and process, start with What is Data Validation?. If you want the full list of validation articles, use the Data Validation hub.

The practical goal is not to collect rules like commemorative teaspoons. The goal is to protect the target system and the decision that depends on it. A payroll import needs different checks from a marketing list. A product catalogue needs different checks from a warehouse stock update.

In most cases, the right validation design starts with the target contract. Target fields, database constraints, reporting rules, lookup tables, and business ownership tell you which validation techniques matter. The source system tells you where the trouble is likely to arrive wearing a false moustache.

Required Field Validation

Definition: Required field validation checks that a field contains a value when the workflow, target table, report, or business process cannot continue without it.

Example: Reject a sales row when `customer_id` is blank. In ETL, this is often the first gate after extraction. A missing customer ID, order number, invoice date, or account code makes later checks less useful because the workflow has nothing reliable to join, group, update, or report against.

When to use it: Use it for primary keys, business keys, dates, amounts, reference codes, and fields needed by target database `NOT NULL` columns. For setup details and edge cases, read Required Field Validation.

Range Validation

Definition: Range validation checks that numbers, dates, or other ordered values fall between allowed minimum and maximum limits.

Example: Flag a discount of `500` when allowed discounts are 0 to 100 percent. It catches values that are technically valid but operationally wrong. The amount may be numeric. The date may parse. That does not mean the amount belongs in the invoice or the date belongs in the current reporting period. This is where validation stops nonsense with a straight face.

When to use it: Use it for financial values, quantities, percentages, periods, dates, counts, ratings, and any field with known business limits. For setup details and edge cases, read Range Validation.

Format Validation

Definition: Format validation checks that a value follows the expected layout before it is parsed, converted, stored, exported, or compared.

Example: Accept `2026-07-21` and reject `21/07/26` when the target requires ISO dates. Format checks are common for dates because `01/02/2026` can mean different things depending on who exported the file and how optimistic they were feeling. They also matter for filenames, identifiers, timestamps, and structured text passed to APIs.

When to use it: Use it when the target system expects a specific date format, timestamp format, file naming convention, ID layout, or export structure. For setup details and edge cases, read Format Validation.

Uniqueness Validation

Definition: Uniqueness validation checks that a field or field combination appears only once where duplicates would create incorrect updates, double counting, or unreliable reporting.

Example: Reject a second row with the same invoice number in the same supplier file. This is not only about database primary keys. In ETL, duplicates can appear inside one file, across several files, or between a new feed and existing target records. Duplicate rows are like relatives at Christmas. They keep showing up whether anyone planned for them or not.

When to use it: Use it for keys, transaction identifiers, event IDs, invoice numbers, imported files, and any measure that would inflate totals if repeated. For setup details and edge cases, read Uniqueness Validation.

Consistency Validation

Definition: Consistency validation checks whether values make sense together across fields, records, files, or systems.

Example: Flag a closed order that still has an open fulfilment status. A single field may pass its own rule while the record still makes no sense. A delivery date can be valid, and an order date can be valid, but the delivery date should not usually be before the order date unless your logistics team has borrowed the DeLorean.

When to use it: Use it when several fields describe the same business event, status, period, customer, product, or financial transaction. For setup details and edge cases, read Consistency Validation.

Cross-Field Validation

Definition: Cross-field validation compares fields in the same row or logical record to confirm that the relationship between them is valid.

Example: Check that `line_total` equals `quantity * unit_price` after rounding. It is closely related to consistency validation, but it usually works inside one record. The workflow might compare two dates, match a total against component values, confirm that a currency matches a country, or check that two related identifiers agree.

When to use it: Use it when the correctness of one field depends on another field in the same record. For setup details and edge cases, read Cross-Field Validation.

Production ETL workflows combine several validation types

Real workflows rarely depend on one validation type. One rule catches one failure mode. A reliable ETL process chains checks together so obvious defects are caught early and expensive checks run only when the row is worth examining.

Consider a sales import from a supplier CSV into SQL Server. The workflow might run these checks:

1. Confirm the file exists, has the expected header, and has not already been processed.
2. Check required fields such as supplier ID, invoice number, product code, quantity, amount, and invoice date.
3. Validate data types for dates, integers, decimals, and booleans.
4. Check ranges for quantities, prices, discounts, and invoice dates.
5. Apply length and pattern checks to product codes, account references, and invoice numbers.
6. Use lookup tables to confirm supplier, product, currency, and account values.
7. Detect duplicate invoice numbers inside the file and against loaded history.
8. Apply business rules for closed accounting periods, inactive suppliers, and approval thresholds.
9. Route rejected rows to an exception file with the failed rule and source row number.
10. Load accepted rows and write counts for source, passed, failed, and loaded records.

This pattern keeps each rule understandable. It also makes support easier. When a row fails, the log should say why. "Import failed" is not a diagnosis. It is a shrug wearing a tie.

Best practices for choosing validation types

Good validation rules are explicit, testable, and owned. They protect the target without turning the workflow into a bureaucratic obstacle course.

• Start with the target contract: required fields, types, lengths, keys, constraints, and accepted values.
• Keep raw source files and source rows unchanged for audit and recovery.
• Validate basic structure before expensive transformations or database writes.
• Use simple validation rules first, then add complex checks where risk justifies them.
• Separate row-level failures from job-level failures so one bad row does not always stop the whole process.
• Write rejected rows to a controlled exception table or file with the failed rule and source row reference.
• Use lookup tables for approved values rather than hard-coding lists inside several workflows.
• Validate duplicates inside the current file and against previously loaded data when double processing matters.
• Test edge cases: blanks, spaces, zero values, negative values, maximum lengths, old dates, future dates, and duplicate keys.
• Review rules with business owners when source systems, targets, products, periods, or compliance requirements change.
• Log passed rows, failed rows, warning rows, rejected files, and loaded rows on every run.
• Keep warning rules separate from rejection rules so important failures do not hide in noise.

The useful rule of thumb: validate what would be expensive, embarrassing, or risky to repair after loading. If the defect does not matter, do not build a shrine to catching it.