Problem

You are trying to change the case of a column name table using the ALTER TABLE <your-catalog>.<your-database>.<your-table> RENAME COLUMN command in Databricks when you encounter an error.

This issue typically occurs when attempting to rename a column to a name that already exists in the table, but with a different case. For example, renaming the column Description to description in a table that already has a description column.

Example error

[FIELDS_ALREADY_EXISTS] Cannot rename column, because `description` already exists...

Cause 

The underlying cause of this issue is the case-insensitive nature of column names in Databricks. Column names are stored in a case-insensitive manner, meaning that Description and description are considered the same column name.

Solution

Set spark.sql.caseSensitive to true to change the default behaviour.

%python

spark.sql("set spark.sql.caseSensitive=true");

You may need to enable column mapping (AWS | Azure | GCP)  on your Delta table with the mapping mode name. 

You will need to verify the protocol version (AWS | Azure | GCP) of your table. If it is using an old protocol, you will need to upgrade the protocol version.

If your table is already on or above the required protocol version:

%python

ALTER TABLE table_name SET TBLPROPERTIES ('delta.columnMapping.mode' = 'name')

If your table is not on the required protocol version and requires a protocol upgrade:

%python

ALTER TABLE table_name SET TBLPROPERTIES (
  'delta.columnMapping.mode' = 'name',
  'delta.minReaderVersion' = '2',
  'delta.minWriterVersion' = '5')