How it Works

The expect_column_values_to_not_match_regex_list test from the dbt-expectations package validates that column values do not match any pattern from a list of regular expressions. By default, values must avoid all patterns in the list (match_on: any), but you can configure match_on: all to require that values avoid every pattern simultaneously.

Steps and Conditions

1. Column Selection: Choose the column to validate.

2. Define Exclusion List: Provide the regex patterns that values must NOT match using regex_list.

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

4. Execution: Each value is tested against all patterns.

5. Outcome: Pass if no values match any pattern (or all patterns for all mode); fail otherwise.

Example Usage: Data Sanitization

A data sanitization layer wants to ensure that no values in a comment column contain patterns associated with credit card numbers or social security numbers.

models:
  - name: customer_comments
    columns:
      - name: comment_text
        tests:
          - dbt_expectations.expect_column_values_to_not_match_regex_list:
              regex_list:
                - "\\b[0-9]{4}[\\s-]?[0-9]{4}[\\s-]?[0-9]{4}[\\s-]?[0-9]{4}\\b"
                - "\\b[0-9]{3}-[0-9]{2}-[0-9]{4}\\b"
              match_on

models:
  - name: customer_comments
    columns:
      - name: comment_text
        tests:
          - dbt_expectations.expect_column_values_to_not_match_regex_list:
              regex_list:
                - "\\b[0-9]{4}[\\s-]?[0-9]{4}[\\s-]?[0-9]{4}[\\s-]?[0-9]{4}\\b"
                - "\\b[0-9]{3}-[0-9]{2}-[0-9]{4}\\b"
              match_on

models:
  - name: customer_comments
    columns:
      - name: comment_text
        tests:
          - dbt_expectations.expect_column_values_to_not_match_regex_list:
              regex_list:
                - "\\b[0-9]{4}[\\s-]?[0-9]{4}[\\s-]?[0-9]{4}[\\s-]?[0-9]{4}\\b"
                - "\\b[0-9]{3}-[0-9]{2}-[0-9]{4}\\b"
              match_on

Any comment matching credit card or SSN patterns triggers a failure for PII remediation.