Generic tests with parameters in dbt - Deep Dive

Overview - Generic tests with parameters

What is it?

Generic tests with parameters in dbt are reusable checks that you can apply to your data models to ensure data quality. Instead of writing a new test for every table or column, you create one test that accepts parameters to customize its behavior. This makes testing efficient and consistent across your data warehouse.

Why it matters

Without generic tests, you would have to write many repetitive tests for each dataset, which wastes time and increases the chance of errors. Generic tests with parameters save effort and help catch data issues early, improving trust in your data. This leads to better decisions and less time fixing data problems.

Where it fits

Before learning generic tests with parameters, you should understand basic dbt models and simple tests. After mastering this, you can explore advanced testing strategies, custom test macros, and automated data quality monitoring.

Mental Model

Core Idea

A generic test with parameters is like a flexible template that runs the same check on different data by changing inputs.

Think of it like...

Imagine a cookie cutter that can make cookies of different shapes by swapping the cutter shape. The cutter is the generic test, and the shape you choose is the parameter.

┌─────────────────────────────┐
│      Generic Test Macro      │
│  (Reusable test logic code)  │
└─────────────┬───────────────┘
              │
      ┌───────┴────────┐
      │                │
┌─────▼─────┐    ┌─────▼─────┐
│ Parameter │    │ Parameter │
│ (e.g.,    │    │ (e.g.,    │
│ column)   │    │ condition)│
└───────────┘    └───────────┘
              │
      ┌───────▼────────┐
      │  Test runs on   │
      │  specific data  │
      └─────────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding dbt tests basics

Concept: Learn what dbt tests are and how they check data quality.

dbt tests are SQL queries that check if data meets certain conditions, like no nulls or unique values. You add tests to your models by specifying columns or conditions. When you run dbt, it executes these tests and reports failures.

Result

You get a report showing which tests passed or failed on your data.

Knowing how basic tests work is essential before making them generic and reusable.

2

Foundation

What are generic tests in dbt?

Concept: Generic tests are reusable test macros that accept parameters to customize their checks.

Instead of writing a test for each column, you write one test macro that takes parameters like column name or condition. This macro runs the same logic but adapts based on input, saving time and ensuring consistency.

Result

You can apply one test macro to many columns or tables by passing different parameters.

Understanding generic tests helps you avoid repetitive code and makes your testing scalable.

3

Intermediate

Writing a generic test macro

🤔Before reading on: do you think a generic test macro is just a SQL query or a reusable function? Commit to your answer.

Concept: Learn how to write a test macro that accepts parameters and returns a SQL query.

In dbt, you write test macros using Jinja templating. The macro takes parameters like 'column_name' and uses them inside SQL to check conditions. For example, a macro to check if a column has no nulls uses the parameter to specify which column to check.

Result

A reusable macro that can be called with different parameters to test various columns.

Knowing how to write parameterized macros unlocks powerful, flexible testing in dbt.

4

Intermediate

Using parameters to customize tests

🤔Before reading on: do you think parameters can only be column names or can they include conditions too? Commit to your answer.

Concept: Parameters can be more than column names; they can include conditions or thresholds to customize test logic.

You can pass parameters like 'column_name', 'threshold', or 'condition' to your test macro. For example, a test can check if values in a column are above a threshold or match a pattern. This makes tests adaptable to different data rules.

Result

Tests that adapt their logic based on parameters, allowing complex checks with one macro.

Understanding parameter flexibility lets you create smarter, more useful tests.

5

Advanced

Calling generic tests in schema.yml

🤔Before reading on: do you think you call generic tests differently than built-in tests in schema.yml? Commit to your answer.

Concept: Learn how to call your generic test macros with parameters inside dbt's schema.yml file.

In schema.yml, you define tests under columns using the 'tests' key. To use a generic test, you specify the test name and pass parameters as key-value pairs. dbt then runs your macro with those parameters on the specified column.

Result

Your generic tests run automatically during dbt test runs with the parameters you set in schema.yml.

Knowing how to connect macros with schema.yml enables seamless integration of custom tests.

6

Advanced

Combining multiple parameters in tests

🤔Before reading on: can you combine multiple parameters in one generic test macro? Commit to your answer.

Concept: Generic tests can accept multiple parameters to perform complex validations.

You can design your macro to accept several parameters, like 'column_name', 'min_value', and 'max_value'. The macro then checks if the column values fall within the range. This lets you write one test that covers many scenarios.

Result

A single test macro that handles multiple validation rules based on parameters.

Understanding multi-parameter macros increases your ability to write versatile tests.

7

Expert

Advanced parameter handling and error reporting

🤔Before reading on: do you think generic tests can handle missing or invalid parameters gracefully? Commit to your answer.

Concept: Learn how to add parameter validation and custom error messages inside generic test macros.

Inside your macro, you can check if required parameters are provided and raise clear errors if not. You can also customize the failure message to include parameter values, making debugging easier. This improves test reliability and user experience.

Result

Robust generic tests that fail clearly and help users fix issues quickly.

