Table of Contents
- Why Forecasting Matters for Data Engineers
- What Makes Time Series Different
- The Four Components — Trend, Seasonality, Cycles, and Noise
- Our Example Dataset — Three Years of Store Sales
- Decomposition — Pulling the Series Apart
- Baselines First — The Models You Must Beat
- Exponential Smoothing and Holt-Winters
- Stationarity and ARIMA
- SARIMA — ARIMA with Seasons
- Machine Learning for Forecasting — Lag and Calendar Features
- The Results — Which Model Won
- The Leakage Trap — Why Random Splits Lie
- Walk-Forward Backtesting — Validating Like Time Moves
- Forecast Metrics — MAE, RMSE, MAPE, and the Zero Problem
- Prediction Intervals — Forecasting a Range, Not a Number
- Prophet and Deep Learning — When Are They Worth It
- Forecasting Thousands of Series with Spark
- A Production Forecasting Pipeline
- Common Mistakes
- Interview Questions
- Wrapping Up
Every business runs on forecasts. How many units to stock next month, how many agents to staff in the call centre on Monday, how much cloud spend to budget for next quarter, how large tomorrow’s data load will be. Forecasting is the most common machine learning task data engineers meet in practice — and the one where it is easiest to fool yourself, because time series break the assumptions that every other model in this series relies on.
This post builds forecasting skills from the ground up on one realistic dataset: three years of daily store sales. We decompose it, set baselines, fit exponential smoothing, SARIMA, and XGBoost, compare them honestly, show exactly how a random train/test split inflates results, validate with walk-forward backtesting, and scale to thousands of stores with Spark. Every number in this post comes from actually running the code.
Analogy — A restaurant owner ordering tomorrow’s ingredients. An experienced owner does not order the same amount every day. They know Saturdays are busy (weekly seasonality), the business has grown 20% since last year (trend), patios fill up in summer (yearly seasonality), December is party season (a special period), and some days are just randomly quiet (noise). Without writing a formula, they combine “same day last week,” “same time last year,” and “how things have been going lately” into one order. Time series forecasting is the disciplined, measurable version of that instinct.
Why Forecasting Matters for Data Engineers
Forecasting problems you will meet:
Retail and supply chain Demand per product per store, inventory, replenishment
Operations Call centre volumes, staffing, delivery capacity
Finance Revenue, cash flow, cloud and infrastructure cost
Energy and utilities Electricity load, gas demand, solar output
Data platforms Daily row counts, storage growth, Fabric capacity
usage, pipeline runtimes (for anomaly alerts)
What you typically own as a data engineer:
Clean, complete daily (or hourly) history -- no missing dates
Calendar and event tables: holidays, promotions, store openings
Feature pipelines: lags and rolling windows computed without leakage
Scheduled forecasting jobs across thousands of series
Forecast tables for planners and BI, with history for accuracy trackingA useful side effect: once you can forecast your own pipeline metrics (rows loaded per day, job runtimes), you can alert when today’s actual value falls far outside the forecast range — a simple, powerful anomaly detector for your data platform.
What Makes Time Series Different
Classical machine learning assumes rows are independent: shuffling the training data changes nothing. Time series violate that assumption completely.
Ordinary ML (customer churn):
Row order is irrelevant. Customer A's row tells you nothing about
customer B's row. Random train/test splits are fine.
Time series (daily sales):
Today's sales are strongly related to yesterday's, last week's, and
last year's. Order IS the information. Shuffling destroys it.
Consequences:
1. You must split by time: train on the past, test on the future
2. Features come from the series' own history (lags, rolling averages)
3. Every feature must be available at the moment you forecast
4. Validation must move forward through time (backtesting)
5. The horizon matters: forecasting tomorrow is easier than next monthAnalogy — Reading a novel versus a deck of shuffled index cards. A pile of customer records is like index cards: shuffle them and nothing is lost. A time series is a novel: each page builds on the previous ones. Shuffle the pages and the story becomes meaningless — and worse, a model trained on shuffled pages may “predict” chapter 10 because it has already read chapter 11.
The Four Components — Trend, Seasonality, Cycles, and Noise
TREND The long-term direction: growth, decline, or flat.
Example: a store's sales growing as the neighbourhood develops.
SEASONALITY Repeating patterns with a FIXED period.
Weekly: weekends busier than weekdays
Yearly: summer peak, December holiday rush
Daily: lunchtime and evening peaks (hourly data)
CYCLES Rises and falls WITHOUT a fixed period.
Example: economic expansions and recessions lasting 2–10 years.
(Often confused with seasonality -- the difference is the
fixed, known length.)
NOISE Random variation no model can predict.
Example: a rainy Tuesday, a local event, a data entry error.Analogy — The ocean. The tide slowly rises over hours (trend). Waves arrive in a regular rhythm (seasonality). Storm swells come and go irregularly over days (cycles). And ripples from the wind are pure randomness (noise). Forecasting means predicting the tide and the waves well — and accepting that nobody can predict individual ripples.
Our Example Dataset — Three Years of Store Sales
To keep every result reproducible, we generate three years of daily sales for one store with the patterns real retail data shows: steady growth, busy Fridays and weekends, a summer peak, a December ramp-up, and random noise.
import numpy as np
import pandas as pd
rng = np.random.default_rng(42)
dates = pd.date_range("2023-01-01", "2025-12-31", freq="D")
t = np.arange(len(dates))
trend = 200 + 0.08 * t # slow growth
weekly = np.select(
[dates.dayofweek == 5, dates.dayofweek == 6, dates.dayofweek == 4],
[60, 45, 25], default=0 # Sat, Sun, Fri busier
)
yearly = 40 * np.sin(2 * np.pi * (dates.dayofyear - 80) / 365.25) # summer peak
december = np.where(dates.month == 12, 35 * (dates.day / 31), 0) # holiday ramp-up
noise = rng.normal(0, 12, len(dates))
df = pd.DataFrame({"date": dates, "sales": (trend + weekly + yearly + december + noise).round(1)})
print(len(df), df.sales.iloc[0], df.sales.iloc[-1], round(df.sales.mean(), 1))
# 1096 209.6 298.0 263.6The forecasting task:
Training history: 2023-01-01 to 2025-12-03 (1,068 days)
Forecast horizon: 28 days
Test window: 2025-12-04 to 2025-12-31 (the December holiday period)
Why December as the test window: it is the hardest and most valuable
period to forecast -- exactly where a weak model gets exposed.Decomposition — Pulling the Series Apart
Before modelling, look at the components. STL decomposition (Seasonal-Trend decomposition using Loess) splits a series into trend, seasonal, and remainder parts.
from statsmodels.tsa.seasonal import STL
series = df.set_index("date")["sales"]
result = STL(series, period=7, robust=True).fit()
weekly_effect = result.seasonal.groupby(series.index.dayofweek).mean().round(1)
print(weekly_effect.to_dict())
print(round(result.resid.std(), 1))Weekly seasonal effect recovered by STL (Mon = 0):
Mon -18.3
Tue -19.4
Wed -19.1
Thu -17.9
Fri +7.0
Sat +41.3 ← busiest day
Sun +26.5
Remainder (noise) standard deviation: 10.3 units per day
Monthly averages show the yearly pattern:
Jan 213.9 Apr 272.8 Jul 299.6 Oct 251.2
Feb 229.1 May 289.0 Aug 286.6 Nov 242.7
Mar 251.4 Jun 299.5 Sep 271.4 Dec 253.7 ← December liftSTL found the weekly pattern without being told which days were busy: Saturday about 41 units above an average day, Monday to Thursday about 18 below. (With period=7, the slower yearly cycle is absorbed into the trend line; decomposing yearly patterns needs a longer period or a second pass.)
Analogy — Unmixing a smoothie. You taste a smoothie and want to know how much is banana, how much is strawberry, and how much is ice. Decomposition is the lab test that separates the ingredients, so you can model each one with the right tool.
Baselines First — The Models You Must Beat
A forecasting model is only useful if it beats simple rules. Always compute baselines before anything sophisticated.
from sklearn.metrics import mean_absolute_error, mean_squared_error
H = 28
train, test = df.iloc[:-H], df.iloc[-H:]
def metrics(actual, predicted):
actual, predicted = np.asarray(actual), np.asarray(predicted)
mae = mean_absolute_error(actual, predicted)
rmse = np.sqrt(mean_squared_error(actual, predicted))
mape = np.mean(np.abs((actual - predicted) / actual)) * 100
return round(mae, 1), round(rmse, 1), round(mape, 1)
# 1. Naive: tomorrow = today (repeat the last value)
naive = np.repeat(train.sales.iloc[-1], H)
# 2. Seasonal naive: same weekday last week
seasonal_naive = np.tile(train.sales.iloc[-7:].to_numpy(), H // 7)
# 3. Moving average: average of the last 28 days
moving_avg = np.repeat(train.sales.iloc[-28:].mean(), H)
for name, pred in [("naive", naive), ("seasonal naive", seasonal_naive), ("moving average", moving_avg)]:
print(name, metrics(test.sales, pred))Baseline results on the December test window:
Model MAE RMSE MAPE
───── ─── ──── ────
Naive (last value) 32.2 41.4 10.6%
Moving average (last 28 days) 24.6 33.0 8.1%
Seasonal naive (same weekday last week) 19.0 23.4 6.5%The seasonal naive forecast — literally “copy last week” — is already decent because it captures the weekly rhythm for free. Any model that cannot beat 19.0 MAE here is not worth deploying.
Analogy — “Tomorrow’s weather will be like today’s.” That simple rule is right surprisingly often, which is why meteorologists measure their skill against it. A forecaster who cannot beat “same as today” is adding complexity, not value.
Exponential Smoothing and Holt-Winters
Simple exponential smoothing forecasts a weighted average of past values, with weights that shrink exponentially the further back you go. Recent days matter most.
Analogy — Your opinion of a restaurant. Last week’s excellent meal shapes your opinion more than a disappointing visit two years ago, but that old visit still counts a little. The smoothing parameter (alpha) controls how quickly old memories fade: a high alpha means you mostly trust your last visit; a low alpha means you weigh your long history.
The family (each adds one component):
Simple exponential smoothing Level only Flat forecast
Holt's linear method Level + trend Straight sloped line
Holt-Winters Level + trend + Sloped line with a
seasonality repeating pattern
Additive vs multiplicative seasonality:
Additive: Saturdays are always ~40 units above average
Multiplicative: Saturdays are always ~15% above average
(use this when seasonal swings grow with the level)from statsmodels.tsa.holtwinters import ExponentialSmoothing
hw = ExponentialSmoothing(
train.sales.to_numpy(), trend="add", seasonal="add", seasonal_periods=7
).fit()
print(metrics(test.sales, hw.forecast(H)))
# (25.6, 29.9, 8.7)Holt-Winters scored worse than seasonal naive here (25.6 vs 19.0 MAE). That is not a bug — it is an important lesson. We told it about trend and the weekly season, but nothing about the December ramp-up. It projected an ordinary late-November week forward and missed the holiday lift. A model can only learn patterns it can see: if the yearly effect matters for your horizon, the model needs yearly information.
Stationarity and ARIMA
Many classical models assume the series is stationary: its average and variability do not change over time. A growing series is not stationary — its average keeps rising.
Making a series stationary with differencing:
Original: 200, 203, 205, 210, 214
Differenced: 3, 2, 5, 4 ← day-to-day changes
Growth disappears; the changes hover around a stable average.
The Augmented Dickey-Fuller (ADF) test checks stationarity formally.
ARIMA(p, d, q) combines three ideas:
AR(p) AutoRegressive Today depends on the previous p values
"If sales were high yesterday, today tends high"
I(d) Integrated Difference the series d times to remove trend
MA(q) Moving Average Today depends on the previous q forecast ERRORS
"If yesterday's forecast was too low, adjust up"Analogy — A basketball player’s free throws. AR is “how did my last few shots go?” — a hot streak tends to continue. MA is “how far off was my last miss, and in which direction?” — you correct for the error you just made. Differencing (I) is judging improvement game to game rather than career totals, so the player’s long-term improvement does not distort the picture.
SARIMA — ARIMA with Seasons
SARIMA adds a second, seasonal set of AR, differencing, and MA terms that look back one full season (7 days for weekly data).
from statsmodels.tsa.statespace.sarimax import SARIMAX
# order=(p, d, q), seasonal_order=(P, D, Q, season_length)
sarima = SARIMAX(
train.sales.to_numpy(), order=(1, 1, 1), seasonal_order=(1, 1, 1, 7)
).fit(disp=False)
print(metrics(test.sales, sarima.forecast(H)))
# (18.4, 22.3, 6.3)SARIMA(1,1,1)(1,1,1,7) reading guide:
(1,1,1) Use yesterday's value, difference once, use yesterday's error
(1,1,1,7) Do the same with the value and error from 7 days ago,
with one seasonal difference
Result: MAE 18.4 -- slightly better than seasonal naive (19.0).
Practical notes:
Fitting is sequential and slow on long series or many series
Choosing (p,d,q)(P,D,Q) is usually done by searching on AIC or with
backtesting; auto_arima tools automate the search
SARIMAX also accepts external regressors (the "X"), such as promotions
or holidaysMachine Learning for Forecasting — Lag and Calendar Features
A gradient boosting model like XGBoost does not understand time natively. You turn forecasting into a regular regression problem by creating features from the series’ history and the calendar.
Analogy — Driving with a rear-view mirror and a calendar. You cannot see the road ahead, but you can see where you have been (lags), how fast you have been going on average (rolling means), and what day it is (calendar). From those, you estimate what is around the next bend.
from xgboost import XGBRegressor
H = 28 # forecast horizon
def make_features(frame, horizon=H):
f = frame.copy()
# Calendar features: always known in advance
f["dayofweek"] = f.date.dt.dayofweek
f["month"] = f.date.dt.month
f["dayofyear"] = f.date.dt.dayofyear
f["day"] = f.date.dt.day
f["is_weekend"] = (f.dayofweek >= 5).astype(int)
f["t"] = np.arange(len(f)) # time index for trend
# Lag features: only values at least `horizon` days old
for lag in [horizon, horizon + 7, 364]:
f[f"lag_{lag}"] = f.sales.shift(lag)
f["rolling_mean_28"] = f.sales.shift(horizon).rolling(28).mean()
return f
FEATURES = ["dayofweek", "month", "dayofyear", "day", "is_weekend", "t",
"lag_28", "lag_35", "lag_364", "rolling_mean_28"]
feat = make_features(df).dropna()
train_f = feat[feat.date < test.date.min()]
test_f = feat[feat.date >= test.date.min()]
model = XGBRegressor(n_estimators=500, learning_rate=0.05, max_depth=4,
subsample=0.8, random_state=42)
model.fit(train_f[FEATURES], train_f.sales)
print(metrics(test_f.sales, model.predict(test_f[FEATURES])))
# (17.4, 21.6, 5.9)The most important rule: lags must respect the horizon.
We forecast 28 days ahead. On the morning we make the forecast, the
most recent value we know is TODAY'S. For a date 28 days out, "lag_1"
(yesterday's sales) does not exist yet. So every lag feature here is
at least 28 days old: lag_28, lag_35, lag_364, and a rolling mean that
ends 28 days back. This is called DIRECT forecasting.
(The alternative, RECURSIVE forecasting, predicts day 1, feeds that
prediction back in as a lag to predict day 2, and so on. It can use
short lags but errors compound over the horizon.)
Feature importance from the trained model:
lag_364 0.675 ← same day last year: captures December
lag_28 0.094
lag_35 0.051
t 0.047
dayofyear 0.038The model leaned heavily on lag_364 — the same day last year — which is exactly the information Holt-Winters was missing. It saw what happened last December and expected a similar holiday lift.
The Results — Which Model Won
| Model | MAE | RMSE | MAPE | Notes |
|---|---|---|---|---|
| Naive (last value) | 32.2 | 41.4 | 10.6% | Ignores every pattern |
| Holt-Winters (trend + weekly) | 25.6 | 29.9 | 8.7% | Missed the December lift |
| Moving average (28 days) | 24.6 | 33.0 | 8.1% | Flat line, no weekly shape |
| Seasonal naive (last week) | 19.0 | 23.4 | 6.5% | The baseline to beat |
| SARIMA(1,1,1)(1,1,1,7) | 18.4 | 22.3 | 6.3% | Small win over baseline |
| XGBoost + lag/calendar features | 17.4 | 21.6 | 5.9% | Best, using last year’s December |
How to read this honestly:
XGBoost beat the seasonal naive baseline by 1.6 units of MAE (about 8%).
That is a real improvement, but not a miracle -- and it is from ONE
28-day window. Before trusting it, check it across several windows
(backtesting, below).The Leakage Trap — Why Random Splits Lie
What if we had used the usual train_test_split with shuffling, as we would for a churn model?
from sklearn.model_selection import train_test_split
X_tr, X_te, y_tr, y_te = train_test_split(feat[FEATURES], feat.sales,
test_size=0.1, random_state=42)
leaky = XGBRegressor(n_estimators=500, learning_rate=0.05, max_depth=4,
subsample=0.8, random_state=42).fit(X_tr, y_tr)
print(round(mean_absolute_error(y_te, leaky.predict(X_te)), 1))
# 11.0 ← looks 37% better than the honest 17.4Why the random split looks so much better:
Test days are scattered among training days. To predict Dec 15, the
model trained on Dec 14 and Dec 16 of the same year -- information from
the future that will never be available in real forecasting.
The honest number is 17.4. The leaky number is 11.0.
A team that reports 11.0 will promise accuracy it can never deliver.Analogy — Taking the exam with tomorrow’s newspaper. Predicting the stock market is easy if you have already read tomorrow’s paper. A random split hands the model pages from the future, so its exam score says nothing about how it will perform when the future is genuinely unknown. The same trap applies to features: a rolling mean that includes the target day, or a “monthly total” computed after the month ended, quietly leaks the answer. (Our model evaluation guide covers leakage more broadly.)
Walk-Forward Backtesting — Validating Like Time Moves
One test window can be lucky or unlucky. Walk-forward backtesting replays history: train on everything before a cutoff, forecast the next horizon, move the cutoff forward, repeat.
fold_results = []
for k in range(4, 0, -1): # 4 folds of 28 days each
cutoff = df.date.iloc[-k * H]
tr = feat[feat.date < cutoff]
te = feat[(feat.date >= cutoff) & (feat.date < cutoff + pd.Timedelta(days=H))]
m = XGBRegressor(n_estimators=500, learning_rate=0.05, max_depth=4,
subsample=0.8, random_state=42).fit(tr[FEATURES], tr.sales)
xgb_mae = mean_absolute_error(te.sales, m.predict(te[FEATURES]))
last_week = df[df.date < cutoff].sales.iloc[-7:].to_numpy()
naive_mae = mean_absolute_error(te.sales, np.tile(last_week, H // 7))
fold_results.append((cutoff.date(), round(xgb_mae, 1), round(naive_mae, 1)))Walk-forward results (each fold forecasts the next 28 days):
Train until Forecast window XGBoost MAE Seasonal naive MAE
─────────── ─────────────── ─────────── ──────────────────
2025-09-10 Sep 11 – Oct 8 10.1 13.5
2025-10-08 Oct 9 – Nov 5 11.5 15.9
2025-11-05 Nov 6 – Dec 3 13.8 12.5 ← naive wins
2025-12-03 Dec 4 – Dec 31 17.4 19.0
Average: 13.2 15.2
XGBoost wins 3 of 4 folds and about 13% on average.
The November fold shows it is not better every time -- exactly the
kind of evidence a single test window would hide.
Folding pattern:
Fold 1: [=====train=====][test]
Fold 2: [======train======][test]
Fold 3: [=======train=======][test]
Fold 4: [========train========][test]
Training always ends before testing begins. Nothing from the future leaks.Analogy — Practice exams taken in date order. You study material up to week 4, then take the week 5 quiz. Then study up to week 5 and take the week 6 quiz. Your average quiz score is an honest estimate of how you will do on the final — and a bad week tells you where you are weak. scikit-learn’s TimeSeriesSplit implements the same idea for cross-validation.
Forecast Metrics — MAE, RMSE, MAPE, and the Zero Problem
MAE Mean Absolute Error
Average size of the error in the original units.
"On average we are off by 17 units a day." Easy to explain.
RMSE Root Mean Squared Error
Squares errors before averaging, so big misses count much more.
Use when one large miss is far worse than several small ones
(running out of stock on the busiest day).
MAPE Mean Absolute Percentage Error
Average error as a percentage of the actual value.
"We are off by about 6%." Easy for business audiences.
The zero problem with MAPE:
Actual Predicted Absolute error Percentage error
────── ───────── ────────────── ────────────────
2 3 1 50%
1 2 1 100%
0.5 1.5 1 200%
100 101 1 1%
MAE = 1.0 (every miss is one unit -- an honest summary)
MAPE = 87.8% (dominated by the tiny actual values)
And if any actual is exactly 0, MAPE divides by zero entirely.Analogy — Being one minute late. One minute late to a two-hour movie is nothing. One minute late to a two-minute train connection means you missed it. Percentage errors explode for tiny values the same way. For series with low or zero values (slow-moving products, overnight hours), prefer MAE or WAPE (total absolute error divided by total actual), which stays stable.
Which metric to report:
Planners and BI dashboards MAE (units) or WAPE (percent, zero-safe)
When big misses are costly RMSE alongside MAE
Comparing series of different WAPE or MASE (scaled by a naive
sizes baseline's error)
Avoid as the only metric MAPE on low-volume or zero-heavy dataPrediction Intervals — Forecasting a Range, Not a Number
A single number hides uncertainty. Planners need a range.
Point forecast: "We will sell 290 units on Dec 20."
Interval forecast: "We will sell 290 units, and 80% of the time the
actual will fall between 270 and 310."
How intervals are produced:
Statistical models (SARIMA, ETS) Built-in: forecast intervals from the
model's estimated error variance
Gradient boosting Quantile regression: train separate
models for the 10th and 90th
percentiles
Any model Empirical: use backtest errors to
build the range around the forecast
How planners use them:
Inventory Stock to the upper bound for must-not-run-out items
Staffing Schedule for the middle, keep on-call staff for the upper
Budgeting Plan to the middle, reserve for the upper boundAnalogy — “70% chance of rain.” A weather app that only said “it will rain” would be less useful than one that says “70% chance.” You decide whether to carry an umbrella based on the probability, not a yes/no. Forecast intervals give businesses the same choice.
Prophet and Deep Learning — When Are They Worth It
PROPHET (open source, from Meta)
Fits trend + multiple seasonalities + holiday effects with a simple API.
Strengths: Handles missing days and outliers well, easy holiday
effects, readable components, quick for business series
Weaknesses: Often beaten by well-tuned gradient boosting or
statistical models in benchmarks; not ideal for very
short histories
Typical usage:
from prophet import Prophet
m = Prophet() # weekly + yearly by default
m.fit(df.rename(columns={"date": "ds", "sales": "y"}))
future = m.make_future_dataframe(periods=28)
forecast = m.predict(future)[["ds", "yhat", "yhat_lower", "yhat_upper"]]
DEEP LEARNING (LSTMs, temporal transformers, foundation models)
Worth considering when you have:
Thousands of related series that share patterns
Long, rich histories and many external signals
A team able to operate GPU training and tuning
Recent pretrained "time series foundation models" can forecast new
series with little or no training -- promising, but always benchmark
them against seasonal naive and XGBoost on YOUR data.
The practical order to try:
1. Seasonal naive (baseline)
2. Exponential smoothing / SARIMA
3. Gradient boosting with lag + calendar features
4. Prophet, deep learning, or foundation models if 1–3 fall shortThe neural networks post explains the LSTM and transformer architectures these deep learning forecasters are built on.
Forecasting Thousands of Series with Spark
A retailer with 500 stores and 2,000 products has a million series. You do not forecast them one by one in a notebook loop. Spark’s applyInPandas runs one model per series, in parallel across the cluster.
Analogy — A tailor for every customer. One-size-fits-all clothing (a single global model) fits nobody perfectly. Spark lets you hire thousands of tailors at once, each fitting one customer (one store’s series) with a model made just for them, all working in parallel.
import numpy as np
import pandas as pd
from statsmodels.tsa.holtwinters import ExponentialSmoothing
HORIZON = 28
def forecast_store(pdf: pd.DataFrame) -> pd.DataFrame:
"""Fit one model for one store and return the next 28 days."""
pdf = pdf.sort_values("date")
store_id = pdf["store_id"].iloc[0]
sales = pdf["sales"].to_numpy(dtype=float)
try:
model = ExponentialSmoothing(sales, trend="add", seasonal="add",
seasonal_periods=7).fit()
forecast, method = model.forecast(HORIZON), "holt_winters"
except Exception:
# Short or unusual history: fall back to "repeat last week"
forecast, method = np.tile(sales[-7:], HORIZON // 7), "seasonal_naive"
last_date = pd.to_datetime(pdf["date"]).max()
future_dates = pd.date_range(last_date + pd.Timedelta(days=1), periods=HORIZON, freq="D")
return pd.DataFrame({
"store_id": store_id,
"forecast_date": future_dates.date,
"forecast_sales": np.round(forecast, 1),
"method": method,
})
schema = "store_id string, forecast_date date, forecast_sales double, method string"
forecasts = (
spark.table("retail.silver.daily_store_sales")
.select("store_id", "date", "sales")
.groupBy("store_id")
.applyInPandas(forecast_store, schema=schema)
)
forecasts.write.mode("overwrite").saveAsTable("retail.gold.store_forecasts")What the function guarantees (tested on sample stores):
A store with a full year of history → 28 Holt-Winters forecasts
A store open only 12 days → model cannot fit two weekly
seasons, so it falls back to
seasonal naive instead of
crashing the whole job
Production tips:
Fallbacks per series One bad series must never fail the job
Record the method Know which series used a fallback
Partition sensibly Thousands of small groups run well; a few huge
groups become stragglers
Fill missing dates Insert zero-sales days for closed days or the
weekly pattern shifts
Log to MLflow Track backtest error per segment, not just overallA Production Forecasting Pipeline
A weekly forecasting job (Lakeflow Jobs or a Fabric pipeline):
1. Complete the history Build a full date spine per series; fill
gaps; flag closed days and outages
2. Join calendar data Holidays, promotions, paydays, school
breaks, store openings
3. Build features Lags and rolling windows that respect the
horizon -- no leakage
4. Backtest Walk-forward error per model per segment;
compare against seasonal naive
5. Forecast Best model per segment, with fallbacks and
prediction intervals
6. Publish Append to a forecasts table with run date,
horizon, model, and intervals
7. Measure accuracy When actuals arrive, join to past forecasts
and track MAE/WAPE over time; alert on
degradationKeeping every past forecast (step 6) is what makes step 7 possible: you can answer “how accurate were our forecasts made 4 weeks ahead, for the last 6 months?” — the question planners always ask. Our MLOps guide covers tracking and monitoring these models.
Common Mistakes
Using a random train/test split. Shuffling lets the model learn from days after the ones it predicts. In our example, a random split reported MAE 11.0 against an honest 17.4. Always split by time and validate with walk-forward backtesting.
Using lags you will not have at forecast time. A
lag_1feature is perfect for forecasting tomorrow and useless for forecasting 28 days out, because yesterday’s value for that future date does not exist yet. Every lag and rolling window must be at least as old as the forecast horizon (or use recursive forecasting deliberately).Skipping baselines. Without a seasonal naive benchmark, a model with 8.7% MAPE looks good — even though “copy last week” scored 6.5%. Every forecasting report should show the baseline next to the model.
Judging a model on one test window. A single month can be easy or unusually hard. Our XGBoost model lost one of four backtest folds. Backtest across several windows and report the average and the spread.
Reporting only MAPE. MAPE explodes for small actual values and is undefined at zero. Use MAE or WAPE for low-volume series, and add RMSE when large misses are costly.
Missing dates in the history. If closed days are simply absent rather than recorded as zero, “7 rows back” is no longer “same weekday last week” and every weekly pattern shifts. Build a complete date spine before computing features.
Ignoring holidays and events. Models that cannot see December, Black Friday, or promotions will forecast them as ordinary days — as Holt-Winters did here. Add calendar features, holiday flags, and last year’s same-day value.
Not storing past forecasts. Overwriting the forecast table each run makes it impossible to measure real accuracy later. Append forecasts with the run date and horizon, then compare against actuals as they arrive.
Interview Questions
Q: How is time series forecasting different from regular regression? A: Observations are ordered and dependent: today’s value is related to recent and seasonal past values. That means you must split data by time rather than randomly, build features from the series’ own history, ensure every feature is available at forecast time, and validate with walk-forward backtesting. The forecast horizon also matters, because accuracy usually falls as you forecast further ahead.
Q: What are trend, seasonality, cycles, and noise? A: Trend is the long-term direction. Seasonality is a repeating pattern with a fixed period, such as weekly or yearly. Cycles are rises and falls without a fixed period, such as economic cycles. Noise is random variation that cannot be predicted. Decomposition methods like STL separate these components so each can be modelled appropriately.
Q: Why should you always compute a baseline forecast? A: Simple baselines like naive or seasonal naive are surprisingly strong because they capture recent level and seasonality for free. A sophisticated model is only valuable if it clearly beats them. In our example, Holt-Winters looked reasonable at 8.7% MAPE but was worse than simply copying last week’s values at 6.5%.
Q: Explain ARIMA and SARIMA. A: ARIMA(p, d, q) models a series using its own past p values (autoregression), d rounds of differencing to remove trend and make it stationary, and the past q forecast errors (moving average). SARIMA adds a seasonal set of the same terms that look back one full season, such as 7 days for weekly patterns. SARIMAX also allows external regressors like holidays or promotions.
Q: How do you use XGBoost for forecasting? A: Turn the series into a supervised learning table with lag features, rolling statistics, and calendar features, then train a regressor. The critical rule is that lags and rolling windows must only use values that will be known at forecast time — for a 28-day direct forecast, lags of at least 28 days. Validate with time-based splits and backtesting.
Q: What is walk-forward validation? A: You repeatedly train on all data before a cutoff and evaluate on the following horizon, then move the cutoff forward and repeat. It mimics how the model will actually be used, prevents future information leaking into training, and shows how stable performance is across different periods rather than relying on one test window.
Q: When is MAPE a bad metric? A: When actual values are small or zero. Percentage errors become huge for tiny actuals and undefined at zero, so a few low-volume days can dominate the score. MAE, WAPE, or scaled metrics like MASE are more reliable in those cases.
Q: How would you forecast sales for 50,000 store-product combinations? A: Use Spark to parallelize one model per series with groupBy(...).applyInPandas(...), or train a single global gradient-boosting model with series identifiers and shared features. Include fallbacks so short or irregular series never fail the job, build complete date spines, backtest by segment against seasonal naive, store every forecast with its run date, and monitor accuracy as actuals arrive.
Wrapping Up
Time series forecasting rewards discipline more than clever algorithms. Respect the order of time: split by date, build only features you will truly have at forecast time, and validate by walking forward. Start with baselines — in our example, “copy last week” beat a Holt-Winters model that could not see December — and only keep a model that beats them consistently across backtest windows. Our final XGBoost model won by using one powerful piece of history, the same day last year, and it still lost one fold out of four: honest evidence beats a single impressive number.
For data engineers, the hard parts are familiar: complete date spines, clean calendars and event tables, leak-free feature pipelines, parallel scoring across thousands of series, and a forecast history table that lets the business measure accuracy over time.
Related posts: – XGBoost and Gradient Boosting – Feature Engineering – Model Evaluation – Hyperparameter Tuning – Neural Networks and Deep Learning – MLOps with MLflow – PySpark Window Functions
Naveen Vuppula is a Senior Data Engineer and app developer based in Ontario, Canada. He writes about Python, SQL, AWS, Azure, and everything data engineering at DriveDataScience.com.