How it Works

The recency test from dbt-utils™ verifies that a model or source contains at least one row with a timestamp more recent than a specified interval from the current time. It is a straightforward freshness guard that protects downstream models and dashboards from consuming stale data.

The test is configured with a datepart (e.g., day, hour) and an interval (e.g., 1, 3), and the field argument specifies which timestamp column to evaluate.

Steps and Conditions

1. Field Selection: Specify the timestamp column using field.

2. Define Freshness Window: Set datepart and interval.

3. Execution: The test queries the maximum value of the specified field and checks it against current_timestamp - interval.

4. Outcome: Pass if the most recent value is within the window; fail if data is stale.

Example Usage: Financial Data Platform

A financial data platform ingests market prices daily and wants to ensure the market_prices model always contains data from the current day.

models:
  - name: market_prices
    tests:
      - dbt_utils.recency:
          datepart: day
          field: price_date
          interval: 1

models:
  - name: market_prices
    tests:
      - dbt_utils.recency:
          datepart: day
          field: price_date
          interval: 1

models:
  - name: market_prices
    tests:
      - dbt_utils.recency:
          datepart: day
          field: price_date
          interval: 1

If market prices have not been refreshed within the last day, the test fails and alerts the data engineering team.