How to test that a column is unique and not null in dbt

A primary key that is duplicated or empty breaks every join that uses it, usually without an error: the numbers are simply wrong. dbt has two built-in tests for it, unique and not_null, declared in a YAML file next to the model. They are the first tests worth adding to any model.

Copy and paste

# models/staging/_staging.yml
version: 2
models:
  - name: stg_orders
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null

dbt test -s stg_orders         # run the tests of one model
dbt build -s stg_orders        # build the model, then test it

Two more built-in tests, with their arguments under arguments:

      - name: status
        data_tests:
          - accepted_values:
              arguments:
                values: ['placed', 'shipped', 'returned']
      - name: customer_id
        data_tests:
          - relationships:
              arguments:
                to: ref('stg_customers')
                field: customer_id

Reading the result

1 of 2 PASS not_null_stg_orders_order_id ........................ [PASS in 0.01s]
2 of 2 FAIL 1 unique_stg_orders_order_id ......................... [FAIL 1 in 0.01s]
[ERROR]: in test unique_stg_orders_order_id (models/staging/_staging.yml)
  Got 1 result, configured to fail if != 0
  compiled code at target/compiled/shop/models/staging/_staging.yml/unique_stg_orders_order_id.sql

Each test is a query that returns the rows breaking the rule. FAIL 1 means one row came back: for unique, one order_id value that appears more than once. The compiled query is at the path dbt prints, so you can run it in the warehouse.

See the failing rows

--store-failures saves the failing rows in a table and prints a query for it:

dbt test -s stg_orders --store-failures

See test failures:
  select * from "shop"."main_dbt_test__audit"."unique_stg_orders_order_id"

The tables go to a schema named after your target schema plus _dbt_test__audit.

Things to know

• dbt build stops bad data. When a test fails, dbt build skips the models that depend on the tested one, so the error does not spread downstream. A warning does not stop anything; see how to make a test warn instead of fail.
• data_tests or tests. data_tests is the current name of the key. tests still works in dbt 1.12.
• Keys made of several columns need another test; see how to test a combination of columns.
• Sources too. The same tests work on the columns of a source table, which catches problems before any model runs.

Tested with dbt Core 1.12.