- Sep 19, 2022
- 4 min read

Organization And Replication In Snowflake

Updated: Mar 14

Author: Sakshi Agrawal

Overview

Creation of accounts in Snowflake can be done with the help of commands as well as with the help of UI, which is the new organization feature introduced by snowflake; we are all very well aware of how to create an account under the organization using commands on your worksheet but how to create an account using UI will see in this blog along with that as we know Snowflake provides account replication as well as database replication, now how the organization feature makes a good impact and ease the task of replication we will read in this blog as we proceed further along with some implemented examples of database replication and account replication.

Let's say I have to create an account under the same organization. The first thing that comes to my mind is it has to be handled and looked after by a very dedicated role in Snowflake, which is orgadmin. To use the organization feature, I have to be logged in as an orgadmin only.

Let's take a look at what exactly an organization is.

What is the organization in Snowflake ?

The organization is a first-class snowflake object; it simplifies account management and billing, Database Replication and Failover/Failback, Snowflake Secure Data Sharing, and other account administration tasks.
The organization feature lets you create, maintain and manage all accounts in your organization across different regions and cloud platforms.
The organization feature is mainly handled and looked after by the orgadmin role, orgadmin can view the account properties but does not have access to account data.
Now with a new feature added in snowflake, one can maintain and manage their organization through snowflake UI itself; he/she just has to log in as an orgadmin role; we will read about it more as we proceed further with this blog.
Snowflake provides historical usage data for all accounts in your organization via views in the organization usage schema.
Monitoring the usage of all your accounts, self-service account creation, central view of all accounts within your organization, data availability, and durability by leveraging data replication and failover are some of the benefits of the organization.

Creation of accounts through snowflake UI after enabling the orgadmin role .

Now as you can see, the account has been created under the same organization with just a few simple steps; these accounts will have different usernames and passwords with different account identifiers. You can also login into respective accounts separately with the help of URLs.

Database replication / need of database replication

Most of the time, whenever there is a need for data sharing across the region or in a different account that is hosted on a different cloud platform / different region within the same organization, one should make use of this feature which is database replication.
Database replication enables replicating databases between Snowflake accounts (within the same organization) and keeping the database objects and stored data synchronized.
Database replication is supported across the cloud and across the region.

Enabling the database replication for different accounts .(single / multiple)

Database replication for Multiple Accounts

alter database <database_name> enable replication to accounts <region_name.account1_name>,<region_name.account1_name>

Database replication for Single Account

alter database <database_name> enable replication to accounts <region_name.account_name>

For ex -

alter database <database_name> enable replication to accounts AWS_AP_SOUTHEAST_1.ACCOUNT_SINGAPORE

Demo where we have replicated a database in cross cloud region

Step 1 - Setup Data Replication

Before configuring data replication, you must create an account in a region where you wish to share data and link it to your local account.
The below account creation is with the help of commands, but as you have already created an account with the organization feature, you can skip this step and follow step 2.

create account TEST_ACCOUNT1

admin_name = admin

admin_password = '********'

first_name = Snowflake

last_name = admin

email = 'Snowflakeadmin@gmail.com'

edition = BUSINESS_CRITICAL

region = AZURE_WESTUS2;

Step 2 - Enable replication for the account

select system$global_account_set_parameter('<account_locator>',

'ENABLE_ACCOUNT_DATABASE_REPLICATION', 'true');

Here account locator should be the account for which you wish to enable the replication

Step 3 -Promote the local database to serve as primary database - (in source account)

As shown below screenshot this is our primary account from where we have to enable the replication; here we have to promote the local database, which is your demo database, to act as a primary database
Below are the commands that were executed on snowflake
Use role orgadmin

Show regions

Show organization accounts

Select system$global_account_set_parameter(‘account_locator’,’ENABLE_ACCOUNT_DATABASE_REPLICATION’, ‘true’);

alter database DEMO enable replication to accounts AZURE_WESTUS2.TEST_ACCOUNT1;

Step 4 - create replica from the primary database into the target account

Now you have to login into the target account which you just created with the help of the organization feature or worksheet commands.
After that, run some queries mentioned below
Create database DEMO as replica of AWS_AP_SOUTH_1.tb82673.DEMO
Alter database DEMO refresh

Now as you can see, we have successfully replicated the database from the primary account to the target account, which is a demo database in this case.

Conclusion

In this blog, we have seen some points below

How to create an account from an already existing snowflake account using the organization feature
The organization feature can only be accessible by orgadmin role
How to enable replication for single/multiple accounts
Finally, a demo around database replication in cross-cloud region