Testing Data Pipelines

What this skill is about

Testing Data Pipelines means proving that your data transformations, models, and metrics are correct, stable, and safe to change. For an Analytics Engineer, this covers column-level constraints (not null, unique), referential integrity (relationships), business-rule checks, schema contracts, metric regression detection, source-to-target reconciliation, incremental model safety, and CI automation so tests run on every change.

Why it matters for an Analytics Engineer

• Prevents broken dashboards and incorrect decisions by catching issues early.
• Enables safe refactors and faster iteration with confidence.
• Reduces on-call stress by making failures obvious and explainable.
• Builds trust with stakeholders through repeatable, auditable checks.

Who this is for

• Analytics Engineers who build and maintain dbt/SQL transformations.
• Data Engineers and BI Developers who want reliable production pipelines.
• Analysts moving into engineering practices and CI workflows.

Prerequisites

• Comfort writing SQL (SELECT, JOIN, GROUP BY, CASE).
• Basic dbt project structure and running commands (build, test, run).
• Familiarity with version control (Git) and code reviews.

Learning path

1. Start with generic tests: Add not_null, unique, and relationships checks to key models and columns.
2. Add business-rule tests: Write custom SQL tests that return failing rows for domain logic.
3. Define schema contracts: Lock column names and types; enforce contracts to prevent accidental changes.
4. Introduce regression checks: Compare core metrics to a baseline with small tolerances.
5. Reconcile sources and targets: Validate counts and key sums across ingestion and curated layers.
6. Harden incremental models: Test uniqueness, deduping, late-arriving data, and safe backfills.
7. Automate in CI: Run tests on pull requests to block breaking changes.

Worked examples (copy–paste ready)

1) dbt generic tests: not_null, unique, relationships

Add to your model YAML to assert data quality at the column level.

# models/staging/stg_orders.yml
version: 2
models:
  - name: stg_orders
    columns:
      - name: order_id
        tests:
          - not_null
          - unique
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id

These tests pass when zero rows violate the rule and fail otherwise.

2) Custom SQL test for a business rule

Business rule: shipped_at must be on or after order_date. Create a test file that returns violating rows.

-- tests/test_shipped_after_order.sql
select *
from {{ ref('fct_orders') }}
where shipped_at < order_date

Any returned row is a failure. Keep the logic simple and focused on the rule.

3) Schema tests and contract thinking

Lock down schema shape so accidental column/type changes fail fast.

# models/marts/fct_orders.sql
{{
  config(
    contract = {"enforced": true}
  )
}}

select
  cast(order_id as bigint) as order_id,
  cast(customer_id as bigint) as customer_id,
  cast(order_date as date)   as order_date,
  cast(total_amount as numeric(12,2)) as total_amount
from {{ ref('stg_orders') }}

# models/marts/fct_orders.yml
version: 2
models:
  - name: fct_orders
    columns:
      - name: order_id
        data_type: bigint
      - name: customer_id
        data_type: bigint
      - name: order_date
        data_type: date
      - name: total_amount
        data_type: numeric(12,2)

When contracts are enforced, mismatched names/types cause the build to fail.

4) Regression test for metric changes with tolerance

Compare today’s metric to a baseline snapshot and flag large drifts.

-- tests/test_revenue_regression.sql
with current as (
  select order_date, sum(total_amount) as rev
  from {{ ref('fct_orders') }}
  group by 1
),
baseline as (
  select order_date, sum(total_amount) as rev
  from {{ ref('fct_orders__baseline') }}
  group by 1
)
select c.order_date,
       c.rev as current_rev,
       b.rev as baseline_rev,
       case when b.rev = 0 then null else abs(c.rev - b.rev)/b.rev end as rel_diff
from current c
full outer join baseline b using (order_date)
where coalesce(abs(c.rev - b.rev)/nullif(b.rev,0), 0) > 0.02

Returns days where revenue differs by more than 2% from the baseline.

5) Source-to-target reconciliation (counts and sums)

