Anofox Forecast - Time Series Forecasting for DuckDB

_{Technical Depth and Code Health scores calculated using PMAT}

[!IMPORTANT]
This extension is in early development, so bugs and breaking changes are expected.
Please use the issues page to report bugs or request features.

A time series forecasting extension for DuckDB with 32 models, data preparation, and analytics — all in pure SQL.

✨ Key Features

🎯 Forecasting (32 Models)

• AutoML: AutoETS, AutoARIMA, AutoMFLES, AutoMSTL, AutoTBATS
• Statistical: ETS, ARIMA, Theta, Holt-Winters, Seasonal Naive
• Advanced: TBATS, MSTL, MFLES (multiple seasonality)
• Intermittent Demand: Croston, ADIDA, IMAPA, TSB
• Exogenous Variables: ARIMAX, ThetaX, MFLESX (external regressors support)

📊 Complete Workflow

• EDA & Data Quality: 5 functions (2 table functions, 3 macros) for exploratory analysis and data quality assessment
• Data Preparation: 12 macros for cleaning and transformation
• Multi-Key Hierarchy: 4 functions for combining, aggregating, and splitting hierarchical time series (region/store/item)
• Cross-Validation & Backtesting: Time series CV with expanding/fixed/sliding windows, gap, embargo, and variable horizon support
• Conformal Prediction: Distribution-free prediction intervals with guaranteed coverage probability
• Evaluation: 12 metrics including coverage analysis
• Seasonality Detection: Automatic period identification, seasonality classification, and peak detection
• Changepoint Detection: Regime identification with probabilities

🔢 Feature Calculation

• 76+ Statistical Features: Extract comprehensive time series features for ML pipelines
• GROUP BY & Window Support: Native DuckDB parallelization for multi-series feature extraction
• Flexible Configuration: Select specific features, customize parameters, or use JSON/CSV configs
• tsfresh-Compatible: Compatible feature vectors for seamless integration with existing ML workflows (hctsa will come also)

⚡ Performance

• Parallel: Native DuckDB parallelization on GROUP BY
• Scalable: Handles millions of series
• Memory Efficient: Columnar storage, streaming operations
• Native Rust Core: High-performance native implementations for data preparation and forecasting

🎨 User-Friendly API

• Zero Setup: All macros load automatically
• Consistent: MAP-based parameters
• Composable: Chain operations easily
• Multi-Language: Use from Python, R, Julia, C++, Rust, and more!

📋 Table of Contents

• Installation
• Multi-Language Support
• API Reference
• Guides
• Performance
• License

Attribution

This extension uses the anofox-forecast Rust crate and implements algorithms from several open-source projects.
See THIRD_PARTY_NOTICES.md for complete attribution and license information.

Installation

Community Extension

INSTALL anofox_forecast FROM community;
LOAD anofox_forecast;

From Source

# Clone the repository
git clone https://github.com/DataZooDE/anofox-forecast.git
cd anofox-forecast

# Build the extension (requires Rust toolchain and CMake)
make release

# The extension will be built to:
# build/extension/anofox_forecast/anofox_forecast.duckdb_extension

🚀 Quick Start on M5 Dataset

The forecast takes ~2 minutes on a Dell XPS 13. (You need DuckDB v1.4.2).

-- Load extension
LOAD httpfs;
LOAD anofox_forecast;

CREATE OR REPLACE TABLE m5 AS 
SELECT item_id, CAST(timestamp AS TIMESTAMP) AS ds, demand AS y FROM 'https://m5-benchmarks.s3.amazonaws.com/data/train/target.parquet'
ORDER BY item_id, timestamp;

CREATE OR REPLACE TABLE m5_train AS
SELECT * FROM m5 WHERE ds < DATE '2016-04-25';

CREATE OR REPLACE TABLE m5_test AS
SELECT * FROM m5 WHERE ds >= DATE '2016-04-25';

-- Perform baseline forecast and evaluate performance
CREATE OR REPLACE TABLE forecast_results AS (
    SELECT *
    FROM anofox_fcst_ts_forecast_by('m5_train', item_id, ds, y, 'SeasonalNaive', 28, {'seasonal_period': 7})
    UNION ALL
    SELECT *
    FROM anofox_fcst_ts_forecast_by('m5_train', item_id, ds, y, 'Theta', 28, {'seasonal_period': 7})
    UNION ALL
    SELECT *
    FROM anofox_fcst_ts_forecast_by('m5_train', item_id, ds, y, 'AutoARIMA', 28, {'seasonal_period': 7})
);

