Skip to content

Testing a data pipeline before the only show of the year opens

Data platformUpdated 2026-08-238 min read

In short

An annual show gives the pipeline one real execution a year, so the test suite has to run against a fixture edition all year round. The minimum set is a uniqueness test on every grain key, a not null test on every foreign key, and a referential test between each fact and the dimension it points at.

Doors open on Tuesday. The registration feed has been quiet since last May, the badge vendor changed their export format in November and told somebody who has since left, and the post-show pack has to go out nine days after close. Whatever is wrong with the pipeline, you will find out during the four days when nobody has time to fix it.

Testing a data pipeline in an event business is shaped entirely by that constraint. Consumer businesses get 365 executions a year and catch their defects on a rolling basis. You get one, so the tests have to run against something other than today's data for eleven months of the year, and they have to be strict enough that passing means something on the one day it counts.

The minimum test set

The dbt documentation, current in 2026, states the mechanism in one sentence worth internalising: "Data tests are assertions you make about your models and other resources in your dbt project". The implementation detail underneath is what makes them cheap. They are select statements that look for rows disproving the assertion, and "If the data test returns zero failing rows, it passes, and your assertion has been validated."

Four assertion types cover the floor, and the documentation ships all four.

  • Uniqueness on the grain key. One row per badge scan means the scan identifier appears once. This is the test that catches doubled files.
  • Not null on every foreign key. A fact row with a null company key silently vanishes from any inner join, and the report it vanishes from will look complete.
  • Referential integrity between fact and dimension. The documentation describes this as checking that "each customer_id in the orders model exists as an id in the customers table (also known as referential integrity)". Substitute exhibitor and edition and you have the two that break most often.
  • Accepted values on coded columns. Badge type, registration status, contract status. A vendor adding a fifth badge type without telling you is a routine occurrence and this is the only test that notices.

Jonathan Neo's April 2023 piece for the dbt developer blog, building a Kimball dimensional model with dbt, puts the first two on both the surrogate key and the natural key of each dimension and runs them after the build. Testing both keys is the detail people skip, and it is the one that catches a surrogate key generator producing collisions after a backfill.

Counting the suite

The inventory is smaller than it sounds. Take a warehouse with three fact tables, for badge scans, registrations and meetings, and six dimensions, for person, company, exhibitor, edition, date and badge.

Uniqueness runs on nine grain keys, one per table. Not null covers 30 columns, being 18 foreign keys across the three facts and the surrogate and natural key on each of the six dimensions. Referential integrity covers the same 18 fact to dimension pairs. Accepted values covers five coded columns.

Nine plus 30 plus 18 plus five is 62 tests, and a first pass at writing them is a day's work. Against a fixture edition the whole suite runs in about 40 seconds, which is the number that decides whether anyone runs it. A suite that takes 40 seconds gets run on every change without a decision being made. A suite that takes 20 minutes gets run before releases, then before major releases, then never.

What does a uniqueness test actually catch?

The failure it catches most often in event data comes from a vendor sending overlapping files, and it has nothing to do with your modelling.

Here is the one that keeps happening. The badge vendor exports day two at midnight and day three at midnight, and the day three export window starts six hours before the day two window ended, because someone configured it in local time against a UTC job. The loader receives 42,020 rows across the two files. The edition contains 38,900 distinct scan identifiers. The overlap is 3,120 rows, which is 7.4 per cent of what was loaded.

Nothing about that looks wrong. Both files parse. Both load. Row counts go up. Every exhibitor in the affected hall gets a lead count roughly 8 per cent too high, which is small enough to be believed and large enough to matter when it appears in a renewal conversation six weeks later.

A uniqueness test on the scan identifier returns 3,120 failing rows and stops the load. The test costs one line of YAML to write. Not having it costs an exhibitor performance report that overstates a whole hall, found by the exhibitor rather than by you.

That is a different instrument from a unit test with hand built mock rows, which asks whether the deduplication logic is correct given two input rows. Both are worth having and they fail on different days, which is the argument in unit testing transformation sql.

The fixture edition, and what it is for

You cannot run data tests against production data when production data arrives once a year. The substitute is a fixture edition held in the repository: a complete miniature show with the same shape as a real one.

