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.

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. 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.

  • 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 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, data sharing, and so on. See Clone a table on Databricks for a few examples.