Detecting Cost Spikes with a Rolling Z-Score

This page builds a threshold-based spike detector for a daily database-spend series using a trailing rolling mean and standard deviation in pandas, then hardens it against its own worst failure mode with a robust median/MAD variant.

Back to: Cost Anomaly Detection

A rolling z-score answers one precise question: how many standard deviations is today’s spend from the recent trailing average? It needs no training run, no model to persist, and no seasonality assumptions — which makes it the right first detector for a spend series that is roughly flat or slowly trending without a strong weekly rhythm. It consumes the same normalized daily series produced by the broader cost anomaly detection layer, and when it fires it emits the structured event that gets handed to alert routing and escalation. Where a series has strong weekday/weekend structure, prefer the forecast-based approach in forecasting daily DB spend with Holt-Winters instead — a plain z-score will flag every Monday.

The classic z-score standardizes a point against the mean and standard deviation of a trailing window of length $w$:

$$z_t = \frac{x_t - \mu_{t-1,w}}{\sigma_{t-1,w}}$$

The window ends at $t-1$, not $t$: the point being scored must never be part of the statistics it is scored against, or a large spike inflates its own denominator and hides itself. A point is flagged when $|z_t| \ge k$, with $k$ typically 3 to 4 for daily cost.

Prerequisites

  • Python: 3.10 or newer.

  • Libraries: pandas and numpy only — no modelling framework is required.

    pip install "pandas>=2.1" "numpy>=1.26"

  • Input: a gapless, timezone-aware daily spend series for a single attribution key (one cost center or database), already past the validation and normalization boundary described in the parent section. This detector assumes missing days have been filled or flagged upstream; a raw NaN will silently null out the rolling statistics around it.

  • Permissions: none beyond read access to wherever the normalized series is stored. This step is pure computation over an already-extracted series, so no cloud billing credentials are needed here — that scoping is covered under security and access control for cost data.

Step-by-Step Implementation

Step 1 — Compute a trailing rolling z-score

The two decisions that matter are the trailing window and the shift. Using .shift(1) before the rolling aggregation guarantees the statistics for day $t$ are built only from days strictly before $t$.

import numpy as np
import pandas as pd

def rolling_zscore(series: pd.Series, window: int = 28, min_periods: int = 14) -> pd.DataFrame:
    """Score each day against the mean/std of the *preceding* `window` days."""
    prior = series.shift(1)                     # exclude the point being scored
    mean = prior.rolling(window, min_periods=min_periods).mean()
    std = prior.rolling(window, min_periods=min_periods).std(ddof=0)
    z = (series - mean) / std.replace(0.0, np.nan)   # guard a flat window
    return pd.DataFrame({"spend": series, "mean": mean, "std": std, "zscore": z})

# A 28-day window tracks a monthly cost rhythm; min_periods=14 lets scoring
# begin two weeks in rather than waiting for a full window.

Expected output for a series that jumps on 2026-07-14:

                            spend     mean       std   zscore
2026-07-12 00:00:00+00:00  1285.40  1291.06   22.71   -0.249
2026-07-13 00:00:00+00:00  1302.90  1290.740  22.31    0.545
2026-07-14 00:00:00+00:00  1840.55  1291.02   22.05   24.92
2026-07-15 00:00:00+00:00  1310.20  1311.99   99.42   -0.018

The spike on 2026-07-14 scores far above any sane k. Note the day after: the spike has now entered the trailing window and inflated std from ~22 to ~99, which is exactly the contamination the robust variant in Step 3 removes.

Step 2 — Threshold and emit anomaly events

Scoring produces a number; detection produces events. This step applies the threshold and materializes the structured AnomalyEvent shape the routing layer consumes.

from dataclasses import dataclass
from datetime import datetime, timezone
from decimal import Decimal

@dataclass(frozen=True)
class AnomalyEvent:
    cost_center: str
    period: str
    observed: Decimal
    expected: Decimal
    deviation: float          # the z-score
    method: str
    detected_at: datetime

def detect_spikes(scored: pd.DataFrame, cost_center: str, k: float = 4.0) -> list[AnomalyEvent]:
    """Emit one event per day whose |z-score| meets the threshold."""
    hits = scored[scored["zscore"].abs() >= k].dropna(subset=["zscore"])
    now = datetime.now(timezone.utc)
    return [
        AnomalyEvent(
            cost_center=cost_center,
            period=ts.date().isoformat(),
            observed=Decimal(str(round(row.spend, 2))),
            expected=Decimal(str(round(row["mean"], 2))),
            deviation=round(float(row.zscore), 2),
            method="rolling_zscore",
            detected_at=now,
        )
        for ts, row in hits.iterrows()
    ]

events = detect_spikes(rolling_zscore(series), "data-platform", k=4.0)
for e in events:
    print(e.period, e.observed, "vs", e.expected, "z=", e.deviation)

Expected output:

2026-07-14 1840.55 vs 1291.02 z= 24.92

Step 3 — Add a robust median/MAD variant

The standard z-score has a self-defeating flaw: the mean and standard deviation it depends on are themselves wrecked by outliers. One spike in the trailing window drags the mean up and the std way up, so the next genuine spike scores lower and can slip under k — a single anomaly masks the following one. The robust variant swaps the mean for the median and the standard deviation for the Median Absolute Deviation (MAD), both of which barely move when a fraction of the window is contaminated.

The MAD is the median of absolute deviations from the median, scaled by 1.4826 so it estimates the standard deviation for normally distributed data:

