freshness-sla-tracker

A data freshness monitor that learns each dataset's own arrival distribution instead of applying one fixed rule to everything, and raises 86% fewer false alarms than "alert if not loaded by 07:00" while detecting 99.4% of genuine lateness.

Every number below comes from a command run in this repository, with the raw output committed: docs/evaluation.json (head to head), docs/run_summary.json (incidents and trust scores), benchmark/results/results.json (performance) and docs/terminal_capture.txt (test run and coverage).

What this solves

• Freshness alerts that nobody reads. A single fixed deadline copied across every table fires constantly on the ones it does not fit. Replacing it with a per-dataset conditional quantile learned from that dataset's own arrival history cut false alarms from 6.04 to 0.84 a day across 47 datasets, a 86.0% reduction, while detection rose from 88.3% to 99.4%.
• Forty pages for one outage. When a source table is late, everything downstream of it is late too, and each one alerts separately. Walking the lineage DAG and attributing every late dataset to its topmost late ancestor collapsed 12 simultaneously late datasets into 4 incidents, suppressing 8 redundant alerts (66.7%) and naming the root cause in each.
• Silently stale dashboards. A chart gives no hint that one of the three tables behind it has not loaded. Mapping 13 dashboards onto the datasets they consume and scoring each from current freshness state produced trust scores from 56 to 100 with a plain-English caveat naming the late table and the part of the report it affects, emitted as JSON and an HTML badge a BI tool can embed.

Executive summary

A mid-size analytics team runs a few hundred tables and a few dozen reports on top of them. Freshness monitoring, if it exists at all, is a fixed deadline per table written by whoever built the pipeline. In a representative scenario, that rule fires around six times a day on tables that were never actually late. Within a month the alert channel is muted, and the outage that matters lands in the same channel as the noise. When something genuinely breaks upstream, the same rule fires on every table downstream of it, so a single supplier feed failure becomes a wall of pages that has to be read end to end before anyone can tell where it started. Meanwhile the business reads a regional sales chart that looks completely normal and is four hours out of date. If that scenario costs a ten person analytics team two hours a week triaging noise and one bad decision a quarter made on stale numbers, the fixed rule is not a monitoring strategy, it is a liability.

This repo learns the answer instead of configuring it. For each dataset it infers the delivery calendar from the arrival history itself (every hour, every day, Mon to Fri, business days with US federal holidays removed, or monthly on business day k), detects whether the schedule has permanently moved and refits on the most recent regime only, trims historic incidents out of the tail before estimating the tail, and publishes an alert threshold as an upper quantile of the conditional arrival distribution with a bootstrap uncertainty band. Late datasets are then collapsed through the lineage DAG into one incident per root cause, and that state is propagated forward into a per-dashboard trust score with an explicit, published scoring rule. Everything is quantiles, medians and counts, so every alert can be traced to a named calendar cell and an observation count. It runs on Python 3.11, DuckDB, numpy, pandas, scipy, sqlglot and jinja2, with zero cloud credentials; Snowflake and Azure SQL appear only as sqlglot transpile targets asserted in tests.

Measured on this repo's own seeded warehouse of 53 datasets, 47 lineage edges and 74,596 arrival events over 271 days: over a 45 day holdout that no model saw during fitting, covering 12,465 cycles from 47 hourly and daily datasets of which 171 were genuinely late, the learned SLAs produced a 0.309% false alarm rate at 99.42% detection (F1 0.897). The fixed 07:00 rule produced 2.212% and 88.30% (F1 0.508). A fixed rule tuned per cadence for best F1 and handed the correct delivery calendar for free still only managed 0.569% and 90.06% (F1 0.780). At scale, fitting 403,200 arrival events across 150 datasets took 12.4 seconds and the hourly check ran at 421 ms p50, on a shared 2 vCPU container.

Architecture

