Snowflake offers four distinct table types, each designed for specific use cases and data management requirements.
**Permanent Tables** are the default table type in Snowflake. They provide full Time Travel capabilities (up to 90 days for Enterprise edition) and Fail-safe protection (7 days). Thes…Snowflake offers four distinct table types, each designed for specific use cases and data management requirements.
**Permanent Tables** are the default table type in Snowflake. They provide full Time Travel capabilities (up to 90 days for Enterprise edition) and Fail-safe protection (7 days). These tables persist until explicitly dropped and incur storage costs for both active data and historical versions. Permanent tables are ideal for production data that requires maximum data protection and recovery options.
**Temporary Tables** exist only for the duration of the session in which they were created. Once the session ends, the table and its data are automatically purged. Temporary tables support Time Travel (up to 1 day) but have no Fail-safe period. They are perfect for storing intermediate results during complex transformations or ETL processes where data persistence beyond the session is unnecessary.
**Transient Tables** persist beyond sessions like permanent tables but have reduced data protection features. They support Time Travel (up to 1 day) with no Fail-safe protection. This makes transient tables cost-effective for data that can be recreated if lost, such as staging tables or data that exists in other source systems. The reduced storage overhead comes from eliminating Fail-safe storage costs.
**External Tables** reference data stored outside Snowflake in cloud storage locations (AWS S3, Azure Blob, Google Cloud Storage). The data remains in its original location and format, while Snowflake provides a read-only table interface for querying. External tables are useful for accessing data lakes, archived data, or when data cannot be moved into Snowflake. They support partitioning for performance optimization but offer limited functionality compared to native Snowflake tables.
Choosing the appropriate table type depends on data retention requirements, recovery needs, cost considerations, and whether data resides within or outside Snowflake storage.
Table Types in Snowflake: Permanent, Temporary, Transient, and External
Why Table Types Matter
Understanding Snowflake table types is essential for the SnowPro Core exam because it directly impacts data persistence, storage costs, Time Travel capabilities, and Fail-safe protection. Choosing the appropriate table type affects both your billing and data recovery options.
Overview of Table Types
Snowflake offers four distinct table types, each designed for specific use cases:
1. Permanent Tables
Permanent tables are the default table type in Snowflake.
Key Characteristics: • Default Time Travel retention: 1 day (can be extended up to 90 days with Enterprise Edition or higher) • Includes 7-day Fail-safe period after Time Travel expires • Data persists until explicitly dropped • Incurs full storage costs including Fail-safe storage • Best for production data requiring maximum protection
2. Temporary Tables
Temporary tables exist only for the duration of the session that created them.
Key Characteristics: • Automatically dropped at the end of the session • Time Travel retention: 0 or 1 day (configurable) • No Fail-safe protection • Not visible to other sessions or users • Lower storage costs due to no Fail-safe • Ideal for intermediate results and session-specific processing
3. Transient Tables
Transient tables persist like permanent tables but lack Fail-safe protection.
Key Characteristics: • Data persists until explicitly dropped • Time Travel retention: 0 or 1 day maximum • No Fail-safe protection • Visible to all users with appropriate privileges • Reduced storage costs compared to permanent tables • Best for data that can be recreated or is not critical
4. External Tables
External tables reference data stored outside of Snowflake in cloud storage.
Key Characteristics: • Data remains in external cloud storage (S3, Azure Blob, GCS) • Read-only access to the data • No Time Travel or Fail-safe • Metadata stored in Snowflake • No Snowflake storage costs for the actual data • Can be used with materialized views for better performance
Comparison Summary
Time Travel Availability: • Permanent: Up to 90 days • Temporary: 0-1 day • Transient: 0-1 day • External: None
Fail-safe Protection: • Permanent: Yes (7 days) • Temporary: No • Transient: No • External: No
Data Persistence: • Permanent: Until dropped • Temporary: Session only • Transient: Until dropped • External: Managed externally
Tip 1: Remember that permanent tables are the DEFAULT. If a question mentions creating a table with standard CREATE TABLE syntax, assume it is permanent.
Tip 2: Focus on the Fail-safe distinction. Only permanent tables have Fail-safe protection. This is a common exam topic.
Tip 3: Temporary tables have SESSION scope. They cannot be seen by other users or sessions, and they vanish when the session ends.
Tip 4: Transient and temporary tables share similar Time Travel limits (0-1 day max), but transient tables persist beyond the session.
Tip 5: External tables are READ-ONLY. You cannot insert, update, or delete data through external tables.
Tip 6: When questions mention cost optimization for non-critical or reproducible data, transient tables are typically the answer.
Tip 7: Watch for questions about cloning. Cloning a permanent table creates another permanent table by default. Temporary and transient tables can be cloned, but external tables cannot be cloned.