Validation rule

What is a validation rule?

A validation rule is a test that judges one value or one row and returns a single verdict: pass or fail. It does not change the data, it asks a yes-or-no question about it: is this invoice date in the past?

That pass-or-fail nature separates it from its neighbours. A mapping rule moves a value from a source field to a target field. A derivation rule calculates a new value, such as a margin. A validation rule produces no value, it only says whether the data is acceptable. All three often run in the same data pipeline.

Validation rules make data quality testable. "The data should be good" is not a check; "a delivery date cannot be earlier than the order date" is.

What a validation rule checks

Most rules line up with one of the standard data quality dimensions:

• Completeness. A required value is present, so a customer ID is not empty.

• Validity. A value fits the allowed format or range, or comes from an agreed code set.

• Uniqueness. A value appears only once, so no two rows share an invoice number.

• Consistency. Values agree, so a delivery date is not before the order date.

• Timeliness. The data arrived on time and the volume looks normal. A daily load with zero orders can pass every row-level check and still be wrong.

A rule that checks whether expected columns still exist also catches schema drift.

How validation rules are enforced

Encoding these checks in code is the job of data testing. In dbt, a data test is a SQL query that returns the rows breaking your assertion: zero rows means pass, any rows means fail. Its built-in tests include not_null, unique, accepted_values, and relationships.

Great Expectations does the same with an expectation, a verifiable assertion like expect_column_values_to_not_be_null. A relational database enforces it at write time with a CHECK constraint like CHECK (price > 0), rejecting any insert whose value fails.

What happens when a row fails

The check is the easy part. What teams skip is what to do with a failing row. There are four common answers.

• Reject the row. The record is dropped before the target, and lost silently unless you log it.

• Quarantine it. The row moves to a separate error table for review, while the good rows carry on.

• Flag it and continue. The row loads with a quality marker, so downstream users see it is suspect.

• Fail the whole load. The pipeline stops until the problem is fixed. Right for a broken source, too blunt for one odd value.

For a concrete case, a nightly invoice load has a data contract: each row needs a unique invoice number, a filled-in customer ID, a date no later than today, a positive total, and a valid currency. Four failing rows go to quarantine while the rest load. But if a quarter fail, the rules stop the load and alert the owner, since that signals a broken feed.