ALTER TABLE

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Alters the schema or properties of a table.

Temporary tables support ALTER TABLE ... SET TBLPROPERTIES and ALTER TABLE ... UNSET TBLPROPERTIES in serverless compute, Databricks Runtime 18.2 and above, and Databricks SQL 2026.15 and above. Other ALTER TABLE clauses are not supported on temporary tables and return an error. If the table is cached, the command clears cached data of the table and all its dependents that refer to it. The cache will be lazily filled when the table or the dependents are accessed the next time.

Foreign tables support a limited set of ALTER TABLE operations, including ALTER TABLE SET OWNER, ALTER TABLE RENAME TO, ALTER TABLE SET MANAGED { MOVE | COPY }, and ALTER TABLE SET EXTERNAL. SET MANAGED and SET EXTERNAL require a foreign table federated using Hive metastore and Glue Federation, and SET MANAGED also requires the Delta Lake format. See Foreign tables and Convert a foreign table to an external Unity Catalog table.

Required permissions

If you use Unity Catalog you must have MODIFY permission to:

  • ALTER COLUMN
  • ADD COLUMN
  • DROP COLUMN
  • SET TBLPROPERTIES
  • UNSET TBLPROPERTIES

If you use Unity Catalog you must have MANAGE permission or ownership to:

  • SET OWNER TO
  • PREDICTIVE OPTIMIZATION

All other operations require ownership of the table, including SET MANAGED and UNSET MANAGED.

SET EXTERNAL and the foreign-table form of SET MANAGED also require CREATE permission on the EXTERNAL LOCATION. For the full prerequisites for each conversion, see Prerequisites and Prerequisites.

Syntax

ALTER TABLE table_name
    { RENAME TO clause |
      ADD COLUMN clause |
      ALTER COLUMN clause |
      DROP COLUMN clause |
      RENAME COLUMN clause |
      DEFAULT COLLATION clause |
      ADD CONSTRAINT clause |
      DROP CONSTRAINT clause |
      DROP FEATURE clause |
      ADD PARTITION clause |
      DROP PARTITION clause |
      PARTITION SET LOCATION clause |
      RENAME PARTITION clause |
      RECOVER PARTITIONS clause |
      SET { ROW FILTER clause } |
      DROP ROW FILTER |
      SET TBLPROPERTIES clause |
      UNSET TBLPROPERTIES clause |
      SET SERDE clause |
      SET LOCATION clause |
      SET EXTERNAL clause |
      SET MANAGED clause |
      UNSET MANAGED clause |
      SET OWNER TO clause |
      SET TAGS clause |
      UNSET TAGS clause |
      CLUSTER BY clause |
      REPLACE PARTITIONED BY WITH CLUSTER BY clause |
      PREDICTIVE OPTIMIZATION clause}

Parameters

  • table_name

    Identifies the table being altered. The name must not include a temporal specification or options specification. If the table cannot be found Azure Databricks raises a TABLE_OR_VIEW_NOT_FOUND error.

  • RENAME TO to_table_name

    Renames the table.

    • to_table_name

      Identifies the new table name. The name must not include a temporal specification or options specification.

      For Unity Catalog tables, the to_table_name must be within the same catalog as table_name. For other tables, the to_table_name must be within the same schema as table_name.

      If to_table_name is unqualified it is implicitly qualified with the current schema.

    > ALTER TABLE student RENAME TO student_info;

  • ADD COLUMN

    Adds one or more columns to the table.

    When you add a column to an existing Delta Lake table, you cannot define a DEFAULT value. All columns added to Delta Lake tables are treated as NULL for existing rows. After adding a column, you can optionally define a default value for new rows using ALTER COLUMN.

    > DESCRIBE StudentInfo;
                col_name data_type comment
----------------------- --------- -------
                    name    string    NULL
                  rollno       int    NULL
                     age       int    NULL

> ALTER TABLE StudentInfo ADD columns (LastName string, DOB timestamp);

-- After adding new columns to the table
> DESCRIBE StudentInfo;
                col_name data_type comment
