# 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.

Source: https://docs.querylab.io/bigquery/forecasting/

Updated: 2026-10-14

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.

**seven days ahead, with 95% intervals**

```sql
-- 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
```

Result in BigQuery (7 rows):

| forecast_timestamp | forecast | lower | upper |
| --- | --- | --- | --- |
| 2026-10-06 00:00:00 UTC | 169.9 | 160.3 | 175.8 |
| 2026-10-07 00:00:00 UTC | 172.8 | 163.2 | 180.5 |
| 2026-10-08 00:00:00 UTC | 169.8 | 163.5 | 178.5 |
| 2026-10-09 00:00:00 UTC | 176.5 | 169.1 | 182.7 |
| 2026-10-10 00:00:00 UTC | 177.8 | 175.0 | 188.4 |
| 2026-10-11 00:00:00 UTC | 199.9 | 193.0 | 205.0 |
| 2026-10-12 00:00:00 UTC | 213.0 | 201.1 | 214.0 |

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.

### TimesFM doesn't extrapolate a trend

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 exactly linear series**

```sql
-- 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
```

Result in BigQuery (4 rows):

| store | forecast_timestamp | forecast |
| --- | --- | --- |
| north | 2026-09-22 00:00:00 UTC | 147.2 |
| north | 2026-09-23 00:00:00 UTC | 144.3 |
| south | 2026-09-22 00:00:00 UTC | 24.5 |
| south | 2026-09-23 00:00:00 UTC | 25.6 |

`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:

**a 2-day series forecast to a fixed end date**

```sql
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
```

Result in BigQuery (3 rows):

| forecast_timestamp | forecast |
| --- | --- |
| 2026-10-13 00:00:00 UTC | 147.2 |
| 2026-10-15 00:00:00 UTC | 144.3 |
| 2026-10-17 00:00:00 UTC | 150.2 |

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:

**a series with 2 points next to one with 21**

```sql
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
```

Result in BigQuery (2 rows):

| store | forecast_timestamp | forecast | ai_forecast_status |
| --- | --- | --- | --- |
| new | NULL | NaN | The time series data is too short. |
| north | 2026-09-22 00:00:00 UTC | 147.2 |  |

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](https://docs.querylab.io/writing-sql/charts/)):

**the last history rows and the first forecast rows**

```sql
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
```

Result in BigQuery (5 rows):

| time_series_type | time_series_timestamp | value |
| --- | --- | --- |
| forecast | 2026-09-13 00:00:00 UTC | 125.0 |
| forecast | 2026-09-12 00:00:00 UTC | 122.6 |
| forecast | 2026-09-11 00:00:00 UTC | 124.5 |
| history | 2026-09-10 00:00:00 UTC | 118.0 |
| history | 2026-09-09 00:00:00 UTC | 116.0 |

`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:

**a dry run of the first example**

```sql
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)
```

BigQuery dry run: this query processes 0 B.

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`:

```sql
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:

| Day    | True | ARIMA_PLUS | TimesFM |
| ------ | ---- | ---------- | ------- |
| Oct 6  | 170  | 169.8      | 169.9   |
| Oct 7  | 172  | 171.9      | 172.8   |
| Oct 8  | 174  | 174.0      | 169.8   |
| Oct 9  | 176  | 175.9      | 176.5   |
| Oct 10 | 178  | 177.9      | 177.8   |
| Oct 11 | 210  | 209.9      | 199.9   |
| Oct 12 | 212  | 212.1      | 213.0   |

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](https://docs.querylab.io/bigquery/bigquery-ml/).

Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [The AI.FORECAST function](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-forecast)

- [The ML.FORECAST function](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-forecast)

- [The CREATE MODEL statement for time series models](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-create-time-series)

- [BigQuery ML pricing](https://cloud.google.com/bigquery/pricing#bqml)