How it Works

The expect_column_values_to_not_match_regex test from the dbt-expectations package is the inverse of expect_column_values_to_match_regex. It asserts that none of the values in a column match a specified regular expression, making it ideal for blocking known bad patterns such as placeholder strings, injection attempts, or deprecated identifier formats.

Steps and Conditions

1. Column Selection: Choose the column to validate.

2. Define Exclusion Pattern: Supply the regex that values must NOT match using regex.

3. Optional Row Condition: Narrow the scope with row_condition.

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

5. Outcome: Pass if no values match the pattern; fail if any do.

Example Usage: Data Security

A data security team wants to ensure no raw SQL injection patterns have been stored in a user_input column.

models:
  - name: form_submissions
    columns:
      - name: user_input
        tests:
          - dbt_expectations.expect_column_values_to_not_match_regex:
              regex: "(DROP|INSERT|UPDATE|DELETE|SELECT)\\s+"

models:
  - name: form_submissions
    columns:
      - name: user_input
        tests:
          - dbt_expectations.expect_column_values_to_not_match_regex:
              regex: "(DROP|INSERT|UPDATE|DELETE|SELECT)\\s+"

models:
  - name: form_submissions
    columns:
      - name: user_input
        tests:
          - dbt_expectations.expect_column_values_to_not_match_regex:
              regex: "(DROP|INSERT|UPDATE|DELETE|SELECT)\\s+"

Any stored value matching SQL keyword patterns will fail the test and trigger a security review.