flowchart TB
    subgraph seed["Arrival history (DuckDB, local file)"]
        AE["arrival_event<br/>74,596 rows"]
        TL["truth_label<br/>ground truth, generator only"]
        CAT["dataset + lineage_edge + dashboard"]
    end

    subgraph fit["Nightly fit (freshness fit)"]
        CAL["1. infer delivery calendar<br/>scored by F1 on delivery days"]
        RG["2. detect regime change<br/>split and calendar effects fitted jointly"]
        TR["3. trim incidents out of the tail<br/>iterative outlier fence"]
        QT["4. conditional quantiles per calendar cell<br/>+ n/(n+k) shrinkage + bootstrap band"]
        CS{"5. enough history?"}
        MOD["dataset_model<br/>cached JSON, one row per dataset"]
    end

    subgraph check["Hourly check (freshness check)"]
        ST["state machine per dataset<br/>ok / pending / late / late_arrival / unknown"]
        LIN["lineage suppression<br/>one incident per root cause"]
        TRUST["dashboard trust score<br/>published rule, no magic number"]
    end

    subgraph out["Outputs"]
        EXIT["exit code<br/>non-zero if a critical dataset is late"]
        HTML["console.html + console.json"]
        BADGE["badges/*.json + *.html<br/>embeddable in Power BI or Tableau"]
        EVAL["evaluation.json<br/>head to head vs fixed thresholds"]
    end

    AE --> CAL --> RG --> TR --> QT --> CS
    CAT --> CAL
    CS -->|"below the grain-scaled floor"| COLD["cold start policy:<br/>loose cadence default,<br/>advisory only, never fails check"]
    CS -->|"above it"| MOD
    COLD --> MOD
    MOD --> ST
    AE --> ST
    ST --> LIN --> TRUST
    LIN --> EXIT
    TRUST --> HTML
    TRUST --> BADGE
    TL -.->|"never read by the model"| EVAL
    MOD --> EVAL

    classDef guard fill:#fff5f5,stroke:#b42318,stroke-width:2px,color:#1f2328;
    classDef edge fill:#f6f8fa,stroke:#0b5fff,stroke-width:2px,color:#1f2328;
    class COLD,CS guard;
    class LIN,EXIT edge;

Loading

Three boundaries carry the failure handling. Cold start (CS and COLD): below a grain-scaled minimum there is no learned SLA at all, and such a dataset can never raise a severity above advisory or fail freshness check. Lineage suppression (LIN): a late dataset with a late ancestor is never its own incident, so an upstream failure produces one page instead of a wall. Exit code (EXIT): the only thing that fails a pipeline is a critical dataset that is genuinely late under a learned model, which is the narrowest gate the system can offer.

Tech stack

Technology	Role here	Why chosen for this problem
DuckDB 1.5	Stores 74,596 arrival events, ground truth labels and the cached models in one local file	Freshness monitoring is a small analytical workload over a table that is millions of rows at most. DuckDB gives columnar aggregation and native TIMESTAMP WITH TIME ZONE with no server, so the whole system runs from a file and a pip install. A warehouse round trip per check would cost more than the check.
numpy + scipy	Quantile estimation, the bootstrap band, the segmented changepoint scan, Mann-Whitney significance	The changepoint scan evaluates every candidate split via per-cell prefix moments, which is a vectorised operation over 6,500 point series. scipy supplies the one statistical test used, and nothing else needed a dependency.
pandas 3.0	Groupby for calendar cells, time indexed joins between arrivals and truth labels	Calendar cell statistics are a groupby over a few hundred thousand rows. Interoperates with DuckDB's .df() with no serialisation step.
sqlglot 30	Transpiles the arrival-feature and lateness-rate queries to Snowflake, T-SQL and Postgres	The feature extraction is the one piece that would move into the warehouse in production. Writing it once in DuckDB dialect and asserting in tests that it still parses as Snowflake proves the portability claim without needing an account.
jinja2	Renders the single file HTML console and the embeddable trust badge	The badge has to be pasteable into a Power BI HTML tile, which means self contained markup with inline styles and no script tag. A template engine keeps that markup readable; a string concat would not.
rich	CLI tables for status, incidents, trust, evaluate	The CLI output is the interface an on-call engineer sees at 06:00. Aligned columns and severity colour are the difference between reading it and squinting at it.
Apache Airflow	Target orchestrator, DAG definition only, not a runtime dependency	The nightly fit and hourly check split maps onto two DAGs. The definition lives in integrations/airflow/; the callables live in the package, so there is one copy of the logic. See "what actually runs here" below.
matplotlib	Arrival distribution chart and benchmark chart	Both charts are static artefacts committed to the repo and embedded in this README, which is what matplotlib is for.
playwright (chromium)	Renders the HTML console to PNG headlessly at 1440x900	The console is a real HTML page, so the screenshot is of the actual artefact rather than a mock.

Quickstart

Prerequisites: Python 3.11 or newer, git, and about 200 MB of disk. No cloud account, no warehouse, no credentials, no network access after install.

git clone https://github.com/Sandeep0430/freshness-sla-tracker.git
cd freshness-sla-tracker

python -m venv .venv && source .venv/bin/activate
pip install -e ".[dev]"

# Generate 271 days of arrival history for 53 datasets, with ground truth labels
freshness seed

# Learn a conditional quantile SLA per dataset (regime changes are refitted)
freshness fit

# Current freshness state for every dataset
freshness status

# Root cause incidents, with the redundant downstream alerts rolled up
freshness incidents

# Trust score and plain-English caveat for one dashboard
freshness trust supplier_scorecard

# The headline: learned SLAs against three fixed threshold baselines
freshness evaluate

# Exits non-zero because a critical dataset is late in the seeded scenario
freshness check; echo "exit code: $?"

# Render artifacts/console.html plus the embeddable badges
freshness report

Everything at once, which is the same task sequence the Airflow DAG declares:

freshness run-local

Tests, lint and benchmark:

pytest --cov=src/freshness --cov-report=term    # 143 passed, 94% coverage
ruff check src tests benchmark
python benchmark/run_benchmark.py               # writes benchmark/results/

Docker, if you would rather not touch your Python:

docker compose build
docker compose run --rm freshness run-local

What actually runs here, and what is an Airflow definition

Being explicit, because this matters. What executes in this repo is freshness run-local (and every other CLI subcommand), which calls the task functions in src/freshness/pipeline.py in order. What is a definition only is integrations/airflow/freshness_dag.py. That file declares two DAGs, freshness_nightly_fit on 30 6 * * * and freshness_hourly_check on 15 * * * *, wires their dependencies and retries, and imports the very same callables from freshness.pipeline. It contains no business logic of its own. Apache Airflow is not a dependency of this package, it is not installed by pip install -e ., and the test suite never executes that DAG. What the suite does do is parse the DAG file and assert that every callable it imports from freshness.pipeline actually exists and is callable (tests/test_pipeline_cli.py::test_airflow_dag_only_calls_callables_that_exist), so the definition cannot silently rot.

Screenshots

The HTML console rendered headlessly with playwright chromium at 1440x900, from artifacts/console.html. Top row: 51 learned SLAs, 1 provisional, 1 in cold start, 12 datasets late right now, collapsed into 4 incidents with 66.7% of alerts suppressed. Middle: the 13 dashboard trust badges, each with the caveat that would be shown to the person reading the report. Bottom: incident INC-002, the root cause, with the six downstream alerts it absorbed listed by hop count rather than raised separately.

Left: 150 days of raw_survey_responses, a vendor feed whose median arrival is 468 minutes after the cycle start, that is 07:48 UTC. The shaded band is the learned expected window (p10 to p90) and the blue line is the learned alert threshold, which follows the day of week. The dashed red line is a fixed "loaded by 07:00 UTC" rule: all 144 undisturbed arrivals in the window sit above it, so that rule fires every single day, while the 6 genuinely late loads (X markers) are indistinguishable from the noise it generates. Centre: the same data as a distribution. Right: raw_crm_contacts, whose vendor moved the export window 134 minutes earlier on 1 January 2026. The changepoint scan found the move and the model refitted on the new regime only, rather than averaging a threshold across both.

Real terminal output, captured to docs/terminal_capture.txt. freshness check reports 12 late datasets collapsed into 4 incidents, prints the suppressed downstream alerts underneath their root cause, and exits with code 1 because three critical datasets are late. freshness evaluate prints the head to head. pytest --cov shows 143 passed and 94% total statement coverage.

Performance under load

Method: benchmark/run_benchmark.py generates synthetic warehouses (20% hourly grain, 80% daily, 2% injected incidents so the trimmer and changepoint scan do real work), writes them into a real DuckDB file, then times the actual fit and compute_status code paths. Fit is timed once per dataset; check is timed over 15 repeats; lineage suppression is timed over 15 repeats on layered DAGs of 512 all-late datasets. Container: 2 vCPU, 7.8 GB RAM, Linux 6.18, Python 3.11.15, DuckDB 1.5.5. The benchmark container is shared with other builds and variance was not controlled for, so these are order of magnitude figures, not a regression gate.

Scale	Datasets	History days	Arrival events	Fit wall (s)	Fit events/s	Fit p50/p95/p99 per dataset (ms)	Check p50/p95/p99 (ms)
small	15	60	5,040	0.65	7,753	21.1 / 137.3 / 150.6	34.1 / 62.6 / 70.7
medium	50	240	67,200	2.80	24,013	25.3 / 236.9 / 257.9	134.3 / 170.6 / 174.0
large	150	480	403,200	12.36	32,635	31.4 / 298.2 / 459.4	421.3 / 460.0 / 460.4

Lineage suppression, 512 datasets all late at once:

DAG depth	Incidents	Suppressed	Suppression %	p50 (ms)	p99 (ms)
2	256	256	50.0	4.1	6.5
4	128	384	75.0	4.2	10.0
8	64	448	87.5	5.7	6.4
16	32	480	93.8	9.8	12.3
32	16	496	96.9	15.7	20.6
64	8	504	98.4	16.7	23.1

Where it degrades: fit cost tracks total arrival events rather than dataset count, and the p99 per dataset (459 ms at the largest scale) is set entirely by the hourly datasets, which carry twenty four times the history of a daily one and populate up to 96 calendar cells each, so a warehouse that is mostly hourly feeds will see a nightly fit several times longer than one that is mostly daily. Full raw output in benchmark/results/.

Architecture Decision Records

• ADR 0001: Learned per-dataset quantile SLAs with regime detection. Why a conditional quantile per dataset beats both a fixed threshold and a forecasting library, argued from the measured false alarm comparison including a fixed baseline that was tuned and handed the correct calendar and still lost.
• ADR 0002: A nightly batch fit with cached models, not online updates. The boring choice: the distribution being estimated barely moves day to day, trimming and changepoint detection are not incremental, and a cached model is a row you can read when someone asks why they were paged.

Intentionally out of scope

• Row count and schema anomaly detection. This answers "did it arrive on time", not "did the right thing arrive". Add it when freshness alerts stop being the top source of data incidents, which is the point at which volume becomes the next signal worth learning.
• Real warehouse connectors. Arrival history comes from a generator here and would come from Snowflake INFORMATION_SCHEMA.LOAD_HISTORY, dbt run results or Airflow task instances in production. Add a connector when there is a specific warehouse to point at; the arrival-feature SQL already transpiles to Snowflake and T-SQL.
• Alert routing to PagerDuty or Slack. freshness check emits an exit code and incidents --json emits structured incidents, which is the correct integration surface. Add routing when there is an on-call rotation to route to.
• Contract comparison. The model learns what a dataset does, not what it promised. A table that has been an hour late for six months looks healthy. Add a declared SLA per dataset and diff learned against declared when a data contract process exists to supply the declared side.
• Multi-tenant or per-team model isolation. One catalog, one model store. Add isolation when two teams need different alert quantiles on the same table.

Security and compliance

• No credentials anywhere. The system reads a local DuckDB file. There is no connection string, no token, no cloud SDK. Snowflake and Azure SQL are sqlglot transpile targets asserted in tests, never runtime dependencies.
• Configuration is environment only. Every tunable lives in freshness.config.Config, is read from a FRESHNESS_* environment variable and is documented with a safe default in .env.example. Nothing downstream hardcodes a path, a threshold or a clock. A test asserts every variable the code reads is documented.
• Nothing sensitive is logged. Log lines carry a dataset id, a cycle timestamp, a count and a duration. No row values, no column names, no credentials, no file contents. The structured JSON logger emits a fixed set of fields plus explicitly passed extra keys, so a new log call cannot accidentally serialise an object graph.
• Run correlation without user data. Every log line carries a 12 character run_id (overridable with FRESHNESS_RUN_ID) and every CLI invocation writes one row to run_log with the command, timing and exit code. No user identity is captured.
• Least privilege by shape. In production this needs read access to load history metadata and nothing else: no access to the tables it monitors, and no write access to anything but its own model store. The container runs as an unprivileged user (uid 10001) with the data directory as the only writable volume.

Failure modes

Failure	Detection	Behaviour	Recovery
A dataset has too little history for a learned SLA	Observation count below a grain-scaled floor at fit time (10 daily cycles, 60 hourly, 3 monthly)	Status becomes insufficient_history, a loose cadence default threshold applies (480 min daily, 90 min hourly), severity is capped at advisory, and it can never fail freshness check	Resolves itself once enough cycles accumulate; the console shows the count so the gap is visible
A vendor permanently moves a schedule	Segmented changepoint scan at fit time, requiring statistical significance, a minimum effect size and a minimum reduction in unexplained variation	Model refits on the post-change segment only. The change date and the shift in minutes are recorded on the model and printed by freshness fit	Automatic on the next nightly fit
A schedule moves too recently to leave a fittable segment behind it	Shift watch compares the trailing window of calendar-adjusted residuals against everything before it	Threshold is widened by the observed shift, the model is downgraded to provisional and carries a note saying why, instead of paging every night	Automatic once the new regime has enough history for a full refit
An upstream source fails and takes its whole subtree with it	Every late dataset is checked for a late ancestor in the lineage DAG	Only the topmost late ancestor becomes an incident; descendants are listed underneath it with hop counts. 12 late datasets became 4 incidents in the seeded scenario	Fixing the root cause clears the incident and all suppressed alerts with it
The nightly fit fails or does not run	fitted_at is stored per model and surfaced in the console	freshness check keeps running against the previous night's cached models, which degrades correctly but silently	Rerun freshness fit. Alerting on a stale fitted_at is listed in future work, and named as an accepted weakness in ADR 0002
freshness check runs against a clock earlier than a dataset existed	Every model records first_cycle from its own arrival history	Cycles before the first arrival on record report unknown with the onboarding date, rather than late. This was a real bug, see below	Automatic; regression test pins it
The lineage graph contains a cycle	Kahn's algorithm on graph construction	LineageError naming the datasets involved, raised before any incident is built, so a bad catalog fails loudly rather than producing wrong suppression	Fix the catalog; topological_order() and LineageGraph.validate() both assert it
Historic incidents contaminate the quantile being estimated	Iterative outlier fence at median + 10 x (q75 - q50) before fitting	Contaminated points are trimmed and the trimmed percentage is recorded on the model. Without this the alert threshold lands inside the incident mass and detects nothing	Visible as trimmed_pct per model; a sudden jump means the dataset's behaviour has changed

