Generic tests with parameters in dbt - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a generic test in dbt?

A generic test in dbt is a reusable test that can be applied to different columns or tables by passing parameters. It helps avoid writing the same test logic multiple times.

beginner

How do you pass parameters to a generic test in dbt?

You pass parameters to a generic test by specifying them in the test call using the 'args' syntax or directly in the test configuration in your schema.yml file.

beginner

Why use parameters in generic tests?

Parameters make generic tests flexible and reusable. You can test different columns or conditions without rewriting the test logic each time.

intermediate

Example: What does this dbt test do?

test_name:
  column_name: id
  where: "status = 'active'"

This test runs on the 'id' column but only checks rows where the status is 'active'. The 'where' parameter filters the data before testing.

beginner

What is the benefit of using generic tests with parameters in a data project?

They save time and reduce errors by reusing test logic. They also make tests easier to maintain and adapt as data changes.

What is the main purpose of a generic test in dbt?

A To reuse test logic with different parameters

B To create a new table

C To load data into the warehouse

D To write SQL queries manually

Generic tests allow you to reuse the same test logic by passing different parameters.

How do you specify which column a generic test should run on?

A By editing the test SQL file directly

B By changing the database schema

C By passing the column name as a parameter

D By renaming the model

You pass the column name as a parameter to tell the generic test which column to check.

Which parameter can you use to filter rows before running a generic test?

A order_by

B limit

C group_by

D where

The 'where' parameter filters rows so the test only runs on a subset of data.

What happens if you do not pass parameters to a generic test?

A The test runs with default settings or may fail if required parameters are missing

B The test creates a new model

C The test automatically passes

D The test deletes data

Generic tests need parameters to know what to test; without them, they may use defaults or error out.

Why is it better to use generic tests with parameters instead of writing many specific tests?

A It hides errors

B It reduces code duplication and makes maintenance easier

C It requires more storage space

D It makes the project slower

Using generic tests avoids repeating code and helps keep tests consistent and easier to update.

Explain how generic tests with parameters improve testing in dbt projects.

Think about how passing different inputs to one test saves time.

Describe how you would use the 'where' parameter in a generic test and why it is useful.

Consider testing only active users or recent data.

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