Data quality testing 101: the four tests to add before anything else

Teams often reach for expensive data observability tooling before they’ve applied the basics consistently across their models. Before adding anything new, make sure these four test types are on every model that matters.

1. Not-null tests on required columns

Any column downstream logic depends on — primary keys, foreign keys, amounts used in aggregations — should have a not-null test. This sounds obvious, but the majority of data incidents traced back far enough turn out to be “a column that was supposed to always be populated, wasn’t, and nothing caught it for three weeks.”

columns:
  - name: order_id
    tests:
      - not_null
      - unique

2. Uniqueness tests on primary keys

Every table with a stated grain needs a uniqueness test on whatever column(s) define that grain. Duplicate rows are the second most common source of silently wrong dashboards — a metric that’s “off by a few percent” is almost always duplicate rows inflating a sum or count, not a logic error.

3. Relationship (referential integrity) tests

If a customer_id in your orders table is supposed to exist in your customers table, test it. Relationship tests catch upstream deletions, failed syncs, and join logic that silently drops rows — problems that not-null and uniqueness tests won’t surface on their own.

columns:
  - name: customer_id
    tests:
      - relationships:
          to: ref('dim_customers')
          field: customer_id

4. Accepted-values tests on enum-like columns

Any column with a known, finite set of valid values — status, category, country code — should test against that list. This catches upstream schema drift (a new status value appearing that your logic doesn’t handle) before it silently breaks a CASE statement or filter downstream.

Where to run these, and how strict to be

Run all four as part of CI on every pull request that touches a model, not just on a schedule — catching a broken test before merge is much cheaper than catching it in production. For genuinely critical models (anything feeding finance or exec reporting), set tests to fail the build; for lower-stakes models, warn severity is a reasonable starting point while you build trust in the test suite.

Where to start if you have none of this today

Add not-null and unique tests to every primary key in your warehouse first — this is a single afternoon of work on most projects and catches the largest share of real incidents. Relationship and accepted-values tests can follow model by model as you touch them.