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.

Practice

1. What is the main purpose of unit testing in dbt models?

easy

A. To check data models for correctness and quality

B. To create new data models from scratch

C. To deploy dbt projects to production

D. To write SQL queries faster

Solution

1. Step 1: Understand unit testing in dbt

   Unit testing in dbt is designed to verify that data models behave as expected and contain correct data.

2. Step 2: Identify the main goal

   The main goal is to check correctness and quality of data models, not to create or deploy them.

3. Final Answer:

   To check data models for correctness and quality -> Option A

4. Quick Check:

   Unit testing = correctness and quality check [OK]

Hint: Unit tests check data correctness, not creation or deployment [OK]

Common Mistakes:

• Confusing testing with model creation
• Thinking tests deploy models
• Assuming tests speed up SQL writing

2. Which of the following is the correct syntax to define a unique test on a column in a dbt YAML file?

easy

A. tests:
- unique: column_name

B. tests: unique: column_name

C. tests: - unique: column_name

D. tests:
unique: - column_name

Solution

1. Step 1: Recall YAML test syntax in dbt

   Tests are listed as a YAML list under the 'tests' key, each with a dash and test name.

2. Step 2: Match correct indentation and list format

   tests: - unique: column_name shows 'tests:' followed by a list item '- unique: column_name', which is correct YAML syntax for dbt tests.

3. Final Answer:

   tests: - unique: column_name -> Option A

4. Quick Check:

   YAML lists use dash (-) before items [OK]

Hint: YAML lists need dash (-) before each test [OK]

Common Mistakes:

• Missing dash (-) before test name
• Incorrect indentation
• Placing test name and column incorrectly

3. Given this YAML snippet for a dbt test:

columns:
  - name: user_id
    tests:
      - unique
      - not_null

What will dbt test check for the user_id column?

medium

A. It checks that user_id values are duplicated and null

B. It checks that user_id values are unique and not null

C. It only checks if user_id is unique

D. It only checks if user_id is not null

Solution

1. Step 1: Read the tests listed for user_id

   The YAML shows two tests: 'unique' and 'not_null' for the user_id column.

2. Step 2: Understand what each test does

   'unique' ensures no duplicate values; 'not_null' ensures no missing values.

3. Final Answer:

   It checks that user_id values are unique and not null -> Option B

4. Quick Check:

   unique + not_null = no duplicates, no nulls [OK]

Hint: Multiple tests run together on the same column [OK]

Common Mistakes:

• Thinking tests check opposite conditions
• Assuming only one test runs
• Confusing unique with not_null

4. You wrote this test in your YAML file:

columns:
  - name: order_id
    tests:
      unique
      not_null

But when you run dbt test, it fails with a syntax error. What is the likely cause?

medium

A. Using 'tests' instead of 'test' key

B. Incorrect indentation of 'name' key

C. Missing quotes around test names

D. Missing dash (-) before 'unique' and 'not_null' tests

Solution

1. Step 1: Check YAML list syntax for tests

   Each test under 'tests' must be a list item starting with a dash (-).

2. Step 2: Identify missing dashes

   The snippet shows 'unique' and 'not_null' without dashes, causing YAML syntax error.

3. Final Answer:

   Missing dash (-) before 'unique' and 'not_null' tests -> Option D

4. Quick Check:

   YAML lists need dash (-) before each item [OK]

Hint: Always put dash (-) before each test in YAML list [OK]

Common Mistakes:

• Omitting dash (-) before tests
• Wrong indentation levels
• Confusing 'tests' with 'test' key

5. You want to ensure that the email column in your users model is always unique and never empty. Which YAML test configuration correctly applies these tests and how would you run them?

hard

A. Define in YAML:columns:
- email:
tests:
- unique
- not_nullThen run dbt test

B. Define in YAML:columns:
- name: email
tests:
unique
not_nullThen run dbt run

C. Define in YAML:columns:
- name: email
tests:
- unique
- not_nullThen run dbt test

D. Define in YAML:columns:
- name: email
tests:
- unique
- not_nullThen run dbt run

Solution

1. Step 1: Write correct YAML test syntax

   The tests must be listed as a YAML list with dashes under 'tests' for the 'email' column, as shown in Define in YAML:
   

   columns:
     - name: email
       tests:
         - unique
         - not_null

   
   Then run dbt test.

2. Step 2: Choose the correct dbt command to run tests

   To execute tests, you must run dbt test, not dbt run. So this configuration with dbt test is correct.

3. Final Answer:

   Define tests with dashes and run dbt test -> Option C

4. Quick Check:

   YAML list + dbt test command = correct testing [OK]

Hint: Use dashes for tests and run 'dbt test' to execute [OK]

Common Mistakes:

• Running 'dbt run' instead of 'dbt test'
• Incorrect YAML list syntax
• Misnaming column key