Mine is about 3,900 scans, 1,150 registrations, 120 exhibitors and 40 sessions, loaded from files committed alongside the code. It is small enough to load in under a minute and complete enough that every model in the warehouse produces rows from it. Every awkward case that has ever hit a real load is represented: a scan against a badge that was never registered, an exhibitor with two contracts, a session with no attendees, a company name in a non-Latin script, a registration cancelled after the badge printed.

The fixture has one property that matters more than realism, which is that it never changes without a commit. When a test starts failing, the cause is the code, because the data is fixed. That single guarantee is what makes the suite useful as a change detector during the eleven months when there is nothing else to run it against.

Keeping the fixture loading on a schedule, so that credentials, warehouse permissions and library versions stay exercised through the quiet months, is the annual cadence rehearsal problem, and the test suite is the payload that makes each rehearsal worth running.

Where tests should run, and when

Three moments, with different suites at each.

On every commit, run the full suite against the fixture edition. Fast, deterministic, and the only feedback loop available for most of the year.

On every real load, run the suite against the loaded partition rather than the whole table. Scoping to one edition keeps the run cheap during show week and, more usefully, keeps the failure legible. A uniqueness test that fails across five years of history tells you something is wrong somewhere. A uniqueness test that fails on the edition you loaded four minutes ago tells you what to look at.

After show close and before the pack goes out, run a wider set that includes the checks too slow or too broad for a per-load run: totals against the source system, cross-edition comparisons, and the count of unresolved dimension stubs. This is also where a comparison against last year's published figures belongs, which for a refactor becomes a formal exercise in regression testing.

What happens when a test fails at 11pm on move-in day?

Decide this before it happens, and write it in the same file as the test.

Every test needs a severity and an owner. Uniqueness and referential failures should stop the load, because loading through them corrupts every downstream number and unwinding that during show week is worse than being late. Accepted values on a coded column usually warns, because a new badge type is more likely to be a legitimate new badge type than a corruption, and the right response is a mapping decision from a person rather than a halted pipeline.

The part people leave out is the runbook. A test that fails with no documented response produces the worst outcome available, which is somebody disabling it to get the load through and forgetting to re-enable it. Two sentences per test are enough: what it means when this fires, and who decides.

Where this stops

A passing suite proves the data has the shape you asked about. It says nothing about whether the shape is right.

Every one of the 62 tests above can pass on an edition where the badge readers in hall three were unplugged for a day. Uniqueness holds, foreign keys resolve, badge types are all recognised, and verified attendance is 4,000 short. Structural tests find structural problems. Volume anomalies need a different instrument, which is a comparison against the same edition last year with a tolerance, and that test only becomes meaningful once you have two comparable editions loaded.

The second limit is the fixture itself. It contains the failure modes somebody thought of, and the next vendor will invent a new one. When that happens the honest response is to add the case to the fixture and let it protect you from then on, which means the suite grows by one test per incident and never shrinks. That is the correct shape for a test suite in a data platform that gets one real execution a year.

Start with one test on one table. Add a uniqueness assertion on the grain key of your largest fact table and run it against the last edition you loaded. If it passes, you have spent ten minutes and learned something real. If it fails, you have just found out that a number you published last year was wrong, which is unpleasant on the day and considerably cheaper than finding out from an exhibitor.

Questions people ask about testing a data pipeline

What tests should a data pipeline have as a minimum?
Uniqueness on the grain key of every fact and dimension, not null on every foreign key and every surrogate key, referential integrity between each fact table and the dimensions it references, and accepted values on coded columns such as badge type and registration status. That set is small enough to write in a day.
How do you test a pipeline that only runs once a year?
Keep a fixture edition in the repository: a small, fixed dataset with the same shape as a real edition. Run the full suite against it on every change and on a scheduled rehearsal, so credentials, schemas and code paths stay exercised during the eleven months when no real data is moving.
Should a failing test stop the load?
Uniqueness and referential failures should stop it, because they corrupt every downstream number. Warnings suit tests where the threshold is a judgement, such as an unexpectedly high share of unresolved companies. Decide the severity of each test when you write it, and record who gets called when it fires during show week.

Related reading

All data platform articles