Snowflake Project 10: Cloning and Swapping Tables

This project demonstrates the use of Snowflake features for table cloning and table swapping. Cloning allows you to create an independent copy of a table instantly without duplicating data, while swapping lets you interchange table data, useful for ETL pipelines or staging environments.

---

TASK 1: Creating and Working with Cloned Tables

Objective:
Create a table, load data from S3, clone it, and verify data.

Steps:

1. Use the demo warehouse for executing all SQL commands.
2. Create a database named sales_clone_db.
3. Create a table sales_data_original with the following columns:
   • order_id (Integer)
   • customer_id (Integer)
   • customer_name (String, 100 characters)
   • order_date (Date)
   • product (String, 100 characters)
   • quantity (Integer)
   • price (Numeric)
   • complete_address (String, 255 characters)
4. Set up an external stage pointing to the S3 bucket containing the sales data.
5. Load data from the stage into sales_data_original.
6. Create a cloned table named sales_data_clone based on sales_data_original.
7. Query both sales_data_original and sales_data_clone to verify data.
8. Clean up by dropping the tables, stage, and database.

---

TASK 2: Swapping Tables

Objective:
Swap data between two tables to demonstrate ETL/staging operations.

Steps:

1. Use the demo warehouse to execute all SQL commands.
2. Create a database named swap_db.
3. Create two tables:
   • current_sales with columns: order_id, customer_id, product (50 characters), quantity
   • staging_sales with columns: order_id, customer_id, product (50 characters), quantity
4. Load sample data into both tables.
5. Use the SWAP command to exchange data between current_sales and staging_sales.
6. Query both tables to verify that the swap was successful.
7. Clean up by dropping the tables and database.

---

Real-World Relevance

• Cloned Tables: Useful for testing, backup, or creating independent versions of production tables without affecting the original data.
• Swapping Tables: Enables seamless replacement of production tables with staged data in ETL workflows, minimizing downtime.
• S3 Integration: Loading data from cloud storage allows centralized data management and reduces dependency on local files.