----------------------- --------- -------
                    name    string    NULL
                  rollno       int    NULL
                LastName    string    NULL
                     DOB timestamp    NULL
                     age       int    NULL

-- Optionally set a default value for new rows
> ALTER TABLE StudentInfo ALTER COLUMN LastName SET DEFAULT 'unknown';

  • ALTER COLUMN

    Changes a property or the location of a column.

    > DESCRIBE StudentInfo;
                col_name data_type comment
----------------------- --------- -------
                    name    string    NULL
                  rollno       int    NULL
                LastName    string    NULL
                     DOB timestamp    NULL
                     age       int    NULL

> ALTER TABLE StudentInfo ALTER COLUMN name COMMENT "new comment";

-- After altering the column
> DESCRIBE StudentInfo;
                col_name data_type     comment
----------------------- --------- -----------
                    name    string new comment
                  rollno       int        NULL
                LastName    string        NULL
                     DOB timestamp        NULL
                     age       int        NULL

    Alter multiple columns in a single statement:

    -- Create a table with 3 columns
> CREATE TABLE my_table (num INT, str STRING, bool BOOLEAN) TBLPROPERTIES('delta.feature.allowColumnDefaults' = 'supported')
> DESCRIBE TABLE my_table;
  col_name    data_type     comment
  --------    ---------     -------
       num          int        null
       str       string        null
       bool      boolean       null

-- Update comments on multiple columns
> ALTER TABLE table ALTER COLUMN
   num COMMENT 'number column',
   str COMMENT 'string column';

> DESCRIBE TABLE my_table;
  col_name    data_type      comment
  --------    ---------   -------------
       num          int   number column
       str       string   string column
      bool      boolean            null

-- Can mix different types of column alter
> ALTER TABLE table ALTER COLUMN
   bool COMMENT 'boolean column',
   num AFTER bool,
   str AFTER num,
   bool SET DEFAULT true;

> DESCRIBE TABLE my_table;
  col_name    data_type      comment
  --------    ---------   --------------
      bool      boolean   boolean column
       num          int    number column
       str       string    string column

  • DROP COLUMN

    Drop one or more columns or fields in a Delta Lake table.

  • RENAME COLUMN

    Renames a column or field in a Delta Lake table.

    > ALTER TABLE StudentInfo RENAME COLUMN name TO FirstName;

-- After renaming the column
> DESCRIBE StudentInfo;
                col_name data_type     comment
----------------------- --------- -----------
               FirstName    string new comment
                  rollno       int        NULL
                LastName    string        NULL
                     DOB timestamp        NULL
                     age       int        NULL

  • ADD CONSTRAINT

    Adds a check constraint, informational foreign key constraint, or informational primary key constraint to the table.

    Foreign keys and primary keys are supported only for tables in Unity Catalog, not the hive_metastore catalog.

  • DEFAULT COLLATION collation_name

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 16.3 and above

    Changes the default collation of the table for new STRING columns. Existing columns are not affected by this clause. To change the collation of an existing column, use ALTER TABLE ... ALTER COLUMN ... COLLATE collation_name.

  • DROP CONSTRAINT

    Drops a primary key, foreign key, or check constraint from the table.

  • DROP FEATURE feature_name [ TRUNCATE HISTORY ]

    Applies to: check marked yes Databricks Runtime 14.3 LTS and above

    Legacy support for DROP FEATURE is available starting in Databricks Runtime 14.3 LTS. For documentation of the legacy functionality, see Drop Delta table features (legacy).

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 16.3 and above

  • Azure Databricks recommends using Databricks Runtime 16.3 and above for all DROP FEATURE commands, which replaces the legacy behavior.

    Removes a feature from a Delta Lake table.

    Removing a feature may result in the addition of the checkpointProtection writer feature in the table protocol. For more information, see Drop Delta table features and Protocol versions and table features.

    • feature_name

      The name of a feature in form of a STRING literal or identifier, that must be understood by Azure Databricks and be supported on the table.

      If the feature is not present in the table Azure Databricks raises DELTA_FEATURE_DROP_FEATURE_NOT_PRESENT.

    • TRUNCATE HISTORY

      Removal of features by truncating history. This requires a two stage process:

The removal of features by truncating history requires a two-step process:

  • The first invocation clears traces of the feature and informs you of partial success.

  • Then, wait until the retention period ends before re-executing the statement to complete the removal.

    If you initiate the second invocation too early, Azure Databricks raises DELTA_FEATURE_DROP_WAIT_FOR_RETENTION_PERIOD or DELTA_FEATURE_DROP_HISTORICAL_VERSIONS_EXIST.

    Truncating the table history limits your ability to perform DESCRIBE HISTORY and execute time travel queries.

    -- Drop the "deletion vectors" from a Delta table
> ALTER TABLE my_table DROP FEATURE deletionVectors;

-- 24 hours later
> ALTER TABLE my_table DROP FEATURE deletionVectors TRUNCATE HISTORY;

  • ADD PARTITION

    Adds one or more partitions to the table.

    > SHOW PARTITIONS StudentInfo;
partition
---------
    age=11
    age=12
    age=15

> ALTER TABLE StudentInfo ADD IF NOT EXISTS PARTITION (age=18);

-- After adding a new partition to the table
> SHOW PARTITIONS StudentInfo;
partition
---------
    age=11
    age=12
    age=15
    age=18

-- Adding multiple partitions to the table
> ALTER TABLE StudentInfo ADD IF NOT EXISTS PARTITION (age=18) PARTITION (age=20);

> SHOW PARTITIONS StudentInfo;
partition
---------
    age=11
    age=12
    age=15
    age=18
    age=20

  • DROP PARTITION

    Drops one or more partitions from the table.

    > SHOW PARTITIONS StudentInfo;
partition
---------
    age=11
    age=12
    age=15
    age=18

> ALTER TABLE StudentInfo DROP IF EXISTS PARTITION (age=18);

-- After dropping the partition of the table
> SHOW PARTITIONS StudentInfo;
partition
---------
    age=11
    age=12
    age=15

  • PARTITION … SET LOCATION

    Sets the location of a partition.

    > ALTER TABLE dbx.tab1 PARTITION (a='1', b='2') SET LOCATION '/path/to/part/ways';

  • RENAME PARTITION

    Replaces the keys of a partition.

    > SHOW PARTITIONS StudentInfo;
partition
---------
    age=10
    age=11
    age=12

> ALTER TABLE default.StudentInfo PARTITION (age='10') RENAME TO PARTITION (age='15');

-- After renaming Partition
> SHOW PARTITIONS StudentInfo;
partition
---------
    age=11
    age=12
    age=15

  • RECOVER PARTITIONS

    Instructs Azure Databricks to scan the table's location and add any files to the table which have been added directly to the filesystem.

  • SET ROW FILTER clause

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 12.2 LTS and above check marked yes Unity Catalog only

    Adds a row filter function to the table. All subsequent queries to the table receive a subset of the rows where the function evaluates to boolean TRUE. This can be useful for fine-grained access control purposes where the function can inspect the identity or group memberships of the invoking user to determine whether to filter certain rows.

  • DROP ROW FILTER

    Applies to: check marked yes Unity Catalog only

    Drops the row filter from the table, if any. Future queries will return all rows from the table without any automatic filtering.

  • SET TBLPROPERTIES

    Sets or resets one or more user defined properties.

    > ALTER TABLE dbx.tab1 SET TBLPROPERTIES ('winner' = 'loser');

  • UNSET TBLPROPERTIES

    Removes one or more user defined properties.

    > ALTER TABLE dbx.tab1 UNSET TBLPROPERTIES ('winner');

  • SET SERDE

    Applies to: check marked yes Databricks Runtime

    Specifies the serializer/deserializer (SerDe) class used to read and write data in a Hive-format table. You can also configure SerDe properties with WITH SERDEPROPERTIES.

    > ALTER TABLE test_tab SET SERDE 'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe';

> ALTER TABLE dbx.tab1 SET SERDE 'org.apache.hadoop' WITH SERDEPROPERTIES ('k' = 'v', 'kay' = 'vee');

  • SET LOCATION

    Moves the location of a table.

    SET LOCATION path

    • LOCATION path

      path must be a STRING literal. Specifies the new location for the table.

      Files in the original location will not be moved to the new location.

  • SET EXTERNAL [ DRY RUN ]

    Applies to: check marked yes Databricks Runtime 17.3 and above check marked yes Unity Catalog only

    Converts a foreign table to a Unity Catalog external table, retaining the table history and configurations, including the name, settings, permissions, and views. Supported only on foreign tables federated using Hive metastore and Glue Federation.

    Requires OWNER or MANAGE permissions on the table and CREATE permission on the EXTERNAL LOCATION.

    To roll back the conversion, drop the table. Azure Databricks re-federates it as a foreign table during the next catalog sync.

    • DRY RUN

      Checks whether the source table can be converted, without converting it. The command returns DRY_RUN_SUCCESS if the table can be converted.

    For prerequisites and format-specific guidance, see Convert a foreign table to an external Unity Catalog table.

    -- Check whether a foreign table can be converted
> ALTER TABLE hms_federated_catalog.my_schema.my_table SET EXTERNAL DRY RUN;

-- Convert a foreign table to an external table
> ALTER TABLE hms_federated_catalog.my_schema.my_table SET EXTERNAL;

  • SET MANAGED

    Applies to: check marked yes Databricks Runtime 17.3 LTS and above check marked yes Unity Catalog only

    Converts a Unity Catalog external or foreign Delta Lake table to a Unity Catalog managed table. The conversion retains the table name, settings, permissions, views, and history.

    External and foreign tables use different forms of the clause, as shown in the following syntax:

    SET MANAGED [ TRUNCATE UNIFORM HISTORY ]  -- external tables
SET MANAGED { MOVE | COPY }               -- foreign tables

    For external tables, omit MOVE and COPY. If you include either one, Azure Databricks raises DELTA_ALTER_TABLE_SET_MANAGED_UNSUPPORTED_COPY_MOVE_SYNTAX.

    For foreign tables, specify either MOVE or COPY. If you omit both, Azure Databricks raises DELTA_ALTER_TABLE_SET_MANAGED_COPY_OR_MOVE_REQUIRED. To choose between the two forms, see Choose the correct command for your source table.

    • TRUNCATE UNIFORM HISTORY

      Applies to external tables that have Apache Iceberg reads enabled. If the table has Iceberg reads enabled and you omit this option, Azure Databricks raises DELTA_ALTER_TABLE_SET_MANAGED_DOES_NOT_SUPPORT_UNIFORM_ICEBERG.

      TRUNCATE UNIFORM HISTORY truncates UniForm Iceberg history and doesn't remove Delta Lake history. Truncation causes a short Iceberg read and write downtime.

    • MOVE

      Converts a foreign table to managed and disables access to the source table in the external catalog. After conversion, access through the external catalog and path-based access fail, so all readers and writers must use the Unity Catalog namespace.

    • COPY

      Converts a foreign table to managed without modifying or disabling access to the source table in the external catalog. The conversion copies data into the managed storage location, creating two separate copies of the data. You are responsible for disabling reads and writes to the source table and migrating workloads to the managed table.

    For prerequisites, downtime estimates, and troubleshooting, see Convert external or foreign Delta Lake tables to Unity Catalog managed tables.

    -- Convert an external table
> ALTER TABLE main.default.my_external_table SET MANAGED;

-- Convert an external table that has Iceberg reads enabled
> ALTER TABLE main.default.my_external_table SET MANAGED TRUNCATE UNIFORM HISTORY;

