> ## Documentation Index
> Fetch the complete documentation index at: https://docs.paradime.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Custom Tests

> Learn how to extend dbt™'s testing capabilities with custom tests. This guide covers creating singular and generic tests, implementing parameterized tests, and integrating with dbt packages for ad

While dbt comes with built-in tests, custom tests allow you to implement specific data quality checks tailored to your business logic. This guide will help you understand how to create and use custom tests to ensure the quality and reliability of your data transformations.

#### Types of Custom Tests

dbt supports two main types of custom tests:

| Test Type      | Description                                              | Where Defined       | Scope             |
| -------------- | -------------------------------------------------------- | ------------------- | ----------------- |
| Singular Tests | SQL files returning failing records                      | `tests/` directory  | Specific use case |
| Generic Tests  | Reusable test definitions applicable to different models | `macros/` directory | Reusable          |

***

### Singular Tests

Singular tests are SQL queries that should return zero rows when the test passes:

```sql theme={"system"}
-- tests/assert_total_payment_amount_matches_order_amount.sql

SELECT
    order_id,
    order_amount,
    payment_amount,
    ABS(order_amount - payment_amount) as amount_diff
FROM {{ ref('orders') }} o
LEFT JOIN {{ ref('payments') }} p USING (order_id)
WHERE ABS(order_amount - payment_amount) > 0.01
```

This test identifies orders where the payment amount doesn't match the order amount within a small tolerance.

To run singular tests:

```bash theme={"system"}
dbt test --select assert_total_payment_amount_matches_order_amount
```

**Organizing Singular Tests**

For larger projects, you might want to organize singular tests by domain or purpose:

```
tests/
  ├── finance/
  │   ├── assert_total_payment_amount_matches_order_amount.sql
  │   └── assert_refund_amount_less_than_order_amount.sql
  └── marketing/
      ├── assert_campaign_spend_within_budget.sql
      └── assert_conversion_rates_above_threshold.sql
```

***

### Creating Generic Custom Tests

Generic tests are more powerful because they can be applied to different models throughout your project. They are defined as macros with a special syntax:

```sql theme={"system"}
{% test test_name(model, column_name, condition_parameter) %}

    -- The test query should return failing records
    SELECT
        {{ column_name }}
    FROM {{ model }}
    WHERE -- Test condition that should return 0 rows when passing
        {{ column_name }} NOT {{ condition_parameter }}

{% endtest %}
```

**Example: Positive Values Test**

Here's a simple custom test that checks if values in a column are positive:

```sql theme={"system"}
{% test is_positive(model, column_name) %}

    SELECT
        {{ column_name }}
    FROM {{ model }}
    WHERE {{ column_name }} <= 0
    
{% endtest %}
```

**Using Custom Tests in YAML Files**

Once defined, you can reference these custom tests in your schema YAML files just like built-in tests:

```yaml theme={"system"}
models:
  - name: orders
    columns:
      - name: order_amount
        tests:
          - is_positive
```

**Parameterizing Custom Tests**

You can make your custom tests more flexible by adding parameters:

```sql theme={"system"}
{% test value_within_range(model, column_name, min_value, max_value) %}

    SELECT
        {{ column_name }}
    FROM {{ model }}
    WHERE {{ column_name }} < {{ min_value }} OR {{ column_name }} > {{ max_value }}
    
{% endtest %}
```

In your YAML file:

```yaml theme={"system"}
models:
  - name: orders
    columns:
      - name: order_amount
        tests:
          - value_within_range:
              min_value: 0
              max_value: 10000
```

***

### Combining Macros and Tests

You can use macros within your custom tests to create powerful, reusable testing frameworks:

```sql theme={"system"}
{% macro get_valid_status_values() %}
    {% set valid_statuses = ['pending', 'shipped', 'delivered', 'cancelled'] %}
    {{ return(valid_statuses) }}
{% endmacro %}

{% test valid_status(model, column_name) %}

    {% set valid_values = get_valid_status_values() %}
    
    SELECT
        {{ column_name }}
    FROM {{ model }}
    WHERE {{ column_name }} NOT IN (
        {% for value in valid_values %}
            '{{ value }}'{% if not loop.last %},{% endif %}
        {% endfor %}
    )
    
{% endtest %}
```

This approach lets you centralize business rules (like valid status values) and reuse them across tests.

***

### Advanced Test Configurations

You can configure how tests behave using additional properties:

```sql theme={"system"}
{% test custom_complex_test(model, column_name) %}

    {{ config(
        severity = 'warn',
        store_failures = true,
        limit = 100
    ) }}

    -- Test query here
    
{% endtest %}
```

| Configuration       | Description                                     | Example Use Case                                  |
| ------------------- | ----------------------------------------------- | ------------------------------------------------- |
| **severity**        | Can be 'error' (default) or 'warn'              | For tests that shouldn't block production runs    |
| **store\_failures** | When true, stores test failures in a table      | For troubleshooting or monitoring over time       |
| **limit**           | Maximum number of failing records to return     | For large tables where full results aren't needed |
| **where**           | Apply additional filtering to the test query    | For focusing tests on specific data subsets       |
| **enabled**         | Boolean that can conditionally disable the test | For environment-specific test configuration       |

