Custom singular tests in dbt - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to define a custom singular test that checks if a column has no null values.

dbt

select count(*) from [1] where {{column_name}} is null having count(*) > 0

Drag options to blanks, or click blank then click option'

Athis

Bref('my_model')

Csource('my_source', 'my_table')

Dconfig

Attempts:

3 left

💡 Hint

Common Mistakes

Using ref() instead of this in singular tests

Using source() which is for sources, not models

Using config which is unrelated here

In dbt, this refers to the current model or table being tested, which is used in singular tests.

2fill in blank

medium

Complete the code to define the test's SQL file with the correct Jinja syntax for a singular test.

dbt

select count(*) from [1] where {{column_name}} is null having count(*) > 0

Drag options to blanks, or click blank then click option'

Asource('my_source', 'my_table')

Bref('my_model')

Cconfig

Dthis

Attempts:

3 left

💡 Hint

Common Mistakes

Using ref() instead of this

Using source() which is for sources

Using config which is unrelated

The this variable is used in singular tests to refer to the current model or table.

3fill in blank

hard

Fix the error in the singular test SQL by completing the missing Jinja variable to refer to the column name.

dbt

select count(*) from {{this}} where [1] is null having count(*) > 0

Drag options to blanks, or click blank then click option'

Athis

Bref

Ccolumn_name

Dconfig

Attempts:

3 left

💡 Hint

Common Mistakes

Using this instead of column_name

Using ref or config which are not column variables

The variable column_name is passed to the singular test and used to refer to the column being tested.

4fill in blank

hard

Fill both blanks to create a singular test that checks if the count of nulls in a column is zero.

dbt

select count(*) as num_nulls from [1] where [2] is null having count(*) > 0

Drag options to blanks, or click blank then click option'

Athis

Bcolumn_name

Cref('my_model')

Dsource('my_source', 'my_table')

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping table and column variables

Using ref() or source() instead of this

The table is referenced by this and the column by column_name in singular tests.

5fill in blank

hard

Fill all three blanks to write a singular test that returns zero if no nulls exist in the column.

dbt

select case when count(*) = 0 then 0 else 1 end as test_result from [1] where [2] is null having [3] > 0

Drag options to blanks, or click blank then click option'

Athis

Bcolumn_name

Ccount(*)

Dref('my_model')

Attempts:

3 left

💡 Hint

Common Mistakes

Using ref() instead of this

Using column_name incorrectly

Using wrong aggregate function

The test checks the current model (this) for nulls in column_name and uses count(*) to compare the number of nulls.

Practice

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

easy

A. To automatically generate documentation for your models

B. To write your own SQL query that checks data quality and returns rows only if there are issues

C. To schedule dbt runs at specific times

D. To create new tables from existing data

Solution

1. Step 1: Understand the role of custom singular tests

   Custom singular tests are SQL queries that check data quality by returning rows only when problems exist.

2. Step 2: Compare options with this definition

   Only To write your own SQL query that checks data quality and returns rows only if there are issues describes writing a SQL query that returns rows if there are data issues, matching the purpose of custom singular tests.

3. Final Answer:

   To write your own SQL query that checks data quality and returns rows only if there are issues -> Option B

4. Quick Check:

   Custom singular test = SQL check returning problem rows [OK]

Hint: Custom singular tests return rows only when data has problems [OK]

Common Mistakes:

• Confusing tests with documentation generation
• Thinking tests create tables
• Assuming tests schedule runs

2. Which of the following is the correct way to define a custom singular test in your schema.yml file?

easy

A. tests:
- my_custom_test.sql

B. tests:
- my_custom_test:
sql: my_custom_test.sql

C. tests:
- name: my_custom_test
test: my_custom_test

D. tests:
- my_custom_test

Solution

1. Step 1: Recall the schema.yml syntax for custom singular tests

   Custom singular tests are referenced by their filename (without .sql) in the tests list of schema.yml.

2. Step 2: Match options to this syntax

   tests: - my_custom_test correctly references the test file tests/my_custom_test.sql. Other options use incorrect structure, extra keys, or include .sql.

3. Final Answer:

   tests: - my_custom_test -> Option D

4. Quick Check:

   schema.yml test syntax = - test_filename_without_sql [OK]

Hint: Reference tests by name (no .sql) in tests: list [OK]

Common Mistakes:

• Using 'name' or 'test' keys
• Including .sql extension
• Using map/dict structure

3. Given the following custom singular test SQL in tests/check_positive_values.sql:

SELECT * FROM {{ ref('orders') }} WHERE amount <= 0

What will be the output if all amounts in the orders table are positive?

medium

A. An empty result with zero rows

B. A table with all rows where amount is less than or equal to zero

C. An error because of invalid SQL syntax

D. A count of rows with amount less than or equal to zero

Solution

1. Step 1: Understand the test SQL logic

   The test selects rows where amount is less than or equal to zero.

2. Step 2: Analyze the data condition

   If all amounts are positive, no rows satisfy the condition, so the query returns zero rows.

3. Final Answer:

   An empty result with zero rows -> Option A

4. Quick Check:

   All positive amounts means zero rows returned [OK]

Hint: No matching rows means test passes with empty output [OK]

Common Mistakes:

• Expecting a count instead of rows
• Thinking it returns all rows
• Assuming SQL syntax error

4. You wrote a custom singular test SQL file but when running dbt test, it fails with a syntax error. Which of the following is the most likely cause?

medium

A. The model referenced in {{ ref() }} does not exist

B. The test SQL returns zero rows

C. The SQL file is missing the required SELECT statement

D. The test is not listed in schema.yml

Solution

1. Step 1: Identify causes of SQL syntax errors

   Syntax errors happen when SQL is malformed, such as missing SELECT statements.

2. Step 2: Evaluate options for syntax error causes

   The SQL file is missing the required SELECT statement directly relates to SQL syntax. Other options cause runtime or configuration errors, not syntax errors.

3. Final Answer:

   The SQL file is missing the required SELECT statement -> Option C

4. Quick Check:

   Syntax error = malformed SQL like missing SELECT [OK]

Hint: Syntax errors usually mean SQL is incomplete or malformed [OK]

Common Mistakes:

• Confusing missing test listing with syntax error
• Assuming zero rows cause syntax errors
• Ignoring missing model references

5. You want to create a custom singular test that checks if any user has a NULL email in the users table. Which SQL query should you write in your test file?

hard

A. SELECT * FROM {{ ref('users') }} WHERE email IS NULL

B. SELECT COUNT(*) FROM {{ ref('users') }} WHERE email IS NULL

C. SELECT email FROM {{ ref('users') }} WHERE email IS NOT NULL

D. SELECT * FROM {{ ref('users') }} WHERE email = ''

Solution

1. Step 1: Understand the test goal

   The test should return rows where email is NULL to detect missing emails.

2. Step 2: Choose the SQL that returns rows with NULL emails

   SELECT * FROM {{ ref('users') }} WHERE email IS NULL returns rows only when there are NULL emails (0 rows = pass). COUNT(*) always returns one row, failing even with zero NULLs. IS NOT NULL selects good rows (opposite). = '' checks empty strings, not NULLs.

3. Final Answer:

   SELECT * FROM {{ ref('users') }} WHERE email IS NULL -> Option A

4. Quick Check:

   Return rows with NULL email = SELECT * FROM {{ ref('users') }} WHERE email IS NULL [OK]

Hint: Use SELECT * WHERE column IS NULL to find missing values [OK]

Common Mistakes:

• Using COUNT(*) instead of returning rows
• Checking for empty string instead of NULL
• Selecting non-NULL emails