duckboost

Experimental DuckDB extension for SQL-native gradient boosting: train dependency-free histogram gradient-boosted decision trees (“GBDT”) inside DuckDB (backend = 'native'), evaluate and inspect in SQL, export pure SQL for orbital-style inference, and optionally import XGBoost / LightGBM / CatBoost dumps when you need their full toolbox.

Documentation: https://javorraca.github.io/duckboost/ · Getting started · Native trainer vignette · Changelog

Standalone extension-template repository targeting DuckDB 2.0.0 LTS (“Cinnamon Teal”). DuckDB 1.x clients cannot load it. Listed on Community Extensions.

Motivation

Orbital converts trained sklearn / tidymodels pipelines into SQL so scoring needs no Python runtime. duckboost brings a similar loop fully into DuckDB:

1. Train with the built-in native histogram trainer (or an optional vendor backend / imported dump)
2. Evaluate fit quality and feature importance in SQL
3. Export model trees to DuckDB SQL (CASE WHEN ensembles, optional separate_trees)
4. Score with either duckboost_predict or the exported SQL (no extension required at inference time)

Try it

On DuckDB 2.0.0+:

INSTALL duckboost FROM community;
LOAD duckboost;

Then train a first model (HTTPS CSV; httpfs autoloads):

CREATE TABLE penguins AS
SELECT
	body_mass_g::DOUBLE AS body_mass_g,
	[bill_length_mm, bill_depth_mm, flipper_length_mm, (sex = 'male')::DOUBLE] AS features
FROM read_csv(
	'https://raw.githubusercontent.com/allisonhorst/palmerpenguins/master/inst/extdata/penguins.csv',
	nullstr = 'NA'
)
WHERE body_mass_g IS NOT NULL
	AND bill_length_mm IS NOT NULL
	AND sex IS NOT NULL;

CREATE TABLE models AS
SELECT duckboost_train(
	body_mass_g, features,
	MAP {
		'backend': 'native',
		'n_estimators': '40',
		'max_depth': '3',
		'learning_rate': '0.15',
		'feature_names': 'bill_length_mm,bill_depth_mm,flipper_length_mm,male'
	}
) AS model
FROM penguins;

SELECT round(duckboost_evaluate_agg(
	model, body_mass_g, features, MAP {'metric': 'rmse'}
), 1) AS rmse_g
FROM models, penguins;

More detail: Getting started.

Build from source (optional)

For contributors or custom builds you need git, a C++17 compiler, cmake, and make (optional: ninja):

git clone --recurse-submodules https://github.com/JavOrraca/duckboost.git
cd duckboost
make
./build/release/duckdb -unsigned

LOAD duckboost;
SELECT * FROM duckboost_backends();

Offline CSV: read_csv('data/penguins.csv', nullstr = 'NA'). Descriptor / republish notes: docs/COMMUNITY_PUBLISH.md.

SQL API

LOAD duckboost;

-- Available backends
SELECT * FROM duckboost_backends();

-- Train (native histogram GBDT; 'reference' remains a compatible alias)
CREATE TABLE models AS
SELECT duckboost_train(
	y,
	[x1, x2],
	MAP {
		'backend': 'native',
		'task': 'regression',
		'n_estimators': '20',
		'max_depth': '3',
		'learning_rate': '0.1',
		'feature_names': 'x1,x2'
	}
) AS model
FROM train;

-- In-process predict
SELECT duckboost_predict(model, [x1, x2]) FROM models, test;

-- Dataset-level metrics
SELECT duckboost_evaluate_agg(model, y, [x1, x2], MAP {'metric': 'rmse'})
FROM models, test;

-- Orbital-style SQL export (trees as separate columns for DuckDB parallelism).
-- Literals use e0 so DuckDB binds them as DOUBLE, not DECIMAL.
SELECT duckboost_to_sql(
	model,
	'test',
	['x1', 'x2'],
	MAP {'separate_trees': 'true', 'prediction_alias': 'pred'}
) FROM models;

-- Import a vendor dump trained outside DuckDB
SELECT duckboost_import('xgboost', xgb_dump_json, MAP {'task': 'binary', 'base_score': '0.0'});
SELECT duckboost_import('lightgbm', lgb_model_txt);
SELECT duckboost_import('catboost', catboost_model_json);

-- Table macros (query_table wrappers; pass table names as VARCHAR literals)
CREATE TABLE models AS
FROM duckboost_fit('train', y, [x1, x2], options := MAP {'n_estimators': '20', 'feature_names': 'x1,x2'});

SELECT * FROM duckboost_score((SELECT model FROM models), 'test', [x1, x2]);

