Generic tests with parameters in dbt - Step-by-Step Execution

Concept Flow - Generic tests with parameters

Define generic test with parameters

↓

Call test with specific parameters

↓

dbt runs test SQL with parameters

↓

Test returns pass/fail results

↓

Review test output in dbt logs or UI

This flow shows how a generic test is defined with parameters, called with specific values, executed by dbt, and returns results.

Execution Sample

dbt

version: 2
models:
  - name: orders
    tests:
      - unique:
          column_name: order_id
      - generic_test:
          param1: value1
          param2: value2

This YAML snippet shows a model with a unique test and a generic test called with parameters.

Execution Table

Step	Action	Parameter Values	SQL Generated	Test Result
1	Define generic test with parameters	param1, param2	N/A	N/A
2	Call generic test on model 'orders'	param1=value1, param2=value2	N/A	N/A
3	dbt compiles test SQL using parameters	param1=value1, param2=value2	SELECT * FROM orders WHERE {{param1}} = 'value1' AND {{param2}} = 'value2'	N/A
4	dbt runs test SQL	param1=value1, param2=value2	SELECT * FROM orders WHERE param1 = 'value1' AND param2 = 'value2'	Pass (no rows returned)
5	Test completes	param1=value1, param2=value2	N/A	Pass

💡 Test ends after SQL runs and returns no failing rows, indicating pass.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
param1	undefined	value1	value1	value1	value1
param2	undefined	value2	value2	value2	value2
SQL	undefined	undefined	SELECT * FROM orders WHERE {{param1}} = 'value1' AND {{param2}} = 'value2'	SELECT * FROM orders WHERE param1 = 'value1' AND param2 = 'value2'	Executed
Test Result	undefined	undefined	undefined	Pass (no rows)	Pass

Key Moments - 3 Insights

How does dbt replace parameters in the generic test SQL?

dbt uses the parameter values provided when calling the test to replace placeholders in the SQL before running it, as shown in execution_table step 3.

What does it mean if the test SQL returns no rows?

Returning no rows means the test passed because no data violated the test condition, as shown in execution_table step 4.

Can you reuse the same generic test with different parameters?

Yes, you can call the generic test multiple times with different parameters to test different conditions or columns.

Visual Quiz - 3 Questions

Look at the execution table, what is the SQL generated at step 3?

A SELECT * FROM orders WHERE param1 = 'value1' AND param2 = 'value2'

B SELECT * FROM orders WHERE {{param1}} = 'value1' AND {{param2}} = 'value2'

C SELECT * FROM orders WHERE param1 = param2

D No SQL generated yet

💡 Hint

Check the 'SQL Generated' column at step 3 in the execution_table.

At which step does the test result become 'Pass'?

A Step 4

B Step 2

C Step 3

D Step 1

💡 Hint

Look at the 'Test Result' column in the execution_table.

If param1 was changed to 'customer_id', how would the SQL at step 4 change?

A It would cause an error

B It would remain the same

C It would use customer_id = 'value1' instead of value1

D param2 would be removed

💡 Hint

Refer to variable_tracker for param1 values and how they affect SQL.

Concept Snapshot

Generic tests in dbt allow you to write one test SQL with parameters.
You call the test with specific parameter values in your model YAML.
dbt replaces parameters in the test SQL before running.
If the test SQL returns no rows, the test passes.
You can reuse generic tests with different parameters for flexibility.

Full Transcript

Generic tests with parameters in dbt let you write flexible tests that accept inputs. You define a generic test with placeholders for parameters. When you call the test in your model YAML, you provide actual values for these parameters. dbt compiles the test SQL by replacing placeholders with the provided values. Then dbt runs the SQL against your data. If no rows are returned, the test passes. This approach lets you reuse one test for many cases by changing parameters. The execution table shows each step from defining, calling, compiling, running, to passing the test. The variable tracker shows how parameters and SQL change over time. Key moments clarify how parameter replacement works and what passing means. The quiz checks your understanding of SQL generation, test results, and parameter effects. This method saves time and keeps tests consistent across your project.

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