We’ve spent the last three articles learning how to unit test your dbt models. You can now mock inputs, validate transformation logic, and catch bugs before they reach production. Unit tests verify that your code works correctly. They don’t verify that your data is healthy.

Your SQL can be flawless and still produce garbage outputs. A source system sends nulls where it never did before. A daily batch arrives six hours late. An upstream change shifts your average order value by 50%. Your unit tests pass. Your dashboards break.

This is where dbt-expectations comes in. It’s a package of 50+ pre-built data quality tests that catch the problems correct SQL can’t prevent. Where unit tests ask “does my transformation logic work?”, dbt-expectations asks “is my actual production data healthy?”

Together with the unit testing skills from articles 1-3, dbt-expectations completes your testing strategy.

What native dbt tests can’t do

Out of the box, dbt ships with four generic tests: unique, not_null, accepted_values, and relationships. These cover the basics, but they leave significant gaps.

Can you validate that an email column contains properly formatted emails? No. Can you check that a timestamp column contains recent data? Only for sources, not for models. Can you verify that a composite key across multiple columns is unique? No. Can you detect when your average metric value shifts outside normal ranges? No.

dbt-expectations fills every one of these gaps. It brings pattern matching, statistical validation, freshness checks on any model, multi-column tests, and conditional testing to your dbt project, all without leaving SQL.

Installation and setup

Add the package to your packages.yml:

packages:
  - package: metaplane/dbt_expectations
    version: [">=0.10.0", "<0.11.0"]

The package requires a timezone variable for date-based tests. Add this to your dbt_project.yml:

vars:
  'dbt_date:time_zone': 'Europe/Paris'

Run dbt deps and you’re ready. The package automatically pulls in dbt-date and dbt-utils as dependencies, so you don’t need to manage those separately.

dbt-expectations requires dbt 1.8. It fully supports BigQuery, Snowflake, Postgres, Redshift, DuckDB, and Trino.

Unit tests vs. data tests: the complete picture

Before looking at specific tests, here’s how dbt-expectations fits alongside the unit testing you learned in articles 1-3.

Think of it as two checkpoints in your data pipeline:

Code changes → Unit tests → Deployment → Data tests → Dashboard

Unit tests gate your deployments. Data tests gate your data. You need both.

A concrete example: you have a model that calculates revenue by multiplying quantity by unit price. Your unit test verifies that 3 * 10.00 = 30.00 (the multiplication logic works). Your dbt-expectations test verifies that the resulting revenue values in production fall between 0 and 10,000,000 (the data is sane). The first catches a bug if someone changes the formula. The second catches a problem if a source system suddenly sends negative quantities.

The tests that matter most

With 50+ tests available, you don’t need to learn them all. Here are the highest-value tests in each category, with BigQuery-specific examples.

Table-level tests

expect_row_values_to_have_recent_data

This is arguably the most valuable test in the entire package. Native dbt only offers freshness checks on sources. This test works on any model, catching stale data before your dashboards show yesterday’s numbers as today’s.

models:
  - name: mrt__sales__orders
    columns:
      - name: order_timestamp
        tests:
          - dbt_expectations.expect_row_values_to_have_recent_data:
              datepart: hour
              interval: 24

This fails if no rows have an order_timestamp within the last 24 hours. For GA4 data, which typically has a 24-48 hour delay, you’d set interval: 48.

expect_table_row_count_to_equal_other_table

Transformations shouldn’t drop rows silently. This test catches it when they do:

models:
  - name: mrt__sales__orders
    tests:
      - dbt_expectations.expect_table_row_count_to_equal_other_table:
          compare_model: ref('base__shopify__orders')

If your base model has 50,000 rows and your mart has 49,000, something went wrong. This test tells you immediately.

expect_table_row_count_to_be_between

Detect unexpected volume changes. If your daily batch normally contains 10,000-100,000 rows and suddenly has 500, you want to know:

models:
  - name: base__ga4__events
    tests:
      - dbt_expectations.expect_table_row_count_to_be_between:
          min_value: 10000
          max_value: 100000

Pattern validation

expect_column_values_to_match_regex

Native dbt has nothing for format validation. This test fills that gap:

columns:
  - 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,}$'

expect_column_values_to_match_like_pattern

When regex is overkill, use SQL LIKE patterns instead:

columns:
  - name: product_sku
    tests:
      - dbt_expectations.expect_column_values_to_match_like_pattern:
          like_pattern: 'PRD-%'

Value range validation

expect_column_values_to_be_between

Catch impossible values before they corrupt your metrics:

columns:
  - name: order_value
    tests:
      - dbt_expectations.expect_column_values_to_be_between:
          min_value: 0
          max_value: 1000000

  - name: conversion_rate
    tests:
      - dbt_expectations.expect_column_values_to_be_between:
          min_value: 0
          max_value: 1

  - name: event_date
    tests:
      - dbt_expectations.expect_column_values_to_be_between:
          min_value: "'2020-01-01'"
          max_value: "current_date()"

