Shape / columns · All operations

Choose and rename columns

Keep, drop, reorder or rename columns.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
one row out per row in, with columns added or changed
Level
one clause of SQL

The DuckDB step

Drop, rename and pick columns by pattern

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 result AS
SELECT * EXCLUDE (meta, tags) RENAME (customer AS customer_name) FROM orders;
DESCRIBE result;
SELECT COLUMNS('^order_.*') FROM orders LIMIT 1;
SELECT CASE WHEN ((SELECT list(column_name) = ['order_id', 'customer_name', 'region', 'order_date', 'amount', 'product'] FROM (DESCRIBE result))) THEN 'as expected' ELSE error('the columns are not the six expected, in order') 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 columns are not the six expected, in order”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Keep, rename and reorder in one step

Choose, rename, reorder and sometimes retype columns in a single projection.

Pick columns by type, pattern or position

Refer to a set of columns by data type, name pattern or position instead of naming each one.

Match columns to a reference table

Keep, rename or reorder columns to follow the columns or name mapping held in a second table or stored schema.

Rename columns by a rule

Rename many columns at once by a prefix, suffix, case change, pattern replacement or function on the name.

Use a row as column names

Take the values of one or more rows as the column names and remove those rows from the data.

What other tools call it

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

Alteryx Designer (4)
  • Dynamic Renameread 2026-09-27

    Rename columns by formula, from the first row of data or from a description stream

  • Dynamic Selectread 2026-09-27

    Select fields by type or by formula, evaluated at run time

  • Selectread 2026-09-27

    Include, exclude, reorder and rename columns, and change their type and size

  • Select In-DBread 2026-09-27

    Select, deselect, rename and reorder fields inside the database

Apache DataFusion (10)
  • ASread 2026-09-27

    Pipe: alias the input relation, BigQuery-style |> syntax

  • Projection (SELECT)read 2026-09-27

    Choose and compute output columns

  • SELECTread 2026-09-27

    Pipe: project columns, BigQuery-style |> syntax

  • Wildcards (EXCLUDE / EXCEPT)read 2026-09-27

    Select all columns of an input, omitting named ones

  • aliasread 2026-09-27

    DataFrame method: Apply an alias to the DataFrame

  • drop_columnsread 2026-09-27

    DataFrame method: Drop the named columns

  • selectread 2026-09-27

    DataFrame method: Project arbitrary expressions, like a SQL SELECT list

  • select_columnsread 2026-09-27

    DataFrame method: Keep only the named columns

  • select_exprsread 2026-09-27

    DataFrame method: Project a list of expression strings

  • with_column_renamedread 2026-09-27

    DataFrame method: Rename one column

Apache Hop (2)
  • Select Valuesread 2026-09-27

    Select, remove, rename and reorder fields, and change their type, length and format

  • Static Schema Mappingread 2026-09-27

    Map incoming fields onto the fields of a stored schema definition

Apache NiFi (3)
  • 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

  • RenameRecordFieldread 2026-09-27

    Rename the fields selected by RecordPath in each record

Apache SeaTunnel (3)
  • FieldMapperread 2026-09-27

    Map input fields to output fields: rename, reorder and drop

  • FieldRenameread 2026-09-27

    Rename fields by case conversion, prefix, suffix, regex replacement or specific pairs

  • Filterread 2026-09-27

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

AWS Glue (12)
  • ApplyMappingread 2026-09-27

    Map source fields to target fields, renaming them and changing their types

  • ChangeSchemaread 2026-09-27

    Map source keys to target keys: rename them, change their types, drop them

  • Drop Null Fieldsread 2026-09-27

    Remove columns whose values are all null

  • DropFieldsread 2026-09-27

    Choose the keys to drop

  • DropFieldsread 2026-09-27

    Drop the named fields

  • DropNullFieldsread 2026-09-27

    Drop fields whose value is missing or null in every record

  • RenameFieldread 2026-09-27

    Rename a single key

  • RenameFieldread 2026-09-27

    Rename one field

  • SelectFieldsread 2026-09-27

    Choose the keys to keep

  • SelectFieldsread 2026-09-27

    Keep only the named fields

  • 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

Azure Data Factory (3)
  • Column Managementread 2026-09-27

    Power Query M functions supported at scale: select, remove, rename and reorder columns

  • Metafunctionsread 2026-09-27

    11 functions: byItem, byOrigin, byOrigins, byName, byNames, …

  • Selectread 2026-09-27

    Rename columns and streams, and drop or reorder columns

Bruin (1)
dbt-utils (1)
  • starread 2026-09-27

    Emit the column list of a relation with exclusions, alias prefix, prefix/suffix and renames

DuckDB (1)
  • SELECT (clause)read 2026-09-27

    Choose, rename and compute columns; * EXCLUDE, REPLACE, RENAME and COLUMNS() act on many at once

Duckle (4)
EasyMorph (8)
Ibis (4)
  • Aliasread 2026-09-27

    Give an expression its output column name

  • DropColumnsread 2026-09-27

    Remove named columns from a relation

  • Fieldread 2026-09-27

    Refer to one column of a relation by name

  • Projectread 2026-09-27

    Select, compute and name the output columns of a relation

KNIME Analytics Platform (17)
OpenRefine (7)
Pentaho Data Integration (1)
  • Select Valuesread 2026-09-27

    Select or remove fields and optionally set type, length and precision

