Cloning considerations¶

Cloning and managed access schemas¶

If you clone a schema and 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.

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 (that is, account, database, schema) and isn’t 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 don’t 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¶

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

You can optionally clone internal named stages when you clone a database or schema.

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 aren’t copied to the clone (that is, the cloned table stages are empty).

• Internal named stages are cloned if you use the INCLUDE INTERNAL STAGES clause. For more information, see the internal stage cloning usage notes.

Cloning and Apache Iceberg™ tables¶

Cloning and event tables¶

When cloning an event table, you can clone to and from only event tables. In other words, you can not clone from a regular table to an event table, nor from an event table to a regular table.

Cloning and pipes¶

When a database or schema is cloned, any pipes in the source container that reference an internal (that is, 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 (for example, 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 (that is, 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 (for example, 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 don’t 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 search optimization¶

You can clone tables that have the Search Optimization Service enabled. When you do, the corresponding search access path is a zero-copy clone. However, if the cloned search access path isn’t up-to-date, it might incur maintenance costs, even if the cloned table doesn’t change, because the search access path must catch up with the current state of the cloned table. For more information about cloning and search optimization, see Cloning the table, schema, or database.

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 alerts¶

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

To resume a suspended alert, you can use the ALTER ALERT … RESUME command.

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.

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

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

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, the table is not protected by the tag-based masking policy set on the source schema or database. The table is protected by the tag-based masking policy set on the target schema or database, if there is a tag-based masking policy set on the target schema or database.

Cloning and differential privacy¶

Cloning a table or view that is protected by differential privacy results in the following behavior.

Cloning and database roles¶

You can clone a database role using the CREATE DATABASE ROLE … CLONE command if the database role doesn’t already exist in the target database. For details, see CREATE <object> … CLONE.

Cloning and Java UDFs¶

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.

Cloning and instances of Snowflake classes¶

An instance of the CUSTOM_CLASSIFIER is cloned when the schema that contains the instance is cloned. Cloning of instances of other Snowflake classes is not supported.

Cloning of historical objects¶

If the source object didn’t 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);

Copy

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

Any child object in a cloned database or schema that didn’t exist at the specified time/point isn’t 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.

  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 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 doesn’t apply to pipe objects created for manual Snowpipe ingest using the REST API (that is, with AUTO_INGEST = FALSE).

• If the IGNORE HYBRID TABLES parameter isn’t specified and any hybrid tables exist in the specified database or schema.