Snowflake Admin Series: Organization
In this blog we are going to discuss Snowflake Organization. These topics describe the administrative concepts and tasks associated with managing your organization in Snowflake.
How much relevant this topic is with respect to certification exam?
This topic is very important, and you may expect a couple of question from this topic in SnowPro advance level certification exam, specially SnowPro Advanced: Administrator or Architect certification.
This topic is not very relevant to SnowPro Core certification.
What is Organization in Snowflake?
Snowflake Organization at high level is nothing different from its normal terminology in real world. Let’s try to understand it with real world example.
Let assumes we have an ABC Group of companies, this company deals with hotel business and span across world in different countries. There will be one head office for this company, say for our example it's India and rest all other outlets across globe is being controlled centrally by head office. So all billing, management, income & expense are centrally managed by head office for all location. Head Office has access to all data to manage and overlook all the other outlets business while it doesn’t have physical access security code or number of security shift details as per rota. These are very location specific security details which are only accessible to particular location security admins. NOT TO OTHERS.
Now say if any company employee want to take transfer to other location in same region or other region he can easily do that within Organization as they belong to same groups of hotel (provided there is vacancy in other region).
His employment related data like leave details, salary, rating etc. data can be share within or across the region depending upon which outlet he/she joined as part of internal job transfer as his employment detail is also maintained at head office.
Now let's map this to Snowflake world, our ABC — Head Office India can be good candidate for Organization, which manages all billing and account management. Just to note here we can make any account as Organization and then create further child account underneath it.
Snowflake organization also simplify your data replication and data sharing capabilities which can help move/share data within or across the region very smoothly.
Now you might think what about all outlets ? Don't worry I am coming to it. These outlets are our individual/standalone account where we actually create our Database,Roles,defines our RBAC etc.
Now that you understood what is Snowflake Organization lets deep down on further concepts.
To access this Organization feature in snowflake you have to get hold of ORGADMIN role. More on this in a later section.
What is ORGADMIN role in Snowflake?
If you are familiar with Snowflake predefined role which came along with Snowflake account than you might know that we have 5 different predefined roles since long time.
- ACCOUNTADMIN
- SYSADMIN
- USERADMIN
- SECURITYADMIN
- PUBLIC
- ORGADMIN — New Entry
With the introduction of Organization in Snowflake we have a new entry in pre-defined role viz. ORGADMIN role aka organization administrator (ORGADMIN) system role, which is responsible for managing operations at the organization level. Below are the core activity you can perform using ORGADMIN role.
How to create/list an account in Organization? How about demo on this ?
In the demo, I had used Classic Web UI to showcase how you can create an account. But in case you are interested to do it via scripting then you can look at Snowflake Documentation here.
After watching above demo you might now know how to create account using ORGADMIN user and how to list the account using SHOW ORGANIZATION ACCOUNTS. Below table will give you detail on which column hold what kind of information in SHOW ORGANIZATION ACCOUNTS function execution.
There are few things which we have to keep in mind while using SHOW ORGANIZATION ACCOUNTS. See below:
- Only user with ORGADMIN role can use this command to list the account.
- The command returns a maximum of 10K records for the specified object type.Any records above the 10K limit are not returned, even with a filter applied.
- This command doesn’t require running warehouse which mean no cost impact of running this query.
What are the core benefits of having Organization ?
Below are the benefits of Snowflake organization as shown in diagram.
What is Database Replication ? How does Organization support Database Replication ?
Database Replication helps to replicate and sync databases across multiple Snowflake accounts in different regions and even on different cloud platforms. More we will cover on upcoming Database Replication series.
To enable the primary database for replication, you need to set up some parameter at Organization parameter so that Organization has info about which is primary account and which is secondary account. To achieve this we have to use the below command:
— Enable replication by executing this statement for each source and target account in your organization
use role orgadmin;
Select system$global_account_set_parameter('<account_locator>’,’ENABLE_ACCOUNT_DATABASE_REPLICATION’, ‘true’); — Primary
Select system$global_account_set_parameter(‘<account_locator>’,’ENABLE_ACCOUNT_DATABASE_REPLICATION’, ‘true’); — Secondary
Can you change the Name of Your Organization ?
At the moment it's not allowed as self-service to change the system generated name of the organization, but you can always do so by contacting Snowflake Support.
How to enable ORGADMIN role to custom role or user?
Let’s see the demo on how to achieve this
What are new Supported URL for Connecting to Snowflake?
Now with the introduction of Organization we can connect using new Account URL as well as old method of using Account Locator URL. See below:
Below are details on how you can cater to different feature using new and old method of connecting to Snowflake.
Things to remember:
- By default, the maximum number of accounts in an organization cannot exceed 25. Contact Snowflake Support to have this limit raised.
- You can only create an account in a region that is enabled for your organization.
- It takes about 30 seconds for the DNS changes to propagate before you can access a newly created account. If the account is not accessible immediately, wait for approximately 30 seconds and try again.
- The account locator and account locator URL will continue to work for all features and nothing will break.
- The new account name and URL will also work in parallel to the account locator/URL and be the basis for new URL features going forward (for example client failover redirect URLs).
- There are no near term plans to deprecate the account locator and account locator URL.
Hope this blog helps you to get insight into the Snowflake Organization feature. If you are interested in learning more details about Snowflake Organization, you can refer to Snowflake documentation. Feel free to ask a question in the comment section if you have any doubts regarding this. Give a clap if you like the blog. Stay connected to see many more such cool stuff. Thanks for your support.
You Can Find Me:
Subscribe to my YouTube Channel: https://www.youtube.com/c/RajivGuptaEverydayLearning
Follow me on Medium: https://rajivgupta780184.medium.com/
Follow me on Twitter: https://twitter.com/RAJIVGUPTA780
Connect with me in LinkedIn: https://www.linkedin.com/in/rajiv-gupta-618b0228/
#Keep learning #Keep Sharing #Everyday Learning.
