Skip to main content

CREATE TABLE CLONE

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

Clones a source Delta, managed Apache Iceberg, or Apache Parquet table to a target location at a specific version. Cloning can be either deep or shallow: deep clones copy the data, while shallow clones reference the source data without copying it.

  • Delta, Parquet, and Foreign Iceberg tables support both deep and shallow cloning.
  • Managed Iceberg tables support only deep cloning, and you can't change the table format during cloning.

For more information, see Incrementally clone Parquet and Apache Iceberg tables to Delta Lake.

In Databricks SQL and Databricks Runtime 13.3 LTS and above, you can use shallow clone with Unity Catalog managed tables. In Databricks Runtime 12.2 LTS and below, there is no support for shallow clones in Unity Catalog. See Shallow clone for Unity Catalog tables.

important

There are important differences between shallow and deep clones that can determine how best to use them. See Clone a table on Databricks.

Syntax​

CREATE TABLE [IF NOT EXISTS] table_name
   [SHALLOW | DEEP] CLONE source_table_name [TBLPROPERTIES clause] [LOCATION path]
[CREATE OR] REPLACE TABLE table_name
   [SHALLOW | DEEP] CLONE source_table_name [TBLPROPERTIES clause] [LOCATION path]

Parameters​

  • IF NOT EXISTS

    If specified, the statement is ignored if table_name already exists.

  • [CREATE OR] REPLACE

    If CREATE OR is specified the table is replaced if it exists and newly created if it does not. Without CREATE OR the table_name must exist.

  • table_name

    The name of the table to be created. The name must not include a temporal specification or options specification. If the name is not qualified the table is created in the current schema. table_name must not exist already unless REPLACE or IF NOT EXISTS has been specified.

  • SHALLOW CLONE or DEEP CLONE

    If you specify SHALLOW CLONE Databricks will make a copy of the source table's definition, but refer to the source table's files. When you specify DEEP CLONE (default) Databricks will make a complete, independent copy of the source table.

    Managed Iceberg tables only support deep cloning, not shallow cloning.

  • source_table_name

    The name of the table to be cloned. The name may include a temporal specification or options specifiation.

  • TBLPROPERTIES

    Optionally sets one or more user-defined properties.

  • LOCATION path

    Optionally creates an external table, with the provided location as the path where the data is stored. If table_name itself is a path instead of a table identifier, the operation will fail. path must be a STRING literal.

Examples​

You can use CREATE TABLE CLONE for complex operations like data migration, data archiving, machine learning flow reproduction, short-term experiments, and data sharing. See Clone a table on Databricks.

Last updated on