dbt-expectations for data quality - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of a dbt-expectations test on null values

Given the following dbt-expectations test configuration, what will be the result when run on a table where 5% of the rows have null values in the email column?

tests:
  - dbt_expectations.expect_column_values_to_not_be_null:
      column: email
      mostly: 0.95

A Test passes because 95% of <code>email</code> values are not null

B Test fails because any null value causes failure

C Test passes only if no null values exist

D Test fails because <code>mostly</code> parameter is ignored

💡 Hint

The mostly parameter allows some exceptions up to the given threshold.

The expect_column_values_to_not_be_null test with mostly: 0.95 means the test passes if at least 95% of the values are not null. Since 5% are null, the test passes.

❓ data_output

intermediate

Resulting data from a uniqueness test

You run the following dbt-expectations test on the user_id column:

tests:
  - dbt_expectations.expect_column_values_to_be_unique:
      column: user_id

What is the expected output if the table contains duplicate user_id values?

A Test passes and returns all rows

B Test passes but returns duplicate rows

C Test fails but returns no rows

D Test fails and returns rows with duplicate <code>user_id</code> values

💡 Hint

Uniqueness tests identify duplicates and fail if any exist.

The uniqueness test fails if duplicates exist and returns the rows causing failure.

❓ visualization

advanced

Visualizing distribution with dbt-expectations

You want to check if the age column in your dataset follows a normal distribution using dbt-expectations. Which visualization would best help you confirm this after running the test?

A Pie chart showing percentage of null vs non-null <code>age</code> values

B Histogram of <code>age</code> values with a normal curve overlay

C Scatter plot of <code>age</code> vs <code>user_id</code>

D Bar chart of counts of unique <code>age</code> values

💡 Hint

Normal distribution is best visualized with histograms and density curves.

A histogram with a normal curve overlay shows how closely the data matches a normal distribution.

🧠 Conceptual

advanced

Understanding the use of mostly in dbt-expectations

What is the main purpose of the mostly parameter in dbt-expectations tests?

A To specify the minimum proportion of rows that must meet the expectation for the test to pass

B To set the maximum number of rows to test

C To define the exact number of rows that must fail the test

D To ignore null values in the tested column

💡 Hint

Think about tolerance for exceptions in data quality tests.

The mostly parameter sets a threshold for how many rows must satisfy the condition for the test to pass.

🔧 Debug

expert

Identifying error in a dbt-expectations test configuration

You wrote this test in your dbt model:

tests:
  - dbt_expectations.expect_column_values_to_be_in_set:
      column: status
      value_set: ['active', 'inactive', 'pending']

When running dbt, you get an error. What is the cause?

A The parameter name should be <code>value_set</code> not <code>value_set</code> (typo in option name)

B The correct parameter name is <code>value_set</code> but it should be <code>value_set</code> (case sensitive)

C The correct parameter name is <code>value_set</code> but it should be <code>value_set</code> (wrong parameter name)

D The parameter name should be <code>value_set</code> but it must be <code>value_set</code> as a string, not a list

💡 Hint

Check the exact parameter name required by dbt-expectations for this test.

The correct parameter name is value_set, but the test uses value_set which is invalid and causes an error.

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