Why Custom singular tests in dbt? - Purpose & Use Cases

The Big Idea

What if you could catch hidden data problems with just one simple test?

The Scenario

Imagine you have a big data table and you want to check if a specific rule is true, like "Are all user emails unique?" or "Is the total sales number above zero?" Doing this by looking at raw data or writing many manual queries can be confusing and slow.

The Problem

Manually writing queries for each check takes a lot of time and can easily lead to mistakes. You might forget to check some important rules or write inconsistent queries. It's hard to keep track of all these checks as your data grows.

The Solution

Custom singular tests let you write one clear, reusable test that checks exactly what you want. You can run these tests automatically every time your data changes, so you catch problems early without extra work.

Before vs After

✗ Before

SELECT COUNT(*) FROM users WHERE email IS NULL OR email IN (SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1);

✓ After

tests:
  - unique_email_check

What It Enables

It makes data quality checks simple, consistent, and automatic, so you trust your data without spending hours on manual queries.

Real Life Example

A company uses a custom singular test to ensure their daily sales data always has positive totals before running reports, preventing wrong business decisions.

Key Takeaways

Manual checks are slow and error-prone.

Custom singular tests automate and simplify data validation.

They help keep data trustworthy and save time.