Why Your Definition of Done Needs Data Quality Checks

A story called "build the orders pipeline" can pass review, win the demo and land in Done while the table underneath has null customer IDs and duplicate order rows. Nobody cut a corner. The Definition of Done simply never asked about the data. Putting data quality in your definition of done fixes that, and it takes a few lines of config, not a new process.

Why code review can't catch bad data

Code review reads SQL. Bad data lives in rows. A reviewer can approve a join that looks right and still fans out duplicates on the real table, because the sample in the pull request never had a customer with two addresses.

Pipelines also fail quietly. An upstream feed stops loading, the job still succeeds on yesterday's data, and the dashboard renders happily. No error, no alert, just a wrong number somebody finds on Thursday.

Unit tests for your transformation logic help, but they run on fixtures you wrote. Data checks run against the actual data on every run, which is where the surprises are.

What to put in your definition of done for data quality

Pick four checks, each aimed at a failure you have probably already had:

• Freshness: the newest row is recent enough for whoever consumes it.
• Nulls: key columns are always populated.
• Uniqueness: one row per grain, so a join can't double the revenue.
• Row count: the table isn't empty, and doesn't vanish without anyone noticing.

Then add one line to your DoD: Every new or changed table has automated checks for freshness, nulls, uniqueness and row count, and they pass in CI. That line doubles as your data acceptance criteria. The story isn't done until the data proves it.

Add the checks with dbt data tests

dbt (a SQL transformation tool) ships four generic data tests: unique, not_null, accepted_values and relationships. Here are three of them on an orders model:

# models/marts/_fct_orders.yml
models:
  - name: fct_orders
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
      - name: customer_id
        data_tests:
          - not_null
      - name: status
        data_tests:
          - accepted_values:
              arguments:
                values: ['placed', 'shipped', 'completed', 'returned']

data_tests: is the current key (tests: still works as an alias), and nesting inputs under arguments: is available from dbt v1.10.5. Each test is a query that returns failing rows, so zero rows means a pass.

Freshness lives on the source, where the raw data lands:

# models/staging/_sources.yml
sources:
  - name: shop
    database: raw
    tables:
      - name: orders
        config:
          loaded_at_field: _loaded_at
          freshness:
            warn_after: {count: 6, period: hour}
            error_after: {count: 12, period: hour}

Run it with dbt source freshness. The freshness docs note that freshness under config: needs dbt v1.9 or later and loaded_at_field under config: needs v1.10 or later. They also name dbt freshness as the recommended command in dbt v2.0 and later, with dbt source freshness kept for backward compatibility.

Row count is a singular test: a SQL file in tests/ that returns a row only when something is wrong.

-- tests/assert_fct_orders_not_empty.sql
select count(*) as row_count
from {{ ref('fct_orders') }}
having count(*) = 0

Leave off the trailing semicolon, since dbt wraps the query and a semicolon can break it. Then build the model and its tests together:

dbt build --select fct_orders

Per the dbt build docs, a failing test on a model causes its downstream models to be skipped. Bad data stops at the story that produced it instead of spreading to everything built on top.

The objection: "this slows the sprint"

Yes, a little, the first time. A key-column check is three lines of YAML. The fair comparison is checks now versus the unplanned work later: the stakeholder ping, the backfill, the re-explained dashboard.

The sharper version of the objection is that checks will be flaky and block merges. Also fair. New checks need calibrating, so ship them as warnings and promote them to errors once they have stayed quiet for a few runs:

      - name: customer_id
        data_tests:
          - not_null:
              config:
                severity: warn

Keep the scope tight too. Test keys and grain columns, not every column. Tests cost warehouse compute, and a unique check on a large table scans it, so in CI build only what the story touched with dbt build --select.

Add one check to your next story

Don't spend a retro rewriting the whole DoD. On your next pipeline story, add the line, then put unique and not_null on the primary key. When that check catches something before the sprint review, you have the example that argues for the other three.

Models and agents answer with full confidence from duplicated or stale rows, so the Definition of Done is the cheapest place to stop bad data before an AI system hands it to a customer.