Forecasting Daily DB Spend with Holt-Winters

This page fits a Holt-Winters exponential smoothing model to a daily database-spend series with additive trend and weekly seasonality, derives a prediction interval from the forecast residuals, and flags any actual day that lands outside that interval as an anomaly.

Back to: Cost Anomaly Detection

Holt-Winters takes a fundamentally different stance from a threshold detector. Instead of asking “how far is today from the recent average?”, it forecasts what today should cost given the series’ trend and its weekly rhythm, then asks whether the actual falls within a plausible band around that forecast. That distinction is the whole reason to reach for it: a plain rolling z-score fires on every Monday ramp because it has no notion of the week, whereas Holt-Winters learns that Mondays are expensive and only flags a Monday that is expensive beyond its usual pattern. It consumes the same normalized daily series described in the parent cost anomaly detection section, and the anomalies it emits flow to alert routing and escalation exactly as the threshold detector’s do — the two are interchangeable at the event boundary and differ only in how the expectation is formed.

The additive Holt-Winters model decomposes each observation into a level, a trend, and a seasonal component, each updated by its own smoothing parameter:

$$\hat{y}{t+h} = \ell_t + h,b_t + s$$

where $\ell_t$ is the level, $b_t$ the trend, and $s$ the seasonal term over a period of $m = 7$ days for a weekly cycle. Additive (rather than multiplicative) seasonality is the right default for cost that swings by a roughly constant dollar amount each weekend rather than by a constant percentage.

Prerequisites

  • Python: 3.10 or newer.

  • Libraries: statsmodels supplies the model; pandas shapes the series.

    pip install statsmodels pandas

  • Input: a gapless, timezone-aware daily spend series for one attribution key with at least two full seasonal cycles of history — for a weekly period that means 14 days minimum, but 8-plus weeks makes the seasonal estimate stable. This deep history is exactly what batch processing for historical metrics exists to materialize.

  • Data hygiene: statsmodels raises on NaN, so missing days must be filled or dropped before fitting — apply the gap policy from the parent section, never pass a raw reindexed series.

  • Permissions: none beyond read access to the stored series; this is pure computation over already-extracted, already-validated data.

Step-by-Step Implementation

Step 1 — Fit an additive Holt-Winters model

ExponentialSmoothing with trend="add" and seasonal="add" and seasonal_periods=7 fits the level, trend, and seven daily seasonal offsets. Leaving the smoothing parameters unset lets statsmodels optimize them by maximum likelihood.

import pandas as pd
from statsmodels.tsa.holtwinters import ExponentialSmoothing

def fit_holt_winters(history: pd.Series):
    """Fit additive Holt-Winters with a weekly (7-day) seasonal period."""
    # statsmodels wants a plain float series with a frequency-bearing index
    y = history.astype(float).asfreq("1D")
    if y.isna().any():
        raise ValueError("fill or drop missing days before fitting")
    model = ExponentialSmoothing(
        y,
        trend="add",
        seasonal="add",
        seasonal_periods=7,
        initialization_method="estimated",
    )
    return model.fit(optimized=True)

fit = fit_holt_winters(train)
print("SSE:", round(fit.sse, 1))
print("alpha/beta/gamma:",
      round(fit.params["smoothing_level"], 3),
      round(fit.params["smoothing_trend"], 3),
      round(fit.params["smoothing_seasonal"], 3))

Expected output:

SSE: 486210.4
alpha/beta/gamma: 0.412 0.031 0.187

A small beta (trend smoothing) means the model sees a fairly stable trend; the non-trivial gamma confirms a real weekly seasonal signal worth modelling.

Step 2 — Forecast and build a prediction interval

statsmodels’ ExponentialSmoothing gives a point forecast but not a built-in interval, so derive one from the in-sample residual spread. The residual standard deviation, scaled by a z-multiplier, sets a symmetric band around each forecast point.

import numpy as np

