How it Works

The expect_column_values_to_be_decreasing test from the dbt-expectations package validates that values in a column are monotonically non-increasing (or strictly decreasing if configured). This is the counterpart to expect_column_values_to_be_increasing and is useful for validating descending rankings, countdown sequences, or metrics that should always decrease over time within a given partition.

The strictly argument (default false) controls whether equal consecutive values are acceptable.

Steps and Conditions

1. Column Selection: Identify the column to validate.

2. Optional Sort: Specify sort_column and sort_column_desc to define ordering.

3. Strictness: Set strictly: true for strictly decreasing, false for non-increasing.

4. Execution: Each value is compared to the previous row's value.

5. Outcome: Pass if the sequence is monotonically decreasing; fail if any increase is detected.

Example Usage: Rank Tables

A competitive gaming platform generates weekly player rankings ordered from highest to lowest score. The rank column should always decrease (i.e., go from 1 toward the bottom).

models:
  - name: weekly_player_rankings
    columns:
      - name: score
        tests:
          - dbt_expectations.expect_column_values_to_be_decreasing:
              strictly: false
              sort_column

models:
  - name: weekly_player_rankings
    columns:
      - name: score
        tests:
          - dbt_expectations.expect_column_values_to_be_decreasing:
              strictly: false
              sort_column

models:
  - name: weekly_player_rankings
    columns:
      - name: score
        tests:
          - dbt_expectations.expect_column_values_to_be_decreasing:
              strictly: false
              sort_column

Any increase in score down the ranked list would indicate a data ordering error in the ranking model.