Data Validation Techniques: 9 Checks and 4 Pipeline Gates

Key Takeaways

Data validation is a pass or fail verdict, not a cleanup. A rule accepts a record or rejects it. Profiling describes data, cleansing changes it, and monitoring watches it over time. Those are three different jobs and confusing them is why teams cannot say which one broke.
Nine techniques cover almost every rule you will write. Type, format, range, presence, length, uniqueness, referential, consistency and distribution. Anything else you write is a combination of those.
Every check sits in one of three layers. Schema checks test the shape of the data, content checks test the values inside it, and referential checks test the relationships between records. A team running only schema checks will happily pass a table where every price is zero.
There are four places a check can run, and most teams use one. At entry, at the source boundary, after transformation and before serving. The same rule costs a different amount at each, and entry is the cheapest of the four.
A failed record needs a disposition, not just an alert. Reject, quarantine, coerce or pass with a warning. Choose one per rule in advance and write it into the rule definition, because nobody invents a good answer at three in the morning.
Compliance turns validation into evidence. GDPR, the EU AI Act and the Basel Committee risk data principles all ask for a record of the checks that were run, not just a claim about the outcome.

What Data Validation Is, and Where It Stops

Data validation is the act of testing a record against a rule and returning a verdict: the record passes, or it fails. That is the whole definition. The rule can be as small as "this column holds an integer" or as large as "every order line points at an order that exists", but the output is always binary and always tied to one rule at one moment.

The reason the boundary matters is that three neighbouring jobs get called validation and behave nothing like it. Profiling measures what is in the data and says nothing about whether it is allowed. Cleansing changes values so they conform, which quietly destroys the evidence that they did not. Monitoring watches a metric over time and fires when it moves, which is a different question from whether any single record is legal. When someone says validation is broken, the first useful question is which of those four they mean.

Job	The question it answers	What it changes	What it cannot tell you
Validation	Does this record obey the rule?	Nothing. It returns a verdict.	Whether the rule was the right rule.
Profiling	What is actually in this column?	Nothing. It returns a description.	Whether any of it is allowed.
Cleansing	Can this value be made legal?	The value itself.	How many records were wrong before you changed them, unless you logged it.
Monitoring	Has this measure moved?	Nothing. It raises an incident.	Which specific record caused the move.

If you want the full taxonomy of check types rather than the operating model, we cover that separately in the types of data validation and why they matter. This article is about choosing between them and placing them.

Understanding the Importance of Data Validation

Validation is where data management stops being a matter of opinion. Without it, every downstream argument about a number becomes an argument about whose extract is right. With it, there is a rule, a verdict and a timestamp, and the argument takes ten minutes instead of a week.

Data validation lifts overall system reliability by detecting errors early, ensuring data integrity, and fueling precise business insights.

In a database that people depend on, validation is what stops one bad load from becoming a month of reconciliation. Catching a malformed record at the point it arrives costs one rejected row. Catching the same record after it has been joined, aggregated and published costs a rebuild of everything downstream of it, plus the conversation with whoever acted on the number in between.

The role of data validation in ensuring accuracy

Validation is the mechanism. Accuracy is the outcome you get when the mechanism runs often enough and in the right places. The two are not interchangeable, and treating them as one is why so many data quality programs stall: a record can pass every rule you wrote and still be wrong, because it satisfied the rules and the rules were incomplete. For what accuracy means as a business property, read why data accuracy matters for business success. For the operating steps a data engineer runs to raise it, read how to ensure data accuracy.

Trustworthy data. A number people stop arguing about, because the rule behind it is written down and the verdict is recorded.
Operational efficiency. Fewer reruns, fewer manual corrections, and fewer reconciliations between two systems that were always meant to agree.
Confident decisions. A dashboard that carries a validation state is a dashboard a director can act on without asking whether the load finished.
Regulatory evidence. A recorded check is the artefact an auditor asks for, and it is far easier to produce than a reconstruction after the fact.
Fewer errors caused by bad inputs. Most data incidents begin as a record that should never have been accepted in the first place.

The consequences of inaccurate data

Bad data does not stay in the table it landed in. It travels through joins into models, through models into reports, and through reports into decisions that are hard to reverse.

