Use cases and choosing a trainer

duckboost puts gradient-boosted trees inside DuckDB. A model is trained with a SQL aggregate, stored as a JSON string in a table, scored with a SQL function, and can be exported as a plain SQL query that needs no extension at all. This page explains when that is useful and which training path to pick.

Who it is for

People who already work in SQL. Analysts and analytics engineers whose data and transformations live in DuckDB, dbt, or SQL scripts can fit a model without moving to Python or R, and without keeping two copies of the feature logic.

Systems that have DuckDB but no Python runtime. A scheduled job, a dbt model, a CLI tool, a desktop app that embeds DuckDB, or a CI check can train and score with the same binary it already ships.

Teams that train elsewhere but want to score in the database. Keep training in Python or R with XGBoost, LightGBM, or CatBoost, export the model, and load it with duckboost_import. Scoring then happens next to the data, the way orbital does for scikit-learn and tidymodels pipelines.

Common use cases

Use case What duckboost provides
Batch scoring inside a warehouse or lakehouse duckboost_predict over a table, or duckboost_to_sql for a SELECT any DuckDB session can run.
Scoring where no ML runtime is allowed Exported SQL is only CASE expressions and arithmetic.
Retraining as a pipeline step CREATE TABLE models AS SELECT duckboost_train(...) in the same scheduled SQL that builds features.
One model per group GROUP BY trains a separate model per store, region, or segment (example below).
Hyperparameter search in SQL Cross join a grid of settings and GROUP BY it; see the example datasets vignette.
Models as data Models are JSON strings in ordinary tables — versioned, diffed, joined, audited with SQL.
Quick baselines during exploration A few lines of SQL give a boosted-tree baseline and holdout metrics.
Teaching and reproducible examples The native trainer is deterministic and dependency-free.
Local ticket / text NLP with other extensions Pair community quackformers embeddings with duckboost multiclass — see Local ticket NLP.

Example: one model per group

LOAD duckboost;
CREATE TABLE penguins AS
SELECT
  species,
  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 sex IS NOT NULL AND bill_length_mm IS NOT NULL;

CREATE TABLE models AS
SELECT
  species,
  count(*) AS n_rows,
  duckboost_train(body_mass_g, features, MAP {
    'backend': 'native',
    'n_estimators': '50',
    'max_depth': '2'
  }) AS model
FROM penguins
GROUP BY species;

SELECT
  species,
  any_value(n_rows) AS n_rows,
  round(duckboost_evaluate_agg(model, body_mass_g, features, MAP {'metric': 'rmse'}), 1) AS training_rmse_g
FROM models JOIN penguins USING (species)
GROUP BY species, model
ORDER BY species;
┌───────────┬────────┬─────────────────┐
│  species  │ n_rows │ training_rmse_g │
├───────────┼────────┼─────────────────┤
│ Adelie    │ 146    │ 233.3           │
│ Chinstrap │ 68     │ 181.0           │
│ Gentoo    │ 119    │ 201.4           │
└───────────┴────────┴─────────────────┘

When duckboost is not the right tool

  • Data larger than memory. duckboost_train materializes every training row (and bin ids) before it fits. DuckDB can scan more than that; the training step cannot stream.
  • Vendor-only features. Monotonic constraints, interaction constraints, GPU training, GOSS/EFB, and CatBoost ordered CTRs are not exposed through duckboost_train. Train with the vendor library and import the result.
  • Huge production ensembles. The native trainer is single-threaded and aimed at SQL pipelines and moderate sizes. For tens of millions of rows or latency-critical HPO, prefer LightGBM/XGBoost outside DuckDB (or a vendor-linked build) and import.
  • Deep explanation tooling. duckboost_importance covers gain / cover / frequency. There is no SHAP API; use the vendor library if you need it.

Choosing a trainer

All three paths produce the same model JSON, so scoring and SQL export behave the same whichever you use.

  1. Native histogram trainer (backend = 'native', alias 'reference'). Built into every duckboost build. Default.
  2. XGBoost or LightGBM inside DuckDB (backend = 'xgboost' or 'lightgbm'). Needs a build with DUCKBOOST_WITH_XGBOOST / DUCKBOOST_WITH_LIGHTGBM. Check duckboost_backends().
  3. Train outside DuckDB, then import. Full vendor API; load with duckboost_import. See Import model dumps.
Native (native / reference) XGBoost / LightGBM inside DuckDB Train outside, then import
Extra dependencies None Vendor shared libraries at build and run time Python or R with the vendor package
Where the data goes Stays in the DuckDB process Stays in-process (copied into the vendor matrix) Exported out of DuckDB and back
Tasks Regression, binary, multiclass Regression, binary, multiclass Anything the vendor supports that duckboost can import
Growth policies Depth-wise, loss-guided, oblivious / symmetric LightGBM: depth-wise or loss-guided; XGBoost: depth-wise Full vendor API
Categoricals Ordered target-statistic EQUAL / IN partitions LightGBM Dataset categoricals; XGBoost via import Yes (including CatBoost CTR on import)
Regularization & sampling L1 / L2 / gamma, row & column subsample, class_weight Mapped subset of vendor knobs Fully configurable
Early stopping early_stopping_rounds + validation_fraction or is_validation Same options / mask (mapped to vendor APIs) Yes
Missing values Per-split default_left (NaN) Vendor semantics on import / native train Vendor semantics
Speed Single-threaded histogram trainer Multithreaded vendor histograms Fast
Determinism Deterministic for a fixed seed and option map Repeatable in practice with the seeds duckboost passes Depends on your seeds
CatBoost train No No public C API Yes via import

Use the native trainer when

  • You want zero setup: any duckboost build, no libraries to install or version-match.
  • Training must stay inside SQL (scheduled jobs, embedded apps, CI, dbt).
  • The data is small to moderate and a strong SQL-native baseline is enough.
  • You want categoricals, early stopping, regularization, or oblivious trees without leaving DuckDB.

Use XGBoost or LightGBM inside DuckDB when

  • You want a proven vendor algorithm and more speed, but still one SQL statement over in-process data.
  • You control the build and can ship the vendor libraries with it.

Train outside DuckDB and import when

  • The model needs the vendor’s full toolbox (constraints, GPU, CatBoost CTRs, large-scale HPO).
  • Your team already trains and validates in Python or R.
  • You only need DuckDB for scoring.

A common pattern: start with backend = 'native' to prove the features and SQL pipeline, then switch the backend or import a vendor model once the model must be production-critical. Scoring and duckboost_to_sql stay the same.

For a full walkthrough of the native path (categoricals, early stopping, importance, SQL export) and small published benchmarks, see the Native trainer vignette.