I have two snowflake instances out of which one instance has data and i'm looking for an option to clone data from this instance to another but it seems like there is no such option of cloning across instances in snowflake but we have something called Secure Data Sharing. While I tried that option and realized that the second account does not get create objects access in the schemas or databases which are shared.
Is there any way that both instances/accounts has create access on the shared database?
Thanks, Jyothirmai
You cannot clone from a share and shares are read only. You can of course copy the data to the account where the data is shared:
create table MY_TABLE_CLONE clone "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1"."NATION";
--SQL compilation error: Cannot clone from a table that was imported from a share
create table MY_TABLE_CLONE like "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1"."NATION";
--Copy the DDL
insert into MY_TABLE_CLONE select * from "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1"."NATION";
--Copy the rows
Remember that if you have a lot of data to copy you can always increase the size of the warehouse performing the copy and then set it back to the original size when the copy is done.