Writing Singular Tests in dbt: A Practical Example

📘 Introduction

In this hands-on dbt tutorial, you’ll learn step-by-step how to create a singular test to validate custom business rules using SQL. Unlike built-in generic tests (like unique or not_null), singular tests allow you to write fully customizable SQL logic to enforce strict data quality checks.

✅ Prerequisites

☑️ A dbt project set up
☑️ Set up Medallion Architecture
☑️ Source data loaded into your data warehouse
☑️ Source configurations defined in sources.yml

💡What is a singular test in dbt?

A singular test in dbt is a custom SQL test that returns rows that should not exist.

✅ The test passes - if the query returns zero rows
❌ The test fails - if the query returns one or more rows

Singular tests are perfect for applying business logic validation.

🧠 Practical Example

Let's consider the model cleaned_course. The table in the warehouse contains the following data:

We want to validate that all records in the dim_course model have at least 10 credits. For this we'll create a singular test.

🗂️1️⃣ Create .sql file inside the tests/ folder

Inside the macros/ folder, create a new file called minimum_credits_test.sql.

✍️2️⃣ Write SQL Query

Inside minimum_credits_test.sql, add the following: