CREATE <object> … CLONE¶

Clone objects using Time Travel¶

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

For databases and schemas, CLONE supports the IGNORE TABLES WITH INSUFFICIENT DATA RETENTION parameter to skip any tables that have been purged from Time Travel (for example, transient tables with a one day data retention period).

Syntax¶

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

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

CREATE [ OR REPLACE ] DYNAMIC TABLE <name>
  CLONE <source_dynamic_table>
        [ { AT | BEFORE } ( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> } ) ]
  [
    TARGET_LAG = { '<num> { seconds | minutes | hours | days }' | DOWNSTREAM }
    WAREHOUSE = <warehouse_name>
  ]

CREATE [ OR REPLACE ] EVENT TABLE <name>
  CLONE <source_event_table>
    [ { AT | BEFORE } ( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> } ) ]

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

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

Time Travel Parameters¶

{ AT | BEFORE } ( { TIMESTAMP => timestamp | OFFSET => time_difference | STATEMENT => id } )

The AT | BEFORE clause accepts one of the following parameters:

TIMESTAMP => timestamp

Specifies an exact date and time to use for Time Travel. The value must be explicitly cast to a TIMESTAMP.

OFFSET => time_difference

Specifies the difference in seconds from the current time to use for Time Travel, in the form -N where N can be an integer or arithmetic expression (e.g. -120 is 120 seconds, -30*60 is 1800 seconds or 30 minutes).

STATEMENT => id

Specifies the query ID of a statement to use as the reference point for Time Travel. This parameter supports any statement of one of the following types:

• DML (e.g. INSERT, UPDATE, DELETE)

• TCL (BEGIN, COMMIT transaction)

• SELECT

The query ID must reference a query that has been executed within the last 14 days. If the query ID references a query over 14 days old, the following error is returned:

Error: statement <query_id> not found

To work around this limitation, use the timestamp for the referenced query.

IGNORE TABLES WITH INSUFFICIENT DATA RETENTION

Ignore tables that no longer have historical data available in Time Travel to clone. If the time in the past specified in the AT | BEFORE clause is beyond the data retention period for any child table in a database or schema, skip the cloning operation for the child table. For more information, see Child Objects and Data Retention Time.

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.

  Schemas:

  If you specify the WITH MANAGED ACCESS clause, the required privileges depends on whether the source schema is a managed or unmanaged schema. For details, see CREATE SCHEMA privileges.

  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 cloned when you run the CREATE DATABASE … CLONE command to clone a database. However, if you clone other database objects, such as a schema or table, database roles in the database are not cloned with the schema or table.

  • 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 and CREATE EVENT 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.

Java UDF:

A Java UDF can be cloned when the database or schema containing the Java UDF is cloned. To be cloned, the Java UDF must meet certain conditions. For more information, see Limitations on Cloning.

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, Tables, and Event 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.

    As a workaround for child objects that have been purged from Time Travel, use the IGNORE TABLES WITH INSUFFICIENT DATA RETENTION parameter of the CREATE <object> … CLONE command. For more information, see Child objects and data retention time.

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

000707 (02000): Time travel data is not available for <object_type>
<object_name>. The requested time is either beyond the allowed time
travel period or before the object creation time.

... AT(TIMESTAMP => '2023-12-31 12:00:00')               -- fails
... AT(TIMESTAMP => '2023-12-31 12:00:00'::TIMESTAMP)    -- succeeds

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

Clone a database and all its objects as they existed four days ago and skip any tables that have a data retention period of less than four days:

CREATE DATABASE restored_db CLONE my_db
  AT (TIMESTAMP => DATEADD(days, -4, current_timestamp)::timestamp_tz)
  IGNORE TABLES WITH INSUFFICIENT DATA RETENTION;

Copy