Note that string and date values need to be wrapped in quotes inside quotes.

expect_column_mean_to_be_between

This catches distribution shifts. Your individual values might all be valid, but if your average suddenly drops 50%, something’s wrong:

columns:
  - name: order_value
    tests:
      - dbt_expectations.expect_column_mean_to_be_between:
          min_value: 50
          max_value: 200

Query your data first to establish reasonable bounds. This test is about catching anomalies, not enforcing exact values.

Multi-column validation

expect_compound_columns_to_be_unique

Native unique only works on single columns. For composite primary keys, you need this:

models:
  - name: mrt__sales__order_lines
    tests:
      - dbt_expectations.expect_compound_columns_to_be_unique:
          column_list: ["order_id", "line_item_id"]

expect_column_pair_values_A_to_be_greater_than_B

Validate business logic that spans columns:

models:
  - name: mrt__finance__subscriptions
    tests:
      - dbt_expectations.expect_column_pair_values_A_to_be_greater_than_B:
          column_A: end_date
          column_B: start_date
          or_equal: true
          row_condition: "end_date is not null"

Other use cases: shipped_date > order_date, total_amount >= subtotal, updated_at >= created_at.

Completeness tests

expect_row_values_to_have_data_for_every_n_datepart

Detect gaps in time series data. If you’re missing an entire day of GA4 events, this test fails:

columns:
  - name: event_date
    tests:
      - dbt_expectations.expect_row_values_to_have_data_for_every_n_datepart:
          date_col: event_date
          date_part: day
          test_start_date: "'2024-01-01'"
          test_end_date: "current_date() - 1"

Always specify date bounds. Without them, this test scans your entire table and can be expensive on large datasets.

The row_condition superpower

Almost every dbt-expectations test supports a row_condition parameter. This lets you apply tests conditionally without writing custom SQL.

Test that account_id is not null, but only for active subscriptions:

columns:
  - name: account_id
    tests:
      - dbt_expectations.expect_column_values_to_not_be_null:
          row_condition: "subscription_status = 'active'"

Validate email format only where email exists:

columns:
  - name: email
    tests:
      - dbt_expectations.expect_column_values_to_match_regex:
          regex: '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$'
          row_condition: "email is not null"

Check value ranges for specific segments:

columns:
  - name: order_value
    tests:
      - dbt_expectations.expect_column_values_to_be_between:
          min_value: 0
          max_value: 50000
          row_condition: "country_code = 'FR' and order_status = 'completed'"

This parameter alone justifies installing the package. It eliminates the need for dozens of custom tests.

Implementation patterns for BigQuery

Where to place tests

Structure your tests by layer:

Sources and base models: Focus on freshness, schema validation, and basic format checks. This is where you catch problems closest to the source.

models:
  - name: base__ga4__events
    tests:
      - dbt_expectations.expect_row_values_to_have_recent_data:
          datepart: hour
          interval: 48
    columns:
      - name: user_pseudo_id
        tests:
          - dbt_expectations.expect_column_values_to_match_regex:
              regex: '^[0-9]+\\.[0-9]+$'

Intermediate models: Focus on join integrity and transformation validation. Verify that your joins don’t drop or duplicate rows unexpectedly.

Marts: Focus on business rules and aggregation sanity checks. These tests protect your final outputs.

models:
  - name: mrt__marketing__campaign_performance
    columns:
      - name: roas
        tests:
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
              max_value: 100
              row_condition: "spend > 0"

Severity configuration

Not every test failure should block your pipeline. Use severity: warn for tests that need investigation but shouldn’t stop production:

columns:
  - name: order_value
    tests:
      - dbt_expectations.expect_column_mean_to_be_between:
          min_value: 50
          max_value: 200
          config:
            severity: warn

Reserve severity: error (the default) for critical failures: primary key violations, freshness on critical tables, data that would break downstream systems.

Performance considerations

Some tests can be expensive on large BigQuery tables. Here’s how to manage costs.

Use row_condition with partition columns. If your table is partitioned by date, always filter:

- dbt_expectations.expect_column_values_to_be_between:
    min_value: 0
    max_value: 1000000
    row_condition: "event_date >= current_date() - 30"

Run expensive tests only in production:

- dbt_expectations.expect_column_mean_to_be_between:
    min_value: 50
    max_value: 200
    config:
      enabled: "{{ target.name == 'prod' }}"

Tag slow tests for separate runs:

- dbt_expectations.expect_row_values_to_have_data_for_every_n_datepart:
    date_col: event_date
    date_part: day
    config:
      tags: ['slow', 'daily']

Then in CI, run only fast tests: dbt test --exclude tag:slow

