A transformation project runs about 2000 data tests on every build - not-null, unique and accepted-values checks against production rows. An engineer edits the CASE expression that classifies refunds, the build is green, and finance finds the misclassification three weeks later. Which kind of test would have caught it on the pull request?

What is being tested

Whether you can separate two questions that look like one. "Is the logic correct?" and "is today's data sane?" need different tests, and a project with 2000 of the second kind and none of the first is blind to exactly this bug.

The mechanism

A data test is a query over the output of a build: it asserts a property of the rows that exist now. refund_class is not null passes whether the expression writes partial or full, because both are non-null and both are in the accepted-values list. The test is asking about shape, and the defect is in meaning.

A unit test supplies the inputs. You write three or four rows by hand - a refund equal to the order total, one smaller, one negative, one with a null fee - and assert the exact output rows. When the CASE branch moves, the assertion fails in the author's terminal before review, because the expected output was written down when the intent was still fresh in someone's head.

Two properties follow from supplying the inputs:

• It runs without production data, so it runs in CI on a pull request in seconds rather than after a build against real tables.
• It covers rows that do not exist yet. A negative refund that has never occurred cannot be caught by any test over current rows, and it is the case that breaks first in January.

dbt shipped unit tests in Core 1.8 (9 May 2024); SQLMesh and Spark-based frameworks have equivalents. This is recent tooling, which is why mature projects have thousands of data tests and no fixtures.

Why the other options fail

• "Not-null and accepted-values on the classification column." The commonest answer and the one the team already had. Both values are legal, so nothing fires. This test catches a broken join or a type change, which is a different failure.
• "Row-count reconciliation." The row count did not change. Classification moved rows between categories; reconciliation is blind to a reclassification and valuable against row loss.
• "A freshness check." Freshness answers whether the input arrived, never whether the transformation is right. A green freshness check is what let the pipeline look healthy.
• "Uniqueness on the primary key." Grain protection, and it would have held through this change. Useful, unrelated.

What it costs

Fixtures are maintenance: every schema change to an input breaks every fixture referencing it, and a project that fixture-tests all 600 models spends real time feeding mock rows to models that are a rename and a filter.

The decision rule is branching logic. Unit-test the models that contain a CASE, a window function, an incremental filter, a currency or unit conversion, or a deduplication rule - in most projects 5 to 10% of models. Leave pass-through and renaming models to data tests.

When this is the wrong answer

If the defect class you keep hitting is upstream - late files, renamed source columns, duplicated source rows - fixtures will not help, because the logic was never wrong. Spend the effort on source freshness, schema contracts and reconciliation instead. Fixtures catch defects in code you wrote; data tests catch defects in data you were sent. Most teams need the second first and then stop, which is how this bug survives three weeks.