Anofox Forecast

Time Series Forecasting for DuckDB

_{Technical Depth: A (93%) | Code Health: A- (90%) - 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 36 models, statistical diagnostics, data preparation, and analytics — all in pure SQL.

🤖 Built for AI pair-programming. anofox-forecast ships an installable Claude Code plugin — five skills that give your AI assistant verified, hallucination-free knowledge of the full SQL API. Describe the forecast in plain English; Claude writes correct ts_forecast_by, ensemble, backtest, and diagnostics queries.

Install with /plugin marketplace add DataZooDE/anofox-forecast → /plugin install anofox-forecast@anofox-forecast.

✨ Key Features

🎯 Forecasting (36 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
• Distributional: Laplace (streaming likelihood-weighted mixture, three zero-config selectors: auto / auto_aid / skaters)
• Volatility & State-Space: GARCH (conditional volatility), Kalman filter (local-level / local-linear-trend)
• Global / Panel: GlobalETS, GlobalTheta, GlobalCroston — cross-series "fit once, forecast all" via ts_forecast_panel_by
• Multivariate: VAR (vector autoregression) via ts_forecast_var_by — N value columns → per-variable forecasts
• Exogenous Variables: ARIMAX, ThetaX, MFLESX (external regressors support)

🔬 Statistical Diagnostics

• Stationarity: ADF, KPSS, and a combined four-way verdict (ts_adf_by, ts_kpss_by, ts_stationarity_by)
• Residual Adequacy: Ljung-Box, Durbin-Watson, Jarque-Bera, plus a combined pass/fail report (ts_residual_diagnostics_by)
• Reference-checked: numerically cross-validated against statsmodels

📊 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

• 117 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

• Key Features
• Claude Code Skills (AI pair-programming)
• Installation
• Quick Start on M5 Dataset
• Multi-Language Support
• API Reference
• Development
• License

🤖 Claude Code Skills (AI pair-programming)

Forecasting is easier when your AI assistant actually knows the API. anofox-forecast ships an installable Claude Code plugin — five skills that give Claude deep, source-verified knowledge of every function, MAP/STRUCT option, and return column. Instead of guessing at signatures, Claude writes correct SQL from a plain-English request like "forecast each store with an AutoETS/Theta ensemble and attach 90% conformal intervals" or "backtest AutoARIMA per SKU and give me MASE."

Install in any Claude Code session:

/plugin marketplace add DataZooDE/anofox-forecast
/plugin install anofox-forecast@anofox-forecast

The skills live in plugins/anofox-forecast/ and stay in sync with the extension's SQL surface. For in-repo development, load them directly with claude --plugin-dir ./plugins/anofox-forecast.

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 --recurse-submodules https://github.com/DataZooDE/anofox-forecast.git
cd anofox-forecast

# Build the extension (choose one)
# This step requires Rust toolchain and CMake
make release -j$(nproc)      # With Make OR
GEN=ninja make release       # With Ninja (faster)

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

🤖 Using an AI assistant? Install the Claude Code skills plugin so it knows the full SQL API.

🚀 Quick Start on M5 Dataset

The forecast takes ~2 minutes on a Dell XPS 13. (Requires DuckDB v1.4.5 LTS or v1.5.5+).

-- 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';

-- Forecast with multiple models
CREATE OR REPLACE TABLE forecast_results AS (
    SELECT * FROM ts_forecast_by('m5_train', item_id, ds, y, 'SeasonalNaive', 28, '1d', MAP{'seasonal_period': '7'})
    UNION ALL
    SELECT * FROM ts_forecast_by('m5_train', item_id, ds, y, 'Theta', 28, '1d', MAP{'seasonal_period': '7'})
    UNION ALL
    SELECT * FROM ts_forecast_by('m5_train', item_id, ds, y, 'AutoARIMA', 28, '1d', MAP{'seasonal_period': '7'})
);

-- Join forecasts with actuals and create composite key for grouping
CREATE OR REPLACE TABLE forecast_vs_actual AS
SELECT
    f.item_id,
    f.model_name,
    f.item_id || '|' || f.model_name AS series_key,
    f.ds,
    t.y AS actual,
    f.yhat AS forecast
FROM forecast_results f
JOIN m5_test t ON f.item_id = t.item_id AND f.ds = t.ds;

-- MAE and Bias per series using scalar metrics + GROUP BY
-- (recommended over the deprecated ts_*_by table macros — ~2400x faster, parallel-safe)
CREATE OR REPLACE TABLE evaluation_results AS
SELECT
    item_id,
    model_name,
    ts_mae(LIST(actual ORDER BY ds), LIST(forecast ORDER BY ds)) AS mae,
    ts_bias(LIST(actual ORDER BY ds), LIST(forecast ORDER BY ds)) AS bias
FROM forecast_vs_actual
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.

📚 API Reference

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

Documentation Structure

API Documentation

Model Reference (36 Models)

📦 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 one)
make -j$(nproc)              # With Make
GEN=ninja make release       # With Ninja (faster)

Code Quality

This project uses Git hooks to ensure code quality before commits:

• cargo fmt - Enforces consistent code formatting
• cargo clippy - Catches common mistakes and enforces best practices

Setup the hooks after cloning:

./scripts/setup-hooks.sh

To run checks manually:

cargo fmt --all           # Format code
cargo clippy --workspace  # Run linter
cargo test --workspace    # Run tests

Verify Installation

# Test the extension
./build/release/duckdb -c "
LOAD 'build/release/extension/anofox_forecast/anofox_forecast.duckdb_extension';
SELECT 'Extension loaded successfully! ✅' AS status;
"

Load Extension

-- In DuckDB
LOAD 'path/to/anofox_forecast.duckdb_extension';

-- Verify all functions are available
SELECT * FROM ts_forecast_by('sales', product_id, date, amount, 'AutoETS', 7, '1d', MAP{'seasonal_period': '7'});

📄 License

Business Source License 1.1 (BSL 1.1)

Key Points

✅ Free for production use - Use internally in your business
✅ Free for development - Build applications with it
✅ Free for research - Academic and research use

❌ Cannot offer as hosted service - No SaaS offerings to third parties
❌ Cannot embed in commercial product - For third-party distribution

🔄 Converts to MPL 2.0 - After 5 years from first publication

See LICENSE for full terms.

🤝 Contributing

We welcome contributions! Please see CONTRIBUTING.md for guidelines.

📞 Support

• Documentation: docs/
• Issues: GitHub Issues
• Discussions: GitHub Discussions
• Email: [email protected]

🎓 Citation

If you use this extension in research, please cite:

@software{anofox_forecast,
  title = {Anofox Forecast: Time Series Forecasting for DuckDB},
  author = {Joachim Rosskopf, Simon Müller, DataZoo GmbH},
  year = {2025},
  url = {https://github.com/DataZooDE/anofox-forecast}
}

🏆 Acknowledgments

Built on top of:

• DuckDB - Amazing analytical database
• anofox-forecast - Core forecasting library
• anofox-regression - Regression and feature extraction

Special thanks to the DuckDB team for making extensions possible!

Made with ❤️ by the Anofox Team

⭐ Star us on GitHub if you find this useful!

📢 Follow us for updates: @datazoo

🚀 Get started now: LOAD 'anofox_forecast';

Feedback

If a forecast looks wrong or anofox_forecast misbehaves, please
open an issue. Forecast quality
depends on real seasonality, gaps and outliers we cannot reproduce from synthetic
series, so a report with your data shape is the fastest path to a fix.

If it saved you time, a star on the repo helps other people find it.

The first time you load the extension in an interactive terminal each day, a small
banner says the same thing. It never prints when output is piped, in notebooks, or in
CI. Silence it with SET datazoo_banner = false; or DATAZOO_NO_BANNER=1.