Polars (12)
  • DataFrame.dropread 2026-09-27

    Remove the named columns

  • DataFrame.drop_in_placeread 2026-09-27

    Remove one column in place and return it as a Series

  • DataFrame.renameread 2026-09-27

    Rename columns by a mapping or a function

  • DataFrame.selectread 2026-09-27

    Keep or compute a chosen set of columns from expressions

  • DataFrame.select_seqread 2026-09-27

    As select, but evaluating the expressions one after another rather than in parallel

  • Expr.aliasread 2026-09-27

    Give the output of an expression a new column name

  • Expr.excluderead 2026-09-27

    Leave named columns out of a multi-column expression

  • Expr.name (group)read 2026-09-27

    10 expressions: prefix, suffix, map, to_lowercase, keep and others; rename output columns or struct fields

  • Functions: column selection (group)read 2026-09-27

    5 functions: all, exclude, first, last, nth; pick columns of the context by position or exclusion

  • Functions: context aliases (group)read 2026-09-27

    2 functions: element, field; stand for the current element in eval and the current field in struct.with_fields

  • Selectors (group)read 2026-09-27

    39 entries: by_dtype, by_name, matches, numeric, temporal and others; pick columns by type, name pattern or position

  • polars.colread 2026-09-27

    Refer to one or more columns by name, pattern or data type, by call or by attribute syntax

Power Query M (7)
RapidMiner Studio (14)
SQLMesh (1)
  • @STARread 2026-09-27

    Emit the column selections of a relation, cast to known types, with exclusions, prefix and suffix

Tableau Prep (6)
  • 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

  • Rename Fieldread 2026-09-27

    Edit the name of a field

  • Rename Fields (in bulk)read 2026-09-27

    Rename many fields at once: replace text, add prefix or add suffix; can apply to future fields

  • Reorder fieldsread 2026-09-27

    Drag fields into a new order; the order persists to output

Talend (2)
  • tFilterColumnsread 2026-09-27

    Reorder, remove or add columns to fit an output schema

  • tSQLTemplateFilterColumnsread 2026-09-27

    Reorganise, remove or add columns of a table by name mapping, run in the DBMS

Trifacta (17)
VisiData (16)
  • clean-namesread 2026-09-23

    Rename visible columns to cleaned, identifier-safe names

  • hide-colread 2026-09-23

    Hide current column

  • hide-selectedread 2026-09-23

    Hide selected source columns

  • hide-uniform-colsread 2026-09-23

    Hide each column that has one distinct value across several rows

  • normalize-col-namesread 2026-09-23

    Normalise names of all visible columns

  • rename-colread 2026-09-23

    Rename current column

  • rename-col-selectedread 2026-09-23

    Rename current column to contents of current cell in selected rows

  • rename-cols-rowread 2026-09-23

    Name unnamed visible columns from the selected rows (promote rows to header)

  • rename-cols-selectedread 2026-09-23

    Rename all visible columns from contents of the selected rows

  • slide-leftread 2026-09-23

    Move current column one position left

  • slide-left-nread 2026-09-23

    Move current column N positions left

  • slide-leftmostread 2026-09-23

    Move current column to the left end of its section

  • slide-rightread 2026-09-23

    Move current column one position right

  • slide-right-nread 2026-09-23

    Move current column N positions right

  • slide-rightmostread 2026-09-23

    Move current column to the right end of its section

  • unhide-colsread 2026-09-23

    Unhide all hidden columns

Background

In relational algebra, a projection keeps a chosen set of attributes and discards the others; for a table, that is picking a subset of its columns.[1] In DuckDB, the SELECT clause specifies the list of columns a query returns, and every expression in it has a name.[2]

When to use it

Reach for it when a table carries more columns than the next step needs, when names arrive in a form that is awkward to type or join on, or when a file must leave with its columns in an agreed order. (Meridian's view) In DuckDB, a star expression can pick columns by matching their names against a pattern with LIKE, GLOB or SIMILAR TO, and the NOT variants exclude the columns that match.[3] In DuckDB, a COLUMNS expression can select columns with a regular expression, a list of column names or a lambda function evaluated for every column in the FROM clause.[3] A pattern is a promise about names you have not seen yet: a column added upstream that happens to match arrives in the output without anyone choosing it, so name the columns outright where the output is a contract others rely on. (Meridian's view)

In DuckDB, the capture groups of a regular expression in a COLUMNS expression can be used to rename the matching columns, with \0 standing for the original column name.[3] Renaming by a rule suits a family of columns that share a prefix or a separator; read the new names afterwards, because two old names can map to the same new one. (Meridian's view)

The method

Relational algebra was introduced by Edgar F. Codd as part of his relational model of data in 1970.[4] In relational algebra, the result of a projection is defined only if the attributes it names are a subset of the relation's header.[1] In relational algebra, projection is idempotent, so a series of valid projections is equivalent to the outermost one.[4] SQL's SELECT allows arithmetic operations to define new columns in the result, which database theory calls extended projection.[4]

In DuckDB, the expressions in the SELECT clause are logically executed only at the end of the query, although the clause appears first.[2]

Compared with the alternatives

In relational algebra, selection commutes with projection if and only if the fields the selection condition references are a subset of the fields in the projection.[4] In practice, filter rows on a column before you drop it, or keep the column until the filter has run. (Meridian's view)

Sources

  1. [1]Projection (relational algebra) - Wikipediasecondary source
  2. [2]SELECT Clause – DuckDBdocumentation
  3. [3]Star Expression – DuckDBdocumentation
  4. [4]Relational algebra - Wikipediasecondary source

Used in

15 Protocols use this operation: