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 non-temporary tables, CLONE supports an additional AT | BEFORE clause for cloning using Time Travel.

In this Topic:

Syntax

Databases, Schemas, Tables, Streams

CREATE [ OR REPLACE ] { DATABASE | SCHEMA | TABLE | STREAM } [ 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 | TASK } [ 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

    Pipes, Streams, Tasks

    OWNERSHIP

    Other objects

    USAGE

    In addition, to clone a schema or an object within a 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 in the database.

    • Cloning a schema clones all the contained objects in the schema.

    However, the following object types are not cloned:

    • External tables

    • Internal (Snowflake) stages

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

  • The CREATE TABLE … CLONE syntax includes the COPY GRANTS keywords, which affect a new table clone as follows:

    • If the COPY GRANTS keywords are used, then the new object inherits any explicit access privileges granted on the original table but does not inherit any future grants defined for the object type in the schema.

    • If the COPY GRANTS keywords are not used, then the new object clone does not inherit any explicit access privileges granted on the original table but does inherit any future grants defined for the object type in the schema (using the GRANT <privileges> … TO ROLE … ON FUTURE syntax).

Additional Rules that Apply to Cloning Objects

Metadata

An object clone inherits the name and structure of the source object current at the time the CREATE <object> CLONE statement is executed or at a specified time/point in the past using Time Travel. An object clone inherits any other metadata, such as comments or table clustering keys, that is current in the source object at the time the statement is executed, regardless of whether Time Travel is used.

Child objects

A database or schema clone 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 name and structure of the source child objects at the time the statement is executed.

Not cloned

Cloning a database or schema does not clone objects of the following types in the database or schema:

  • External tables

  • Internal (Snowflake) stages

Pipes

A database or schema clone includes only pipe objects that reference external (Amazon S3, Google Cloud Storage, or Microsoft Azure) stages; internal (Snowflake) pipes are not cloned.

The default state of a pipe clone is as follows:

  • When AUTO_INGEST = FALSE, a cloned pipe is paused by default.

  • When AUTO_INGEST = TRUE, a cloned pipe is set to the STOPPED_CLONED state. In this state, pipes do not accumulate event notifications as a result of newly staged files. When a pipe is explicitly resumed, it only processes data files triggered as a result of new event notifications.

A pipe clone in either state can be resumed by executing an ALTER PIPE … RESUME statement.

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

Object references

Objects such as views, streams, and tasks include object references in their definition. For example, a view contains a stored query that includes table references. A stream points to a source table. A task calls a stored procedure or executes a SQL statement that references other objects. And so on.

When one of these objects is cloned, either in a cloned database or schema or as an individual object, for those object types that support cloning, the clone inherits references to other objects from the definition of the source object. For example, a clone of a view inherits the stored query from the source view, including the table references in the query.

Pay close attention to whether any object names in the definition of a source object are fully or partially qualified. A fully-qualfied name includes the database and schema names. Any clone of the source object includes these parts in its own definition.

For example:

-- Create a schema to serve as the source for a cloned schema.
CREATE SCHEMA source;

-- Create a table.
CREATE TABLE mytable (col1 string, col2 string);

-- Create a view that references the table with a fully-qualified name.
CREATE VIEW myview AS SELECT col1 FROM source.mytable;

-- Retrieve the DDL for the source schema.
SELECT GET_DDL ('schema', 'source', true);

+--------------------------------------------------------------------------+
| GET_DDL ('SCHEMA', 'SOURCE', TRUE)                                       |
|--------------------------------------------------------------------------|
| create or replace schema MPETERS_DB.SOURCE;                              |
|                                                                          |
| create or replace TABLE MPETERS_DB.SOURCE.MYTABLE (                      |
|   COL1 VARCHAR(16777216),                                                                                                                                                     |
|   COL2 VARCHAR(16777216)                                                                                                                                                     |
| );                                                                       |
|                                                                          |
| CREATE VIEW MPETERS_DB.SOURCE.MYVIEW AS SELECT col1 FROM source.mytable; |
|                                                                          |
+--------------------------------------------------------------------------+

-- Clone the source schema.
CREATE SCHEMA source_clone CLONE source;

-- Retrieve the DDL for the clone of the source schema.
-- The clone of the view references the source table with the same fully-qualified name
-- as in the view in the source schema.
SELECT GET_DDL ('schema', 'source_clone', true);

+--------------------------------------------------------------------------------+
| GET_DDL ('SCHEMA', 'SOURCE_CLONE', TRUE)                                       |
|--------------------------------------------------------------------------------|
| create or replace schema MPETERS_DB.SOURCE_CLONE;                              |
|                                                                                |
| create or replace TABLE MPETERS_DB.SOURCE_CLONE.MYTABLE (                      |
|   COL1 VARCHAR(16777216),                                                                                                                                                   |
|   COL2 VARCHAR(16777216)                                                                                                                                                   |
| );                                                                             |
|                                                                                |
| CREATE VIEW MPETERS_DB.SOURCE_CLONE.MYVIEW AS SELECT col1 FROM source.mytable; |
|                                                                                |
+--------------------------------------------------------------------------------+

If you intend to point a view to tables with the same names in other databases or schemas, we suggest creating a new view rather than cloning an existing view. This guidance also pertains to other objects that reference objects in their definition.

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, Tables, and Streams Only)

  • The AT | BEFORE clause clones a database, schema, table, or stream 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');