The most expensive tests are typically expect_row_values_to_have_data_for_every_n_datepart, statistical tests like expect_column_mean_to_be_between, and any test that doesn’t filter on partition columns.

Real-world example: GA4 and ads data quality

Here’s a complete example covering a GA4 events base model and an ads performance mart (typical models in a marketing analytics project).

GA4 events base model

version: 2

models:
  - name: base__ga4__events
    description: "Base GA4 events with basic cleaning applied"

    tests:
      # Table should have recent data (accounting for GA4's 24-48h delay)
      - dbt_expectations.expect_row_values_to_have_recent_data:
          datepart: hour
          interval: 48

      # No missing days in the time series
      - dbt_expectations.expect_row_values_to_have_data_for_every_n_datepart:
          date_col: event_date
          date_part: day
          test_start_date: "date_sub(current_date(), interval 90 day)"
          test_end_date: "date_sub(current_date(), interval 2 day)"

    columns:
      - name: event_id
        description: "Unique event identifier"
        tests:
          - unique
          - not_null


      - name: event_timestamp
        description: "Event timestamp in UTC"
        tests:
          - not_null
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: "'2020-01-01 00:00:00'"
              max_value: "current_timestamp()"

      - name: event_name
        description: "GA4 event name"
        tests:
          - not_null
          - dbt_expectations.expect_column_values_to_match_regex:
              regex: '^[a-z_]+$'

Ads performance mart

version: 2

models:
  - name: mrt__marketing__ads_performance
    description: "Daily ads performance by campaign"

    tests:
      # Composite primary key
      - dbt_expectations.expect_compound_columns_to_be_unique:
          column_list: ["date", "platform", "campaign_id"]

      # Should have recent data
      - dbt_expectations.expect_row_values_to_have_recent_data:
          datepart: day
          interval: 2

    columns:
      - name: date
        tests:
          - not_null
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: "'2023-01-01'"
              max_value: "current_date()"

      - name: platform
        tests:
          - not_null
          - accepted_values:
              values: ['google_ads', 'meta_ads', 'tiktok_ads', 'linkedin_ads']

      - name: campaign_id
        tests:
          - not_null

      - name: impressions
        tests:
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
              max_value: 1000000000

      - name: clicks
        tests:
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
              max_value: 100000000

      - name: spend
        tests:
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
              max_value: 10000000
          # Warn if average daily spend seems off
          - dbt_expectations.expect_column_mean_to_be_between:
              min_value: 10
              max_value: 100000
              row_condition: "date >= date_sub(current_date(), interval 30 day)"
              config:
                severity: warn

      - name: conversions
        tests:
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
              max_value: 1000000

      - name: roas
        description: "Return on ad spend"
        tests:
          # ROAS should be between 0 and 100 (where it's calculable)
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
              max_value: 100
              row_condition: "spend > 0"
              config:
                severity: warn

What else is out there

dbt-expectations isn’t the only testing package. Here’s how it compares to alternatives:

dbt-utils includes about 15 tests alongside its utility macros. You’ll find unique_combination_of_columns, expression_is_true, recency, and others. There’s some overlap with dbt-expectations, but they complement each other well. Use both; there’s no conflict.

Elementary takes a different approach. Instead of fixed thresholds (“mean should be between 50 and 200”), Elementary learns what’s normal from your historical data and alerts when values deviate. It also provides observability dashboards. Consider Elementary when you want anomaly detection without defining thresholds manually. Use dbt-expectations when you have specific business rules to enforce.

Custom generic tests remain valuable for business-specific logic that doesn’t fit pre-built tests. Even with dbt-expectations installed, you’ll occasionally need a custom test for a unique requirement.

Getting started: your first three tests

If you install dbt-expectations today, start with these three tests on your most critical model:

1. Freshness: Add expect_row_values_to_have_recent_data on your main mart’s timestamp column. This catches stale data before anyone notices the dashboard is showing yesterday’s numbers.

2. Format: Add expect_column_values_to_match_regex on one key identifier column. This catches upstream format changes immediately.

3. Range: Add expect_column_values_to_be_between on one numeric KPI column. This catches impossible values before they corrupt your metrics.

These three tests alone will catch issues that native dbt tests miss entirely. Expand from there as you identify what breaks in your specific data.

Conclusion

dbt-expectations fills the gap between basic data testing and production-grade data quality. It gives you the tests that native dbt should have included: pattern matching, statistical validation, multi-column checks, and freshness on any model.

More importantly, it completes the testing strategy you started building in articles 1-3. Unit tests verify your transformation logic is correct. dbt-expectations verifies your actual data is healthy. Together, they catch problems at both checkpoints: code bugs before deployment, data issues during production runs.

Install the package, add three tests to your most critical model, and run dbt test. You’ll likely catch something you didn’t know was broken.

Quick reference