How it Works

The expect_column_values_to_match_regex_list test from the dbt-expectations package verifies that column values match at least one pattern from a supplied list of regular expressions. By default, a value needs to satisfy only one pattern (match_on: any), but you can set match_on: all to require all patterns to match.

Steps and Conditions

1. Column Selection: Choose the column to evaluate.

2. Define Regex List: Provide the list of regex patterns using regex_list.

3. Match Mode: Set match_on to any (default) or all.

4. Execution: Each value is tested against the regex list.

5. Outcome: Pass if values satisfy the match condition; fail otherwise.

Example Usage: Identity Platform

An identity platform accepts phone numbers in multiple international formats. The test validates that all stored phone numbers conform to at least one of the accepted patterns.

models:
  - name: user_profiles
    columns:
      - name: phone_number
        tests:
          - dbt_expectations.expect_column_values_to_match_regex_list:
              regex_list: ["^\\+1[0-9]{10}$", "^\\+44[0-9]{10}$", "^\\+61[0-9]{9}$"]
              match_on

models:
  - name: user_profiles
    columns:
      - name: phone_number
        tests:
          - dbt_expectations.expect_column_values_to_match_regex_list:
              regex_list: ["^\\+1[0-9]{10}$", "^\\+44[0-9]{10}$", "^\\+61[0-9]{9}$"]
              match_on

models:
  - name: user_profiles
    columns:
      - name: phone_number
        tests:
          - dbt_expectations.expect_column_values_to_match_regex_list:
              regex_list: ["^\\+1[0-9]{10}$", "^\\+44[0-9]{10}$", "^\\+61[0-9]{9}$"]
              match_on

This ensures that all stored phone numbers follow a valid US, UK, or Australian format.