Zero clone in snowflake-Independent copy of a database, table, or individual file, without duplicating the underlying data.

Snowflake

In Snowflake, a “zero clone” is a feature that allows users to create a new, independent copy of a database, table, or individual file, without duplicating the underlying data. Instead, the new copy references the same data as the original, effectively creating a “zero-copy clone.” This feature is useful for creating test environments, or for creating multiple copies of a table or database for different users or teams, without incurring the cost of duplicating the data.

Here’s an example of how zero clone works in Snowflake:

Snowflake Zero Clone @ Freshers.in

Let’s say you have a large table called “freshers_in_views” in your Snowflake database, and you want to create a new copy of that table for testing purposes. Instead of duplicating the entire table and all its data, you can use the zero clone feature to create a new table called “freshers_in_views_test” that references the same data as the original “freshers_in_views” table.

The new “freshers_in_views_test” table will have the same structure and data as the original “freshers_in_views” table, but it will not take up any additional space in your Snowflake account, as it references the same data. You can then perform any testing or experimentation on the “freshers_in_views_test” table without affecting the original “freshers_in_views” table.

It’s also important to note that the zero-cloned objects have independent access controls, quota, and resource monitoring, making them isolated from the original objects.

The syntax for creating a zero clone in Snowflake using SQL is as follows:

Syntax

CREATE [OR REPLACE] [TEMPORARY] CLONE <target> [AS <new_name>]

CREATE is the command to create a new object in Snowflake.
OR REPLACE is an optional clause that allows you to overwrite an existing object with the same name.
TEMPORARY is an optional clause that allows you to create a temporary object that will be automatically dropped when the current session ends.
CLONE is the keyword that indicates that you are creating a zero clone.
<target> is the name of the existing object that you want to clone (i.e, the name of the database, table, or file you want to clone).
AS <new_name> is an optional clause that allows you to specify a new name for the cloned object. If you do not specify a new name, Snowflake will use the same name as the original object with “_clone” appended to it.
For example, to create a zero clone of an existing table called “freshers_in_views” called “freshers_in_views_test”:

CREATE CLONE <span style="color: #000000;">freshers_in_views</span> AS <span style="color: #000000;">freshers_in_views</span>_test;

Example, to create a temporary zero clone of an existing table called “freshers_in_views” called “freshers_in_views_temp”:

CREATE TEMPORARY CLONE <span style="color: #000000;">freshers_in_views</span> AS <span style="color: #000000;">freshers_in_views</span>_temp;

Access required to create zero clone in snowflake

In order to create a zero clone in Snowflake, the user must have the following access rights:

USAGE privilege on the schema that contains the object to be cloned. This privilege grants the user the ability to access the object and its metadata.

CREATE privilege on the schema that will contain the cloned object. This privilege grants the user the ability to create a new object in the schema.

MODIFY privilege on the warehouse that will be used to run the clone operation. This privilege grants the user the ability to execute the clone command and create the new cloned object.

These privileges can be granted by a user with the SECURITYADMIN or SYSADMIN role, and can be granted to a user, role or a shared schema.

For example, to grant a user “johndoe” the rights to create a zero clone on the schema “sales”:

GRANT USAGE, CREATE ON SCHEMA sales TO USER johndoe;
GRANT MODIFY ON WAREHOUSE mywarehouse TO USER johndoe;

It’s also important to note that, if the user is trying to create a zero clone of a table or database that is shared, the user must have the rights to access the shared schema, as well as privileges to create and modify the objects within the shared schema.

It is good practice to review the access rights and privileges periodically to ensure that they are correctly assigned to the right users and roles.

Snowflake blogs
Snowflake interview Questions

Author: user

Leave a Reply