-- Multiclass: labels are class indices 0 .. n_classes - 1 (n_classes inferred, or set 'n_classes')
CREATE TABLE mc_models AS
SELECT duckboost_train(label, features, MAP {'task': 'multiclass', 'n_estimators': '20'}) AS model
FROM mc_train;

-- predict returns argmax class index; predict_proba returns softmax LIST
SELECT duckboost_predict(model, features), duckboost_predict_proba(model, features) FROM mc_models, mc_test;

-- Robust and asymmetric regression losses (reference backend)
SELECT duckboost_train(y, features, MAP {'objective': 'absolute_error'}) AS mae_model FROM train;
SELECT duckboost_train(
	y, features, MAP {'objective': 'quantile', 'quantile_alpha': '0.9'}
) AS q90_model FROM train;
SELECT duckboost_train(
	y, features, MAP {'objective': 'expectile', 'tau': '0.9'}
) AS e90_model FROM train;

-- Loss-guided (leaf-wise) growth spends a fixed leaf budget on the best available split
SELECT duckboost_train(
	y, features, MAP {'growth_policy': 'lossguide', 'max_leaves': '31'}
) AS leafwise_model FROM train;

-- Oblivious / symmetric trees share one split per depth (CatBoost-style)
SELECT duckboost_train(
	y, features, MAP {'growth_policy': 'oblivious', 'max_depth': '4'}
) AS oblivious_model FROM train;

-- Sample weights / class_weight, early stopping, and feature importance
SELECT duckboost_train(y, features, weight, MAP {
	'task': 'binary',
	'class_weight': 'balanced',
	'n_estimators': '50',
	'early_stopping_rounds': '5',
	'validation_fraction': '0.2',
	'reg_lambda': '1.0',
	'seed': '42'
}) AS model FROM train;

-- Or pass an external validation mask (e.g. from duckboost_initial_validation_split)
SELECT duckboost_train(
	y, features, split = 'validation',
	MAP {'early_stopping_rounds': '5', 'n_estimators': '50', 'seed': '42'}
) AS model
FROM data
WHERE split IN ('train', 'validation');

SELECT * FROM duckboost_importance(model);  -- variable, gain, cover, frequency, importance

Feature NULLs are treated as missing (NaN): the reference trainer learns an XGBoost-style default_left direction per split, and duckboost_to_sql emits matching IS NULL OR isnan(...) branches.

Example datasets

Site examples prefer public HTTPS CSVs. For offline SQL from a checkout, data/ still has thin copies:

LOAD duckboost;
CREATE TABLE iris AS FROM read_csv('data/iris.csv');                          -- 150 rows
CREATE TABLE penguins AS FROM read_csv('data/penguins.csv', nullstr = 'NA');  -- 344 rows

-- Binary: is a (non-setosa) iris virginica?
CREATE TABLE iris_binary AS
SELECT (species = 'virginica')::DOUBLE AS y,
       [sepal_length, sepal_width, petal_length, petal_width] AS features
FROM iris WHERE species <> 'setosa';

SELECT duckboost_evaluate_agg(model, y, features, MAP {'metric': 'accuracy'})
FROM (SELECT duckboost_train(y, features, MAP {'task': 'binary'}) AS model FROM iris_binary), iris_binary;

The example datasets vignette walks through train/validation/test splits, one-hot encoding, regression, binary classification, and a SQL grid search on both files.

Splitting and preprocessing

Small macros borrow the ideas (not the code) of tidymodels' rsample and recipes:

-- Split: adds split = 'train' | 'test' (or 'train' | 'validation' | 'test'), or fold = 1 .. v
CREATE TABLE penguins_split AS
FROM duckboost_initial_split('penguins', prop := 0.75, strata := species, seed := 42);
FROM duckboost_initial_validation_split('penguins', prop := [0.6, 0.2], strata := species);
FROM duckboost_vfold('penguins_train', v := 5, strata := species);

-- Preprocess: learn values on training rows only ("prep") ...
CREATE TABLE prep AS
SELECT median(bill_length_mm) AS bill_length_mm, duckboost_levels(island) AS island_levels
FROM penguins_split WHERE split = 'train';

-- ... then apply them to every row ("bake")
SELECT list_concat(
         [coalesce(s.bill_length_mm, p.bill_length_mm)],
         duckboost_dummy(s.island, p.island_levels, one_hot := true)) AS features,
       duckboost_dummy_names('island', p.island_levels, one_hot := true) AS island_names
FROM penguins_split s, prep p;

