Should you unit test ETL?

Yes, unit test the transform step of an ETL job, one test per transformation, with hand-built input rows and expected output rows. The extract and load steps hold no logic of your own, so a freshness alert and a row-count comparison with the source watch them instead.

How do I test a dbt model?

Test a dbt model with a dbt unit test, which replaces each ref() input with rows written in YAML and compares the output with expected rows. Pick input rows that change the output when a join or filter is wrong.

Do data quality checks replace pipeline tests?

No, data quality checks do not replace unit tests of the transformations, because they find different failures. A not_null or unique check catches a source that sends broken rows, but a join that counts an account twice passes both checks. Run the checks on every load and the unit tests in CI.

Should I test data pipelines?

Verdict

Yes

Yes, give each transformation step of a recurring data pipeline one test that runs it on a few hand-built input rows, chosen so that a wrong join or filter changes the result, and compares the output with rows you worked out by hand.

Why

Blast radiusinternal
Change frequencyregularly
Detectabilityeventually
Reversibilitywith-effort
Test costmoderate

Give each transformation in a recurring data pipeline one test, on a few rows built by hand. The typical case is a nightly job that loads product data into a warehouse for the company's dashboards. Blast radius is internal, because staff read the numbers, and Change frequency is regularly, because new metrics change the transformations monthly. Detectability is eventually, because a wrong join gives a plausible total that nobody reports. Reversibility is with-effort, because the fix reruns every partition since the change, and Test cost is moderate, because a test needs a few mocked input rows, so rule R12 gives Test minimally.

When the decision changes
When	Decision	Why
The pipeline's output feeds a feature customers see, such as product recommendations	Test each transformation's main path and its likeliest edge cases in CI	Blast radius rises to users
The pipeline totals the usage that the billing system charges customers for	Test mandatory: the main path, every known failure and the billing-period boundaries, with a second person reviewing the tests	Blast radius rises to money and Reversibility to costly, because a wrong charge ends in refunds
A pipeline step must drop the email and phone columns before analysts query the warehouse	Test mandatory: assert that no output table holds a personal field	Blast radius rises to safety-or-legal and Detectability to never, because a leaked column looks like normal output
The main risk is a source system that renames columns, adds nulls or changes units without notice	Test it differently: run schema, null and range checks on every load, and stop the load on a failed check	Test cost rises to heavy, because nobody can write a fixture for every way a source changes
A one-time backfill loads two years of history for internal reports	Do not test the backfill; after the run, compare row counts per month with the source	Change frequency falls to once while Blast radius stays internal
A managed connector copies source tables unchanged, and a freshness alert fires when a table stops loading	Do not test the copy step; keep the freshness alert	Change frequency falls to rarely and Detectability moves to same-day, because only connector settings change and the alert fires within a day

What breaks if you don't test

A join to a history table, with one row per plan change, repeats each account once per row and inflates every count. An inner join to a dimension with a missing key drops rows without an error. The output keeps the right columns and believable numbers, so the dashboard shows them for weeks before anyone compares them with another source.

What you lose if you over-test

The common over-test compares a full production-sized run with a stored copy; new data arrives every night, so it fails every night and the team learns to ignore it. A test that mocks the warehouse client and asserts the SQL string passes when the SQL is wrong. Tests for steps that only rename columns add fixtures to update, while a wrong name already fails the next step with a missing-column error.

How to test

Write one unit test per transformation, on the main path. For dbt models, unit tests, available since dbt 1.8, mock each input as rows in YAML and compare the output with expected rows. For Python steps, compare small DataFrames with assert_frame_equal. Choose input rows that change the result when the logic is wrong:

For each join, a key that appears twice in the joined table.
For each aggregation, two rows that must land in the same group.
In Airflow, one DAG loader test that fails on import errors.

Checks on incoming data, such as dbt data tests, are a separate control that runs on every load.

Procedure and references

When the answer changes

The output reaches customers, billing or a partner.
A step removes personal data before others read it.
The pipeline only copies tables, with no transformation of your own.

Real incident + Code example

The upgrade that counted twice

On a SaaS product I worked on, a nightly dbt model counted weekly active accounts for the growth dashboard. A change split the count by plan and joined events to account_plans, one row per plan change, on account_id alone. An upgraded account counted under both plans, and the weekly total came out 9% above the product database's count. Three weeks later an analyst found the gap. The fix joined on the plan row valid on the event date, we reran 21 daily partitions, and we added this test:

unit_tests:
  - name: upgraded_account_counts_once_per_week
    model: weekly_active_accounts
    given:
      - input: ref('stg_events')
        rows:
          - {account_id: 1, event_date: "2026-03-03"}
          - {account_id: 1, event_date: "2026-03-05"}
          - {account_id: 2, event_date: "2026-03-04"}
      - input: ref('stg_account_plans')
        rows:
          - {account_id: 1, plan: free, valid_from: "2026-01-01", valid_to: "2026-02-10"}
          - {account_id: 1, plan: pro, valid_from: "2026-02-10", valid_to: null}
          - {account_id: 2, plan: free, valid_from: "2026-01-05", valid_to: null}
    expect:
      rows:
        - {week_start: "2026-03-02", plan: free, active_accounts: 1}
        - {week_start: "2026-03-02", plan: pro, active_accounts: 1}

The old join on account_id alone reports two free accounts and fails the test above, which expects one active account per plan.

Sources

docs.getdbt.com/docs/build/unit-tests

Should I test data quality?Test it differently
Should I test SQL queries?Yes
Should I test data science code?Yes
Should I test with production data?Test it differently
Should I test stored procedures?Yes

FAQ

Should you unit test ETL?: Yes, unit test the transform step of an ETL job, one test per transformation, with hand-built input rows and expected output rows. The extract and load steps hold no logic of your own, so a freshness alert and a row-count comparison with the source watch them instead.
How do I test a dbt model?: Test a dbt model with a dbt unit test, which replaces each ref() input with rows written in YAML and compares the output with expected rows. Pick input rows that change the output when a join or filter is wrong.
Do data quality checks replace pipeline tests?: No, data quality checks do not replace unit tests of the transformations, because they find different failures. A not_null or unique check catches a source that sends broken rows, but a join that counts an account twice passes both checks. Run the checks on every load and the unit tests in CI.