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?

CodePudding user response：

Perhaps try creating objects using the LIKE option? It doesn't work on databases unfortunately but does on schemas and tables etc. This creates a copy of the table with no data

So get the list of schemas and tables of your db and loop through them

CREATE TABLE myTable2 LIKE myTable1

Another option would be to use the CREATE command with a SELECT that returns no rows

CREATE TABLE myTable2 AS SELECT * FROM myTable1 WHERE 1=0

Not sure if any of them will be faster though

CodePudding user response：

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.