Decisions made on misinformation. The expensive failures are the ones where the number looked plausible, so nobody questioned it.
Operational waste. Invalid records cause failed jobs, retried loads and manual workarounds that quietly become permanent.
Customer trust. A wrong balance, a wrong address or a duplicate invoice is visible to the customer long before it is visible to the data team.
Regulatory exposure. Poor data handling is an enforcement risk in its own right, separate from whatever the data was used for.

The Three Layers of a Validation Check: Schema, Content and Referential

Almost every validation rule asserts one of three things: the shape of the data, the values inside it, or the relationship between one record and another. Naming the layer before you write the rule tells you where it has to run and, more usefully, what it will not catch.

Layer	What it asserts	What it catches	What it misses	Where it runs
Schema	Structure: which columns exist, their names, their data types, whether they accept nulls.	A column dropped or renamed upstream, an integer that arrived as a string, a new column nobody declared.	Every value error. A schema check passes a table in which every price is zero.	At the source boundary, before any transformation reads the table.
Content	The values in each field: type, format, range, presence, length, uniqueness.	Nulls in a required field, a date in 2087, a currency code that is not on the list, a duplicate customer id.	Anything that needs a second record to judge, such as an order id that is well formed and points at nothing.	Immediately after ingestion, and again after transformation.
Referential	The relationships between records and between tables.	Orphan rows, foreign keys that resolve to nothing, a child row loaded before its parent, a total that does not match the sum of its parts.	Values that are internally consistent and still wrong, because every record agrees with every other one.	After the full load completes. Never part way through a load.

The common failure is a team that instruments only the first layer, because schema drift alerts are cheap and mostly automatic. Those alerts are worth having and they are the least of what validation owes you. A table can hold exactly the right columns of exactly the right type and be made entirely of zeros.

9 Data Validation Techniques, What Each One Catches and What It Misses

These nine cover the rules most teams actually write. Each one names the layer it belongs to, so you already know where it has to run. The final column is the part most guides leave out, and it is the part that decides whether you need a second check.

Technique	Layer	Example rule	What it misses
1. Type check	Schema	order_qty is an integer	A quantity of minus 4,000, which is a perfectly valid integer.
2. Format check	Content	country_code matches a two letter pattern	A well formed value that is not a real country, such as ZZ.
3. Range check	Content	order_date falls between go live and today	A date inside the range that belongs to a different record.
4. Presence check	Content	customer_id is never null	An empty string or a literal N/A, both of which are present and useless.
5. Length check	Content	iban is between 15 and 34 characters	A string of the right length whose check digits do not compute.
6. Uniqueness check	Content	invoice_number appears once per legal entity	The same invoice sent twice under two different numbers.
7. Referential check	Referential	every order_line.order_id exists in orders	A parent row that exists and is itself wrong.
8. Consistency check	Referential	line amounts sum to the order header total	Two figures that agree with each other and are both wrong.
9. Distribution check	Content	daily row count stays inside the learned band	A shift small enough to sit inside the band, which is where slow corruption lives.

1. Type check: does the value fit its declared type

The cheapest check there is, and usually the one your warehouse already runs for you. It catches the class of error where an upstream system changes a column from numeric to text and everything downstream silently starts concatenating instead of adding. It says nothing about whether the number is sensible.

2. Format check: does the value match the pattern it should

Pattern rules cover email addresses, phone numbers, postcodes, currency codes, account numbers and identifiers with a defined shape. Write the pattern from the specification rather than from a sample of the data, because a pattern inferred from what arrived will accept whatever was already broken.

3. Range check: is the value inside the boundaries that make sense

Ranges apply to numbers, dates and times. The useful discipline is to set both ends. A rule that only checks the upper bound will pass an order dated 1970, and a birth date rule with no lower bound will pass a customer aged 300.

4. Presence check: is the field populated at all

A presence rule has to define what counts as empty for your systems. Null, empty string, a single space, N/A, UNKNOWN and 0 are six different things, and at least three of them usually mean the same thing in practice. Decide which ones the rule treats as missing and write it down.

5. Length check: is the value the size the target expects

