Snowflake fundamentals : Part 2

In our previous blog about snowflake we discussed about Snowflake, its key concepts, tools, and commands which are necessary for using this very popular multi-cloud data platform. If you have missed the blog please click on the?link.

In this blog we will learn rest of the fundamental concepts. By the end of this blog you will be familiar with the below topics.

1. Flexibility and Elastic nature of Snowflake.
2. Database objects in Snowflake.
3. Data Stages in Snowflake.
4. Security Model in Snowflake.

Flexibility and Elasticity

Snowflake’s one of the core features is flexibility and Elasticity. These features simply allow us to automatically increase the compute capacity by adding more nodes into the virtual warehouse or we can increase the number of virtual warehouses if we want to serve the number of workloads independently with their resource

Virtual Warehouses are sized from as small as extra small which consumes 1 node to 4x large which is 128 nodes.

The Virtual warehouse architecture allows us to run multiple Virtual warehouses at a time. Let’s say we have Revenue Datamart and Profit Datamart within a database and we have Revenue Virtual Warehouse and Profit Virtual Warehouse respectively. Now, these two data marts can consume both the Warehouses anytime independently.

The Enterprise edition also offers the Multi cluster warehouse which allows the snowflake to automatically adapt to increase the concurrency.

Let’s say we have a Warehouse and it can run for 3 resources at a time. Now if unexpectedly more users say 6 are hitting the Warehouse then snowflake automatically creates a new cluster and manages all the resources without them getting queues up.

?????????????????????????????????Database objects in Snowflake

a)?Table

Tables in snowflake are of 4 different types.

1. Permanent Tables?: It’s a regular table that supports features like data time travel and fail-safe.
2. Temporary Tables: Here the tables created as part of the session are active till the session is active and we can enjoy the benefit of time travel till the session is open, once the session is over these tables don’t exist. This concept is very similar to the other relational database systems.
3. Transient Tables: These are the same as the permanent tables except for the fail-safe feature.
4. External Tables: This is basically a meta data table where all the files and records are in the cloud storage of snowflake.

Snowflake implements Table storage with 2 important features.

Micro partition

Cluster Key

b)?Constraints

Snowflake allows us to specify the below 4 key constraints.

There is however a big difference from the relational database systems as snowflake does not enforce any of these constraints except for the not null.

c)?Data Types

Snowflake supports vast number of data types as shown below

Views

There are 3 different categories of views that you can create.

1. Normal Views: These work the same as the relational database system where you specify the code when you create it and the view code expands when the query is executed.
2. Secure View: These types of views are created when you need to secure the data underneath and when you don’t need to expose any part of the execution. Here Snowflake will execute the view.
3. Materialized View: This is a snowflake enterprise feature where it works the same as the normal view but snowflake will be saving their results permanently and will keep it updated for all the changes done.

???????????????????????????????????????????Data stages

For loading data from different sources/files into snowflake and for unloading the data from snowflake, we use data stages. This is a virtual file location system.

The data is imported through the below process

The data is exported through the below process.

Snowflake has 2 types of Stages

1. Internal: This is managed directly by snowflake. The content present in this stage is considered active data.
2. External: This is managed by the cloud provider.

???????????????????????????????????????????Security Model

Access control is achieved in snowflake through the below hierarchy

Another key feature of snowflake is the role activation

Built-in roles in Snowflake

1. Account Admin: This is the most powerful role and can change almost everything in our account.
2. Security Admin: This can manage, monitor, and modify the user roles and sessions.
3. Sysadmin: This can manage, monitor, and modify objects such as virtual warehouses, databases, and the objects present in the databases.
4. User Admin: This can be used to create users and roles
5. Public: This role is granted automatically to any user that has been created under our account. Since this role is automatically granted to any user-created under our account it is not recommended to grant privileges to the Public role directly.

So, finally here we end as we covered all the core concepts of snowflake at high level. I hope this will be a tool for you to get started on this new data warehousing technology. If you feel this is helpful to you and others please like and share this article.

Cheers,

Chenna

Credits : Pluralsight