Shape / table · All operations

Split a table

Split one table into several by a key or a condition.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
fewer rows, same columns
Level
several clauses of SQL, or a window

The DuckDB step

One table split into a file per key

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
COPY (SELECT * FROM orders WHERE region IS NOT NULL) TO '<your_scratch_dir>/by_region' (FORMAT csv, PARTITION_BY (region));
CREATE TEMP TABLE result AS
SELECT count(DISTINCT regexp_extract(file, 'region=([^/]+)', 1)) AS branches, (SELECT count(*) FROM read_csv('<your_scratch_dir>/by_region/region=north/*.csv')) AS north_rows FROM glob('<your_scratch_dir>/by_region/*/*.csv');
SELECT * FROM result;
SELECT CASE WHEN ((SELECT branches = 4 AND north_rows = 3 FROM result)) THEN 'as expected' ELSE error('the table did not split into four branches with three rows in north') END AS verdict;

Replace <your_scratch_dir> with a directory of your own that you can write to before running the statement.

  • The statement writes by_region itself, in <your_scratch_dir>.
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the table did not split into four branches with three rows in north”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Split by the value of a key

Make one table per distinct value of a key, a hash of it, or a script's result.

Split into chunks by size

Cut a table or file into pieces of a fixed row count or byte size, or at a delimiter.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Apache NiFi (9)
  • PartitionRecordread 2026-09-27

    Group records by the value of user-named RecordPaths into separate FlowFiles

  • ScriptedPartitionRecordread 2026-09-27

    Group records into separate FlowFiles by the value a user script returns for each

  • SegmentContentread 2026-09-27

    Split content into smaller FlowFiles on byte boundaries

  • SplitAvroread 2026-09-27

    Split an Avro data file into smaller files by configured output size

  • SplitContentread 2026-09-27

    Split content at each occurrence of a byte sequence

  • SplitExcelread 2026-09-27

    Split a multi-sheet Excel workbook into one workbook per sheet

  • SplitPCAPread 2026-09-27

    Split a pcap file into several files by maximum size

  • SplitRecordread 2026-09-27

    Split a record-oriented FlowFile into several smaller FlowFiles

  • SplitTextread 2026-09-27

    Split text into smaller files on line boundaries, by line count or fragment size

Polars (1)
Power Query M (3)
  • Table.Partitionread 2026-09-27

    Split a table into a list of N tables by hashing a column's value

  • Table.Splitread 2026-09-27

    Split a table into a list of tables of a fixed page size

  • Table.SplitAtread 2026-09-27

    Split a table in two at a row count: the first N rows and the rest

RapidMiner Studio (1)
  • Group Into Collectionread 2026-09-27

    Split a table into a collection of tables, one per value of a group-by column

Background

Horizontal partitioning puts different rows of a table into different tables, such as customers split by ZIP code range.[1] In a partitioned database, each piece of data belongs to exactly one partition.[1] A view with a union over the partition tables can be created to give a complete view of all the rows again.[1]

When to use it

Database partitioning is normally done for manageability, performance or availability reasons, or for load balancing.[1] In DuckDB, filters on the partition keys of a Hive partitioned dataset are pushed down into the files, so a query skips files it does not need.[2] Split a table when the pieces will be read, shipped or managed separately; when they will only ever be queried together again, a filter at query time keeps one table to look after. (Meridian's view)

In DuckDB, writing a larger number of partitions can be very expensive, as many files will be created.[3] DuckDB generally recommends at least 100 MB of data per partition, because writing data into many small partitions is expensive.[3] In a partitioned database, uneven distribution of data across partitions is termed skew, and skew reduces partitioning efficiency.[1] Choose a key with few enough values that each piece earns its own table or file, and look at how many rows each value holds before splitting on it. (Meridian's view)

In SQL, a WHERE clause keeps only the rows for which its predicate evaluates to True, and rows where it is False or Unknown are discarded by SELECT queries.[4] In SQL, a query filtered on (x = 10) OR NOT (x = 10) is not equivalent to selecting every row if the column x contains any nulls.[4] When a table is split into one filtered branch per condition, give rows with a null key a branch of their own, and check that the branches' row counts add up to the table's. (Meridian's view)

The method

High-end relational database management systems take a partitioning key and assign a partition based on criteria such as ranges, lists of values or a hash.[1] Range partitioning assigns continuous key ranges to partitions, such as all rows whose zipcode lies between 70000 and 79999.[1] List partitioning assigns a partition a list of values, such as the Nordic countries Iceland, Norway, Sweden, Finland and Denmark.[1] Hash partitioning applies a hash function to spread skewed data evenly across partitions, at the cost of scattering adjacent keys across partitions.[1] Round-robin partitioning assigns the ith row in insertion order to partition i mod n, which gives a uniform distribution across n partitions.[1]

In Power Query M, Table.Partition places each row in one of a given number of tables, chosen by a hash of a column's value modulo that number.[5] In Power Query M, Table.Split returns a list of tables in which the first holds the first pageSize rows, the next holds the next pageSize rows, and so on.[6] In Power Query M, Table.SplitAt returns two tables: the first N rows of a table and the remaining rows.[7]

In Polars, DataFrame.partition_by groups a DataFrame by the given columns and returns the groups as separate dataframes.[8] In Polars, partition_by can return a dictionary instead of a list, keyed by tuples of the distinct group values that identify each group.[8] In Polars, asking partition_by to keep the order of the groups consistent with the input is slower than a default partition by operation.[8]

In DuckDB, a COPY statement with a PARTITION_BY clause writes its files in a Hive partitioned folder hierarchy, extracting the partition values from the data.[3] In DuckDB's Hive partitioning, files are organised into folders, and the name of each folder determines the value of the partition key within it.[2] In DuckDB, the PARTITION_BY options cannot use expressions, so a derived key such as the year of a timestamp is produced as a column in a SELECT first.[2] When DuckDB reads a Hive partitioned dataset with the hive_partitioning flag, the values of the partition columns are read from the directories.[2]

In Apache NiFi, the PartitionRecord processor sends two records to the same output only if they have the same value for every field path it is configured with.[9]

Compared with the alternatives

A SQL GROUP BY clause also partitions rows into groups based on their values in one or several columns, but its result contains one row for each group.[10] Vertical partitioning, by contrast, creates tables with fewer columns and uses additional tables to store the remaining columns.[1]

Sources

  1. [1]Partition (database) - Wikipediasecondary source
  2. [2]Hive Partitioning – DuckDBdocumentation
  3. [3]Partitioned Writes – DuckDBdocumentation
  4. [4]Null (SQL) - Wikipediasecondary source
  5. [5]Table.Partition - PowerQuery M | Microsoft Learndocumentation
  6. [6]Table.Split - PowerQuery M | Microsoft Learndocumentation
  7. [7]Table.SplitAt - PowerQuery M | Microsoft Learndocumentation
  8. [8]polars.DataFrame.partition_by — Polars documentationdocumentation
  9. [9]PartitionRecord - Apache NiFidocumentation
  10. [10]Group by (SQL) - Wikipediasecondary source