CLONE (SQL Analytics)

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

Syntax 

CREATE TABLE [IF NOT EXISTS] target_table_identifier
[SHALLOW | DEEP] CLONE source_table_identifier [<time_travel_version>]
[LOCATION 'path']

[CREATE OR] REPLACE TABLE target_table_identifier
[SHALLOW | DEEP] CLONE source_table_identifier [<time_travel_version>]
[LOCATION 'path']
  • table_identifier
    • [database_name.] table_name: A table name, optionally qualified with a database name.
    • delta.`<path-to-table>` : The location of an existing Delta table.

where

<time_travel_version>  =
  TIMESTAMP AS OF timestamp_expression |
  VERSION AS OF version
  • Specify CREATE IF NOT EXISTS to avoid creating a table target_table if a table already exists. If a table already exists at the target, the clone operation is a no-op.
  • Specify CREATE OR REPLACE to replace the target of a clone operation if there is an existing table target_table. This updates the metastore with the new table if table name is used.
  • If you don’t specify SHALLOW or DEEP, a deep clone is created by default.
  • LOCATION creates an external table, with the provided location as the path where the data is stored. If target is a path instead of a table name, the operation will fail.

Examples

You can use CLONE for complex operations like data migration, data archiving, machine learning flow reproduction, short-term experiments, data sharing, and so on. See Clone use cases for a few examples.