Data Quality & Validation
How do you check that data is right, when "right" is a claim about the world and not about the bytes?
Who this mechanism is for: a developer who writes code, builds or uses data pipelines, and knows basic SQL, but has never designed a data-quality programme. You do not need database-internals knowledge. Every technical term is explained the first time it appears, and again in the glossary at the end. Small formulas appear with worked arithmetic, so you can check them yourself.
The story we will follow
Ravi is a data engineer at Freshlane, a grocery-delivery company. Customers order through a mobile app. Three partner supermarkets send Freshlane a daily price-and-stock file. A payment provider settles the money. Every night a pipeline of 14 steps turns raw orders into tables in a data warehouse. From those tables, three things are produced: a finance report (read by the CFO and an auditor), a dashboard (read by about 40 managers) and a recommendation feed (read by the app). Anita is the finance analyst who reads these numbers and has to defend them in meetings.
During one quarter Ravi meets six problems, one at the start of each of From proof to hypothesis, The free tier, Beyond one record, Gates, quarantine, stop-the-line, Deduplication and The reconciliation ladder below. Each problem looks like a bug in code. Each one is actually a structural property of checking data. At the end of each article we go back and solve the problem with that article's mechanisms. What quality can claim closes the story.
This is the main idea of this whole mechanism:
How this mechanism is organized
- 1
From proof to hypothesis
Why is checking data different from checking a disk write, and where in a pipeline should checks live?
Case14 sets of checks, and a price 100 times too big
- 2
The free tier
Why did warehouses stop enforcing primary keys, and what can a check at parse time prove?
CaseA primary key, and duplicate customers
- 3
Beyond one record
Why do some checks fire false alarms by design, and why does alert fatigue come from arithmetic?
CaseAlarms at 2 a.m. that are gone by noon
- 4
Gates, quarantine, stop-the-line
What should a failed check do, and what does each action cost?
Case1,000 orders set aside, and revenue 4% low
- 5
Deduplication
Why is "same person?" a matching problem with an error rate, and why can a small threshold change merge thousands of people?
Case2,300 people merged into one customer
- 6
The reconciliation ladder
What changes when the reference is a second system, how do you find five wrong rows among a billion, and what do audits, diffs and sampling really give you?
CaseA ₹4.2 lakh gap, and an argument every morning
- 7
What data quality can claim
What is the single claim of this mechanism, and what is still unsolved?
CaseCan we say our numbers are correct?
After that: a summary, hands-on labs, interview questions and a glossary.
One request: do not skip the small calculations. This mechanism quotes defect rates such as 10⁻⁴ and false-alarm counts such as "12 a day". The arithmetic is where the surprising conclusions come from.
What do I do, and what does each option cost?
Can I see it myself?
Per-value checks ride on the parse
Time parsing a 5-million-row CSV alone, with checks in the same pass, and with a separate checking pass.
MeasurePythonThe false-alarm floor of a cross-table check
Two loaders on different schedules: the orphan-order rate a referential check reports, and how allowed lateness removes it.
SimulatePythonP-values measure your row count
A 0.07% shift tested with KS at 10³ to 10⁷ rows: the p-value collapses with n, the PSI does not.
SimulatePython with NumPy, SciPyAlert fatigue by arithmetic
12,000 checks at a 0.001 false-alarm rate for 90 days, with and without a Bonferroni correction, against weekly real defects.
SimulatePython with NumPy, SciPyHow many rows must you sample, and what does blocking cost?
How large a sample finds a rare defect, against 1 − (1 − p)ⁿ and n ≈ 3/p, and how blocking caps the recall of duplicate search.
SimulatePython with NumPyFind five bad rows among a million with a Merkle tree
Find 5 changed rows among a million with a Merkle tree, then see why a XOR table hash cannot see duplicated rows.
SimulatePython
Can I reason about it under questioning?
Interview questions
Short questions on data quality, such as where checks belong, constraints and schema checks, cross-table and statistical checks, gates and quarantine, deduplication and reconciliation, each with an answer you could give aloud and a link to the full explanation.
Scenarios
Longer problems on data quality, of the kind asked in senior interviews: each one estimated with numbers, diagnosed, and answered with its trade-offs.
The tensions this mechanism takes a position on. Each recurs across mechanisms.
Ideas this mechanism meets that keep coming back, mechanism after mechanism.
Absence is not evidence
Absence of an event is not absence of a failure.
Variance costs more than volume
Variance costs more than volume.
Which way the error points
Designate one write
Atomicity over n items is atomicity over one designated item.
Independence is assumed
Independence is assumed everywhere and is true almost nowhere.
Show all 9 principlesShow fewer
Fixed cost per operation
Fixed cost per operation implies a natural batch size.
Masking moves failure
Masking a failure moves it from a channel with a schema to a channel without one.
Sound approximations
When exactness is too expensive, use a sound approximation.
The end-to-end argument
Guarantees compose to the weakest link, and only endpoint guarantees are real.