#Basic Concepts of Warehouse and Role in Snowflake
More and more organizations are gathering scattered data sources and creating data-house environments optimized for analysis. According to a recent interview with Forbes, Snowflake is not only one of the most influential players in the data-warehouse market but also reported over 174% of revenue in 2019.
In the last article, we briefly looked at what Snowflake is and how it works. This time, we're going to look at how it differs from other data warehouses.
1. Key concepts of Snowflake: Storage & Compute Needs
Snowflake has three layers of Core concepts in it. We will focus on today how its storage and computational methods vary from other data warehouses. Snowflake's storage keeps data in the form of tables and views, similar to existing databases. And it is easy to query SQL for semi-structured data (ex. json) as well as the structured data used commonly in RDBMS.
Snowflake computes queries using the concept of Virtual Warehouse. This warehouse is highly flexible and scalable because it isn't affected by storage size and runs according to the user's specific requirements.
For example, suppose you have an e-commerce company (let's call it A), and you have data in different sizes according to revenue/user/product. People who analyze revenue are receiving two billion data, people who analyze user data collect 100,000 data, and people who analyze products are getting over 50,000 data. The computing power required to analyze each area is drastically different. Depending on the size of the data you're analyzing and the speed at which you look at the results, you can create a different Warehouse. That's one of the most significant advantages of Snowflake.
2. How to switch Warehouse & Roles in Snowflake Web UI
Let's say we run a query on Snowflake Web UI based on these concepts. Before we start, we should go over the idea of a role: to summarize, the system-defined roles in snowflake can be divided into four major categories: "Account Admin," "Security Admin," "Sysadmin" and "Public."
The Account Admin has the highest level of access, with all access rights associated with the account. Security Admin has the right to create, query, and manage users and roles. A Sysadmin has the right to develop warehouses and databases. And Public is automatically granted to all users and can only access specific authorized databases and warehouses.
In addition to the default system roles, you can create more with specific roles and access rights. Find out more information here.
Roles can be changed by clicking 'Switch Role' at the top of the WEB UI. Depending on which account (user) is logged in, the displayed roles are different, and the list of accessible databases is limited.
If you want to access a database assigned to a specific role, you need to set which warehouse you want to use to look up the data before running the query. Just keep in mind that even if you run the same query according to each warehouse size, it differs in terms of speed or performance. As you can see in the image below, the cost per hour from X-Small to 4X-Large is also significantly different. Depending on what data you analyze, you need to change the warehouse to manage the cost-efficiently.
3. Switching warehouse & roles in another SQL client
Usually, there is a limit to the Snowflake Web UI when inquiring and querying large amounts of data, so other SQL Client tools are used for functions such as table lookup/auto-complete. But other tools on the market also have difficulty changing permissions and warehouses. When entering access information for the first time, you can select a warehouse and a role. But if you want to change it every time you run a query, you must go to the access information and modify it, or change the instructions by writing a USE Role / Warehouse.
4. QueryPie: Intuitive SQL client for Snowflake
QueryPie supports several functions that make it easy to auto-complete and display json style data, including those provided by the Snowflake Web UI. One of the other great features that QueryPie delivers is running queries efficiently with simple role/warehouse switching.
Warehouse changes can easily be performed within a tab while executing queries. As described above, you can see that the same query is actually different in terms of performance/speed, depending on the size of your warehouse.
Roles can also be easily changed to ones with specific features and access, unlike other tools that require changing access information or writing USE statements within the editor area.
One of the biggest advantages of Snowflake is that it can be queried by changing the warehouse according to the size of the data for analysis. So QueryPie is adding new features to keep that advantage and make the user experience better. If you were previously analyzing your data with Snowflake, try out this new Snowflake SQL IDE!
📌 Download QueryPie