Shape / columns · All operations

Split columns into two tables

A variant of Choose and rename columns

Send the chosen columns to one output table and the remaining columns to a second.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Send the chosen columns to one table and the rest to another, keeping order_id in both as the rejoin 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
CREATE TEMP TABLE chosen AS SELECT order_id, customer, amount FROM orders;
CREATE TEMP TABLE remainder AS SELECT * EXCLUDE (customer, amount) FROM orders;
SELECT * FROM chosen LIMIT 3;
SELECT * FROM remainder LIMIT 3;
SELECT CASE WHEN ((SELECT list(column_name) FROM (DESCRIBE chosen)) = ['order_id', 'customer', 'amount'] AND (SELECT list(column_name) FROM (DESCRIBE remainder)) = ['order_id', 'region', 'order_date', 'product', 'tags', 'meta'] AND (SELECT count(*) FROM chosen c JOIN remainder r USING (order_id)) = 10) THEN 'as expected' ELSE error('the two tables were not the chosen three columns and the six that remain, joined by order_id') 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 two tables were not the chosen three columns and the six that remain, joined by order_id”.
  • 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).

AWS Glue (2)
  • SplitFieldsread 2026-09-27

    Split a frame into two by field: the named fields and the remaining fields

  • SplitFieldsread 2026-09-27

    Split the keys into two outputs: the selected keys and the remaining keys

KNIME Analytics Platform (2)

Background

Vertical partitioning involves creating tables with fewer columns and using additional tables to store the remaining columns.[1] In AWS Glue, SplitFields splits one or more fields off into a new DynamicFrame and creates another new DynamicFrame with the fields that remain.[2] In KNIME, the Column Splitter node puts the columns selected in its dialog in the first output table and the remaining columns in the second.[3]

When to use it

A common form of vertical partitioning splits static data from dynamic data, since static data is faster to access than dynamic data.[1] Storing infrequently used or very wide columns, which take up a significant amount of memory, on a different machine is a method of vertical partitioning.[1] A view across the two tables made by vertical partitioning restores the original table, with a performance penalty.[1] Split when one group of columns is read far more often than the rest, or when part of a table must be stored or shared under different rules from the rest. (Meridian's view) Keep a key column in both tables, because a split that sends each column to one side only leaves the two halves with nothing to join back on. (Meridian's view) In KNIME, the Column Splitter keeps the columns of each output table in the same order as in the input table.[3]

The method

Splitting a table's columns into further tables is generally known as normalization, but vertical partitioning goes further and partitions columns even when a table is already normalized.[1] Vertical partitioning is also called row splitting, since rows get split by their columns.[1] A columnar database can be regarded as a database that has been vertically partitioned until each column is stored in its own table.[1]

In AWS Glue, SplitFields takes optional names for the two new DynamicFrames and, when none is given, uses the source frame's name with 1 or 2 appended.[2]

Sources

  1. [1]Partition (database) - Wikipediasecondary source
  2. [2]SplitFields class - AWS Gluedocumentation
  3. [3]Column Splitterdocumentation