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

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 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 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.

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:

{% 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) to abstract warehouse-specific datediff logic:

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

2. Reference in schema.yml

Unlike built-in tests that attach to columns, custom tests attach to models under the tests: key. In models/staging/schema.yml, add the macro name under the model's test section:

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 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:

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:

{% 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 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, 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 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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}