Shallow clone for Unity Catalog tables

Use shallow clone to create Unity Catalog tables with access control privileges independent from their source tables, without copying the underlying data files. Shallow clone in Unity Catalog is supported only for Delta Lake tables. You can't create a shallow clone of an Iceberg or other non-Delta table.

For information about cloning a table, see Clone a table on Azure Databricks.

Create a Unity Catalog managed shallow clone

Create a shallow clone of a managed table in Unity Catalog.

CREATE TABLE <catalog-name>.<schema-name>.<target-table-name>
SHALLOW CLONE <catalog-name>.<schema-name>.<source-table-name>

To create a managed shallow clone on Unity Catalog, you must have the following privileges on the source and target resources.

Like other create table statements, when you run SHALLOW CLONE, you own the target table. The owner of a cloned target table controls the access rights for that table independently of the source table. The owner of a cloned table might be different from the owner of a source table.

Create a Unity Catalog external shallow clone

Create a Unity Catalog external shallow clone by specifying an external location.

CREATE TABLE <catalog-name>.<schema-name>.<target-table-name>
SHALLOW CLONE <catalog-name>.<schema-name>.<source-table-name>
LOCATION 's3://<bucket-name>/<path-name>/<target-table-name>'

To create an external shallow clone on Unity Catalog, you must have the following privileges on the source and target resources.

Work with shallow cloned tables in standard access mode

To query a shallow clone in standard access mode (formerly shared access mode), you must have the following privileges on the table and containing resources:

You must also have MODIFY permissions on the target of the clone operation to run the following operations:

• INSERT
• DELETE
• UPDATE
• MERGE
• CREATE TABLE
• DROP TABLE

Work with shallow cloned tables in dedicated access mode

When working with Unity Catalog shallow clones in dedicated access mode (formerly single user access mode), you must have permissions on the resources for both the cloned table source and the target table.

For simple queries, in addition to the required permissions on the target table, you must have USE permissions on the source catalog and schema and SELECT permissions on the source table. For any queries that update or insert records to the target table, you must also have MODIFY permissions on the source table.

Databricks recommends using Unity Catalog clones on compute with standard access mode as this allows independent changes of permissions for Unity Catalog shallow clone targets and their source tables.

Use VACUUM with Unity Catalog shallow clones

When you use Unity Catalog tables for the source and target of a shallow clone operation, Unity Catalog manages the underlying data files to improve reliability for the source and target of the clone operation. Running VACUUM on the source of a shallow clone doesn't break the cloned table.

Normally, when VACUUM identifies valid files for a given retention threshold, only the metadata for the current table is considered. However, shallow clone support for Unity Catalog tracks the relationships between all cloned tables and the source data files, so valid files are expanded to include data files necessary for returning queries for both shallow cloned tables and the source table.

For VACUUM on a Unity Catalog shallow clone, a valid data file is any file within the specified retention threshold for the source table or any cloned table. Managed tables and external tables have slightly different behaviors.

This enhanced tracking of metadata changes how VACUUM operations affect underlying data files for the Delta Lake tables, with the following behavior:

• For managed tables, VACUUM operations on either the source or target of a shallow clone operation might delete data files from the source table.
• For external tables, VACUUM operations only remove data files from the source table when run against the source table.
• Only data files not considered valid for the source table or any shallow clone against the source are removed.
• If multiple shallow clones are defined against a single source table, running VACUUM on any of the cloned tables does not remove valid data files for other cloned tables.

Even if you drop a shallow cloned table, you might require SELECT access to that shallow cloned table to run VACUUM on the base table. Databricks reads the shallow clone's Delta log to verify which base table data files the clone still references before vacuuming them. Databricks maintains this link for 7 days after dropping a shallow cloned table to support the UNDROP operation. In standard access mode, however, this permission is not required.

Drop the base table for a shallow clone

If you drop a shallow clone’s base table, the clone becomes unusable. By default, Databricks blocks you from dropping a base table if it still has shallow clones referencing it.

To override this protection, use the DROP TABLE ... FORCE syntax. If you use FORCE:

• The base table is dropped immediately.
• All referencing shallow clones become broken and:
  • Shallow clones fail on operations that require reading data or metadata (for example, SELECT, INSERT, UPDATE, DESCRIBE HISTORY, CLONE).
  • To allow cleanup, shallow clones are still visible through metadata-level operations (for example, SHOW TABLES, DROP TABLE).

This behavior applies only to Unity Catalog managed tables. For more information, see DROP TABLE.

Limitations

• Shallow clone is supported only for Delta Lake tables. You can't create a shallow clone of an Iceberg or other non-Delta table.
• Shallow clones on external tables must be external tables. Shallow clones on managed tables must be managed tables.
• You cannot share shallow clones using OpenSharing.
• You cannot nest shallow clones, meaning you cannot make a shallow clone from a shallow clone.
• For managed tables, dropping the source table breaks the target table for shallow clones. The underlying data files for external tables are not removed by DROP TABLE operations, and so shallow clones of external tables are not impacted by dropping the source.
• Unity Catalog allows users to UNDROP managed tables for around 7 days after a DROP TABLE command. In Databricks Runtime 13.3 LTS and above, managed shallow clones of a dropped source table continue to work for the 7-day period during which Unity Catalog supports UNDROP. If the source table is not restored within that window, the shallow clone stops functioning when the source data files are deleted during garbage collection.