dbt-expectations for data quality - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to add a dbt-expectation that checks if the column 'user_id' has no null values.

dbt

expect_column_values_to_not_be_null('[1]')

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

Aemail

Border_id

Cuser_id

Dcreated_at

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing a column unrelated to user identification.

Using a column that might have nulls allowed.

This expectation checks that the 'user_id' column does not contain any null values, ensuring data completeness for user identifiers.

2fill in blank

medium

Complete the code to expect that the 'order_amount' column values are greater than zero.

dbt

expect_column_values_to_be_between('[1]', min_value=0, strict_min=True)

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

Aorder_amount

Bdiscount

Ccustomer_id

Dorder_date

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting a date or ID column instead of a numeric amount.

Not setting strict_min=True to exclude zero.

This expectation ensures that all values in the 'order_amount' column are strictly greater than zero, which is important for valid sales data.

3fill in blank

hard

Fix the error in the dbt-expectation code to check that 'email' column values match a simple email pattern.

dbt

expect_column_values_to_match_regex('[1]', regex=r'^[\w\.-]+@[\w\.-]+\.\w+$')

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

Aphone_number

Bemail

Cuser_id

Daddress

Attempts:

3 left

💡 Hint

Common Mistakes

Using a column unrelated to emails.

Not matching the regex to the correct column.

The expectation uses a regex to check that the 'email' column values look like valid email addresses. The correct column to check is 'email'.

4fill in blank

hard

Fill both blanks to create a dbt-expectation that checks if 'signup_date' values are between '2020-01-01' and '2023-12-31'.

dbt

expect_column_values_to_be_between('[1]', min_value='[2]', max_value='2023-12-31')

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

Asignup_date

B2020-01-01

Corder_date

D2021-01-01

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong column name.

Setting an incorrect minimum date.

This expectation ensures that the 'signup_date' column values fall within the specified date range from January 1, 2020 to December 31, 2023.

5fill in blank

hard

Fill all three blanks to create a dictionary that maps each column to an expectation checking values are not null and unique.

dbt

expectations = { '[1]': ['expect_column_values_to_not_be_null', 'expect_column_values_to_be_unique'], '[2]': ['expect_column_values_to_not_be_null', 'expect_column_values_to_be_unique'], '[3]': ['expect_column_values_to_not_be_null', 'expect_column_values_to_be_unique'] }

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

Auser_id

Bemail

Corder_id

Dsignup_date

Attempts:

3 left

💡 Hint

Common Mistakes

Including date columns which may not be unique.

Missing any of the key identifier columns.

This dictionary sets expectations for 'user_id', 'email', and 'order_id' columns to ensure their values are not null and unique, which is important for identifiers.

Practice

1. What is the main purpose of dbt-expectations in data projects?

easy

A. To create visual dashboards for data

B. To load data into the database

C. To write tests that check data quality in your models

D. To write SQL queries faster

Solution

1. Step 1: Understand dbt-expectations role

   dbt-expectations is a package that helps add tests to check data quality.

2. Step 2: Identify main function

   Its main function is to write simple tests like checking for nulls or uniqueness in data models.

3. Final Answer:

   To write tests that check data quality in your models -> Option C

4. Quick Check:

   dbt-expectations = data quality tests [OK]

Hint: Remember: dbt-expectations is for testing data quality [OK]

Common Mistakes:

• Confusing dbt-expectations with visualization tools
• Thinking it loads or transforms data
• Assuming it speeds up SQL writing

2. Which of the following is the correct way to add a null check test using dbt-expectations in a schema.yml file?

easy

A. tests: - expect_column_values_to_be_null

B. tests: - dbt_expectations.expect_column_values_to_not_be_null

C. tests: - dbt-expectations.expect_column_values_to_not_be_null

D. tests: - expect_column_values_to_not_be_null

Solution

1. Step 1: Recall dbt-expectations test syntax

   In schema.yml, tests are listed by their function name without the package prefix.

