Snowflake offers different types of tables to be created.
Table of Contents
Understanding types of tables in Snowflakes
Permanent Table
- This is the normal table type and is the default. Mostly used types of tables in snowflake
- Available as a Permanent Storage across the data warehouse.
Temporary Table
- Temporary tables are created for storing non-permanent, transitory data (e.g. ETL data, session-specific data).
- Available for the session in which the temporary table is created.
- Dropped after the session is dropped and data is automatically purged.
- Not visible to other users or sessions.
- Only available in the database where it is created. Also note that you can create temporary and non-temporary tables with the same name within the same schema.
- Ex:
- CREATE TEMPORARY TABLE mytemptable (id NUMBER, creation_date DATE);
Transient Tables
- Transient tables persist until being explicitly dropped.
- Available to all users with the appropriate privileges and Maintained beyond each session.
- Ex:
- CREATE TRANSIENT TABLE mytranstable (id NUMBER, creation_date DATE);
Note
- Both temporary and transient tables do have fail safe and hence only storage cost is incurred but not the fail safe cost.
- You can create a clone of permanent table as a transient table and in such scenario Zero Copy Cloning happens.
- Snowflake also supports creating transient databases and schemas.
Ref : Snowflake Website
External tables
- An external table in Snowflake is a functionality that enables querying of data stored in an external stage, treating it as if it were stored within a Snowflake table.
- It’s important to note that the external stage itself is independent of Snowflake; thus, Snowflake doesn’t store or administer the stage.
- For external tables, snowflake maintains certain metadata like file pattern, delimiter, version, identifiers etc.
- External tables are read-only. You can also create views/materialized views against external tables.
- Snowflake recommends to use partitioned external tables as the underlying base external cloud location contains partitioned data across hierarchies like date, week , month or product etc. This gives better performance.
- Ex:
Iceberg Table
An Iceberg table leverages the Apache Iceberg open table format specification, offering an abstraction layer over data files stored in open formats. It supports various features, including:
- ACID (atomicity, consistency, isolation, durability) transactions
- Schema evolution
- Hidden partitioning
- Table snapshots
Iceberg tables in Snowflake integrate the performance and query functionality of standard Snowflake tables with externally managed cloud storage. They are particularly suited for existing data lakes that you prefer not to store directly within Snowflake.
Hybrid tables
A hybrid table within Snowflake is specifically designed for hybrid transactional and operational workloads necessitating low latency and high throughput, especially for small random point reads and writes. It offers support for enforcing unique and referential integrity constraints crucial for transactional activities. Hybrid tables can be employed alongside other Snowflake tables and functionalities to facilitate Unistore workloads, which integrate transactional and analytical data within a unified platform.
Potential use cases benefiting from hybrid tables include:
- Establishing cohorts for targeted marketing campaigns via interactive user interfaces.
- Managing a central workflow state to coordinate extensive parallel data transformation pipelines.
- Delivering precomputed promotion treatments to users accessing your website or mobile app.
