Why testing ensures data quality in dbt - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

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

dbt

version: 2
models:
  - name: customers
    columns:
      - name: email
        tests:
          - [1]

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

Anot_null

Brelationships

Caccepted_values

Dunique

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing 'unique' which checks for duplicates, not nulls.

Using 'accepted_values' which checks for allowed values, not nulls.

The not_null test ensures that the column has no missing values, which is a basic data quality check.

2fill in blank

medium

Complete the code to add a test that checks if values in the 'status' column are within allowed values.

dbt

version: 2
models:
  - name: orders
    columns:
      - name: status
        tests:
          - accepted_values:
              values: [1]

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

A['yes', 'no']

B['open', 'closed']

C['active', 'inactive']

D['pending', 'shipped', 'delivered']

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing values unrelated to order status.

Using an empty list or wrong format.

The accepted_values test checks that the 'status' column only contains the specified list of allowed values.

3fill in blank

hard

Fix the error in the test definition that checks for unique values in the 'user_id' column.

dbt

version: 2
models:
  - name: sessions
    columns:
      - name: user_id
        tests:
          - [1]

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

Anot_null

Bunique

Caccepted_values

Drelationships

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'not_null' which only checks for missing values.

Using 'relationships' which checks foreign key constraints.

The unique test ensures that each 'user_id' appears only once, which is important for identifying distinct users.

4fill in blank

hard

Fill both blanks to create a test that checks if 'order_id' in 'orders' model exists in 'order_details' model.

dbt

version: 2
models:
  - name: orders
    columns:
      - name: order_id
        tests:
          - [1]:
              to: [2]

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

Arelationships

Borders.order_id

Cref('order_details.order_id')

Dunique

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'unique' instead of 'relationships'.

Referencing the wrong table or column in 'to'.

The relationships test checks foreign key constraints. Here, it verifies that 'order_id' in 'orders' exists in 'order_details'.

5fill in blank

hard

Fill all three blanks to define a custom test that checks if the 'age' column values are greater than 18.

dbt

version: 2
models:
  - name: users
    tests:
      - name: age_check
        test: "select * from [1] where [2] [3] 18"

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

Aref('users')

Bage

C>

D<=

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong table name.

Using '>' instead of '<=' for the condition.

Referencing the wrong column.

This custom test selects rows where age <= 18 from the users model. The test passes only if zero rows are returned, ensuring all ages are greater than 18.

Practice

1. Why is testing important in dbt for data quality?

easy

A. It automatically checks if data meets expected rules.

B. It speeds up data loading into the warehouse.

C. It creates visual reports for data trends.

D. It deletes old data to save space.

Solution

1. Step 1: Understand the purpose of testing in dbt

   Testing in dbt is designed to check if data follows certain rules or expectations automatically.

2. Step 2: Compare options with testing goals

   Only It automatically checks if data meets expected rules. describes automatic checking of data correctness, which matches testing's role.

3. Final Answer:

   It automatically checks if data meets expected rules. -> Option A

4. Quick Check:

   Testing = automatic data checks [OK]

Hint: Testing means automatic checks for data correctness [OK]

Common Mistakes:

• Confusing testing with data loading speed
• Thinking testing creates visual reports
• Assuming testing deletes data

2. Which of the following is the correct syntax to add a test in a dbt model's YAML file?

easy

A. tests: - unique: column_name

B. test: unique column_name

C. tests: unique(column_name)

D. test: - unique: column_name

Solution

1. Step 1: Recall dbt YAML test syntax

   In dbt, tests are added under the 'tests' key as a list with test name and column.

2. Step 2: Match syntax with options

   tests: - unique: column_name correctly shows 'tests:' followed by '- unique: column_name' which is valid YAML for dbt tests.

3. Final Answer:

   tests: - unique: column_name -> Option A

4. Quick Check:

   YAML tests list = tests: - unique: column_name [OK]

Hint: Tests in YAML use 'tests:' with dash list [OK]

Common Mistakes:

• Using 'test' instead of 'tests'
• Missing dash '-' before test name
• Incorrect parentheses usage

3. Given this dbt test result output:

{"failures": 3, "total_tests": 5}

What does this mean about the data quality?

medium

A. No tests were run on the data.

B. All tests passed, data is perfect.

C. 5 tests failed, data is unusable.

D. 3 tests failed, indicating some data issues.

Solution

1. Step 1: Interpret test result fields

   'failures' shows how many tests failed; 'total_tests' is total run.

2. Step 2: Analyze given numbers

   3 failures out of 5 means some tests failed, so data has issues but not all tests failed.

3. Final Answer:

   3 tests failed, indicating some data issues. -> Option D

4. Quick Check:

   failures = 3 means some errors [OK]

Hint: Failures number shows how many tests found problems [OK]

Common Mistakes:

• Assuming failures means all tests failed
• Thinking zero failures means errors
• Ignoring total_tests count

4. You wrote this test in your dbt model YAML:

tests:
  - not_null: id
  - unique: id

But dbt throws an error when running tests. What is the likely problem?

medium

A. The tests list is missing a dash before 'not_null'.

B. The tests should be under 'columns', not directly under 'tests'.

C. The test names 'not_null' and 'unique' are invalid.

D. The YAML file must be named 'schema.yml' to run tests.

Solution

1. Step 1: Recall correct YAML structure for dbt tests

   Tests on columns must be nested under 'columns:' key, not directly under 'tests:'.

2. Step 2: Identify error cause

   Placing tests directly under 'tests:' causes syntax error; they belong under 'columns:' with column name and tests list.

3. Final Answer:

   The tests should be under 'columns', not directly under 'tests'. -> Option B

4. Quick Check:

   Tests belong under columns key [OK]

Hint: Tests on columns go under 'columns:' in YAML [OK]

Common Mistakes:

• Putting tests directly under 'tests:' without 'columns:'
• Using wrong test names
• Wrong YAML file naming

5. You want to ensure no duplicate emails exist in your users table using dbt tests. Which YAML snippet correctly applies this test?

hard

A. columns:
- email:
tests:
- unique

B. tests:
- unique: email

C. columns:
- name: email
tests:
- unique

D. columns:
- name: email
test: unique

Solution

1. Step 1: Recall correct YAML format for column tests

   Tests are listed under 'columns:', each with 'name' and 'tests' list.

2. Step 2: Match options with correct syntax

   columns: - name: email tests: - unique correctly uses 'columns:', '- name: email', and 'tests:' with '- unique'.

3. Final Answer:

   columns: - name: email tests: - unique -> Option C

4. Quick Check:

   Correct YAML structure = columns: - name: email tests: - unique [OK]

Hint: Use 'columns:' with 'name' and 'tests:' list [OK]

Common Mistakes:

• Using 'test' instead of 'tests'
• Missing 'name:' key for column
• Placing tests outside 'columns:'