Cloning Considerations¶

Cloning and Object Parameters¶

Cloned objects inherit any object parameters that were set on the source object when that object was cloned. If an object parameter can be set on object containers (i.e. account, database, schema) and is not explicitly set on the source object, an object clone inherits the default parameter value or the value overridden at the lowest level. For more information about object parameters, see Parameters.

Cloning and Default Sequences¶

In a table, a column can reference a sequence that generates default values. When a table is cloned, the cloned table references the source or cloned sequence:

• If the database or schema containing both the table and sequence is cloned, the cloned table references the cloned sequence.

• Otherwise, the cloned table references the source sequence.

  For example, if the sequence is defined in a different database or schema, the cloned table references the source sequence. Or if you clone just the table itself, the cloned table references the source sequence.

  If you do not want the new table to continue using the source sequence, run the following command:

  ALTER TABLE <table_name> ALTER COLUMN <column_name> SET DEFAULT <new_sequence>.nextval;
  

  Copy

Cloning and Foreign Key Constraints¶

A table can have a foreign key constraint that references a table that includes the primary key. When a table with a foreign key constraint is cloned, the cloned table references the source or cloned table that includes the primary key:

• If the database or schema containing both tables is cloned, the cloned table with the foreign key references the primary key in the other cloned table.

• If the tables are in separate databases or schemas, the cloned table references the primary key in the source table.

Cloning and Clustering Keys¶

A table can have a subset of columns designated as a clustering key to co-locate similar rows in the same micro-partition. When a table with a clustering key is cloned, the new table is created with a clustering key. By default, Automatic Clustering is suspended for the new table. To resume automatic clustering for the new table, run the following command:

ALTER TABLE <name> RESUME RECLUSTER

Cloning and Stages¶

Individual external named stages can be cloned. An external stage references a bucket or container in external cloud storage; cloning an external stage has no impact on the referenced cloud storage.

Internal (i.e. Snowflake) named stages cannot be cloned.

When cloning a database or schema:

• External named stages that were present in the source when the cloning operation started are cloned.

• Tables are cloned, which means the internal stage associated with each table is also cloned. Any data files that were present in a table stage in the source database or schema are not copied to the clone (i.e. the cloned table stages are empty).

• Internal named stages are not cloned.

Cloning and Pipes¶

When a database or schema is cloned, any pipes in the source container that reference an internal (i.e. Snowflake) stage are not cloned.

However, any pipes that reference an external stage are cloned. This includes any pipe objects where the INTEGRATION parameter is set. This parameter points to a notification integration to enable auto-ingest Snowpipe when loading data from files in Google Cloud Storage or Microsoft Azure blob storage.

When a data file is created in a stage location (e.g. blob storage container), a copy of the notification is sent to every pipe that matches the stage location. This results in the following behavior:

• If a table is fully qualified in the COPY statement in the pipe definition (in the form of db_name.schema_name.table_name or schema_name.table_name), then Snowpipe loads duplicate data into the source table (i.e. the database.schema.table in the COPY statement) for each pipe.

• If a table is not fully qualified in the pipe definition, then Snowpipe loads the data into the table (e.g. mytable) in the source and cloned databases/schemas.

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.

Cloning and Streams¶

Currently, when a database or schema that contains source tables and streams is cloned, any unconsumed records in the streams (in the clone) are inaccessible. This behavior is consistent with Time Travel for tables. If a table is cloned, historical data for the table clone begins at the time/point when the clone was created.

Cloning and Tasks¶

When a database or schema that contains tasks is cloned, the tasks in the clone are suspended by default. The tasks can be resumed individually (using ALTER TASK … RESUME).

Cloning and Governance Objects¶

Masking & row access policies:

The following approach helps to safeguard data from users with the SELECT privilege on the table or view when accessing a cloned object:

• Cloning an individual policy object is not supported.

• Cloning a schema results in the cloning of all policies within the schema.

• A cloned table maps to the same policies as the source table. In other words, if a policy is set on the base table or its columns, the policy is attached to the cloned table or its columns.

  • When a table is cloned in the context of its parent schema cloning, if the source table has a reference to a policy in the same parent schema (i.e. a local reference), the cloned table will have a reference to the cloned policy.

  • If the source table refers to a policy in a different schema (i.e. a foreign reference), then the cloned table retains the foreign reference.

For more information, see CREATE <object> … CLONE.

Also see:

• Cloning external tables and masking policies.

• Cloning external tables and row access policies.

Tags:

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

Tag-based masking policies:

For a tag-based masking policy where the tag is stored in a different schema than the masking policy and table, cloning the schema containing the masking policy and table results in the cloned table being protected by the masking policy in the source schema not the cloned schema.

However, for a tag-based masking policy where the tag, masking policy, and table all exist in the schema, cloning the schema results in the table being protected by the masking policy in the cloned schema, not the source schema.

Cloning of Historical Objects¶

If the source object did not exist at the time/point set in the AT | BEFORE clause, an error is returned.

In the following example, a CREATE TABLE … CLONE statement attempts to clone the source table at a point in the past (30 minutes prior) when it didn’t exist:

CREATE TABLE t_sales (numeric integer) data_retention_time_in_days=1;

CREATE OR REPLACE TABLE sales.public.t_sales_20170522 CLONE sales.public.t_sales at(offset => -60*30);

002003 (02000): SQL compilation error:
Object 'SALES.PUBLIC.T_SALES' does not exist.

Copy

Any child object in a cloned database or schema that did not exist at the specified time/point is not cloned.

The cloning operation fails in the following scenarios:

• If the specified Time Travel time is beyond the retention time of any current child of the cloned database or schema.

• If a pipe object with AUTO-INGEST = TRUE set was recreated (using the CREATE OR REPLACE PIPE syntax) or dropped since the point in time specified in the AT | BEFORE clause. This limitation does not apply to pipe objects created for manual Snowpipe ingest using the REST API (i.e. with AUTO-INGEST = FALSE).

Cloning of Historical Object 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.