Author: Sakshi Agrawal
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
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 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.
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