Road to Snowflake SnowPro Core Certification: Tables & Views

Sixth Chapter: Tables & Views

Sixth Chapter of the Snowflake SnowPro Core Certification Complete Course.

As we saw in the chapter on Snowflake Architecture, a Database contains Schemas, which contain other objects. This chapter will discuss two of the most important ones in more detail: the tables and the views.

1. Tables
2. Views
3. Typical Exam Questions

Remember that all the chapters from the course can be found in the following link.

TABLES

Tables are database objects that contain all the data, like in any other SQL language. There are different types of tables, and there are some differences between them, which are shown in the following diagram:

Difference between Snowflake tables.

Permanent tables

The tables that we can create on Snowflake by default. They have the following characteristics:

• 0–90 days of Time Travel (Enterprise Edition necessary, 0–1 in Standard Edition).
• Seven days FAIL-SAFE.
• You can create a temporary, transient, or permanent table as a clone from permanent tables. If you clone a table to Transient or Temporary, the old partitions will remain permanent, but the new partitions we add to this clone will be Transients/Temporary.

Transient tables

Transient tables persist until explicitly dropped and are available to all users with the appropriate privileges. They are similar to permanent tables, with the key difference being that they do not have Fail-safe, and they have a shorter Time Travel period. For that reason, we use them for transitory data that we need to maintain beyond each session

• 0–1 day of Time Travel. You cannot set 90 days.
• No Fail-Safe
• You can create a temporary or transient table as a clone, but you cannot create a permanent table as a clone.

Temporary tables

They store non-permanent data and only exist within the session they were created. They only persist for the remainder of the session. As such, they are not visible to other users or sessions. Once the session ends, data stored in the table is entirely purged from the system.

• 0–1 day of Time Travel. You cannot set 90 days. A temporary table is purged once the session ends, so the actual retention period is for 24 hours or the remainder of the session.
• No Fail-Safe.
• You can create a temporary or transient table as a clone, but not a permanent table as a clone.

External tables

In external tables, the data is stored in an external stage. They store file-level metadata about the data files, enabling querying data stored in files in an external stage as if it were inside a database. You can only perform read operations on these tables, for example, joins or select queries.

• No Time Travel.
• No Fail-Safe.
• No Cloning.

External tables are helpful when you have objects in the Cloud Provider that cannot be copied elsewhere by regulations, or you have lots of data in the Cloud Provider and only need a part of it in Snowflake.

VIEWS

A view allows us to access the result of a query as a table. An example of using a view could be when you have a BI tool that has to access a variant column (a column with non-structured data like JSON, which is also possible in Snowflake, as we will study later). You can use a view almost anywhere where a table can be used (joins, subqueries, etc.). We have two types of views, Non-Materialized and Materialized views, which can be Secure or Not Secure. In this diagram, we can see the differences, and we will see them below in more depth:

Different types of views in Snowflake.

The most common type of view. The results are NOT stored for future use, so the performance is slower than materialized views.

• You don’t store the data.
• No clustering as you don’t store the data
• You can only share these views if they are secure
• As a new feature of March 2022, Streams are supported in views and secure views!

Materialized View

In many ways, it behaves more like a table. The results are stored to allow faster access but require storage space and active maintenance, which incur additional costs. You need an enterprise edition to be able to create them. Changes to a base table are not automatically propagated to materialized views based on that table (for example, if you add a column in the table, this column is not propagated here. If you delete a column or even the table, this view will give you an error). You can also do clustering in Materialized views.

• You store the data
• You can do clustering as you store the data
• You can only share these views if they are secure
• Currently, streams cannot track changes in materialized views

This view incurs both storage and computing costs. Storage because the information is stored as if it were a table, as we said before, and compute costs because they are automatically and transparently maintained by Snowflake (new data loaded into the table will be propagated here). When should we use materialized views?

• Query results contain a small number of rows and/or columns relative to the base table (the table on which the view is defined).
• Query results contain results that require significant processing, including the analysis of semi-structured data or aggregates that take a long time.
• The query is on an external table, which might have slower performance.
• The view’s base table does not change frequently.

Secure View

You might not wish to expose the underlying tables or internal structural details for security or privacy reasons for a view. The view definition and details are only visible to authorized users with secure views. You cannot see the syntax of how the view was created from the table. They are mandatory to share views (Sharing process that we will see later). Both Regular and Materialized views can also be at the same time Secure Views.

• You store the data depending on if they are materialized or non-materialized.
• You do clustering depending on whether they are materialized or non-materialized.
• You can share these views
• As a new feature of March 2022, Streams are supported in views and secure views!

TYPICAL EXAM QUESTIONS

What types of tables are available in Snowflake?

1. Permanent
2. Temporary
3. Transient
4. External
5. Internal

Solution: 1, 2, 3, 4

What types of views are available in Snowflake?

1. Regular
2. Secure View
3. Table View
4. Materialized View
5. External View

Solution: 1, 2, 4

We need to temporarily store intermediate data, which an ETL process will only use. We don’t need the data outside the ETL process. If you want to optimize storage cost, what type of table will you create to store this data?

1. Permanent
2. Temporary
3. Transient
4. External

Solution: 2.

Can you use streams to track changes in materialized view?

1. True
2. False

Solution: 2. This is a limitation of materialized views.

In which of the below scenarios will you use an external table?

1. You have data on the cloud providers, but the data cannot be copied or moved to any other location due to compliance regulations.
2. You have a high volume of data on the cloud providers, but we only need some of the data in Snowflake.
3. You have data on the cloud providers that need to be updated by Snowflake.
4. You have XML data on the cloud provider.

Solution: 1, 2. The third answer is incorrect, as external tables can only read data. They also don’t support XML.

Select two limitations with materialized views:

1. Time Travel is not supported.
2. We cannot define Cluster Keys.
3. Streams cannot track changes.

Solution: 1, 3

Can temporary tables be created with a clustering key defined?

1. True
2. False

Solution: 1

Thanks for Reading!

If you like my work and want to support me…

1. The BEST way is to follow me on Medium here.
2. Feel free to clap if this post is helpful for you! :)
3. More Snowflake SnowPro Core practice exam questions? Find them at FullCertified.com!