Categories:
Database, Schema, & Share DDL , Table, View, & Sequence DDL , Data Loading / Unloading DDL

CREATE <object> … CLONE

Creates a copy of an existing object in the system. This command is primarily used for creating zero-copy clones of databases, schemas, and tables; however, it can also be used to quickly/easily create clones of other schema objects (i.e. external stages, file formats, and sequences).

The command is a variation of the object-specific CREATE <object> commands with the addition of the CLONE keyword.

Note

For databases, schemas, and tables, CLONE supports an additional AT | BEFORE clause for cloning using Time Travel.

In this Topic:

Syntax

Databases, Schemas, Tables

CREATE [ OR REPLACE ] { DATABASE | SCHEMA | TABLE } [ IF NOT EXISTS ] <object_name>
  CLONE <source_object_name>
        [ { AT | BEFORE } ( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> } ) ]
  ...

Other Schema Objects

CREATE [ OR REPLACE ] { STAGE | FILE FORMAT | SEQUENCE } [ IF NOT EXISTS ] <object_name>
  CLONE <source_object_name>
  ...

General Usage Notes

  • A clone is writable and is independent of its source (i.e. changes made to the source or clone are not reflected in the other object).

  • To create a clone, your current role must have the following privilege(s) on the source object:

    Tables:SELECT
    Other objects:USAGE

    In addition, to clone a table or schema, your current role must have required privileges on the container object(s) for both the source and the clone.

  • For databases and schemas, cloning is recursive:

    • Cloning a database clones all the schemas and other objects, particularly tables, in the database.
    • Cloning a schema clones all the contained objects, particularly tables, in the schema.
  • For databases, schemas, and tables, a clone does not contribute to the overall data storage for the object until operations are performed on the clone that modify existing data or add new data, such as:

    • Adding, deleting, or modifying rows in a cloned table.
    • Creating a new, populated table in a cloned schema.
  • Cloning a table replicates the structure, data, and certain other properties (e.g. STAGE FILE FORMAT) of the source table. A cloned table does not include the load history of the source table. Data files that were loaded into a source table can be loaded again into its clones.

  • When cloning tables or views (including materialized views), the CREATE <object> command syntax includes the COPY GRANTS keywords:

    • If the COPY GRANTS keywords are not included in the CREATE <object> statement, any future grants defined for the object type in the schema are applied to the cloned object.
    • If the COPY GRANTS option is specified in the CREATE <object> statement, the new object retains the access permissions of the original table and does not inherit any future grants defined for the object type in the schema.

Additional Rules that Apply to Cloning Objects

Metadata:A clone inherits the current metadata definition of the source object at the time the clone is created. The definition includes the current object name, comment, other properties, etc. This is important to note when using Time Travel to clone tables, schemas, and databases at a time/point in the past. For example, when a table is cloned, if columns were renamed, added, or dropped in earlier versions of the table, the clone inherits the column metadata at the moment when the cloning statement is executed, rather than the previous metadata.
Child objects:A cloned database or schema includes all child objects active at the time the statement is executed or at the specified time/point in the past. A snapshot of the table data represents the state of the source data when the statement is executed or at the specified time/point in the past. Child objects inherit the current definitions of the source child objects at the time the statement is executed.
Table data:When cloning a database, schema, or table, a snapshot of the data in each table is taken and made available to the clone. The snapshot represents the state of the source data either at the time the statement is executed or at the specified time/point in the past (using Time Travel).

Note

  • Certain limitations apply to cloning operations. For example, DDL statements that affect the source object during a cloning operation can alter the outcome or cause errors.
  • Cloning is not instantaneous, particularly for large objects (databases, schemas, tables), and does not lock the object being cloned. As such, a clone does not reflect any DML statements applied to table data, if applicable, while the cloning operation is still running.

For more information about this and other use cases that might affect your cloning operations, see Cloning Considerations.

Notes for Cloning with Time Travel (Databases, Schemas, and Tables Only)

  • The AT | BEFORE clause clones a database, schema, or table as of a specified time in the past or based on a specified SQL statement:

    • The AT keyword specifies that the request is inclusive of any changes made by a statement or transaction with timestamp equal to the specified parameter.
    • The BEFORE keyword specifies that the request refers to a point immediately preceding the specified parameter.
  • Cloning using STATEMENT is equivalent to using TIMESTAMP with a value equal to the recorded execution time of the SQL statement (or its enclosing transaction), as identified by the specified statement ID.

  • An error is returned if:

    • The object being cloned did not exist at the point in the past specified in the AT | BEFORE clause.
    • The historical data required to clone the object or any of its child objects (e.g. tables in cloned schemas or database) has been purged.
  • If any child object in a cloned database or schema did not exist at the point in the past specified in the AT | BEFORE clause, the child object is not cloned.

For more information, see Understanding & Using Time Travel.

Examples

Clone a database and all objects within the database at its current state:

CREATE DATABASE mytestdb_clone CLONE mytestdb;

Clone a schema and all objects within the schema at its current state:

CREATE SCHEMA mytestschema_clone CLONE testschema;

Clone a table at its current state:

CREATE TABLE orders_clone CLONE orders;

Clone a schema as it existed before the date and time in the specified timestamp:

CREATE SCHEMA mytestschema_clone_restore CLONE testschema BEFORE (TIMESTAMP => TO_TIMESTAMP(40*365*86400));

Clone a table as it existed exactly at the date and time of the specified timestamp:

CREATE TABLE orders_clone_restore CLONE orders AT (TIMESTAMP => TO_TIMESTAMP_TZ('04/05/2013 01:02:03', 'mm/dd/yyyy hh24:mi:ss'));

Clone a table as it existed immediately before the execution of the specified statement (i.e. query ID):

CREATE TABLE orders_clone_restore CLONE orders BEFORE (STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726');