BigQuery AI.Forecast

AI.FORECAST leverages the pre-trained TimesFM foundation model to generate forecasts without the need to train and manage custom models.

Syntax Reference

SELECT
  *
FROM
  AI.FORECAST(
    { TABLE `project.dataset.table` | (QUERY_STATEMENT) },
    data_col => 'DATA_COL',
    timestamp_col => 'TIMESTAMP_COL'
    [, model => 'MODEL']
    [, id_cols => ID_COLS]
    [, horizon => HORIZON]
    [, confidence_level => CONFIDENCE_LEVEL]
    [, output_historical_time_series => OUTPUT_HISTORICAL_TIME_SERIES]
    [, context_window => CONTEXT_WINDOW]
  )

Input Arguments

Argument	Requirement	Type	Description
`input_data`	Required		The source table or subquery containing historical data.
`data_col`	Required	String	The numeric column to predict.
`timestamp_col`	Required	String	The column containing dates/timestamps.
`id_cols`	Optional	Array	Grouping columns for multiple series (e.g., `['store_id']`).
`horizon`	Optional	Int64	Number of future points to predict. Defaults to 10. The valid input range is [1, 10,000].
`confidence_level`	Optional	Float64	Confidence interval (0 to 1). Defaults to 0.95.
`model`	Optional	String	Model version. Defaults to `TimesFM 2.0`.
`context_window`	Optional	Int64	The number of historical data points the model uses to forecast. The min value is 64 and the max value is 2048 for `TimesFM 2.0`. If not set, the model determines this automatically.

Output Schema

The schema adjusts based on the output_historical_time_series flag.

Column	Type	Included if output_historical_time_series=FALSE	Included if output_historical_time_series=TRUE	Description
`id_cols`	(As Input)	Yes	Yes	Original identifiers for the series.
`forecast_timestamp`	TIMESTAMP	Yes	No	Timestamp for predicted points.
`forecast_value`	FLOAT64	Yes	No	The 50% quantile (median) prediction.
`time_series_timestamp`	TIMESTAMP	No	Yes	Uniform timestamp column for both history and forecast.
`time_series_data`	FLOAT64	No	Yes	Merged column: actual values for history, median for forecast.
`time_series_type`	STRING	No	Yes	Label: `'history'` or `'forecast'`.
`prediction_interval_lower_bound`	FLOAT64	Yes	Yes	Lower bound (NULL for historical rows).
`prediction_interval_upper_bound`	FLOAT64	Yes	Yes	Upper bound (NULL for historical rows).
`confidence_level`	FLOAT64	Yes	Yes	The constant confidence level used.
`ai_forecast_status`	STRING	Yes	Yes	Error messages or empty string on success. A minimum of 3 data points is required.

Examples

Forecasting with History

WITH
  citibike_trips AS (
    SELECT EXTRACT(DATE FROM starttime) AS date, usertype, COUNT(*) AS num_trips
    FROM `bigquery-public-data.new_york.citibike_trips`
    GROUP BY date, usertype
  )
SELECT *
FROM
  AI.FORECAST(
    TABLE citibike_trips,
    data_col => 'num_trips',
    timestamp_col => 'date',
    id_cols => ['usertype'],
    horizon => 30,
    output_historical_time_series => true);

Command Palette