software-tester-skills

Data Pipeline & ETL Testing

Testing ETL/ELT pipelines, data warehouses, dbt models, Spark/Airflow jobs, and analytics data quality — schema, row-count and checksum reconciliation, null/uniqueness/referential rules, freshness, SCD logic, Great Expectations/dbt tests, and idempotent reruns.

Data bugs are silent: dashboards look fine and are wrong. Test data, not just code.

Test pyramid for data

  1. Unit — transformation functions/SQL CTEs on tiny fixtures (pytest + pandas/Polars/Spark local, dbt unit tests).
  2. Schema/contract — column names, types, nullability, enums; contract with upstream producers.
  3. Data quality (in-flight) — assertions on each run.
  4. Reconciliation — source vs target.
  5. Pipeline/integration — DAG runs end to end on a sample.
  6. Monitoring — freshness, volume, distribution drift in prod.

Data-quality rules (dimensions)

DimensionCheck
CompletenessNOT NULL critical columns; row counts vs source ±tolerance
UniquenessPK/natural key has no duplicates
ValidityEnums, regex, ranges, positive amounts
ConsistencyReferential integrity; cross-table sums equal; currency/units uniform
Timelinessmax(updated_at) within SLA
AccuracySpot-check vs source of truth; reconcile aggregates
DistributionMean/stddev/null-rate drift vs baseline
# dbt schema.yml
models:
  - name: orders
    columns:
      - name: order_id
        tests: [unique, not_null]
      - name: customer_id
        tests: [{ relationships: { to: ref('customers'), field: customer_id } }]
      - name: status
        tests: [{ accepted_values: { values: [created, paid, shipped, cancelled] } }]
# Great Expectations (fluent)
validator.expect_column_values_to_not_be_null("order_id")
validator.expect_column_values_to_be_between("amount", min_value=0)
-- reconciliation
SELECT (SELECT COUNT(*) FROM src.orders) - (SELECT COUNT(*) FROM dw.fact_orders) AS diff;
SELECT SUM(amount) FROM src.orders WHERE dt=:d EXCEPT SELECT SUM(amount) FROM dw.fact_orders WHERE dt=:d;

Pipeline behavior tests

  • Idempotent reruns: run twice → same result, no dupes (MERGE/upsert, partition overwrite).
  • Backfill a range; late-arriving & out-of-order data; SCD Type 2 effective-dating (no overlaps/gaps).
  • Schema drift: new/removed/renamed upstream column → alert, not silent null.
  • Failure: mid-load crash → atomic (no partial partition), resumable.
  • Timezone & DST in partition keys; PII masked in lower environments (test-data-engineering).
  • Performance: volume test at 10× rows; skew (hot keys); small-file problem.

Airflow: DagBag import test (no cycles/import errors), airflow tasks test, task-level idempotency.

database-testing, data-migration-testing, test-data-engineering, observability-testing