SQL reference

Signatures below match SELECT * FROM duckdb_functions() WHERE function_name LIKE 'duckboost%' on build/release/duckdb, including parameter names. MAP keys come from the parsers in src/model.cpp and src/import.cpp. Table-macro argument types are NULL in duckdb_functions(); the types stated for the table macros are taken from their bodies in src/duckboost_functions.cpp.

Every example is a new in-memory database. LOAD uses the unsigned extension path relative to the repository root.

`duckboost_train`

Aggregate. Returns one model JSON VARCHAR for the whole input.

duckboost_train(y DOUBLE, features DOUBLE[]) -> VARCHAR
duckboost_train(y DOUBLE, features DOUBLE[], options MAP(VARCHAR, VARCHAR)) -> VARCHAR
duckboost_train(y DOUBLE, features DOUBLE[], weight DOUBLE) -> VARCHAR
duckboost_train(y DOUBLE, features DOUBLE[], weight DOUBLE, options MAP(VARCHAR, VARCHAR)) -> VARCHAR
duckboost_train(y DOUBLE, features DOUBLE[], is_validation BOOLEAN) -> VARCHAR
duckboost_train(y DOUBLE, features DOUBLE[], is_validation BOOLEAN, options MAP(VARCHAR, VARCHAR)) -> VARCHAR
duckboost_train(y DOUBLE, features DOUBLE[], weight DOUBLE, is_validation BOOLEAN) -> VARCHAR
duckboost_train(y DOUBLE, features DOUBLE[], weight DOUBLE, is_validation BOOLEAN, options MAP(VARCHAR, VARCHAR)) -> VARCHAR

Option	Aliases	Default	Meaning
`backend`		`reference`	Preferred public name: `native` (built-in histogram GBDT). Stable aliases: `reference`, `duckboost`, `gbdt`. Vendor: `xgboost` / `xgb`, `lightgbm` / `lgbm` / `lgb`, `catboost` / `cb`. Model JSON still serializes the native backend as `"reference"`.
`task`		`regression`	`regression` (`regressor`, `mse`), `binary` (`classification`, `logistic`, `binomial`), `multiclass` (`multi_class`, `multi-class`, `softmax`)
`objective`	`loss`	`squared_error`	Regression loss: `squared_error` (`l2`, `mse`, `reg:squarederror`), `absolute_error` (`mae`, `l1`, `regression_l1`, `reg:absoluteerror`), `quantile` (`pinball`, `reg:quantileerror`), or `expectile`. Legacy task names such as `binary` remain accepted. Non-squared objectives require the native backend and `task=regression`.
`quantile_alpha`	`expectile_alpha`, `tau`	`0.5`	Quantile or expectile level in `(0, 1)`. Only valid with those objectives. `alpha` remains an alias for `reg_alpha`.
`n_estimators`	`num_boost_round`, `iterations`	`10`	Number of boosting rounds. Must be > 0.
`growth_policy`	`grow_policy`	`depthwise`	Tree growth: `depthwise` (`depth_wise`, `levelwise`), loss-guided `lossguide` (`leafwise`, `leaf_wise`, `best_first`), or CatBoost-style `oblivious` (`symmetric`, `depthwise_oblivious`). Oblivious is native-only; lossguide also works with linked LightGBM.
`max_leaves`	`num_leaves`, `max_leaf_nodes`	`31` with `lossguide`	Maximum leaves per loss-guided tree. Must be >= 2 and cannot be set with depth-wise or oblivious growth.
`max_depth`	`depth`	`3`; unlimited with `lossguide` when omitted	Maximum tree depth. Must be > 0 for depth-wise and oblivious growth. Loss-guided growth accepts `0` for unlimited depth.
`learning_rate`	`eta`, `lr`	`0.1`	Must be > 0.
`min_samples_leaf`	`min_data_in_leaf`	`1`	Minimum rows in a leaf.
`min_child_weight`		`0`	Minimum sum of hessians in a child (XGBoost-style).
`max_bins`		`256`	Global quantile histogram bins per continuous feature (computed once from training rows). A feature with at most `max_bins + 1` distinct values keeps every midpoint; otherwise thresholds are thinned to `max_bins` quantiles. LightGBM receives it as `max_bin`.
`reg_lambda`	`lambda`, `lambda_l2`	`0`	L2 regularization on leaf weights.
`reg_alpha`	`alpha`, `lambda_l1`	`0`	L1 regularization on leaf weights.
`min_split_gain`	`gamma`	`0`	Minimum loss reduction required to make a split.
`subsample`	`bagging_fraction`	`1`	Row subsample ratio per tree in `(0, 1]`.
`colsample_bytree`	`colsample`, `feature_fraction`	`1`	Column subsample ratio per tree in `(0, 1]`.
`validation_fraction`	`valid_fraction`	`0`	Random hold-out fraction for early stopping. Defaults to `0.2` when `early_stopping_rounds > 0` and this is unset. Cannot be combined with the `is_validation` argument.
`early_stopping_rounds`	`early_stopping`	`0`	Stop if the hold-out metric does not improve for this many rounds; keep the best iteration.
`seed`	`random_seed`	`0`	RNG seed for subsampling and the random validation hold-out.
`class_weight`	`class_weights`		`balanced` or comma-separated per-class multipliers (binary / multiclass).
`n_classes`	`num_class`, `num_classes`	max label + 1	Multiclass only; ignored for other tasks. Must be >= 2 and greater than every label. Set it when a class is absent from the training rows.
`feature_names`		`f0,f1,...`	Comma-separated names. The count must match the feature width.
`categorical_features`	`cat_features`, `categorical_feature`		Comma-separated feature names or zero-based indices. The native trainer orders categories by local target statistic (`g/h`) and emits `EQUAL` or multi-category `IN` splits. Linked LightGBM maps this to Dataset categoricals; XGBoost / CatBoost train reject it (use import).

