Skip to main content

03. Built-in Tests in DBT

What Are Built-in Tests in DBT?​

Built-in tests are the default DBT checks you define in your schema.yml file. They help you validate the quality of your cooked data before you trust it for reporting and dashboards.

In an ECOMM-style project, a common flow looks like this:

  1. You have cooked data in your warehouse or staging model.
  2. You add test rules in schema.yml.
  3. You run dbt run to build the models.
  4. You run dbt test to validate the data quality.

This is the exact sequence we will use for all 4 built-in tests.


Example Setup: ECOMM Models​

Imagine we have two cooked models:

  • customers
  • orders

The orders table contains sales records, and each order should:

  • have a unique order_id
  • not have missing customer_id
  • only use valid status values
  • always point to a valid customer in customers

Step 1: Cooked Data Exists​

Before testing, the data is already cooked into models like this:

SELECT *
FROM raw_orders;

or in DBT, the model may look like:

SELECT
order_id,
customer_id,
status,
created_at
FROM {{ source('raw', 'orders') }}

This means the model is ready to be built and tested.


Step 2: Add Tests to schema.yml​

Now we define the validation rules in schema.yml.

version: 2

models:
- name: orders
description: "Cooked ECOMM order data"
columns:
- name: order_id
tests:
- unique
- not_null

- name: customer_id
tests:
- not_null
- relationships:
to: ref('customers')
field: id

- name: status
tests:
- accepted_values:
values: ['pending', 'paid', 'shipped', 'cancelled']

This is where DBT stores the rules for built-in tests.


Step 3: Run dbt run​

Once the model is defined, build it:

dbt run

This compiles the SQL and creates the tables/views in the warehouse. After this step, the cooked data is available for validation.


Step 4: Run dbt test​

Now run the tests:

dbt test

DBT will generate SQL for each test and check whether the conditions pass.

If a query returns any rows, the test fails.


1️⃣ unique​

Purpose​

Checks whether a column has duplicate values.

This is commonly used for primary keys such as order_id.

Data setup​

Create the base/source table rows before testing:

INSERT INTO raw_orders (order_id, customer_id, status)
VALUES
(1, 101, 'paid'),
(2, 102, 'shipped'),
(1, 103, 'cancelled');

This is the source data that feeds the cooked orders model. Here, order_id = 1 appears twice, so the unique test should fail.

Where to check in SQL​

SELECT order_id, COUNT(*) AS cnt
FROM {{ ref('orders') }}
GROUP BY order_id
HAVING COUNT(*) > 1;

If this query returns rows, the unique rule is broken.

schema.yml setup​

version: 2

models:
- name: orders
columns:
- name: order_id
tests:
- unique

dbt run​

dbt run --select orders

dbt test​

dbt test --select orders

Expected output​

When the duplicate exists, the result should fail:

1 of 1 tests failed

or a failure summary like:

Failure in test unique_orders_order_id (models/orders.yml)

If there are no duplicates, the result would be:

1 passed, 0 failed

2️⃣ not_null​

Purpose​

Checks whether important columns contain missing values.

This is used for fields like customer_id, email, or created_at.

Data setup​

Create the source rows before the test:

INSERT INTO raw_orders (order_id, customer_id, status)
VALUES
(1, 101, 'paid'),
(2, NULL, 'shipped'),
(3, 103, 'cancelled');

The second row has a missing customer_id, so the not_null test should fail.

Where to check in SQL​

SELECT *
FROM {{ ref('orders') }}
WHERE customer_id IS NULL;

If this query returns rows, the not_null rule fails.

schema.yml setup​

version: 2

models:
- name: orders
columns:
- name: customer_id
tests:
- not_null

dbt run​

dbt run --select orders

dbt test​

dbt test --select orders

Expected output​

1 of 1 tests failed

The failing result will show the rows where customer_id is null.

If the column is clean, the output would be:

1 passed, 0 failed

3️⃣ accepted_values​

Purpose​

Checks whether a column contains only allowed values.

This is useful for status, country_code, or payment_method.

Data setup​

Insert source-level rows before running the test:

INSERT INTO raw_orders (order_id, customer_id, status)
VALUES
(1, 101, 'paid'),
(2, 102, 'unknown'),
(3, 103, 'cancelled');

Here, 'unknown' is not in the accepted values list, so the accepted_values test should fail.

Where to check in SQL​

SELECT *
FROM {{ ref('orders') }}
WHERE status NOT IN ('pending', 'paid', 'shipped', 'cancelled');

If this query returns rows, the accepted values rule fails.

schema.yml setup​

version: 2

models:
- name: orders
columns:
- name: status
tests:
- accepted_values:
values: ['pending', 'paid', 'shipped', 'cancelled']

dbt run​

dbt run --select orders

dbt test​

dbt test --select orders

Expected output​

1 of 1 tests failed

If all statuses are valid, the result would be:

1 passed, 0 failed

4️⃣ relationships​

Purpose​

Checks whether values in one table exist in another model.

This is used for foreign keys such as customer_id in orders matching id in customers.

Data setup​

Insert data into both source tables before testing the relationship:

customers base table​

INSERT INTO raw_customers (id, name)
VALUES
(101, 'Alice'),
(102, 'Bob'),
(103, 'Charlie');

orders base table​

INSERT INTO raw_orders (order_id, customer_id, status)
VALUES
(1, 101, 'paid'),
(2, 999, 'shipped'),
(3, 103, 'cancelled');

Here, customer_id = 999 does not exist in raw_customers.id, so the relationships test should fail.

Where to check in SQL​

SELECT o.customer_id
FROM {{ ref('orders') }} o
LEFT JOIN {{ ref('customers') }} c
ON o.customer_id = c.id
WHERE c.id IS NULL;

If this query returns rows, relationship integrity is broken.

schema.yml setup​

version: 2

models:
- name: orders
columns:
- name: customer_id
tests:
- relationships:
to: ref('customers')
field: id

dbt run​

dbt run --select orders customers

dbt test​

dbt test --select orders

Expected output​

1 of 1 tests failed

The failure indicates that a customer_id in orders does not exist in customers.

If all IDs are valid, the output would be:

1 passed, 0 failed

Full Sequence for All 4 Built-in Tests​

This is the standard DBT process for every built-in test:

version: 2

models:
- name: orders
columns:
- name: order_id
tests:
- unique

- name: customer_id
tests:
- not_null
- relationships:
to: ref('customers')
field: id

- name: status
tests:
- accepted_values:
values: ['pending', 'paid', 'shipped', 'cancelled']

Then run:

dbt run --select orders customers
dbt test --select orders

The pattern is always:

  • define the test in schema.yml
  • prepare a dataset
  • run dbt run
  • run dbt test
  • check whether the result passes or fails

Why Built-in Tests Matter​

Built-in tests are useful because they help you:

  • catch bad data early
  • protect joins and key columns
  • keep reporting clean
  • improve trust in analytics data

Summary​

Built-in tests in DBT are the quickest way to check data quality.

For an ECOMM example, we usually define checks for:

  • unique on order IDs
  • not_null on key fields
  • accepted_values on status columns
  • relationships between orders and customers

And the standard flow is always:

schema.yml → cooked data → dbt run → dbt test