Knowing how to handle parameters and errors professionally makes your tests production-ready and user-friendly.

Under the Hood

dbt compiles your test macros by replacing parameters with actual values in SQL queries. When you run dbt test, it executes these queries against your data warehouse. The test passes if the query returns no rows (meaning no errors found). Parameters guide the SQL logic dynamically, enabling reuse.

Why designed this way?

This design allows maximum flexibility and reuse without duplicating code. Instead of hardcoding tests for each case, parameters let one macro serve many purposes. It balances simplicity for users with power for complex scenarios.

┌───────────────┐
│ Test Macro    │
│ (with params) │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Jinja renders │
│ SQL with      │
│ parameters    │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ SQL executed  │
│ in warehouse  │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Test result   │
│ pass/fail     │
└───────────────┘

Myth Busters - 3 Common Misconceptions

Quick: Do you think generic tests automatically check all columns without specifying parameters? Commit to yes or no.

Common Belief: Generic tests run on all columns automatically without needing parameters.

Reality: Generic tests require parameters to specify which columns or conditions to check; they do not run blindly on all data.

Why it matters: Assuming tests run everywhere can cause missed errors or unexpected failures if parameters are not set correctly.

Quick: Do you think parameters in generic tests can be any data type without validation? Commit to yes or no.

Common Belief: You can pass any value as a parameter to a generic test without checking its type or presence.

Reality: Parameters must be validated inside the macro to avoid errors or incorrect test results.

Why it matters: Ignoring parameter validation can cause confusing errors or false test passes, reducing trust in your tests.

Quick: Do you think generic tests are slower than writing individual tests for each column? Commit to yes or no.

Common Belief: Generic tests are slower because they add complexity and parameter handling.

Reality: Generic tests compile to simple SQL queries just like individual tests, so performance is similar or better due to less duplicated code.

Why it matters: Believing generic tests are slow may discourage their use, missing out on their efficiency and maintainability benefits.

Expert Zone

1

Generic tests can be combined with dbt's 'test severity' feature to prioritize critical data checks.

2

Parameter defaults can be set inside macros to simplify calls and reduce schema.yml clutter.

3

You can use Jinja control flow inside test macros to create conditional logic based on parameters, enabling very flexible tests.

When NOT to use

Avoid generic tests when the validation logic is unique and complex for a single case; in such cases, write a custom test macro or SQL test. Also, if parameters become too numerous or complicated, it may be better to split tests for clarity.

Production Patterns

In production, teams use generic tests for common checks like uniqueness, nulls, or value ranges across many tables. They store these macros in shared packages for reuse. Parameters are carefully documented and standardized to ensure consistent data quality enforcement.

Connections

Function parameters in programming

Generic tests in dbt use parameters like functions accept arguments to customize behavior.

Understanding how functions take inputs to produce different outputs helps grasp how generic tests adapt to different data.

Templates in document generation

Generic tests are like templates that fill in blanks with parameters to produce customized results.

Knowing how templates work in other fields clarifies how generic tests reuse logic efficiently.

Quality control checklists in manufacturing

Generic tests act like standardized checklists applied to different products with slight adjustments.

Seeing generic tests as adaptable checklists helps appreciate their role in consistent quality assurance.

Common Pitfalls

#1 Not passing required parameters to the generic test macro.

Wrong approach:

test:
- name: my_generic_test
args: {}

Correct approach:

tests:
- name: my_generic_test
args:
column_name: user_id

Root cause:

Misunderstanding that generic tests need explicit parameters to know what to check.

#2 Hardcoding column names inside the generic test macro instead of using parameters.

Wrong approach:

{% macro my_generic_test() %}
SELECT * FROM {{ model }} WHERE user_id IS NULL
{% endmacro %}

Correct approach:

{% macro my_generic_test(column_name) %}
SELECT * FROM {{ model }} WHERE {{ column_name }} IS NULL
{% endmacro %}

Root cause:

Not leveraging parameters reduces reusability and forces rewriting tests for each column.

#3 Ignoring parameter validation leading to runtime errors.

Wrong approach:

{% macro my_generic_test(column_name) %}
SELECT * FROM {{ model }} WHERE {{ column_name }} IS NULL
{% endmacro %}

# Called without column_name parameter

Correct approach:

{% macro my_generic_test(column_name) %}
{% if not column_name %}
{{ exceptions.raise_compiler_error('column_name parameter is required') }}
{% endif %}
SELECT * FROM {{ model }} WHERE {{ column_name }} IS NULL
{% endmacro %}

Root cause:

Assuming parameters are always provided without checks causes confusing failures.

Key Takeaways

Generic tests with parameters let you write one reusable test that adapts to different data by changing inputs.

They save time, reduce errors, and make your data quality checks consistent and scalable.

Parameters can be column names, conditions, or thresholds, making tests flexible for many scenarios.

Proper parameter validation and error handling inside macros make tests robust and user-friendly.

Using generic tests effectively requires understanding dbt macros, schema.yml syntax, and Jinja templating.