Custom singular tests in dbt - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a custom singular test in dbt?

A custom singular test in dbt is a user-defined check that runs a single SQL query returning zero or more rows. If the query returns any rows, the test fails. It helps validate specific data quality rules beyond built-in tests.

beginner

How do you define a custom singular test in dbt?

You create a SQL file in the 'tests' directory with a query that returns rows when the test fails, typically named `<model_name>__<test_name>.sql`. dbt automatically runs it during `dbt test`.

beginner

Example: What does this custom singular test do?

select * from {{ model }} where order_amount < 0

This test checks if any orders have a negative amount. If any rows are returned, it means there are invalid negative order amounts, so the test fails.

intermediate

Why use custom singular tests instead of built-in tests in dbt?

Custom singular tests let you check complex or unique business rules that built-in tests can't cover. They provide flexibility to enforce specific data quality needs.

beginner

Where do you place custom singular test SQL files in a dbt project?

Place them inside the 'tests' folder at the root or inside a subfolder like 'tests/custom'. This keeps tests organized and discoverable by dbt.

What does a custom singular test in dbt return when it fails?

A One or more rows

B No rows

C A boolean value

D An error message

A custom singular test returns rows when the test fails. If no rows are returned, the test passes.

Where should you place your custom singular test SQL files in a dbt project?

A In the 'models' folder

B In the 'tests' folder

C In the 'macros' folder

D In the root folder

Custom singular test SQL files belong in the 'tests' folder so dbt can find and run them.

Which of these is a reason to create a custom singular test?

A To check for null values in a column

B To enforce a unique constraint

C To create a new model

D To validate a complex business rule not covered by built-in tests

Custom singular tests are used to validate complex or unique business rules beyond built-in tests.

How do you run custom singular tests in dbt?

A Using 'dbt test'

B Using 'dbt run'

C Using 'dbt compile'

D Using 'dbt seed'

'dbt test' runs all tests including custom singular tests.

What happens if a custom singular test query returns zero rows?

A The test is skipped

B The test fails

C The test passes

D An error is raised

If the query returns zero rows, it means no issues were found and the test passes.

Explain what a custom singular test is in dbt and how it works.

Think about how dbt knows a test passed or failed based on query results.

Describe the steps to create and use a custom singular test in a dbt project.

Consider both the file location and how dbt executes the test.