How it Works

The expect_column_values_to_match_regex test from the dbt-expectations package validates that every value in a column matches a specified regular expression. This is a powerful format validation tool for fields with strict structural requirements, such as email addresses, phone numbers, postal codes, or ISO date formats.

An optional row_condition can scope the check to specific rows.

Steps and Conditions

1. Column Selection: Identify the column to validate.

2. Define Pattern: Provide the regex pattern using regex.

3. Optional Row Condition: Filter rows using row_condition.

4. Execution: Each value is tested against the pattern.

5. Outcome: Pass if all values match; fail if any do not.

Example Usage: Email Validation

A customer platform stores email addresses and wants to ensure they always match a standard email format.

models:
  - name: customer_profiles
    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,}$"

models:
  - name: customer_profiles
    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,}$"

models:
  - name: customer_profiles
    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,}$"

Malformed email addresses that would cause delivery failures are caught before they reach the marketing automation system.