Unknown keys raise duckboost: unknown train option. Values are strings inside the MAP.

Optional is_validation marks rows for an external validation set (for example split = 'validation' from duckboost_initial_validation_split). Those rows are held out of fitting and, when early_stopping_rounds > 0, drive early stopping. Do not also set validation_fraction when any row is true. NULL is treated as false; an all-false mask is ignored so validation_fraction still works (including via duckboost_fit defaults).

Feature NULLs become NaN and the native trainer learns a per-split default_left missing direction (same idea as XGBoost). Sample weight must be non-NULL and >= 0.

Regression supports squared-error, absolute-error, quantile, and expectile objectives on the native backend. Absolute-error and quantile trees renew each grown leaf to the weighted median or requested weighted residual quantile. Binary uses logistic loss and requires labels in {0, 1}. duckboost_predict then returns the class probability, not a 0/1 label.

Multiclass uses softmax cross-entropy and requires integer class indices 0, 1, ..., n_classes - 1 as y, so map string classes to integers first (for example with CASE). Each boosting round fits one tree per class, so a model has n_estimators * n_classes trees. Each class’s starting score is the log of its share of the training rows. duckboost_predict returns the most likely class index and duckboost_predict_proba returns one probability per class. The XGBoost and LightGBM trainers apply the same label rules.

LOAD 'build/release/extension/duckboost/duckboost.duckdb_extension';
CREATE TABLE train AS
SELECT i::DOUBLE AS x, (2 * i)::DOUBLE AS y FROM range(12) t(i);
SELECT left(model, 80) AS model_prefix
FROM (
  SELECT duckboost_train(
    y, [x],
    MAP {'task': 'regression', 'n_estimators': '4', 'max_depth': '2', 'feature_names': 'x'}
  ) AS model
  FROM train
);

