Data Quality
Every task green, every table populated, and the number still wrong. What "correct enough to trust" means, and why no single check establishes it.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
The run succeeded, the tables have rows and the dashboard renders. How do I know the data is correct enough to trust?
A finance team closing a month and signing the result, an analyst comparing this week with last, a pricing model retraining on last quarter, an executive making a headcount decision from one tile. None of them can inspect your pipeline. All of them read its output and assume somebody checked it.
A quality claim is only meaningful at a stated grain. "Complete" against fct_orders means every order arrived; run the identical count against a table that is one row per order *line* and the check passes while asserting nothing anybody believes. Every check in this module names its unit before it counts it (Grain: What Does One Row Represent?).
Trust the orchestrator. Tasks exited zero, the run finished inside its window, nothing raised, so the data is fine. When a number turns out to be wrong, add a retry and widen the timeout. This is not laziness — task status is the only signal most platforms emit by default, and for the failures that actually raise it is the correct signal.
The upstream extract returned an empty page because the source API changed its pagination. The join succeeds against zero rows, the write succeeds, the task succeeds, and yesterday is reported as a very quiet day (Missing Rows).
- The upstream extract returned an empty page because the source API changed its pagination. The join succeeds against zero rows, the write succeeds, the task succeeds, and yesterday is reported as a very quiet day (Missing Rows).
- A dimension gained duplicate keys after a non-idempotent re-run, so the fact join now emits several rows per order. The task is *faster* than usual because no error is raised, and revenue is reported at a multiple of its real value (Duplicate Rows).
- The producer starts sending
amountas a string. The cast to numeric yields null rather than an error, every row survives, the row counts reconcile perfectly against the source, and every amount is nothing (Breaking Schema Changes). - The transformation stops subtracting refunds. Every row is present, unique, fresh, well-typed and normally distributed. The only signal that moves is a comparison against the source, and most platforms do not run one (Two Dashboards, Two Numbers).
- The job crashed at 02:00 and the serving table still holds the previous period. The dashboard renders last Tuesday with total confidence, and nobody notices because the number is plausible (Stale Dashboards).
What is actually happening
- An orchestrator observes process outcomes: exit code, duration, exceptions, retries. That is a statement about code execution, and it is orthogonal to whether the rows are right. The two are correlated only for failures that raise, which is the minority of data failures (The Pipeline Succeeded. The Data Is Wrong.).
- Almost every expensive data failure is *type-clean*. Missing rows are valid rows that are absent. Duplicates are valid rows that are present twice. Late data is valid data in the wrong period. A refund not subtracted is arithmetic that a compiler will never question. Nothing in the execution path is in a position to object.
- So quality has to be a separate, explicit assertion layer that reads the data itself. Each assertion encodes a belief — "one row per order", "amount is never negative", "today looks like the days before it" — and turns a belief nobody wrote down into one that fails loudly.
- Every assertion has a blind spot, and the blind spots do not overlap neatly. Completeness cannot see duplicates that offset losses. Uniqueness cannot see a duplicate that arrived under a new key. Freshness cannot see fresh data that is wrong. Distribution cannot see an error that preserves shape. Reconciliation cannot see a bug present identically at both ends.
- Which is why quality is a portfolio, not a gate. The right portfolio is the set of checks whose blind spots, taken together, exclude the failures this consumer would actually be harmed by — not the set the tool ships with.
What a green run actually proves
An orchestrator answers one question: did this process finish without raising, inside its window? That is a genuinely useful question, and it is the wrong one to answer in isolation, because the data failures that cost the most are the ones in which every process finishes cleanly.
Read the guarantees column below from top to bottom. Each stage promises something narrow and technically true, and nowhere in the chain does any stage promise that the rows are the right rows. That promise is not weakened along the way — it is never made at all, by anybody, unless you build the thing that makes it.
This is why "add monitoring" is not an answer here. The orchestrator is already monitored, exhaustively, and it is structurally unable to observe the failure classes this module exists for.
- 1Scheduler
Starts the run at its interval and records the attempt.
guarantees That the run was triggered, once, at the expected time.
fails by Triggering perfectly against a source that produced nothing, which looks identical to a source that produced normal data.
- 2Extract task
Reads a window from the source and writes it to raw storage.
guarantees That the read returned without error and the write completed.
fails by Returning an empty or partial page and calling it a window. Zero rows is a valid result set.
- 3Transform task
Joins, filters, casts and aggregates into the model.
guarantees That the SQL was syntactically valid and executed. Nothing about the arithmetic.
fails by A fan-out join, an over-broad filter, a cast that nulls instead of raising — all of which are successful executions.
- 4Publish
Swaps the result into the serving table.
guarantees That readers see either the old table or the new one, if it was built atomically.
fails by Publishing atomically and correctly a table whose contents are wrong. Atomicity is a statement about visibility, not about values.
- 5Check suite
Runs the assertions somebody wrote.
guarantees That those assertions held on the rows they read.
fails by Passing every assertion while the metric is meaningless, because no assertion encoded the meaning — or by not running at all.
- 6Dashboard
Aggregates the serving table into a number a human acts on.
guarantees Nothing whatsoever. It renders what it is given with complete confidence.
fails by Applying a filter or join defined in the BI layer that changes the metric after every upstream check has passed (The Metrics Layer).
Six stages, six honest guarantees, and no stage anywhere claiming the rows are the right rows. Data quality is the layer that makes that claim, and it does not exist by default.
One bug, six checks, and only one of them moves
src/de/sim/pipeline.ts and asserted by scripts/de-sim.test.ts, which pins exactly this result: the transform-bug fault trips reconciliation and no other check, while the run publishes successfully. It is a teaching model with a fixed seed, not a measurement of any real platform.Take the most ordinary bug imaginable: the revenue model stops subtracting refunds. There is no crash, no null, no missing row, no duplicate, no schema change. The code does precisely what it was told, and what it was told is wrong.
Run that against a portfolio of six checks and watch what happens. Completeness passes, because every order in the source is present. Uniqueness passes, because every order id appears once. Freshness passes, because the run completed on time. Validity passes, because every amount is a well-formed number. Distribution passes, because the shape of the day is unchanged — the same orders in the same countries, just valued differently.
Reconciliation fails, alone, because it is the only check in the portfolio that compares the serving table against something outside it. Every other check reads the copy and asks whether the copy is internally plausible. The copy is entirely plausible. It is also wrong.
Notice the direction of the error too. Revenue is *overstated*, and a number that is too high is questioned far less often than one that is too low. If nobody runs the comparison, the correcting signal is a finance team that cannot close the month, several weeks later.
| Check | What it asserts | Under the refund bug | Why |
|---|---|---|---|
| Completeness | Every order the source recorded reached the serving table. | PASS | Nothing was lost. The bug changes a value, not a row. |
| Uniqueness | Each order id appears once and only once. | PASS | No row was added or redelivered. |
| Freshness | The newest complete record is recent enough. | PASS | The run finished normally and published on schedule. |
| Validity | Every amount is non-null and numeric. | PASS | The amounts are perfectly well-formed. They are simply the wrong amounts. |
| Distribution | Today resembles the days before it, per country and in total. | PASS | Volume and mix are untouched; only a subset of values changed, within the normal range. |
| Reconciliation | Serving-table revenue equals source revenue for the same closed period. | FAIL | The only check that observes both ends at once, and therefore the only one with an external reference to disagree with. |
Correct enough for whom
"Correct" is not a property a dataset has on its own. It is a relation between the data and a decision, and the same table can be entirely adequate for one consumer and unusable for another on the same day.
An experiment readout tolerates a fraction of a percent of missing events and does not tolerate a systematic bias in which events are missing. A month-end financial close tolerates no missing rows at all and happily tolerates being a day stale. A recommendation model tolerates both and does not tolerate a distribution that shifted without anyone noticing. These are not different levels of rigour; they are different failure definitions.
So the useful design conversation is never "how do we improve data quality". It is: name the consumer, name the wrong answer that would hurt, name the observable property that would have differed, and assert that property. Everything else in this module is technique for the last step.
What is the wrong answer this dataset must not produce, and which check follows from it?
when Financial reporting, regulatory submissions, billing — anywhere under-counting is a liability and every record has to be accounted for.
cost Reconciliation against the source for closed periods, which needs source access, a stable period boundary and an agreed definition of the measure on both sides (Reconciliation).
when Any additive metric fed by an at-least-once stream or a pipeline that can be re-run — which is most of them (Deduplication).
cost A uniqueness assertion on a genuinely unique business key, plus the modelling work to establish that such a key exists (Surrogate Keys).
when Operational dashboards driving same-day decisions, on-call rotas, anything where staleness looks exactly like calm.
cost A freshness check per dataset with a stated SLO, and the false alarms it will produce on genuinely quiet periods (Freshness Checks).
when Models, experiments, segmentation — consumers whose output depends on the distribution rather than on individual rows.
cost Distribution checks with thresholds that must be tuned, re-tuned as the business grows, and defended against seasonality (Distribution Tests).
when Any metric with a contested definition — active user, revenue, churn — especially where two teams compute it separately.
cost A metric definition owned in one place and a human who understands the domain reviewing changes to it. No automated check finds this (The Metrics Layer).
Why the portfolio is the unit, not the check
The instinct after an incident is to add the check that would have caught it. That instinct is right and insufficient: it produces a suite shaped like your incident history rather than like your risk, and the next failure is by definition one you have not had yet.
A better construction is to lay the blind spots side by side. Each check covers a class and misses a class; the portfolio is adequate when the union of what is covered contains the failures your consumers would be harmed by, and inadequate otherwise — regardless of how many checks it contains.
The comparison below is the shape of that argument. One column is what most platforms have. The other is what makes the difference, and it is not more assertions of the same kind.
Two hundred generated tests over the serving layer: not-null on every key column, accepted values on every enum, uniqueness on every primary key. Every one passes on every run, and has for months.
A dozen assertions chosen from consumer harm, plus one scheduled reconciliation of a closed period against the source on both row count and a summed measure, plus a freshness SLO per serving dataset.
Internal assertions can only detect states the copy makes visibly implausible. A pipeline that is wrong in a self-consistent way — a filter that drops a category, an arithmetic change, a join at the wrong grain — produces a copy that satisfies every internal assertion. Detecting it requires a reference the pipeline did not produce, and the source is the only one available.
How to build it
Most important first.
- Write the checks from the consumer backwards. Ask what wrong answer would cost the most, then ask which observable property would have differed, then assert that property. Checks chosen from a feature list assert whatever was easy to assert.
- Run at least one check that observes both ends at once. Per-hop counts localise a loss; only a source-to-serving reconciliation notices that the composition is wrong (Reconciliation).
- Separate checks that must block a publish from checks that should only notify. Blocking on a noisy distribution check trains people to disable it; not blocking on a broken primary key ships a corrupt table (Quality Alerting).
- Test against the raw arrival as well as the serving table. A check that only reads the final model cannot distinguish "the source sent nothing" from "we dropped it" (The Raw Landing Zone).
- State every check's blind spot next to the check, in the same file. An undocumented blind spot becomes an assumed guarantee within about two incidents (Dataset Documentation).
- Give the checks an owner who can fix the cause, not just the person who can read the alert. A data team can measure correctness; only the producing team can restore it (Who Owns Data Quality).
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- A passing check guarantees exactly one thing: the property it asserts held on the rows it read at the moment it read them. It does not generalise to properties you did not assert, to rows outside its predicate, or to the next run.
- No combination of internal checks establishes correctness, because correctness is a claim about the world and every check reads only the copy. The strongest available claim is consistency with an independent authority for a closed period (The Dimensions of Data Quality).
- Checks give no guarantee about open periods. Anything still receiving late data is expected to be incomplete, so every completeness assertion is scoped to a closed boundary or it is noise (Late-Arriving Data).
- A check suite guarantees nothing about semantics.
revenuechanging from gross to net passes every test ever written for it, because the schema, the types, the counts and the distribution are all unchanged (Semantic Changes).
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- The check for this lesson is recursive and worth taking seriously: is the check suite itself running? A suite that silently stopped executing produces exactly the same green as a suite that passed, and this failure is common.
- Assert the count of executed checks per run and alert when it drops, and periodically inject a known-bad row into a staging copy to confirm the suite still fails on it (Data Tests).
- It misses a check that runs, passes, and asserts nothing useful — a
NOT NULLtest on a column that is never null by construction produces perpetual green and zero information.
- Checks add latency between "the data exists" and "the data is publishable". A pre-publish suite delays every consumer by its own runtime; a post-publish suite delays nobody and lets wrong data be read before it is caught.
- That is a real trade with no universal answer: pre-publish protects correctness at the cost of freshness, post-publish protects freshness at the cost of exposure. Decide it per dataset from what a wrong read costs (Atomic Publish).
- Checks that compare against history need history. A newly created dataset has no distribution to compare with, so its first days are structurally unprotected by the cheapest broad detector there is.
- Every schema change is a quality event. A new column arrives with no test; a renamed column silently vacates the one it had; a retyped column can pass a test whose predicate no longer means what it did (Schema Evolution).
- Checks must be versioned with the model they protect. A test suite maintained separately from the transformation drifts within a quarter and then fails for reasons nobody can attribute (dbt Concepts).
- The dangerous evolution is a threshold that was tuned to last year's traffic. Volume checks quietly become useless as a business grows, and the failure mode is a check that can no longer fire.
- A failed check is not an incident resolution — it is the start of one. The first question is whether the bad data was published, because that decides whether you are fixing forward or repairing history (Data Incidents).
- If it was published, the repair is a bounded backfill of the affected range validated before publish, not a re-run of the DAG (Planning a Backfill).
- If the transformation logic was wrong, fixing the code fixes only future runs. History carries the old logic until it is recomputed, and consumers who exported the old numbers carry them forever (Reprocessing vs Retrying).
What can go wrong
- The characteristic failure: everything succeeds and the number is wrong. This is not an edge case; it is the modal data incident.
- A check suite that stopped running, which is indistinguishable from a passing suite unless execution itself is monitored.
- A check tuned so loosely that it can never fire, usually after being tightened once, paging somebody at 3 a.m., and being loosened in anger (Alert Fatigue: The Page Nobody Reads).
- A check tight enough to fire on normal seasonality, which trains its audience to ignore it and therefore removes the coverage it appears to provide (Distribution Tests).
- Checks that all read the same downstream model, so a fault upstream of that model is invisible to every one of them simultaneously.
- Correct checks routed to a team with no ability to fix the cause, which converts detection into a ticket queue (Data Ownership).
- "All tests passed, so the data is correct." Tests passing means the beliefs you wrote down held. The bugs that matter are the beliefs you did not write down.
- "We have a quality tool, so quality is handled." The tool executes assertions; it does not know your business. A platform with two hundred generated
NOT NULLtests and no reconciliation is unprotected against every failure in this lesson'sbreakslist. - "The row counts match, so nothing was lost." Counts matching is consistent with every row being present and every value being wrong, which is exactly what a bad cast produces (Nullability & Defaults).
- "Two dashboards agree, so the number is right." Agreement means a shared upstream, which is what you would expect if the shared upstream is wrong (Two Dashboards, Two Numbers).
- Quality checks read the data, which means a check on a PII column is a system with access to PII, and its logged failure messages are a place where sample values escape (PII in Pipelines).
- Never print offending row values into an alert channel by default. Emit the count, the keys and the query that reproduces it, and require an authenticated tool to see the rows (What You Just Wrote Into a Log Half the Company Can Read).
Operating it
- Per-dataset check results over time, not just the latest run. A check that flipped from pass to fail last Thursday localises the incident to a deploy window immediately ("What Changed?" — Deploy Markers and the Invisible Deploys).
- Number of checks executed per run per dataset, alerting on a drop. This is the only signal that catches a suite that stopped.
- Freshness and row count per serving dataset as continuously recorded series rather than pass/fail booleans, so drift is visible before a threshold is crossed (Pipeline Metrics).
- The count of incidents reported by a human before any check fired. That ratio is the honest measure of the suite's coverage, and it is usually worse than teams expect (Debugging a Data Incident).
- At 10x datasets, hand-written checks stop being maintained. The checks that survive are the ones generated from declared metadata — grain, keys, nullability — rather than written per table (Data Contracts).
- At 100x, per-row assertions on the full serving layer become infeasible and the portfolio shifts toward sampled checks, aggregate reconciliation and anomaly detection on recorded series.
- Consumer count scales the *severity* problem rather than the technical one. With eighty dashboards on one model, "who do we tell" becomes harder than "did it break" (Impact Analysis).
- Checks are queries, and the expensive ones scan. A completeness check that counts a full history nightly costs proportionally to all history rather than to the day that changed (Scan Cost).
- Prefer checks scoped to the affected partition, with periodic full checks on a slower schedule. Scanning everything every hour is how a quality suite becomes the largest line in the platform.
- The cost that is never counted is human: every false alarm spends attention from an on-call engineer, and attention is the scarcest resource in the system (Alerts Worth Waking Someone For).
- Every check costs compute to run, latency to wait for, and attention when it fires. A suite large enough to catch everything is a suite nobody reads and a bill nobody approves.
- Blocking checks convert silent wrong numbers into loud outages. That is almost always the right trade and it will still page someone at 3 a.m. for a source that was legitimately quiet (Contract Enforcement).
- Tight thresholds catch more and cry wolf more; loose thresholds are quiet and blind. There is no setting that is both, only a choice about which error you would rather make for this dataset.
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- GENERALThe separation between process health and data health holds for every stack, orchestrator and warehouse. What varies is which layer runs the assertions — the transformation tool, the warehouse, a dedicated observability product — not whether the two questions are distinct.
- SIMULATEDThe check results in the second section come from
src/de/sim/pipeline.tsin this repository, a deterministic row-level model, not from a measured production platform. It is pinned byscripts/de-sim.test.ts; treat it as a worked example, not as evidence about your own pipeline. - SCALE-SPECIFICHand-written per-table checks are maintainable for tens of datasets and abandoned somewhere in the hundreds, where checks generated from declared contracts take over. The portfolio idea survives that transition; the authoring style does not.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — DevOps / Production Engineering owns how the check code itself is versioned, reviewed, deployed and rolled back. A test suite that ships outside the delivery pipeline it protects drifts from the models it asserts against.
- — Distributed Systems owns the delivery guarantees that make duplicates and gaps normal rather than exceptional. This module treats at-least-once as a fact of life and checks for its consequences; that domain explains why it is a fact of life.