-- MAE and Bias of Forecasts
CREATE OR REPLACE TABLE evaluation_results AS (
  SELECT 
      item_id,
      model_name,
      anofox_fcst_ts_mae(LIST(y), LIST(point_forecast)) AS mae,
      anofox_fcst_ts_bias(LIST(y), LIST(point_forecast)) AS bias
  FROM (
      -- Join Forecast with Test Data
      SELECT 
          m.item_id,
          m.ds,
          m.y,
          n.model_name,
          n.point_forecast
      FROM forecast_results n
      JOIN m5_test m ON n.item_id = m.item_id AND n.date = m.ds
  )
  GROUP BY item_id, model_name
);

-- Summarise evaluation results by model
SELECT
  model_name,
  AVG(mae) AS avg_mae,
  STDDEV(mae) AS std_mae,
  AVG(bias) AS avg_bias,
  STDDEV(bias) AS std_bias
FROM evaluation_results
GROUP BY model_name
ORDER BY avg_mae;

🌍 Multi-Language Support

Write SQL once, use everywhere! The extension works from any language with DuckDB bindings.

Language	Status	Guide
Python	✅	Python Usage
R	✅	R Usage
Julia	✅	Julia Usage
C++	✅	Via DuckDB C++ bindings
Rust	✅	Via DuckDB Rust bindings
Node.js	✅	Via DuckDB Node bindings
Go	✅	Via DuckDB Go bindings
Java	✅	Via DuckDB JDBC driver

See: Multi-Language Overview for polyglot workflows!

---

📚 API Reference

For complete function signatures, parameters, and detailed documentation, see the API Reference.

Guides and API Sections

Guide	API Reference Section
Quick Start	Forecasting
EDA & Data Preparation	Exploratory Data Analysis, Data Quality, Data Preparation
Detecting Seasonality	Seasonality
Detecting Changepoints	Changepoint Detection
Time Series Features	Time Series Features
Basic Forecasting	Forecasting
Exogenous Variables	Exogenous Forecasting
Evaluation Metrics	Evaluation
Backtesting & Cross-Validation	Cross-Validation & Backtesting
Conformal Prediction	Conformal Prediction
Multi-Key Hierarchy	Multi-Key Hierarchy
Forecasting Model Parameters	Supported Models, Parameter Reference

📦 Development

Prerequisites

Before building, install the required dependencies:

Manjaro/Arch Linux:

sudo pacman -S base-devel cmake ninja openssl eigen

Ubuntu/Debian:

sudo apt update
sudo apt install build-essential cmake ninja-build libssl-dev libeigen3-dev

Fedora/RHEL:

sudo dnf install gcc-c++ cmake ninja-build openssl-devel eigen3-devel

macOS:

brew install cmake ninja openssl eigen

Windows (Option 1 - vcpkg, recommended):

# Install vcpkg
git clone https://github.com/Microsoft/vcpkg.git
.\vcpkg\bootstrap-vcpkg.bat

# Install dependencies
.\vcpkg\vcpkg install eigen3 openssl

# Build with vcpkg toolchain
cmake -DCMAKE_TOOLCHAIN_FILE=.\vcpkg\scripts\buildsystems\vcpkg.cmake .
cmake --build . --config Release

Windows (Option 2 - MSYS2/MinGW):

# In MSYS2 MinGW64 terminal
pacman -S mingw-w64-x86_64-gcc mingw-w64-x86_64-cmake mingw-w64-x86_64-ninja
pacman -S mingw-w64-x86_64-openssl mingw-w64-x86_64-eigen3

# Then build as normal
make -j$(nproc)

Windows (Option 3 - WSL, easiest):

# Use Ubuntu in WSL
wsl --install
# Then follow Ubuntu instructions above

Required:

• C++ compiler (GCC 9+ or Clang 10+)
• CMake 3.15+
• OpenSSL (development libraries)
• Eigen3 (linear algebra library)
• Make or Ninja (build system)

Build from Source

# Clone with submodules
git clone --recurse-submodules https://github.com/DataZooDE/anofox-forecast.git
cd anofox-forecast

# Set up Git hooks (recommended)
./scripts/setup-hooks.sh

# Build (choose