Is it possible to clone databases, tables, etc. across two snowflake accounts?

Solved6.56K viewsCloning and Time Travel
0

Is it possible to clone databases, tables, etc. across two snowflake accounts?

Question is closed for new answers.
Frank Bell Selected answer as best April 13, 2024
0

Yes, it is possible to clone databases, tables, and other objects across two Snowflake accounts. This can be done using Snowflake's cross-account data sharing feature, which allows you to securely share data between Snowflake accounts.

Here are the general steps to clone an object from one Snowflake account to another using cross-account data sharing:

Share the Object: In the source Snowflake account, you'll need to share the object you want to clone with the destination account. This can be done by creating a share object and granting the destination account the necessary privileges to access it. For example:
sql
Copy code
-- Create a share object
CREATE SHARE myshare;

-- Grant access to the share object
GRANT USAGE ON SHARE myshare TO ACCOUNT destination_account;
Copy the Object: In the destination Snowflake account, you can use the COPY INTO command to copy the object from the shared location to your own account. For example:
sql
Copy code
-- Copy a table from the shared location to your own account
COPY INTO mytable FROM '@myshare.mydb.myschema.mytable';
Optionally Modify the Object: Once you have the object in your own account, you can modify it as needed. For example, you could rename the table or modify its schema.

Re-Share the Object (Optional): If you want to share the modified object with other accounts, you can create a new share object and grant the necessary privileges. For example:

sql
Copy code
-- Create a new share object with the modified table
CREATE SHARE mynewshare;

-- Grant access to the new share object
GRANT USAGE ON SHARE mynewshare TO ACCOUNT other_account;

-- Share the modified table
GRANT SELECT ON TABLE mynewtable TO SHARE mynewshare;
These are the basic steps to clone an object from one Snowflake account to another using cross-account data sharing. Note that there may be additional considerations, such as permissions, encryption, and networking, depending on your specific use case. You can find more information and examples in the Snowflake documentation.

Frank Bell Selected answer as best April 13, 2024
Feedback on Q&A