Time-series forecasting in BigQuery

BigQuery forecasts time series two ways. AI.FORECAST calls the built-in TimesFM model with no training. ARIMA_PLUS trains a model per series and exposes trend, seasonality and holidays. We ran both on the same data to show where they differ.

BigQuery has two forecasting paths. AI.FORECAST sends your series to TimesFM, a pretrained model, and returns the forecast in one query with nothing to train or store. CREATE MODEL ... model_type = 'ARIMA_PLUS' trains a statistical model on each series, which you then query with ML.FORECAST and take apart with ML.EXPLAIN_FORECAST. On a clean series with a repeating pattern, ARIMA_PLUS tracked the pattern closely and TimesFM was looser. TimesFM needs no training step, which is its advantage. Both are billed on the bytes your input query reads, so a forecast over inline data costs nothing.

AI.FORECAST in one query

The function takes a table or a query, the name of the value column and the name of the timestamp column. It returns horizon rows (10 by default, up to 10,000) with a median forecast and a prediction interval. The example is a made-up daily orders series: 35 days with a +2 per day trend, and 30 extra orders on two days out of every seven.

-- 35 days of orders: +2 a day, and +30 on Saturdays and Sundays
SELECT
  forecast_timestamp,
  ROUND(forecast_value, 1) AS forecast,
  ROUND(prediction_interval_lower_bound, 1) AS lower,
  ROUND(prediction_interval_upper_bound, 1) AS upper
FROM AI.FORECAST(
  (SELECT TIMESTAMP_ADD(TIMESTAMP '2026-09-01', INTERVAL d DAY) AS day,
          100 + 2 * d + IF(MOD(d, 7) IN (5, 6), 30, 0) AS orders
   FROM UNNEST(GENERATE_ARRAY(0, 34)) AS d),
  data_col => 'orders',
  timestamp_col => 'day',
  horizon => 7)
ORDER BY forecast_timestamp

The true values for these seven days are 170, 172, 174, 176, 178, 210 and 212. TimesFM got the level and the jump on the last two days roughly right (199.9 and 213.0 for 210 and 212), but its value for October 8 was 169.8, a dip below October 7 that the data doesn’t have. forecast_value is the 50% quantile of the model, and the interval is the 95% one unless you set confidence_level.

Columns for the timestamp can be TIMESTAMP, DATE or DATETIME. The output timestamp is always a TIMESTAMP. The data column can be INT64, NUMERIC, BIGNUMERIC or FLOAT64. To forecast many series in one call, pass id_cols => ['store'], and every distinct combination of the ID columns is forecast separately.

The model doesn’t extrapolate a trend like a line fit does

TimesFM is a pretrained model, not a regression on your numbers. On a clean straight line it returned values near the line, not on it:

-- two perfectly linear series: north rises by 2 a day (next values 142, 144), south falls by 1 a day (next 29, 28)
SELECT
  store,
  forecast_timestamp,
  ROUND(forecast_value, 1) AS forecast
FROM AI.FORECAST(
  (SELECT 'north' AS store, TIMESTAMP_ADD(TIMESTAMP '2026-09-01', INTERVAL d DAY) AS day, 100.0 + 2 * d AS orders
   FROM UNNEST(GENERATE_ARRAY(0, 20)) AS d
   UNION ALL
   SELECT 'south', TIMESTAMP_ADD(TIMESTAMP '2026-09-01', INTERVAL d DAY), 50.0 - d
   FROM UNNEST(GENERATE_ARRAY(0, 20)) AS d),
  data_col => 'orders',
  timestamp_col => 'day',
  id_cols => ['store'],
  horizon => 2)
ORDER BY store, forecast_timestamp

north rises by 2 a day and ended at 140, so a line gives 142 and 144. TimesFM said 147.2 and 144.3. south falls by 1 a day and ended at 30, so a line gives 29 and 28. TimesFM said 24.5 and 25.6. This is the same model doing the same job as in the first example, and for noisy business data the difference may not matter. If your series is a clean trend, compare the forecast with a simple line fit before you trust it. A series of only 3 points (10, 20, 30) is accepted, and the first two forecast values were 30.4 and 28.2.

The function reads the sequence of values, not the spacing of the timestamps. A series with a point every two days and a series with a point every day, with the same 21 values, produced the same forecast values (147.2, 144.3, 150.2 for the first three steps). Only the forecast timestamps differ, because BigQuery infers the step from the input:

SELECT forecast_timestamp, ROUND(forecast_value, 1) AS forecast
FROM AI.FORECAST(
  (SELECT TIMESTAMP_ADD(TIMESTAMP '2026-09-01', INTERVAL d * 2 DAY) AS day, 100.0 + 2 * d AS orders
   FROM UNNEST(GENERATE_ARRAY(0, 20)) AS d),
  data_col => 'orders',
  timestamp_col => 'day',
  forecast_end_timestamp => TIMESTAMP '2026-10-17')
ORDER BY forecast_timestamp

Use forecast_end_timestamp instead of horizon when you need a date, not a count. BigQuery computes the horizon from the inferred frequency, and the two arguments can’t be combined.

Short series don’t fail the query

A series with fewer than 3 points gets a row of NaN with a NULL timestamp and a message in ai_forecast_status, and the other series in the same query still come back:

SELECT
  store,
  forecast_timestamp,
  ROUND(forecast_value, 1) AS forecast,
  ai_forecast_status
FROM AI.FORECAST(
  (SELECT 'north' AS store, TIMESTAMP_ADD(TIMESTAMP '2026-09-01', INTERVAL d DAY) AS day, 100.0 + 2 * d AS orders
   FROM UNNEST(GENERATE_ARRAY(0, 20)) AS d
   UNION ALL
   SELECT 'new', TIMESTAMP_ADD(TIMESTAMP '2026-09-01', INTERVAL d DAY), 10.0
   FROM UNNEST(GENERATE_ARRAY(0, 1)) AS d),
  data_col => 'orders',
  timestamp_col => 'day',
  id_cols => ['store'],
  horizon => 1)
