Skip to Content

Train / Val / Test Splits via SQL

When to use

Carve a labelled table into an offline train/validation/test split while preserving label distribution (stratification) and avoiding leakage.

Analogy

Shuffle the deck deterministically (hash the primary key), then deal the top 70% to train, next 15% to val, last 15% to test — the deal is reproducible because the shuffle is keyed on user_id.

Data-flow diagram

  labelled_set
       |
       |  hash(user_id) % 100
       v
  +--------+--------+--------+
  | 0..69  | 70..84 | 85..99 |   <- modulo buckets
  +--------+--------+--------+
    TRAIN     VAL      TEST

  reproducible across runs, runs in SQL,
  preserves class distribution when stratified.

Deep explanation

Stable, reproducible splits are the spine of trustworthy ML evaluation. The simplest approach: hash the entity key (user_id), take modulo 100, and bucket. For stratification (rare classes), bucket on the pair (label, hash). Never split on row index — inserts shift rows between splits, invalidating prior baselines. For time-aware splits (forecast tasks), split on TIMESTAMP ranges, not hash. Caveat: Postgres HASH() is reserved-and-stable inside a major version but is documented as not guaranteed across major releases — for long-lived datasets, store an explicit random_uuid or salted integer bucketing column at insert time.

Examples

Example 1

-- 10a: deterministic hash-based split
SELECT *,
       CASE
         WHEN (ABS(HASH(user_id)) % 100) < 70 THEN 'train'
         WHEN (ABS(HASH(user_id)) % 100) < 85 THEN 'val'
         ELSE                                    'test'
       END AS split
  FROM labelled_set;

HASH of user_id makes the split deterministic — same user always lands in the same bucket across reruns.

Example 2

-- 10b: stratified by label bucket
-- hash a (user_id, label) pair so each (entity, class) maps to a stable bucket
SELECT *,
       CASE
         WHEN (ABS(HASH(user_id::text || ':' || label::text)) % 100) < 80 THEN 'train'
         ELSE                                                                'test'
       END AS split
  FROM labelled_set;

BUCKETING the hash × label enforces stratification with no extra scan: each (user, label) pair has a stable bucket.

Example 3

-- 10c: time-range split for forecasting tasks
SELECT *,
       CASE
         WHEN ts <  '2026-01-01' THEN 'train'
         WHEN ts <  '2026-03-01' THEN 'val'
         ELSE                        'test'
       END AS split
  FROM labelled_set;

Time-range split is the only valid choice for forecasting and time-series models — never let the future leak into training.

Common mistake

Splitting on row number with LIMIT/OFFSET. Inserts and updates shift rows; the same user may end up in train AND test, breaking CV and inflating offline metrics (data leakage). Another trap: forgetting to checkpoint the split logic — minor SQL changes silently move users across splits.

Key takeaway

Hash-based splits for IID tasks; time-based splits for time-series tasks; never split on row-number ORDER BY without stabilizing key; document the split SQL in the model card.

Production Failure Playbook

Failure scenario 1: leakage-via-overlap

Failure scenario 2: future-leakage-in-time-split