Data Pipeline Testing: Catch Bad Data Early

Set up data pipeline testing that catches bad data early: unit-test transforms, check uniqueness, nulls and relationships, and reconcile row counts.

A data pipeline can run without a single error and still deliver wrong numbers. A source system changes a field format, a join quietly duplicates rows, a filter drops a region, or yesterday's file arrives empty. The pipeline reports success, dashboards update and nobody notices until a manager asks why revenue doubled overnight. Data pipeline testing is how you catch these problems before reports and downstream systems are affected. It combines tests of the code that transforms data, checks on the data itself and reconciliations against the source. This guide explains each layer, what to test first and how to respond when tests fail.

Why data pipeline testing is different

Testing application code usually checks that functions behave correctly for given inputs. Data pipelines add another dimension: the inputs change every day and are controlled by other systems. Code can be correct while the data is wrong. Effective data pipeline testing therefore covers:

Code tests: does the transformation logic do what it should?
Data tests: does the data meet expectations after each step?
Reconciliation: does the output match the source in count and value?
Freshness: did new data arrive when expected?

Layer 1: test transformation code

Write unit tests for transformation functions or SQL models using small, known inputs:

Typical records produce the expected output
Empty, null and unusual values are handled as intended
Duplicates are removed or merged correctly
Date, currency and time zone conversions are right
Business rules, such as how discounts are applied, match the agreed definition

Keep test inputs small and readable so failures are easy to understand. Run code tests automatically whenever the pipeline changes.

Layer 2: test the data itself

Data tests are assertions about the data a pipeline produces. The dbt documentation describes data tests as assertions you make about models and other resources, and lists four generic tests that ship with dbt: unique, not_null, accepted_values and relationships (dbt: data tests). These four cover many everyday checks, whatever tool you use:

Unique: identifiers such as order IDs appear only once
Not null: required fields always have values
Accepted values: status or category fields only contain known values
Relationships: every foreign key, such as a customer ID on an order, matches a record in the related table

Add custom checks for rules specific to your business, such as order totals never being negative or end dates never preceding start dates.

Layer 3: reconcile with the source

Reconciliation compares output with input to catch silent losses or duplication:

Row counts between source and target, allowing for known filters
Sums of key amounts, such as invoice totals per day
Counts by important groups, such as region or product line
Samples of individual records traced from source to target

Small, explainable differences are normal when filters or deduplication apply; unexplained differences need investigation.

Layer 4: check freshness and volume

Many problems show up as missing or unusual volumes:

No new data since the last run
Far fewer or far more rows than usual
The latest record timestamp is older than expected

Set expected ranges based on history and alert when data falls outside them.

Worked example: a test plan for an orders pipeline

The table below is a hypothetical test plan for a pipeline that loads orders from an e-commerce system into a reporting warehouse. It shows the structure, not real thresholds.

LayerTestRuleIf it failsCodeDiscount calculationKnown inputs give agreed outputsBlock deploymentDataOrder ID uniqueNo duplicates in orders tableStop load, alert data ownerDataCustomer relationshipEvery order has a matching customerWarn; quarantine affected rowsDataStatus accepted valuesStatus in agreed listWarn and report new valuesReconciliationDaily totalsTarget totals match source within agreed toleranceAlert and hold reportsFreshnessLatest order timeNewer than expected windowAlert on-call person

Decide what failure means

Not every failed test should stop the pipeline. Classify tests by severity:

Blocking: stop the load or hold reports, because using the data would be harmful
Warning: load the data but alert someone to investigate
Informational: record for trend analysis

Agree severities with the people who use the data, and document who responds to each type of failure.

Test where problems enter

Put checks close to where problems are likely to start. Validate incoming data at extraction to catch source changes early, check transformations after each major step and run reconciliations before data is published to reports or other systems. The earlier a problem is caught, the less it spreads.

Test changes before deployment

When pipeline code changes, run code tests and data tests against a copy of recent data before deploying. Compare outputs from the old and new versions on the same input to spot unexpected differences. This catches changes that are technically valid but alter results.

Build realistic test data

Code tests need inputs that reflect real problems. Collect examples of awkward records seen in production, such as missing fields, odd date formats, duplicate customers and unexpected characters, and turn them into anonymised test cases. Each time a new data problem causes an incident, add a test that would have caught it. Over time, this builds a library of cases that protects the pipeline against the mistakes that actually happen in your data.

Keep tests maintainable

Tests that fail constantly for known reasons are soon ignored. Review failing tests promptly, fix the cause or adjust the rule if expectations have genuinely changed, and remove tests that no longer serve a purpose. Name tests clearly so anyone can see what they check.

Connect testing to data quality ownership

Tests reveal problems; people fix them. Assign owners for important datasets who decide on rules and respond to failures. Many data quality problems start in source systems, so feedback to source owners matters. Our guide to CRM data quality covers improving data at the point it is created.

Monitor over time

Track test results over time to see patterns: which sources fail most often, which tests catch real problems and how quickly failures are resolved. Use this to prioritise improvements.

Build testing into pipeline design

Testing works best when planned from the start rather than added after problems appear. Our guide to ETL pipeline development covers designing pipelines with validation and monitoring built in.

Frequently Asked Questions

What is data pipeline testing?

It is the practice of testing transformation code, checking the data a pipeline produces, reconciling output with sources and monitoring freshness, so problems are caught early.

Which data tests should a team start with?

Uniqueness of identifiers, required fields not being null, accepted values for categories and relationships between tables, plus row count and total reconciliations.

Should every failed data test stop the pipeline?

No. Classify tests as blocking, warning or informational, based on how harmful it would be to use data that fails each check.

How do you catch source system changes?

Validate incoming data at extraction against the expected structure and value ranges, and alert when fields, formats or volumes change.

Trust the data your pipelines deliver

We add testing, reconciliation and monitoring to data pipelines so problems surface before reports go wrong. See our CRM and API integration services, or talk to us about data pipeline testing.

About the Author

Manav Sharma is the founder of Insyrge and a CRM solutions architect. He works across Zoho, Salesforce, HubSpot, Odoo and more, designing the CRM setups, integrations and automations that keep sales and operations teams working from the same data.