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 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
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 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.
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