Unit testing transformation sql with a fixture edition of eleven rows
Unit testing transformation SQL means running a model against a small fixed set of input rows and comparing the output to a hand written expected table. A fixture of about eleven rows, one for each awkward case in your event data, keeps the expected output small enough to verify by reading it.
The scan deduplication logic in your warehouse has a bug, and you will find out in October. The badge vendor resent a file during show week, the merge condition matched on the wrong column, and 214 scans landed twice. Nobody noticed, because the transformation ran green, loaded cleanly, and produced numbers that looked plausible.
Unit testing transformation SQL is the cheapest defence against that morning, and almost nobody in event data does it, because the standard advice assumes you have production data to test against every day. An annual show gives you one real load a year. The rest of the time, the only data your tests can run on is data you built by hand.
That constraint turns out to be a gift. A hand built fixture forces you to write down, one row at a time, every awkward case your pipeline claims to handle.
What does a unit test actually check?
The dbt documentation, current as of 2026, defines the mechanism plainly: "Unit tests allow you to validate your SQL modeling logic on a small set of static inputs before you materialize your full model in production." The feature has shipped in dbt Core since version 1.8. You declare mock rows for each input the model reads, you declare the rows you expect out, and the test fails if the model produces anything else.
The same page lists when the effort is justified, and the list reads like a description of an event pipeline: complex logic including "Regex", "Date math", "Window functions", and "case when statements when there are many whens", along with "Edge cases not yet seen in your actual data that you want to handle" and "Prior to refactoring the transformation logic (especially if the refactor is significant)."
Every one of those is present in a show data platform. Date math against a moving show open. Window functions picking the first scan per badge. Case expressions mapping five vendors' status codes onto one. And the edge cases arrive on a schedule: each edition's file contains at least one shape of row you have never seen.
A unit test is a different instrument from the schema tests most dbt projects already carry. Jonathan Neo's April 2023 piece for the dbt developer blog, on building a Kimball dimensional model with dbt, applies not_null and unique tests to the surrogate and natural keys of each dimension and runs them with dbt test after the build. Those tests interrogate the real data after it exists. A unit test interrogates the logic before any real data is involved, which is exactly what an annual pipeline needs, because for eleven months of the year the logic is all you have.
The eleven rows
Build one fixture edition and make every awkward case a citizen of it. Mine has eleven input rows across three staged inputs, and I can hold the whole thing in my head, which is the point.
| Row | Input | The case it exercises |
|---|---|---|
| 1 | registration | clean row, passes through untouched |
| 2 | registration | company is null |
| 3 | registration | status is cancelled |
| 4 | registration | email arrives in mixed case |
| 5 | registration | surname carries a diacritic |
| 6 | scan | clean row |
| 7 | scan | same vendor scan id as row 8 |
| 8 | scan | duplicate of row 7 |
| 9 | scan | timestamp before doors opened |
| 10 | meeting | both sides present |
| 11 | meeting | seller side missing |
The expected output is nine rows, and the arithmetic is worth stating in full. Eleven rows go in. The duplicate pair in rows 7 and 8 collapses to one surviving scan, which removes one row. The cancelled registration in row 3 is dropped by the status filter, which removes another. Eleven minus two is nine. Every other awkward row survives in modified form: the null company picks up the placeholder key, the mixed case email comes out lowercased, the diacritic surname is preserved in the display column and folded in the matching column, the before-doors scan carries a quarantine flag instead of being deleted, and the one sided meeting survives with an incomplete flag so that meeting counts can choose to include or exclude it.
In dbt the declaration looks like this, abbreviated to the duplicate case:
unit_tests:
- name: dedupe_resent_scan_file
model: int_scans_deduped
given:
- input: ref('stg_scans')
rows:
- {scan_id: "S-101", badge_id: "B-9", scanned_at: "2026-05-03 09:14:02"}
- {scan_id: "S-101", badge_id: "B-9", scanned_at: "2026-05-03 09:14:02"}
expect:
rows:
- {scan_id: "S-101", badge_id: "B-9"}
Two rows in, one row out. If a refactor ever breaks the merge condition the way the October bug did, this test fails in the same second the mistake is made, eleven months before the next real file arrives.
Why only eleven rows?
Because you have to read the expected output, and reading is the whole verification.
An expected table of nine rows fits on a screen. You can check it by eye against the input and convince yourself that every transformation it encodes is the one you intended. An expected table of nine hundred rows cannot be checked by anyone, which means it was almost certainly generated by running the model and pasting the result. A test built that way does not verify the logic. It enshrines whatever the logic did on the day the fixture was generated, bugs included, and from then on it defends those bugs against correction.
Small fixtures also keep the suite fast. Eleven rows run in well under a second per test. Twenty tests over fixtures this size finish in seconds, which means they run on every change without anyone deciding whether they can afford to. The dbt documentation is explicit that these tests belong in development and CI, before the build, and a suite only lives there if it is too fast to be worth skipping.
The discipline is one case per row and one behaviour per test. When the next edition's file produces a new shape of row, and it will, that shape becomes row twelve, with its expected output added by hand and argued about in review before it is committed.
Where the fixture rows come from
Not from imagination. Each awkward row in the fixture is a fossil of a real defect.
The duplicate scan pair exists because badge vendors resend files, and the scan fact design keeps the vendor scan identifier precisely so resent rows can be recognised. The null company row exists because self service registration forms make company optional and roughly 3 per cent of a typical file arrives without one. The cancelled registration exists because a cancelled row once leaked into an attendance count. The one sided meeting exists because meeting exports join two participant feeds that do not always both arrive.
This is also why the fixture belongs to the whole team and lives next to the declared grain of each table it feeds. The grain statement says what one row means. The fixture says what the transformation does to eleven specific rows on their way to that grain. Together they are the closest thing an annual pipeline gets to a specification.
What do the tests protect during a refactor?
The moment the suite earns its keep is the first serious refactor, and the dbt guidance quoted above names that moment directly. Before you touch the transformation, the tests state the current behaviour in eleven concrete rows. After the change, a green run says the behaviours you cared enough to encode are intact.
The suite makes no claim about behaviours you never encoded, and a refactor usually changes more than eleven rows can witness. That is a different job with a different tool: running old and new models over a full closed edition and diffing every published measure before the merge. The unit fixture answers in a second whether the logic still handles the awkward cases. The edition diff answers in minutes whether any published number moved. Neither substitutes for the other, and the pairing is what makes changing an annual pipeline in February feel routine instead of reckless.
Where this stops
A fixture encodes the failure modes you have already imagined, and the real file specialises in the ones you have not. No fixture of mine contained a scan whose timestamp was in the venue's timezone while the registration feed was in UTC, until an edition delivered exactly that and the fixture grew to include it. Unit tests ratchet: they prevent regression on known cases and are silent about unknown ones.
They are also silent about volume. Eleven rows will never reveal that the dedupe window function takes forty minutes on 1.5 million rows, or that a join explodes only when a company appears in 4,000 scans. Performance and scale live in rehearsal runs against full sized data, which is a scheduling concern for the data platform rather than a testing one.
And the expected output is only as correct as the person who wrote it. A reviewer has to actually recompute the nine rows from the eleven, by hand, once. Skip that and the suite is a mirror.
The first step is an afternoon. Pick the one transformation that scared you most at the last show close, write a fixture with a clean row and the two or three failure cases you remember, and hand compute the expected output. Commit it with the next change to that model and watch what the review conversation becomes.
Questions people ask about unit testing transformation sql
- What rows belong in a SQL unit test fixture?
- One clean row that should pass through untouched, plus one row for each awkward case the logic handles: a duplicate scan with the same vendor identifier, a registration with a null company, a cancelled registration, and a meeting with only one side present. Each row exists to exercise one branch of the transformation.
- How is a unit test different from a schema test in dbt?
- A unit test runs the model over fixed mock inputs before anything is built in production, so it exercises the logic itself. A schema test such as unique or not_null runs after the model builds and inspects the real data that came out. A healthy project runs both kinds on every change.
- How fast should SQL unit tests run?
- A fixture of around eleven rows compiles and runs in well under a second per test on any warehouse. Speed matters because a slow suite stops being run on every change, and a test nobody runs protects nothing. If your unit tests take minutes, the fixtures have grown past the point of being readable.
Related reading
- Badge scan fact table design that survives five editions of questions
- How to declare the event data warehouse grain before you build tables