Skip to main content

CREATE TABLE CLONE

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

Clones a source Delta table to a target destination at a specific version. A clone can be either deep or shallow: deep clones copy over the data from the source and shallow clones do not. You can also clone source Parquet and Iceberg tables. See Incrementally clone Parquet and 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 Delta Lake 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.

  • source_table_name

    The name of the Delta Lake 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