Length rules matter most at the boundary between two systems with different column widths, where the symptom is silent truncation rather than a failure. Check the length before the load, not after, because after the load the evidence has already been cut off.

6. Uniqueness check: does this value appear only where it should

Uniqueness is nearly always conditional. An invoice number is unique per legal entity, an email is unique per active account rather than per row, and a product code is unique per catalog version. A uniqueness rule written without its qualifier will either fire constantly or catch nothing.

7. Referential check: does this key point at something that exists

This is the check that finds orphan rows, and it is the one most often skipped because it needs both tables loaded before it can run. Run it after the full load rather than part way through, or you will spend your time investigating rows whose parent simply had not arrived yet.

8. Consistency check: do the numbers agree with each other

Consistency rules encode the business logic that no schema can express: header totals match line totals, a closed ticket has a close date, a shipped order has a carrier. These are the rules that pay for themselves, and they are the ones that only the business can write.

9. Distribution check: does the shape of today match the shape of yesterday

Row counts, null rates, cardinality and value distributions catch the failures that no row level rule can see, because every individual record is legal. A load that arrives at ten percent of its usual volume passes every other check on this list. Set the band from the table own history rather than from a fixed number, or the rule will fire every Monday and be muted by Wednesday.

Where Validation Belongs in the Pipeline: The 4 Gates

The same rule costs a different amount depending on where it runs. Four positions are worth instrumenting, and most teams only use one of them.

Gate	What belongs here	Default disposition on failure	Who owns it
1. At entry, the form, API or upload	Type, format, presence, length and range, applied to one record at a time.	Reject, and tell the person or system that submitted it.	The application team.
2. At the source boundary, when data lands in the warehouse	Schema checks, plus row count and freshness on the batch as a whole.	Quarantine the batch and do not let it into the models.	The platform team.
3. After transformation	Content rules and business logic that only become checkable once the join has happened.	Fail the build and keep yesterday table serving.	The analytics engineer.
4. Before serving, the mart, API or feature store	Referential and consistency rules across the finished tables.	Pass with a warning, and hold the release when the rule is a hard one.	The data product owner.

Gate 1 is the cheapest place to catch anything and the only one where you can hand the problem back to whoever created it. Gate 3 is where most teams put everything, which is why so many incidents get found by a business user rather than by a test. If you are starting from nothing, put schema and freshness checks on gate 2 first: they take an afternoon and they catch the failures that cause the loudest outages.

Real time validation: what changes when you cannot batch

Real time data validation is the same nine techniques with two constraints added. The check has to finish inside the latency budget of the stream, and there is no second pass. Together those rule out most referential and consistency checks, because they need a record that has not arrived yet.

The pattern that works is to split the two. Run schema, type, format, presence, length and range inline, route anything that fails to a dead letter topic with the rule it broke attached, and run the referential and consistency checks in a reconciliation job that reads the same stream a few minutes behind. A real time pipeline that claims to run every check inline is either doing lookups against a store, which costs latency it has not budgeted for, or it is not running them.

What to Do With a Record That Fails

An alert is not a decision. Every rule needs a disposition written into it before it ships, because nobody invents a good answer at three in the morning. There are four, and the choice is per rule rather than per pipeline.

Disposition	What happens	When to use it	What it costs you
Reject	The record never enters the system and the sender is told why.	Entry gate rules, and any rule where a wrong value does more damage than a missing one.	Data loss, if the sender never retries. Only safe where the sender can retry.
Quarantine	The record is written to a side table alongside the rule it broke and the timestamp.	The default at ingestion, where you want the rest of the batch to continue and the evidence kept.	Somebody has to work the quarantine table. Without an owner and an age limit it becomes a slower way of deleting data.
Coerce	The value is changed to a legal one and the change is logged.	Only where the correct value is unambiguous: trimming whitespace, normalizing a country code, casting a numeric string.	It hides the upstream defect. Pair it with a daily count that a named person reads.
Pass with a warning	The record continues and a flag travels with it.	Advisory rules, and any new rule during its first month while you find out how often it fires.	A warning nobody reads is the same as having no rule at all.

