Y

Data cleaning and quality · Intermediate

Data tests that fail loudly

3 min read

Why it matters

A checklist you run by hand gets skipped the week you are busy, which is the week the source changes. Data engineering and analytics engineering job descriptions ask for "data validation", "data quality frameworks" and "dbt tests". The idea is simple: every refresh runs the checks, and a failure stops bad data from reaching reports.

The concept

A data test is an assertion about data, not code:

  • schema tests: expected columns and types exist

  • not null and unique on keys

  • accepted values: payment_type in a known set

  • ranges: fares within plausible bounds

  • relationships: every PULocationID exists in the zone table

  • volume: row counts within an expected band compared with last period

  • freshness: latest timestamp within the expected delay

Decide per test whether a failure should stop the pipeline (keys, schema) or warn (volume drift, a few invalid rows). Thresholds make tests realistic: "fewer than 1% negative fares" instead of "no negative fares".

Worked example: pytest data tests for the taxi file

test_trips.py
import duckdb
import pytest

TRIPS = "https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2025-01.parquet"
ZONES = "https://d37ci6vzurychx.cloudfront.net/misc/taxi_zone_lookup.csv"


@pytest.fixture(scope="module")
def con():
    con = duckdb.connect()
    con.sql(f"create view trips as select * from '{TRIPS}'")
    con.sql(f"create view zones as select * from '{ZONES}'")
    return con


def scalar(con, sql):
    return con.sql(sql).fetchone()[0]


def test_expected_columns(con):
    columns = {row[0] for row in con.sql("describe trips").fetchall()}
    assert {"tpep_pickup_datetime", "PULocationID", "fare_amount", "total_amount"} <= columns


def test_zone_ids_are_unique(con):
    assert scalar(con, "select count(*) - count(distinct LocationID) from zones") == 0


def test_every_pickup_zone_exists(con):
    assert scalar(con, "select count(*) from trips where PULocationID not in (select LocationID from zones)") == 0


def test_payment_type_values(con):
    values = {row[0] for row in con.sql("select distinct payment_type from trips").fetchall()}
    assert values <= {0, 1, 2, 3, 4, 5, 6}


def test_negative_fares_below_five_percent(con):
    share = scalar(con, "select avg(case when fare_amount < 0 then 1 else 0 end) from trips")
    assert share < 0.05, f"{share:.2%} of fares are negative"


def test_volume_in_expected_band(con):
    rows = scalar(con, "select count(*) from trips")
    assert 2_500_000 < rows < 4_500_000, rows

Run with pytest -q test_trips.py. On the January 2025 file all six pass: negative fares are about 4.1%, below the 5% threshold. Point the fixture at a new month and the same tests guard it.

In a dbt project the same ideas are one line each in YAML:

models/schema.yml
version: 2
models:
  - name: stg_trips
    columns:
      - name: pickup_zone_id
        data_tests:
          - not_null
          - relationships:
              arguments:
                to: ref('stg_zones')
                field: zone_id
      - name: payment_type
        data_tests:
          - accepted_values:
              arguments:
                values: [0, 1, 2, 3, 4, 5, 6]
                quote: false

This is dbt 1.10 and later syntax (data_tests, with test settings under arguments); the data warehousing shelf builds the full project.

Common mistakes

  • Tests that always pass. Break the data on purpose once and confirm the test fails.

  • All-or-nothing thresholds. "No negative fares" fails every month and gets ignored. Measure the normal rate and alert on changes.

  • Tests nobody sees. A failure must reach a person: a failed CI run, a Slack alert, a blocked refresh.

  • Testing only the output. Test sources as they arrive, so you know whose problem a failure is.

Interview angle

"How would you know if your pipeline produced bad data?" Answer with the test categories, stop-versus-warn thresholds, and where alerts go. Naming dbt tests or Great Expectations shows you know the tools; explaining thresholds shows you have run them.

Practice task

  1. Add a freshness test: the latest pickup must be within the file's month.

  2. Add a relationships test for DOLocationID.

  3. Run the suite on February 2025 and on a file you deliberately corrupt (delete a column in a copy).

Further reading