CODEWITHSUNDEEP
snowflake-cloud-data-platform
jeffcott
jeffcott

Reputation: 13

Clone a shared DB in Snowflake

I am trying to clone a shared database in Snowflake and it doesn't look like that is allowed. We are trying to do a nightly refresh of our QA/Dev databases based off our Prod snowflake account. Right now we have a shared read only copy of the prod database in the QA/Dev accounts but I would like to create a read/write copy of that for development purposes. I was hoping there was a better solution than individually copying each table into the new database. Thanks in advance for your help!

Upvotes: 0

Views: 3979

Answers (2)

Charlie B
Charlie B

Reputation: 95

If this is for copying data from the data sharing function. You can't clone a data share DB.

So what you need to do is run the following command for each table in the schema, and set up a task to schedule this.

create table copied_db.<shema_name>.<table_name> 
as select * from data_share_db.<shema_name>.<table_name_to_be_copied> ;

Tasks: https://docs.snowflake.com/en/user-guide/tasks-intro.html

Streams - to make task incremental: https://docs.snowflake.com/en/user-guide/streams.html#streams-on-shared-tables

Upvotes: 4

NickW
NickW

Reputation: 9778

you cannot clone between different accounts. This article explains how to achieve what you want: DTAP Environments.

Basically you need to:

  1. Link you accounts (if they're not already linked)
  2. Replicate Prod to your environment(s) - which creates a readonly copy
  3. Clone the RO copy which creates a read-write DB

Upvotes: 0

Related Questions