How it Works

The expect_column_value_lengths_to_be_between test from the dbt-expectations package validates that the character length of every value in a string column falls within a defined minimum and maximum range. This is useful for variable-length fields that nonetheless have sensible bounds — for example, user names, addresses, or short text fields.

An optional row_condition can scope the check.

Steps and Conditions

1. Column Selection: Identify the string column to evaluate.

2. Define Length Range: Set min_value and/or max_value.

3. Optional Row Condition: Filter rows using row_condition.

4. Execution: The length of each value is measured and compared to the range.

5. Outcome: Pass if all lengths are within range; fail if any fall outside.

Example Usage: User Registration

A user registration system requires that usernames be between 3 and 30 characters long.

models:
  - name: user_registrations
    columns:
      - name: username
        tests:
          - dbt_expectations.expect_column_value_lengths_to_be_between:
              min_value: 3
              max_value: 30

models:
  - name: user_registrations
    columns:
      - name: username
        tests:
          - dbt_expectations.expect_column_value_lengths_to_be_between:
              min_value: 3
              max_value: 30

models:
  - name: user_registrations
    columns:
      - name: username
        tests:
          - dbt_expectations.expect_column_value_lengths_to_be_between:
              min_value: 3
              max_value: 30

This catches usernames that are too short (likely a data entry error) or too long (possibly truncated by a frontend that didn't enforce the constraint).