Monitoring Freshness, Volume and Data SLOs

Scenario

Example company: a partner sends a daily product-catalogue file to an SFTP folder. After the partner changes their export tool, the file still arrives, but empty (only the header row).

What you see in production

• The pipeline reports success seven nights in a row; it loaded 0 rows each time.
• The catalogue in the warehouse is a week old, and pricing analyses use stale prices.
• The problem is found when a customer complains about a product that no longer exists.

The naive code

rows = read_csv(path)       # an empty file is valid CSV
load(rows)                  # loading 0 rows is "success"

Reproduce it

Three monitors watch the table: freshness (age of the newest data), volume (rows per load versus history) and job status.

loads = [("Mon", 51_200), ("Tue", 50_900), ("Wed", 0), ("Thu", 0), ("Fri", 0)]
freshness_slo_hours = 26

last_good_day, history = None, []
for day_index, (day, rows) in enumerate(loads):
    job = "success"
    alerts = []
    if history:
        avg = sum(history) / len(history)
        if rows < 0.5 * avg:
            alerts.append(f"volume: {rows} rows vs avg {avg:.0f}")
    if rows > 0:
        last_good_day = day_index
        history.append(rows)
    age_hours = (day_index - last_good_day) * 24 + 2
    if age_hours > freshness_slo_hours:
        alerts.append(f"freshness: newest data {age_hours}h old (SLO {freshness_slo_hours}h)")
    print(f"{day}: job={job:<8} rows={rows:<6} alerts={alerts if alerts else 'none'}")

Mon: job=success  rows=51200  alerts=none
Tue: job=success  rows=50900  alerts=none
Wed: job=success  rows=0      alerts=['volume: 0 rows vs avg 51050']
Thu: job=success  rows=0      alerts=['volume: 0 rows vs avg 51050', 'freshness: newest data 50h old (SLO 26h)']
Fri: job=success  rows=0      alerts=['volume: 0 rows vs avg 51050', 'freshness: newest data 74h old (SLO 26h)']

Root cause

Monitoring watched jobs, not data. Zero rows, partial files and stale sources are valid inputs from the code's point of view, so they never raise errors.

The fix

Define and monitor data SLOs for important tables:

• Freshness: the newest record's timestamp (or last successful load) is no older than X hours.
• Volume: rows per load within an expected band compared with recent history (and with day-of-week patterns).
• Completeness and distribution: null rates, distinct counts and key metrics within expected ranges.
• Schema: alert on changes (previous lesson).

Then make alerts actionable: route them to a named owner per dataset, link a runbook, and block publishing for critical failures. Inputs should also fail loudly: reject empty files or files with too few rows unless explicitly expected.

Data observability tools (and dbt source freshness checks) automate much of this, but the targets must come from the people who use the data.

Trade-offs

• Static thresholds are noisy for seasonal data; use day-of-week baselines or anomaly detection, and tune after false alarms.
• Blocking publication protects consumers but means stale-yet-consistent data; communicate status on dashboards ("data delayed").

Detect it in production

• A status page or dashboard banner showing each key table's freshness against its SLO.
• Weekly review of alert noise and of incidents that monitors missed.

Key points

• Job success is not data success: monitor freshness, volume and distributions.
• Set data SLOs with the consumers and assign an owner per dataset.
• Make inputs fail loudly (empty or tiny files) and block publishing on critical failures.