Snowflake - Table & View Types

Table Types

Snowflake categorizes tables into different types based on its uses and nature. There are four types of tables −

Permanent tables are created in the database.
These tables persist until deleted or dropped from database.
These tables are designed to store the data that requires highest level of data protection and recovery.
These are default table type.
Time travel is possible in these tables up to 90 days, i.e., that someone can get the data up to 90 days back.
It is Fail-safe and data can be recovered if lost due to fail.

Temporary tables, as the name indicates, exist for a shorter duration.
These tables persist for a session.
If a user wants a temporary table for his subsequent queries and analytics, then once a session is completed, it automatically drops the temporary table.
It is mostly used for transitory data like ETL/ELT
Time travel is possible in temporary tables but only 0 to 1 day.
It is not fail-safe, which means data cannot be recovered automatically.

These tables persist until the users drop or delete them.
Multiple users can access a transient table.
It is used where "data persistence" is required but doesn't need "data retention" for a longer period. For example, the details of guest visitors of a website, the details of users who visited a website as well as registered on it, so after registration, storing the details in two different tables might not be required.
Time travel is possible in transient tables but only for 0 to 1 day.
It is also not failed safe.

These tables persist until removed.
Here, the word removed is used, as external tables are like outside of snowflake and they can't be dropped or deleted. It should be removed.
It can be visualized as Snowflake over an external data lake, i.e., the main source of data lake is pointed to Snowflake to utilize the data as per user's need.
Data cannot be directly accessed. It can be accessed in Snowflake via an external stage.
External tables are only meant for reading.
Time travel is not possible for external tables.
It is not fail-safe inside Snowflake environment.

There are three main categorized views in Snowflake −

Secure View means it can be accessed only by authorized users.
Authorized users can view the definition and details.
Authorized users with proper role can access these tables and execute the queries.
In secure view, Snowflake query optimizer bypasses optimizations used for regular view.

Materialized view is more like a table.
These views store the result from the main source using filter conditions. For example, a company has records of all employees who are active, inactive, or deceased from starting of the company. Now, if a user needs the details of active employees only, then the main table can be queried and stored as materialized view for further analytics.
Materialized view is auto-refreshed, i.e., whenever the main table gets additional/new employee records, it refreshes the materialized view as well.
Snowflake supports secure materialized view as well.
Materialized views are maintained automatically, and it can consume significant compute resources.
Total costs for materialized views are based on "data storage + compute + serverless services."
Compute charges per materialized view are calculated based on the volume of data changes.