-- tests/test_reconcile_orders.sql
with src as (
  select count(*) as cnt, sum(amount) as amt from raw.orders
), tgt as (
  select count(*) as cnt, sum(total_amount) as amt from {{ ref('fct_orders') }}
)
select 1
where (select cnt from src) != (select cnt from tgt)
   or (select amt from src) != (select amt from tgt)

Zero rows returned means counts and sums match.

6) Testing incremental models and safe backfills

Strategy: validate uniqueness, simulate full refresh in CI, and check for duplicates after backfills.

# Example unique test on the natural key of an incremental model
version: 2
models:
  - name: fct_events_inc
    columns:
      - name: event_id
        tests: [not_null, unique]

-- After a backfill, verify no duplicates
select event_id
from {{ ref('fct_events_inc') }}
group by 1
having count(*) > 1

# CI run (example commands)
dbt deps
# Full refresh in CI to ensure reproducibility
dbt build -s fct_events_inc --full-refresh

# .github/workflows/dbt.yml (illustrative)
name: dbt-ci
on: [pull_request]
jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - name: Set up Python
        uses: actions/setup-python@v5
        with:
          python-version: '3.11'
      - name: Install deps
        run: |
          pip install dbt-core dbt-postgres
      - name: Run tests
        env:
          DBT_USER: ${{ secrets.DBT_USER }}
          DBT_PASS: ${{ secrets.DBT_PASS }}
        run: |
          dbt deps
          dbt seed --full-refresh
          dbt build --fail-fast

Drills and exercises

• Add not_null and unique tests to every primary key in your marts.
• Add relationships tests for all foreign keys in facts to their dimensions.
• Write two custom SQL tests that encode business rules (date logic, allowable ranges).
• Enforce a schema contract on one critical model and fix any resulting type mismatches.
• Create a regression test for a top KPI with a 1–3% tolerance.
• Run a full-refresh in a scratch schema and confirm tests still pass.

Common mistakes and debugging tips

Mini project: Hardening a sales pipeline

Goal: Make a small sales pipeline production-safe in a day.

1. Add generic tests to keys in stg_customers, stg_orders, fct_orders.
2. Write two custom tests: (a) shipped_at ≥ order_date, (b) total_amount ≥ 0.
3. Enforce a contract on fct_orders and fix casts until it passes.
4. Create a regression test comparing daily revenue to a baseline with 2% tolerance.
5. Reconcile raw.orders to fct_orders on counts and sum(amount).
6. For an incremental fct_events_inc, add uniqueness tests, then run a full-refresh in a scratch schema and confirm zero duplicates after backfill.
7. Automate: run dbt build with fail-fast in CI on every pull request.

Practical projects

• Finance mart audit: Implement tests for P&L metrics, tolerances for month-over-month changes, and reconciliation to the GL feed.
• Marketing funnel QA: Validate user journeys with relationships tests and business rules for stage progressions.
• Event pipeline guardrails: Deduplication tests, late-arriving event handling, and incremental backfill safety checks.

Subskills

• Unit Tests For Transformations: Validate transformation logic in isolation with small fixtures to catch logic regressions early.
• dbt Generic Tests Not Null Unique Relationships: Enforce column-level and referential constraints using standard tests.
• Custom SQL Tests For Business Rules: Encode domain rules as SQL that returns violating rows.
• Schema Tests And Contract Thinking: Lock names and types so unintended changes fail builds.
• Regression Tests For Metric Changes: Compare metrics to a baseline with tolerances to detect drifts.
• Data Reconciliation Tests Source To Target: Confirm counts and key sums match between raw and curated layers.
• Testing Incremental Models And Backfills: Prove idempotency, deduplication, and safe reprocessing.
• CI Automation For Tests: Run tests on every change and block merges on failures.

Next steps

• Pick one critical model, add contracts, and make tests green.
• Schedule reconciliation queries and alert on failures.
• Set up CI to run dbt build with fail-fast; require it to pass before merging.