$$\text{MAD} = 1.4826 \cdot \operatorname{median}\big(|x_i - \operatorname{median}(x)|\big), \qquad z^{\text{rob}}t = \frac{x_t - \operatorname{median}{t-1,w}}{\text{MAD}_{t-1,w}}$$

def _mad(window_vals: np.ndarray) -> float:
    """Scaled median absolute deviation of a 1-D window."""
    med = np.median(window_vals)
    return 1.4826 * np.median(np.abs(window_vals - med))

def robust_zscore(series: pd.Series, window: int = 28, min_periods: int = 14) -> pd.DataFrame:
    """Median/MAD z-score: resists outliers already inside the trailing window."""
    prior = series.shift(1)
    med = prior.rolling(window, min_periods=min_periods).median()
    mad = prior.rolling(window, min_periods=min_periods).apply(_mad, raw=True)
    z = (series - med) / mad.replace(0.0, np.nan)
    return pd.DataFrame({"spend": series, "median": med, "mad": mad, "zscore": z})

Expected output for the same series, on the day after the spike:

                            spend   median     mad   zscore
2026-07-14 00:00:00+00:00  1840.55  1289.55   26.98   20.42
2026-07-15 00:00:00+00:00  1310.20  1290.20   27.14    0.74

Compare 2026-07-15 across the two methods: the classic z-score’s std had ballooned to ~99, but the robust mad stays near 27 because the median ignored the single spike. A genuine second spike on the 15th would still be caught by the robust detector while the classic one would understate it.

The decision between the two detectors comes down to how the window is expected to behave once an anomaly lands in it.

Classic mean/std versus robust median/MAD rolling z-score, and how each behaves when the trailing window is already contaminatedA trailing window feeds two branches. The classic branch uses mean and standard deviation, which a prior in-window outlier distorts, masking the next spike. The robust branch uses median and scaled MAD, which resist the outlier so the next spike is still detected. Both converge on emitting an anomaly event when the absolute score meets the threshold k.TrailingwindowClassic: mean & stdin-window outlier distorts → masks nextRobust: median & MADresists outlier → next still caughtEmit eventif |z| ≥ k

Verification

Prove the detector fires on a known spike and stays silent on a clean series before trusting it in production. A synthetic series with an injected spike is the fastest assertion:

import numpy as np, pandas as pd

rng = pd.date_range("2026-06-01", periods=60, freq="1D", tz="UTC")
clean = pd.Series(1300 + np.random.default_rng(0).normal(0, 20, 60), index=rng)
spiked = clean.copy()
spiked.iloc[45] += 600                       # inject a +600 spike

assert detect_spikes(rolling_zscore(spiked), "test", k=4.0), "spike missed"
assert not detect_spikes(rolling_zscore(clean), "test", k=4.0), "false positive"
print("OK: fired on spike, silent on clean series")

Expected output:

OK: fired on spike, silent on clean series

A fired event carries the shape {cost_center, period, observed, expected, deviation, method} — reconcile observed against the closed-period figure in your billing export for that date before escalating.

Gotchas & Edge Cases

  • Scoring an open billing period. A partial current-day total is smaller than the trailing mean and can even fire a negative z-score “dip”. Only score closed periods; the settlement lag is covered in the parent cost anomaly detection section.
  • A flat window produces std == 0. A perfectly steady series divides by zero. The code coerces a zero std/MAD to NaN so those days are skipped rather than emitting inf — but a persistently flat series may need an absolute floor on the denominator so a tiny real change is not scored as infinite.
  • Window too short. A 7-day window adapts so fast that a 3-day plateau at a new level is absorbed into the mean and stops firing by day four — the detector “gets used to” the leak. Use a longer window plus a cumulative check for slow drifts.
  • Missing days passed through raw. A NaN in the input nulls the rolling mean and std for every window that spans it. Enforce the gap policy from normalization upstream; this detector assumes a filled series.
  • Standard z-score after a real spike. As shown in Step 3, one spike contaminates the next fortnight of std. If your series has recurring spikes, use the robust variant by default — its resistance is not a nicety but the difference between catching a second incident and missing it.
  • Threshold reused across series. A k tuned on a smooth production database over-fires on a spiky dev environment. Tune k per series, and feed the resulting events into alert routing and escalation so severity is set centrally rather than baked into each detector.

Frequently Asked Questions

What window length and k should I start with?

A 28-day window with k = 4 is a sound default for a daily database-spend series: the window spans a monthly rhythm and k = 4 keeps false positives rare. Lower k toward 3 if you would rather investigate a few false alarms than miss a moderate spike, and shorten the window only if your spend genuinely changes level often.

When should I use the robust median/MAD variant over the classic one?

Use the robust variant whenever the series has ever contained a spike, which is to say almost always. Its only real cost is the .apply for the MAD being slower than vectorized mean/std, which is negligible at daily granularity. The classic z-score is fine for a quick exploratory pass or a series you know is spike-free.

Why exclude the current point from its own window with shift(1)?

Because a large spike included in its own statistics inflates the mean and standard deviation it is measured against, shrinking its z-score and potentially hiding it under the threshold. Ending the trailing window at $t-1$ keeps the point being judged out of the jury.

Does the rolling z-score handle weekly seasonality?

No — that is its main limitation. A plain rolling window treats a routine Monday ramp as a deviation and will fire on it. If your weekday and weekend spend differ materially, use Holt-Winters forecasting, which models the weekly cycle explicitly.

How do I stop the same multi-day spike paging every night?

Emit events with a stable dedup key derived from the cost center, period, and method, and let the routing layer suppress an already-acknowledged incident. The detector’s job is to report each anomalous day faithfully; deduplication and escalation belong downstream.

Back to: Cost Anomaly Detection