How it Works

The accepted_range test asserts that the values of a column are within a specified minimum and maximum value. This range can be defined to be either inclusive or exclusive of the boundary values. The test allows for flexibility in defining the range, as you can specify either or both min_value and max_value, and even compare a column's values against another column or a scalar value like the current date. This makes it suitable for a wide array of data validation needs, from ensuring IDs are positive to verifying dates are in the past.

Steps and Conditions

1. Range Specification: Define the acceptable range for the column values with min_value and/or max_value. The range can be made inclusive or exclusive.

2. Comparison Execution: The test compares each value in the specified column against the defined range.

3. Filtering (Optional): A where argument can be provided to apply the test to a subset of records, allowing for more targeted validation.

4. Outcome:

Example Usage: E-commerce Company

For an E-commerce company, the accepted_range test can be applied to various columns in the users and orders tables to ensure data integrity.

models:
  - name: users
    columns:
      - name: user_id
        tests:
          - dbt_utils.accepted_range:
              min_value: 1
              inclusive: true

      - name: account_created_at
        tests:
          - dbt_utils.accepted_range:
              max_value: "getdate()"

      - name: num_returned_orders
        tests:
          - dbt_utils.accepted_range:
              min_value: 0
              max_value: "num_orders"

  - name: orders
    columns:
      - name: num_web_sessions
        tests:
          - dbt_utils.accepted_range:
              min_value: 0
              inclusive: false
              config:
              where: "num_orders > 0"

models:
  - name: users
    columns:
      - name: user_id
        tests:
          - dbt_utils.accepted_range:
              min_value: 1
              inclusive: true

      - name: account_created_at
        tests:
          - dbt_utils.accepted_range:
              max_value: "getdate()"

      - name: num_returned_orders
        tests:
          - dbt_utils.accepted_range:
              min_value: 0
              max_value: "num_orders"

  - name: orders
    columns:
      - name: num_web_sessions
        tests:
          - dbt_utils.accepted_range:
              min_value: 0
              inclusive: false
              config:
              where: "num_orders > 0"

models:
  - name: users
    columns:
      - name: user_id
        tests:
          - dbt_utils.accepted_range:
              min_value: 1
              inclusive: true

      - name: account_created_at
        tests:
          - dbt_utils.accepted_range:
              max_value: "getdate()"

      - name: num_returned_orders
        tests:
          - dbt_utils.accepted_range:
              min_value: 0
              max_value: "num_orders"

  - name: orders
    columns:
      - name: num_web_sessions
        tests:
          - dbt_utils.accepted_range:
              min_value: 0
              inclusive: false
              config:
              where: "num_orders > 0"

In this example, the accepted_range test ensures that:

• User IDs are positive integers.

• Account creation dates are not in the future.

• The number of returned orders does not exceed the total number of orders.

• The number of web sessions is positive for users with at least one order.