duckboost

duckboost is an experimental DuckDB extension for SQL-native gradient boosting. Train a histogram gradient-boosted decision trees (“GBDT”) inside DuckDB with zero vendor libraries, score with SQL functions, export trees as ordinary SQL, and optionally import XGBoost / LightGBM / CatBoost dumps when you need their full toolbox.

NoteStatus

Experimental. Builds against the DuckDB 2.0 community alpha pin (v2.0.0-alpha46057). PyPI / CRAN DuckDB 1.x cannot load this extension yet. Community INSTALL waits on the descriptor PR merging into duckdb/community-extensions.

Why train inside the database

Orbital turns an already-trained Python or R pipeline into SQL so production scoring can stay in the warehouse. duckboost pulls the training step into DuckDB as well:

1. Train with the built-in native histogram trainer (or an optional vendor backend / imported dump).
2. Measure fit quality in SQL (duckboost_evaluate_agg, duckboost_importance).
3. Export the trees with duckboost_to_sql (CASE expressions, optionally one column per tree).
4. Score later with duckboost_predict, or run the exported SQL in any DuckDB session — even without the extension.

Native-first, import when you need to

The default path is the native histogram trainer (backend = 'native', also accepted as 'reference' for compatibility):

• Global quantile pre-binning and histogram split finding
• Ordered target-statistic categorical partitions (EQUAL / IN)
• Depth-wise, loss-guided, or oblivious / symmetric growth
• Squared-error, absolute-error, quantile, expectile, logistic, and softmax losses
• Missing-value defaults, L1/L2/gamma, sample weights, early stopping

It is aimed at SQL-native workflows and moderate data sizes, not at replacing LightGBM or XGBoost at every scale. Prefer duckboost_import() when you already train outside DuckDB or need vendor features duckboost does not expose.

Use cases and choosing a trainer · Native trainer vignette · Native vs LightGBM benchmark

Use cases

• SQL-first analysts and pipelines that train, evaluate, and retrain as ordinary queries, including one model per group with GROUP BY.
• Scoring without an ML runtime: duckboost_to_sql turns a model into a plain SELECT for views, dbt models, or any DuckDB session.
• Teams that train in Python or R with XGBoost, LightGBM, or CatBoost and want to score next to the data with duckboost_import.
• Teaching, prototyping, and CI, where the native trainer gives a deterministic model with no extra dependencies.
• Local ticket NLP, by pairing community quackformers embeddings with duckboost (no separate Python scoring loop or hosted embedding API) — vignette.

Practice datasets

Getting Started and the vignettes load small public CSVs over HTTPS (no repo checkout):

Dataset	Rows	Good for
Iris (Rdatasets)	150	Multiclass species, binary setosa detection
Palmer penguins	344	Body-mass regression, binary sex, species classification

Read penguins with nullstr = 'NA'. Getting started fits a first penguins model; the example datasets vignette walks through train/test splits for both files. Offline SQL tests still use thin copies under data/.

What you can do

Piece	What it does
Native GBDT	duckboost_train with backend = 'native' (default) for regression, binary, and multiclass
Dump import	duckboost_import for XGBoost dump_model JSON, LightGBM save_model text, and CatBoost save_model JSON
In-process score	duckboost_predict and duckboost_predict_proba
Metrics & importance	duckboost_evaluate / duckboost_evaluate_agg (incl. brier_score, roc_auc_ovr, roc_auc_ovo), duckboost_importance
SQL export	duckboost_to_sql writes a query that reproduces those predictions

Optional in-process XGBoost and LightGBM training is a compile-time flag (DUCKBOOST_WITH_XGBOOST, DUCKBOOST_WITH_LIGHTGBM). CatBoost has no public training C API, so it is import-only. A default make build ships the native trainer and dump import, not the vendor libraries.

DuckDB 2.0

This repository pins the same DuckDB commit community CI builds against (b15358c / v2.0.0-alpha46057). duckboost is not on the community extension store yet — the descriptor and submission checklist live in the repo (docs/COMMUNITY_PUBLISH.md). Until that PR merges, clone this repo and build the bundled shell — PyPI / CRAN DuckDB 1.x cannot load the extension. After DuckDB’s official v2.0.0 release, community binaries are redeployed under that tag.

Getting started is the step-by-step path: clone → make → first penguins model.