Organization And Replication In Snowflake

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 about 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. For using 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 ?
  • 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.

  • Organization feature lets you create, maintain and manage all accounts in your organization across different region and cloud platforms.

  • 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 new feature added in snowflake one can maintain and manage their organization through snowflake UI itself, he/she just have to logged 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 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 of data sharing across the region or in a different account which 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 organization feature you can skip this step and follow from 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 of 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 in the 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 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 primary database into target account

  • Now you have to login into the target account which you just created with help of 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 primary account to target account which is a demo database in this case.


Conclusion

In this blog we have seen some points as below

  • How to create an account from already existing snowflake account using organization feature

  • 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


References

12 views0 comments

Recent Posts

See All