Shape / columns · All operations

Keep or drop columns

A variant of Choose and rename columns

Keep the named columns and drop the rest, or drop the named columns and keep the rest.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Keep named columns, or drop them and keep the rest

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 kept AS SELECT order_id, customer, amount FROM orders;
CREATE TEMP TABLE dropped AS SELECT * EXCLUDE (meta, tags, region, product) FROM orders;
SELECT * FROM kept;
SELECT * FROM dropped;
SELECT CASE WHEN ((SELECT list(column_name) FROM (DESCRIBE kept)) = ['order_id', 'customer', 'amount'] AND (SELECT list(column_name) FROM (DESCRIBE dropped)) = ['order_id', 'customer', 'order_date', 'amount'] AND (SELECT count(*) FROM kept) = 10) THEN 'as expected' ELSE error('keeping three columns and dropping four did not leave the three named and the four others') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “keeping three columns and dropping four did not leave the three named and the four others”.
  • 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).

Apache DataFusion (3)
Apache NiFi (2)
  • FilterAttributeread 2026-09-27

    Keep a named set of FlowFile attributes and drop the rest, or drop the named set

  • RemoveRecordFieldread 2026-09-27

    Remove the fields selected by RecordPath from each record

Apache SeaTunnel (1)
  • Filterread 2026-09-27

    Keep the listed fields or drop the listed fields; it selects columns, not rows

AWS Glue (4)
Duckle (2)
EasyMorph (1)
Ibis (1)
  • DropColumnsread 2026-09-27

    Remove named columns from a relation

KNIME Analytics Platform (3)
  • Column Filterread 2026-09-27

    Keep a chosen set of columns and drop the rest

  • Column Filter (PMML)read 2026-09-27

    Drop columns and record the change in a PMML preprocessing document

  • DB Column Filterread 2026-09-27

    In-database: drop columns from the query that defines the DB table

OpenRefine (1)
Polars (3)
Power Query M (3)
RapidMiner Studio (3)
Tableau Prep (3)
  • Hide Fieldread 2026-09-27

    Hide a field from the profile; it stays unloaded until unhidden and is still written to output

  • Keep Only Fieldread 2026-09-27

    Keep the selected fields and drop the others

  • Remove Fieldread 2026-09-27

    Remove a field from the flow

Trifacta (3)
  • Delete columnsread 2026-09-27

    Delete one or more columns

  • Drop Transformread 2026-09-27

    Remove one or more columns

  • Selectread 2026-09-27

    Create a new table from a chosen set of columns of the current dataset

VisiData (3)

Background

Projection picks a subset of a table's columns, and a language can let it be written by the columns to keep or by the columns to leave out.[1] In DuckDB, EXCLUDE leaves named columns out of a star expression, and as of DuckDB 2.0 EXCEPT is accepted as a synonym for EXCLUDE.[2]

When to use it

Drop what the next step does not need as early as you can: fewer columns are easier to read, and a column that is not there cannot leak into a result by accident. (Meridian's view) In the SQL standard, the default projection returns a multiset instead of a set, and the DISTINCT keyword is added to eliminate duplicate data.[1] Keeping fewer columns can leave rows that differed only in a dropped column looking identical; add DISTINCT only when one row per remaining combination is what you mean. (Meridian's view)

In DuckDB, the star expression in the simplest case expands to all the expressions in the FROM clause.[3] In Polars, DataFrame.drop has a strict parameter, on by default, that validates that every named column exists in the schema and throws an exception if any does not.[4] Keep strict name checking on, so that a drop naming a misspelt or renamed column fails rather than quietly keeping data you meant to remove. (Meridian's view) Prefer dropping a column in the query that does not need it to dropping it from the stored table, which changes the table for every later reader. (Meridian's view)

The method

Projection is relational algebra's counterpart of existential quantification in predicate logic, and the attributes it leaves out correspond to existentially quantified variables.[1] Some authorities prefer to define projection in terms of the attributes it excludes, and a computer language can provide notations for both.[1] In relational algebra, projection over no attributes at all is possible and yields a relation of degree zero, with one tuple unless the operand is empty.[1]

In DuckDB, ALTER TABLE DROP COLUMN removes a column from a stored table, and a column can only be removed if no index relies on it.[5]

Sources

  1. [1]Projection (relational algebra) - Wikipediasecondary source
  2. [2]Star Expression – DuckDBdocumentation
  3. [3]SELECT Clause – DuckDBdocumentation
  4. [4]polars.DataFrame.drop — Polars documentationdocumentation
  5. [5]ALTER TABLE Statement – DuckDBdocumentation