Singular tests

Singular tests are simple SQL queries that return failing records of a model or a resource. They are typically used for one-off checks or specific business rules that do not require reusability.

You can use Jinja functions like ref() and source() to make reference to the target resource.

For example, we can create a singular test that checks for suspicious payments by method in the stg_payments table:

with large_payments_by_method as (
    select 
        paymentmethod,
        amount,
        status
    from {{ source("stripe", "raw_payments") }}
    where status = 'success'
        and (
            (paymentmethod = 'bank_transfer' and amount > 10000) or
            (paymentmethod = 'credit_card' and amount > 5000) or
            (paymentmethod = 'gift_card' and amount > 1000)
        )
)

select *
from large_payments_by_method

Notice that the singular test accepts no parameters as it's only applied on the stg_orders model.

Create a singular test

To add a singular test to a model:

1. Open the Test case template and select / search for the Singular Test template.

2. Fill in the name, description, and SQL contents.

1. Click Save.

Once added, you can verify the test by running the model in Console.