***

### Testing Data Quality with Packages

Popular packages like dbt-expectations extend dbt's testing capabilities with advanced data validation:

```yaml theme={"system"}
packages:
  - package: calogica/dbt_expectations
    version: 0.8.5
```

**Example: Advanced Data Validation**

Using dbt-expectations to implement sophisticated data quality checks:

```yaml theme={"system"}
models:
  - name: customer_orders
    tests:
      - dbt_expectations.expect_table_row_count_to_be_between:
          min_value: 1
          max_value: 1000000
    columns:
      - name: order_amount
        tests:
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
              max_value: 50000
              severity: warn
      - name: customer_email
        tests:
          - dbt_expectations.expect_column_values_to_match_regex:
              regex: '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'
```

**Popular Testing Packages**

| Package              | Purpose                                           | Key Features                                       |
| -------------------- | ------------------------------------------------- | -------------------------------------------------- |
| **dbt-expectations** | Data quality tests inspired by Great Expectations | Advanced schema validation, statistical tests      |
| **dbt-audit-helper** | Compare query results between models              | Model comparison, reconciliation tests             |
| **dbt-utils**        | Common test utilities                             | Equal rowcounts, relationships, cardinality checks |
| **elementary**       | Anomaly detection and data validation             | Historical test comparisons, metrics monitoring    |

***

### Real-World Custom Test Examples

**Date Range Validation**

```sql theme={"system"}
{% test date_between_project_dates(model, column_name) %}
    SELECT
        {{ column_name }}
    FROM {{ model }}
    WHERE {{ column_name }} NOT BETWEEN 
        (SELECT min_valid_date FROM {{ ref('project_date_settings') }})
        AND
        (SELECT max_valid_date FROM {{ ref('project_date_settings') }})
{% endtest %}
```

**Numeric Distribution Test**

```sql theme={"system"}
{% test standard_deviation_within_range(model, column_name, min_stddev, max_stddev) %}
    WITH stats AS (
        SELECT 
            STDDEV({{ column_name }}) AS std_dev
        FROM {{ model }}
        WHERE {{ column_name }} IS NOT NULL
    )
    
    SELECT *
    FROM stats
    WHERE std_dev < {{ min_stddev }} OR std_dev > {{ max_stddev }}
{% endtest %}
```

**Referential Integrity with Exceptions**

```sql theme={"system"}
{% test foreign_key_with_exceptions(model, column_name, to, field, exceptions) %}
    
    {% set exceptions_list = [] %}
    {% for exception in exceptions %}
        {% do exceptions_list.append("'" ~ exception ~ "'") %}
    {% endfor %}
    
    SELECT
        {{ column_name }}
    FROM {{ model }}
    WHERE {{ column_name }} NOT IN (
        SELECT {{ field }}
        FROM {{ to }}
    )
    AND {{ column_name }} NOT IN ({{ exceptions_list | join(', ') }})
    
{% endtest %}
```

***

### Best Practices for Custom Tests

| Best Practice                        | Description                                                                                            |
| ------------------------------------ | ------------------------------------------------------------------------------------------------------ |
| **Test Critical Data First**         | Focus on testing key business metrics and join keys. Identify high-risk areas for data quality issues. |
| **Make Tests Descriptive**           | Name tests clearly to indicate what they verify. Add comments explaining the purpose and expectations. |
| **Balance Coverage and Performance** | Consider the runtime impact of extensive testing. Use selective testing for large tables.              |
| **Group Related Tests**              | Organize tests that verify related business rules. Use consistent naming conventions.                  |
| **Handle Edge Cases**                | Test boundary conditions. Consider null handling and empty tables.                                     |
| **Monitor Test Results**             | Track test failures over time. Establish alerting for critical test failures.                          |

<Info>
  **Pro Tip: Troubleshooting Failed Tests**

  When tests fail, use these strategies to diagnose issues:

  1. Check error messages in the dbt logs
  2. Examine a sample of failing records with `store_failures: true`
  3. Verify test logic by inspecting the compiled SQL in `target/compiled/`
  4. Use `--vars` to test with different parameters
</Info>

By implementing custom tests, you can ensure your transformations meet business requirements and maintain high data quality standards throughout your dbt project.


## Related topics

- [Testing Data Quality](/guides/dbt-fundamentals/getting-started-with-dbt/testing-data-quality.md)
- [Autocompletion](/products/code-ide/user-interface/autocompletion.md)
- [Alerts Configuration and Customization](/integrations/elementary-data/sending-alerts/alerts-configuration-and-customization.md)
- [Run and Test all your dbt™ Models](/products/bolt/creating-schedules/templates/dbt-tm-templates/run-and-test-all-your-dbt-tm-models.md)
- [Test Code Changes On Pull Requests](/products/bolt/creating-schedules/templates/ci-cd-templates/test-code-changes-on-pull-requests.md)