Shape / rows · All operations

Split rows into training and test sets

A variant of Sample rows

Partition every row at random into two or more sets, such as training, validation and holdout.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Split every row at random into training, validation and test sets

It reads the orders table the proofs share. Create it first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL
-- rank the rows by a seeded hash of the key, so the split is random-looking and the same on every run
CREATE TEMP TABLE shuffled AS
SELECT *, row_number() OVER (ORDER BY hash(order_id::VARCHAR || '-split-42')) AS rn FROM orders;
CREATE TEMP TABLE train AS SELECT order_id FROM shuffled WHERE rn <= 6;
CREATE TEMP TABLE validation AS SELECT order_id FROM shuffled WHERE rn > 6 AND rn <= 8;
CREATE TEMP TABLE test AS SELECT order_id FROM shuffled WHERE rn > 8;
SELECT 'train' AS split, count(*) AS n FROM train UNION ALL SELECT 'validation', count(*) FROM validation UNION ALL SELECT 'test', count(*) FROM test;
SELECT CASE WHEN ((SELECT count(*) FROM train) = 6 AND (SELECT count(*) FROM validation) = 2 AND (SELECT count(*) FROM test) = 2 AND (SELECT list(order_id ORDER BY order_id) FROM train) = [2, 3, 4, 7, 8, 9] AND (SELECT list(order_id ORDER BY order_id) FROM validation) = [1, 10] AND (SELECT list(order_id ORDER BY order_id) FROM test) = [5, 6] AND (SELECT count(DISTINCT order_id) FROM (SELECT * FROM train UNION ALL SELECT * FROM validation UNION ALL SELECT * FROM test)) = 10 AND (SELECT sum(order_id) FROM (SELECT * FROM train UNION ALL SELECT * FROM validation UNION ALL SELECT * FROM test)) = 55) THEN 'as expected' ELSE error('the three sets were not orders 2, 3, 4, 7, 8, 9 and 1, 10 and 5, 6 under the seeded hash') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the three sets were not orders 2, 3, 4, 7, 8, 9 and 1, 10 and 5, 6 under the seeded hash”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

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

Alteryx Designer (1)
  • Create Samplesread 2026-09-27

    Split records at random into estimation, validation and holdout samples by percentage

KNIME Analytics Platform (2)
  • DB Table Partitionerread 2026-09-27

    In-database: split a DB table row-wise into two partitions such as train and test

  • Table Partitionerread 2026-09-27

    Split rows into two partitions, for instance train and test

mlpack (1)
  • Dataset splittingread 2026-09-27

    Split a dataset, and its labels, into a training set and a test set

RapidMiner Studio (1)
  • Split Dataread 2026-09-27

    Partition rows into several subsets by given ratios, such as train and test

Background

In machine learning, the input data used to build a model are usually divided into multiple data sets, commonly training, validation and test sets.[1] A model is initially fit on a training data set, a set of examples used to fit the model's parameters.[1] A test data set is a data set used to provide an unbiased evaluation of a model fit on the training data set.[1]

When to use it

Learning the parameters of a prediction function and testing it on the same data is a methodological mistake, since a model that repeated the labels it had seen would score perfectly yet fail on unseen data.[2] Tuning hyperparameters until a model performs best on the test set risks overfitting on the test set, so another part of the data can be held out as a validation set.[2] Partitioning the data into three sets reduces the number of samples available for learning, and the results can depend on a particular random choice of the training and validation sets.[2] Deciding the sizes and strategies for dividing data into training, test and validation sets is very dependent on the problem and the data available.[1]

Data leakage occurs when information that would not be available at prediction time is used when building the model, and results in overly optimistic performance estimates.[3] A common cause of data leakage is not keeping the test and train subsets separate.[3] Split the rows first, before any cleaning step that learns from the data, such as filling gaps with an average, and learn every such step from the training set alone. (Meridian's view)

Data with groups of dependent samples, such as several samples taken from each patient, breaks the assumption that samples are independent and identically distributed (i.i.d.).[2] In scikit-learn, KFold and ShuffleSplit assume i.i.d. samples, and on time series data would give poor estimates of generalisation error.[2] Split on the key that groups dependent rows, such as a customer or a patient, and hold out the latest rows when the data is ordered in time. (Meridian's view)

In scikit-learn, train_test_split's random_state controls the shuffling applied to the data before the split, and an integer gives reproducible output across calls.[4]

The method

In scikit-learn, train_test_split splits arrays or matrices into random train and test subsets.[4] In k-fold cross-validation, the training set is split into k smaller sets, and for each fold a model is trained on k-1 of the folds and validated on the remaining part.[2] Under cross-validation a test set should still be held out for final evaluation, but the validation set is no longer needed.[2]

In scikit-learn, GroupKFold ensures that the same group is not represented in both the testing and training sets.[2] In scikit-learn, TimeSeriesSplit returns the first k folds as the training set and the (k+1)th fold as the test set, so successive training sets are supersets of those before them.[2]

Compared with the alternatives

K-fold cross-validation can be computationally expensive, but does not waste as much data as fixing an arbitrary validation set does.[2] The term validation set is sometimes used instead of test set, for example when the original data were partitioned into only two subsets.[1] In scikit-learn, train_test_split splits the data in a stratified fashion when class labels are passed as its stratify argument.[4]

Sources

  1. [1]Training, validation, and test data sets - Wikipediasecondary source
  2. [2]3.1. Cross-validation: evaluating estimator performance — scikit-learn 1.9.1 documentationdocumentation
  3. [3]12. Common pitfalls and recommended practices — scikit-learn 1.9.1 documentationdocumentation
  4. [4]train_test_split — scikit-learn 1.9.1 documentationdocumentation