Time-series forecasting: AR / ARIMA
Train on earlier observations, forecast seven later periods, and compare predictions with held-out values.
The practical problem and model
An operations team wants a short-horizon revenue forecast to plan capacity. Unlike an ordinary shuffled training table, time-series data has an ordering: using future observations during fitting would invalidate the forecast exercise.
An autoregressive model predicts from previous observations. ARIMA adds differencing and moving-average terms. This example uses p = 7, d = 1, and q = 0: seven autoregressive lags, one differencing step, and no moving-average term. Seven lags alone are not a declaration of a seasonal model.
The first 21 daily observations are used for training, and days 22–28 are held out. The model function's argument is a forecast horizon, not a new revenue feature.
Before you run
Complete the shared setup. This walkthrough uses synthetic data and recreates its tutorial objects. Use a sandbox tenant or schema, and run the steps in order.
Studio: Tutorial: Time-Series Forecasting (AR / ARIMA). Download the complete SQL.
Step 1: Create an ordered revenue series
There are 28 observations with an explicit day index. Retaining that order is essential to the model.
CREATE CATALOG IF NOT EXISTS tutorial;
CREATE SCHEMA IF NOT EXISTS tutorial.forecast;
CREATE OR REPLACE TABLE tutorial.forecast.daily_revenue (
day_idx INT,
revenue DOUBLE
);
INSERT INTO tutorial.forecast.daily_revenue VALUES
(1, 100.0), (2, 105.0), (3, 102.0), (4, 110.0), (5, 115.0), (6, 108.0), (7, 95.0),
(8, 112.0), (9, 118.0), (10, 116.0), (11, 124.0), (12, 130.0), (13, 122.0), (14, 109.0),
(15, 128.0), (16, 134.0), (17, 132.0), (18, 140.0), (19, 146.0), (20, 138.0), (21, 125.0),
(22, 144.0), (23, 150.0), (24, 148.0), (25, 156.0), (26, 162.0), (27, 154.0), (28, 141.0);
Step 2: Fit only the earlier observations
The WHERE clause excludes the last seven days and the ORDER BY makes the temporal ordering explicit. Engine metadata may identify the common implementation family as ar for the arima alias.
CREATE OR REPLACE MODEL tutorial.forecast.revenue_arima
(DOUBLE)
RETURNS DOUBLE
TYPE 'arima'
OPTIONS (p = 7, d = 1, q = 0)
AS SELECT revenue AS label FROM tutorial.forecast.daily_revenue
WHERE day_idx <= 21 ORDER BY day_idx;
EXPLAIN MODEL tutorial.forecast.revenue_arima;
Step 3: Forecast the holdout interval
day_idx - 21 requests horizons one through seven. The error column is actual minus predicted, so a positive value means underprediction.
SELECT day_idx AS forecast_day,
tutorial.forecast.revenue_arima(day_idx - 21) AS predicted_revenue
FROM tutorial.forecast.daily_revenue
WHERE day_idx > 21 ORDER BY day_idx;
SELECT day_idx, revenue AS actual,
tutorial.forecast.revenue_arima(day_idx - 21) AS predicted,
revenue - tutorial.forecast.revenue_arima(day_idx - 21) AS error
FROM tutorial.forecast.daily_revenue
WHERE day_idx > 21 ORDER BY day_idx;
What to check in the results
Both forecast queries return seven ordered rows for days 22–28. In the recorded run, day 22 was approximately 134.94398 versus an actual 144, and day 28 approximately 139.10469 versus 141.
Check the subtraction in every error row and ensure no training query includes days 22–28. The predictions are useful for learning the horizon and ordering contract; the fixture is too short to establish stable forecasting quality.
Adapt it to real data
Regularize the sampling interval, define how missing periods are handled, and keep feature availability aligned with forecast time. Compare against naive and seasonal-naive forecasts using rolling-origin evaluation rather than random train/test splits.
Evaluate several forecast horizons and inspect systematic under- or overprediction. Changes in pricing, campaigns, or demand can make historical relationships less useful even when the SQL remains valid.