Cloning Introduction

Snowflake provides a convenient feature for cloning various database objects, including databases, schemas, database objects, stages, file formats, sequences, and tasks, with the execution of just one line command, also known as 'zero copy clone'. This process involves taking a snapshot of the source data and attaching it to the cloned object, making the cloned object independent of the source object and writable. However, it's important to note that only the metadata pointing to the underlying data changes; the underlying data itself is not copied.

When cloning a database or schema, the clone inherits all granted privileges on the clones of all child objects contained in the source object, although the clone of the container itself (database or schema) does not inherit the privileges granted on the source container. Furthermore, while most CREATE <object> ... CLONE statements do not copy grants on the source object to the object clone, commands that support the COPY GRANTS clause, such as CREATE TABLE or CREATE VIEW, optionally enable the copying of grants to object clones, except for OWNERSHIP.

With the WITH MANAGED ACCESS clause, the required privileges for cloning a schema depend on whether the source schema is managed or unmanaged. Additionally, cloned objects inherit any object parameters that were set on the source object when it was cloned.

When dealing with sequences used in a table column, if the database or schema containing both the table and sequence is cloned, the cloned table references the cloned sequence. Otherwise, the cloned column references the source sequence. If it's necessary to stop the new table from continuing to use the source sequence, an ALTER TABLE command can be executed to set a new sequence as the default for the column.

Cloning Considerations:

Cloning and Object Grants

When cloning a database or schema in Snowflake, the clone inherits all granted privileges on the clones of all child objects contained within the source object. This includes schemas, tables, views, and other objects. However, it's important to note that the clone of the container itself (i.e., the database or schema) does not inherit the privileges granted on the source container. While CREATE <object> … CLONE statements for most objects do not copy grants from the source object to the object clone, specifying the COPY GRANTS parameter in a CREATE TABLE statement will copy all privileges, except OWNERSHIP, from the source table to the new table.

Cloning and Managed Access Schema

“When you clone a schema in Snowflake and specify the WITH MANAGED ACCESS clause, the privileges required for the operation depend on whether the source schema is managed or unmanaged. In Snowflake, a managed schema is one that has security access policies managed by Snowflake, while an unmanaged schema has its security access policies defined by the user. Therefore, the privileges needed for cloning with managed access will differ based on the type of schema being cloned. Snowflake handles the security access policies differently for managed and unmanaged schemas, hence the variance in required privileges during the cloning process.

Cloning and Object Parameters

When an object is cloned in Snowflake, it inherits any object parameters that were previously set on the source object. This means that if specific parameters were configured for the source object, such as settings related to data compression or partitioning, the cloned object will retain those settings. However, if a parameter was not explicitly set on the source object, the default parameter values will be applied to the cloned object. This ensures consistency in configuration between the original and cloned objects while allowing flexibility for customization based on individual requirements.

Cloning and Default Sequence

When cloning both a table and its associated sequence in Snowflake, the column using the sequence will reference the cloned sequence. However, if either the table or the sequence is cloned independently, the column will reference the original sequence. Additionally, if the sequence is defined in a different database or schema, the cloned table will reference the source sequence, regardless of whether the table is cloned along with it. Conversely, when only the table itself is cloned, the cloned table will still reference the source sequence. This ensures that the appropriate sequence is referenced based on the cloning context, maintaining consistency in column values across the database or schema boundaries.

Cloning and Foreign Key Constraint

When cloning a database or schema in Snowflake, the behavior of foreign key references depends on the relationship between the tables being cloned. If both tables reside within the same database or schema and are cloned together, the cloned table with the foreign key will reference the primary key in the other cloned table. However, if the tables are located in separate databases or schemas, the cloned table will reference the primary key in the original source table. This distinction ensures that the integrity of foreign key relationships is maintained during the cloning process, regardless of the organizational structure of the tables.

Cloning and Clustering

When cloning a table in Snowflake that has a clustering key, the resulting new table will also have a clustering key. However, by default, the Automatic Clustering feature is suspended for the new table. This means that the new table will not automatically undergo clustering, which is the process of organizing data within the table based on the clustering key. To resume automatic clustering for the new table, administrators can execute the following command: ALTER TABLE <name> RESUME RECLUSTER. This command instructs Snowflake to resume the automatic clustering process for the specified table, thereby optimizing data organization and improving query performance.

Cloning and Stages

In Snowflake, certain rules apply when cloning stages within a database or schema. While internal named stages cannot be cloned, individual external named stages can be cloned without affecting the referenced cloud storage. When cloning a database or schema, external named stages are cloned along with any internal stages associated with cloned tables. However, it's important to note that while internal stages are cloned, the data within them is not copied over. Therefore, internal named stages are not cloned during this process.

Cloning and Event Tables

You cannot clone from a regular table to an event table or vice versa

Cloning and Pipes

When cloning a database or schema in Snowflake, certain pipes behave differently depending on their references to internal or external stages. If a pipe in the source container references an internal stage, such as one provided by Snowflake itself, it will not be cloned along with the database or schema. However, if the pipe references an external stage, it will be cloned along with the database or schema. This distinction ensures that pipes referencing Snowflake's internal stages remain unchanged after cloning, while those tied to external stages are replicated to maintain consistency in the cloned environment.

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

Cloning and Tasks

When a database or schema that contains tasks is cloned, the tasks in the clone are suspended by default

Cloning and Alerts

When a database or schema that contains alerts is cloned, the alerts in the clone are suspended by default.

Cloning and Masking

When a schema is cloned in Snowflake, all policies within that schema are also cloned. However, it's important to note that cloning individual policy objects is not supported. When a table is cloned, it retains the same policies as the source table. This means that any policies set on the base table or its columns are attached to the cloned table or its columns. If the source table refers to a policy in a different schema (a foreign reference), the cloned table maintains this foreign reference. In the case of a tag-based masking policy where the tag, masking policy, and table all exist in the schema, cloning the schema ensures that the table is protected by the masking policy in the cloned schema. However, if the table is cloned or moved to a different schema or database and was originally protected by a tag-based masking policy set on the schema or database, it will not be protected by the tag-based masking policy set on the source schema or database.

Cloning and Tags

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

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

Cloning and Database Roles

·      You can clone a database role using the CREATE DATABASE ROLE … CLONE command if the database role does not already exist in the target database.

Cloning and DML

·      Refrain, if possible, from executing DML transactions on the source object (or any of its children) until after the cloning operation completes

Cloning Using Time Travel (clone objects at a specific time/point in the past)

When cloning a database or schema in Snowflake, any child object that did not exist at the specified time or point is not cloned. If the specified Time Travel time exceeds the retention time of any current child of the cloned database or schema, the cloning operation will fail. The object clone inherits the name and structure of the source object as it exists at the time the CREATE <object> … CLONE statement is executed, or at a specified time/point in the past using Time Travel. Additionally, the object clone inherits any other metadata, such as comments or table clustering keys, from the source object at the time the statement is executed, regardless of whether Time Travel is utilized.