ALTER TABLE REPLACE COLUMNS

Replaces the entire column schema of a Delta table with a new set of columns in a single atomic operation.

Category: ddlDeltaForge extension

Syntax

ALTER TABLE <table> REPLACE COLUMNS (<col1> <type1> [NOT NULL] [COMMENT '<desc>'], ...)

Description

## Overview Replaces the entire column schema of a Delta table with a new definition. The engine compares the current schema with the specified columns and computes the minimal set of changes: columns present in the old schema but absent from the new are dropped, columns present in the new schema but absent from the old are added, and columns present in both with differing types are type-changed. ## Behavior - The current schema is read from the table's latest snapshot. - Columns that exist in the old schema but not in the new specification are dropped. - Columns that exist in the new specification but not in the old schema are added. - Columns that exist in both but have different data types are type-changed (subject to the same type widening rules as ALTER COLUMN TYPE). - If the new specification is identical to the current schema, the command returns a no-op message without writing a commit. - Column mapping mode (`delta.columnMapping.mode = 'name'`) must be enabled if columns are being dropped. - All changes are applied as a single atomic commit to the Delta log. ## Compatibility REPLACE COLUMNS follows standard Delta Lake schema evolution semantics. The resulting schema is compatible with all Delta readers. Older Parquet files that lack new columns will project NULL; files that contain dropped columns will skip them.

Parameters

Name	Type	Description
`table`		Fully qualified table name or registered table name. The table must be registered in the current session.
`columns`		New column specifications.

Examples

-- Replace the entire schema
ALTER TABLE warehouse.sales.users REPLACE COLUMNS (
  user_id BIGINT NOT NULL COMMENT 'Unique user identifier',
  name STRING COMMENT 'User display name',
  email STRING,
  created_at TIMESTAMP
);

-- Replace columns to remove obsolete fields and add new ones
ALTER TABLE warehouse.sales.products REPLACE COLUMNS (
  product_id BIGINT NOT NULL,
  title STRING NOT NULL,
  price DECIMAL(12,2),
  category STRING
);

-- Replace columns with type changes
ALTER TABLE warehouse.staging.raw_data REPLACE COLUMNS (
  id BIGINT NOT NULL,
  payload STRING,
  received_at TIMESTAMP NOT NULL
);

Pitfalls

Dropping columns requires column mapping mode to be enabled. If column mapping is not active and the new schema omits existing columns, the operation will fail.
Type changes within REPLACE COLUMNS follow the same widening rules as ALTER COLUMN TYPE. Narrowing conversions are rejected.
REPLACE COLUMNS does not rewrite data files. Dropped column data persists in Parquet files until those files are rewritten by OPTIMIZE or VACUUM.