ORDER BY store

In a large multi-series forecast, check ai_forecast_status for non-empty values. Nothing else tells you that a store was skipped.

History and intervals

With output_historical_time_series => TRUE the output has different columns: time_series_type (history or forecast), time_series_timestamp and time_series_data, so you can chart both from one query. In Querylab.io, a VISUALIZE clause with time_series_type AS color and DRAW line draws history and forecast as two lines in the results Chart tab. That is ggsql, not BigQuery syntax: Querylab.io strips it before the query reaches BigQuery (see Charts):

SELECT time_series_type, time_series_timestamp, ROUND(time_series_data, 1) AS value
FROM AI.FORECAST(
  (SELECT DATE_ADD(DATE '2026-09-01', INTERVAL d DAY) AS day, 100.0 + 2 * d AS orders
   FROM UNNEST(GENERATE_ARRAY(0, 9)) AS d),
  data_col => 'orders',
  timestamp_col => 'day',
  horizon => 3,
  output_historical_time_series => TRUE)
ORDER BY time_series_timestamp DESC
LIMIT 5

confidence_level sets the interval. On a noisy test series of 41 points, a confidence_level of 0.5 gave an interval 7.0 wide for the first forecast point (178.5 to 185.5), and the default 0.95 gave 20.2 (171.6 to 191.8).

Which TimesFM, and what it costs

Google’s reference says model accepts TimesFM 2.0 and TimesFM 2.5 and that 2.0 is the default. We tested it. Leaving model out gave the same numbers as 'TimesFM 2.5' and different numbers from 'TimesFM 2.0', so the live default is 2.5. 'TimesFM 3.0' is also accepted, and the error for anything else lists all three names. The context window is the number of most recent points the model sees: BigQuery picks the smallest of 64, 128, 256 and so on that covers your series, up to 2,048 points for 2.0 and 15,360 for 2.5.

The reference page says AI.FORECAST is billed at the BigQuery ML prediction rate, which is the normal on-demand rate per TiB your input query reads. The BigQuery pricing page separately says TimesFM 3.0 moves to token pricing (each data point is one token, with a price per million input and output tokens) from December 1, 2026, and that until then usage is billed as slots or bytes. A dry run shows only the input bytes:

SELECT *
FROM AI.FORECAST(
  (SELECT TIMESTAMP_ADD(TIMESTAMP '2026-09-01', INTERVAL d DAY) AS day, 100.0 + 2 * d AS orders
   FROM UNNEST(GENERATE_ARRAY(0, 34)) AS d),
  data_col => 'orders',
  timestamp_col => 'day',
  horizon => 7)

The query above reads no table, so it reports 0 bytes and ran at no charge. Over a real table, the dry run is the cost of reading the series and will stay the same if billing switches to tokens.

ARIMA_PLUS

ARIMA_PLUS fits one model per series, picks the ARIMA order automatically, and handles trend, seasonality, holidays and anomalies. You train it with CREATE MODEL:

CREATE OR REPLACE MODEL mydataset.orders_arima
OPTIONS (
  model_type = 'ARIMA_PLUS',
  time_series_timestamp_col = 'day',
  time_series_data_col = 'orders',
  horizon = 14
) AS
SELECT day, orders FROM mydataset.daily_orders;

SELECT forecast_timestamp, forecast_value, prediction_interval_lower_bound, prediction_interval_upper_bound
FROM ML.FORECAST(MODEL mydataset.orders_arima, STRUCT(7 AS horizon, 0.95 AS confidence_level));

We trained this on the same 35 days as above (the query is inline, so it reads no bytes). The model was ready in 12 seconds. These are plain blocks, not runnable examples, because CREATE TEMP MODEL failed in our test, so a model has to live in a dataset. The forecast for the same seven days:

ARIMA_PLUS was within 0.2 of the truth every day, and its prediction interval was about 0.5 wide. That is a synthetic series with no noise, which is the best case for a statistical model. TimesFM’s interval for the same days was 12 to 17 wide. On real data with noise, expect both to be less sharp.

ML.FORECAST returns forecast_value as the mean of the lower and upper prediction bounds. On our model the average of the two bounds matched forecast_value to one decimal. ML.EXPLAIN_FORECAST returns the parts of a forecast: trend, seasonal components, holiday effects, spikes and dips, and step changes.

What ARIMA_PLUS costs

Training is billed on the bytes the training query reads at $312.50 per TiB, 50 times the on-demand query rate of $6.25. With auto_arima on, the bytes are counted once per candidate model: 3 to 42 candidates, depending on auto_arima_max_order and the differencing order. A dry run of a CREATE MODEL for a built-in model type shows the training bytes. Forecasting with ML.FORECAST is billed at the normal query rate on the input you pass. Google bills the storage of a trained model at normal storage prices.

Minimum input is 3 points. The limit is 500,000 points per series with decomposition on and 1,000,000 with it off, and 100 million series in one model.

Which one to use

AI.FORECAST is the right first try when you have a handful of series, a short history, or no time to tune. It has no training step and no model to keep. ARIMA_PLUS is the one to pick when you need to explain the forecast (trend and seasonality are exposed), when you want holiday handling for a country, when the series is clean and long enough to learn from, or when a straight-line trend matters. For anomaly detection on the same data there are AI.DETECT_ANOMALIES (TimesFM) and ML.DETECT_ANOMALIES.

BigQuery ML in general is covered in BigQuery ML.