-- Convert a foreign table and disable access to the source table
> ALTER TABLE hms_federated_catalog.my_schema.my_table SET MANAGED MOVE;

  • UNSET MANAGED

    Applies to: check marked yes Databricks Runtime 17.3 LTS and above check marked yes Unity Catalog only

    Rolls a table converted with SET MANAGED back to an external table by updating the table metadata to point to the original external location. Azure Databricks preserves writes made to the managed location after conversion. For a converted external table, you can roll back within 14 days of conversion.

    UNSET MANAGED [ TRUNCATE UNIFORM HISTORY ]

    Commits made between conversion and rollback support time travel by version, but not by timestamp.

    To roll a foreign table converted with MOVE back to a foreign table, run UNSET MANAGED and then drop the resulting external table, which re-federates it during the next catalog sync. A foreign table converted with COPY doesn't need UNSET MANAGED, because the conversion left the source table unmodified.

    Warning

    If you converted a foreign table with MOVE, do not drop the managed table before you run UNSET MANAGED. Dropping it first might result in data loss or inconsistencies.

    For full rollback instructions, see Roll back a managed table conversion.

    -- Roll a converted managed table back to an external table
> ALTER TABLE main.default.my_managed_table UNSET MANAGED;

  • [ SET ] OWNER TO principal

    Transfers ownership of the table to principal.

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 11.3 LTS and above

    SET is allowed as an optional keyword.

    Note

    Changing the owner is not available on datasets managed by a workspace pipeline.

  • SET TAGS ( { tag_name = tag_value } [, ...] )

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 13.3 LTS and above

    Apply tags to the table. You need to have APPLY TAG permission to add tags to the table.

    • tag_name

      A literal STRING. The tag_name must be unique within the table or column.

    • tag_value

      A literal STRING.

    -- Applies three tags to the table named `test`.
> ALTER TABLE test SET TAGS ('tag1' = 'val1', 'tag2' = 'val2', 'tag3' = 'val3');

-- Applies three tags to table `main.schema1.test` column `col1`.
> ALTER TABLE main.schema1.test ALTER COLUMN col1 SET TAGS ('tag1' = 'val1', 'tag2' = 'val2', 'tag3' = 'val3');

  • UNSET TAGS ( tag_name [, ...] )

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 13.3 LTS and above

    Remove tags from the table. You need to have APPLY TAG permission to remove tags from the table.

    • tag_name

      A literal STRING. The tag_name must be unique within the table or column.

    -- Removes three tags from the table named `test`.
> ALTER TABLE test UNSET TAGS ('tag1', 'tag2', 'tag3');

-- Removes three tags from table `main.schema1.test` column `col1`.
> ALTER TABLE main.schema1.test ALTER COLUMN col1 UNSET TAGS ('tag1', 'tag2', 'tag3');

  • CLUSTER BY clause

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 13.3 LTS and above

    Adds, changes, or drops the clustering strategy for a Delta Lake table.

  • REPLACE PARTITIONED BY WITH CLUSTER BY [( <clustering_columns> ) | AUTO]

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 18.1 and above

    Converts an existing partitioned Delta Lake table to liquid clustering in place, with minimal reader and writer downtime. The CLUSTER BY clause accepts explicit clustering columns, AUTO to delegate key selection to predictive optimization, or can be omitted to use the existing partition columns.

    See Convert a partitioned table to liquid clustering.

  • { ENABLE | DISABLE | INHERIT } PREDICTIVE OPTIMIZATION

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 12.2 LTS and above check marked yes Unity Catalog only

    Alters the managed Delta Lake table to the desired predictive optimization setting.

    By default, when tables are created, the behavior is to INHERIT from the schema.

    When predictive optimization is explicitly enabled or inherited as enabled OPTIMIZE and VACUUM will be automatically invoked on the table as deemed appropriate by Azure Databricks. For more details see: Predictive optimization for Unity Catalog managed tables.

    -- Enables predictive optimization for my_table
> ALTER TABLE my_table ENABLE PREDICTIVE OPTIMIZATION;

Additional examples

For Delta Lake add constraints and alter column examples, see

  • Last updated on