To backtest a time-series forecasting model across regions with rolling windows, monthly metrics, holiday/missing-data handling, and retraining cadence driven by drift, use this approach:1) Approach summary:- Produce region-level time series, impute/flag gaps, include holiday indicators.- Use rolling-origin (sliding) windows for train/validate/test.- For each fold generate forecasts, aggregate monthly per-region RMSE/MAE.- Monitor drift (forecast error distribution + population/stats drift) and trigger retrain when thresholds exceeded.SQL: extract and prepare data with holiday join and window partitioningsql
-- per-day series per region with holiday flag
WITH base AS (
SELECT
date,
region_id,
metric,
CASE WHEN h.date IS NOT NULL THEN 1 ELSE 0 END AS is_holiday
FROM metrics_table m
LEFT JOIN holidays h ON m.date = h.date
),
filled AS (
-- ensure continuous dates per region using a calendar table
SELECT c.date, r.region_id,
COALESCE(b.metric, NULL) AS metric,
COALESCE(b.is_holiday, 0) AS is_holiday
FROM calendar c
CROSS JOIN (SELECT DISTINCT region_id FROM base) r
LEFT JOIN base b ON b.date = c.date AND b.region_id = r.region_id
)
SELECT * FROM filled;
python
import pandas as pd
from sklearn.metrics import mean_squared_error, mean_absolute_error
from sklearn.ensemble import GradientBoostingRegressor
from holidays import CountryHolidays
import numpy as np
# params
train_window = pd.Timedelta(days=365)
horizon = pd.Timedelta(days=30)
step = pd.Timedelta(days=30)
drift_threshold_rmse = 1.2 # ratio vs baseline
min_retrain_interval_days = 14
def impute_and_features(df):
# forward-fill short gaps, flag long gaps
df['gap_len'] = df['metric'].isna().astype(int).groupby(df['region_id']).cumsum()
df['metric'] = df.groupby('region_id')['metric'].apply(lambda s: s.interpolate(limit=7).fillna(method='ffill').fillna(0))
# lag/rolling features
df['lag_1'] = df.groupby('region_id')['metric'].shift(1)
df['r7_mean'] = df.groupby('region_id')['metric'].transform(lambda s: s.rolling(7).mean())
# holiday flag
# assume is_holiday present
return df.dropna(subset=['lag_1'])
def backtest(df):
results = []
regions = df['region_id'].unique()
for region in regions:
s = df[df.region_id==region].set_index('date').sort_index()
start = s.index.min() + train_window
while start + horizon <= s.index.max():
train_start = start - train_window
train = s.loc[train_start:start - pd.Timedelta(days=1)]
test = s.loc[start:start + horizon - pd.Timedelta(days=1)]
train = impute_and_features(train.reset_index())
test = impute_and_features(test.reset_index())
X_train = train[['lag_1','r7_mean','is_holiday']]; y_train = train['metric']
X_test = test[['lag_1','r7_mean','is_holiday']]; y_test = test['metric']
model = GradientBoostingRegressor().fit(X_train, y_train)
pred = model.predict(X_test)
# monthly aggregation
test['pred'] = pred
test['month'] = test['date'].dt.to_period('M')
agg = test.groupby('month').apply(lambda g: pd.Series({
'rmse': mean_squared_error(g.metric, g.pred, squared=False),
'mae': mean_absolute_error(g.metric, g.pred, )
}))
agg['region'] = region
results.append(agg.reset_index())
start += step
return pd.concat(results)
def monitor_and_decide_retrain(metric_history, baseline_rmse):
latest = metric_history.sort_values('month').iloc[-1]
if latest.rmse > baseline_rmse * drift_threshold_rmse:
return True
# statistical drift test example (KL or population shift) simplified:
if np.abs(metric_history['metric_mean'].pct_change().iloc[-1]) > 0.2:
return True
return False
