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.