How it Works

The expect_column_values_to_not_match_like_pattern_list test from the dbt-expectations package is the complement of expect_column_values_to_match_like_pattern_list. It validates that column values do not match any pattern from a list of disallowed SQL LIKE patterns. The match_on argument controls whether values must avoid any (default) or all patterns.

Steps and Conditions

1. Column Selection: Identify the column to validate.

2. Define Exclusion List: Provide the LIKE patterns that values must NOT match.

3. Match Mode: Set match_on to any or all.

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

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

Example Usage: Content Moderation

A content moderation team wants to block usernames that start with well-known bot or spam prefixes.

models:
  - name: user_accounts
    columns:
      - name: username
        tests:
          - dbt_expectations.expect_column_values_to_not_match_like_pattern_list:
              like_pattern_list: ["bot_%", "spam_%", "auto_%", "test_%"]
              match_on

models:
  - name: user_accounts
    columns:
      - name: username
        tests:
          - dbt_expectations.expect_column_values_to_not_match_like_pattern_list:
              like_pattern_list: ["bot_%", "spam_%", "auto_%", "test_%"]
              match_on

models:
  - name: user_accounts
    columns:
      - name: username
        tests:
          - dbt_expectations.expect_column_values_to_not_match_like_pattern_list:
              like_pattern_list: ["bot_%", "spam_%", "auto_%", "test_%"]
              match_on

Any username matching a known spam prefix triggers a failure, enabling automated review of suspicious registrations before they go live.