How it Works

The expect_column_values_to_match_like_pattern_list test from the dbt-expectations package validates that column values match at least one SQL LIKE pattern from a provided list. The match_on argument controls whether values need to satisfy any (default) or all patterns in the list.

Steps and Conditions

1. Column Selection: Identify the column to validate.

2. Define Pattern List: Provide the list of LIKE patterns.

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

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

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

Example Usage: Product SKUs

A retail company accepts product SKUs in multiple regional formats — either starting with a region prefix or following a global format.

models:
  - name: product_inventory
    columns:
      - name: sku
        tests:
          - dbt_expectations.expect_column_values_to_match_like_pattern_list:
              like_pattern_list: ["US-%", "EU-%", "GLOB-%"]
              match_on

models:
  - name: product_inventory
    columns:
      - name: sku
        tests:
          - dbt_expectations.expect_column_values_to_match_like_pattern_list:
              like_pattern_list: ["US-%", "EU-%", "GLOB-%"]
              match_on

models:
  - name: product_inventory
    columns:
      - name: sku
        tests:
          - dbt_expectations.expect_column_values_to_match_like_pattern_list:
              like_pattern_list: ["US-%", "EU-%", "GLOB-%"]
              match_on

Any SKU that doesn't start with a recognized region prefix fails the test, catching malformed entries before they reach inventory systems.