Skip to main content

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:

  1. create a small dataset
  2. add a business rule
  3. write SQL that returns bad rows
  4. run dbt run
  5. run dbt test

This makes custom testing easy to understand and easy to practice.