Unit testing dbt models - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

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

dbt

version: 2
models:
  - name: my_model
    tests:
      - not_null:
          column: [1]

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

Aid

Bname

Cdate

Damount

Attempts:

3 left

💡 Hint

Common Mistakes

Using a column name that does not exist in the model.

Forgetting to specify the column name under the test.

The not_null test checks that the specified column has no null values. Here, the column is 'id'.

2fill in blank

medium

Complete the code to write a test that ensures the 'email' column contains unique values.

dbt

version: 2
models:
  - name: users
    tests:
      - unique:
          column: [1]

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

Auser_id

Bemail

Ccreated_at

Dstatus

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing a column that can have duplicates, like 'status'.

Not specifying the correct column name.

The unique test ensures that all values in the specified column are unique. Here, it is applied to the 'email' column.

3fill in blank

hard

Fix the error in the test definition to correctly check that the 'order_date' column has no nulls.

dbt

version: 2
models:
  - name: orders
    tests:
      - not_null:
          column: [1]

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

Aorderdate

Bdate_order

Corder_date

Dorder-date

Attempts:

3 left

💡 Hint

Common Mistakes

Using incorrect column names with typos or wrong separators.

Using camelCase or hyphens instead of snake_case.

The column name must exactly match the model's column name. 'order_date' is the correct column name here.

4fill in blank

hard

Fill both blanks to create a test that checks if the 'status' column only contains allowed values.

dbt

version: 2
models:
  - name: transactions
    tests:
      - accepted_values:
          column: [1]
          values: [[2]]

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

Astatus

B'completed', 'pending', 'failed'

C'open', 'closed'

D'yes', 'no'

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong column names.

Providing incorrect or incomplete value lists.

The accepted_values test checks that the column only contains specified values. Here, 'status' must be one of 'completed', 'pending', or 'failed'.

5fill in blank

hard

Fill all three blanks to write a custom test that checks if the 'amount' column values are greater than zero.

dbt

version: 2
models:
  - name: payments
    tests:
      - custom_test:
          sql: "SELECT * FROM [1] WHERE [2] [3] 0"

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

Aref('payments')

Bamount

C>

D<=

Attempts:

3 left

💡 Hint

Common Mistakes

Not using ref() to reference the model.

Using wrong comparison operators.

Using incorrect column names.

This custom test selects rows where 'amount' is less than or equal to zero (the failing rows). The ref('payments') function references the model.