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 Table

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 Table

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 pke 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.

Transient Table

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.

External Table

These tables persist until removed.

Here, the word removed is used, as external tables are pke outside of snowflake and they can t be dropped or deleted. It should be removed.

It can be visuapzed as Snowflake over an external data lake, i.e., the main source of data lake is pointed to Snowflake to utipze 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.

View Types

There are three main categorized views in Snowflake −

Standard View

It is the default view type.

Select queries for tables to view data.

User can execute queries based on role and permissions.

Underlying DDL is available to any role who has access to these view.

Secure View

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.

Materiapzed View

Materiapzed view is more pke 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 materiapzed view for further analytics.

Materiapzed view is auto-refreshed, i.e., whenever the main table gets additional/new employee records, it refreshes the materiapzed view as well.

Snowflake supports secure materiapzed view as well.

Materiapzed views are maintained automatically, and it can consume significant compute resources.

Total costs for materiapzed views are based on "data storage + compute + serverless services."

Compute charges per materiapzed view are calculated based on the volume of data changes.