Zero Copy Cloning in snowflake cloud data warehouse

What is Zero-Copy Cloning: 

Snowflake cloning is a meta data-only related operation only, there is no physical data copied from one environment to another, clone data point out to  the actual database and further CRUD operations keep separately in new environment which do not effect the old data,  it is important to note that there is no data movement during a clone operation, only creation of new meta data. The meta data for these new database objects simply point back to the original source data. There is no additional cost when creating a clone. You will incur the cost of storage when DML ops is applied to the cloned data.



Create a clone of the table:

CREATE OR REPLACE TABLE demo_db.public.employees_clone CLONE employees;

 

Create a clone of the database:

 

CREATE or replace DATABASE demo_db_clone CLONE demo_db;

 

Clone objects are writable and is independent from clone source database, changes made on either cloned object or source object are not a part of other. ðŸ˜Š interesting. This makes clone very easy and without any cost.

There are some restrictions while cloning of objects or databases:

Ø  Network policy, Resource Monitor , Warehouse Objects and other Account level objects are out of scope during cloning operations.

Ø  The database level grants are not inherited from the source databases, they are reset with the ownership privileges.

Ø  Table objects in a clone that utilizes SEQUENCE objects are not automatically updated to reference cloned objects. You will need to manually update these references in the cloud database as follows:


Alter table test
Alter column <sequence> set default clone_db.clone_schema.original_sequencename.nextval

 

Ø  Pipe and Internal stages can not be cloned

 

It is highly recommended that such objects are scripted and ideally version controlled so that they can be easily ported and reused.



Regards,

Vidit - A data scientist in snowflake data warehouse  

Comments

Post a Comment

Popular posts from this blog

error_on_column_count_mismatch=false : Number of columns in file does not match that of the corresponding table use file format option error_on_column_count_mismatch=false to ignore this error File

Image
Error: Number of columns in file does not match that of the corresponding table  use file format option error_on_column_count_mismatch=false to ignore this error File  this error normally occurs when number of columns in staged file and table are not match, I would give you an example to check this and a solution, that can work for you  😊 if you check the file format you choose and your staged file, you format and data is looking like the below images, as I do have the first column with blank value and somewhere I do have data there if you uncheck the error_on_column_count_mismatch to false, you can better about this now try to execute the select query on stage file. I am using a Table stage and you can see on above screen, columns data are not coming as per table column so you do need to create one more column in table to insert it correctly. It is very necessary to design the model properly, otherwise a mistake can cost you, however it is good that get and put do...
Read more

Practice Set 1: Snowpro Certification Practice Set with more than 250 Unique Questions

Image
Snowflake SNOWPRO Core Certification Practice Set Questions: Note : Please verify correct/incorrect answers using green / red highlighter. Top of Form Question 1:  Correct Can you share data with multiple consumers with row based access control? ·          NO ·          YES (Correct) Explanation Secure View with Account mapping using CURRENT_ACCOUNT(); Bottom of Form Top of Form Question 2:  Correct A user can connect Snowflake in multiple ways. Please select ·          Command line clients (e.g. SnowSQL) (Correct) ·          A web-based user based (Correct) ·          Native connector (e.g. Python) that can be used to develop applications (Correct) ·          ODBC and JDBC drivers ...
Read more

Snowflake Automation using SnowSql CLI || Create Azure Devops CI CD using Snowsql CLI

Image
 Hello Guys, I am writing this blog to understand how we can automate snowflake databases using CI CD pipelines in Devops, I am using Azure Devops tool to automate snowflake pipeline.  To start it lets have a repository and create a folder inside like called Migrations and inside it create a .sql file or you can have multiple .sql files if you want to execute the multiple files. file1.sql -- create a table using a Sequence  use   role  sysadmin; use   database  snowstages_db; use   schema   public ; create   or   replace   table  test ( Id  integer   default  SEQ_1.nextval, name   varchar ( 50 ), Dept_number  int ); file2.sql -- create a clone table using first one use   role  sysadmin; use   schema   public ; Create  transient  table   if   not   exists  Test_Clone1  clone Test; Go inside the Library in Devops Portal and add Config variab...
Read more