Generic tests with parameters in dbt - Mini Project: Build & Apply

Generic tests with parameters

📖 Scenario: You are working on a data warehouse project using dbt. You want to ensure data quality by creating generic tests that can be reused with different parameters on various tables and columns.

🎯 Goal: Build a reusable generic test in dbt that accepts parameters for table name, column name, and a threshold value, then apply this test to check if the number of null values in a column exceeds the threshold.

📋 What You'll Learn

Create a generic test SQL file that accepts parameters: model, column_name, and max_nulls

Write a SQL query inside the test that counts nulls in the specified column of the specified model

Fail the test if the count of nulls is greater than max_nulls

Configure the test in schema.yml with parameters for a specific model and column

Run the test and output the result

💡 Why This Matters

🌍 Real World

Data teams use generic tests in dbt to enforce data quality rules consistently across many tables and columns without rewriting SQL for each case.

💼 Career

Knowing how to write and configure generic tests with parameters is essential for data engineers and analysts working with dbt to maintain reliable data pipelines.

1

Create a generic test SQL file

Create a generic test SQL file named tests/not_too_many_nulls.sql with parameters model, column_name, and max_nulls. Write a SQL query that counts the number of nulls in {{ column_name }} of {{ model }}. The query should select the count of nulls as null_count.

dbt

-- Your code here to create tests/not_too_many_nulls.sql with parameters and count nulls

Hint

Use Jinja templating to accept model and column_name parameters. Count rows where the column is null.

2

Add a threshold parameter for max nulls

Modify the generic test SQL file tests/not_too_many_nulls.sql to fail the test if the count of nulls is greater than the parameter max_nulls. Use a having clause or where clause to compare null_count with max_nulls.

dbt

{% raw %}-- Modify tests/not_too_many_nulls.sql to compare null_count with max_nulls
-- Your code here{% endraw %}

Hint

Use having count(*) > {{ max_nulls }} to fail the test when nulls exceed the threshold.

3

Configure the test in schema.yml with parameters

In your schema.yml file, configure the generic test not_too_many_nulls for the model customers and column email. Set the parameter max_nulls to 5. Use the tests key with name and args to pass parameters.

dbt

# Your code here to add the test configuration in schema.yml

Hint

Use the tests list under the column with the test name and parameters.

4

Run the test and display the result

Run the dbt test command to execute your generic test on the customers model. Then, print the test result summary showing if the test passed or failed.

dbt

# Your code here to run dbt test and print the result summary

Hint

Use dbt test --select customers to run tests on the customers model.

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