Snowflake Project 9: Table Types

This project focuses on working with different table types in Snowflake: Permanent, Temporary, Transient, and External Tables. Each task demonstrates how to create, load, and manage these table types using data from an AWS S3 bucket.

---

TASK 1: Permanent Tables

Objective:
Load data into a permanent table from an S3 bucket.

Steps:

• Created a database sales_db.
• Created a permanent table sales_data with sales-related columns.
• Configured a named stage s3_stage pointing to an S3 bucket containing CSV data.
• Loaded data from the stage into the sales_data table.
• Queried the table to validate successful data load.
• Dropped created objects as part of cleanup.

---

TASK 2: Temporary Tables

Objective:
Work with temporary tables in Snowflake.

Steps:

• Created a database sales_db_temp.
• Created a temporary table temp_sales_data with sales-related columns.
• Configured a stage pointing to the S3 bucket.
• Loaded data into the temporary table.
• Verified the data load.
• Logged out and relogged in to check that the temporary table no longer existed.
• Dropped the created stage and database.

---

TASK 3: Transient Tables

Objective:
Create and manage transient tables.

Steps:

• Created a database sales_db_transient.
• Created a transient table transient_sales_data with sales-related columns.
• Configured a named stage s3_transient_stage pointing to the S3 bucket.
• Loaded data into the transient table.
• Queried the table to verify data load.
• Observed implications of transient tables regarding data retention and Time Travel.

---

TASK 4: External Tables

Objective:
Create an external table in Snowflake.

Steps:

• Created a database sales_db_external and schema public.
• Configured an external stage s3_stage with AWS credentials to access the S3 bucket.
• Created an external table external_sales_data that referenced CSV data in the S3 bucket.
• Used CSV file format (comma delimiter, header skipped, optional double quotes).
• Queried the external table to confirm access to S3 data.
• Dropped the external table, stage, and database as part of cleanup.

---

Real-World Relevance

• Permanent Tables: Used for core business data that must be retained long-term, such as sales, inventory, or finance data. They support full Time Travel and fail-safe for recovery.