Two rules hold nearly everywhere. Give every quarantine table an owner and a maximum age, or it turns into a landfill that makes the data look clean. And never coerce without logging a count, because coercion with no counter is how a systematic upstream error stays invisible for a year.

Data Validation Best Practices

Rules that are written down beat rules that live in one person head, and rules that run on a schedule beat rules that run when somebody remembers. Everything below is a way of making the nine techniques survive contact with a real team.

Define clear data validation rules

A rule is finished when someone who did not write it can implement it without asking a question. That means five things are recorded, not three.

The field and the layer. Which column, and whether the rule is schema, content or referential. The layer decides where it runs.
The condition, written as a predicate. Not "dates should be reasonable" but "order_date is between 2019-01-01 and the current date".
The disposition. Reject, quarantine, coerce or pass with a warning, chosen once and attached to the rule rather than decided during an incident.
The owner. A named person who is told when it fires and who is allowed to change it.
The reason. One sentence on what goes wrong downstream if the rule is removed. Rules without a reason are the first to be disabled and the last to be missed.

Implement automated data validation processes

Manual validation scales to about one analyst and one spreadsheet. Automation is not about running more checks, it is about running the same checks on every load without anyone deciding to. The practical shape is that structural checks activate themselves when a source is connected, while value and business rules are configured deliberately, because only your team knows what a legal value is.

The short video below covers exactly that choice: which monitor types exist, which ones switch on automatically when you connect a source, and which ones you have to define yourself. It also shows how a freshness or volume check can learn each table own pattern instead of firing against a fixed threshold, which is the difference between an alert people read and an alert people mute.

Regular data monitoring and auditing

Monitoring and auditing answer two different questions and both are needed. Monitoring asks whether today looks like yesterday and runs continuously. Auditing asks whether the rules themselves are still the right rules, and it runs on a calendar. A quarterly review that reads the firing rate of every rule will usually find two categories worth acting on: rules that have never fired, which are either perfect or wrong, and rules that fire constantly, which people have already learned to ignore.

Leveraging data profiling techniques

Profiling is how you find out what rules to write. Run it before you write a single rule and it will tell you the real null rate, the real cardinality, the real value list and the real date span of every column, which is almost never what the documentation claims. Profiling is not validation, because it makes no judgment, but a validation program built without it tends to encode the assumptions of whoever wrote the schema rather than the behavior of the data.

Using statistical analysis for data validation

Statistical methods earn their place at exactly one layer: the distribution check. Row counts, null rates, mean and variance drift, and cardinality changes are the failures no row level rule can see, because every record is individually legal. Keep the methods simple. A learned band on a daily row count catches more real incidents than a sophisticated model that nobody on the team can explain when it fires at midnight.

Tools and Technologies for Data Validation

Tooling for validation splits into four groups that solve different problems, and most teams end up with two or three of them rather than one. If you want the vendor by vendor view, we keep that in our guide to data validation tools for data engineers. What follows is what each category is actually for.

Introduction to data observability platforms

A data observability platform is the one that watches gates 2 and 4: it sits over your connected sources, raises an incident when a schema drifts, a load is late, a volume moves or a column health rule breaks, and tells you which downstream assets are affected. That last part is the difference between an alert and a decision. If the concept is new, we cover what data observability is and how it works in more depth.

Decube covers this layer with schema drift and job failure monitors that switch on when a source is connected, plus freshness, volume, field health and custom SQL monitors you configure per dataset, and column level lineage so an incident on one table immediately shows what it breaks downstream.

Using data quality tools for data validation

Dedicated data quality and testing tools are where row level rules live. Open source options such as dbt tests and Great Expectations put the rules in version control next to the transformation code, which is the right place for gate 3. Their limitation is that they only run when the pipeline runs, so they cannot tell you that a table stopped updating at all.

Leveraging machine learning for data validation

Machine learning is useful for one narrow job in validation: setting a threshold you cannot set by hand. Learning each table own freshness pattern and each column own volume band produces alerts that survive seasonality, month end spikes and a growing business. It is not useful for deciding whether a value is legal, because that is a business rule and a business rule has an author. Treat any tool that promises to find your data quality rules for you as a starting list to review, not a rule set to deploy.

