Testing model outputs in dbt - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

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

dbt

select count(*) from [1] where id is null

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

Atable('my_table')

Bsource('my_source')

Cref('my_model')

Dmodel('my_model')

Attempts:

3 left

💡 Hint

Common Mistakes

Using source() instead of ref() to reference a model.

Using table() which is not a dbt function.

Using model() which is not a dbt function.

In dbt, to refer to a model in SQL, we use the ref() function. So ref('my_model') correctly references the model.

2fill in blank

medium

Complete the code to write a test that checks if the 'email' column contains the '@' character.

dbt

select count(*) from [1] where email not like '%@%'

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

Amodel('users')

Btable('users')

Csource('users')

Dref('users')

Attempts:

3 left

💡 Hint

Common Mistakes

Using source() instead of ref() for models.

Using table() which is not a dbt function.

Using model() which is not a dbt function.

To test a model in dbt, use ref() to reference the model named 'users'.

3fill in blank

hard

Fix the error in the test code that checks for duplicate 'user_id' values.

dbt

select user_id, count(*) from [1] group by user_id having count(*) > 1

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

Aref('user_data')

Bmodel('user_data')

Ctable('user_data')

Dsource('user_data')

Attempts:

3 left

💡 Hint

Common Mistakes

Using source() instead of ref() for models.

Using table() or model() which are not valid dbt functions.

Use ref() to correctly reference the dbt model 'user_data' in the test query.

4fill in blank

hard

Fill both blanks to create a test that checks if the 'status' column only contains 'active' or 'inactive'.

dbt

select count(*) from [1] where status not in ([2])

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

Aref('customer_status')

B'active', 'inactive'

Csource('customer_status')

D'pending', 'closed'

Attempts:

3 left

💡 Hint

Common Mistakes

Using source() instead of ref() for models.

Using wrong allowed values in the IN clause.

Use ref() to reference the model and specify the allowed values 'active' and 'inactive' in the IN clause.

5fill in blank

hard

Complete the code to write a test that checks if the 'order_date' column has any future dates.

dbt

select count(*) from [1] where order_date >  current_date 

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

Aref('orders')

Dsource('orders')

Attempts:

3 left

💡 Hint

Common Mistakes

Using source() instead of ref() for models.

Adding extra operators after current_date.

Use ref() to reference the model. No extra operator is needed after current_date, so blanks 2 and 3 are empty strings.