duckboost: Native gradient boosting trainer in DuckDB
Gradient-Boosted Trees Are One INSTALL Away
Here is the whole setup. You need DuckDB 2.0.0 or newer, and then two lines:
INSTALL duckboost FROM community;
LOAD duckboost;And here is the smallest train-and-predict loop I know how to write, in fewer than 15 lines of SQL. The data is a table of 50 rows with one feature and a perfectly linear target.
CREATE TABLE tiny AS
SELECT i::DOUBLE AS x, (2 * i + 1)::DOUBLE AS y FROM range(50) t(i);
CREATE TABLE tiny_model AS
SELECT duckboost_train(y, [x], MAP {'backend': 'native', 'n_estimators': '50', 'feature_names': 'x'}) AS model
FROM tiny;
SELECT x, y, round(duckboost_predict(model, [x]), 2) AS pred
FROM tiny_model, tiny
WHERE x IN (5, 25, 45)
ORDER BY x;┌────────┬────────┬────────┐
│ x │ y │ pred │
│ double │ double │ double │
├────────┼────────┼────────┤
│ 5.0 │ 11.0 │ 11.19 │
│ 25.0 │ 51.0 │ 51.0 │
│ 45.0 │ 91.0 │ 90.78 │
└────────┴────────┴────────┘duckboost_train() is an aggregate, so it behaves like sum() or avg(): many rows in, one value out. That value is the model, stored as a JSON string. duckboost_predict() takes the string plus a list of features and returns a number.
Features always go in as a DOUBLE[] list, which is why you see [x] even with a single column. Everything else in this post is a variation on those two functions.
duckboost is the DuckDB extension I maintain. It trains histogram gradient-boosted decision trees (GBDT, the model family behind XGBoost, LightGBM, and CatBoost) with a built-in trainer, evaluates them in SQL, and exports a trained model as a plain SQL query.
It reached the community extensions repository on October 23, 2026 through community-extensions PR #3236. The current release is v0.0.5, under an MIT license.
Thank you to the DuckDB team and the maintainers of DuckDB community extensions. Their build pipeline compiles the extension for each platform, and that is the reason the install is one line.
What DuckDB 2.0 Changes for This
DuckDB 2.0.0 LTS, codenamed “Cinnamon Teal”, shipped on October 21, 2026. The announcement calls it the team’s biggest feature release so far. The headline items are a polished Quack protocol with the new CONNECT statement, asynchronous I/O, a first-class VARIANT type, triggers, agent mode in the CLI, and a PEG-based SQL parser that is now the default.
None of those are what duckboost builds on. Three smaller facts from the announcement and the extension docs matter for this post:
- DuckDB 2.0 is an LTS release with support until at least October 2027, and duckboost v0.0.5 targets it. DuckDB 1.x clients cannot load the extension.
- The old single-arrow lambda syntax is disabled by default. The one lambda below uses the
lambda p:form. - DuckDB versions before 2.0 cannot read database files created by 2.0. The model store in this post is a
.duckdbfile, so read the migration guide before you point an older client at it.
The Data: Palmer Penguins
Both models in this post use the Palmer penguins data, measurements of 344 adult foraging penguins from three species near Palmer Station, Antarctica. Dr. Kristen Gorman and the Palmer Station Antarctica LTER collected the data. Allison Horst, Alison Hill, and Kristen Gorman packaged it as the R package palmerpenguins, which is also on CRAN. The data is released under a CC0 license, and the package asks you to cite it like this:
Horst AM, Hill AP, Gorman KB (2020). palmerpenguins: Palmer Archipelago (Antarctica) penguin data. R package version 0.1.0. doi: 10.5281/zenodo.3960218
You do not need R for the SQL examples. DuckDB reads the CSV file from the package’s GitHub repository over HTTPS, and nullstr = 'NA' turns R’s missing-value marker into a proper NULL. That is the load I ran for every result below. I also exported palmerpenguins::penguins from R to a CSV and compared it with the GitHub file using EXCEPT in both directions. Both have 344 rows and neither has a row the other lacks.
The model needs complete rows and numeric features, so the first queries count the missing values, drop those rows, and encode two columns. sex becomes an is_male column of 1.0 and 0.0, and species also gets a class index (Adelie 0, Chinstrap 1, Gentoo 2) that the classification section uses.
-- Palmer penguins straight from the palmerpenguins GitHub repo, read over HTTPS
CREATE TABLE penguins_raw AS
FROM read_csv(
'https://raw.githubusercontent.com/allisonhorst/palmerpenguins/master/inst/extdata/penguins.csv',
nullstr = 'NA'
);
-- How many rows survive the NA handling?
SELECT count(*) AS total_rows,
count(*) FILTER (WHERE body_mass_g IS NULL OR bill_length_mm IS NULL
OR bill_depth_mm IS NULL OR flipper_length_mm IS NULL
OR sex IS NULL) AS rows_with_na,
count(*) FILTER (WHERE body_mass_g IS NOT NULL AND bill_length_mm IS NOT NULL
AND bill_depth_mm IS NOT NULL AND flipper_length_mm IS NOT NULL
AND sex IS NOT NULL) AS rows_kept
FROM penguins_raw;
-- Keep complete rows. Features must be numbers, so sex becomes 0/1 and species becomes 0/1/2.
CREATE TABLE penguins AS
SELECT row_number() OVER () AS id,
species,
CASE species WHEN 'Adelie' THEN 0 WHEN 'Chinstrap' THEN 1 WHEN 'Gentoo' THEN 2 END::DOUBLE AS species_id,
bill_length_mm,
bill_depth_mm,
flipper_length_mm::DOUBLE AS flipper_length_mm,
body_mass_g::DOUBLE AS body_mass_g,
(sex = 'male')::DOUBLE AS is_male
FROM penguins_raw
WHERE body_mass_g IS NOT NULL AND bill_length_mm IS NOT NULL
AND bill_depth_mm IS NOT NULL AND flipper_length_mm IS NOT NULL
AND sex IS NOT NULL;┌────────────┬──────────────┬───────────┐
│ total_rows │ rows_with_na │ rows_kept │
│ int64 │ int64 │ int64 │
├────────────┼──────────────┼───────────┤
│ 344 │ 11 │ 333 │
└────────────┴──────────────┴───────────┘That is 11 incomplete rows out of 344, and I drop them in the open rather than let a model quietly skip them. It leaves 333 penguins to work with.
Multivariate Regression in Plain SQL
The first model predicts body_mass_g, a penguin’s weight in grams, from bill length, bill depth, flipper length, and sex.
For the split I use duckboost_initial_split(), a table macro that adds a split column holding 'train' or 'test'. It borrows the idea (not the code) of rsample’s initial_split(). The assignment is a hash of the row values plus the seed, so the same rows and seed give the same split. I stratify on species, so each species lands in train and test in about the same proportions, and I use one split for both models in this post.
-- 75/25 train/test split, stratified by species and reproducible with a seed
CREATE TABLE penguins_split AS
FROM duckboost_initial_split('penguins', prop := 0.75, strata := species, seed := 42);
SELECT split, count(*) AS n, round(avg(body_mass_g), 1) AS mean_body_mass_g
FROM penguins_split GROUP BY split ORDER BY split;┌─────────┬───────┬──────────────────┐
│ split │ n │ mean_body_mass_g │
│ varchar │ int64 │ double │
├─────────┼───────┼──────────────────┤
│ test │ 83 │ 4213.3 │
│ train │ 250 │ 4205.0 │
└─────────┴───────┴──────────────────┘That is 250 training penguins and 83 test penguins, with similar average body mass on both sides. I like to see that before I train anything.
Now the model. Options travel in a MAP of strings, so numbers are quoted. Unknown keys raise an error instead of being ignored, so a typo in 'learning_rate' fails loudly.
'backend': 'native' selects the built-in trainer. The docs describe it as dependency-free, single-threaded, and aimed at SQL-native workflows rather than replacing XGBoost, LightGBM, or CatBoost at every scale. That matches how I would use it. I picked round-number settings (100 trees, depth 3), and a few nearby settings gave similar holdout errors, so I did not go hunting for the best combination.
-- Train on the training rows only
CREATE TABLE reg_models AS
SELECT duckboost_train(
body_mass_g, [bill_length_mm, bill_depth_mm, flipper_length_mm, is_male],
MAP {
'backend': 'native',
'task': 'regression',
'n_estimators': '100',
'max_depth': '3',
'learning_rate': '0.1',
'min_samples_leaf': '5',
'seed': '42',
'feature_names': 'bill_length_mm,bill_depth_mm,flipper_length_mm,is_male'
}
) AS model
FROM penguins_split
WHERE split = 'train';Predicting on the holdout is a cross join between the one-row model table and the test rows. Here are five predictions next to the measured body mass in grams:
-- Predict on the holdout
CREATE TABLE reg_preds AS
SELECT id, species, body_mass_g,
duckboost_predict(model, [bill_length_mm, bill_depth_mm, flipper_length_mm, is_male]) AS pred
FROM reg_models, penguins_split
WHERE split = 'test';
SELECT id, species, body_mass_g, round(pred, 1) AS pred
FROM reg_preds ORDER BY id LIMIT 5;┌───────┬─────────┬─────────────┬────────┐
│ id │ species │ body_mass_g │ pred │
│ int64 │ varchar │ double │ double │
├───────┼─────────┼─────────────┼────────┤
│ 1 │ Adelie │ 3750.0 │ 3943.5 │
│ 3 │ Adelie │ 3250.0 │ 3721.0 │
│ 4 │ Adelie │ 3450.0 │ 3700.7 │
│ 15 │ Adelie │ 4200.0 │ 4254.4 │
│ 22 │ Adelie │ 3550.0 │ 3776.2 │
└───────┴─────────┴─────────────┴────────┘Metrics are ordinary SQL, which I prefer because I can group them by segment later. The baseline column is the RMSE you get by predicting the training mean for every test row. An 83-penguin holdout is small, so read these numbers as a rough guide rather than a precise score.
-- Holdout metrics in plain SQL, next to a "predict the training mean" baseline
WITH train_mean AS (SELECT avg(body_mass_g) AS m FROM penguins_split WHERE split = 'train'),
test_mean AS (SELECT avg(body_mass_g) AS ybar FROM reg_preds)
SELECT
round(sqrt(avg((body_mass_g - pred) ^ 2)), 1) AS rmse,
round(avg(abs(body_mass_g - pred)), 1) AS mae,
round(1 - sum((body_mass_g - pred) ^ 2) / sum((body_mass_g - ybar) ^ 2), 3) AS r2,
round(sqrt(avg((body_mass_g - m) ^ 2)), 1) AS baseline_rmse
FROM reg_preds, train_mean, test_mean;┌────────┬────────┬────────┬───────────────┐
│ rmse │ mae │ r2 │ baseline_rmse │
│ double │ double │ double │ double │
├────────┼────────┼────────┼───────────────┤
│ 314.5 │ 258.0 │ 0.842 │ 791.8 │
└────────┴────────┴────────┴───────────────┘RMSE is the typical size of a prediction miss, in the same units as the target. Here that means grams: the model is typically off by about 315 grams (314.5), while the baseline is typically off by about 790 grams (791.8). For scale, the average penguin in the test rows weighs about 4,200 grams.
The baseline RMSE is what you get if you always predict the average of the training weights, so it is the score to beat. MAE is the average absolute miss, 258.0 grams here. R2 is the share of the variation in body mass that the model explains: 0.842 means about 84%, and 0 means no better than predicting the mean.
The model cuts the typical miss from about 790 grams to about 315 grams. The built-in evaluator should agree with the hand-written SQL, so I check:
-- Same numbers from duckboost's own evaluator
SELECT
round(duckboost_evaluate_agg(model, body_mass_g, [bill_length_mm, bill_depth_mm, flipper_length_mm, is_male],
MAP {'metric': 'rmse'}), 1) AS rmse,
round(duckboost_evaluate_agg(model, body_mass_g, [bill_length_mm, bill_depth_mm, flipper_length_mm, is_male],
MAP {'metric': 'mae'}), 1) AS mae
FROM reg_models, penguins_split
WHERE split = 'test';┌────────┬────────┐
│ rmse │ mae │
│ double │ double │
├────────┼────────┤
│ 314.5 │ 258.0 │
└────────┴────────┘Same RMSE and MAE. The metric option also accepts accuracy, logloss, brier_score, and roc_auc_ovr for classification, which is where we go next.
Multivariate Multi-Class Classification
Multi-class labels must be integer class indices from 0 to n_classes - 1, passed as DOUBLE. The penguins table already maps the species names with a CASE. Set 'task': 'multiclass' and duckboost fits one tree per class per boosting round, so 50 rounds on 3 classes is 150 trees.
This model predicts species from bill length, bill depth, flipper length, and body mass. It uses the same stratified split as the regression, so Adelie, Chinstrap, and Gentoo all appear on both sides.
-- Species as a class index: Adelie = 0, Chinstrap = 1, Gentoo = 2 (already built into the penguins table)
SELECT species, species_id::INT AS species_id, count(*) AS n
FROM penguins GROUP BY ALL ORDER BY species_id;┌───────────┬────────────┬───────┐
│ species │ species_id │ n │
│ varchar │ int32 │ int64 │
├───────────┼────────────┼───────┤
│ Adelie │ 0 │ 146 │
│ Chinstrap │ 1 │ 68 │
│ Gentoo │ 2 │ 119 │
└───────────┴────────────┴───────┘CREATE TABLE clf_models AS
SELECT duckboost_train(
species_id, [bill_length_mm, bill_depth_mm, flipper_length_mm, body_mass_g],
MAP {
'backend': 'native',
'task': 'multiclass',
'n_estimators': '50',
'max_depth': '2',
'learning_rate': '0.15',
'min_samples_leaf': '3',
'seed': '42',
'feature_names': 'bill_length_mm,bill_depth_mm,flipper_length_mm,body_mass_g'
}
) AS model
FROM penguins_split
WHERE split = 'train';duckboost_predict() returns the most likely class index. duckboost_predict_proba() returns a list with one softmax probability per class. SQL lists are 1-based, so class k sits at position k + 1, which matters in the log loss below.
CREATE TABLE clf_preds AS
SELECT id, species, species_id,
duckboost_predict(model, [bill_length_mm, bill_depth_mm, flipper_length_mm, body_mass_g]) AS pred_class,
duckboost_predict_proba(model, [bill_length_mm, bill_depth_mm, flipper_length_mm, body_mass_g]) AS proba
FROM clf_models, penguins_split
WHERE split = 'test';
SELECT id, species, pred_class::INT AS pred_class,
list_transform(proba, lambda p: round(p, 3)) AS proba
FROM clf_preds ORDER BY id LIMIT 5;┌───────┬─────────┬────────────┬─────────────────────┐
│ id │ species │ pred_class │ proba │
│ int64 │ varchar │ int32 │ double[] │
├───────┼─────────┼────────────┼─────────────────────┤
│ 1 │ Adelie │ 0 │ [1.0, 0.0, 0.0] │
│ 3 │ Adelie │ 0 │ [1.0, 0.0, 0.0] │
│ 4 │ Adelie │ 0 │ [1.0, 0.0, 0.0] │
│ 15 │ Adelie │ 1 │ [0.005, 0.995, 0.0] │
│ 22 │ Adelie │ 0 │ [1.0, 0.0, 0.0] │
└───────┴─────────┴────────────┴─────────────────────┘All five rows are Adelie penguins. Four are scored as Adelie with a rounded probability of 1.0. Row 15 is also an Adelie, but the model gave it a probability of about 0.995 for Chinstrap, so it is a confident miss. Keep that row in mind when we get to log loss.
Now the holdout metrics. Accuracy and log loss are one query. The majority-class accuracy is the baseline to beat, meaning what you get by always predicting the most common training species.
SELECT round(avg((pred_class = species_id)::INT), 4) AS accuracy,
round(-avg(ln(greatest(proba[(species_id + 1)::INT], 1e-15))), 4) AS log_loss
FROM clf_preds;
-- Baseline: always predict the most common training species
SELECT round(avg((species_id = (SELECT mode(species_id) FROM penguins_split WHERE split = 'train'))::INT), 4)
AS majority_class_accuracy
FROM clf_preds;┌──────────┬──────────┐
│ accuracy │ log_loss │
│ double │ double │
├──────────┼──────────┤
│ 0.9518 │ 0.1885 │
└──────────┴──────────┘
┌─────────────────────────┐
│ majority_class_accuracy │
│ double │
├─────────────────────────┤
│ 0.4337 │
└─────────────────────────┘Accuracy is 0.9518 against a 0.4337 baseline, and log loss is 0.1885. A confusion matrix is one PIVOT away, with true species in rows and predicted species in columns:
-- Confusion matrix: rows are the true species, columns are the predicted species
PIVOT (
SELECT species AS actual,
CASE pred_class::INT WHEN 0 THEN 'Adelie' WHEN 1 THEN 'Chinstrap' ELSE 'Gentoo' END AS predicted
FROM clf_preds
)
ON predicted USING count(*) GROUP BY actual ORDER BY actual;┌───────────┬────────┬───────────┬────────┐
│ actual │ Adelie │ Chinstrap │ Gentoo │
│ varchar │ int64 │ int64 │ int64 │
├───────────┼────────┼───────────┼────────┤
│ Adelie │ 33 │ 3 │ 0 │
│ Chinstrap │ 1 │ 16 │ 0 │
│ Gentoo │ 0 │ 0 │ 30 │
└───────────┴────────┴───────────┴────────┘The model got 79 of the 83 holdout penguins right. All 30 Gentoo penguins are correct, and the four misses are three Adelie predicted as Chinstrap and one Chinstrap predicted as Adelie. The built-in evaluator reports the same accuracy and log loss, plus a one-vs-rest AUC:
-- duckboost's own evaluator on the same holdout
SELECT
round(duckboost_evaluate_agg(model, species_id, [bill_length_mm, bill_depth_mm, flipper_length_mm, body_mass_g],
MAP {'metric': 'accuracy'}), 4) AS accuracy,
round(duckboost_evaluate_agg(model, species_id, [bill_length_mm, bill_depth_mm, flipper_length_mm, body_mass_g],
MAP {'metric': 'logloss'}), 4) AS log_loss,
round(duckboost_evaluate_agg(model, species_id, [bill_length_mm, bill_depth_mm, flipper_length_mm, body_mass_g],
MAP {'metric': 'roc_auc_ovr'}), 4) AS auc_ovr
FROM clf_models, penguins_split
WHERE split = 'test';┌──────────┬──────────┬─────────┐
│ accuracy │ log_loss │ auc_ovr │
│ double │ double │ double │
├──────────┼──────────┼─────────┤
│ 0.9518 │ 0.1885 │ 0.9975 │
└──────────┴──────────┴─────────┘Accuracy is the share of holdout penguins whose predicted species is right: 0.9518 means about 95 of every 100. The majority-class baseline (0.4337) is the score to beat, and it is what you would get by calling every penguin an Adelie. Log loss punishes confident wrong probabilities, so lower is better, and that Adelie scored as a 0.995 Chinstrap is a big part of the 0.1885. AUC (one-vs-rest) measures how well the predicted probabilities rank the right species above the others: 1.0 is perfect and 0.5 is a coin flip, so 0.9975 is close to perfect.
Saving the Model: As an Object and as SQL
A duckboost model is a VARCHAR containing JSON. The docs call it an opaque document, so a table is a perfectly good model store. I keep the name next to the model, and in a real project I would add a version, a training date, and the holdout metrics as columns too.
-- 1) The model object is a VARCHAR holding JSON, so a table is a model store
CREATE TABLE saved_models AS
SELECT 'penguin_mass_v1' AS model_name, now() AS saved_at, model FROM reg_models
UNION ALL
SELECT 'penguin_species_v1', now(), model FROM clf_models;
SELECT model_name, length(model) AS json_chars, left(model, 60) AS model_prefix
FROM saved_models ORDER BY model_name;┌────────────────────┬────────────┬──────────────────────────────────────────────────────────────┐
│ model_name │ json_chars │ model_prefix │
│ varchar │ int64 │ varchar │
├────────────────────┼────────────┼──────────────────────────────────────────────────────────────┤
│ penguin_mass_v1 │ 140911 │ {"duckboost_version":1,"backend":"reference","task":"regress │
│ penguin_species_v1 │ 127856 │ {"duckboost_version":1,"backend":"reference","task":"multicl │
└────────────────────┴────────────┴──────────────────────────────────────────────────────────────┘Because that table lives in a database file, a new process can reuse the model. I quit the shell, reopened duckdb demo.duckdb, and scored the test penguins again with both stored models:
-- New process, new connection. Load duckboost and reuse the stored model objects.
LOAD duckboost;
CREATE TABLE reg_preds_reloaded AS
SELECT s.id,
duckboost_predict(m.model, [s.bill_length_mm, s.bill_depth_mm, s.flipper_length_mm, s.is_male]) AS pred
FROM saved_models m, penguins_split s
WHERE m.model_name = 'penguin_mass_v1' AND s.split = 'test';
CREATE TABLE clf_preds_reloaded AS
SELECT s.id,
duckboost_predict(m.model, [s.bill_length_mm, s.bill_depth_mm, s.flipper_length_mm, s.body_mass_g]) AS pred_class,
duckboost_predict_proba(m.model, [s.bill_length_mm, s.bill_depth_mm, s.flipper_length_mm, s.body_mass_g]) AS proba
FROM saved_models m, penguins_split s
WHERE m.model_name = 'penguin_species_v1' AND s.split = 'test';
SELECT count(*) AS n_rows,
count(*) FILTER (WHERE a.pred = b.pred) AS identical_rows,
max(abs(a.pred - b.pred)) AS max_abs_diff
FROM reg_preds a JOIN reg_preds_reloaded b USING (id);
SELECT count(*) AS n_rows,
count(*) FILTER (WHERE a.pred_class = b.pred_class AND a.proba = b.proba) AS identical_rows
FROM clf_preds a JOIN clf_preds_reloaded b USING (id);┌────────┬────────────────┬──────────────┐
│ n_rows │ identical_rows │ max_abs_diff │
│ int64 │ int64 │ double │
├────────┼────────────────┼──────────────┤
│ 83 │ 83 │ 0.0 │
└────────┴────────────────┴──────────────┘
┌────────┬────────────────┐
│ n_rows │ identical_rows │
│ int64 │ int64 │
├────────┼────────────────┤
│ 83 │ 83 │
└────────┴────────────────┘All 83 regression predictions are identical to the ones from before the restart, and so are all 83 predicted species. The extension is still needed for this path, because duckboost_predict() does the scoring.
The second form needs no extension at all. duckboost_to_sql() takes the model, a table name, and the feature column names, and returns a SELECT that scores that table. separate_trees computes one column per tree and sums them, keep_columns carries your ID column through, and proba adds one proba_k column per class for classifiers.
Here I write each query to a file. Casting to BLOB and using FORMAT blob writes the text untouched, with no CSV quoting in the way:
-- 2) Export to plain SQL. Views name the holdout rows so the SQL has a table to score.
CREATE VIEW mass_test AS
SELECT id, bill_length_mm, bill_depth_mm, flipper_length_mm, is_male
FROM penguins_split WHERE split = 'test';
CREATE VIEW species_test AS
SELECT id, bill_length_mm, bill_depth_mm, flipper_length_mm, body_mass_g
FROM penguins_split WHERE split = 'test';
COPY (
SELECT duckboost_to_sql(
model, 'mass_test', ['bill_length_mm', 'bill_depth_mm', 'flipper_length_mm', 'is_male'],
MAP {'separate_trees': 'true', 'prediction_alias': 'pred', 'keep_columns': 'id'}
)::BLOB
FROM saved_models WHERE model_name = 'penguin_mass_v1'
) TO 'mass_model.sql' (FORMAT blob);
COPY (
SELECT duckboost_to_sql(
model, 'species_test', ['bill_length_mm', 'bill_depth_mm', 'flipper_length_mm', 'body_mass_g'],
MAP {'separate_trees': 'true', 'prediction_alias': 'pred_class', 'keep_columns': 'id', 'proba': 'true'}
)::BLOB
FROM saved_models WHERE model_name = 'penguin_species_v1'
) TO 'species_model.sql' (FORMAT blob);The regression export is 179,092 bytes of SQL text and the classifier export is 106,733 bytes. That is a lot to read and no trouble to ship.
Now the part that matters. In a fresh process, without loading duckboost, I ran the exported text as-is and compared it with the predictions duckboost_predict() made earlier. The exported query spells every number as a DOUBLE literal such as 0.10000000000000001e0, so DuckDB does the same arithmetic that duckboost_predict() does.
The first query confirms that the extension is not loaded:
-- New process, duckboost is NOT loaded. Only DuckDB's built-in SQL runs from here.
SELECT count(*) FILTER (WHERE extension_name = 'duckboost' AND loaded) AS duckboost_loaded
FROM duckdb_extensions();
SET VARIABLE mass_sql = (SELECT content FROM read_text('mass_model.sql'));
SET VARIABLE species_sql = (SELECT content FROM read_text('species_model.sql'));
CREATE TABLE mass_preds_sql AS FROM query(getvariable('mass_sql'));
CREATE TABLE species_preds_sql AS FROM query(getvariable('species_sql'));┌──────────────────┐
│ duckboost_loaded │
│ int64 │
├──────────────────┤
│ 0 │
└──────────────────┘Then the comparison queries for the regression export and the classifier export:
-- Regression: exported SQL vs. the predictions duckboost_predict made earlier
SELECT count(*) AS n_rows,
count(*) FILTER (WHERE a.pred = b.pred) AS identical_rows,
max(abs(a.pred - b.pred)) AS max_abs_diff
FROM reg_preds a JOIN mass_preds_sql b USING (id);
-- Classification: what the exported query returns
SELECT column_name, column_type FROM (DESCRIBE species_preds_sql);
SELECT count(*) AS n_rows,
count(*) FILTER (WHERE a.pred_class = b.pred_class) AS same_class,
max(greatest(abs(a.proba[1] - b.proba_0),
abs(a.proba[2] - b.proba_1),
abs(a.proba[3] - b.proba_2))) AS max_abs_proba_diff
FROM clf_preds a JOIN species_preds_sql b USING (id);┌────────┬────────────────┬──────────────┐
│ n_rows │ identical_rows │ max_abs_diff │
│ int64 │ int64 │ double │
├────────┼────────────────┼──────────────┤
│ 83 │ 83 │ 0.0 │
└────────┴────────────────┴──────────────┘
┌─────────────┬─────────────┐
│ column_name │ column_type │
│ varchar │ varchar │
├─────────────┼─────────────┤
│ id │ BIGINT │
│ pred_class │ INTEGER │
│ proba_0 │ DOUBLE │
│ proba_1 │ DOUBLE │
│ proba_2 │ DOUBLE │
└─────────────┴─────────────┘
┌────────┬────────────┬────────────────────┐
│ n_rows │ same_class │ max_abs_proba_diff │
│ int64 │ int64 │ double │
├────────┼────────────┼────────────────────┤
│ 83 │ 83 │ 0.0 │
└────────┴────────────┴────────────────────┘The exported regression SQL matched all 83 predictions exactly, with a maximum difference of 0.0. The classifier SQL matched all 83 species and every probability to a maximum difference of 0.0. No extension, no Python, and the same answers.
Importing XGBoost, LightGBM, and CatBoost Dumps
If you already train with XGBoost, LightGBM, or CatBoost, duckboost_import() converts a model dump into the same JSON model. After that, duckboost_predict(), duckboost_predict_proba(), and duckboost_to_sql() all work on it.
The docs list the inputs it reads: XGBoost JSON from dump_model(..., dump_format='json'), LightGBM text from booster_.save_model(), and CatBoost JSON from save_model(..., format='json'). In the community build CatBoost is import-only, and unsupported LightGBM objectives such as poisson are rejected rather than skipped.
-- Shape of the call; swap in your own dump file
SELECT duckboost_import(
'lightgbm',
(SELECT content FROM read_text('lgb_model.txt'))
) AS model;The import vignette has the details for each format, including how learning rate and missing values are handled.
Calling It from R
I live in R most days, and the extension works through the duckdb R package as long as the engine is 2.0.0 or newer. Here the data comes straight from the palmerpenguins package, which is the same data as the CSV above. I ran this with the CRAN duckdb 2.0.0 package and DBI. It writes the data frame into DuckDB, counts the complete rows, trains the body mass model on all of them, and compares average actual and predicted body mass by species. Those predictions are on the training rows, so this shows that the calls work, not how well the model generalizes.
library(DBI)
library(palmerpenguins)
con <- dbConnect(duckdb::duckdb())
invisible(dbExecute(con, "INSTALL duckboost FROM community"))
invisible(dbExecute(con, "LOAD duckboost"))
# The data frame from the palmerpenguins package, straight into DuckDB
dbWriteTable(con, "penguins_raw", as.data.frame(penguins))
dbGetQuery(con, "
SELECT count(*) AS total_rows,
count(*) FILTER (WHERE body_mass_g IS NOT NULL AND bill_length_mm IS NOT NULL
AND bill_depth_mm IS NOT NULL AND flipper_length_mm IS NOT NULL
AND sex IS NOT NULL) AS rows_kept
FROM penguins_raw
")
invisible(dbExecute(con, "
CREATE TABLE penguin_model AS
SELECT duckboost_train(
body_mass_g::DOUBLE,
[bill_length_mm, bill_depth_mm, flipper_length_mm::DOUBLE, (sex = 'male')::DOUBLE],
MAP {'backend': 'native', 'n_estimators': '100', 'max_depth': '3', 'feature_names': 'bill_length_mm,bill_depth_mm,flipper_length_mm,is_male'}
) AS model
FROM penguins_raw
WHERE body_mass_g IS NOT NULL AND bill_length_mm IS NOT NULL AND bill_depth_mm IS NOT NULL
AND flipper_length_mm IS NOT NULL AND sex IS NOT NULL
"))
# Average actual vs predicted body mass by species (training rows, just to show the call)
dbGetQuery(con, "
SELECT species,
round(avg(body_mass_g)) AS avg_g,
round(avg(duckboost_predict(model, [bill_length_mm, bill_depth_mm, flipper_length_mm::DOUBLE, (sex = 'male')::DOUBLE]))) AS avg_pred_g
FROM penguin_model, penguins_raw
WHERE body_mass_g IS NOT NULL AND bill_length_mm IS NOT NULL AND bill_depth_mm IS NOT NULL
AND flipper_length_mm IS NOT NULL AND sex IS NOT NULL
GROUP BY species
ORDER BY species
")
dbDisconnect(con, shutdown = TRUE) total_rows rows_kept
1 344 333
species avg_g avg_pred_g
1 Adelie 3706 3699
2 Chinstrap 3733 3757
3 Gentoo 5092 5087Where I Would Use It
I reach for duckboost when the data already lives in DuckDB, the table is moderate in size, and the model has to travel as SQL. That last part is the point: a scoring query with no Python or R runtime and no extension is easy to review and easy to run in a place where you cannot install anything.
I would not reach for it for very large training sets. The docs are direct about the limits: training is single-threaded, all training rows are materialized in memory, and there are no monotonic constraints. The README also labels the project experimental, and v0.0.5 is early, so pin your versions.
For anything bigger, train with XGBoost, LightGBM, or CatBoost, then import the dump. The inspiration for the SQL export is Posit’s orbital, which converts trained scikit-learn and tidymodels pipelines into SQL, and I wanted a similar loop inside DuckDB.
Learn More
- duckboost on GitHub by me, MIT licensed
- duckboost documentation site, including Getting started, the native trainer vignette, and the train, predict, export vignette
- SQL reference for every function and option used above
- duckboost on DuckDB Community Extensions
- Announcing DuckDB 2.0.0 LTS “Cinnamon Teal” by the DuckDB team
- palmerpenguins by Allison Horst, Alison Hill, and Kristen Gorman, also on CRAN
- DuckDB installation page
- duckboost issues if something breaks
If you try duckboost on a table you already have in DuckDB, I would love to hear how it goes. Open an issue if anything breaks and I will take a look.
Thank you to the early testers of duckboost, including Greg Faletto, who ran into issues along the way and is a duckboost contributor.
Thanks again to the DuckDB team, the community extensions maintainers, Allison Horst, Alison Hill, and Kristen Gorman for the penguins, and the XGBoost, LightGBM, and CatBoost projects whose model formats make the importers possible.
Happy coding!