In this lesson, you will learn how to use built-in singular data tests in dbt to programmatically identify rows in your models that violate your defined business logic.
Why Singular Tests?
While we often use generic tests (like unique or not_null) for common patterns, sometimes you need to enforce a specific business rule that is unique to your data. A singular test is essentially a SQL file that contains a SELECT statement. In dbt, if this query returns any rows, the test fails; if it returns zero rows, the test passes.
Writing Your First Singular Test
Imagine you are building a retail pipeline. You have a stg_orders model and a business rule stating that an order_total should never be negative.
To enforce this, you create a new .sql file inside your tests/ directory (e.g., tests/assert_order_totals_are_positive.sql):
-- This query identifies rows that violate the business logic
SELECT
order_id,
order_total
FROM {{ ref('stg_orders') }}
WHERE order_total < 0
When you execute dbt test, dbt runs this query behind the scenes. If your database contains an order with a value of -5.00, the query returns that row. Because the result set is not empty, dbt flags the test as a failure, notifying you that your data integrity has been compromised.
How It Fits Your Workflow
These tests act as an automated "sanity check" for your transformations. By placing them in the tests/ directory, you ensure that every time you run your pipeline, these specific constraints are evaluated against the current state of your production data. This allows you to catch data quality issues at the point of transformation rather than discovering them later in your dashboards or downstream reports.
Since a singular test is just standard SQL, you can write complex joins or multi-column logic to check for relationships between tables—like ensuring an order_date is never earlier than a customer_signup_date.
Key Takeaway: A singular test is a SQL query that returns rows only when your business logic is violated; if the result is empty, the data is considered valid.