AnoFox

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, AutoTBATS, plus the deprecated-but-accepted aliases AutoMFLES/AutoMSTL (same code path as MFLES/MSTL, reported model_name matches the bare family name)
• 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: crate-native exog support for Naive, ARIMA, AutoARIMA, the Theta family, ETS/AutoETS, MFLES, and MSTL — reported model_name is the base model's own name with " with regressors" appended (no separate model name)

🔬 Statistical Diagnostics

• Stationarity: ADF, KPSS, and a combined four-way verdict, each with regression := 'c' (default) or 'ct' for trend-stationarity (ts_adf_by, ts_kpss_by, ts_stationarity_by)
• Residual Adequacy: Ljung-Box (model-aware via fitted_params := p + q, R Box.test(fitdf)-exact), Durbin-Watson, Jarque-Bera, plus a combined pass/fail report (ts_residual_diagnostics_by)
• Reference-checked: Ljung-Box, Jarque-Bera, Durbin-Watson, and the KPSS statistic are numerically cross-checked against R (stats::Box.test, tseries) and scipy; ADF/KPSS p-values are a coarse approximation, not cross-validated (see docs/api/10-diagnostics.md)

📊 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, horizon-ordered and calibrated per group, plus residual-resampling bootstrap intervals (see docs/api/11-conformal-prediction.md)
• 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-Inspired: Feature vectors inspired by tsfresh's naming/catalogue; a documented subset of features deviates from tsfresh's exact formulas (see docs/api/21-feature-reference.md)

⚡ 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

Skill	Covers
anofox-forecast-models	36 models + ensembles; ts_forecast_by / ts_forecast_ensemble_by / ts_forecast_var_by
anofox-forecast-backtest	Cross-validation, metrics, conformal prediction intervals
anofox-forecast-eda	Per-series stats, data quality, 117 features, stationarity/residual diagnostics
anofox-forecast-detection	Seasonality, period, changepoint, peak detection
anofox-forecast-data-prep	Gap filling, imputation, differencing, hierarchical keys

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;

To upgrade an already-installed copy, use FORCE INSTALL anofox_forecast FROM community; — a plain INSTALL is a no-op when a build is already cached locally.

DataZoo Repository (latest builds)

For the freshest builds — ahead of the community-extensions cadence — install from DataZoo's own extension repository. These binaries are unsigned (independent of the DuckDB Foundation), so start DuckDB with the -unsigned flag. This command requires outbound network access and will error in a network-restricted environment:

-- launch with:  duckdb -unsigned
INSTALL anofox_forecast FROM 'http://get.erpl.io';
LOAD anofox_forecast;

(Use FORCE INSTALL … FROM 'http://get.erpl.io'; to refresh an already-cached copy.) Builds are published there for DuckDB v2.0.0, v1.5.6 and v1.4.5 LTS.

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 >= 1.89 (workspace rust-version) 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

The duckdb submodule is pinned to v2.0.0, so a source build targets DuckDB v2.0.0. To build for v1.5.6 or v1.4.5 LTS, check out the duckdb submodule at that tag and extension-ci-tools at v1.5-variegata / v1.4-andium.

🤖 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 v2.0.0, v1.5.6, or v1.4.5 LTS). This loads httpfs and reads a remote parquet file, so it requires outbound network access and the httpfs extension binary and will error without either:

-- 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.

Language	Status	Notes
Python	✅	Via DuckDB Python bindings
R	✅	Via DuckDB R bindings
Julia	✅	Via DuckDB Julia bindings
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

---

📚 API Reference

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

Documentation Structure

Category	Description	Documentation
Getting Started	Installation and first forecast	Getting Started Guide
Model Selection	Choose the right model	Model Selection Guide
Cross-Validation	Evaluate forecast accuracy	Cross-Validation Guide

API Documentation

Topic	Description	Reference
Hierarchical Data	Multi-key hierarchy functions	02-hierarchical.md
Statistics	34 statistical metrics, data quality	03-statistics.md
Data Preparation	Cleaning, imputation, filtering	04-data-preparation.md
Period Detection	Seasonality detection (12 methods)	05-period-detection.md
Changepoint Detection	Structural break detection	06-changepoint-detection.md
Forecasting	36 forecasting models (incl. GARCH, Kalman, Global/panel, VAR)	07-forecasting.md
Statistical Diagnostics	Stationarity (ADF/KPSS) & residual adequacy tests	10-diagnostics.md
Cross-Validation	Backtesting and CV functions	08-cross-validation.md
Evaluation Metrics	12 accuracy metrics

Model Reference (36 Models)

Category	Models	Reference
Baseline	Naive, SMA, SeasonalNaive, RandomWalkDrift	baseline/
Exponential Smoothing	SES, Holt, HoltWinters, SeasonalES	exponential-smoothing/
State Space	ETS, ARIMA, AutoETS, AutoARIMA, Kalman	state-space/
Classical	GARCH (conditional volatility)	classical/garch.md
Theta	Theta, OptimizedTheta, DynamicTheta, AutoTheta	theta/
Multi-Seasonal	MFLES, MSTL, TBATS (+ Auto variants)	multi-seasonal/
Intermittent Demand	Croston, CrostonSBA, ADIDA, IMAPA, TSB	intermittent/
Distributional	Laplace (variants: auto, auto_aid, skaters)	distributional/laplace.md
Global / Panel	GlobalETS, GlobalTheta, GlobalCroston (via )

📦 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.