Snowflake - Table & View Types

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

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

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.

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

Frequently Asked Questions

Ans: Snowflake - Objects view more..
Ans: Snowflake - Pricing Model view more..
Ans: Snowflake - Editions view more..
Ans: Snowflake - Table & View Types view more..
Ans: Snowflake - Login view more..
Ans: Snowflake - Warehouse view more..
Ans: Snowflake - Database view more..
Ans: Snowflake - Schema view more..
Ans: Snowflake - Table & Columns view more..
Ans: Snowflake - Load Data From Files view more..
Ans: Snowflake - Sample Useful Queries view more..
Ans: Snowflake - Monitor Usage and Storage view more..
Ans: Snowflake - Cache view more..
Ans: Unload Data from Snowflake to Local view more..
Ans: Snowflake - External Data Loading view more..
Ans: Snowflake - External Data Unloading view more..

Rating - NAN/5