Your next forecast is a SQL query

You have a Parquet file, a DuckDB session, and a column you want to forecast. It would be nice to stay there for the next step.

We’ve been working on a DuckDB extension that runs our t0 ONNX models locally. You point it at a table, tell it which column to forecast, and get a table back. The model runs on your laptop’s CPU, inside the DuckDB process. There is no inference service to start or send your data to.

The extension defaults to t0-alpha INT8, a 107 MB download. t0-alpha and t0-beta are each available in INT8 and FP16. This walkthrough uses t0-beta ONNX FP16, selected before loading the extension:

export TFC_MODEL=t0-beta-onnx-fp16

That checkpoint stores its weights in 16-bit floating point; ONNX Runtime computes in 32-bit floating point. Once the model and native runtime are cached, forecasting works offline. You don’t need Python or PyTorch in the inference environment, and you don’t need a GPU.

We’ve already tried putting a forecasting model in a browser. And we really like DuckDB: open a file, write some SQL, and start exploring. We wanted forecasting to feel just as natural, with the model right there alongside the data.

Start with the table you have

For this demo, we use hourly German electricity prices. We take 512 hours of history and forecast the next 24. The data also contains day-ahead forecasts of electricity demand and renewable generation. We’ll use those in a moment.

Download the two public data files once:

curl -fL https://autogluon.s3.amazonaws.com/datasets/timeseries/electricity_price/train.parquet -o prices-history.parquet
curl -fL https://autogluon.s3.amazonaws.com/datasets/timeseries/electricity_price/test.parquet -o prices-next-day.parquet

After loading the extension, define the history and the observations we’ll keep aside for evaluation:

CREATE TEMP VIEW history AS
SELECT id, timestamp AS date, target AS price,
       "Ampirion Load Forecast" AS load_forecast,
       "PV+Wind Forecast" AS renewables_forecast
FROM read_parquet('prices-history.parquet')
WHERE timestamp >= TIMESTAMP '2017-11-20 16:00:00';

CREATE TEMP VIEW actuals AS
SELECT id, timestamp AS date, target AS price
FROM read_parquet('prices-next-day.parquet');

Then forecast:

CREATE TEMP TABLE forecast_result AS
SELECT * FROM tfc_forecast(
    'history', 24,
    target_col := 'price',
    timestamp_col := 'date',
    id_cols := ['id'],
    frequency := '1 hour',
    context := 512,
    quantiles := [0.10, 0.25, 0.50, 0.75, 0.90]
);

This returns 24 rows, one for each hour on 12 December 2017. We haven’t asked the model to use the extra columns yet: this forecast uses price history alone. With several series in the input table, each distinct id would get its own forecast. To forecast several target columns, reshape them into rows with SQL and include the target name in the series ID. Each target is forecast independently.

You can join the result to actual prices, filter dates, or write it back to Parquet. All the usual SQL still works.

Five quantiles in the same call

The query asks for P10, P25, P50, P75 and P90 together. prediction is the median, so it equals P50. P10–P90 gives a wider view of uncertainty; P25–P75 shows the middle of the predicted distribution.

Raw output from the local DuckDB query. Only the final 48 hours of the 512-hour history are drawn. The actual prices after the cutoff were withheld from the model. The forecast follows the daily shape, but overshoots much of the following morning.

t0-beta produces 21 native quantile levels, from P01 to P99. The extension lets you choose which ones come back as columns; it does not run a separate forecast for each level. If you omit the argument, you get P10, P50 and P90.

These bands describe the model’s forecast distribution. Whether they cover as often as their nominal levels suggest is something to check on held-out data.

Add known-future covariates

Electricity prices respond to supply and demand. A forecast of tomorrow’s load or wind generation can tell us something the price history alone cannot.

Covariates are additional inputs that can help explain the target we’re forecasting. The extension accepts numeric covariates with historical values and values known for the forecast period. Here we have day-ahead forecasts of load and combined solar and wind generation.

The history already contains both columns. For the future, we select only their forecasts and the identifying columns:

CREATE TEMP VIEW future_inputs AS
SELECT id, timestamp AS date,
       "Ampirion Load Forecast" AS load_forecast,
       "PV+Wind Forecast" AS renewables_forecast
FROM read_parquet('prices-next-day.parquet');

There is no future price column in this view. We pass it to the same function:

CREATE TEMP TABLE forecast_with_covariates AS
SELECT * FROM tfc_forecast(
    'history', 24,
    target_col := 'price',
    timestamp_col := 'date',
    id_cols := ['id'],
    frequency := '1 hour',
    context := 512,
    covariate_cols := ['load_forecast', 'renewables_forecast'],
    future_table := 'future_inputs',
    quantiles := [0.10, 0.25, 0.50, 0.75, 0.90]
);

Same model, history and forecast period. The covariates bring the morning forecast down, closer to the prices that followed. Both curves come from local tfc_forecast calls.

The future inputs must cover every requested date for every series. Missing coverage raises an error. In a historical evaluation, use forecasts or plans that were available at the time. Supplying the demand or generation that actually happened later would give the model information it wouldn’t have had.

Check whether the covariates helped

tfc_evaluate takes the history and a separate table of actual observations:

CREATE TEMP TABLE evaluation_result AS
SELECT * FROM tfc_evaluate(
    'history', 'actuals',
    horizon := 24,
    target_col := 'price',
    timestamp_col := 'date',
    id_cols := ['id'],
    frequency := '1 hour',
    context := 512,
    covariate_cols := ['load_forecast', 'renewables_forecast'],
    future_table := 'future_inputs',
    seasonal_period := 24
);

SELECT id, mae, rmse, mase FROM evaluation_result;

This returns MAE, MSE, RMSE, MAPE, sMAPE and MASE for each series. Actual prices are used for scoring, never as model input. Evaluation runs inference again; it doesn’t read the saved forecast table. To evaluate the history-only version, omit the two covariate arguments.

On this day, the local ONNX model’s MAE falls from 6.53 to 2.29 with covariates. RMSE falls from 8.07 to 3.24. We also checked the preceding 27 days with the same context and horizon:

Mean absolute error across all 28 daily forecasts, with the same model, 512-hour history and 24-hour horizon in both runs. Lower is better. Each day contributes 24 held-out prices; the vertical axis starts at zero.

Covariates reduce MAE by 56.6% across these 28 days, improving 27 of the 28 daily forecasts. They help in this example; a covariate is still something to test on your own data.

Run forecasts locally, without per-forecast fees

With the model loaded and source tables prepared, our Apple M4 laptop (16 GiB RAM) produced the 28 daily forecasts with covariates in 1.12 seconds on CPU in one batched call, including input preparation within the forecast query and writing the result table. This is one warm run, excluding downloads and model startup; timing varies with hardware and workload.

• No subscription or inference API bill. Run as many forecasts as you need on your own hardware.
• Apache-2.0 model weights. Keep the checkpoint and use ONNX to run it outside DuckDB too.
• Portable results. Inputs and forecasts stay in ordinary tables and files, ready to query or export to Parquet.

The extension repository includes build instructions and the complete German electricity example: data downloads, SQL, the 28-day comparison, and CSV exports for the charts above. Start with the quick start to reproduce the walkthrough and adapt it to your own data.

The extension is currently a locally built, unsigned preview, not yet in DuckDB Community. We plan to submit it to DuckDB Community Extensions soon. The current build requires DuckDB 1.5.4 and Rust/Cargo 1.89+. Its justfile builds the extension; the first LOAD downloads and caches the model and ONNX Runtime automatically. The model is publicly downloadable from Hugging Face; no account, access approval, or token is required. Subsequent sessions reuse the cache.