def forecast_with_interval(fit, horizon: int, z: float = 3.0) -> pd.DataFrame:
    """Point forecast plus a +/- z-sigma prediction interval from residual spread."""
    point = fit.forecast(horizon)
    resid_sigma = np.std(fit.resid, ddof=1)     # in-sample residual spread
    margin = z * resid_sigma
    return pd.DataFrame({
        "forecast": point,
        "lower": point - margin,
        "upper": point + margin,
    })

pred = forecast_with_interval(fit, horizon=7, z=3.0)
print(pred.round(2))

Expected output for a 7-day horizon:

                           forecast    lower    upper
2026-07-13 00:00:00+00:00   1042.11   799.34  1284.88
2026-07-14 00:00:00+00:00   1305.60  1062.83  1548.37
2026-07-15 00:00:00+00:00   1298.44  1055.67  1541.21
2026-07-16 00:00:00+00:00   1310.02  1067.25  1552.79
2026-07-17 00:00:00+00:00   1287.73  1044.96  1530.50
2026-07-18 00:00:00+00:00    905.18   662.41  1147.95
2026-07-19 00:00:00+00:00    720.44   477.67   963.21

Notice the model has learned the week: the forecast dips on 2026-07-18 and 2026-07-19 (a weekend) and rises midweek. A flat detector could not have produced that shape, and would flag the weekday values as high and miss a weekend anomaly.

Step 3 — Score actuals against the interval and emit events

An actual outside its forecast band is an anomaly. The sign of the breach matters — above upper is a spend spike, below lower is an unexpected drop that can signal a stalled workload or a data-collection gap worth its own investigation.

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          # signed distance beyond the band, in dollars
    method: str
    detected_at: datetime

def score_actuals(actual: pd.Series, pred: pd.DataFrame, cost_center: str) -> list[AnomalyEvent]:
    """Emit an event for each actual day falling outside the prediction interval."""
    joined = pred.join(actual.rename("actual"), how="inner").dropna(subset=["actual"])
    now = datetime.now(timezone.utc)
    events = []
    for ts, r in joined.iterrows():
        if r["actual"] > r["upper"]:
            dev = r["actual"] - r["upper"]
        elif r["actual"] < r["lower"]:
            dev = r["actual"] - r["lower"]     # negative for a drop
        else:
            continue
        events.append(AnomalyEvent(
            cost_center=cost_center,
            period=ts.date().isoformat(),
            observed=Decimal(str(round(r["actual"], 2))),
            expected=Decimal(str(round(r["forecast"], 2))),
            deviation=round(float(dev), 2),
            method="holt_winters",
            detected_at=now,
        ))
    return events

# Suppose Monday 2026-07-14 actually cost 1980.00 against a forecast band top of 1548.37
events = score_actuals(actual, pred, "data-platform")
for e in events:
    print(e.period, "observed", e.observed, "expected", e.expected, "over by", e.deviation)

Expected output:

2026-07-14 observed 1980.00 expected 1305.60 over by 431.63

The Monday was expected to be expensive — the forecast was already 1305.60 — but 1980.00 breached the upper band by $431.63, so it fires. A plain z-score would have blamed the weekday pattern; Holt-Winters correctly attributes the surprise to the excess over an already-elevated Monday expectation.

The forecast-then-compare flow makes the difference from a threshold detector concrete.

Holt-Winters detection: fit level/trend/seasonality, forecast an interval, and flag actuals outside the bandHistorical daily spend is fit into a Holt-Winters model with level, trend, and weekly seasonal components. The model produces a forecast with a prediction interval. Each actual is compared to its interval; values inside the band are normal, values above the upper or below the lower bound are emitted as anomaly events.outside bandHistoricaldaily spendHolt-Winters fitlevel + trend + seasonForecast +intervalActual inband?Anomalyevent

Verification

Validate the model the way you would any forecaster: hold out the most recent window, forecast it, and confirm the fitted band brackets the held-out actuals on a clean series while a planted spike breaks out of it.

