How it Works

The expect_column_most_common_value_to_be_in_set test from the dbt-expectations package validates that the most frequently occurring value (or one of the top N most common values) in a column belongs to a specified set. This is useful for detecting when an unexpected value has overtaken normal values in frequency — which could indicate a data quality issue or an upstream change.

The top_n argument controls how many of the most common values are checked.

Steps and Conditions

1. Column Selection: Identify the column to evaluate.

2. Define Expected Set: List acceptable most-common values using value_set.

3. Top N: Specify top_n (default: 1) for how many top values to check.

4. Execution: The top N most frequent values are identified and checked against the set.

5. Outcome: Pass if the top values are in the set; fail if unexpected values dominate.

Example Usage: Status Monitoring

A platform wants to ensure that the most common order status is always 'completed' or 'processing', not 'failed' or 'pending'.

models:
  - name: orders
    columns:
      - name: order_status
        tests:
          - dbt_expectations.expect_column_most_common_value_to_be_in_set:
              value_set: ['completed', 'processing']
              top_n: 1

models:
  - name: orders
    columns:
      - name: order_status
        tests:
          - dbt_expectations.expect_column_most_common_value_to_be_in_set:
              value_set: ['completed', 'processing']
              top_n: 1

models:
  - name: orders
    columns:
      - name: order_status
        tests:
          - dbt_expectations.expect_column_most_common_value_to_be_in_set:
              value_set: ['completed', 'processing']
              top_n: 1

If 'failed' becomes the most common status, the test fails immediately, signalling a systemic issue with order processing.