Also duckboost_integer(x, levels) (integer encoding) and duckboost_other(x, levels) (pool rare levels). The splitting and preprocessing vignette explains train/test versus train/validation/test versus cross-validation, and walks through a complete example.

Backends

Backend	Train in this build	Dump import	Predict / evaluate / to_sql
native (alias reference)	Yes (built-in histogram GBDT; JSON still says "reference")	duckboost JSON	Yes
xgboost	Optional (DUCKBOOST_WITH_XGBOOST)	dump_model(..., dump_format='json')	Yes
lightgbm	Optional (DUCKBOOST_WITH_LIGHTGBM)	booster_.save_model() text	Yes
catboost	Optional (DUCKBOOST_WITH_CATBOOST)	save_model(..., format='json') float trees	Yes

Lead with backend='native' for dependency-free training. Opt into vendor C API linking with DUCKBOOST_WITH_* when you want XGBoost / LightGBM inside the same process; prefer duckboost_import() when you already train outside DuckDB. Use cases and choosing a trainer and the native trainer vignette compare the paths and publish small holdout benchmarks.

Dump import notes

• Imported models set learning_rate = 1.0. XGBoost and LightGBM dump leaf values already include their learning-rate shrinkage, so they are imported unchanged; CatBoost scale_and_bias is applied at import.
• Unknown XGBoost dump fields and LightGBM tree fields fail loudly (no silent skips). Named XGBoost dumps require feature_names in scoring-column order; positional f0..fN dumps do not.
• XGBoost categorical splits (split_condition arrays) import as EQUAL/IN nodes with category_cast=xgboost (trunc(x) for x >= 0). Pass config from Booster.save_config() to recover base_score / objective / num_class.
• LightGBM numerical <= splits preserve default_left and missing_type (None, Zero, or NaN) while converting to duckboost < via nextafter(threshold, +∞). Categorical bitsets import as IN set-membership nodes with category_cast=lightgbm (trunc(x) membership). Unsupported objectives (poisson, multiclassova, ranking, RF average_output, linear trees) are rejected.
• CatBoost support: FloatFeature, OneHotFeature, and OnlineCtr (Counter/Borders). CTR combinations may include cat_feature_value, float_feature, and cat_feature_exact_value. Pass categorical CityHash values as numeric features.
• Multiclass CatBoost JSON uses class-blocked leaf_values (2^depth values per class). Import expands each oblivious tree into n_classes duckboost trees (layout [round][class]).
• OnlineCtr requires ctr_data in the dump (save_model(..., pool=...)). duckboost_to_sql inlines CTR hash lookups via UHUGEINT modular arithmetic.
• Optional import map keys: task, base_score, learning_rate, feature_names, n_classes, config (XGBoost save_config() JSON; mutually exclusive with base_score).

Model format

Models are opaque VARCHAR JSON documents. ToJSON writes the lowest format version that can represent the model (1 for plain LESS/NaN/L2 trees; 2 when the model uses IN splits, non-NaN missing_type, non-L2 objectives, or non-exact category_cast). FromJSON rejects versions newer than this build supports.

{
  "duckboost_version": 1,
  "backend": "reference",
  "task": "regression",
  "base_score": 0.0,
  "learning_rate": 0.1,
  "n_features": 2,
  "n_classes": 1,
  "feature_names": ["x1", "x2"],
  "trees": [{"nodes": [{"is_leaf": false, "feature": 0, "threshold": 1.5, "left": 1, "right": 2, "value": 0.0}, ...]}]
}

For task: "multiclass", n_classes >= 2, optional base_scores holds per-class bias, and trees are stored as round * n_classes + class. duckboost_predict returns the argmax class index; duckboost_to_sql exports an argmax over per-class score expressions. Pass keep_columns (comma-separated names or *) to retain identity columns, and proba=true for binary/multiclass probability columns beside the prediction.

Split nodes may use "compare":"equal" with threshold, or "compare":"in" with a sorted, unique categories array. Equality and membership matches route right; non-matches route left. Optional "category_cast":"lightgbm"|"xgboost" controls fractional-category truncation.

Design notes

