Why do a stratified train/test split in SQL instead of pandas?

Category: SQL for AI Engineering

Answer

When your dataset is 10M rows, SQL does the split in the database and avoids shuffling. Stratified SQL split: bucket each row by ABS(HASH(some_col)) % 100, then split by bucket. The HASH distributes evenly; stratification keeps rare-class proportions.

Concrete examples from the fca project context

Example 1

SELECT *, ABS(HASH(user_id)) % 100 AS bucket FROM features; bucket < 80 = train, 80-89 = val, 90-99 = test.

Example 2

Per-row random() with same seed works but Postgres HASH() guarantees stable across migrations.

Example 3

Per-class stratified: bucket = ABS(HASH(class || id)) % 100 - rare classes get more rows per bucket.

Data flow / flow chart

rows -> bucket = ABS(HASH(col)) % 100
  bucket < 80  -> train
  bucket in 80..89 -> val
  bucket >= 90 -> test

Takeaway

SQL split is faster for big data, reproducible across environments when the HASH is stable.