Custom singular tests in dbt - Step-by-Step Execution

Concept Flow - Custom singular tests

Write SQL query for test

↓

Define test in schema.yml

↓

Run dbt test command

↓

dbt executes test query

↓

Check if query returns rows

↓

Test fails

↓

Report result

This flow shows how you write a custom singular test as a SQL query, define it in dbt, run it, and dbt checks if the query returns any rows to decide pass or fail.

Execution Sample

dbt

SELECT id FROM {{ ref('customers') }} WHERE email IS NULL;

-- In schema.yml:
-- tests:
--   - custom_singular_test

-- Run: dbt test

This test checks if any customer has a NULL email; if yes, test fails.

Execution Table

Step	Action	SQL Query Result	Test Outcome	Explanation
1	Run custom singular test query	Returns rows with ids 3, 7	Fail	Query found customers with NULL email
2	dbt detects rows returned	Rows exist	Fail	Any rows mean test fails
3	Report test result	Fail	Fail	Test fails because condition is not met
4	If no rows returned	No rows	Pass	Test passes if query returns no rows

💡 Test stops after checking if query returns any rows; rows mean fail, no rows mean pass.

Variable Tracker

Variable	Start	After Query Execution	Final
query_result_rows	empty	[3,7]	[3,7]
test_status	undefined	Fail	Fail

Key Moments - 3 Insights

Why does the test fail if the query returns rows?

Because custom singular tests in dbt are designed so that returning any rows means the test condition is broken, as shown in execution_table step 2.

What if the query returns no rows?

If no rows are returned, it means the condition is met and the test passes, as explained in execution_table step 4.

How do you define a custom singular test in dbt?

You write a SQL query that returns rows when the test fails, then reference it in schema.yml under tests, as shown in execution_sample.

Visual Quiz - 3 Questions

Look at the execution_table, what does dbt conclude when the query returns rows?

A The test passes

B The test fails

C The test is skipped

D The test is retried

💡 Hint

See execution_table row 2 where rows returned lead to test failure.

At which step does dbt decide the test outcome?

A Step 2

B Step 1

C Step 3

D Step 4

💡 Hint

Check execution_table row 2 where dbt detects rows and sets test status.

If the query returned no rows, what would be the test outcome?

A Fail

B Error

C Pass

D Unknown

💡 Hint

Refer to execution_table row 4 where no rows means test passes.

Concept Snapshot

Custom singular tests in dbt:
- Write a SQL query returning rows if test fails
- Define test in schema.yml referencing the SQL
- Run 'dbt test' to execute
- If query returns rows, test fails
- If no rows, test passes

Full Transcript

Custom singular tests in dbt work by writing a SQL query that returns rows when a condition is broken. You define this test in your schema.yml file. When you run 'dbt test', dbt runs the query. If the query returns any rows, dbt marks the test as failed. If no rows are returned, the test passes. This method helps you check specific data quality rules easily by writing simple SQL queries.