04. Custom Tests
Why Custom Tests?
Built-in tests help with common checks like unique, not_null, and accepted_values.
But in real projects, data quality rules are often more specific.
For example:
- amount should always be greater than 0
- customers should not have empty email values
- order quantity should not be negative
These rules are not always covered by default DBT tests, so we create custom tests.
Simple Rule for Freshers
A DBT custom test is simply a SQL query that returns rows that should fail.
- If the query returns 0 rows → test passes
- If the query returns any rows → test fails
That is the core idea.
Ready Data Set for Freshers
Let’s use a very simple ECOMM example.
Source table: raw_customers
CREATE TABLE raw_customers (
customer_id INT,
customer_name VARCHAR(100),
email VARCHAR(100),
signup_date DATE
);
INSERT INTO raw_customers (customer_id, customer_name, email, signup_date)
VALUES
(101, 'Alice', 'alice@gmail.com', '2024-01-10'),
(102, 'Bob', NULL, '2024-01-12'),
(103, 'Charlie', 'charlie@gmail.com', '2024-01-15'),
(104, 'Diana', '', '2024-01-18');
Source table: raw_orders
CREATE TABLE raw_orders (
order_id INT,
customer_id INT,
order_amount NUMERIC(10,2),
quantity INT,
order_status VARCHAR(50)
);
INSERT INTO raw_orders (order_id, customer_id, order_amount, quantity, order_status)
VALUES
(1, 101, 250.00, 2, 'paid'),
(2, 102, 0.00, 1, 'paid'),
(3, 103, -40.00, 3, 'cancelled'),
(4, 104, 199.99, 0, 'paid'),
(5, 999, 75.00, 1, 'paid');
This dataset intentionally contains invalid values for learning.
01. Custom Test for Positive Amount
Business rule
Every order amount should be greater than 0.
SQL logic
SELECT *
FROM {{ ref('orders') }}
WHERE order_amount <= 0
Generic custom test in Jinja
Create a file like:
tests/generic/positive_amount.sql
{% test positive_amount(model, column_name) %}
SELECT *
FROM {{ model }}
WHERE {{ column_name }} <= 0
{% endtest %}
Use it in schema.yml
version: 2
models:
- name: orders
columns:
- name: order_amount
tests:
- positive_amount
Why this fails
Rows with amount 0.00 and -40.00 are invalid.
02. Custom Test for Non-Empty Email
Business rule
Every customer email should not be null or empty.
SQL logic
SELECT *
FROM {{ ref('customers') }}
WHERE email IS NULL OR TRIM(email) = ''
Singular test file
Create file:
tests/no_null_or_blank_emails.sql
SELECT *
FROM {{ ref('customers') }}
WHERE email IS NULL OR TRIM(email) = ''
Why this fails
The rows for Bob and Diana have invalid email values.
03. Custom Test for Valid Quantity
Business rule
Order quantity should be greater than 0.
SQL logic
SELECT *
FROM {{ ref('orders') }}
WHERE quantity <= 0
Generic test in Jinja
{% test quantity_gt_zero(model, column_name) %}
SELECT *
FROM {{ model }}
WHERE {{ column_name }} <= 0
{% endtest %}
Usage in YAML
version: 2
models:
- name: orders
columns:
- name: quantity
tests:
- quantity_gt_zero
Why this fails
The row with quantity 0 is invalid.
04. Custom Test for Valid Customer ID
Business rule
Every order customer_id should refer to a valid customer.
SQL logic
SELECT o.order_id, o.customer_id
FROM {{ ref('orders') }} o
LEFT JOIN {{ ref('customers') }} c
ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL
Why this fails
The order with customer_id = 999 has no matching customer record.
How You Run These Custom Tests
After saving the files and YAML config, run:
dbt run
Then run:
dbt test
Expected output for this dataset
Since the sample data contains invalid records, the tests should fail with messages similar to:
1 of 1 tests failed
This is a good learning example because the learner can see exactly which rows are invalid.
Best Practices for Freshers
- Keep custom tests simple
- Start with one business rule per test
- Use clear file names
- Always write the SQL that returns bad rows
- Check the output after
dbt test
Summary
Custom tests are used when built-in tests are not enough.
For freshers, the easiest way to learn this is:
- create a small dataset
- add a business rule
- write SQL that returns bad rows
- run
dbt run - run
dbt test
This makes custom testing easy to understand and easy to practice.