Skip to main content

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.