2. Step 2: Identify correct test name

   The correct test for checking no nulls is expect_column_values_to_not_be_null.

3. Final Answer:

   tests: - expect_column_values_to_not_be_null -> Option D

4. Quick Check:

   Test names exclude package prefix [OK]

Hint: Use test function names without package prefix in schema.yml [OK]

Common Mistakes:

• Including package name in test list
• Using wrong test name like 'expect_column_values_to_be_null'
• Using hyphens instead of underscores

3. Given this snippet in a schema.yml file:

tests:
  - expect_column_values_to_be_between:
      min_value: 10
      max_value: 100

What does this test check for the column?

medium

A. Values are between 10 and 100 inclusive

B. Values are unique

C. Values are not null

D. All values are exactly 10 or 100

Solution

1. Step 1: Understand the test name

   The test expect_column_values_to_be_between checks if values fall within a range.

2. Step 2: Interpret parameters

   Parameters min_value: 10 and max_value: 100 mean values must be between 10 and 100, including these limits.

3. Final Answer:

   Values are between 10 and 100 inclusive -> Option A

4. Quick Check:

   Between test checks range limits [OK]

Hint: Between test checks inclusive range by default [OK]

Common Mistakes:

• Thinking values must be exactly min or max
• Confusing with null or uniqueness tests
• Assuming exclusive range instead of inclusive

4. You wrote this test in your schema.yml:

tests:
  - expect_column_values_to_be_unique_or_nulls

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

medium

A. The test name is incorrect; it should be expect_column_values_to_be_unique_or_nulls

B. The test name is incorrect; it should be expect_column_values_to_be_unique_or_null

C. The test requires parameters min_value and max_value

D. The test cannot be used in schema.yml files

Solution

1. Step 1: Check test name spelling

   The correct test name is expect_column_values_to_be_unique_or_null without the trailing 's'.

2. Step 2: Understand test usage

   This test does not require parameters and is valid in schema.yml files.

3. Final Answer:

   The test name is incorrect; it should be expect_column_values_to_be_unique_or_null -> Option B

4. Quick Check:

   Exact test names matter for dbt-expectations [OK]

Hint: Check exact test function names for typos [OK]

Common Mistakes:

• Adding extra characters to test names
• Assuming parameters are needed for all tests
• Thinking some tests can't be in schema.yml

5. You want to ensure a column email in your users model has no nulls and all values match a simple email pattern using dbt-expectations. Which test setup in schema.yml is correct?

hard

A. tests:
- expect_column_values_to_not_be_null
- expect_column_values_to_match_regex:
regex: '^[\w.-]+@[\w.-]+\.\w+$'

B. tests:
- expect_column_values_to_be_null
- expect_column_values_to_match_regex:
regex: '^[\w.-]+@[\w.-]+\.\w+$'

C. tests:
- expect_column_values_to_not_be_null
- expect_column_values_to_be_unique

D. tests:
- expect_column_values_to_not_be_null
- expect_column_values_to_match_regex:
regex: '[e-mail]'

Solution

1. Step 1: Identify null check test

   Use expect_column_values_to_not_be_null to ensure no null emails.

2. Step 2: Identify regex pattern test

   Use expect_column_values_to_match_regex with a regex matching email format.

3. Step 3: Evaluate options

   tests: - expect_column_values_to_not_be_null - expect_column_values_to_match_regex: regex: '^[\w.-]+@[\w.-]+\.\w+$' correctly combines both tests with a valid regex pattern.

4. Final Answer:

   tests: - expect_column_values_to_not_be_null - expect_column_values_to_match_regex: regex: '^[\w.-]+@[\w.-]+\.\w+$' -> Option A

5. Quick Check:

   Null check + regex pattern = correct test setup [OK]

Hint: Combine null check and regex for pattern validation [OK]

Common Mistakes:

• Using expect_column_values_to_be_null instead of not null
• Using a fixed string instead of regex pattern
• Omitting the regex test for pattern check