Utilizing data governance platforms

A data governance platform is where the rule set becomes evidence. It holds the ownership, the classification and the policy that say which columns are sensitive, who is accountable for them and which checks are mandatory rather than advisory. Validation without governance produces alerts. Validation with governance produces an audit trail that shows which check ran, on which asset, on which date, and who owned the outcome.

Compliance Data Validation: What Three Regimes Actually Ask For

Compliance data validation is not a separate set of techniques. It uses the same nine checks with one extra requirement attached: the run has to leave a record. Regulators rarely specify a check. They specify an outcome and then ask you to evidence how you got there, which means an undocumented check that ran is worth roughly the same as a check that did not.

The EU AI Act is the most specific of the three. Article 10(3) of Regulation (EU) 2024/1689 states that training, validation and testing data sets for a high risk AI system shall be:

relevant, sufficiently representative, and to the best extent possible, free of errors and complete in view of the intended purpose.

Regime	What it asks of the data	The validation work that evidences it
GDPR, Regulation (EU) 2016/679, Article 5(1)(d)	Personal data must be accurate and, where necessary, kept up to date, with every reasonable step taken to erase or rectify inaccurate data without delay.	Presence, format and range checks on personal data fields, a dated log of every correction, and a freshness check that proves "up to date" is measured rather than assumed.
EU AI Act, Regulation (EU) 2024/1689, Article 10	Training, validation and testing data sets must be governed, examined for bias, and to the best extent possible free of errors and complete for the intended purpose.	Distribution and completeness checks on every training set, held per version, with the data preparation steps recorded alongside them.
BCBS 239, Basel Committee, 9 January 2013	Risk data aggregation must meet principles for accuracy and integrity, completeness, timeliness and adaptability.	Referential and consistency checks across the aggregation chain, plus a timeliness check that records when each input arrived, not just that it did.

The three above are the ones most often cited, and they are not the only ones that matter to a Decube customer. Firms we work with answer to OJK in Indonesia, APRA in Australia, MAS in Singapore and the NAIC in United States insurance, each of which asks for data lineage and data quality evidence in its own form. The practical answer is the same in every case: attach the rule, the run, the result and the owner to the asset itself, so the evidence is a query rather than a project. The full text of both EU regulations is public, and so are the Basel Committee principles, if you want to read what is actually required rather than a summary of it.

Ensuring data security and privacy during validation

Validation reads the data, which makes it a privacy surface in its own right. Three controls cover most of the exposure. Run checks in place rather than exporting a sample to somebody laptop. Store the rule and the verdict in the quarantine record rather than the failing value itself, or mask the value when the column is classified as sensitive. And apply the same access rules to the quarantine table as to the source, because a quarantine table is a copy of your worst data with none of the controls unless you put them there deliberately.

Addressing Common Challenges in Data Validation

Four problems come up on nearly every implementation. None of them is solved by adding more rules.

Dealing with missing or incomplete data

Decide what missing means before you decide what to do about it. Null, empty string, a space, N/A and 0 are separate states, and a presence rule that treats only null as missing will pass most of the real cases. Once the definition exists, missing data is a disposition question rather than a validation question: reject it at entry where the sender can fix it, quarantine it at ingestion where they cannot, and never quietly impute a value into a field that a person will later read as fact.

Handling ambiguous or inconsistent data

Ambiguity is almost always two systems using one word differently. The fix is upstream and social rather than technical: agree the definition, record it in the business glossary, and then write the validation rule against the agreed definition so the disagreement becomes visible the next time it happens. Writing a clever rule that accepts both meanings buries the problem instead of ending it.

Managing large volumes of data during validation

Validating a large table row by row on every load is usually unnecessary. Three techniques make it affordable. Push the check into the warehouse as SQL rather than pulling rows out to a validation service. Run row level rules on the incremental partition and aggregate rules on the whole table. And sample for expensive rules while keeping cheap rules at full coverage, so a rule that costs a full scan runs nightly while a null check runs on every load.

Conclusion: The Shortest Version of a Validation Program

