Time-series forecasting in a single SQL call: Databricks' ai_forecast()

There's a request every data team has heard: "give me a sales forecast for next quarter". It sounds trivial, but it usually turns into a mini-project — a notebook, choosing a library, comparing models, backtesting, logging to MLflow, scheduling the job. Two or three weeks later, you deliver a number the business needed yesterday.

Databricks' ai_forecast() was built to shorten exactly that path. It's a SQL table-valued function: you point it at your time series, set the forecast horizon and get back the projected values with confidence intervals already included — no model training, no ML infrastructure.

This article shows how to use it, what the function returns, and — just as important — when not to use it.

The problem it solves

Most forecasts inside a company aren't a research problem; they're an operational one. Someone needs a reasonable number, fast, to plan inventory, size a team or close a budget. In those cases, standing up a full machine-learning pipeline is disproportionate to the value of the question.

At the same time, the traditional "quick" alternative — export to a spreadsheet and draw a trend line — breaks governance: the data leaves the controlled environment, nobody audits the calculation and each area uses a different method.

ai_forecast() sits in the middle: as fast as a SQL query, but running inside Unity Catalog, with lineage and permissions preserved.

The syntax

The minimal form needs four things: the observed table, the horizon, the time column and the value column.

-- forecast to the end of the year
SELECT * FROM ai_forecast(
  observed  => TABLE(daily_sales),
  horizon   => '2026-12-31',
  time_col  => 'ds',
  value_col => 'sales'
);

The main parameters:

• observed — the training input, passed as TABLE(...). It can be a table, a view or a subquery.
• horizon — the (exclusive) end of the forecast, as DATE, TIMESTAMP or a string. The function projects from the last observation up to that point.
• time_col — the time column (DATE or TIMESTAMP).
• value_col — the column to forecast (must be castable to DOUBLE). It also accepts an array of columns to forecast several series at once.

And the optional ones that pay off most day to day:

• group_col — one or more grouping columns. With it, you generate one independent forecast per group (store, SKU, region) in a single call, with no Python loop.
• prediction_interval_width — the width of the confidence interval (default 0.95).
• frequency — the granularity of the series (default auto).
• seed — a seed for reproducible results.

Per-group forecasting

group_col is what turns the function from a "toy" into a production tool. Imagine forecasting sales for 500 stores:

SELECT * FROM ai_forecast(
  observed  => TABLE(SELECT ds, sales, store_id FROM daily_sales),
  horizon   => '2026-12-31',
  time_col  => 'ds',
  value_col => 'sales',
  group_col => 'store_id'
);

Each store_id gets its own model and its own forecast, partitioned automatically. The result comes out stacked, ready to materialize into a Delta table or feed a dashboard.

What the function returns

The output is a table with the time column, the forecast value and the interval bounds — something like:

 ds           sales_forecast   sales_upper   sales_lower
 2026-10-01   1,240.5          1,410.2       1,070.8
 2026-10-02   1,255.9          1,428.7       1,083.1

Since it's SQL, you chain it directly: materialize with CREATE TABLE ... AS SELECT, join with actuals to monitor error, or expose it in a view for BI. Nothing leaves the lakehouse.

Why it matters

Three concrete gains:

1. Zero ML pipeline. Model selection is automatic. You don't choose between ARIMA, Prophet or gradient boosting — the service decides and tunes. That drops the barrier to entry for people who are strong in SQL but don't do modeling for a living.

2. Scales per group with no extra code. One call covers hundreds or thousands of series. What used to be a for loop over groups with manual parallelization becomes a single group_col line.

3. Governance preserved. Because it runs as a native function over Unity Catalog data, the same permissions, lineage and auditing as the rest of your environment apply. The forecast stops being a loose script on someone's machine.

When NOT to use it

Being honest about the limits is part of the value:

• When the problem needs external features. If the forecast depends heavily on price, promotions, weather or events, a dedicated model that ingests those variables will beat an automatic function that only looks at the series' own history.
• When you need fine-grained explainability. For a number that will support a sensitive regulatory or financial decision, you probably want to control and document the method.
• Very short or very noisy series. Without enough history, any method — automatic or not — is guessing. The function doesn't do magic with missing data.

For everything else — the vast majority of "I need a number to plan" requests — ai_forecast() delivers in minutes what used to cost a sprint.

How to start

1. Make sure you have a table with a time column (DATE/TIMESTAMP) and a numeric value column, in Unity Catalog.
2. Run the minimal version of the query with a short horizon to validate the output shape.
3. Add group_col and tune prediction_interval_width as needed.
4. Materialize the result into a Delta table and schedule a refresh — or leave it as a view for on-demand queries.

It's the kind of feature that changes the cost of answering a question: from "open a project" to "write a query".