0

Is it possible to clone schemas selectively in Snowflake?

For e.g.

Original:

DB_OG
--schema1
--schema2
--schema3

Clone: DB_Clone
--schema1
--schema3

vijayjain024
  • 43
  • 2
  • 5
  • Update: Solved this by creating an empty schema and then recursively cloning schemas and permission for the underlying objects in the schema. Created an yaml file that had fixed set of steps (cloning as well as changing ownership of underlying objects) and called that yaml recursively for each schema from a python script. – vijayjain024 Jun 21 '20 at 19:52

2 Answers2

2

The CREATE <object> … CLONE statement does not support applying a filter or pattern or multiple objects, and its behaviour is to recursively clone every object underneath:

For databases and schemas, cloning is recursive:

Cloning a database clones all the schemas and other objects in the database.

There are a few explicit ways to filter the clone:

  • Clone the whole database, then follow up with DROP SCHEMA commands to remove away unnecessary schema
  • Create an empty database and selectively clone only the schemas required from the source database into it

Both of the above can also be automated by logic embedded within a stored procedure that takes a pattern or a list of names as its input and runs the appropriate SQL commands.

Community
  • 1
  • 1
  • Thanks for the response. I used the second method of creating an empty database and then recursively adding schemas provided by the user input. The logic was embedded in a python script though. – vijayjain024 Jun 21 '20 at 19:48
0

Currently the elimination of certain schemas and cloning all the other schema's of a database is not supported.

If the use case has schemas that are not required, are the recently created schemas, you could use the AT | BEFORE clause to eliminate the schemas(clone till a particular timestamp, that will eliminate the schemas that are created post the mentioned timestamp).

Ref: https://docs.snowflake.com/en/sql-reference/sql/create-clone.html#notes-for-cloning-with-time-travel-databases-schemas-tables-and-streams-only

Other options include dropping the schemas post the cloning operation or cloning only the required schemas

sprethepa
  • 544
  • 2
  • 4