If you take one thing from this article, take the three questions that every competing guide skips. Which layer is this check, schema, content or referential. Which of the four gates does it run at. And what happens to the record when it fails. A rule that answers all three is deployable. A rule that answers none of them is a good intention.

A workable first month looks like this. Profile your ten most used tables. Turn on schema and freshness checks at gate 2 for all of them, which is the afternoon of work that prevents the loudest outages. Write presence, range and uniqueness rules for the twenty columns that appear in board reporting. Give every rule an owner and a disposition. Then review the firing rate after four weeks and delete the rules nobody acted on, because a rule that is ignored is worse than no rule: it makes the whole set look like noise.

Validation is the mechanism behind data people are willing to act on. If you want to see what it looks like when the rules, the runs, the ownership and the downstream impact all sit on the same asset, you can book a walkthrough of Decube.

Frequently Asked Questions

What is data validation?

Data validation is the act of testing a record against a rule and returning a verdict: the record either passes or it fails. The rule can be as small as requiring a column to hold an integer or as large as requiring every order line to point at an order that exists, but the output is always binary and always tied to one rule at one moment. Validation is distinct from profiling, which describes data without judging it, from cleansing, which changes values, and from monitoring, which watches a measure over time.

What are the main data validation techniques?

Nine techniques cover almost every rule a team writes: type checks, format checks, range checks, presence checks, length checks, uniqueness checks, referential checks, consistency checks and distribution checks. Each belongs to one of three layers. Schema checks test the shape of the data, content checks test the values inside it, and referential checks test the relationships between records. Anything else you write is a combination of those nine.

What is the data validation process?

The data validation process has four steps. First, profile the data so the rules reflect what is actually there rather than what the schema claims. Second, write each rule with five things recorded: the field, the layer, the condition as a predicate, the disposition on failure and a named owner. Third, place each rule at one of the four pipeline gates: at entry, at the source boundary, after transformation or before serving. Fourth, review the firing rate on a calendar and delete the rules nobody acted on.

Where should data validation run in a pipeline?

There are four places a check can run and each has a different cost. Gate 1 is at entry, in the form, API or upload, where type, format, presence, length and range rules belong and where a failure can be handed straight back to whoever submitted it. Gate 2 is at the source boundary, where schema, row count and freshness checks belong and a failing batch is quarantined. Gate 3 is after transformation, where business logic that only becomes checkable after the join belongs and a failure fails the build. Gate 4 is before serving, where referential and consistency rules across finished tables belong.

What should happen to a record that fails validation?

Every rule needs one of four dispositions chosen in advance and written into the rule definition. Reject means the record never enters the system and the sender is told, which suits entry gate rules. Quarantine means the record is written to a side table with the rule it broke and a timestamp, which is the sensible default at ingestion. Coerce means the value is changed to a legal one and the change is logged, which is only safe where the correct value is unambiguous. Pass with a warning means the record continues with a flag attached, which suits advisory rules and any new rule in its first month.

What is compliance data validation?

Compliance data validation is the same set of checks with one extra requirement attached: the run has to leave a record. Regulators specify an outcome rather than a check, then ask you to evidence how you reached it, so an undocumented check that ran is worth about the same as a check that did not. GDPR Article 5(1)(d) requires personal data to be accurate and kept up to date. EU AI Act Article 10(3) requires training, validation and testing data sets to be free of errors and complete to the best extent possible. BCBS 239 sets principles for accuracy and integrity, completeness, timeliness and adaptability in risk data aggregation.

What is real time data validation, and what can it not do?

Real time data validation applies the same techniques inside a stream, with two constraints added: each check must finish inside the latency budget, and there is no second pass. Those constraints rule out most referential and consistency checks, because they need a record that has not arrived yet. The workable pattern is to run schema, type, format, presence, length and range checks inline, route failures to a dead letter topic with the broken rule attached, and run referential and consistency checks in a reconciliation job that reads the same stream a few minutes behind.

Is data validation the same as data accuracy?

No. Validation is the mechanism and accuracy is the outcome you get when that mechanism runs often enough and in the right places. A record can pass every rule you wrote and still be wrong, because it satisfied the rules and the rules were incomplete. Accuracy is a property of the data measured against reality, while validation is a verdict measured against a rule you chose.