You can tell a data pipeline is in trouble when the dashboard is technically up, the numbers are technically present, and the business still can't trust a single row on the screen. One bad reference value, one missing field, or one record that slips past a loose check is enough to make a Friday review turn into an archaeology session. That's usually the moment people realise the problem isn't the report, it's the absence of data validation rules that were designed, tested, and governed like real production code.

Validation works best when you stop treating it like a clerical task and start treating it like a contract. Eurostat's framing is blunt, if a value combination isn't in the accepted set, it fails, and the rule must be unambiguously defined and documented so producers and consumers share the same understanding of the check being applied, which is a much cleaner basis for auditability and data governance than a vague best-effort filter (Eurostat data validation guidance). If you want the same idea in a practical product context, this overview of data validation makes the lifecycle more concrete without softening the operational reality.

Why Your Data Is Quietly Lying to You

The worst data failures rarely announce themselves. A report still loads, the chart still renders, and everyone assumes the numbers are “close enough” until an exception bubbles up during a meeting and somebody has to explain why yesterday's figures don't line up with today's extract. Manual spot checks don't scale to that kind of problem, because the bad rows usually hide inside the normal-looking ones.

Validation is a pact, not a filter

That's why record-level validation matters. It turns a fuzzy expectation into a shared rule, and it gives engineering and business teams the same language for failure. Eurostat defines data validation as checking whether a combination of values belongs to an accepted set, and its guidance says the rules must be clearly and unambiguously defined, documented, and fit for purpose so everyone can interpret the result the same way (Eurostat data validation guidance).

A failed validation should tell you what business rule was broken, not just that the row annoyed the database.

That distinction changes how teams work. A bad record is no longer just “dirty data”, it's evidence that a business constraint, a schema assumption, or a reporting obligation has been violated. This is the core value of data validation rules: they create measurable control points instead of turning quality into a hand-wavy promise.

The operational payoff is predictability. When the rules are documented and enforced consistently, data producers know what they need to send, and consumers know what kind of data they're allowed to trust. If you've ever watched a pipeline “mostly work” for months before a hidden edge case detonates a dashboard, you already know why this discipline pays for itself.

The Anatomy of Record-Level Validation Rules

