Skip to content

Data & Analytics

Data quality and validation: stop wrong numbers before they're reported

A half-loaded export doesn't throw an error. It produces a smaller number, and the smaller number goes in the report. Validation checks data at every boundary it crosses, and when a check fails, something actually happens.
Discuss your systemPick a fault and see what catches it ↓
On arrivalAfter transformationBefore publishingAccounting rows today: 0 · usual 180–240Weekly reportHeldFinance leadalertedSet aside · 0Set aside · 1Set aside · 2dupno date

Wrong numbers look like numbers

When software breaks, it usually says so. When data breaks, it doesn't. An export that only covered half the week, a duplicate file, a column a vendor renamed: each one produces a figure that is a little off and perfectly formatted.

By the time anyone notices, it's been in a report, a meeting and a decision. Validation moves the noticing to the start: each boundary the data crosses gets checks, and each check knows what to do when it fails.

Every fault has a check waiting for it

Four things that go wrong in ordinary weeks. Each one is caught by a check, with a decided next step.
  1. On arrival
  2. After transformation
  3. Before publishing
Caught by
Volume check: 0 rows, usual 180–240
What happens
Accounting is marked incomplete for Sunday. The other sources keep loading. The weekly report is held.
Alertto Finance lead

Sunday's accounting export has 0 rows (usual 180–240). The weekly operations report is on hold. Re-run the export, or release the report without accounting figures.

Six kinds of check

Between them, these catch most of what goes wrong. Which ones apply, and how strict they are, is set per source.

  1. 01

    Required fields

    Catches: the thing that should always be there

    A job with no date, an invoice with no amount.

    Watch for: “required” fields that the source lets people skip.

  2. 02

    Format and type

    Catches: values in the wrong shape

    Text in a number field, a date that can't be read, a phone number with letters in it.

    Watch for: formats that change with a user's settings.

  3. 03

    Duplicates

    Catches: the same thing twice

    A repeated file, a record entered in two systems, a retry that appended.

    Watch for: duplicates that differ only in spacing or case.

  4. 04

    Allowed values

    Catches: values nobody agreed on

    A status the rules don't know, a location that doesn't exist, a negative price.

    Watch for: new values that are legitimate and need a rule, not a rejection.

  5. 05

    Relationships

    Catches: references to nothing

    A job pointing at a missing customer, an invoice for a job that was deleted.

    Watch for: records that arrive before the thing they refer to.

  6. 06

    Volume and freshness

    Catches: too little, too much, too old

    Zero rows on a weekday, triple the usual count, a source that hasn't updated since Thursday.

    Watch for: thresholds set once and never revisited.

Every check needs a consequence

A check that only writes to a log hasn't protected anything. Each one is assigned what happens when it fails, in proportion to the damage the bad data could do.

  1. 01

    Note it

    When it fits: harmless oddities.

    Example: a phone number in an unusual format. Logged and counted.

  2. 02

    Set records aside

    When it fits: a few bad records, the rest fine.

    Example: two jobs with no customer, excluded and listed.

  3. 03

    Hold the report

    When it fits: the output would be misleading.

    Example: accounting missing for a day; the report waits.

  4. 04

    Stop the action

    When it fits: something irreversible depends on it.

    Example: invoices about to be sent from incomplete data.

Passing means the contract held

A passing check says the data met the agreement: the fields were there, the counts were normal, the references resolved. It doesn't say the agreement is right. Whether “completed” should include part-paid jobs is still the business's decision.

Every result is recorded with the load it checked. When something fails, the fix goes where the problem started, in the source or the rule, and the affected work is run again without duplicating what already succeeded.

What validation hands off

In

Contracts
fields · values · volumes
Records
staged or transformed
Timing
when each load is due
Validation
check · decide · record

Out

Accepted records
met the contract
Set aside
with the failed check
Alerts
to a named owner

Worth building when a wrong number has consequences

Good fit

  • Data feeds recurring reports, customer records or automated actions.
  • A wrong figure has gone out before, or would be expensive if it did.
  • Several sources mean several ways for data to break.

Not the right fit

  • No one can say what acceptable data looks like or own the failures. Agree the rules and owners before building checks around guesses.
  • The data is exploratory and nobody acts on it directly.

Where validation does its work

Next to validation

Questions about data quality and validation

Where should validation checks go?

At the boundaries: when data arrives, after it changes shape, and before anything uses it. A check at the end alone finds problems too late to say where they started.

Won't strict checks block reports all the time?

Not if each check's consequence fits its damage. Most failures set a few records aside and let the rest through. Holding a report is kept for problems that would make it misleading.

Who gets the alert?

A named person or role for each source and each report, agreed up front. An alert to a shared inbox is an alert nobody is responsible for.

Can you check data we already have?

Yes. Running the checks over existing data is often the first step. It shows how much is wrong today, and which rules are worth enforcing on new loads.