CODEWITHSUNDEEP
snowflake-cloud-data-platformsnowflake-schemasnowflake-connector
Avenger
Avenger

Reputation: 877

Clone data across snowflake accounts on schema basis

I have two snowflake account. I want to clone data from one some schemas from database of one account to schemas of database in another account.

Account1: https://account1.us-east-1.snowflakecomputing.com Database: DB_ONE SCHEMAS: A1SCHEMA1, A1SCHEMA2, A1SCHEMA3, A1SCHEMA4(has external tables)

Account2: https://account2.us-east-1.snowflakecomputing.com Database: DB_TWO SCHEMAS: A2SCHEMA1, A2SCHEMA3, A2SCHEMA4(has external tables)

Both accounts are under same organization.

I want to clone A1SCHEMA1 of DB_ONE from account1 to A2SCHEMA1 of DB_TWO in account2.

Is it possible? If you, what are the instruction. I have found info on db level but not on schema level. Also, I would need to refresh the data from clone on demand basis.

Can I clone the A1SCHEMA4 of DB_ONE from account1 to A2SCHEMA4 of DB_TWO in account2? as it has external tables.

Note: DB_ONE is not created from a share. Basically I want to get data from prod to lower env. replicate or clone but I want to refresh it as well.

Upvotes: 0

Views: 2132

Answers (1)

Mike Walton
Mike Walton

Reputation: 7369

Since your goal appears to be to leverage prod data for development purposes, then data sharing isn't a good solution, since it is read-only. Data replication is probably the best solution here, but since you want it at a schema-level, then you're going to need to change things up a bit.

  1. Create a schema-level clone on Prod into a separate database on Prod
  2. On dev, create a database that is a replica of the Prod clone database. This will be a read-only replica of prod, so you'll need the next step.
  3. Once the prod clone is replicated to dev, you can then clone that database/schema into your persistent development structures at a schema-level.

This sounds like a lot of hops of data, but keep in mind that clones are zero-copy, so the only true data movement is across the replication process. This will cost you some replication processing, but since the 2 accounts are in the same region, you will not be charged for data egress and the process will run pretty fast.

Upvotes: 1

Related Questions