train, holdout = series.iloc[:-7], series.iloc[-7:]
fit = fit_holt_winters(train)
pred = forecast_with_interval(fit, horizon=7, z=3.0)

# a clean holdout should fall entirely inside the band
inside = ((holdout >= pred["lower"]) & (holdout <= pred["upper"])).all()
assert inside, "model under-fits: clean holdout escaped the interval"

# a planted +50% spike on the last day must be flagged
spiked = holdout.copy(); spiked.iloc[-1] *= 1.5
assert score_actuals(spiked, pred, "test"), "spike missed"
print("OK: brackets clean holdout, flags planted spike")

Expected output:

OK: brackets clean holdout, flags planted spike

A fired event has the same {cost_center, period, observed, expected, deviation, method} shape as every other detector’s, so it drops into the routing layer unchanged. Reconcile observed against the closed-period billing figure for that date before escalating.

Gotchas & Edge Cases

  • Too little history. With fewer than two full weeks, statsmodels cannot estimate a weekly season and raises or returns a degenerate fit. Require at least 14 days, and prefer 8-plus weeks; until then, fall back to the rolling z-score.
  • Additive vs multiplicative seasonality. Additive suits spend that swings by a constant dollar amount; if weekend spend is a constant fraction of weekday spend and the overall level is climbing, switch to seasonal="mul". Multiplicative fitting fails on any non-positive value, so a series with credit-driven negatives must use additive.
  • NaN in the series. statsmodels raises ValueError on missing values. The gap policy must run before fitting — never let a reindexed series with holes reach ExponentialSmoothing.
  • A one-off spike poisons the fit. Holt-Winters fits to all history including past anomalies, so a huge past spike widens the residual sigma and desensitizes the interval. Clean known incidents out of the training window, or fit on a robustly winsorized series.
  • Refitting cost. Re-optimizing the model every day on years of history is wasteful. Refit on a schedule (weekly) and reuse the fitted model to score intervening days, or cap the training window to a rolling year.
  • Interval from residuals understates far-horizon uncertainty. A flat residual-sigma band does not widen with horizon the way a true predictive interval does. Forecast only a short horizon (a week) and refit frequently, or use statsmodels’ state-space ETSModel, which yields horizon-aware get_prediction intervals. Feed whichever you choose into alert routing and escalation with a severity keyed on how far outside the band the actual landed.

Frequently Asked Questions

When is Holt-Winters the right choice over a rolling z-score?

When the series has a strong, repeating weekly (or other fixed-period) pattern that a flat baseline keeps flagging. Holt-Winters models that pattern explicitly, so it only fires on deviations beyond the expected cycle. On a series without clear seasonality, the simpler rolling z-score is easier to reason about and needs no training history.

Should I use additive or multiplicative seasonality for cost?

Start additive. Database spend usually swings by a roughly constant dollar amount each weekend, which additive captures, and additive tolerates the zero and negative values that credits introduce. Switch to multiplicative only when the seasonal swing scales with the overall level and the series is strictly positive.

How much history does the model need?

At least two full seasonal cycles to estimate the season at all — 14 days for a weekly period — but the seasonal offsets are noisy until you have roughly eight weeks. The long backfill from batch processing for historical metrics is what makes a stable fit possible.

Why does statsmodels not give me a prediction interval directly?

The classic ExponentialSmoothing.forecast returns point forecasts only. This page derives a symmetric interval from the in-sample residual spread, which is adequate for a short horizon. For horizon-aware intervals that widen with distance, use the state-space ETSModel and its get_prediction(...).conf_int().

How do I keep past anomalies from desensitizing the model?

Because the fit minimizes error over all training data, a large past spike inflates the residual sigma and widens every future interval, hiding smaller subsequent anomalies. Remove or winsorize known incidents in the training window before fitting, and refit on a rolling window so old spikes eventually age out.

Back to: Cost Anomaly Detection