Skip to content
Tutorials Center
My learning

Topics

Lesson 21 of 41 ยท Tests and Documentation

Generic Tests: unique and not_null

Learn how dbt generic tests work: declare unique and not_null in YAML, see the SQL dbt really runs, and run the same checks in your browser.

A model that builds without errors can still be wrong. A key may repeat, a required column may be empty, a join may have silently dropped rows. dbt tests catch these problems by turning your expectations into queries: a test passes when its query returns zero rows.

The quickest way to add a test is a generic test, a reusable check you attach to a column in a YAML file. dbt ships four of them: unique, not_null, accepted_values and relationships. This lesson covers the first two, which together say "this column is a usable primary key".

The real dbt engine cannot run on this site, because it needs a warehouse connection. Everything that is dbt-specific (YAML, dbt test output) is shown read-only. The SQL that dbt generates is plain SQL, so those queries run live in the editors against the sample shop database.

Declaring tests in YAML

Tests live next to the model they protect, in a properties file such as models/staging/_stg_shop__models.yml:

YAML
version: 2

models:
  - name: stg_orders
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
      - name: customer_id
        data_tests:
          - not_null

Newer dbt versions call the key data_tests; older ones used tests, which still works as an alias. Run every test with dbt test, one model with dbt test --select stg_orders, or build and test together with dbt build.

Terminal
$ dbt test --select stg_orders
1 of 3 START test unique_stg_orders_order_id ......... [RUN]
1 of 3 PASS unique_stg_orders_order_id ............... [PASS in 0.21s]
2 of 3 PASS not_null_stg_orders_order_id ............. [PASS in 0.18s]
3 of 3 PASS not_null_stg_orders_customer_id .......... [PASS in 0.17s]

The test names are generated: <test>_<model>_<column>. That is the name you see in logs and in target/run_results.json.

What dbt really runs

For each test dbt compiles a SELECT that returns the bad rows. For not_null it is essentially:

SQL
select order_id
from stg_orders
where order_id is null

For unique it groups and keeps the duplicates:

SQL
select order_id, count(*) as n_records
from stg_orders
group by order_id
having count(*) > 1

dbt wraps the query so it only needs the number of rows (count(*) of failures). Zero means pass, anything above zero means fail. Run both checks on the sample data:

SQL The SQL behind unique and not_null ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Both counts are 0, so both tests would pass. The shop tables declare a primary key, so the database itself blocks duplicates. That is exactly why warehouses like BigQuery and Snowflake need dbt tests: they accept primary key declarations but do not enforce them.

Catching a real problem

Tests earn their keep when data is dirty. Here a small raw table has a repeated key and a missing value, the kind of thing a flaky source system produces. The query is what unique and not_null would run:

SQL Failing tests on dirty data

Press Run (or Ctrl + Enter) to see the result here.

Each test reports one failure. In a real project dbt test would print FAIL 1 for both and exit with a non-zero status, which is what stops a CI pipeline.

flowchart LR
  A["YAML: unique, not_null"] --> B["dbt compiles a SELECT of bad rows"]
  B --> C["Warehouse runs it"]
  C --> D{"Rows returned?"}
  D -->|"0"| E["PASS"]
  D -->|"1 or more"| F["FAIL"]

A test is just a query; zero rows means success.

Where to put these tests

A good habit: every model gets a unique and not_null on its primary key, and every foreign key gets not_null plus a relationship test. Test sources too, so you notice bad data before it flows into your models.

Some details worth knowing:

  • unique ignores NULL values. A column with two NULLs still passes unique, which is why you pair it with not_null.
  • Tests on large tables scan the whole table. For very big models, limit the cost with the where config (covered in severity and selection).
  • You can test a combination of columns with a package test such as dbt_utils.unique_combination_of_columns, introduced in packages.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. When does a dbt generic test pass?

    dbt compiles each test to a query that selects the failing rows. No rows means no failures.
  2. 2. Which pair of tests makes a column behave like a primary key?

    unique catches duplicates and not_null catches missing values; together they describe a primary key.
  3. 3. A column holds the values 1, 2, NULL, NULL. What does the unique test report?

    unique groups non-null values, so repeated NULLs are not duplicates. Add not_null to catch them.
  4. 4. Which command builds models and runs their tests in dependency order?

    dbt build runs models, tests, seeds and snapshots together, stopping downstream nodes when an upstream test fails.

Summary

  • A dbt test is a query that returns bad rows; zero rows means pass.
  • unique and not_null are generic tests you declare per column in YAML.
  • Tests have generated names like unique_stg_orders_order_id.
  • unique ignores NULLs, so pair it with not_null.
  • Warehouses often do not enforce keys, so dbt tests are your safety net.

Next: Relationships and accepted_values Tests