Chapter 23 — Key Takeaways (Data Quality)
The page to work through when a mart is new, and to re-run when one is old.
The argument
A pipeline that fails costs a bounded delay, discovered in hours, borne by someone who can escalate. A pipeline that lies costs an unbounded error, discovered late, borne by people downstream of everyone who could have caught it.
All five of Kestrel's documented incidents had every job green. 31 days · 11 weeks · 6 days + 5 weeks · 8 months · 5 months. Not one was detected by a pipeline failure, because in not one did a pipeline fail.
📐 Say it in currency: $$0.5\% \times \$182{,}000{,}000 = \$910{,}000/\text{year that can be wrong without anyone looking}$$
And frame the choice as who is inconvenienced — yesterday's correct number, or today's possibly wrong one. A finance partner answers that immediately, and their answer is the policy.
Five dimensions are testable. Accuracy is not.
| Completeness · Uniqueness · Validity · Consistency · Timeliness | ✅ |
|---|---|
| Accuracy — is it true? | ❌ |
The three substitutes: reconcile against an independent source · compute it twice by different paths · unit-test the logic. Pretending an assertion about shape reaches truth is the central dishonesty of data quality tooling.
Pipeline versus data
The test: can this check fail while the pipeline is completely healthy? If not, it is a pipeline check with a data-shaped name — worse than no check, because it occupies the slot and inflates a pass rate somebody reads as evidence.
🏭 Three that look like data tests and are not: "the file is non-empty" · "the row count increased" (Chapter 1's incident increased it for 31 nights) · "it finished within its SLA" (Chapter 21 CS2 got 4.2× slower inside the window for eleven days).
The six that earn their keep
| Would have caught | ||
|---|---|---|
| 1. Grain | unique_combination_of_columns — nine lines, the highest-value test here |
Ch. 20 CS1's 3.4× overstatement |
| 2. Volume floor | far below expected — catch zero, not low | Ch. 19 CS1, $498,630.14 |
| 3. Freshness | on the source and on the mart | Ch. 19 CS2, 6 days of green |
| 4. Referential + unknown-member volume | relationships alone passes when unmatched keys resolve to -1 |
Ch. 19 CS2 |
| 5. Distribution | a null rate, a category mix, a mean in bounds | drift no per-row test reaches |
| 6. A business rule someone would argue about | requires deciding what is true — which is why nobody writes it | a logic change |
None is clever. All six are things a competent engineer agrees with when asked. They are missing not from disagreement but because nothing prompts a test for a failure you have not had yet — which is the argument for writing all six on day one, and against "we'll add tests as we find problems." The finding is the cost.
dbt versus a platform
A dbt test is a query returning zero rows, against a warehouse table, inside the dbt DAG. Three constraints hide there: it cannot see data that has not landed · it is pass/fail on a query · it has no memory.
| Use | For |
|---|---|
| dbt tests | ~80% of what you need |
dbt_expectations |
statistical checks inside the DAG — usually enough |
| Great Expectations | validation before the warehouse; distributional checks with history |
| a vendor tool | breadth across tables you do not own |
⚠️ The profiler writes the tests that cannot fail. accepted_values listing every current value,
bounds from today's min and max, a row count around today's. Use it as a first draft and delete most
of it. The failure mode of keeping them is alert fatigue, which ends with the suite ignored.
Placement, thresholds, mutes
Test early — a bad row gets more expensive as it moves. 📏 And weight toward the mart when the budget is tight, because a mart test's coverage is the whole lineage above it; a source test's is one table.
⚠️ A threshold that fires on Black Friday is disabled before Cyber Monday and never re-enabled. Kestrel's traffic varies 6.28×.
- Set for catastrophe, not anomaly — 3,000 against 17,753
- Make it relative — "within 60% of the trailing 28-day median for this day of week"
- Exclude known dates from a seed file
- Route by severity rather than suppressing
The metric that tells you a threshold is wrong is not its failure rate. It is whether anyone has muted it — and mutes are invisible. Audit them.
Anomaly detection is a net for the unknown, not a control for the known. It learns whatever it is shown, so a defect present before the baseline is invisible — Chapter 20 CS2's eight months of 0.037% loss exactly.
What happens to a bad row
Block (publishing is worse than stale) · quarantine (bad rows a minority, good ones useful) · flag (you genuinely cannot decide for the consumer).
🔁 A quarantine needs four things and usually has one:
- a monitored row count — growing quarantine is growing data loss
- an owner — "the data team owns it" is what "nobody owns it" looks like on an org chart
- an idempotent replay path, with
--dry-run - a retention decision, and announce the deletions
Without all four you have implemented WHERE quality_is_bad and told yourself otherwise.
What a test costs
Not compute. 313 tests = 6 minutes a night = $292 a year. Anyone declining a test on compute grounds is arguing about something else.
Attention · pass-rate dilution · alert fatigue · maintenance · and the test you did not write instead.
💸 Kestrel deleted 31 of 313 tests and went from 17 filled coverage boxes to 24. Five were rewritten: the author's concern was right and the instrument could not detect it. Audit against a fixed list of what should exist, not against what does — counting what you have tells you nothing about what is missing, and counting is what everyone does.
Metrics and ownership
Not the pass rate. Instead: coverage of the six (six boxes, one row per mart — an afternoon to build, the most actionable metric available) · data downtime (hours wrong or stale, whether or not noticed) · time to detection · mutes, with ages.
A test's owner is whoever can fix what it reports — often outside the data team, which is what
Chapter 17's owner and consumers fields are for. And the failure message should say what to do,
not what happened.
The register — 22 assertions, every one from a failure that happened
Grain · no keys dropped · volume floor · source freshness · mart freshness · unknown-member volume ·
anti-join completeness · run-twice equivalence · reconciliation vs a full rebuild · SCD2 no overlaps ·
SCD2 no gaps · SCD2 one current row · dimension growth ceiling · overlap > gap · no hardcoded
references · enum from the contract · declared schema at every read · output dtypes · peak memory
< 60% of limit · duration within 2× the median · skew < 10× · quarantine drained-owned-or-admitted.
Their combined cost exceeded two million dollars. None of them is difficult.
Three are not about data at all — memory, duration, skew — and they are in the register deliberately: an operational property that degrades silently produces a data failure eventually.
The property both case studies are about
A quarantine held 41,900 rows for fourteen months, of which 38,104 were valid wholesale orders
worth $1,070,341 — rejected by a BETWEEN 1 AND 100 bound derived from MAX(quantity) = 84.
A bound from observed data encodes what has happened; validation is a claim about what is allowed,
and they differ at the tails. Ask a person. A round bound is a guess with a confident face. When you
must guess, guess loose.
A freshness check was muted for 511 days by a duration string the parser did not recognize, where
None meant forever. A second muted check had been firing correctly, daily, on a real defect, for
47 days — and nobody could see it, because muting suppressed the evaluation's result rather than
just the notification.
📐 Measure whether a control is OPERATING, not whether it EXISTS.
A test that cannot fail · a threshold that cannot fire · an alert to an archived channel · a freshness check never invoked · a quarantine never drained. Five failures, five chapters, one property — and every one of them passes an audit that counts artifacts.