# Drop empty or constant columns

Shape / columns

A variant of [Choose and rename columns](https://meridian.online/operations/choose-and-rename-columns).

Drop the columns whose values are all empty, mostly missing or a single repeated value.

Page: <https://meridian.online/operations/choose-and-rename-columns/drop-empty-or-constant-columns>

## Status

Runs as a SQL step.

arcform carries it as a `sql:` step today.

## The DuckDB step

drop the columns that are empty, mostly missing or hold one value

```sql
CREATE TEMP TABLE wide AS SELECT * FROM (VALUES
  (1, NULL, 'x', NULL, 'a', 1.5),
  (2, NULL, 'x', NULL, 'b', NULL),
  (3, NULL, 'x', 'p',  'a', 2.5),
  (4, NULL, 'x', NULL, 'c', 3.5),
  (5, NULL, 'x', 'q',  'b', NULL),
  (6, NULL, 'x', NULL, 'a', 4.5)
) t(id, empty_col, constant_col, mostly_missing_col, label, measure);
-- a column goes when it has at most one distinct value, or when more than half of its values are missing
SET VARIABLE useless = (
  SELECT list(col ORDER BY col)
  FROM (SELECT col, count(DISTINCT v) AS n_distinct, 1 - count(v) / count(*) AS missing_share
        FROM (SELECT id, COLUMNS(* EXCLUDE (id))::VARCHAR FROM wide)
             UNPIVOT INCLUDE NULLS (v FOR col IN (COLUMNS(* EXCLUDE (id))))
        GROUP BY col)
  WHERE n_distinct <= 1 OR missing_share > 0.5);
CREATE TEMP TABLE result AS
SELECT COLUMNS(lambda c: NOT list_contains(getvariable('useless'), c)) FROM wide;
SELECT * FROM result;
SELECT CASE WHEN (getvariable('useless') = ['constant_col', 'empty_col', 'mostly_missing_col'] AND (SELECT list(column_name) FROM (DESCRIBE result)) = ['id', 'label', 'measure'] AND (SELECT count(*) FROM result) = 6 AND (SELECT count(measure) FROM result) = 4) THEN 'as expected' ELSE error('the empty, constant and mostly missing columns were not the three dropped, with the measure that is one third missing kept') END AS verdict;
```

The statement builds its own data.

It needs nothing installed beyond DuckDB itself.

## What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

- EasyMorph: [`Remove empty columns`](https://help.easymorph.com/doku.php?id=transformations:removeemptycolumns) (read 2026-09-27)
- AWS Glue: [`Drop Null Fields`](https://docs.aws.amazon.com/glue/latest/dg/transforms-dropnull-fields.html) (read 2026-09-27)
- AWS Glue: [`DropNullFields`](https://docs.aws.amazon.com/glue/latest/dg/aws-glue-api-crawler-pyspark-transforms-DropNullFields.html) (read 2026-09-27)
- KNIME Analytics Platform: [`Constant Value Column Filter`](https://api.hub.knime.com/nodes/*j60SFMSkRj-uaDgY?details=full) (read 2026-09-27)
- KNIME Analytics Platform: [`Missing Value Column Filter`](https://api.hub.knime.com/nodes/*xTIHpIYhQRCNETkp?details=full) (read 2026-09-27)
- RapidMiner Studio: [`Filter Attributes with Missing Values`](https://docs.rapidminer.com/latest/studio/operators/extensions/Operator%20Toolbox/blending/filter_missing_attributes.html) (read 2026-09-27)
- VisiData: [`hide-uniform-cols`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/features/layout.py#L81) (read 2026-09-23)