data-eng-testing-patterns

Data Engineering Testing Patterns

Comprehensive testing strategies for data pipelines, SQL transformations, and data contracts.

Core Principles

1. Test data, not just code - Validate row counts, schema shapes, value distributions, and business rules alongside logic
2. Data contracts are boundaries - Every producer/consumer interface needs explicit schema and semantic contracts
3. Idempotent test fixtures - Tests must create, validate, and tear down their own state without side effects
4. Test at multiple granularities - Unit test individual transforms, integration test pipeline stages, end-to-end test full workflows
5. Fail fast with clear diagnostics - Store test failures with sample rows so debugging does not require re-running the full pipeline

Unit Testing SQL Transforms

Use pytest with DuckDB to validate individual transformations in memory

import pytest
import pandas as pd
from sqlalchemy import create_engine, text

@pytest.fixture(scope="module")
def test_engine():
    engine = create_engine("duckdb:///:memory:")
    yield engine
    engine.dispose()

@pytest.fixture
def seed_orders(test_engine):
    df = pd.DataFrame({
        "order_id": [1, 2, 3],
        "customer_id": [101, 101, 102],
        "amount": [50.00, 75.00, 200.00],
        "status": ["completed", "completed", "refunded"],
    })
    df.to_sql("orders", test_engine, if_exists="replace", index=False)
    return df

def test_revenue_excludes_refunds(test_engine, seed_orders):
    result = pd.read_sql(text("""
        SELECT customer_id, SUM(amount) AS total_revenue
        FROM orders WHERE status != 'refunded'
        GROUP BY customer_id
    """), test_engine)
    assert len(result) == 1
    assert result.iloc[0]["total_revenue"] == 125.00

See sql-transform-testing.md for:

• CTE isolation testing
• dbt unit test patterns with mock inputs
• Testing window functions and complex aggregations
• Snapshot and slowly changing dimension tests