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:
- You have cooked data in your warehouse or staging model.
- You add test rules in
schema.yml. - You run
dbt runto build the models. - You run
dbt testto 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:
customersorders
The orders table contains sales records, and each order should:
- have a unique
order_id - not have missing
customer_id - only use valid
statusvalues - 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:
uniqueon order IDsnot_nullon key fieldsaccepted_valueson status columnsrelationshipsbetween orders and customers
And the standard flow is always:
schema.yml → cooked data → dbt run → dbt test