Can Höbek
26-02-2023
Snowflake’s type of tables and views

In Snowflake there are several options of table and view types. In this article I am going to try to explain the diffrence between all types of tables and views in Snowflake and how to choose one to use in a efficient way. So let’s start with our tables.

What is a Table?

Tables are the objects that contains the actual data inside of them. They are database tables, made up by rows and columns that makes usage easier and logical structure. To use in a efficient way the tables, we should understand the physical structure really well. These topics describe micro-partitions and data clustering, two of the principal concepts utilized in Snowflake physical table structures.

Micro-partitions and data clustering, two of the key ideas used in Snowflake physical table architectures. When data arrives to Snowflake, immidieatly gets optimized and compressed then stored in cloud storage. For a more in-depth overview, read the blog of Niccolo about this topic. But for now we will look at to types of tables:

There are three types of tables (plus External Tables) in Snowflake, and in the Summit 2022 of Snowflake they introduced even four new types of tables that they are Iceberg, Dynamic, Hybrid, Event Tables that they are not in general availibity right now. We are going to start from first three table called – Permanent, Temporary and Transient that are the most used at this time in Snowflake.

1) Permanent Tables

The default table type when you create a table in snowflake. No need of any additional syntax in creation

CREATE DATABASE blog_for_tables_and_views;
CREATE SCHEMA table_types;

CREATE TABLE customers(
	  id int
    , first_name varchar
    , last_name varchar
);

Permanent tables will take some storage for store their data so it will take some costs that depends from your Cloud Service Region that you use. Permanent tables can use some unique features like Fail-Safe and Time-Travel which help us in several ways that we are going to talk about later.

2) Temporary Tables

Temporary tables are objects that present only for existing session. When the session will end, temporary tables will deleted and they will not be in database no longer. Their main usage are for the session-specific data. That’s important to remain that in the period of their existence, they use a storage and will take costs up anyway up to their deletion. Once temporary table has created, it can not be changed to any other table type.

It means that features like Fail-Safe and Time-Trave will no longer be available for these types of tables.

It has a special syntax to use while creation which like that:

CREATE TEMPORARY TABLE temp_customers (
	  id int
    , first_name varchar
	  , second_name varchar
);

As you can see in above, as soon as we run the code it generates the temporary table but when we will logout and login again it will no longer be there (example in below)

3) Transient Tables

We can describe transient tables in the middle of permanent and temporary tables: so they not require security as much as permanent tables but they should not deleted after each session.

Transient tables does not utilize Fail-Safe feature and have a little period of time for Time-Travel.

It need a specific syntax for create them that is like:

CREATE TRANSIENT TABLE colors (
	  name varchar
    , hex_code text
);

Important Notes

• If we ever want to see our tables there is a command that is:

SHOW TABLES;

that is going to show us all the details about existing tables, we can see our table type below the column kind

as you can see our temporary table temp_customers are not longer available

• Temporary tables have ‘priority’ over Permanent tables in sense that:
  
  • if you create a temporary table named same as one of your permanent table; your next queries will process on your temporary table.
    
  • if you create a permanent table named same as one of your temporary table; your next queries still be proccess on your temporary table.

•  Time-Travel Period:
  • Permanent tables have different types of time-travel retention period depending by your Snowflake edition.
    • for Standart Edition you have 0 or 1 days while for Enterprise or higher Edition you have 0 or 90 days(configurable)
      
  • Transient and Temporary tables have time-travel option 0 or 1 days(default 1 day)

•  Fail-Safe Period
  • Permanent tables (regardless from Snowflake Edition) they have 7 days of Fail-Safe period
    
  • while transient and temporary tables doesn’t have any period of Fail-Safe feature

Time-Travel and Fail-Safe

• Time-Travel is a feature that enable you to query the data of any time you want between creation of table and current time. So you could take like a photograph of the data in the certain time.
  
• Fail-Safe is a feature is a recovery option that provides a (non-configurable) 7-day period during which historical data may be recoverable by Snowflake. This period starts immediately after the Time-Travel retention period ends.

VIEWS

We can think a view as a virtual table that can be used almost everwhere table uses. Views can do all the operaitons that tables does.

One of the big difference with tables that views can not be changed after their create statement

NOTE: with ALTER VIEW command you can change your view name, adding or deleting a command or converting to a Secure View but you can not change the definition of view, to do that you should drop and recreate the view

Secure View definition will not be visible to other users but for other types there is not a limitation like this

There is three types of views in Snowflake

• Non-materialized Views (default views)
• Materialized Views
• Secure Views

Let’s look in detail to all of them starting with

1) Non-Materialized Views

• Non-materialized views are regular views, they don’t need any special syntax than creating a view.
• They are slower than materialized views
• Their results of query is not stored for future use

CREATE VIEW customers_view AS (
	SELECT * FROM customers
);

2) Materialized Views

A materialized view differs from a regular view by its ability to pre-compute the dataset derived from the query specified in its definition. Every time the base table referenced in the materialized view’s query definition is updated, the output dataset will be re-computed.

• Main difference from Non-materialized view is Materialized views are stored while joins are not supported
• They are perfect to use when the table is not updated so often
• Materialized views are automatically and transparently maintained by Snowflake. The automatic maintenance of materialized views consumes credits.
• They are quicker than non-materialized views: Materialized Views are designed to improve performance.
• Materialized Views contain a copy of a subset of the data in a table. Depending upon the amount of data in the table and in the materialized view, scanning the materialized view can be much faster than scanning the table. Materialized views also support clustering, and you can create multiple materialized views on the same data, with each materialized view being clustered on a different column, so that different queries can each run on the view with the best clustering for that query.
• They are created with special syntax like:

CREATE MATERIALIZED VIEW color_view AS(
	SELECT * FROM colors
);

3) Secure Views

Regular (Non-materialized) and Materialized views both also secure by themselves; but Secure views have some extras such as improved data privacy and data sharing, and they have some performance impacts to consider.

The definition of a secure view is only exposed to authorized users i.e. users who have been granted the role that owns the view

Uses special syntax like:

CREATE SECURE VIEW person_view AS (
	SELECT * FROM person_details
);

Important Notes About Views

• To see all Views available on your database you can use:

SHOW VIEWS;

that will show us all of our views and their types available on your database.

Advantages Of Views

• Views Enable Writing More Modular Code: We can access to different topics that we want using different views
• Views Allow Granting Access to a Subset of a Table
• Materialized Views Can Improve Performance

Disadvantages of Views

• Views can not be updated, the only way to change a view is changing its definition, you must recreate the view with the new definition.
• Updates, changes that you make on tables will change the usage of views because views aree not autoımatically propagated. That’s because it is more efficient to use views with tables that not update often
• Views are read-only so you cannot execute DML commands on a view. The thing you can do is execute a subquery as a view within a DML statement that updates the base table

CONCLUSION Snowflake’s type of tables and views

Both Snowflake’s type of tables and views has their own points of use and these several types helps us to work with more knowledge and in an orginaze way with our data. Being free to choose type of tables and views makes us control our Snowflake usage too. If you wondering how to look to your Snowflake usage, you can check it from here.

Credits

https://docs.snowflake.com/