BQBrains | Forecasting in BigQuery: AI.FORECAST, TimesFM, and When ARIMA

Forecasting used to be the point where a BigQuery project leaked into Python: export the table, fit a model in a notebook, load the predictions back, and hope someone remembers to rerun it. That round trip is no longer necessary. BigQuery now offers two very different in-warehouse paths — a zero-training foundation model you call with a single function, and trained ARIMA_PLUS models you control in detail — and knowing which to reach for is most of the skill.

This tutorial builds both against a public dataset, compares the output, and finishes with evaluation and scheduling.

The two paths in one paragraph

AI.FORECAST is a table-valued function that wraps TimesFM, Google Research's pretrained time-series foundation model. You pass it a query with a timestamp column and a value column, tell it how far ahead to forecast, and it returns predictions. There is no CREATE MODEL, no training cost, and no model object to manage. CREATE MODEL ... MODEL_TYPE = 'ARIMA_PLUS' (and its multivariate sibling ARIMA_PLUS_XREG) trains a real statistical model on your history, which takes minutes and money but gives you holiday effects, external regressors, explainable decomposition, and hierarchical reconciliation.

Rule of thumb: prototype with AI.FORECAST, productionise with ARIMA_PLUS when you need explanations or external drivers.

Prerequisites

A Google Cloud project with BigQuery enabled and a dataset named demo in the US region
No Vertex AI connection required — both TimesFM and ARIMA_PLUS run inside BigQuery
Roughly 10 minutes of query time; the examples below scan well under a gigabyte

Step 1: Build a tidy time series

Forecasting functions want three things: a time column, a numeric value column, and (optionally) an ID column separating multiple series. Daily New York citywide bike trips are a convenient, seasonal, public example.

CREATE OR REPLACE TABLE `demo.daily_trips` AS
SELECT
  DATE(starttime) AS trip_date,
  COUNT(*) AS trips
FROM `bigquery-public-data.new_york_citibike.citibike_trips`
WHERE starttime IS NOT NULL
  AND DATE(starttime) BETWEEN '2015-01-01' AND '2017-12-31'
GROUP BY trip_date;

Two checks before you forecast anything:

-- Gaps and duplicates ruin forecasts quietly
SELECT
  COUNT(*) AS rows_present,
  DATE_DIFF(MAX(trip_date), MIN(trip_date), DAY) + 1 AS days_expected,
  COUNT(DISTINCT trip_date) AS distinct_days
FROM `demo.daily_trips`;

If rows_present and days_expected disagree, you have missing days. ARIMA_PLUS will interpolate them for you when data_frequency is set, but you are better off deciding explicitly whether a missing day means zero or unknown — for retail sales a closed store is a real zero, for sensor data it is a gap.

Step 2: A one-line forecast with AI.FORECAST

SELECT *
FROM AI.FORECAST(
  TABLE `demo.daily_trips`,
  data_col => 'trips',
  timestamp_col => 'trip_date',
  horizon => 30,
  confidence_level => 0.9
);

You get one row per forecast step with the forecast value, timestamp, and a prediction interval (prediction_interval_lower_bound / ..._upper_bound). That is the entire workflow: no training, no model, no hyperparameters.

Multi-series works by naming an ID column. Forecast every start station separately:

CREATE OR REPLACE TABLE `demo.station_daily` AS
SELECT
  start_station_name AS station,
  DATE(starttime) AS trip_date,
  COUNT(*) AS trips
FROM `bigquery-public-data.new_york_citibike.citibike_trips`
WHERE starttime IS NOT NULL
  AND start_station_name IS NOT NULL
  AND DATE(starttime) BETWEEN '2016-01-01' AND '2017-12-31'
GROUP BY station, trip_date;

SELECT *
FROM AI.FORECAST(
  TABLE `demo.station_daily`,
  data_col => 'trips',
  timestamp_col => 'trip_date',
  id_cols => ['station'],
  horizon => 14,
  confidence_level => 0.95
)
ORDER BY station, forecast_timestamp;