• Layout: standalone community extension (extension-template) so optional vendor ML libraries stay out of core DuckDB builds. The duckdb and extension-ci-tools git submodules stay (required for local make); duckdb is pinned to v2.0.0.
• SQL export mirrors orbital's separate_trees idea so DuckDB can evaluate ensemble members as independent columns.
• Native histogram trainer (backend='native', alias 'reference') is a dependency-free GBDT: global quantile pre-binning, ordered target-statistic categorical partitions (EQUAL/IN), and depth-wise, loss-guided, or oblivious/symmetric growth. It supports squared-error, absolute-error, quantile, expectile, logistic, and softmax losses plus missing-value defaults, L1/L2/gamma, sample weights, and early stopping. Set categorical_features to comma-separated feature names or zero-based indices. It buffers rows and bin ids in memory, finds continuous splits from those histograms (depth-wise, loss-guided, and oblivious/symmetric), trains single-threaded, and is aimed at SQL-native workflows rather than replacing production XGBoost/LightGBM/CatBoost at every scale. Model JSON still serializes "backend":"reference" for compatibility. duckboost_predict caches a parsed model within each chunk when the JSON repeats; for large scoring jobs prefer duckboost_to_sql.
• Native LightGBM maps absolute_error / quantile / lossguide / categorical_features / subsample (bagging_freq=1) / seed. Native XGBoost focuses on L2 depth-wise trees (plus seed / max_bin / sample weights / SaveJsonConfig intercept). Both native backends honor early_stopping_rounds with either validation_fraction (random hold-out) or a BOOLEAN is_validation column (LightGBM uses early_stopping_round, XGBoost evaluates each iter and truncates the dump). class_weight is folded into sample weights.
• Table macros duckboost_fit / duckboost_score wrap duckboost_train / duckboost_predict with query_table for a compact SQL workflow. duckboost_fit accepts optional weight and is_validation (for example after ).

Build from source (developers)

The Try it steps above are enough to experiment. Extra knobs for contributors:

make test
# optional faster builds
GEN=ninja make

# Link real XGBoost / LightGBM (pip wheels work; set ROOT or rely on ~/.local)
EXT_FLAGS='-DDUCKBOOST_WITH_XGBOOST=ON -DDUCKBOOST_WITH_LIGHTGBM=ON' make release

# Compile #ifdef paths without linking vendor libraries
EXT_FLAGS='-DDUCKBOOST_WITH_XGBOOST=ON -DDUCKBOOST_NATIVE_STUB_ONLY=ON' make release

CatBoost has no public in-process training C API — use duckboost_import('catboost', ...).

SELECT * FROM duckboost_build_info();
SELECT * FROM duckboost_backends();

./build/release/duckdb and make test have duckboost statically linked, so LOAD duckboost; is all they need, and a path LOAD there uses the built-in copy. The .duckdb_extension file is for a separate DuckDB 2.0 shell built from the same duckdb submodule commit:

/path/to/other/duckdb -unsigned

LOAD '/path/to/duckboost/build/release/extension/duckboost/duckboost.duckdb_extension';

Native train tests (linked builds only). GitHub Actions workflow Native trainers builds with the pinned pip wheels and runs the same test:

export LD_LIBRARY_PATH="$HOME/.local/lib/python3.12/site-packages/xgboost/lib:$HOME/.local/lib/python3.12/site-packages/lightgbm/lib:${LD_LIBRARY_PATH}"
DUCKBOOST_NATIVE_TRAIN_TEST=1 make test T=test/sql/duckboost/native_train.test

Before opening a PR that touches C++ sources, run make format-check (or make format-fix) so CI Format Check stays green. Before a community submission, also run make check-extension-metadata so the loadable binary footer carries v2.0.0.

See PACKAGING.md for the community-extension descriptor and native-trainer flags.

Roadmap

• Native trainer scaffolding behind DUCKBOOST_WITH_* / DUCKBOOST_NATIVE_STUB_ONLY + duckboost_build_info()
• Vendor C API bridges for XGBoost / LightGBM (train → dump → import into BoostModel)
• Standalone extension-template repository (Makefile, extension_config.cmake, docs/community_extensions_description.yml)
• Split helpers (train/test, train/validation/test, v-fold) and preprocessing helpers (dummy/one-hot, integer encoding, rare-level pooling)
• Missing-value aware splits, sample weights / class_weight, early stopping, regularization, and duckboost_importance
• Fail-loud vendor imports, categorical cast semantics, model format v2, deployable to_sql (keep_columns / proba), vendor parity fixtures
• Native-linked CI, native early stopping, LightGBM kZeroThreshold predict parity
• Histogram pre-binning, ordered-TS categoricals, oblivious growth, vendor-free quality probes
• Docs rebrand: backend='native' alias, Pages refresh, native-trainer vignette + holdout benchmarks
• Medium-scale native vs LightGBM holdout benchmark (scripts/native_benchmark/)
• Community descriptor readiness (penguins hello_world, current repo.ref, no extra toolchain)
• Cut 0.0.4 against DuckDB 2.0.0 LTS
• Bump repo.ref to the v0.0.4 commit (b775ef4)
• Cut : emits literals so DuckDB 2.0 does not parse them as