How it Works

The expect_column_values_to_match_like_pattern test from the dbt-expectations package validates that all values in a column match a given SQL LIKE pattern. This is a simple but effective way to enforce string format conventions without needing full regex syntax.

Steps and Conditions

1. Column Selection: Identify the column to check.

2. Define Pattern: Provide the SQL LIKE pattern using like_pattern.

3. Optional Row Condition: Scope the check using row_condition.

4. Execution: Each value is compared against the LIKE pattern.

5. Outcome: Pass if all values match; fail if any do not.

Example Usage: Data Warehouse

An analytics team stores internal project codes that must always begin with a two-letter department prefix followed by a dash.

models:
  - name: project_budget
    columns:
      - name: project_code
        tests:
          - dbt_expectations.expect_column_values_to_match_like_pattern:
              like_pattern: "__-%"

models:
  - name: project_budget
    columns:
      - name: project_code
        tests:
          - dbt_expectations.expect_column_values_to_match_like_pattern:
              like_pattern: "__-%"

models:
  - name: project_budget
    columns:
      - name: project_code
        tests:
          - dbt_expectations.expect_column_values_to_match_like_pattern:
              like_pattern: "__-%"

This ensures all project codes follow the expected naming convention, catching any codes that deviate from the standard format.