## TL;DR
Check `max(updated_at)` or the latest partition against a threshold on every run, and fail or alert when the data is older than expected. The fix is a lightweight check task at the start of downstream pipelines: one query, one comparison, one clear alert naming the actual age and the expected cadence. Pipelines happily process stale data unless something checks the clock.

```text
data freshness checks: how to implement them
```

## Use this when
- A dashboard showed yesterday's data and nobody noticed for hours
- Downstream jobs should not run until fresh data has landed
- You are setting up source freshness monitoring (dbt or otherwise)

## Not for this skill when
- The schema changed but data is fresh (schema drift, different check)
- Row counts dropped but timestamps look fine (volume anomaly, different check)
- You need sub-minute streaming lag monitoring (different tooling)

## Steps

1. Pick the freshness signal for each table: a timestamp column or the latest partition:

```sql
SELECT MAX(updated_at) AS latest, NOW() - MAX(updated_at) AS age
FROM orders;
```
Expected output: the newest timestamp and how old it is right now. If there is no reliable timestamp, the latest partition name serves the same purpose.

2. Turn it into a check with a threshold derived from the expected cadence plus margin:

```python
age = get_data_age("orders")  # from step 1
threshold = timedelta(minutes=90)  # hourly feed, 30 min of slack
if age > threshold:
    raise ValueError(f"orders is {age} old, expected under {threshold}")
```
Expected output: the check passes silently on normal runs and fails loudly with the actual age when data is stale.

3. Wire the check as the first task of every downstream DAG, so nothing consumes stale data URIs

```python
fresh = PythonOperator(task_id="check_orders_fresh", python_callable=check_freshness)
transform = MyOperator(task_id="transform", ...)
 fresh >> transform
```
Expected output: downstream tasks never run on stale input; the DAG fails fast at the check instead of producing quietly wrong output.

4. In dbt, declare it as source freshness so it runs with the standard tooling:

```yaml
sources:
  - name: raw
    tables:
      - name: orders
        freshness:
          warn_after: {count: 60, period: minute}
          error_after: {count: 90, period: minute}
        loaded_at_field: updated_at
```
Expected output: `dbt source freshness` reports warn and error states per table, visible in the same workflow as your models and tests.

5. Make the alert actionable: name the table, the actual age, the expected cadence, and the upstream job to check:

```text
orders is 4h old (expected under 90m). Upstream job: nightly_extract. Last success: 2026-10-03 22:10.
```
Expected output: the on-call person knows exactly where to look instead of starting from "the dashboard looks wrong."

## Variant phrasings

### stale data detection pipeline
Freshness checks are the standard mechanism. Run them on a schedule independent of the consuming pipelines so staleness is caught even when downstream is paused.

### dbt source freshness setup
Declare `freshness` with `warn_after` and `error_after` on each source table plus the `loaded_at_field`. Run `dbt source freshness` in CI or on a schedule; treat errors like failed tests.

### how fresh is my table right now
The step 1 query answers it directly. For a quick manual check across many tables, loop it over `information_schema` or your catalog and sort by age descending; the stalest tables float to the top.

## Why it happens
Upstream delays are the most common cause of "the dashboard is wrong," and they are silent: the pipeline runs fine, the SQL is correct, the data is just old. Nothing in a typical DAG validates timeliness, so staleness accumulates until a human notices. A freshness check converts that human vigilance into a query.

## Edge cases
- Backfills make old data look fresh by timestamp; check partition-level freshness when backfills are common.
- Business-day feeds have no fresh data on weekends by design; make thresholds calendar-aware or you will page yourself every Monday.
- The timestamp column's timezone must be known; comparing a UTC `now()` against a local-time column produces phantom staleness.
- Late partitions versus no data URIs distinguish "partition exists but is empty" from "partition missing" for better diagnostics.
- Freshness of the raw source and freshness of the derived marts are different checks; a fresh source with a broken transform still yields stale marts.

## Provenance

Resolved from the public thread: https://vectle.com/posts/pst_VGkZ17KorMPpI5qa9Z4Sag