Custom singular tests in dbt - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of a simple custom singular test in dbt

Given the following dbt custom singular test code, what will be the output when the test is run?

dbt

select * from {{ ref('orders') }} where order_amount < 0

A Returns no rows because order_amount cannot be negative

B Returns all rows from 'orders' where order_amount is positive

C Raises a compilation error due to invalid syntax

D Returns all rows from 'orders' where order_amount is negative

💡 Hint

Think about what the SQL query is filtering for.

The test selects rows where order_amount is less than zero, so it returns all such rows. This helps identify invalid negative amounts.

🧠 Conceptual

intermediate

Purpose of custom singular tests in dbt

What is the main purpose of creating a custom singular test in dbt?

A To check a condition that applies to the entire table or dataset as a whole

B To validate individual rows based on column values

C To generate documentation for models automatically

D To create new tables from existing data

💡 Hint

Think about whether the test checks rows or the whole dataset.

Custom singular tests check conditions that apply to the entire dataset, such as ensuring no negative values exist anywhere.

🔧 Debug

advanced

Identify the error in this custom singular test

What error will occur when running this custom singular test in dbt? ```sql select count(*) from {{ ref('customers') }} where email is null ```

A Compilation error because the test must return rows, not an aggregate

B No error; test runs and returns count of customers with null email

C Runtime error due to missing table 'customers'

D Syntax error because 'count(*)' is not allowed in singular tests

💡 Hint

Consider what a singular test expects as output.

Singular tests can return a single row with an aggregate value. This query returns the count of customers with null emails, which is valid.

❓ data_output

advanced

Result of a custom singular test with a condition

Consider this custom singular test SQL: ```sql select case when count(*) = 0 then 0 else 1 end as test_result from {{ ref('sales') }} where sale_date > current_date ``` What will be the value of 'test_result' if there are no future sales?

A NULL

B 1

C 0

D Raises an error because current_date is invalid

💡 Hint

Think about what count(*) returns when no rows match.

If no sales have a sale_date in the future, count(*) is zero, so the case returns 0.

🚀 Application

expert

Designing a custom singular test for data freshness

You want to create a custom singular test in dbt to check if the latest update timestamp in your 'events' table is within the last 24 hours. Which SQL query correctly implements this test?

A select case when max(updated_at) >= current_timestamp - interval '1 day' then 0 else 1 end as freshness_test from {{ ref('events') }}

B select * from {{ ref('events') }} where updated_at < current_timestamp - interval '1 day'

C select count(*) from {{ ref('events') }} where updated_at > current_timestamp + interval '1 day'

D select case when min(updated_at) <= current_timestamp + interval '1 day' then 1 else 0 end as freshness_test from {{ ref('events') }}

💡 Hint

The test should return 0 if data is fresh, 1 if stale.

Option A returns 0 if the latest update is within 24 hours, indicating fresh data, else 1 for stale data. This matches the test goal.