Hardest problem solved

The hardest bug in this build was a daylight saving boundary eating a regime change. Two datasets in the seeded warehouse have vendors who permanently moved their export window mid-history, and two others are scheduled in America/New_York, so their UTC arrival time steps by an hour on the second Sunday of March. The first version of the changepoint detector did the obvious thing: subtract each cycle's calendar cell median to remove known seasonal effects, then run a changepoint test on the residuals. It reported that raw_crm_contacts changed regime on 11 March 2026 with a shift of +30 minutes. The truth was 1 January 2026 with a shift of -134 minutes. It had found the daylight saving boundary and missed the actual schedule move by ten weeks.

The root cause is a circularity that is invisible until you draw it. The residualisation subtracts a cell median, and the cell in question (standard time, weekday) straddled the regime boundary. Its median therefore sat halfway between the old and new arrival times, so inside that cell the residuals were bimodal rather than stepped, and the only clean step left in the series was the one at the daylight saving boundary, where the cell changed. Estimating the calendar effect before locating the changepoint requires already knowing where the changepoint is. Worse, a sequential fit would then trim the pre-regime points inside the daylight-saving cell as outliers, deleting the very evidence the detector needed.

The fix was to stop doing it in two steps. changepoint.py now scores every candidate split against a model that allows a separate mean per calendar cell on each side, computed for all splits at once from per-cell prefix moments, and takes the split that best reduces unexplained variation. A daylight saving move is already absorbed by the cell effects and so buys no improvement, while a genuine schedule move cannot be absorbed by any cell and wins by a wide margin. The effect size is then measured only on calendar cells present on both sides of the split, which is what makes the two cases distinguishable at all. Both are pinned by regression tests (test_a_pure_calendar_effect_is_not_a_regime_change and test_a_real_shift_is_still_found_when_a_calendar_cell_straddles_it), and the detector now reports 1 January 2026 at -134 minutes, which is what the generator injected.

A second, smaller bug came out of adversarial probing after the tests were green: running freshness check with the clock set to a historical instant raised critical incidents for two datasets that had not been onboarded yet, claiming each was sixteen hours past a learned SLA on a date when neither had produced a single row. The inferred delivery calendar is a pure calendar predicate with no notion of when a dataset started existing, so it happily produced an anchor from before the first arrival on record. In production that fires on every backfill, incident replay and CI run against a pinned clock, which is exactly the class of false alarm this project exists to remove. Fixed in 385acea by recording first_cycle on every model and reporting unknown for anything earlier.

Future work

• Alert when the newest fitted_at is older than 36 hours. This is the first metric to watch after deploying: a silently failed nightly fit degrades into checking against stale models, which is the correct behaviour but must not be quiet. Named as an accepted weakness in ADR 0002.
• Diff learned SLAs against declared ones. The model learns what a dataset does, not what it promised. Publishing "expected 07:20 to 09:40, contract says 06:00" would turn six months of quiet lateness into a visible conversation.
• Cap the history read at the detected regime start. Nothing before a regime change is used in the fit, so reading it is pure cost. This is the cheapest available win on fit time and it costs nothing in accuracy.
• Learn the propagation lag per lineage edge. Suppression currently uses the graph shape only. Knowing that fct_orders normally lands 12 minutes after stg_orders would let an incident predict when each downstream table will recover, which is the first question anyone asks after "what broke".
• Feed the trust score back into the BI layer as a gate. The badge is informational today. The next step is a scheduled refresh that refuses to run while the score is below a threshold, so a stale report is never published rather than published with a warning nobody reads.