Things worth knowing about the foundation-model path:

It is zero-shot. TimesFM has never seen your data and does not learn from it; identical inputs give identical forecasts.
Longer horizons degrade faster than a trained model's, because there is no fitted seasonality — keep horizons short relative to history.
It cannot use external regressors. Marketing spend, price changes, and weather are invisible to it.
There is no decomposition to show a stakeholder: you get numbers, not an explanation.

Step 3: Train an ARIMA_PLUS model

CREATE OR REPLACE MODEL `demo.trips_arima`
OPTIONS (
  model_type = 'ARIMA_PLUS',
  time_series_timestamp_col = 'trip_date',
  time_series_data_col = 'trips',
  data_frequency = 'DAILY',
  holiday_region = 'US',
  auto_arima = TRUE,
  clean_spikes_and_dips = TRUE,
  adjust_step_changes = TRUE,
  decompose_time_series = TRUE
) AS
SELECT trip_date, trips
FROM `demo.daily_trips`;

What each option is actually buying you:

Option	Effect
`holiday_region = 'US'`	Fits named holiday effects; Thanksgiving and July 4 stop looking like anomalies
`clean_spikes_and_dips`	Outlier removal before fitting, so one bad ETL day does not bend the trend
`adjust_step_changes`	Handles level shifts — a pricing change, a new market launch
`decompose_time_series`	Stores trend / seasonality / holiday components for `ML.EXPLAIN_FORECAST`
`auto_arima`	Searches ARIMA orders automatically; cap the search with `auto_arima_max_order` on wide multi-series jobs

Forecast and explain:

SELECT *
FROM ML.FORECAST(MODEL `demo.trips_arima`,
  STRUCT(30 AS horizon, 0.9 AS confidence_level));

-- Trend, weekly seasonality, yearly seasonality and holiday effects, per row
SELECT time_series_timestamp, trend, seasonal_period_weekly, seasonal_period_yearly, holiday_effect
FROM ML.EXPLAIN_FORECAST(MODEL `demo.trips_arima`,
  STRUCT(30 AS horizon, 0.9 AS confidence_level))
ORDER BY time_series_timestamp DESC
LIMIT 40;

ML.EXPLAIN_FORECAST is the feature that wins arguments in review meetings. "Next month is down 8%" is a claim; "trend is flat, but the forecast window contains two fewer weekend days and no holidays" is an explanation.

Step 4: Add external regressors with ARIMA_PLUS_XREG

When you know something about the future that history alone cannot express — temperature, promotion flags, planned outages — use the multivariate variant. Regressor values must be known for the forecast horizon, not just for the training period.

CREATE OR REPLACE MODEL `demo.trips_xreg`
OPTIONS (
  model_type = 'ARIMA_PLUS_XREG',
  time_series_timestamp_col = 'trip_date',
  time_series_data_col = 'trips',
  data_frequency = 'DAILY',
  holiday_region = 'US'
) AS
SELECT trip_date, trips, temp_c, is_weekend
FROM `demo.daily_trips_with_weather`;

SELECT *
FROM ML.FORECAST(MODEL `demo.trips_xreg`,
  STRUCT(14 AS horizon, 0.9 AS confidence_level),
  (SELECT trip_date, temp_c, is_weekend FROM `demo.future_weather`));

The most common failure here is leakage in reverse: teams feed a regressor they will not actually have at prediction time ("actual revenue") and then discover the model is useless in production. If you cannot supply the column for future dates, it is not a regressor.

Step 5: Hierarchical forecasts that add up

A per-store forecast that does not sum to the regional forecast is a reporting incident waiting to happen. ARIMA_PLUS reconciles a hierarchy for you:

CREATE OR REPLACE MODEL `demo.station_hier`
OPTIONS (
  model_type = 'ARIMA_PLUS',
  time_series_timestamp_col = 'trip_date',
  time_series_data_col = 'trips',
  time_series_id_col = ['borough', 'station'],
  hierarchical_time_series_cols = ['borough', 'station'],
  data_frequency = 'DAILY',
  holiday_region = 'US'
) AS
SELECT borough, station, trip_date, trips
FROM `demo.station_daily_with_borough`;

