Transformation Fixture Test

A mature transformation project typically carries hundreds or thousands of data tests: not-null, unique, accepted-values and relationship checks that run against production rows on every build. They are genuinely useful, and they all answer one question - is today's data the shape we expect?

They cannot answer the other question. Move a CASE branch so that partial refunds classify as full refunds, and every one of those tests still passes, because both values are non-null and both are in the accepted list. The shape is intact and the meaning is wrong. Finance finds it three weeks later in a reconciliation.

A fixture test inverts the direction. You supply the inputs - four or five rows written by hand - and assert the exact expected output, so the assertion encodes intent at the moment the author still has it in mind. When the logic moves, the test fails in their terminal before review.

Why it matters

Two properties follow from supplying the inputs, and neither is available to any test over production rows.

• It runs with no data. No warehouse build, no permissions, no waiting for a nightly load - so it runs on every pull request in seconds and is cheap enough to be mandatory.
• It covers rows that do not exist yet. A negative refund, a null fee, a currency that appears next quarter, a duplicate arriving from a source that has never duplicated. No test over current data can catch a case that is not in the current data, and those cases are what break in January.

Fixture testing of SQL models is recent tooling. dbt shipped unit tests in Core 1.8 (9 May 2024), and SQLMesh and Spark-based frameworks have equivalents. That is why projects started before 2024 usually have thousands of data tests and no fixtures - the practice was unavailable, not rejected.

Implementation patterns

• Test the models with branching logic. A CASE expression, a window function, an incremental filter, a unit or currency conversion, a deduplication rule, a date-boundary calculation. In most projects that is 5 to 10% of models, and they contain nearly all of the logic defects.
• Write the fixture from the bug. When a defect reaches production, the regression test is the rows that caused it. This grows the suite where the risk actually is rather than uniformly.
• Keep fixtures small and literal. Four rows that each exist for a reason beats forty copied from production; a fixture nobody can read is a fixture nobody updates.
• Mock only the direct inputs, not the whole upstream graph, or the fixture becomes a reimplementation of the pipeline.
• Run fixtures in CI and data tests after the build. They answer different questions at different times: fixtures gate the merge, data tests gate the publish.

Industry example

The pattern is imported from application engineering, where it is ordinary, and the import is visible in the tooling's own design: dbt's unit tests take given input rows and an expect block, which is the arrange-act-assert shape of a unit test in any language.

The telling detail from projects that adopt it in production is where the first failures land. They are rarely in new code. They are in a model nobody has touched for a year, whose author's intent was never written down anywhere, and the argument about what the expected output should be is the value the test delivers before it ever runs again.

Failure scenarios

• A refactor that changes meaning silently, passing every data test, discovered in a reconciliation weeks later - the case the practice exists for.
• Fixtures everywhere. Testing all 600 models means every schema change to an input breaks every fixture referencing it, and the suite is deleted in a sprint.
• Fixtures copied from production rows, which carry no stated intent and drift to meaningless as the data changes.
• A fixture suite that replaces reconciliation. Logic can be correct while the input is wrong; fixtures say nothing about whether the source sent everything.
• Mocked inputs that no longer resemble the source after an upstream column is renamed, so the test passes against a schema that no longer exists.

Trade-offs

Gained: defects caught on the pull request instead of in a report · tests that run without production data, so CI is seconds rather than an hour · intent written down for the next engineer · coverage of cases the data has not yet produced.

Paid: fixtures are maintenance, and the bill arrives at every input schema change · writing good mock rows takes judgement about which cases matter · two test suites to understand, with a rule for which belongs where · nothing about data quality, which still needs data tests.

When not to use it

If the defects you keep hitting are upstream, fixtures are the wrong investment. Late files, renamed source columns, duplicated source rows and silently coerced types are all failures where the logic was never wrong. Spend the effort on source freshness checks, schema contracts and row-count reconciliation instead.

They are also wasted on pass-through models - a rename, a filter, a union of two sources with no transformation - where the test would restate the SQL. The rule is simple: if you cannot write down an input that should produce a surprising output, there is nothing to assert.

Interview question

Q: You join a team with 2000 data tests and a history of logic defects reaching finance. You have one sprint. What do you add, to which models, and how do you keep the suite from becoming a tax nobody pays?

What a strong answer covers: the distinction between shape tests and intent tests, with an example of a defect no not-null test can see; selecting models by branching logic rather than uniformly, at roughly 5 to 10% of the project; growing the suite from real defects as regression fixtures; running fixtures in CI on pull requests and data tests after the build; and the maintenance cost, naming input schema changes as where it lands.

Quick check

Quiz: Which defect passes 2000 not-null and accepted-values tests? - Logic that writes a legal but wrong value, such as a CASE branch that classifies partial refunds as full; only a fixture test with declared expected output catches it.

Flashcard: What does a fixture test cover that no test over production rows can? - Rows that do not exist yet - the negative amount or the null fee that has never occurred - plus the author's intent, asserted before the data arrives.