Generic tests with parameters in dbt - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of a generic test with a parameter

Given the following dbt generic test configuration, what will be the output when the test runs on a table with 3 null values in the email column?

dbt

version: 2
models:
  - name: users
    columns:
      - name: email
        tests:
          - not_null:
              severity: warn
              where: "email LIKE '[e-mail]'"

A The test will pass because the where clause filters out null emails.

B The test will fail with 3 null values found in the filtered rows.

C The test will fail with 0 null values because the where clause excludes all rows.

D The test will error due to invalid syntax in the where clause.

💡 Hint

Think about how the where clause filters rows before checking for nulls.

The generic test 'not_null' checks for nulls only in rows matching the where clause. Since 3 null emails match the filter, the test fails with 3 nulls found.

🧠 Conceptual

intermediate

Purpose of parameters in dbt generic tests

What is the main purpose of using parameters in dbt generic tests?

A To customize test behavior like filtering rows or setting thresholds without writing new SQL.

B To automatically generate documentation for tests.

C To define new models dynamically during test execution.

D To schedule tests to run at specific times.

💡 Hint

Parameters help change how tests work without changing the test code itself.

Parameters allow users to adjust test logic, such as filtering rows or setting limits, making tests reusable and flexible.

🔧 Debug

advanced

Identify the error in this generic test with parameters

This dbt generic test is intended to check uniqueness on the user_id column but raises an error. What is the cause?

dbt

version: 2
models:
  - name: users
    columns:
      - name: user_id
        tests:
          - unique:
              where: "user_id != ''"
              threshold: 0

A The 'threshold' parameter is not valid for the 'unique' test and causes an error.

B The 'where' clause syntax is invalid and causes a SQL error.

C The 'unique' test cannot be used on the 'user_id' column.

D The test is missing a required 'severity' parameter.

💡 Hint

Check which parameters are supported by the 'unique' generic test.

The 'unique' test does not support a 'threshold' parameter, so including it causes an error during compilation or runtime.

❓ data_output

advanced

Result of a generic test with a threshold parameter

A generic test is configured to check that the number of nulls in the phone column is below a threshold of 2. The table has 3 null values in phone. What is the test result?

dbt

version: 2
models:
  - name: customers
    columns:
      - name: phone
        tests:
          - not_null:
              threshold: 2

A Test errors because 'threshold' is not a valid parameter for not_null.

B Test passes because threshold is ignored by not_null test.

C Test fails because 3 nulls exceed the threshold of 2.

D Test passes because threshold means minimum nulls allowed.

💡 Hint

Threshold sets the maximum allowed nulls before failing.

The test fails because the number of nulls (3) is greater than the allowed threshold (2).

🚀 Application

expert

Using multiple parameters in a generic test

You want to create a generic test that checks for duplicates in the order_id column but only for orders placed in 2023 and allow up to 5 duplicates before failing. Which configuration is correct?

A

tests:
  - unique:
      where: "order_date BETWEEN '2023-01-01' AND '2023-12-31'"
      threshold: 0

B

tests:
  - unique:
      where: "order_date >= '2023-01-01' AND order_date < '2024-01-01'"
      severity: error

C

tests:
  - unique:
      threshold: 5
      severity: warn

D

tests:
  - unique:
      where: "order_date >= '2023-01-01' AND order_date < '2024-01-01'"
      threshold: 5

💡 Hint

Combine filtering with threshold to allow some duplicates.

Option D correctly uses 'where' to filter 2023 orders and 'threshold' to allow up to 5 duplicates before failing.

Practice

1. What is the main benefit of using generic tests with parameters in dbt?

easy

A. They replace the need for SQL queries.

B. They automatically fix data errors.

C. They create new tables for each test.

D. They allow reusing test logic with different settings.

Solution

1. Step 1: Understand generic tests

   Generic tests are reusable test templates that can be applied to many columns or tables.

2. Step 2: Role of parameters

   Parameters customize these tests for specific columns or rules without rewriting the test logic.

3. Final Answer:

   They allow reusing test logic with different settings. -> Option D

4. Quick Check:

   Generic tests + parameters = reusable logic [OK]

Hint: Generic tests reuse logic; parameters customize them [OK]

Common Mistakes:

