In today’s data-driven world, selecting the right table type is crucial to ensure optimal performance, scalability, and cost-efficiency. Snowflake Database, a cloud-based data warehouse, also offers a variety of table types to cater specific needs. In this blog post, we’ll dive into each table type and explore their benefits.
Table Types in Snowflake Database:
Snowflake Database offers four main table types: regular, transient, temporary and external types. Each has its unique features and advantages, depending on your requirements and use cases.
- Regular/Permanent Tables: Regular tables are the default table type in Snowflake Database. When you create a table without specifying the type, it becomes a regular table. They store data permanently and are suitable for most data warehousing scenarios.
A table in snowflake can be considered as permanent table if that possesses the following properties.
- Time travel of 90 days (for Enterprise and above editions)
- Fail safe (Non-configurable 7 days )
- Available until deleted explicitly
The syntax for creating a permanent table is as follows.
CREATE OR REPLACE TABLE <table_name>
CREATE OR REPLACE TABLE PERMANENT_TBL (NAME VARCHAR(100))
The retention period for Permanent_tbl will be set to default 1 day. We can set this to any value within 90 days as follows.
ALTER TABLE TEST_TBL SET DATA_RETENTION_TIME_IN_DAYS = 60;
Pros:
- Data persistence: Regular tables store data indefinitely, ensuring your information is safe and available for future use.
- Suitable for most use cases: Regular tables work well for a wide range of data storage needs, from transactional data to analytical processing.
Cons:
- Storage costs: Since data is stored indefinitely, you may incur higher storage costs over time.
2.Transient Tables:
Transient tables are an alternative to regular tables, designed for scenarios where data persistence isn’t a top priority. They store data temporarily, which helps reduce storage costs.
A table is considered as transient table in snowflake if it obeys the following properties.
- Time Travel of 1 day
- No fail safe
- Available until explicitly deleted
Syntax for creating a transient table is as follows
CREATE OR REPLACE TRANSIENT TABLE <table_name>
CREATE OR REPLACE TRANSIENT TABLE TRANSIENT_TABLE (NAME VARCHAR(100))
Pros:
- Reduced storage costs: Transient tables automatically purge old data, which helps minimize storage costs.
- Faster performance: With less data to manage, transient tables can deliver better performance for some use cases.
Cons:
- Limited data persistence: Transient tables don’t store data permanently, making them unsuitable for long-term data storage.
3.Temporary Tables:
Temporary tables in Snowflake Database are designed for short-lived, session-specific operations. They’re automatically dropped when the user session ends or after a specified time period.
A table is considered as temporary table in snowflake if the scope of the table limited to the particular session/worksheet and no time travel and fail safe available for the table.
The following figure will illustrates the differences among the table types.
Syntax for creating a temporary table is as follows
CREATE OR REPLACE TEMPORARY TABLE <table_name>
CREATE OR REPLACE TEMPORARY TABLE TEMP_TABLE (NAME VARCHAR(100));
Pros:
- No storage costs: Temporary tables don’t incur storage costs since they’re automatically deleted after the session ends.
- Isolated to user session: Temporary tables are only visible to the user session that created them, ensuring data isolation.
Cons:
- Limited scope: Temporary tables are not suitable for sharing data across user sessions or for long-term storage.
4. External Table:
External tables in snowflake provide a unique way of accessing the files in the cloud storage (outside snowflake) without physically bringing to the snowflake.
We can perform partitioning and can create materialized views on top of external tables for performance improvement.
- The external stage where these external tables refer can be AWS s3, Azure blob, GCP bucket.
- Querying data stored external to the database is likely to be slower than querying native database tables, but materialized views based on external tables can improve query performance
- We can perform joins with external tables.
- We cannot perform DML operations on external tables.
So, we can call snowflake as a query engine since the data outside snowflake can be queried using external tables.
Setting up external tables:
step 1: create an external using the following syntax.
CREATE OR REPLACE EXTERNAL TABLE <table_name> WITH LOCATION = @<external_stage_location> FILE_FORMAT =<file_format_name> AUTO_REFRESH=true |false;
Ex: create or replace external table ext_customerswith location = @EXT_STAGE/DW/customer_dim/ file_format =CSV_FORMAT;
Step 2: Execute the command ‘SHOW EXTERNAL TABLES’ and copy the channel arn
Step 3: Got to s3 bucket and click on properties and then events
Step 4: Now click on add notifications
Step 5: Configure the event notification, select the
ObjectCreate and ObjectRemoved options and then select SQS as the notification destination
Step 6: Now paste the arn that we have copied by executing show external tables command.
Step 7: Finally click on save button.