Data quality and testing

A pipeline that runs successfully can still publish wrong numbers. Bad data rarely fails loudly: a join silently duplicates rows, an upstream team renames a status, a feed stops arriving and yesterday’s figures look like today’s. Testing data is how you find out before your users do.

Dimensions of quality

“Good data” is vague. Break it into checks you can write:

Start with keys and the numbers that reach reports.

Tests in the pipeline

Put tests next to the transformations, and run them on every build. In dbt, you declare them in YAML beside the model:

models:
  - name: orders
    columns:
      - name: order_id
        data_tests:
          - not_null
          - unique
      - name: customer_id
        data_tests:
          - not_null
          - relationships:
              to: ref('customers')
              field: customer_id
      - name: status
        data_tests:
          - accepted_values:
              values: ['placed', 'shipped', 'returned']

Each test compiles to a query that returns failing rows; zero rows means a pass. Older dbt versions use the key tests instead of data_tests. Tools such as Great Expectations or Soda express the same ideas.

Give each test a severity. A duplicated primary key should stop the pipeline; a slightly higher null rate in an optional column might only warn.

Data contracts

Most quality problems start upstream, when a producer changes something without knowing who depends on it. A data contract is an explicit agreement between a producing team and its consumers about a dataset: its schema, types, meaning of each field, allowed values, freshness and who owns it.

Contracts work when they are checked by machines, not stored in a wiki. Validate the schema in the producer’s CI, so a breaking change such as dropping or renaming a field fails their build rather than your dashboard. Treat breaking changes like API changes: version them and give consumers notice.

Anomaly detection on volumes

Rule-based tests catch known problems. Volume checks catch the unknown ones: if a table normally receives about 2 million rows a day and today it received 40,000, something is wrong even if every row is valid.

• Compare today’s row count with the same weekday over recent weeks, since many datasets have weekly patterns.
• Alert when the count falls outside an expected range, rather than on any change.
• Apply the same idea to null rates, distinct counts and sums of key metrics.

Alerts that fire every day get ignored, so tune thresholds.

Quarantine instead of publishing

When a check fails, avoid publishing half-good data. Two common patterns:

• Quarantine rows that fail validation into a separate table with the reason attached, load the rest, and report how many were set aside.
• Write, audit, publish: build into a staging table or branch, run the tests there, and only then swap it into the table people query. Consumers see yesterday’s correct data rather than today’s wrong data.

Whichever you choose, keep the rejected rows. They are the evidence you need to fix the source.

Alerting owners

A failure only helps if it reaches someone who can act. Every dataset should have a named owning team, and alerts should go to that team with the table, the failed check, a sample of failing rows and a link to the run. Tell downstream users too, so they know a report is stale.

Checklist

• Primary keys tested for not_null and unique.
• Foreign keys tested with relationships, status columns with accepted_values.
• Freshness and volume checks on every source.
• Severity set per test, so only real problems block the pipeline.
• Failing data quarantined or held back, never silently published.
• An owner and an alert route for every dataset.