How it Works

The expect_column_values_to_not_match_like_pattern test from the dbt-expectations package is the inverse of expect_column_values_to_match_like_pattern. It asserts that none of the values in a column match a given SQL LIKE pattern. This is useful for blocking known bad patterns, such as test data, placeholder strings, or deprecated identifier formats.

Steps and Conditions

1. Column Selection: Identify the column to validate.

2. Define Exclusion Pattern: Supply the SQL LIKE pattern that values must NOT match.

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

4. Execution: Each value is checked; any match to the pattern causes a failure.

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

Example Usage: Customer Data Platform

A customer data platform wants to ensure that no real customer email addresses contain 'test' as part of the domain, which would indicate staging data leaked into production.

models:
  - name: customers
    columns:
      - name: email
        tests:
          - dbt_expectations.expect_column_values_to_not_match_like_pattern:
              like_pattern: "%@test%"

models:
  - name: customers
    columns:
      - name: email
        tests:
          - dbt_expectations.expect_column_values_to_not_match_like_pattern:
              like_pattern: "%@test%"

models:
  - name: customers
    columns:
      - name: email
        tests:
          - dbt_expectations.expect_column_values_to_not_match_like_pattern:
              like_pattern: "%@test%"

This prevents test email addresses from appearing in the production customer table.