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.