Distribution Tests
Every row is valid, every type is right, every key is unique — and today holds a small fraction of a normal day. The checks that compare data with its own history.
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.
How do I detect data that satisfies every structural rule and is obviously wrong to anyone who has seen a normal day?
An analyst who would immediately notice that a chart fell off a cliff, and who currently notices it a day later than a check could have. Also every automated consumer — a model, an alerting rule, a downstream aggregate — that has no intuition at all and will consume the collapsed day without comment.
A distribution check compares an aggregate of a period against aggregates of comparable periods. The unit is therefore the period, not the row, and choosing it is the design decision: a check on daily totals cannot see a source that stopped for four hours, and a check on hourly totals fires constantly on normal traffic shape (Grain: What Does One Row Represent?).
Assume the structural tests cover it. Every column is validated, the key is unique, references resolve — a table that passes all of that is surely fine. And it is fine, in the sense that every row in it is a legitimate row. There are simply almost none of them.
An upstream API changes its pagination and the extract returns only the first page. Every returned row is perfect. The day's volume collapses and not one structural assertion notices (Incremental Extraction).
- An upstream API changes its pagination and the extract returns only the first page. Every returned row is perfect. The day's volume collapses and not one structural assertion notices (Incremental Extraction).
- A
WHEREclause is added to a staging model to exclude test accounts, and its predicate is broader than intended. A whole category disappears; the rows that remain are impeccable. - A source system stops emitting one event type after a deploy. Total volume barely moves because that type was a small share, but every metric built on it goes to zero and the table still looks healthy in aggregate (Schema Evolution).
- A currency conversion starts applying a stale rate. Row counts, uniqueness, nullability and ranges are all unchanged; only the *shape* of the amount distribution shifts, and only a check that looks at the values would see it.
- A duplicate load doubles the day. Volume is anomalous in the other direction, and a check that only alerts on drops sees nothing — high numbers are questioned much less than low ones (Duplicate Rows).
What is actually happening
- A distribution check compares a summary statistic of the current period with the same statistic over a set of comparable prior periods, and fails when the difference exceeds a band. Everything interesting is in the words "comparable" and "band".
- Comparable almost never means "yesterday". Most business data has a weekly shape, so the right reference set is the same weekday over recent weeks; monthly and holiday effects layer on top of that (Correlation Is Not the Root Cause).
- The band encodes how much variation is normal, and it has to come from the data's own history rather than from a number somebody liked. A fixed band tuned once becomes wrong as the business grows — in the safe direction if it widens, in the useless direction if the data outgrows it.
- The statistics worth watching are more than the row count: distinct count of a key, null rate per column, share of each category, and a percentile of a numeric measure. Volume catches loud failures; category share and percentiles catch the ones that preserve volume (Percentiles: Which One, and How Many Users Is That?).
- A distribution check is fundamentally a statement about the past being a good guide to the present, which is false at exactly the moments a business finds most interesting: launches, migrations, campaigns, outages upstream. That is not a flaw to engineer away, it is the property that makes the check both useful and noisy (Alert Fatigue: The Page Nobody Reads).
Valid, and broken
Picture a table where daily order volume has sat between roughly 100,000 and 130,000 for months, and today it holds 3,000. Every one of those 3,000 rows passes every structural test: the key is present and unique, the amount is a positive number, the status is in the accepted set, every customer reference resolves.
There is nothing wrong with the data that is there. The problem is the data that is not, and no assertion that reads a row can see a row that does not exist. The only way to detect this is to compare the period with periods like it.
Those numbers are the shape of the check, not figures to copy. What matters is the structure of the reasoning: a statistic, a set of comparable prior periods, a band derived from them, and a value far outside it. Substituting your own constants for these is how a check ends up useless within a year of growth.
The table below lists the statistics worth watching. Read the last column: each one has a class of failure that leaves it entirely unmoved, which is the same portfolio argument that runs through this module. Volume alone is the most commonly implemented and the most easily fooled.
Six statistics, six blind spots. The pair worth implementing first is row count and distinct-key count together, because their *ratio* detects the duplication that neither detects alone.
| Statistic | Detects | Normal variation looks like | Blind to |
|---|---|---|---|
| Row count for the period | Partial loads, stopped sources, over-broad filters, duplicate loads. | Weekday shape, seasonality, campaigns, holidays — large and legitimate swings. | Every failure that preserves the row count, which includes all value-level bugs. |
| Distinct count of the business key | A load that duplicated rows without changing the count of real entities, and a join that fanned out. | Tracks the row count closely in a healthy table; the ratio between them is the interesting series. | A duplicate that arrived under a new key, which raises both counts together. |
| Null rate per column | An upstream field that stopped being populated, a rename, a cast that started failing. | Small and stable per column; genuinely optional fields have their own steady rate. | A field populated with a wrong non-null value, and a default that replaced a null. |
| Share per category | A category that disappeared, a new enum value taking share, a filter that excluded a segment. | Slow drift as the business changes mix; sharp moves on launches. | A uniform loss across all categories, which preserves shares while halving the data. |
| A percentile of a numeric measure | Currency and unit errors, a stale conversion rate, a pricing change, a cast that truncated. | Gradual movement with pricing and mix; tails move more than the median. | An error applied to a small subset, which barely moves a central percentile (Percentiles: Which One, and How Many Users Is That?). |
| Ratio between two counts | A relationship breaking — orders per customer, lines per order, payments per order — which is often the earliest signal of a grain problem. | Very stable in most businesses, which is what makes it sensitive. | Both sides moving together, which is exactly what a duplicated load produces. |
Comparing against the right history
Almost every false alarm in a volume check comes from comparing against the wrong reference. Yesterday is the wrong reference for anything with a weekly shape, and a fixed constant is the wrong reference for anything that grows.
The query below compares a closed day with the same weekday over a trailing window, and derives its band from the spread of that window rather than from a hard-coded percentage. As the business grows the band moves with it; as the data becomes noisier the band widens on its own.
Two details are doing real work. The reference excludes the day being tested, so a bad day cannot widen the band that judges it. And the window is measured in weeks rather than days, so the comparison is like-for-like on the strongest seasonal component most businesses have (Window Functions).
1WITH daily AS (2 SELECT3 order_date,4 COUNT(*) AS rows_loaded,5 COUNT(DISTINCT order_id) AS distinct_orders6 FROM fct_orders7 GROUP BY order_date8),9reference AS (10 -- Same weekday, trailing weeks, excluding the day under test.11 SELECT12 AVG(rows_loaded) AS mean_rows,13 STDDEV(rows_loaded) AS spread_rows14 FROM daily15 WHERE EXTRACT(DOW FROM order_date) = EXTRACT(DOW FROM DATE '2026-08-25')16 AND order_date < DATE '2026-08-25'17 AND order_date >= DATE '2026-08-25' - INTERVAL '8 weeks'18)19SELECT20 d.order_date,21 d.rows_loaded,22 r.mean_rows,23 d.rows_loaded < r.mean_rows - 3 * r.spread_rows AS below_band,24 d.rows_loaded > r.mean_rows + 3 * r.spread_rows AS above_band,25 -- The ratio is the duplicate detector: it moves when rows grow26 -- without the number of real orders growing.27 d.rows_loaded::numeric / NULLIF(d.distinct_orders, 0) AS rows_per_order28FROM daily AS d29CROSS JOIN reference AS r30WHERE d.order_date = DATE '2026-08-25';The multiplier on the spread is a policy dial, not a fact: widen it and the check goes quiet, tighten it and it fires on holidays. Note that both directions are tested — a check that only looks for drops misses every duplicate load.
When the check is right and the alert is wrong
A distribution check does not detect broken data. It detects *unusual* data, and unusual has causes on both sides of the boundary between your platform and the business it observes. Treating every firing as a data incident is how these checks acquire their reputation for noise.
The triage question is always the same and is rarely answered with more data: did something happen in the world, or did something happen in the pipeline? The fastest route to an answer is usually the producing team, which is one of the practical arguments for routing quality alerts to producers rather than to a central data team (Who Owns Data Quality).
The rows below are the recurring cases. Notice that the correct response differs in every one, and that in two of them the correct response is to change the check rather than the data — including the case where the check was right and the baseline is now poisoned by the incident it caught.
| Trigger | Symptom | Cause | Response |
|---|---|---|---|
| Volume collapses on a public holiday | A low-volume alert on a day that was genuinely quiet everywhere. | The reference window has no notion of a holiday calendar, so a real business pattern reads as an anomaly. | Add a holiday calendar to the reference selection, or accept and explicitly record these days as known anomalies. Do not widen the band — that removes the coverage you built it for. |
| Volume halves after a product launch in one market | A sustained anomaly that does not resolve and is re-alerted daily. | The mix changed for a real reason and the baseline predates it. | Reset the reference window with a recorded reason and a deployment marker, so the change is documented rather than absorbed silently ("What Changed?" — Deploy Markers and the Invisible Deploys). |
| Volume doubles overnight | An upper-band alert, if one exists; otherwise complete silence. | A re-run appended instead of replacing the partition, so the period is present twice (What Backfills Break). | Check the rows-per-key ratio first — it separates a duplicated load from genuine growth immediately. Then repair with an idempotent merge rather than a delete (Upserts and Merges). |
| A category drops to zero while total volume barely moves | The total check is silent; a per-category check fires. | One event type stopped after an upstream deploy, or an enum value was renamed. | This is a producer-side change. Confirm with the producing team and treat it as a contract event, not a pipeline bug (Data Contracts). |
| The check has not fired in eleven months | Uninterrupted green on the quality dashboard. | The band was widened after a false alarm and never revisited, or the business outgrew a hard-coded threshold. | Inject a known-bad period into a staging copy and confirm the check still fires. A check that cannot fail is not evidence of health. |
| The band widened after last month's incident | A recurrence of the same failure passes without alerting. | The incident period is inside the reference window, so the broken shape is now part of what normal means. | Exclude recorded incident periods from reference windows. Baselines need a mechanism for forgetting, and it has to be deliberate (Data Incidents). |
How to build it
Most important first.
- Compare against the same weekday over a trailing window rather than against a fixed constant or against yesterday. This one change removes most false alarms in most businesses.
- Watch several statistics, not just row count. Null rate per column and share per category catch the failures that preserve volume, which are the ones structural tests also cannot see (Data Tests).
- Derive the band from the reference window itself — a robust spread measure over the trailing periods — so it widens as the data becomes noisier and tightens as it stabilises, without anybody re-tuning it.
- Make distribution checks warn, not block, by default. They are the checks most likely to be wrong for a legitimate reason, and a blocking check that is wrong once a fortnight is a check that will be disabled (Quality Alerting).
- Suppress the check for datasets whose history is too short to have a shape, and say so on the dashboard rather than reporting a spurious green (The Data Quality Dashboard).
- Record the statistic as a series regardless of whether it fires, because the value during an incident is being able to see when the drift started (Pipeline Metrics).
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 distribution check guarantees only that the period resembles its own recent history. If the history was already wrong, the check certifies the error as normal — this is the failure mode with no internal remedy (Reconciliation).
- It guarantees nothing about individual rows. A day of perfectly normal volume can consist entirely of rows with a wrong value in one column.
- It gives no guarantee at a period boundary. A day that is still open is expected to be low, so every distribution check needs an explicit rule about when a period is eligible for comparison (Late-Arriving Data).
- It cannot distinguish a data failure from a business event. A genuine outage in the product produces the same signal as a broken extract, and only a human with context can tell them apart (Data Incidents).
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 on this check is a deliberate injection: take a copy of a recent period, drop a category or a share of rows, and confirm the check fires. A distribution check that has never been shown to fire is untested software in the alerting path.
- Also track false-positive rate per check as an explicit number. A check firing more often than it is right is doing net harm and should be widened or removed rather than tolerated (Alert Fatigue: The Page Nobody Reads).
- It misses a check whose band has silently widened past usefulness as the reference window absorbed a previous incident. Anomalies included in the baseline become normal — the baseline needs a way to exclude known-bad periods.
- A distribution check on a closed period is the strongest and slowest form: you learn about yesterday today. A check on the current partial period is faster and much noisier because the comparison is against a partial baseline.
- The intermediate form worth building is a within-period check against the same elapsed fraction of comparable periods — hour 14 of today against hour 14 of recent same-weekdays — which detects a source that stopped mid-day without waiting for the day to close.
- Whichever form you choose, publish which one it is. A consumer who thinks "volume looks normal" refers to a closed day and is actually reading a partial one has been misinformed by the dashboard, not by the data (The Data Quality Dashboard).
- A schema change usually moves a distribution before it breaks a type. A new enum value appearing takes share from existing ones, and the category-share check sees it first (Enum Evolution: The New Value That Broke Old Clients).
- A genuine product change — a new market, a new checkout flow — invalidates the baseline deliberately. There should be a supported way to reset a check's reference window with a recorded reason, or people will do it by editing the threshold ("What Changed?" — Deploy Markers and the Invisible Deploys).
- The band itself evolves. Any check whose thresholds are hard-coded constants will be wrong within a year of business growth, and its failure mode is silence.
- A fired distribution check is a triage signal, not a diagnosis. The first branch is business event versus data failure, and it is answered by asking the producing team, not by looking at more data (Who Owns Data Quality).
- If it is a data failure, recovery is usually re-ingestion of the affected window followed by an idempotent re-run of the downstream models (Planning a Backfill).
- If it is a business event, the recovery is to the *check*: record the period as an explained anomaly so it does not poison the baseline for the next several weeks.
What can go wrong
- A band wide enough that only a total outage fires it, which is the state most checks drift into after two or three false alarms.
- A band tight enough to fire on every public holiday, which trains its audience to close the alert without reading it.
- A baseline contaminated by a previous incident, so the broken shape is now the expected shape.
- Checks on total volume only, which are blind to every failure confined to one category or one column (Missing Rows).
- Alerting only on drops, so a duplicate load that doubles the day passes silently (Duplicate Rows).
- A check on a dataset with no meaningful history, producing noise that discredits every other check on the same dashboard.
- "Volume is normal, so the data is fine." Volume is the statistic least sensitive to value-level errors. A day with exactly the right number of rows and a broken currency conversion looks perfectly normal.
- "The check did not fire, so nothing changed." It did not fire, so nothing changed *by more than the band*, against a baseline that may itself be wrong.
- "We should block the publish on distribution checks." Rarely. These are the checks most likely to be legitimately wrong, and blocking on them converts every unusual business day into an outage (Quality Alerting).
- "Anomaly detection replaces thresholds." It replaces *tuning*, at the cost of explainability, and it inherits every contaminated baseline the simple version had.
Operating it
- The statistic itself as a series with its band drawn on the same axis, so the question "is this unusual" is answered visually before anyone reads a threshold (Dashboards Built Around Questions).
- Per-category shares over time as a stacked view, which makes a category disappearing obvious in a way a total never will.
- Null rate per column per period, which is the cheapest early detector of an upstream field that quietly stopped being populated (Volume Anomalies).
- Fired-versus-actionable counts per check, reviewed periodically, because that ratio is the only honest measure of whether a check is earning its place (Alerts Worth Waking Someone For).
- At 10x volume nothing changes structurally — these are aggregates, and aggregates scale with the summary rather than with the rows.
- At 10x datasets, hand-tuned bands become unmaintainable and the checks have to derive their own thresholds from history. That transition is where "anomaly detection" earns its name, and where it starts producing alerts nobody can explain.
- At high dimension cardinality, per-category checks must be limited to the categories that carry meaningful share, with the long tail aggregated into an "other" bucket that is itself watched (Partition Cardinality).
- Distribution checks are aggregates over one period plus a small reference window, so they are among the cheapest checks per run — they read summaries, not rows (Scan Cost).
- Cost grows with the number of statistics and the cardinality of the grouping. Per-category checks on a high-cardinality dimension produce an enormous number of series and are the usual cause of a metrics bill nobody predicted (Cardinality: The Label That Took Down Monitoring).
- The real cost is attention. Every false alarm spends on-call time, and unlike compute it does not appear on any bill until people stop reading alerts entirely (Alert Fatigue: The Page Nobody Reads).
- Sensitivity and noise are the same dial. There is no setting that catches subtle drops and stays quiet through holidays, only a choice about which error this dataset would rather make.
- Deriving bands automatically removes tuning work and makes failures harder to explain. "The model says this is anomalous" is a much weaker position in an incident review than "volume is below the range of the last eight Tuesdays".
- Watching many statistics per dataset multiplies coverage and multiplies series count, storage and alert volume by the same factor (Cardinality: The Label That Took Down Monitoring).
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.
- GENERALComparing a period against its own comparable history works on any engine and any storage layer, because it operates on aggregates rather than on physical layout. What differs is how cheaply the aggregate can be computed for a partial period.
- SIMPLIFIEDThe volume figures used in the example are illustrative shapes, not measurements, and must not be copied into a check. What transfers is the form of the reasoning — a band derived from comparable periods, and a value far outside it — never the specific numbers, which depend entirely on the business.
- SCALE-SPECIFICPer-category distribution checks are practical while a dimension has tens of members and become a cardinality problem in the thousands, where the tail must be bucketed. Below a few weeks of history no distribution check is meaningful at all, and reporting one is worse than reporting nothing.
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 deployment markers as a practice — correlating a change in a signal with the release that caused it. Half the distribution alerts in a healthy platform are explained by a deploy, and the correlation is only cheap if the markers already exist.