15.table Types
15.table Types
15.table Types
by
Veerendra.v
Types of Tables
Snowflake supports three types of tables
1. Permanent tables
2. Transient tables
3. Temporary tables
Permanent Tables
Default table type in Snowflake
These are the regular and common tables
Tables exists until we drop them explicitly
7 days of fail-safe period
Syntax to create Permanent table is
Permanent table can be guarded by time travel as per edition of choice. Standard edition(0 or 1)
& Enterprise edition or higher(1 to 90).
7 days fail-safe for permanent table.
Query cache is supported for 24 hours.
Use-Case:
• Need feature like permanent table but want cost-effective solution.
• Don't want to protect data for fail-safe period.
• Want an option to recover deleted history data for max 1 day is required.
• Specifically designed for transitory data that needs to be maintained beyond each
session.
Temporary Tables
Table exists only within the session
Once the session ends, table gets dropped completely and is not recoverable.
They are not visible to other users or sessions
1 day retention period
No fail-safe period
Useful for development work
Can be used in stored procedures for intermediate data storing
Syntax to create Temporary table is
CREATE TEMPORARY TABLE TABLE_NAME();
EXP
Time-Travel Fail-Safe
Table Type Persistence Retention period period in
in days days
Until session is
Temporary 0 or 1 (default is 1) 0
active
Until explicitly
Transient 0 or 1 (default is 1) 0
dropped