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.