• Thinking generic tests fix data automatically
• Confusing generic tests with table creation
• Believing generic tests replace SQL queries

2. Which of the following is the correct syntax to call a generic test with a parameter in dbt YAML?

easy

A. tests: - unique(column_name='id')

B. tests: - unique: { column_name: 'id' }

C. tests: - unique: id

D. tests: - unique: [id]

Solution

1. Step 1: Review dbt test YAML syntax

   Generic tests with parameters are called using a dictionary format: test_name: {param: value}.

2. Step 2: Match syntax to options

   tests: - unique: { column_name: 'id' } uses the correct dictionary format with the parameter inside curly braces.

3. Final Answer:

   tests: - unique: { column_name: 'id' } -> Option B

4. Quick Check:

   YAML test call = dictionary with parameters [OK]

Hint: Use dictionary syntax for parameters in YAML tests [OK]

Common Mistakes:

• Using function call syntax in YAML
• Passing parameters as plain strings or lists
• Omitting curly braces for parameters

3. Given this dbt test YAML snippet:

tests:
  - not_null:
      column_name: 'email'
  - accepted_values:
      column_name: 'status'
      values: ['active', 'inactive']

What does the accepted_values test check?

medium

A. It checks that 'status' column has only 'active' or 'inactive' values.

B. It checks that 'email' column is not null.

C. It checks that 'status' column has no null values.

D. It checks that 'email' column contains only 'active' or 'inactive'.

Solution

1. Step 1: Identify the test and parameters

   The accepted_values test is applied to the 'status' column with allowed values ['active', 'inactive'].

2. Step 2: Understand test purpose

   This test ensures the column only contains the specified allowed values.

3. Final Answer:

   It checks that 'status' column has only 'active' or 'inactive' values. -> Option A

4. Quick Check:

   accepted_values test = allowed values check [OK]

Hint: accepted_values limits column to listed values [OK]

Common Mistakes:

• Confusing accepted_values with not_null test
• Mixing columns between tests
• Assuming accepted_values checks for nulls

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

tests:
  - unique:
      column: 'user_id'

But the test fails to run. What is the likely error?

medium

A. Parameter name should be 'column_name', not 'column'.

B. The test 'unique' does not accept parameters.

C. The column name must be in double quotes, not single quotes.

D. The test should be under 'columns:', not 'tests:'.

Solution

1. Step 1: Check parameter naming

   Generic tests expect parameter 'column_name' to specify the column, not 'column'.

2. Step 2: Validate test structure

   Using 'column' instead of 'column_name' causes the test to fail because the parameter is unrecognized.

3. Final Answer:

   Parameter name should be 'column_name', not 'column'. -> Option A

4. Quick Check:

   Correct parameter name = column_name [OK]

Hint: Use exact parameter names like 'column_name' [OK]

Common Mistakes:

• Using wrong parameter keys
• Misplacing tests outside 'columns:' block
• Confusing quote types for strings

5. You want to create a generic test that checks if a numeric column's values are within a custom range using parameters min_value and max_value. Which YAML snippet correctly calls this test for column age with range 18 to 65?

hard

A. tests:
- within_range('age', 18, 65)

B. tests:
- within_range:
column: 'age'
min: 18
max: 65

C. tests:
- within_range:
column_name: 'age'
min_value: 18
max_value: 65

D. tests:
- within_range:
column_name: age
min_value: '18'
max_value: '65'

Solution

1. Step 1: Use correct parameter names

   The test expects parameters named 'column_name', 'min_value', and 'max_value'.

2. Step 2: Use correct YAML syntax and types

   tests: - within_range: column_name: 'age' min_value: 18 max_value: 65 uses correct parameter names and numeric values without quotes for numbers.

3. Step 3: Check other options

   Function call syntax is invalid in YAML; incorrect parameter names like 'column', 'min', 'max'; unquoted column name and quoted numbers may cause type issues.

4. Final Answer:

   tests: - within_range: column_name: 'age' min_value: 18 max_value: 65 -> Option C

5. Quick Check:

   Correct params + types = tests: - within_range: column_name: 'age' min_value: 18 max_value: 65 [OK]

Hint: Match parameter names and use correct YAML types [OK]

Common Mistakes:

• Using wrong parameter keys like 'column' or 'min'
• Passing numbers as strings
• Using function call syntax in YAML