Data Tests
Assertions over rows and columns — not null, unique, non-negative, in a set, references valid — and the precise blind spot each one carries.
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.
Which assertions about a table are worth writing first, and what does each one still fail to notice?
The next engineer to change this model, who needs the beliefs it depends on written down as executable statements rather than inferred from the SQL; and every downstream consumer, who benefits from the run stopping before a table with a broken key reaches them.
A test asserts a property of one unit. unique(order_id) is a claim that the table is one row per order; run it against a table that is one row per order line and it fails correctly, having discovered a grain you did not intend. Tests are therefore the cheapest executable documentation of grain in existence (Grain: What Does One Row Represent?).
Write tests when something breaks. After each incident, add an assertion that would have caught it. This is how nearly every suite actually begins, it produces real value immediately, and it is a reasonable way to start — the problem is only that it stops there.
The suite becomes a record of past incidents rather than a description of the model's invariants, so the next failure — by definition one you have not had — is uncovered.
- The suite becomes a record of past incidents rather than a description of the model's invariants, so the next failure — by definition one you have not had — is uncovered.
- A test is written against the wrong column:
unique(id)on a surrogate key that the load process generates, which is unique by construction and passes forever while the business key duplicates freely (Surrogate Keys). - Accepted-values tests are written from the values present on the day they were written. The source adds a
pending_reviewstatus six months later, the test fails, somebody adds the value to the list without asking what it means, and the metric that filters on status is now wrong (Enum Evolution: The New Value That Broke Old Clients). - Every test is scoped to the whole table, so as history accumulates the suite scans more each night and eventually stops finishing inside the window. It is then disabled (Scan Cost).
- Tests run *after* publish, so they faithfully detect a broken table that consumers have already read. Detection without prevention is worth something, and much less than people assume (Atomic Publish).
What is actually happening
- A data test is a query that returns the rows violating a belief, plus a rule that a non-empty result means failure. That framing matters: the test yields the offending rows, so a failure comes with its own investigation starting point rather than just a boolean (Debugging a Data Incident).
- The five canonical assertions map to four of the six dimensions.
NOT NULLand range and set-membership are validity.uniqueis uniqueness. A referential test is consistency. None of them is completeness, and none of them is accuracy — which is precisely why a suite made only of these passes while a table is missing a day (The Dimensions of Data Quality). - Tests are compiled and executed by the transformation layer in most modern stacks, which means they live in version control next to the model, run in the same DAG, and can block the publish. That co-location is the whole reason they get maintained (dbt Concepts).
- The engine matters less than the placement. The same assertion is a constraint in a transactional database, a test in a transformation tool, and a filter in a dashboard — with wildly different consequences on violation (Database Constraints).
- A test's severity is a separate axis from its correctness.
unique(order_id)failing means the model is unusable and the run must stop. A row-count-versus-yesterday test failing means somebody should look. Encoding both as "failure" is how suites become noise (Quality Alerting).
The five assertions worth writing first
These five cover the failures that make a model structurally unusable, as opposed to merely wrong. A null key, a duplicated key, a negative measure, an unknown status and a dangling reference each break a class of downstream query outright, which is why they earn the first place in every suite.
The misses column is the point of this table. Each of these tests is genuinely worth having and each is a narrow instrument; read the five blind spots together and the shape of what a validity-and-uniqueness suite cannot see becomes obvious — it cannot see anything about rows that never arrived, and it cannot see a value that is well-formed and wrong.
That is not an argument against writing them. It is an argument for not stopping, and for writing the blind spot down next to the assertion so that the next person does not have to rediscover it during an incident.
| Check | Expresses | Catches | Still misses |
|---|---|---|---|
order_id IS NOT NULL | Every row identifies the entity it describes, so it can be joined, deduplicated and traced back to a source record. | A failed cast in the key column; a left join that produced unmatched rows; an upstream field that was renamed and now arrives absent (Nullability & Defaults). | A key that is present and wrong — a default such as 0 or the empty string passes this test and then collides with every other row that got the same default. |
unique(order_id) | The table is at order grain: one row per order, so SUM(amount) means what a consumer assumes it means. | At-least-once redelivery, a non-idempotent re-run that appended, a fan-out join against a dimension with duplicate keys (Duplicate Rows). | The same order re-emitted under a new id, which is what a producer retry usually looks like — two distinct keys, one real order, and a uniqueness test that is delighted (Idempotency vs Deduplication). |
revenue >= 0 | A business invariant: this measure has a floor that the type system does not know about. | Reversed subtraction in a refund calculation, a sign flip on a currency conversion, a discount applied twice, a cast that wrapped. | Every wrong value that happens to be positive, which is nearly all of them. A revenue figure at ten times its true size satisfies this test perfectly. |
status IN ('placed', 'paid', 'shipped', 'cancelled') | The set of states is agreed with the producer, and any state outside it is a change nobody told us about. | A new status added upstream — which silently changes the meaning of every metric that filters or groups on this column (Semantic Changes). | A status that is reused for a new meaning. cancelled starting to include chargebacks passes this test forever and moves every downstream number. |
customer_id exists in dim_customers | Every fact can be attributed to a dimension member, so an inner join loses no rows. | A dimension load that ran late or partially; a type mismatch between the key columns; a new entity created after the dimension snapshot (Dimension Tables). | A dimension row that exists and describes the wrong thing. Referential existence is satisfied by any matching row, correct or not. |
count(*) for the partition is within its historical band | The period contains roughly the number of rows a period like this one usually contains. | A partial load, an over-broad filter, an upstream outage, a source that stopped mid-window (Volume Anomalies). | Any error that preserves volume — which includes every value-level bug in this table — and it fires on legitimate seasonality unless the band accounts for it (Distribution Tests). |
Five structural assertions and one shape assertion. Notice that not one of them can detect a period that is entirely missing from both the source extract and the serving table, because all six read only the copy.
A test is a query that returns the offenders
WHERE shape to actually avoid the scan.The most useful way to write a data test is as a query whose result set is the violations. Zero rows means the belief held; any rows means it did not, and the rows themselves are the start of the investigation. A test that returns only a boolean has thrown away the evidence.
Written this way, tests compose. The uniqueness assertion below is also the query you run during an incident to find which orders duplicated; the referential test is also the query that lists the customers a dimension load missed. The suite becomes a library of debugging queries that happen to run on a schedule.
Scope matters as much as logic. Both queries below are written against a single partition rather than the whole table, which is what keeps them cheap enough to stay on the critical path as history grows (Partition Pruning).
1-- Grain: fct_orders must be exactly one row per order, for this partition.2-- A non-empty result is a failure, and is also the list to investigate.3SELECT4 order_id,5 COUNT(*) AS rows_for_this_order6FROM fct_orders7WHERE order_date = DATE '2026-08-25'8GROUP BY order_id9HAVING COUNT(*) > 1;10 11-- Referential: every customer referenced by the new partition must exist12-- in the dimension. Scoped to the partition, so the join stays small.13SELECT DISTINCT14 f.customer_id15FROM fct_orders AS f16LEFT JOIN dim_customers AS d17 ON d.customer_id = f.customer_id18WHERE f.order_date = DATE '2026-08-25'19 AND d.customer_id IS NULL;Both are scoped to one partition, so cost grows with new data rather than with history. Both return keys rather than full rows, which keeps a failure message free of personal data while still naming exactly what to look at.
Where the test runs decides what it can do
The same assertion has completely different power depending on where in the pipeline it executes. Run against raw arrivals it can tell you the source sent something strange, which is information you cannot recover later. Run before publish it can prevent a bad table from existing. Run after publish it can only tell you that consumers have already read one.
The distinction that matters most is between the raw layer and the serving layer. A check that only reads the final model is structurally unable to distinguish "the source sent nothing" from "we dropped it", and those two incidents have different owners and different fixes (The Raw Landing Zone).
Placement also decides severity. A test at the boundary can reject a batch and page the producer. A test before publish can hold the release and page the pipeline owner. A test after publish can only notify, and notification is the weakest of the three.
- 1At the boundary, on arrival
Validates the incoming payload against the agreed schema and domain before it is written anywhere.
guarantees That nothing violating the contract enters the platform, and that the producer learns immediately.
fails by Rejecting a legitimate evolution the producer forgot to announce, turning an upstream oversight into a downstream outage (Contract Enforcement).
- 2On the raw layer, after landing
Asserts properties of what actually arrived, before any transformation has touched it.
guarantees That "was it us or them" is answerable, permanently, because the evidence is preserved and was checked in its original form.
fails by Being skipped for cost reasons, after which every incident begins with an unresolvable argument about which side lost the rows.
- 3Before publish, on the built table
Runs the full suite against the new partition while it is still invisible to consumers.
guarantees That consumers never observe a table violating a blocking assertion — prevention rather than detection.
fails by Adding its full runtime to every consumer's freshness, and by blocking a publish at 3 a.m. for a source that was legitimately quiet.
- 4After publish, on the serving table
Detects violations in the table consumers are currently reading.
guarantees Detection only. The bad data was authoritative between publish and alert, and may already have been exported (Stale Dashboards).
fails by Being the only placement a team has, which converts every quality problem into an incident rather than a prevented release.
Prevention costs freshness; detection costs exposure. Most platforms need both — blocking assertions on structure before publish, and monitoring assertions on shape afterwards — and the mistake is having only the last row.
How to build it
Most important first.
- Start with the grain assertion.
uniqueon the business key, plusnot nullon that key, is the single highest-value pair in any suite, because a broken grain corrupts every additive measure downstream simultaneously. - Assert referential existence against every dimension the model joins to, and know before you write it whether the join is inner or left — an inner join to a partial dimension silently deletes facts (Dimension Tables).
- Write range tests as business statements, not as type statements.
revenue >= 0is a claim about the world that aDECIMALcolumn will never make on its own, and it catches sign errors, bad refund handling and reversed subtractions in one predicate. - Scope tests to the partition being built, with a slower full-table run on a schedule. A test that scans all history nightly is a test that will be disabled during a cost review (Incremental Processing).
- Give every test a severity and route the blocking ones to the publish gate and the warning ones to a channel. Two levels is usually enough; four is a taxonomy nobody remembers (Quality Alerting).
- Record the blind spot next to the test. The
missescolumn of the table below is not commentary — it is the field that stops a passing suite from being read as a correctness proof (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 test guarantees the asserted predicate held over the rows matched by its own predicate, at execution time. It says nothing about rows excluded by its
WHEREclause, which is where scoped tests hide their gaps. - Tests that block the publish guarantee that consumers never read a table violating them — a genuinely strong guarantee, and the reason blocking is worth the operational pain (Contract Enforcement).
- Tests that run after publish guarantee only detection, and detection has a latency: the window between publish and alert is a window in which wrong data was authoritative and may have been exported.
- No test guarantees anything about a property nobody thought to assert, and the failures that hurt most are usually in that category rather than in an assertion that was written badly.
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 the checks: assert that the expected number of tests ran, and that the suite fails when it should. Periodically write a known-bad row into a staging copy and confirm the suite rejects it.
- Also track the pass rate over time per test. A test that has never failed in a year is either protecting an invariant that cannot break, or it is asserting something vacuous — and the two look identical from outside.
- This misses a test that is meaningful, runs, and is scoped so narrowly that the rows where the bug lives are outside its predicate. Reviewing test predicates is as important as reviewing test logic.
- A pre-publish suite sits directly on the critical path: end-to-end freshness for every consumer increases by the suite's runtime. That cost is real and is the main argument people use against blocking tests.
- The mitigation is scoping rather than skipping — test the new partition, not the table — which usually reduces the suite's runtime by far more than removing tests would (Partition Pruning).
- Tests that need a closed period cannot protect an open one. Anything published continuously is protected only by tests that are valid on partial data, which excludes most completeness-shaped assertions (Late-Arriving Data).
- New columns arrive untested. Make that visible: a coverage report of columns with no assertion is more actionable than a test count (Dataset Documentation).
- Accepted-values tests are the ones that break on legitimate change, and they should. Treat every such failure as a conversation with the producer about what the new value means, not as a list to append to (Data Contracts).
- A renamed column removes its own tests without failing anything. This is one of the few cases where a green suite is actively misleading, and it argues for testing by contract rather than by hand (Breaking Schema Changes).
- A blocking test that fails leaves the previous good table in place, so recovery is: fix the cause, re-run, publish. The dataset is stale rather than wrong, which is the better of the two failures (Atomic Publish).
- A non-blocking test that fails means bad data is live. Recovery is a decision about whether to roll back to the previous partition or fix forward, and it should be made before the incident rather than during it (Rolling Back Data).
- When a test fails because it was wrong, resist editing it to pass. Record why the belief changed; a suite whose history shows tests loosened after every failure is a suite that has been negotiated into uselessness.
What can go wrong
- A test asserting a property that cannot fail, producing permanent green and zero information.
- A test scoped by a
WHEREclause that excludes exactly the rows the bug produces. - A uniqueness test on a pipeline-generated key rather than a business key (Surrogate Keys).
- A suite so slow it is moved off the critical path "temporarily" and never returns.
- Severity collapsed to a single level, so a broken primary key and a slightly unusual row count page the same person the same way (Alert Fatigue: The Page Nobody Reads).
- The suite silently not running — the failure of the mitigation itself, and invisible unless execution count is monitored.
- "We have 200 tests, so the model is well covered." Count is not coverage. Two hundred not-null tests and no uniqueness test on the business key is an uncovered model with an impressive number.
- "The tests passed, so the data is correct." They passed, so the beliefs somebody wrote down held. Completeness and accuracy are usually not among them (Data Quality).
- "Constraints in the warehouse make tests unnecessary." Many analytical stores accept constraint declarations and do not enforce them, using them only as optimiser hints. Verify enforcement before relying on it.
- "A failing test means the data is broken." It means a belief and the data disagree. Sometimes the belief was wrong — and finding that out is exactly what the test is for (Data Contracts).
Operating it
- Test results as a time series per test, with pass, fail and *not executed* as distinct states. Collapsing the third into the first is how a stopped suite stays hidden (Pipeline Metrics).
- Suite runtime per run, because the slow drift from two minutes to twenty is what precedes a suite being disabled (Regression or Tuesday? Telling a Real Change from Noise).
- Rows returned by each failing test, retained as a sample, so the investigation begins with the offending keys rather than with a boolean (Structured Logging: Fields a Program Can Read).
- At 10x models, hand-written tests are not maintained and coverage becomes accidental. Generating the standard assertions from declared metadata — key, grain, nullability, enum — is the transition that keeps coverage uniform (Data Contracts).
- At 100x rows, exhaustive uniqueness tests over full history become the largest query in the platform. The usual answer is to assert uniqueness within the partition plus a periodic global check, and to be explicit that the guarantee weakened (Deduplication).
- Test count scales worse than dataset count because each model tests its own columns and its parents' keys. Deduplicating assertions across a lineage is a real optimisation at that size (Data Lineage).
- Cost is dominated by bytes scanned, so the expensive dimension is scope rather than count. A hundred partition-scoped tests are cheaper than three full-history ones (Scan Cost).
- Referential tests are joins, and joins against large dimensions are the most expensive assertions in a typical suite. Scoping them to the keys present in the new partition usually removes the problem.
- The cost people forget is failure-handling time. A suite that fires weekly on legitimate variation costs more in engineer-hours than it ever cost in compute (Alert Fatigue: The Page Nobody Reads).
- Blocking tests trade availability for correctness. Consumers get a stale table instead of a wrong one, and somebody has to be available to unblock it — which is the operational cost people underestimate.
- Generated tests trade specificity for coverage. They assert the things every table should satisfy and none of the things this table specifically means, so a generated suite still needs hand-written business assertions on top.
- Retaining failing rows as samples is invaluable for debugging and is a data-exposure surface. For a table with personal data, retain keys and counts rather than values (PII in Pipelines).
Data quality lab
Change an input and watch which number moves — and which one does not. Everything here comes from a model in this repository, not from a measurement.
The revenue model stops subtracting refunds.
| Check | Running? | Would it have fired? | Blind spot it keeps anyway |
|---|---|---|---|
Completeness Every order the source recorded for the period reached the serving table. | running | pass Every order in the source for this period is present. | Duplicates that coincidentally offset losses, and any period that is not yet closed. |
Uniqueness Each order id appears exactly once in the serving table. | running | pass Every order id appears exactly once. | A genuine duplicate that arrived under a new key — a producer retry with a fresh event id looks like a second order. |
Freshness The newest complete record is recent enough for the decisions this table drives. | running | pass Newest complete record is 12 simulated minutes old. | Data that is perfectly fresh and completely wrong. It also fires falsely on a period where the source genuinely produced nothing. |
Validity Every amount is non-null and parses as a number. | not run | pass Every amount is non-null and numeric. | A value that is well-typed and wrong — a price in the wrong currency passes every type check there is. |
Distribution The shape of the day resembles the days before it, per country and in total. | not run | pass Largest per-country share drift 0.8pp; total volume drift 0.0%. | Slow drift, and any error that preserves the shape while changing every value inside it. |
Reconciliation Revenue summed in the serving table equals revenue summed in the source for the same closed period. | not run | FAIL Serving table reports 1,010,654.50 against a source total of 934,498.90. | Anything wrong identically at both ends — a bug in logic shared by the extract and the model reconciles perfectly. |
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 five assertions and their blind spots hold across every engine and tool. What varies is where they execute — as warehouse constraints, transformation-tool tests, or an external quality service — and therefore what happens on violation.
- TOOL-SPECIFICWhether a failing test blocks the publish is a property of the orchestration, not of the test. In some tools a test is a node in the DAG whose failure stops descendants; in others tests run after the model materialises, so failure is detection only. Check which one you have before promising prevention.
- WAREHOUSE-SPECIFICSeveral analytical warehouses accept
PRIMARY KEYandFOREIGN KEYdeclarations without enforcing them, treating them as optimiser metadata. A declaration that is not enforced is documentation, and relying on it as a guarantee produces duplicates that nothing objected to.
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 the delivery half of this: tests running in CI against a sample before the model is deployed, rather than only in production against real data. A data test that first runs in production is a test whose first failure is an incident.