# Cross-validate a model

Model / evaluate

A variant of [Evaluate a model](https://meridian.online/operations/evaluate-a-model).

Train and test a model repeatedly over folds, hold-out splits or bootstrap samples and pool the scores.

Page: <https://meridian.online/operations/evaluate-a-model/cross-validate-a-model>

## Status

Runs as a SQL step.

arcform carries it as a `sql:` step today.

## The DuckDB step

cross-validate a line by folds, by hold-out splits and by bootstrap samples, and pool the errors

```sql
-- twelve observations of a noisy line, and the model to validate: a least-squares line fitted with regr_slope and regr_intercept
CREATE TEMP TABLE data AS SELECT i AS id, i::DOUBLE AS x, y FROM (VALUES
  (1, 3.1), (2, 4.8), (3, 7.3), (4, 8.9), (5, 11.2), (6, 12.8), (7, 15.1), (8, 17.3), (9, 18.7), (10, 21.2), (11, 22.8), (12, 25.1)) t(i, y);
-- a bootstrap draw takes twelve rows with replacement; the draws are a fixed formula so that the proof gives the same samples on every run
CREATE TEMP TABLE draws AS SELECT b, (j * j + 3 * b * j + b) % 13 % 12 + 1 AS id FROM range(1, 4) rb(b), range(12) rj(j);
-- one table says, for every scheme and split, which rows train the model and which test it (a drawn row trains once per draw)
CREATE TEMP TABLE splits AS
SELECT 'folds' AS scheme, f AS split, id, CASE WHEN (id - 1) % 3 = f THEN 'test' ELSE 'train' END AS role FROM data, range(3) r(f)
UNION ALL
SELECT 'hold-outs', s, id, CASE WHEN id % 4 = s THEN 'test' ELSE 'train' END FROM data, range(2) r(s)
UNION ALL
SELECT 'bootstrap', b, id, 'train' FROM draws
UNION ALL
SELECT 'bootstrap', b, id, 'test' FROM data, range(1, 4) rb(b) WHERE NOT EXISTS (SELECT 1 FROM draws d WHERE d.b = rb.b AND d.id = data.id);
CREATE TEMP TABLE fits AS
SELECT scheme, split, regr_slope(y, x) AS slope, regr_intercept(y, x) AS intercept
FROM splits JOIN data USING (id) WHERE role = 'train' GROUP BY scheme, split;
CREATE TEMP TABLE errors AS
SELECT scheme, split, y - (intercept + slope * x) AS err
FROM splits JOIN data USING (id) JOIN fits USING (scheme, split) WHERE role = 'test';
CREATE TEMP TABLE per_split AS SELECT scheme, split, count(*) AS n_test, sqrt(avg(err * err)) AS rmse FROM errors GROUP BY scheme, split;
-- pool: the root mean square of every held-out error of the scheme, not an average of the splits' scores
CREATE TEMP TABLE result AS
SELECT scheme, count(*) AS splits, sum(n_test) AS n_test, (SELECT sqrt(avg(err * err)) FROM errors e WHERE e.scheme = p.scheme) AS pooled_rmse
FROM per_split p GROUP BY scheme;
SELECT scheme, splits, n_test, round(pooled_rmse, 6) AS pooled_rmse FROM result ORDER BY scheme;
-- the expected values were computed outside DuckDB with the same splits: folds test every row once (12), the two hold-outs test 3 rows each,
-- and the bootstrap tests the 5, 5 and 6 rows each sample left out; the tolerance 1e-8 is for float rounding only
SELECT CASE WHEN ((SELECT map_from_entries(list((scheme, (splits, n_test)) ORDER BY scheme)) = MAP {'bootstrap': (3, 16), 'folds': (3, 12), 'hold-outs': (2, 6)} FROM result)
  AND (SELECT abs(pooled_rmse - 0.218900778) < 1e-8 FROM result WHERE scheme = 'folds')
  AND (SELECT abs(pooled_rmse - 0.239645227) < 1e-8 FROM result WHERE scheme = 'hold-outs')
  AND (SELECT abs(pooled_rmse - 0.23247675) < 1e-8 FROM result WHERE scheme = 'bootstrap')
  AND (SELECT list(round(rmse, 6) ORDER BY split) = [0.143963, 0.230184, 0.264655] FROM per_split WHERE scheme = 'folds')
  AND (SELECT count(*) = 12 AND count(DISTINCT id) = 12 FROM splits WHERE scheme = 'folds' AND role = 'test')) THEN 'as expected' ELSE error('the pooled held-out errors of the folds, hold-outs and bootstrap samples should be 0.2189, 0.2396 and 0.2325, with every row tested once in the folds') END AS verdict;
```

The statement builds its own data.

It needs nothing installed beyond DuckDB itself.

## What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

- Alteryx Designer: [`Cross-Validation`](https://help.alteryx.com/current/en/designer/tools/predictive/cross-validation-tool.html) (read 2026-09-27)
- KNIME Analytics Platform: [`X-Aggregator`](https://api.hub.knime.com/nodes/*AwzC_JyEuicS9pXC?details=full) (read 2026-09-27)
- KNIME Analytics Platform: [`X-Partitioner`](https://api.hub.knime.com/nodes/*BKpS2Ow-rnvw2TSf?details=full) (read 2026-09-27)
- mlpack: [`Cross-validation`](https://www.mlpack.org/doc/user/cv.html) (read 2026-09-27)
- RapidMiner Studio: [`Bootstrapping Validation`](https://docs.rapidminer.com/latest/studio/operators/validation/bootstrapping_validation.html) (read 2026-09-27)
- RapidMiner Studio: [`Cross Validation`](https://docs.rapidminer.com/latest/studio/operators/validation/cross_validation.html) (read 2026-09-27)
- RapidMiner Studio: [`Split Validation`](https://docs.rapidminer.com/latest/studio/operators/validation/split_validation.html) (read 2026-09-27)
- RapidMiner Studio: [`Wrapper Split Validation`](https://docs.rapidminer.com/latest/studio/operators/validation/wrapper_split_validation.html) (read 2026-09-27)
- RapidMiner Studio: [`Wrapper-X-Validation`](https://docs.rapidminer.com/latest/studio/operators/validation/wrapper_x_validation.html) (read 2026-09-27)
- Yggdrasil Decision Forests: [`cross_validation`](https://github.com/google/yggdrasil-decision-forests/blob/0a54b57c95ff0cf42ecf0310ace0513a4f32cb65/yggdrasil_decision_forests/port/python/ydf/learner/generic_learner.py) (read 2026-09-27)