# How to Implement Data Quality Checks in dbt with Custom Tests and Assertions

> Learn to implement robust data quality checks in dbt using custom Jinja macros and assertions. Ensure data accuracy and reliability with effective testing strategies.

- Repository: [DataTalksClub/data-engineering-zoomcamp](https://github.com/DataTalksClub/data-engineering-zoomcamp)
- Tags: how-to-guide
- Published: 2026-05-30

---

**Implement data quality checks in dbt by creating reusable Jinja macros that return SQL queries selecting violating rows, then attach them to models under the `tests:` key in your [`schema.yml`](https://github.com/DataTalksClub/data-engineering-zoomcamp/blob/main/schema.yml) files.**

DataTalksClub's **data-engineering-zoomcamp** repository demonstrates production-grade patterns for enforcing data quality in dbt pipelines. While built-in generic tests handle basic constraints like `not_null` and `unique`, complex business rules require custom test macros that encapsulate domain-specific logic. This guide walks through the architecture and implementation of custom data quality assertions using the NYC taxi data project as a reference.

## Built-in Generic Tests vs. Custom Tests

dbt ships with three built-in generic tests—`not_null`, `unique`, and `accepted_values`—that attach directly to columns in [`schema.yml`](https://github.com/DataTalksClub/data-engineering-zoomcamp/blob/main/schema.yml) files. In the Data Engineering ZoomCamp project, the staging layer for NYC taxi data uses these to guard critical fields like `vendor_id` and `pickup_datetime` in [`models/staging/schema.yml`](https://github.com/DataTalksClub/data-engineering-zoomcamp/blob/main/models/staging/schema.yml).

However, production pipelines often require **domain-specific assertions** that cross multiple columns, such as verifying that trip duration is positive or that cash payments have zero tips. Custom tests fill this gap by allowing you to write Jinja macros that return SQL queries; dbt interprets any rows returned by the query as test failures.

## Architecture of a Custom Test

Custom tests in dbt follow a three-part architecture that separates logic from configuration.

### 1. Macro Definition in the `macros/` Directory

Create a `.sql` file in your `macros/` directory containing a Jinja macro that accepts a `model` argument and returns a `SELECT` statement. The query should surface only rows that violate your assertion.

For example, to ensure positive trip durations:

```sql
{% macro test_positive_trip_duration(model) %}
  SELECT *
  FROM {{ model }}
  WHERE {{ get_trip_duration_minutes('pickup_datetime','dropoff_datetime') }} <= 0
{% endmacro %}

```

This macro leverages the reusable `get_trip_duration_minutes` macro (defined in [`macros/get_trip_duration_minutes.sql`](https://github.com/DataTalksClub/data-engineering-zoomcamp/blob/main/macros/get_trip_duration_minutes.sql)) to abstract warehouse-specific `datediff` logic:

```sql
{% macro get_trip_duration_minutes(pickup_datetime, dropoff_datetime) %}
    {{ dbt.datediff(pickup_datetime, dropoff_datetime, 'minute') }}
{% endmacro %}

```

### 2. Reference in [`schema.yml`](https://github.com/DataTalksClub/data-engineering-zoomcamp/blob/main/schema.yml)

Unlike built-in tests that attach to columns, custom tests attach to models under the `tests:` key. In [`models/staging/schema.yml`](https://github.com/DataTalksClub/data-engineering-zoomcamp/blob/main/models/staging/schema.yml), add the macro name under the model's test section:

```yaml
models:
  - name: stg_yellow_tripdata
    description: "Staging model for yellow taxi trips."
    columns:
      - name: vendor_id
        data_tests:
          - not_null
    tests:
      - test_positive_trip_duration

```

### 3. Helper Macros for Safe Operations

The repo includes a `safe_cast` macro in [`macros/safe_cast.sql`](https://github.com/DataTalksClub/data-engineering-zoomcamp/blob/main/macros/safe_cast.sql) that abstracts BigQuery’s `SAFE_CAST` versus regular `CAST`. Use these helpers inside custom tests when coercing data types before asserting conditions to prevent runtime errors.

## Executing Custom Tests

When you run the test command, dbt compiles the model, injects the custom test macro with the model name, and executes the generated SQL against your warehouse:

```bash
dbt test --select stg_yellow_tripdata

```

If the query returns any rows, dbt reports a failure and displays the compiled SQL, allowing you to inspect the violating records directly.

## Advanced Pattern: Conditional Acceptance

Custom tests can enforce complex business rules involving multiple columns. For example, to assert that cash trips (payment_type = 2) should have no tips:

```sql
{% macro test_cash_trips_no_tip(model) %}
  SELECT *
  FROM {{ model }}
  WHERE payment_type = 2
    AND tip_amount > 0
{% endmacro %}

```

Add this under `tests:` in [`models/staging/schema.yml`](https://github.com/DataTalksClub/data-engineering-zoomcamp/blob/main/models/staging/schema.yml) alongside your other assertions to maintain comprehensive data quality coverage.

## Summary

- **Create reusable macros** in the `macros/` directory that return SQL queries selecting violating rows to implement custom data quality rules.
- **Attach custom tests** to models using the `tests:` key in [`schema.yml`](https://github.com/DataTalksClub/data-engineering-zoomcamp/blob/main/schema.yml), distinguishing them from column-level built-in tests.
- **Leverage helper macros** like `get_trip_duration_minutes` and `safe_cast` to write warehouse-agnostic assertions that abstract dialect-specific SQL.
- **Execute with `dbt test`**; any rows returned by the custom query surface as failures in the run output.

## Frequently Asked Questions

### How do custom tests differ from built-in generic tests?

Built-in tests like `not_null` and `unique` attach to specific columns and handle common constraints, while custom tests are Jinja macros that attach to entire models and can evaluate complex logic across multiple columns or calculations.

### Where should I store custom test macros in a dbt project?

Store custom test macros as `.sql` files in your project's `macros/` directory. The Data Engineering ZoomCamp repo follows this pattern, placing reusable logic like [`macros/test_positive_trip_duration.sql`](https://github.com/DataTalksClub/data-engineering-zoomcamp/blob/main/macros/test_positive_trip_duration.sql) alongside helper utilities.

### Can custom tests reference other macros?

Yes, custom tests can call other macros using standard dbt syntax. The `test_positive_trip_duration` macro in the ZoomCamp repository calls `get_trip_duration_minutes` to reuse date difference logic across the project.

### How does dbt determine if a custom test passes or fails?

dbt interprets the result set of the SQL query returned by the macro. If the query returns zero rows, the test passes; if it returns one or more rows, dbt marks the test as failed and surfaces the violating records.