How it Works

The expect_grouped_row_values_to_have_recent_data test from the dbt-expectations package extends freshness checking by validating that every group within a model has at least one row with a timestamp within a defined recency window. This is critical for pipelines that process data per-entity (e.g., per region, per account, per sensor) where a global freshness check might miss that one specific group has gone stale.

Steps and Conditions

1. Group By Columns: Specify the columns to group by using group_by.

2. Timestamp Column: Identify the timestamp field to evaluate.

3. Define Window: Set datepart and interval.

4. Execution: For each group, the test checks whether any row has a timestamp within the recency window.

5. Outcome: Pass if all groups have recent data; fail if any group is stale.

Example Usage: Multi-region IoT

A multi-region IoT platform wants to ensure that every region has reported sensor data within the last 6 hours, not just the platform globally.

models:
  - name: sensor_readings
    tests:
      - dbt_expectations.expect_grouped_row_values_to_have_recent_data:
          group_by: ["region_code"]
          timestamp_column: recorded_at
          datepart: hour
          interval: 6

models:
  - name: sensor_readings
    tests:
      - dbt_expectations.expect_grouped_row_values_to_have_recent_data:
          group_by: ["region_code"]
          timestamp_column: recorded_at
          datepart: hour
          interval: 6

models:
  - name: sensor_readings
    tests:
      - dbt_expectations.expect_grouped_row_values_to_have_recent_data:
          group_by: ["region_code"]
          timestamp_column: recorded_at
          datepart: hour
          interval: 6

A specific region going silent is detected even if other regions continue to report normally.