BigQuery trains a model per series and reconciles bottom-up so station forecasts sum to borough forecasts. Watch the slot cost: a few thousand series is routine, a few hundred thousand needs a reservation and a sane auto_arima_max_order.

Step 6: Evaluate honestly

Never compare a model against the data it was trained on. Hold out the tail:

-- Train on everything up to 2017-11-30
CREATE OR REPLACE MODEL `demo.trips_arima_holdout`
OPTIONS (model_type = 'ARIMA_PLUS', time_series_timestamp_col = 'trip_date',
         time_series_data_col = 'trips', data_frequency = 'DAILY', holiday_region = 'US') AS
SELECT trip_date, trips FROM `demo.daily_trips` WHERE trip_date < '2017-12-01';

-- Score against the held-out month
SELECT *
FROM ML.EVALUATE(MODEL `demo.trips_arima_holdout`,
  (SELECT trip_date, trips FROM `demo.daily_trips` WHERE trip_date >= '2017-12-01'),
  STRUCT(TRUE AS perform_aggregation, 31 AS horizon));

ML.EVALUATE returns MAE, MAPE, RMSE and friends. Score AI.FORECAST the same way with a manual join so the comparison is apples to apples:

WITH fc AS (
  SELECT DATE(forecast_timestamp) AS trip_date, forecast_value
  FROM AI.FORECAST(
    (SELECT trip_date, trips FROM `demo.daily_trips` WHERE trip_date < '2017-12-01'),
    data_col => 'trips', timestamp_col => 'trip_date', horizon => 31)
)
SELECT
  AVG(ABS(a.trips - fc.forecast_value)) AS mae,
  AVG(ABS(a.trips - fc.forecast_value) / NULLIF(a.trips, 0)) AS mape
FROM fc
JOIN `demo.daily_trips` a USING (trip_date);

Also compute a naive baseline — last week's same weekday — and refuse to ship any model that cannot beat it. In our experience roughly one forecasting project in four turns out not to need a model at all.

Step 7: Ship it

A production forecast is a table that a dashboard reads, refreshed on a schedule:

CREATE OR REPLACE TABLE `demo.trips_forecast` AS
SELECT
  CURRENT_TIMESTAMP() AS generated_at,
  DATE(forecast_timestamp) AS trip_date,
  forecast_value,
  prediction_interval_lower_bound,
  prediction_interval_upper_bound
FROM ML.FORECAST(MODEL `demo.trips_arima`,
  STRUCT(30 AS horizon, 0.9 AS confidence_level));

Wrap the retrain and the forecast in a scheduled query (or a Dataform / Cloud Composer step) and keep generated_at so you can audit forecast drift later. Two habits worth adopting:

Retrain on a cadence, not on every run. Daily forecasts from a weekly-retrained model are usually fine and much cheaper.
Store every vintage. Appending forecasts rather than replacing them lets you answer "what did we think in March?" — the single most useful question in a forecast post-mortem.

Choosing between them

Need	Use
Quick exploration, new series, no history of tuning	`AI.FORECAST` (TimesFM)
Explainable trend / seasonality / holiday breakdown	`ARIMA_PLUS` + `ML.EXPLAIN_FORECAST`
External drivers known for the future	`ARIMA_PLUS_XREG`
Forecasts that must sum across a hierarchy	`ARIMA_PLUS` with `hierarchical_time_series_cols`
Tens of thousands of short, sparse series	Benchmark both; TimesFM often wins on cold starts

Both paths keep the data, the model, and the governance in one place, which is the real argument for forecasting in the warehouse rather than in a notebook nobody else can run.

If you want help picking the approach, benchmarking it against your current process, and putting it on a schedule, that is exactly what our BigQuery machine learning services do — get in touch with a description of the series you need to forecast.

Forecasting in BigQuery: AI.FORECAST, TimesFM, and When ARIMA_PLUS Still Wins