┌──────────────────────────────────────────────────────────────────────────────────┐
│                                   model_prefix                                   │
├──────────────────────────────────────────────────────────────────────────────────┤
│ {"duckboost_version":1,"backend":"reference","task":"regression","base_score":11 │
└──────────────────────────────────────────────────────────────────────────────────┘

A three-class model: the label is 0, 1, or 2, and the model stores one tree per class per round.

LOAD 'build/release/extension/duckboost/duckboost.duckdb_extension';
CREATE TABLE train AS
SELECT (i // 10)::DOUBLE AS y, [i::DOUBLE] AS features FROM range(30) t(i);
CREATE TABLE models AS
SELECT duckboost_train(
  y, features,
  MAP {'task': 'multiclass', 'n_estimators': '5', 'max_depth': '2'}
) AS model
FROM train;
SELECT
  x,
  duckboost_predict(model, [x]) AS class,
  list_transform(duckboost_predict_proba(model, [x]), lambda p: round(p, 3)) AS proba
FROM models, (VALUES (3.0), (14.0), (27.0)) v(x);

┌──────┬───────┬───────────────────────┐
│  x   │ class │         proba         │
├──────┼───────┼───────────────────────┤
│ 3.0  │ 0.0   │ [0.728, 0.136, 0.136] │
│ 14.0 │ 1.0   │ [0.137, 0.724, 0.139] │
│ 27.0 │ 2.0   │ [0.14, 0.139, 0.721]  │
└──────┴───────┴───────────────────────┘

`duckboost_predict`

Scalar.

duckboost_predict(model VARCHAR, features DOUBLE[]) -> DOUBLE

Regression returns the numeric prediction. Binary returns a probability in (0, 1). Multiclass returns the argmax class index as a double (ties keep the lowest index).

LOAD 'build/release/extension/duckboost/duckboost.duckdb_extension';
CREATE TABLE train AS
SELECT i::DOUBLE AS x, (2 * i)::DOUBLE AS y FROM range(12) t(i);
CREATE TABLE models AS
SELECT duckboost_train(
  y, [x],
  MAP {'n_estimators': '4', 'max_depth': '2', 'feature_names': 'x'}
) AS model
FROM train;
SELECT duckboost_predict(model, [4.0]) AS pred FROM models;

┌────────┐
│  pred  │
├────────┤
│ 9.8225 │
└────────┘

`duckboost_predict_proba`

Scalar.

duckboost_predict_proba(model VARCHAR, features DOUBLE[]) -> DOUBLE[]

Regression returns a one-element list holding duckboost_predict. Binary returns [1 - p, p]. Multiclass returns a softmax over class scores. The multiclass import on Import model dumps checks that those values sum to 1.

LOAD 'build/release/extension/duckboost/duckboost.duckdb_extension';
CREATE TABLE train AS
SELECT i::DOUBLE AS x, CASE WHEN i > 6 THEN 1.0 ELSE 0.0 END AS y
FROM range(12) t(i);
CREATE TABLE models AS
SELECT duckboost_train(
  y, [x],
  MAP {'task': 'binary', 'n_estimators': '8', 'max_depth': '2', 'feature_names': 'x'}
) AS model
FROM train;
SELECT duckboost_predict_proba(model, [10.0]) AS proba FROM models;

┌──────────────────────────────────────────┐
│                  proba                   │
├──────────────────────────────────────────┤
│ [0.2542375876576114, 0.7457624123423886] │
└──────────────────────────────────────────┘

`duckboost_evaluate`

Scalar. One prediction versus one label.

duckboost_evaluate(model VARCHAR, y DOUBLE, features DOUBLE[]) -> DOUBLE
duckboost_evaluate(model VARCHAR, y DOUBLE, features DOUBLE[], options MAP(VARCHAR, VARCHAR)) -> DOUBLE

The only option is metric (auto, rmse, mae, pinball / quantile, expectile, accuracy, logloss, brier_score / brier, roc_auc_ovr / auc_ovr, roc_auc_ovo / auc_ovo). Unknown keys raise duckboost: unknown evaluate option.

What the scalar function returns for each metric:

`metric`	Result on that row
`mae`	Absolute error
`pinball`, `quantile`	Pinball loss at the model’s objective alpha.
`expectile`	Asymmetric squared loss at the model’s objective alpha.
`accuracy`	`1` if the classification is correct, else `0`. Binary treats probability `>= 0.5` as class 1. Multiclass compares the argmax index.
`logloss`	Row log loss. Multiclass uses the probability of the integer label.
`brier_score`, `brier`	Row Brier contribution. Binary: `(y - p)²`. Multiclass: `Σ_c (I(y=c) - p_c)²`.
`rmse`	Squared-error contribution; take the square root after averaging rows.
`roc_auc_ovr`, `auc_ovr`, `roc_auc_ovo`, `auc_ovo`	Not available on the scalar function (dataset-level only). Use `duckboost_evaluate_agg`.

auto selects accuracy for classification and the matching regression loss for regression models.

LOAD 'build/release/extension/duckboost/duckboost.duckdb_extension';
CREATE TABLE train AS
SELECT i::DOUBLE AS x, (2 * i)::DOUBLE AS y FROM range(12) t(i);
CREATE TABLE models AS
SELECT duckboost_train(
  y, [x], MAP {'n_estimators': '4', 'max_depth': '2', 'feature_names': 'x'}
) AS model
FROM train;
SELECT
  duckboost_evaluate(model, y, [x]) AS auto_squared_error,
  duckboost_evaluate(model, y, [x], MAP {'metric': 'mae'}) AS abs_error
FROM models, train
WHERE x = 4;

┌────────────────────┬────────────────────┐
│ auto_squared_error │     abs_error      │
├────────────────────┼────────────────────┤
│ 3.321506249999999  │ 1.8224999999999998 │
└────────────────────┴────────────────────┘

`duckboost_evaluate_agg`

Aggregate. One number for every input row that shares the model.

duckboost_evaluate_agg(model VARCHAR, y DOUBLE, features DOUBLE[]) -> DOUBLE
duckboost_evaluate_agg(model VARCHAR, y DOUBLE, features DOUBLE[], options MAP(VARCHAR, VARCHAR)) -> DOUBLE

metric defaults to auto: the model’s training loss for regression, and accuracy for binary and multiclass. rmse, mae, pinball / quantile, expectile, logloss, and brier_score are dataset means (RMSE is the square root of the mean squared error). accuracy is the fraction of correct rows. Pinball and expectile use the alpha stored in the model.

Classification probability metrics:

`metric`	Meaning
`brier_score`, `brier`	Mean Brier score. Binary uses the common `[0, 1]` form `mean((y - p)²)`. Multiclass uses the original sum-over-classes form on `[0, 2]`.
`roc_auc_ovr`, `auc_ovr`	Macro-averaged one-vs-rest ROC AUC. Binary is ordinary ROC AUC on the positive-class probability.
`roc_auc_ovo`, `auc_ovo`	Macro-averaged one-vs-one ROC AUC (pairwise probabilities `p_a / (p_a + p_b)`). On binary models this matches `roc_auc_ovr`.

ROC AUC needs both classes present in each binary reduction; otherwise evaluate raises. model and y cannot be NULL.

LOAD 'build/release/extension/duckboost/duckboost.duckdb_extension';
CREATE TABLE train AS
SELECT i::DOUBLE AS x, (2 * i)::DOUBLE AS y FROM range(12) t(i);
CREATE TABLE models AS
SELECT duckboost_train(
  y, [x], MAP {'n_estimators': '4', 'max_depth': '2', 'feature_names': 'x'}
) AS model
FROM train;
SELECT duckboost_evaluate_agg(m.model, t.y, [t.x], MAP {'metric': 'rmse'}) AS rmse
FROM models m, train t;

┌───────────────────┐
│       rmse        │
├───────────────────┤
│ 4.675411458274733 │
└───────────────────┘

`duckboost_to_sql`

Scalar. Returns a SELECT that scores table_name.

duckboost_to_sql(model VARCHAR, table_name VARCHAR, feature_columns VARCHAR[]) -> VARCHAR
duckboost_to_sql(model VARCHAR, table_name VARCHAR, feature_columns VARCHAR[], options MAP(VARCHAR, VARCHAR)) -> VARCHAR

Option	Aliases	Default	Meaning
`separate_trees`		`true`	`true` / `1` / `yes` emits one column per tree and sums them. `false` / `0` / `no` inlines a single expression.
`prediction_alias`	`alias`	`prediction`	Output column name.

table_name must be non-empty. feature_columns must name at least as many raw features as the model has. Binary predictions are wrapped in a sigmoid. Multiclass export is an argmax over per-class scores. The train and export vignette runs the generated text without calling duckboost_*.

LOAD 'build/release/extension/duckboost/duckboost.duckdb_extension';
CREATE TABLE train AS
SELECT i::DOUBLE AS x, (2 * i)::DOUBLE AS y FROM range(8) t(i);
CREATE TABLE models AS
SELECT duckboost_train(
  y, [x], MAP {'n_estimators': '2', 'max_depth': '1', 'feature_names': 'x'}
) AS model
FROM train;
SELECT left(duckboost_to_sql(model, 'train', ['x']), 120) AS sql_prefix
FROM models;

┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                        sql_prefix                                                        │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ SELECT ("__duckboost_base" + 0.10000000000000001 * "__duckboost_tree_0" + 0.10000000000000001 * "__duckboost_tree_1") AS │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

`duckboost_import`

Scalar. Returns model JSON.

duckboost_import(backend VARCHAR, dump VARCHAR) -> VARCHAR
duckboost_import(backend VARCHAR, dump VARCHAR, options MAP(VARCHAR, VARCHAR)) -> VARCHAR

backend uses the same names as duckboost_train’s backend option. dump is XGBoost JSON, LightGBM text, CatBoost JSON, or duckboost’s own JSON. See Import model dumps for executed fixtures and the option keys (task, base_score, learning_rate, feature_names, n_classes, plus the aliases parsed in ImportOptions::FromMap).

LOAD 'build/release/extension/duckboost/duckboost.duckdb_extension';
SELECT duckboost_predict(model, [1.0]) AS pred
FROM (
  SELECT duckboost_import(
    'xgboost',
    (SELECT content FROM read_text('test/sql/duckboost/data/xgb_stump.json')),
    MAP {'task': 'regression', 'base_score': '0.0'}
  ) AS model
);

┌──────┐
│ pred │
├──────┤
│ 0.5  │
└──────┘

`duckboost_importance`

Table function. Returns one row per feature with gain / cover / frequency and a tidy importance score (vip-style).

duckboost_importance(model VARCHAR) -> TABLE(...)
duckboost_importance(model VARCHAR, options MAP(VARCHAR, VARCHAR)) -> TABLE(...)

Column	Type	Meaning
`variable`	`VARCHAR`	Feature name (`feature_names`, else `f0`, `f1`, …)
`feature_index`	`BIGINT`	Zero-based feature index
`gain`	`DOUBLE`	Sum of split gains (0 for imports without persisted stats)
`cover`	`DOUBLE`	Sum of parent hessian mass at splits
`frequency`	`BIGINT`	Number of times the feature was used for a split
`importance`	`DOUBLE`	Selected metric, normalized to sum to 1 by default

Option	Default	Meaning
`metric`	`gain`	`gain`, `cover`, or `frequency` (aliases `weight` / `count` → frequency). If `metric=gain` but all gains are 0 (typical for imports), frequency is used.
`normalize`	`true`	Divide `importance` by the column sum.
`include_unused`	`true`	Keep features that never split.
`sort`	`importance`	`importance`, `gain`, `cover`, `frequency`, `name`, or `index`.

LOAD 'build/release/extension/duckboost/duckboost.duckdb_extension';
CREATE TABLE train AS
SELECT i::DOUBLE AS x_signal, ((i * 17) % 11)::DOUBLE AS x_noise, (3.0 * i)::DOUBLE AS y
FROM range(40) t(i);
CREATE TABLE models AS
SELECT duckboost_train(
  y, [x_signal, x_noise],
  MAP {'n_estimators': '20', 'max_depth': '3', 'feature_names': 'x_signal,x_noise', 'seed': '7'}
) AS model
FROM train;
SET VARIABLE trained_model = (SELECT model FROM models);
SELECT variable, round(importance, 3) AS importance, frequency
FROM duckboost_importance(getvariable('trained_model'))
ORDER BY importance DESC;

┌──────────┬────────────┬───────────┐
│ variable │ importance │ frequency │
├──────────┼────────────┼───────────┤
│ x_signal │ 1.0        │ 140       │
│ x_noise  │ 0.0        │ 0         │
└──────────┴────────────┴───────────┘

`duckboost_backends`

Table function with no arguments. Columns from the bind function: backend VARCHAR, training_supported BOOLEAN, notes VARCHAR.

LOAD 'build/release/extension/duckboost/duckboost.duckdb_extension';
SELECT backend, training_supported FROM duckboost_backends() ORDER BY backend;

┌───────────┬────────────────────┐
│  backend  │ training_supported │
├───────────┼────────────────────┤
│ catboost  │ false              │
│ lightgbm  │ false              │
│ reference │ true               │
│ xgboost   │ false              │
└───────────┴────────────────────┘

`duckboost_build_info`

Table function with no arguments. Columns: name VARCHAR, enabled BOOLEAN, notes VARCHAR.

Rows are the compile flags DUCKBOOST_WITH_XGBOOST, DUCKBOOST_WITH_LIGHTGBM, DUCKBOOST_WITH_CATBOOST, and DUCKBOOST_NATIVE_STUB, then native_*_compiled / native_*_linked for each vendor backend. native_catboost_linked is always false: CatBoost has no public training C API.

LOAD 'build/release/extension/duckboost/duckboost.duckdb_extension';
SELECT name, enabled FROM duckboost_build_info() ORDER BY name;

┌──────────────────────────┬─────────┐
│           name           │ enabled │
├──────────────────────────┼─────────┤
│ DUCKBOOST_NATIVE_STUB    │ false   │
│ DUCKBOOST_WITH_CATBOOST  │ false   │
│ DUCKBOOST_WITH_LIGHTGBM  │ false   │
│ DUCKBOOST_WITH_XGBOOST   │ false   │
│ native_catboost_compiled │ false   │
│ native_catboost_linked   │ false   │
│ native_lightgbm_compiled │ false   │
│ native_lightgbm_linked   │ false   │
│ native_xgboost_compiled  │ false   │
│ native_xgboost_linked    │ false   │
└──────────────────────────┴─────────┘

`duckboost_fit`

Table macro.

duckboost_fit(source, y, features, weight := NULL, is_validation := NULL, options := MAP {})

The body is:

SELECT duckboost_train(y, features, coalesce(weight, 1.0), coalesce(is_validation, false), options) AS model
FROM query_table(source::VARCHAR)

Pass the table name as a VARCHAR literal. y, features, weight, and is_validation are expressions in that table. Omitting is_validation (all false) leaves random validation_fraction early stopping available; pass is_validation := (split = 'validation') with rows from duckboost_initial_validation_split for an external hold-out. The macro’s parameter types are NULL in duckdb_functions().

LOAD 'build/release/extension/duckboost/duckboost.duckdb_extension';
CREATE TABLE train AS
SELECT i::DOUBLE AS x, (2 * i)::DOUBLE AS y FROM range(12) t(i);
SELECT left(model, 60) AS model_prefix
FROM duckboost_fit(
  'train', y, [x],
  options := MAP {'n_estimators': '4', 'max_depth': '2', 'feature_names': 'x'}
);

┌──────────────────────────────────────────────────────────────┐
│                         model_prefix                         │
├──────────────────────────────────────────────────────────────┤
│ {"duckboost_version":1,"backend":"reference","task":"regress │
└──────────────────────────────────────────────────────────────┘

`duckboost_score`

Table macro. No default arguments.

duckboost_score(model, source, features)

The body is:

SELECT *, duckboost_predict(model, features) AS prediction
FROM query_table(source::VARCHAR)

model is a model JSON value (typically a scalar subquery). source is a table name. features is the feature list expression inside that table.

LOAD 'build/release/extension/duckboost/duckboost.duckdb_extension';
CREATE TABLE train AS
SELECT i::DOUBLE AS x, (2 * i)::DOUBLE AS y FROM range(12) t(i);
CREATE TABLE models AS
SELECT duckboost_train(
  y, [x], MAP {'n_estimators': '4', 'max_depth': '2', 'feature_names': 'x'}
) AS model
FROM train;
SELECT * FROM duckboost_score((SELECT model FROM models), 'train', [x])
WHERE x = 4;

┌─────┬─────┬────────────┐
│  x  │  y  │ prediction │
├─────┼─────┼────────────┤
│ 4.0 │ 8.0 │ 9.8225     │
└─────┴─────┴────────────┘

`duckboost_initial_split`

Table macro. Adds a split column, 'train' or 'test', to every row of source. The rsample equivalent is initial_split().

duckboost_initial_split(source, prop := 0.75, strata := NULL, seed := 42)

source is a table or view name, as a string.
prop is the training share, strictly between 0 and 1.
strata is a column or expression; each of its values is split in the same proportions, with round(prop × n) training rows per value. It must average at least 5 rows per value, so bin numeric columns first, for example strata := ntile(4) OVER (ORDER BY price).
seed picks the split. The same rows and seed give the same split regardless of physical row order.

The assignment is a hash of each row’s values, so adding a column or changing a value changes it. Save the result with CREATE TABLE ... AS. See Splitting data and preprocessing.

LOAD 'build/release/extension/duckboost/duckboost.duckdb_extension';
CREATE TABLE t AS SELECT i, i % 2 AS grp FROM range(20) r(i);
SELECT grp, split, count(*) AS n
FROM duckboost_initial_split('t', prop := 0.8, strata := grp)
GROUP BY ALL
ORDER BY ALL;

┌─────┬───────┬───┐
│ grp │ split │ n │
├─────┼───────┼───┤
│ 0   │ test  │ 2 │
│ 0   │ train │ 8 │
│ 1   │ test  │ 2 │
│ 1   │ train │ 8 │
└─────┴───────┴───┘

`duckboost_initial_validation_split`

Table macro. Adds a split column, 'train', 'validation', or 'test'. The rsample equivalent is initial_validation_split().

duckboost_initial_validation_split(source, prop := [0.6, 0.2], strata := NULL, seed := 42)

prop is [training share, validation share]. Both must be positive and sum to less than 1; test gets the rest. Per stratum, the first round(prop[1] × n) rows are training and the rows up to round((prop[1] + prop[2]) × n) are validation. The other arguments behave as in duckboost_initial_split.

LOAD 'build/release/extension/duckboost/duckboost.duckdb_extension';
CREATE TABLE t AS SELECT i FROM range(20) r(i);
SELECT split, count(*) AS n
FROM duckboost_initial_validation_split('t', prop := [0.5, 0.25])
GROUP BY ALL
ORDER BY ALL;

┌────────────┬────┐
│   split    │ n  │
├────────────┼────┤
│ test       │ 5  │
│ train      │ 10 │
│ validation │ 5  │
└────────────┴────┘

`duckboost_vfold`

Table macro. Adds an INTEGER fold column from 1 to v, for v-fold cross-validation. The rsample equivalent is vfold_cv().

duckboost_vfold(source, v := 5, strata := NULL, seed := 42)

Rows are dealt to folds in turn, ordered by stratum and then by the seeded hash, so fold sizes differ by at most one row and each stratum is spread evenly. v must be between 2 and the number of rows. For fold k, fit on fold <> k and score fold = k. Pass only the training rows, never the test rows.

LOAD 'build/release/extension/duckboost/duckboost.duckdb_extension';
CREATE TABLE t AS SELECT i FROM range(11) r(i);
SELECT fold, count(*) AS n
FROM duckboost_vfold('t', v := 3)
GROUP BY fold
ORDER BY fold;

┌──────┬───┐
│ fold │ n │
├──────┼───┤
│ 1    │ 4 │
│ 2    │ 4 │
│ 3    │ 3 │
└──────┴───┘

`duckboost_levels`

Scalar macro that wraps an aggregate, so use it like min or avg.

duckboost_levels(x, min_count := 1)

Returns the sorted list of distinct non-NULL values of x that occur at least min_count times. Compute it on the training rows and pass the list to the encoding macros below.

`duckboost_dummy`

Scalar macro. The recipes equivalent is step_dummy().

duckboost_dummy(x, levels, one_hot := false) -> DOUBLE[]

Returns one 0/1 value per level, in the order of levels. By default the first level is the reference and gets no entry (k − 1 values). one_hot := true returns all k. Values not in levels and NULL return all zeros.

`duckboost_dummy_names`

Scalar macro.

duckboost_dummy_names(prefix, levels, one_hot := false) -> VARCHAR[]

The names matching duckboost_dummy(x, levels, one_hot): prefix || '_' || level, with characters other than letters, digits, and _ replaced by _. Join them with array_to_string(..., ',') for the feature_names option.

`duckboost_integer`

Scalar macro. The recipes equivalent is step_integer().

duckboost_integer(x, levels) -> DOUBLE

The 1-based position of x in levels. NULL and values not in levels return 0.

`duckboost_other`

Scalar macro. The recipes equivalent is step_other().

duckboost_other(x, levels, other := 'other')

Returns x if it is in levels, NULL if x is NULL, and other otherwise.

LOAD 'build/release/extension/duckboost/duckboost.duckdb_extension';
CREATE TABLE t AS FROM (VALUES ('a'), ('a'), ('a'), ('b'), ('b'), ('c'), (NULL)) v(x);
CREATE TABLE prep AS SELECT duckboost_levels(x) AS lv, duckboost_levels(x, min_count := 2) AS common FROM t;
SELECT
  x,
  duckboost_dummy(x, lv) AS dummy,
  duckboost_dummy(x, lv, one_hot := true) AS one_hot,
  duckboost_integer(x, lv) AS integer,
  duckboost_other(x, common) AS pooled
FROM t, prep;

┌──────┬────────────┬─────────────────┬─────────┬────────┐
│  x   │   dummy    │     one_hot     │ integer │ pooled │
├──────┼────────────┼─────────────────┼─────────┼────────┤
│ a    │ [0.0, 0.0] │ [1.0, 0.0, 0.0] │ 1.0     │ a      │
│ a    │ [0.0, 0.0] │ [1.0, 0.0, 0.0] │ 1.0     │ a      │
│ a    │ [0.0, 0.0] │ [1.0, 0.0, 0.0] │ 1.0     │ a      │
│ b    │ [1.0, 0.0] │ [0.0, 1.0, 0.0] │ 2.0     │ b      │
│ b    │ [1.0, 0.0] │ [0.0, 1.0, 0.0] │ 2.0     │ b      │
│ c    │ [0.0, 1.0] │ [0.0, 0.0, 1.0] │ 3.0     │ other  │
│ NULL │ [0.0, 0.0] │ [0.0, 0.0, 0.0] │ 0.0     │ NULL   │
└──────┴────────────┴─────────────────┴─────────┴────────┘

LOAD 'build/release/extension/duckboost/duckboost.duckdb_extension';
SELECT
  duckboost_dummy_names('island', ['Biscoe', 'Dream', 'Torgersen']) AS dummy_names,
  duckboost_dummy_names('island', ['Biscoe', 'Dream', 'Torgersen'], one_hot := true) AS one_hot_names;

┌──────────────────────────────────┬─────────────────────────────────────────────────┐
│           dummy_names            │                  one_hot_names                  │
├──────────────────────────────────┼─────────────────────────────────────────────────┤
│ [island_Dream, island_Torgersen] │ [island_Biscoe, island_Dream, island_Torgersen] │
└──────────────────────────────────┴─────────────────────────────────────────────────┘