CREATE <object> … CLONE¶

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

Database roles

CREATE [ OR REPLACE ] DATABASE ROLE [ IF NOT EXISTS ] <database_role_name>
  CLONE <source_database_role_name>

Other Schema Objects

CREATE [ OR REPLACE ] { ALERT | FILE FORMAT | SEQUENCE | STAGE | STREAM | 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).

• Parameters that are explicitly set on a source database, schema, or table are retained in any clones created from the source container or child objects.

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

  Database roles

  OWNERSHIP on the database role and the CREATE DATABASE ROLE privilege on the target database.

  Tables

  SELECT

  Alerts, 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 database roles:

  • A database role is not cloned when you run the CREATE CLONE command to clone a database or other objects that are contained in the database. You must use the CREATE DATABASE ROLE … CLONE command to clone a database role to the target database.

  • If the database role is already cloned to the target database, the command fails. If this occurs, drop the database role from the target database and try the CLONE command again.

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

  However:

  • A cloned table does not include the load history of the source table. One consequence of this is that data files that were loaded into a source table can be loaded again into its clones.

  • Although a cloned table replicates the source table’s clustering keys, the new table starts with Automatic Clustering suspended – even if Automatic Clustering is not suspended for the source table.

• 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> … ON FUTURE syntax).

  Note

  If the statement is replacing an existing table of the same name, then the grants are copied from the table being replaced. If there is no existing table of that name, then the grants are copied from the source table being cloned.

• Regarding metadata:

  Attention

  Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata Fields in Snowflake.

• CREATE OR REPLACE <object> statements are atomic. That is, when an object is replaced, the old object is deleted and the new object is created in a single transaction.

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.

Tags

Cloning a database or schema affects tags in that database or schema as follows:

• Tag associations in the source object (e.g. table) are maintained in the cloned objects.

• For a database or a schema:

  The tags stored in that database or schema are also cloned.

  When a database or schema is cloned, tags that reside in that schema or database are also cloned.

  If a table or view exists in the source schema/database and has references to tags in the same schema or database, the cloned table or view is mapped to the corresponding cloned tag (in the target schema/database) instead of the tag in the source schema or database.

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 or alert calls a stored procedure or executes a SQL statement that references other objects.

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-qualified 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; |
|                                                                                |
+--------------------------------------------------------------------------------+

Copy

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.

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;

Copy

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

CREATE SCHEMA mytestschema_clone CLONE testschema;

Copy

Clone a table at its current state:

CREATE TABLE orders_clone CLONE orders;

Copy

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

Copy

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'));

Copy

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');

Copy