Ad

Fastest Way To Create A Copy Of Snowflake Database

I need to make a copy of the database to check my code before release in snowflake.

Currently, we use clone method for creating copy of database: https://docs.snowflake.com/en/sql-reference/sql/create-clone.html

But it's take around 15 min. I'm trying to find faster way to create a copy (with or without copying data - it doesn't matter).

What I tried:

  • get_ddl function for whole database - returns objects without saving order of creating creating objects

Maybe someone know better options for it?

Ad

Answer

You can clone several schemas in parallel at the same time.

Write a stored procedure that will create a root TASK and sub tasks for each schema you will clone in the database. Then you run root TASK.

Sample code.

At the beginning, the 'TASK root' which creates the database:

CREATE OR REPLACE TASK root_clone_task
WAREHOUSE = 'COMPUTE_WH'
AS
CREATE OR REPLACE DATABASE CLONE_MY_DB;

Then as many sub-tasks as you have schema to clone, or preferably 8 sub-tasks and balanced cloning between them (by default WH supports 8 threads in parallel):

CREATE OR REPLACE TASK sub_clone_task1
WAREHOUSE = 'COMPUTE_WH'
AFTER root_clone_task
AS
CREATE OR REPLACE SCHEMA CLONE_MY_DB.SCHEMA1 CLONE MY_DB.SCHEMA1;

CREATE OR REPLACE TASK sub_clone_task2
WAREHOUSE = 'COMPUTE_WH'
AFTER root_clone_task
AS
CREATE OR REPLACE SCHEMA CLONE_MY_DB.SCHEMA2 CLONE MY_DB.SCHEMA2;

Then we need to resume all the sub-tasks, this can be done one at a time using ALTER TASK <name> RESUME or we can use the command:

select system$task_dependents_enable('MY_DB.PUBLIC.root_clone_task');

You will get the error message:

"Task should have a SCHEDULE or AFTER to be resumed."

If you want to avoid this you can define SCHEDULE in root TASK, but all child tasks will be enabled.

Finally, just execute the root TASK:

EXECUTE TASK root_clone_task;

Of course, you can automate this by writing a stored procedure with dynamic code that will generate all the necessary tasks.

At the end, it seems to me that it takes most of the time to move metadata and information about permissions, and in many cases we remove these permissions later to give them a completely different one. Therefore, I proposed a new idea: Add an option WITHOUT PRIVILEGES to the CLONE option.

Please go to the website: https://community.snowflake.com/s/ideas and search for the idea:

Add an option WITHOUT PRIVILEGES to the CLONE option and vote for it, I hope it will significantly speed up the cloning process.

Ad
source: stackoverflow.com
Ad