The dbt-expectations Package
Out of the box, dbt only provides four built-in tests: unique, not_null, accepted_values, and relationships.
What if you need to test that a phone number column exactly matches an Indian mobile number regex? Or what if you want to ensure the order_amount column never deviates by more than 3 standard deviations from the historical average?
Writing custom Jinja tests for all of these scenarios is exhausting. Fortunately, the open-source community has done the work for you by creating the dbt-expectations package (inspired by the famous Python library Great Expectations).
Topics Covered
- Installing
dbt-expectations - Common structural tests (row counts, schema checks)
- Common column tests (string lengths, regex)
- Advanced mathematical tests
1. What is dbt-expectations?
The dbt-expectations package adds a set of reusable data-quality tests to your dbt project. It is especially useful when you want to validate patterns, distributions, and business rules without writing custom SQL every time.
It is built for real-world pipelines where data quality is not only about nulls and uniqueness but also about:
- valid email formats
- acceptable row counts
- regex-based string validation
- threshold and anomaly checks
2. Install the package
Add the package to your packages.yml and run dbt deps.
packages:
- package: calogica/dbt_expectations
version: ">=0.10.0", "<0.11.0"
Then run:
dbt deps
3. Structural tests
These tests validate the shape or size of a table.
models:
- name: fct_orders
tests:
- dbt_expectations.expect_table_row_count_to_be_between:
min_value: 1
- dbt_expectations.expect_table_columns_to_match_ordered_list:
column_list: ["order_id", "customer_id", "amount", "order_date"]
This helps catch:
- empty fact tables
- unexpected schema drift
- column ordering issues after upstream changes
4. Column-level regex and string checks
The package is especially helpful for validating string and pattern-based business data.
models:
- name: dim_customers
columns:
- name: pincode
tests:
- dbt_expectations.expect_column_value_lengths_to_equal:
value: 6
- name: pan_card_number
tests:
- dbt_expectations.expect_column_values_to_match_regex:
regex: "^[A-Z]{5}[0-9]{4}[A-Z]{1}$"
- name: customer_email
tests:
- dbt_expectations.expect_column_values_to_match_like_pattern:
like_pattern: "%@%.%"
These checks are very common in e-commerce, fintech, and identity data pipelines.
5. Statistical checks and anomaly thresholds
You can also create business-level checks for unusual values, outliers, or numeric thresholds.
models:
- name: fct_daily_revenue
columns:
- name: total_revenue_inr
tests:
- dbt_expectations.expect_column_values_to_be_between:
min_value: 0
max_value: 5000000
- dbt_expectations.expect_column_values_to_be_within_n_stdevs:
group_by: [store_city]
n_stdevs: 3
This is useful when you want to detect sudden spikes or anomalies in revenue or inventory data before they reach reporting users.
6. When to use it
Use dbt-expectations when:
- you need reusable quality checks
- you want to validate values using regex or patterns
- you want to detect outliers or unexpected thresholds
- you want to avoid writing the same custom tests repeatedly
Summary
dbt-expectationsextends dbt with many ready-to-use data-quality tests.- It is ideal for schema checks, pattern validation, and advanced business rules.
- It keeps your project cleaner than writing one-off Jinja tests for every edge case.
- It belongs in the package and utilities topic area, not in the core testing module as a separate duplicate lesson.