Snowflake hands-On

 

✅ 1. Create Databases, Tables, Views, and Virtual Warehouses

Step 1: Create a Database

CREATE DATABASE my_database;

Step 2: Create a Schema (optional)

CREATE SCHEMA my_database.my_schema;

Step 3: Create a Table

CREATE OR REPLACE TABLE my_database.my_schema.customer (
    customer_id INT,
    customer_name STRING,
    email STRING
);

Step 4: Create a View

CREATE OR REPLACE VIEW my_database.my_schema.customer_view AS
SELECT * FROM my_database.my_schema.customer;

Step 5: Create a Virtual Warehouse

CREATE OR REPLACE WAREHOUSE my_wh
  WITH WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE;

---

✅ 2. Load and Query Structured and Semi-Structured Data

Step 1: Load Structured Data (CSV from Stage)

-- Create file format
CREATE OR REPLACE FILE FORMAT my_csv_format
  TYPE = 'CSV'
  FIELD_OPTIONALLY_ENCLOSED_BY = '"';

-- Stage the file
CREATE OR REPLACE STAGE my_stage;

-- Load data into table
COPY INTO my_database.my_schema.customer
FROM @my_stage/myfile.csv
FILE_FORMAT = (FORMAT_NAME = 'my_csv_format');

Step 2: Load Semi-Structured Data (JSON)

-- Create table with VARIANT column
CREATE OR REPLACE TABLE my_database.my_schema.json_data (raw VARIANT);

-- Load JSON
COPY INTO my_database.my_schema.json_data
FROM @my_stage/myfile.json
FILE_FORMAT = (TYPE = 'JSON');

Step 3: Query Semi-Structured Data

SELECT raw:name, raw:email
FROM my_database.my_schema.json_data;

---

✅ 3. Perform Analytical Queries (Including Joins)

-- Create another table for join
CREATE OR REPLACE TABLE orders (
    order_id INT,
    customer_id INT,
    order_total FLOAT
);

-- Sample Join Query
SELECT c.customer_name, o.order_id, o.order_total
FROM customer c
JOIN orders o
  ON c.customer_id = o.customer_id;

---

✅ 4. Clone Objects (Zero-Copy Cloning)

-- Clone a table
CREATE OR REPLACE TABLE customer_clone CLONE my_database.my_schema.customer;

-- Clone a schema
CREATE OR REPLACE SCHEMA my_schema_clone CLONE my_database.my_schema;

-- Clone a database
CREATE OR REPLACE DATABASE my_database_clone CLONE my_database;

---

✅ 5. Undo User Errors using Time Travel

Snowflake allows you to access data as it existed at a previous time (up to 90 days based on your edition).

Recover a dropped table:

UNDROP TABLE my_table;

Query a table at a point in time:

SELECT * FROM my_table AT (TIMESTAMP => '2025-06-18 10:00:00');

Restore a previous version of a table:

CREATE OR REPLACE TABLE my_table_restored AS
SELECT * FROM my_table BEFORE (STATEMENT => '01a23b45-...'); -- Use statement ID if needed