Singular Custom Tests
Singular tests are custom SQL checks written for one specific validation.
They are useful when:
- the rule applies to only one model
- the test needs custom joins or filters
- the logic is easier to write as a full SQL query
- you do not need to reuse the same test in
schema.yml
Folder Location
Singular tests should live directly inside:
tests/
Example:
my_first_dbt_project/
└── tests/
└── no_negative_amounts_in_silver_customer_orders.sql
Do not put singular tests inside tests/generic/.
Example: One Model Specific Test
Suppose this model creates completed customer orders.
File
models/silver/silver_customer_orders.sql
{{ config(materialized='table') }}
SELECT
o.ORDER_ID,
o.CUSTOMER_ID,
o.ORDER_DATE,
o.AMOUNT,
o.STATUS,
c.NAME AS CUSTOMER_NAME,
c.COUNTRY,
c.EMAIL AS CUSTOMER_EMAIL
FROM {{ ref('stg_orders') }} o
JOIN {{ ref('stg_customers') }} c
ON o.CUSTOMER_ID = c.CUSTOMER_ID
WHERE o.STATUS = 'completed'
You want one specific test:
Completed customer orders should never have a negative
amount.
Step 1: Create the Singular Test
File
tests/no_negative_amounts_in_silver_customer_orders.sql
SELECT *
FROM {{ ref('silver_customer_orders') }}
WHERE amount < 0
This test returns the bad rows. If it returns zero rows, the test passes.
Step 2: Run the Singular Test
dbt test --select no_negative_amounts_in_silver_customer_orders
You can also run all tests:
dbt test
DBT automatically finds singular tests in the tests/ folder.
Do Not Add Singular Tests to schema.yml
This is not the right pattern for a singular test:
models:
- name: silver_customer_orders
columns:
- name: amount
tests:
- no_negative_amounts_in_silver_customer_orders
That syntax tells DBT to look for a generic test macro.
If you want to add a test under schema.yml, write it as a generic test instead.
Example: Null Email Check for One Model
File
tests/validate_no_null_emails.sql
SELECT *
FROM {{ ref('stg_customers') }}
WHERE email IS NULL
Run it:
dbt test --select validate_no_null_emails
This is specific to stg_customers, so it can stay as a singular test.
For simple column null checks, DBT also has a built-in option:
models:
- name: stg_customers
columns:
- name: email
tests:
- not_null
Use the built-in not_null test when the rule is simply “this column cannot be null.”
Debug Checklist
If your singular test is not running:
- Make sure the file is inside
tests/. - Make sure the file is not inside
tests/generic/. - Do not add the test under
schema.yml. - Use
{{ ref('model_name') }}inside the SQL. - Run it by file name without
.sql.
Example:
dbt test --select validate_no_null_emails
Key Takeaways
- Singular tests are plain SQL files.
- Singular tests are for one specific validation.
- Singular tests live in
tests/. - Singular tests are not added in
schema.yml. - Use
ref()to point to the exact model being tested.