How can i rename a column in a delta table?

[gowri_databrick]

I have a delta table and i want to rename one of its columns.

what is the recommended way to rename a column in databricks?

is there any difference between renaming a column using sql and using pyspark?

[balajij8 · Accepted solution]

@gowri_databrick 

You can do it seamlessly using a one-time metadata upgrade that decouples the logical column names from the physical files, turning the rename into a fast and metadata-only operation that avoids rewriting any underlying data files. You can enable column mapping upgrade and run the rename command using standard SQL

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

-- Metadata-only column rename
ALTER TABLE workspace.files.files RENAME COLUMN files_old TO files_new;

Both Pyspark and SQL renames execute the exact same underlying Delta Lake engine command. If the pipeline is in Python, wrap the identical statements inside spark sql and run the code.

Enabling column mapping mode is a permanent, one-way upgrade. Once the table properties are updated, legacy Delta Lake readers on protocol versions lower than reader version 2 will not be able to read the table.

[Satyasai · Accepted solution]

The recommended way to rename a column in Databricks Delta Lake is by enabling Delta Column Mapping.
Step 1: Enable Column Mapping
You can enable Column Mapping during table creation or alter an existing table:
ALTER TABLE catalog_name.schema_name.table_name SET TBLPROPERTIES ('delta.columnMapping.mode' = 'name');

Note: Once enabled, delta.columnMapping.mode cannot be downgraded back to none. Tables created with Databricks Runtime 10.2+ or Unity Catalog defaults often have this property pre-configured.

Step 2: Rename the Column
Once column mapping is enabled, you can rename the column using SQL or PySpark.
Using SQL (Recommended)
Executing a metadata-only rename via SQL is standard practice:
ALTER TABLE catalog_name.schema_name.table_name
RENAME COLUMN old_column_name TO new_column_name;

[data_pulse]

@gowri_databrick 

Common Approaches are:

SQL migration through PySpark: This is only a Python wrapper around the same SQL DDL. It is not a different renaming mechanism. It needs the columnMapping.mode = 'name' to be enabled on the table first.

spark.sql("""
    ALTER TABLE catalog.schema.orders
    RENAME COLUMN order_dt TO order_date
""")

 DataFrame level rename:

df = source_df.withColumnRenamed("old_name", "new_name")
or
df = source_df.select(col("old_name").alias("new_name"))

This changes only the DataFrame schema, not the existing Delta table. If metadata-based renaming is unavailable (In cases where it's Not possible to Enable Column Mapping Mode or to change the table protocol), the DataFrame must be written back using overwriteSchema=true, which generally rewrites the table.

Caveats :

• Column mapping makes a Delta column rename metadata-only, but downstream dependencies still need updating: views, jobs, constraints, dashboards, and data contracts.
• Enabling column mapping upgrades the Delta protocol, typically to at least minReaderVersion = 2 and minWriterVersion = 5. It is effectively a one-way upgrade, so older Delta clients, external engines, or direct storage readers may no longer be compatible.
• For changing the data type of an existing column, the common pattern is to add a replacement column, backfill it, drop the original column, and rename the replacement. 
• Streaming-based tables use persistent checkpoints. Schema changes must therefore be coordinated with the stream, do not casually delete or reset checkpoints. The stream may need to be stopped and restarted while preserving its checkpoint and progress.
• mergeSchema = true supports additive schema evolution, but does not automatically make column renames safe.