How to test a data pipeline

Most advice about testing data pipelines is advice about testing software, repeated louder. It transfers badly, and the reason is worth saying plainly before any of the practical parts.

A unit test asks whether your code is correct. A data check asks whether today's data is what you expected. Those are different questions, and the second one is the one that pages you at 3am. A pipeline whose SQL has not changed in eight months can start producing nonsense tomorrow because an upstream team added a currency, renamed a status, or backfilled a year of rows at midnight. No amount of testing the SQL finds that, because the SQL is fine.

This page is how we think the problem decomposes, and what we have built for each part.

Three questions, not one

Everything useful you can check falls into one of three questions, and they fail at different times for different reasons.

1. Will it run at all? Does the SQL parse, in this dialect, against tables and columns that exist? This is the cheapest question and the only one you can answer without touching data. Get it wrong and you find out at 3am from a scheduler.

2. Is the data it produced sane? Did the row count collapse, did a key column go null, does the total reconcile? This is the question that actually protects you, and it is the one most teams under-invest in.

3. Did the logic change meaning? If you edit a step, does it still turn the same input into the same output? This needs fixed input you control, which is the one piece we have not shipped yet — see Pipeline Tests.

The order matters. Question 1 is nearly free, so there is no excuse for skipping it. Question 2 is where the value is. Question 3 is the most rigorous and the most work, and a team that does 1 and 2 well is in far better shape than one that attempts 3 and neglects the others.

Question 1: validate before you activate

Every SQL step can be validated before it runs for real. We send a dialect-correct dry run to the warehouse and show you what it says — the vendor's own error, not our paraphrase of it.

That matters more than it sounds. A real example, from building the demo estate behind these docs:

SQL access control error: Insufficient privileges to operate on table
'MRR_BY_MONTH'. Your primary role DATA_CONDUCTOR_ROLE must have OWNERSHIP
granted on TABLE ANALYTICS.MARTS.MRR_BY_MONTH.

That is Snowflake talking. It names the role, the object and the exact missing grant, and the fix took one line. A tool that caught the same failure and reported "validation failed" would have cost an hour.

Validation runs before activation, not after deployment, which is the whole point: the cheapest moment to find a broken reference is while you are still looking at the query.

Question 2: assert the things that would hurt

An assertion is a query plus an expectation, attached to a step. The usual shape is a query that looks for trouble, with the expectation that it returns nothing.

Get that inversion the right way round — you write the query that finds the bad rows. People reach for the opposite instinctively, and a check that confirms everything is fine is much harder to write and much easier to write wrongly.

What is worth asserting:

  • Row counts in a range, never an exact number. An exact count drifts, goes red every week, and trains everyone to ignore it.
  • Nulls absent from columns that must never be null. Cheap, and catches a surprising share of upstream changes.
  • Totals reconciling against a control figure you trust from elsewhere. The strongest check you can write, and the most work.
  • Keys unique where you expect uniqueness. A silent fan-out from a join is the classic way revenue doubles in every chart downstream.

What is not worth asserting: anything that only confirms the step did not error. The run status already says that. A check that cannot fail independently of the run is decoration.

Several checks can share one assertion by returning a row per problem, each naming itself — then a failure tells you which invariant broke without opening the query. Pipeline Tests has a worked example against a revenue mart.

Decide what failure means

This is the part most tools skip, and it is at least as important as the check. A failing assertion can warn, pause or stop the run, and choosing well is what keeps people trusting the gates.

Use it when
Warn You want to know, but the run should finish. Drift you are monitoring rather than policing.
Pause The load worked and the data is telling you something. A human should look before downstream work builds on it.
Stop The step produced something wrong. Continuing makes the mess bigger.

The distinction people miss is between pause and stop. If invoices have gone unpaid for sixty days, nothing is broken in your pipeline — the load did its job and the business has a collections problem. Stopping the run punishes the pipeline for being correct. Pause it, let a person look, and leave the data where it is.

If your pipeline produced duplicate months in a revenue mart, that is different. The pipeline is the thing that is wrong, and every report built on it between now and the fix inherits the error. Stop.

A gate everyone ignores is worse than no gate

The failure mode is social, not technical. One check that goes red every Tuesday for a reason nobody acts on teaches a team that red means nothing — and the next red, which mattered, gets the same shrug. Fewer checks, each of which would genuinely change what someone does, beats comprehensive coverage nobody reads.

Where this sits in a deploy

Assertions are attached to steps, and steps belong to pipeline versions. A deployment names a version and keeps running it, so editing a pipeline that has an active deployment creates a new version rather than rewriting the one in production — your checks and your logic move together, and tonight's run is still the thing you deployed. See Deployments.

Variables are per-environment, QA and PROD both pinned, so the same step can be checked against a staging table before it is pointed at a production one. See Your First Pipeline.

What we have not built

Fixture-based end-to-end tests — question 3 — are in development, not available. Until they land, the honest position is that we help you check what happened, and not yet whether the logic still means what it meant. The card is on the Data Tools hub, marked coming soon, and Pipeline Tests describes the shape it will take.

We would rather say that than describe a roadmap in the present tense.