ALTER TABLE SET TBLPROPERTIES

Sets one or more table properties on an existing Delta table, used to enable features such as column mapping, auto-optimize, type widening, and change data feed.

Category: ddl

Syntax

ALTER TABLE <table> SET TBLPROPERTIES ('<key>' = '<value>', ...)

Description

## Overview Sets one or more table properties on an existing Delta table. Table properties control Delta Lake features, protocol versions, and behavioral settings. This is the primary mechanism for enabling advanced features like column mapping, type widening, deletion vectors, and change data feed. ## Common Properties | Property | Values | Effect | |----------|--------|--------| | delta.columnMapping.mode | 'none', 'name', 'id' | Enables column mapping for RENAME/DROP COLUMN | | delta.enableTypeWidening | 'true', 'false' | Enables ALTER COLUMN TYPE widening | | delta.enableChangeDataFeed | 'true', 'false' | Enables CDC (change data capture) | | delta.enableDeletionVectors | 'true', 'false' | Enables deletion vectors for faster DELETEs | | delta.autoOptimize.optimizeWrite | 'true', 'false' | Auto-coalesces small files on write | | delta.autoOptimize.autoCompact | 'true', 'false' | Auto-compacts after writes | | delta.minReaderVersion | '1', '2', '3' | Minimum reader protocol version | | delta.minWriterVersion | '2', '3', '4', '5', '6', '7' | Minimum writer protocol version | | delta.logRetentionDuration | e.g., 'interval 30 days' | How long Delta log entries are retained | | delta.deletedFileRetentionDuration | e.g., 'interval 7 days' | How long deleted files are retained before VACUUM | ## Behavior - Each property is set as a key-value pair in the Delta table metadata. - Setting a feature-enabling property (e.g., delta.enableTypeWidening) may automatically upgrade the protocol version (minReaderVersion, minWriterVersion) as required by the Delta specification. - If a property already has a value, it is overwritten with the new value. - Properties are persisted as a metadata-only commit to the Delta log. - Multiple properties can be set in a single statement. ## Compatibility SET TBLPROPERTIES is a standard Delta Lake operation. However, some properties (e.g., delta.enableDeletionVectors) require specific protocol versions. Setting such properties will upgrade the protocol, which may make the table unreadable by older Delta clients.

Parameters

Name	Type	Description
`table`		Fully qualified table name or registered table name. The table must be registered in the current session.
`properties`		Properties to set.

Examples

-- Enable column mapping (required for DROP COLUMN and RENAME COLUMN)
ALTER TABLE warehouse.sales.users SET TBLPROPERTIES (
  'delta.columnMapping.mode' = 'name'
);

-- Enable auto-optimize for write compaction
ALTER TABLE warehouse.sales.events SET TBLPROPERTIES (
  'delta.autoOptimize.optimizeWrite' = 'true',
  'delta.autoOptimize.autoCompact' = 'true'
);

-- Enable type widening
ALTER TABLE warehouse.sales.orders SET TBLPROPERTIES (
  'delta.enableTypeWidening' = 'true'
);

-- Enable change data feed
ALTER TABLE warehouse.sales.customers SET TBLPROPERTIES (
  'delta.enableChangeDataFeed' = 'true'
);

-- Enable deletion vectors
ALTER TABLE warehouse.sales.orders SET TBLPROPERTIES (
  'delta.enableDeletionVectors' = 'true'
);

Pitfalls

Enabling features like column mapping or deletion vectors upgrades the protocol version. This upgrade is irreversible and may make the table incompatible with older Delta readers.
Setting delta.columnMapping.mode on a table that already has data and partitions requires careful planning. The mapping is applied to existing columns automatically, but external tools that rely on physical column names may break.
Property keys are case-sensitive. Using 'Delta.columnMapping.mode' instead of 'delta.columnMapping.mode' will create a separate, ineffective property.