dbt test: expect_column_values_to_be_decreasing

This page outlines the expect_column_values_to_be_decreasing test from the dbt-expectations package. This test is designed to assert that the values in a specific column of a dataset are in a decreasing order, crucial for datasets where order and trend analysis are important.

How it Works

The expect_column_values_to_be_decreasing test evaluates a specified column to ensure that each subsequent value is less than or equal to (or strictly less than, if configured) the previous value recorded.

Steps and Conditions:

1. Column Selection: Designate the column whose values need to be scrutinized for the decreasing sequence.
2. Strictness Configuration: Decide if the test should enforce a strictly decreasing sequence (strictly: true) where each subsequent value must be less than its predecessor or allow for equal consecutive values (strictly: false).
3. Row Condition: Optionally apply a condition to filter rows that are included in the test, enabling focused validation on relevant data subsets.
4. Grouping: Optionally, specify one or more columns for grouping data. The test will be applied within each group separately, useful in scenarios like time-series data for different categories.
5. Execution: Run the test where the values are checked in sequence based on the sort criteria, and adherence to the decreasing condition is validated.
6. Outcome:
   • Pass: If all values in the column comply with the chosen strictly or non-strictly decreasing condition, the test confirms the column maintains the expected order.
   • Fail: If any value violates the decreasing sequence, the test will highlight these instances for further data review and rectification.

Example Usage: Fintech

In a Fintech environment, ensuring the correctness of time-series data, for example, bond yields or interest rates over days, is essential.

Consider a scenario where a daily_rates table records interest rates provided to clients with a rate_date and interest_rate column. For financial stability and policy compliance, it may be vital to test that interest rates are not increasing over time.

models:
  - name: daily_rates
    columns:
      - name: interest_rate
        tests:
          - dbt_expectations.expect_column_values_to_be_decreasing:
              sort_column: rate_date
              strictly: true

In this example, the expect_column_values_to_be_decreasing test is applied to the interest_rate column, sorted by rate_date. This setup helps ensure that the interest rates are continuously decreasing or at least not increasing, adhering to specific financial regulations or company policies.