How it Works

The expect_row_values_to_have_recent_data test from the dbt-expectations package checks that a model or source contains at least one row whose timestamp column value falls within a recent time window. This is a critical freshness check to detect pipeline staleness before downstream consumers notice.

The recency window is defined by datepart and interval arguments (e.g., the last 1 day or last 3 hours).

Steps and Conditions

1. Column Selection: Identify the timestamp column to evaluate.

2. Define Recency Window: Set the datepart (e.g., day, hour) and interval (e.g., 1, 6).

3. Execution: The test checks whether any row has a timestamp within the window relative to the current time.

4. Outcome: Pass if at least one recent row exists; fail if all data is stale.

Example Usage: IoT Platform

An IoT platform ingests device telemetry every hour. The team wants to ensure the device_telemetry table always has data from the last 2 hours.

models:
  - name: device_telemetry
    columns:
      - name: recorded_at
        tests:
          - dbt_expectations.expect_row_values_to_have_recent_data:
              datepart: hour
              interval: 2

models:
  - name: device_telemetry
    columns:
      - name: recorded_at
        tests:
          - dbt_expectations.expect_row_values_to_have_recent_data:
              datepart: hour
              interval: 2

models:
  - name: device_telemetry
    columns:
      - name: recorded_at
        tests:
          - dbt_expectations.expect_row_values_to_have_recent_data:
              datepart: hour
              interval: 2

If no data has arrived in the last 2 hours, the test fails and triggers an alert.