Before writing code, you need a map of the rule families you will use. The standard workflow groups checks into format validation, range checks, schema validation, and cross-field validation, which is a useful starting point because it forces you to separate syntax problems from business-logic problems (Atlan's data validation workflow). That separation keeps your framework understandable, which matters more than sounding advanced.

Start with the simple checks

The easiest rules are usually the first ones you should ship. Null presence, data type, and format validation catch the kind of mistakes that are cheap to reject and painful to clean later. If an email doesn't match the expected pattern, or a date arrives in the wrong shape, there's no value in letting that record drift downstream just because the rest of the row looks fine.

Add numeric and domain constraints next

Range checks are where validation begins to feel like business policy instead of field hygiene. A price should be positive, a discount shouldn't exceed the price, and an age field should sit inside an acceptable range if the business domain requires one. For pet care workflows, that same logic is useful when you're ensuring compliant pet health records, because a missing or malformed field can break the chain of accountability even when the record “loads”.

Don't forget relationships between fields

Cross-field validation is where people usually get burned. A shipping country can't be treated in isolation if another field is supposed to carry a country-specific identifier, and a status flag may only make sense when paired with a matching date or code. These rules are harder to write than single-field checks, but they're also the ones that catch the subtle contradictions that would otherwise make reporting look internally consistent while still being wrong.

Practical rule: if a field only makes sense in context, validate it in context.

Referential integrity sits beside cross-field logic, even if it feels more database-native than business-native. A foreign key that doesn't point to a real parent row is still a business failure, not just a storage issue. That's why domain-specific validation rule sets often include explicit checks such as positive prices, no negative observations, and unique observations, all written as machine-checkable conditions that keep automated QA interpretable for engineers (SNStatComp Domain Validation Rules).

You can also use a platform-oriented view of this anatomy if you need a broader enterprise pattern. The record-level model in digna's enterprise validation approach is useful precisely because it maps these rule families to execution inside the database rather than leaving them as abstract policy notes.

From Logic to Code

Theory only matters once the rule can reject a bad order without making good orders harder to process. Suppose you're validating an incoming orders record with order_id, customer_id, price, discount, shipping_country, and nif. You want the code to be readable enough that the next engineer doesn't have to reverse-engineer the business rules from a pile of side effects.

Make the rule obvious in SQL

A clean CHECK constraint is still the best first line for simple field logic.

CREATE TABLE orders (
    order_id           BIGINT PRIMARY KEY,
    customer_id        BIGINT NOT NULL,
    price              DECIMAL(10,2) NOT NULL,
    discount           DECIMAL(10,2) NOT NULL DEFAULT 0,
    shipping_country   CHAR(2) NOT NULL,
    nif                VARCHAR(20),

    CONSTRAINT chk_price_positive
        CHECK (price > 0),

    CONSTRAINT chk_discount_non_negative
        CHECK (discount >= 0),

    CONSTRAINT chk_discount_not_exceed_price
        CHECK (discount <= price)
);

CREATE TABLE orders (
    order_id           BIGINT PRIMARY KEY,
    customer_id        BIGINT NOT NULL,
    price              DECIMAL(10,2) NOT NULL,
    discount           DECIMAL(10,2) NOT NULL DEFAULT 0,
    shipping_country   CHAR(2) NOT NULL,
    nif                VARCHAR(20),

    CONSTRAINT chk_price_positive
        CHECK (price > 0),

    CONSTRAINT chk_discount_non_negative
        CHECK (discount >= 0),

    CONSTRAINT chk_discount_not_exceed_price
        CHECK (discount <= price)
);

CREATE TABLE orders (
    order_id           BIGINT PRIMARY KEY,
    customer_id        BIGINT NOT NULL,
    price              DECIMAL(10,2) NOT NULL,
    discount           DECIMAL(10,2) NOT NULL DEFAULT 0,
    shipping_country   CHAR(2) NOT NULL,
    nif                VARCHAR(20),

    CONSTRAINT chk_price_positive
        CHECK (price > 0),

    CONSTRAINT chk_discount_non_negative
        CHECK (discount >= 0),

    CONSTRAINT chk_discount_not_exceed_price
        CHECK (discount <= price)
);

That gives you a crisp failure mode for the obvious cases. It also keeps the rule close to the data model, which is important because a hidden validation rule buried in application code is easy to forget and hard to audit.

Use cross-field logic when one column depends on another

Some rules don't belong in a bare CHECK because they need conditional behaviour. In that case, a CASE expression or trigger-style logic is clearer than trying to squeeze the whole business rule into a single predicate.

SELECT
    order_id,
    CASE
        WHEN shipping_country = 'ES' AND nif IS NULL THEN 'FAIL'
        WHEN shipping_country = 'ES' AND LENGTH(nif) < 5 THEN 'FAIL'
        ELSE 'PASS'
    END AS validation_result
FROM staging_orders;

SELECT
    order_id,
    CASE
        WHEN shipping_country = 'ES' AND nif IS NULL THEN 'FAIL'
        WHEN shipping_country = 'ES' AND LENGTH(nif) < 5 THEN 'FAIL'
        ELSE 'PASS'
    END AS validation_result
FROM staging_orders;

SELECT
    order_id,
    CASE
        WHEN shipping_country = 'ES' AND nif IS NULL THEN 'FAIL'
        WHEN shipping_country = 'ES' AND LENGTH(nif) < 5 THEN 'FAIL'
        ELSE 'PASS'
    END AS validation_result
FROM staging_orders;

That pattern makes the rule explicit. If the shipping country is Spain, the record needs the expected identifier, and if it doesn't, you get a direct failure signal instead of a cryptic downstream exception.

Test uniqueness and referential integrity with queries

Uniqueness checks are easier to maintain when they're written as direct diagnostics instead of clever procedural code.

SELECT order_id
FROM staging_orders
GROUP BY order_id
HAVING COUNT(*) > 1;

SELECT order_id
FROM staging_orders
GROUP BY order_id
HAVING COUNT(*) > 1;

SELECT order_id
FROM staging_orders
GROUP BY order_id
HAVING COUNT(*) > 1;

And referential integrity is just as direct.

SELECT s.customer_id
FROM staging_orders s
LEFT JOIN customers c
  ON s.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

SELECT s.customer_id
FROM staging_orders s
LEFT JOIN customers c
  ON s.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

SELECT s.customer_id
FROM staging_orders s
LEFT JOIN customers c
  ON s.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

Those queries are boring in the best possible way. They tell you exactly what failed, which is why domain-specific validation rules are often implemented as explicit record-level checks such as positive prices, no negative observations, and unique observations, all of which can be executed at volume while staying interpretable to engineers (SNStatComp Domain Validation Rules).

If you want a platform example of this style, digna's manual rule maintenance discussion is worth reading alongside your own SQL patterns, because the useful question isn't whether the rule exists, it's how safely it can be maintained over time.

Keep the error message useful

A bad rule with a vague message just creates extra work. “Validation failed” helps nobody, while “shipping_country=ES requires nif” gives the analyst and the engineer the same starting point for triage. In practice, the message is part of the rule, not decoration.

Beyond It Runs Smarter Testing Strategies

A validation rule that hasn't been tested is just a theory with a production budget. Treat it like application code, because that's what it is once it can block data, alert on failures, or feed compliance workflows. The cost of being wrong here is ugly, since a broken validation rule can reject good data or, worse, let bad data sail through with a clean bill of health.

Test the rule in layers

A practical approach starts small. Unit tests use a tiny set of curated records, some valid, some invalid, so each rule can be checked in isolation. That's where you catch the obvious mistakes, the off-by-one threshold, the inverted null check, or the rule that accidentally flags legitimate edge cases.

Integration tests belong in the pipeline itself. They show you how the rules behave when staging, transformation, and loading are all in play, which is where a lot of tidy logic starts acting messy. Regression tests then protect existing behaviour whenever a rule changes, so you don't “fix” one validation and inadvertently break three others.

Separate file-level failures from record-level failures

The most useful operational pattern I've seen is the one used in EU transaction reporting, where schema-level syntax rules reject the whole file and content rules reject only the invalid transactions. That split keeps the blast radius smaller and reduces unnecessary resubmissions (ESMA MiFIR transaction reporting validation rules).

Don't make a whole batch suffer for one bad row unless the structure itself is broken.

That principle is easy to state and surprisingly easy to ignore. If the file is malformed, stop early. If only one transaction violates business logic, quarantine or flag the record and let the rest continue, assuming your process and compliance requirements allow it.

For root-cause work, this guide to analysing data issues with AI can help teams move from “something failed” to “this specific pattern caused it” without turning every incident into a manual investigation.

A simple test matrix

• Valid happy path: the order should pass every rule.

• Known bad record: the order should fail exactly one named rule.

• Boundary case: the rule should behave correctly at the edge value.

• Regression sample: a previously accepted record should stay accepted after a rule edit.

That's enough to keep the rule honest without overcomplicating the harness. If you can't explain why a test exists, you probably don't need it.

Deploying and Managing Rules Without the Headaches

Writing rules is the easy part. Living with them is where the core design work starts, because every business-policy change risks breaking old assumptions. A validation framework that can't be versioned, explained, and re-run safely will eventually become the thing everyone avoids touching.

Choose your deployment pattern deliberately

The Strict Gatekeeper blocks bad records before they enter downstream systems. That's the right choice for high-trust pipelines, regulated reporting, and anything that would create a mess if invalid data spread further.

The Observant Monitor lets the data through but flags, quarantines, or routes failures for review. That's useful when availability matters more than immediate rejection, or when you need to observe the shape of the problem before hardening the control.

Neither pattern is universally right. Gatekeeping gives you tighter control, while monitoring buys you resilience and visibility. Most mature teams end up using both, because not every dataset deserves the same level of friction.

Make the rule lifecycle explicit

A rule should have a version, a change note, and a clear owner. It also needs logs that show when it ran, what it evaluated, and how it failed or passed, because that's the only way to support audits without turning every incident into a detective story. Eurostat's guidance is useful here too, because it says the rules and even their error messages should be documented so everyone interprets failures the same way (Eurostat principles).

The big maintenance problem is rule drift. Business policy changes, source systems evolve, and what once was a sensible threshold can turn into a nuisance or a blind spot. ArcGIS's validation workflow is a good reminder that validation is operational, not static, because rules often need evaluation, inspection, and re-evaluation after edits, especially when schemas and reporting conditions change over time (ArcGIS validation attribute rules).

Where a platform can reduce the pain

A platform can help when the volume of rules, systems, and exceptions starts to outgrow spreadsheets and ad hoc scripts. One option is digna, which uses deterministic business and technical validation logic with in-database rule execution and complete audit trails, so checks run where the data lives and the evidence is kept for compliance and regulatory review (digna platform introduction).

That matters because validation is only useful if people can trust both the outcome and the process. A good UI is handy, but the deeper win is traceability, the ability to answer who changed what, what failed, and why the record was handled the way it was.

If you're also comparing observability and rule enforcement options, protecting your research data is a useful adjacent read because it keeps the focus on integrity rather than just alerting.

The Endgame Validation as a Pillar of Data Trust

A good rule starts as a business need, becomes code, gets tested, and eventually lives as a monitored production asset with an owner and an audit trail. That lifecycle is the difference between random checks and a real governance posture. Once the organisation sees validation as a system, not a patch, trust gets easier to earn and easier to defend.

The healthiest teams stop asking whether they should validate data and start asking where each rule belongs, how it's versioned, and what happens when it fails. That shift is what turns data engineers into guardians of integrity instead of cleanup crews. If you need a broader culture-level framing, this guide to building data quality habits connects the technical controls to day-to-day ownership in a useful way.

The end goal isn't perfect data, because that doesn't exist. The end goal is predictable data, visible exceptions, and a process that tells the truth quickly enough for analysts, auditors, and operators to act on it. Once those parts are in place, validation stops being overhead and starts becoming one of the quiet reasons your analytics, compliance, and automation hold together.

A CTA for digna.

For running these record-level rules inside the database, with an audit trail of what failed and why, see digna Data Validation.

Frequently asked questions

What are the main types of data validation rules?

The article groups rules into format validation, range checks, schema validation and cross-field validation, with referential integrity alongside. Start with simple null, type and format checks, add numeric and domain constraints such as positive prices, then write cross-field rules for fields that only make sense in context.

How do you write a data validation rule in SQL?

For simple field logic, use a CHECK constraint in the table definition, such as CHECK (price > 0) or CHECK (discount <= price). Conditional rules fit better in a CASE expression, for example failing an order when shipping_country = 'ES' and nif IS NULL, which keeps the business rule explicit.

How do you test data validation rules?

Test them like application code, in layers: unit tests on small curated sets of valid and invalid records, integration tests inside the pipeline, and regression tests whenever a rule changes. A simple matrix covers a happy path, a known bad record, a boundary case and a regression sample.

Should a validation failure reject the whole file?

Only when the structure itself is broken. Following EU MiFIR transaction reporting practice, schema-level syntax errors reject the whole file, while content rule failures reject only the invalid transactions. That split keeps the blast radius small and avoids unnecessary resubmissions when a single row violates business logic.

What is the difference between gatekeeper and monitor validation?

The Strict Gatekeeper blocks bad records before they reach downstream systems, suiting regulated reporting and high-trust pipelines. The Observant Monitor lets data through but flags, quarantines or routes failures for review, which suits cases where availability matters more. Most mature teams use both, since not every dataset deserves equal friction.