Data Validation

Bad data usually reveals itself late and in the worst possible room, because a number always comes out. An average still computes when blanks are mixed in. A total still lands when the same user appears on two rows. Without something that says the value on screen is wrong, a wrong value looks exactly like a right one.

Pagedeck learned this the hard way. In a monthly review Grace saw that paid conversion had doubled and asked what had changed, and Mia couldn't answer on the spot. Three days of digging turned up the cause: the data pipeline carrying payment records had been inserting some rows twice for the past ten days. No warning fired during those ten days, and the inflated number had been used as evidence in three separate meetings.

Data validation exists to remove those ten days. Instead of relying on someone noticing a strange figure, you write down the conditions a value has to satisfy and let a machine compare every incoming row against them. When a row breaks a condition, the check stops it or raises an alert right there.

Validation runs in three parts: write the rules, hang them somewhere along the path the data travels, and handle what gets caught. In practice that is five steps.

1. Choose the tables and columns to cover. Covering everything is unmanageable, so start with the tables feeding dashboards and billing. If data lineage is mapped, you can pick the tables with the most downstream weight first.
2. Write a condition per column. Conditions come in four shapes. Format rules check the shape of a value, like whether an email column contains an @. Range rules check the value itself, like a payment amount being greater than zero. Allowed-list rules check that a plan column holds only free, team, or enterprise. Uniqueness rules check that the same order number never appears on two rows.
3. Decide where the check runs. Either block at the entrance as data arrives, or scan after it lands. Blocking at the entrance keeps bad values out entirely, but the whole row gets thrown away. Scanning afterward preserves the original, but by then a calculation has already run on it once.
4. Decide what happens to a failing row. There are three options: let it through and send an alert, drop that row and continue, or halt the whole job. A negative payment amount is worth halting for; a blank country column is usually worth skipping past. The same rule can warrant different handling on different tables.
5. Name who looks at what gets caught. If alerts have no destination and no owner, they get muted within a month. This is where writing an owner beside every table under data governance earns its keep.

Rules are not written once. Add a new plan name and the allowed list has to change with it; skip that and perfectly good values start failing every day. A few weeks of false alarms and the real ones get ignored alongside them, which leaves the table in worse shape than having no check at all.

Validation works where a person can state in advance what a value must look like. A payment amount can't be negative. A signup date can't be in the future. A status column holds exactly five possible values. Conditions like these produce the same verdict every time once they're written down, which is what makes the check worth automating: the answer doesn't depend on who is looking or how carefully.

It does nothing for failures that can't be stated as conditions, and there are three common ones. First, every rule passes but the values themselves are wrong: send amounts in cents instead of dollars by mistake and every row clears both format and range while the figures come out a hundred times too large. Second, rows never arrive at all — validation only sees what showed up, so a missing day passes silently, and catching it belongs to completeness. Third, the values are fine but stale: yesterday's data still satisfies every rule, and that one belongs to freshness.

Anomaly detection divides along a different line. Validation asks whether a value crossed a boundary a person drew; anomaly detection asks whether it strayed from its usual distribution. The unit mistake above is exactly the kind of thing validation misses and anomaly detection catches.

Pushing toward ever denser rules costs more than it returns. Five conditions on every column produces hundreds of failures a day, of which a handful actually need fixing. Starting with the columns where a wrong value changes a decision, then adding rules of a given type each time an incident happens, is the version that survives past the first quarter.

"The country column isn't in this table's validation rules, so blanks have been coming through since last week."

Nobody noticed because no condition was ever written for that column.

"Validation caught 1,200 rows yesterday and most of them are the new plan name. Can you update the allowed list?"

It's a false alarm caused by a stale rule, not by bad data.

"Let's not just alert on negative payment amounts — have the pipeline halt."

An alert isn't enough for this condition; the downstream calculation should be blocked outright.

After the duplicate-row incident, Theo and Mia wrote the first set of rules together at Pagedeck. Theo started with format and uniqueness conditions, and the first week caught 400 rows — 380 of which came from test accounts. Once test accounts were excluded from the checks, the remaining 20 were the ones worth reading, and inside those 20 sat a bug that recorded some payment failures twice. Writing the rules took an afternoon; sorting out which failures were real took most of the following week, and that ratio is normal.

The most common mistake is treating a validation pass as proof the data can be trusted. The word "pass" invites that reading. But passing only means nothing broke a condition someone wrote, and it says nothing whatsoever about conditions nobody wrote. Amounts with a changed unit pass. A day of rows that never arrived passes. Miss this and a team installs validation, skips every other check, and takes the next incident from outside the rules entirely.

The second is treating validation as the data team's private chore. The rules get installed on the data side, but the conditions are known by whoever produced the values. How many plan names exist is a product and engineering question. Without that conversation, the data team ends up guessing at conditions, and every product change knocks the rules out of alignment until the alerts become noise.