Data warehouse – Snowflake
Table of Contents
What is snowflake.................................................................................................. 1
Common Use Cases............................................................................................... 1
Why Snowflake Is Great for Beginners in Data Warehousing.................................1
Example Student Projects Using Snowflake...........................................................2
Steps to create a trial account in Snowflake:.........................................................3
Sample normalized tables creation Query.............................................................7
SQL exercise:....................................................................................................... 11
What is snowflake:
Snowflake is a cloud-based data warehouse that enables storage,
processing, and analysis of large volumes of data. It is most commonly used for
data warehousing, analytics, and data engineering in the cloud.
Common Use Cases
1. Data Warehousing – Central store for analytics and reporting data
2. Data Lake – Stores raw or semi-structured data
3. Data Engineering – ETL/ELT jobs using SQL or tools like dbt/Dataform
4. Business Intelligence – Integrated with Tableau, Power BI, Looker, etc.
5. Machine Learning – Data prep and feature engineering for ML models
6. Data Sharing – Internal or external data collaboration
Why Snowflake Is Great for Beginners in Data Warehousing
1. No Infrastructure Overhead
Students don’t have to install or maintain servers.
They can focus on SQL, data modeling, and analytics, not system setup.
Snowflake handles scaling, patching, backups automatically.
2. Familiar Interface & SQL Support
Uses standard ANSI SQL, which is easy to learn and widely transferable.
Students can write queries, explore datasets, and build reports without
learning proprietary languages.
Data warehouse – Snowflake
3. Web-Based Access
Accessible via browser – no need to install client tools.
Great for remote learning, hackathons, or coding bootcamps.
4. Free Trial & Student-Friendly Tier
Free trial credits (~$400) with a Snowflake trial account.
Small compute sizes (like X-Small) are enough for student workloads.
5. Real-World Experience
Snowflake is widely used in the industry (Netflix, Capital One, etc.).
Students trained in Snowflake are job-ready for roles in data engineering,
analytics, and BI.
6. Modern Features Encourage Best Practices
Teaches decoupling compute from storage.
Students learn about data sharing, time travel, and schema design on
a cutting-edge platform.
Enables understanding of cloud-native concepts from the start.
7. Excellent for Group Work
Snowflake allows concurrent querying and multi-user collaboration.
Ideal for projects, classroom exercises, or analytics competitions.
8. Compatible with Modern Tools
Easily connects with Power BI, Tableau, dbt, Airflow, etc.
Students can learn the full data stack (ETL, BI, ML) with Snowflake at the
center.
Example Student Projects Using Snowflake
Build a dimensional model for online sales
Analyze website clickstream logs
Create dashboards from structured + semi-structured data (JSON)
Simulate a retail business warehouse using SQL
Steps to create a trial account in Snowflake:
The below are the steps to explore data warehouse using a trial account:
Data warehouse – Snowflake
Step#1 create a free trial account snowflake which is a cloud data warehouse
Data warehouse – Snowflake
Step: 2 - You will receive an email to activate and setup username and
password. Once you setup username password, login into snowflake which I
nothing but a relational database
Once you log in to snowflake, you will see 2 options on the left,
1. Databases – Shows the list of databases in the trial version
2. Worksheet – This is your notepad to write and execute queries
Click worksheet and then select SQL worksheet.
Data warehouse – Snowflake
Create a new worksheet – SKCET Exercise DB (this is your play ground
to write queries)
Then click Databases on the left
Data warehouse – Snowflake
Run scripts in this worksheet to create a database, sample tables and
query the tables. There is a play button the right most corner which
used to execute your query.
Refresh databases on the left and you will see the newly created
database.
Data warehouse – Snowflake
Sample normalized tables creation Query
-- CUSTOMERS
CREATE OR REPLACE TABLE CUSTOMERS (
CUSTOMER_ID NUMBER PRIMARY KEY,
FIRST_NAME STRING,
Data warehouse – Snowflake
LAST_NAME STRING,
EMAIL STRING,
PHONE STRING,
CREATED_AT TIMESTAMP
);
INSERT INTO CUSTOMERS VALUES
(1, 'John', 'Doe', 'john.doe@example.com', '1234567890', CURRENT_TIMESTAMP),
(2, 'Jane', 'Smith', 'jane.smith@example.com', '0987654321',
CURRENT_TIMESTAMP),
(3, 'Alex', 'Johnson', 'alex.j@example.com', '1112223333',
CURRENT_TIMESTAMP),
(4, 'Priya', 'Patel', 'priya.p@example.com', '9998887777', CURRENT_TIMESTAMP),
(5, 'Rahul', 'Mehta', 'rahul.m@example.com', '8887776666',
CURRENT_TIMESTAMP);
-- PRODUCTS
CREATE OR REPLACE TABLE PRODUCTS (
PRODUCT_ID NUMBER PRIMARY KEY,
PRODUCT_NAME STRING,
CATEGORY STRING,
PRICE NUMBER(10,2),
STOCK_QUANTITY NUMBER
);
INSERT INTO PRODUCTS VALUES
(101, 'Echo Dot', 'Electronics', 49.99, 100),
(102, 'Kindle Paperwhite', 'Books', 129.99, 50),
(103, 'Fire TV Stick', 'Electronics', 39.99, 200),
(104, 'Amazon Basics USB Cable', 'Accessories', 9.99, 500),
(105, 'Bluetooth Speaker', 'Electronics', 89.99, 75);
-- ORDERS
Data warehouse – Snowflake
CREATE OR REPLACE TABLE ORDERS (
ORDER_ID NUMBER PRIMARY KEY,
CUSTOMER_ID NUMBER REFERENCES CUSTOMERS(CUSTOMER_ID),
ORDER_DATE TIMESTAMP,
ORDER_STATUS STRING
);
INSERT INTO ORDERS VALUES
(5001, 1, CURRENT_TIMESTAMP, 'PLACED'),
(5002, 2, CURRENT_TIMESTAMP, 'SHIPPED'),
(5003, 3, CURRENT_TIMESTAMP, 'DELIVERED'),
(5004, 4, CURRENT_TIMESTAMP, 'CANCELLED'),
(5005, 5, CURRENT_TIMESTAMP, 'PLACED');
-- ORDER_ITEMS
CREATE OR REPLACE TABLE ORDER_ITEMS (
ORDER_ITEM_ID NUMBER PRIMARY KEY,
ORDER_ID NUMBER REFERENCES ORDERS(ORDER_ID),
PRODUCT_ID NUMBER REFERENCES PRODUCTS(PRODUCT_ID),
QUANTITY NUMBER,
ITEM_PRICE NUMBER(10,2)
);
INSERT INTO ORDER_ITEMS VALUES
(1, 5001, 101, 2, 49.99),
(2, 5002, 102, 1, 129.99),
(3, 5003, 103, 3, 39.99),
(4, 5004, 104, 5, 9.99),
(5, 5005, 105, 1, 89.99),
(6, 5001, 104, 1, 9.99),
(7, 5003, 101, 1, 49.99);
Data warehouse – Snowflake
-- PAYMENTS
CREATE OR REPLACE TABLE PAYMENTS (
PAYMENT_ID NUMBER PRIMARY KEY,
ORDER_ID NUMBER REFERENCES ORDERS(ORDER_ID),
PAYMENT_METHOD STRING,
PAYMENT_DATE TIMESTAMP,
AMOUNT NUMBER(10,2)
);
INSERT INTO PAYMENTS VALUES
(1001, 5001, 'Credit Card', CURRENT_TIMESTAMP, 109.97),
(1002, 5002, 'UPI', CURRENT_TIMESTAMP, 129.99),
(1003, 5003, 'Net Banking', CURRENT_TIMESTAMP, 169.96),
(1004, 5005, 'Credit Card', CURRENT_TIMESTAMP, 89.99);
-- SHIPPING
CREATE OR REPLACE TABLE SHIPPING (
SHIPPING_ID NUMBER PRIMARY KEY,
ORDER_ID NUMBER REFERENCES ORDERS(ORDER_ID),
ADDRESS STRING,
CITY STRING,
STATE STRING,
ZIP STRING,
SHIPPED_DATE TIMESTAMP
);
INSERT INTO SHIPPING VALUES
(2001, 5001, '123 Maple St', 'Seattle', 'WA', '98101', CURRENT_TIMESTAMP),
(2002, 5002, '456 Oak Ave', 'New York', 'NY', '10001', CURRENT_TIMESTAMP),
(2003, 5003, '789 Pine Rd', 'Chicago', 'IL', '60601', CURRENT_TIMESTAMP),
(2004, 5005, '321 Cedar Blvd', 'San Francisco', 'CA', '94107', NULL);
Data warehouse – Snowflake
SQL exercise:
1. List all customers and their email addresses.
Answer: SELECT FIRST_NAME, LAST_NAME, EMAIL FROM CUSTOMERS;
2. List all products with stock less than 100.
Answer: SELECT * FROM PRODUCTS WHERE STOCK_QUANTITY < 100;
3. Get all orders placed by customer John Doe.
Answer: SELECT * FROM ORDERS
WHERE CUSTOMER_ID = (
SELECT CUSTOMER_ID FROM CUSTOMERS
WHERE FIRST_NAME = 'John' AND LAST_NAME = 'Doe'
);
4. Show all products in order 5001 with quantity and price.
Answers: SELECT P.PRODUCT_NAME, OI.QUANTITY, OI.ITEM_PRICE
FROM ORDER_ITEMS OI
JOIN PRODUCTS P ON OI.PRODUCT_ID = P.PRODUCT_ID
WHERE OI.ORDER_ID = 5001;
🧩 Intermediate Level
5. Find total number of orders placed by each customer.
Answer: SELECT CUSTOMER_ID, COUNT(*) AS TOTAL_ORDERS
FROM ORDERS
GROUP BY CUSTOMER_ID;
6. List all orders along with their order items (one row per item).
Answer: SELECT O.ORDER_ID, OI.ORDER_ITEM_ID, OI.PRODUCT_ID,
OI.QUANTITY,OI.ITEM_PRICE
FROM ORDERS O
JOIN ORDER_ITEMS OI ON O.ORDER_ID = OI.ORDER_ID;
FROM ORDERS O
JOIN ORDER_ITEMS OI ON O.ORDER_ID = OI.ORDER_ID;
Data warehouse – Snowflake
7. Find total amount spent per order (sum of item_price * quantity).
Answer: SELECT ORDER_ID, SUM(ITEM_PRICE * QUANTITY) AS TOTAL_AMOUNT
FROM ORDER_ITEMS
GROUP BY ORDER_ID;
8. Get names of customers who have ordered more than 1 product in
a single order.
Answers: SELECT DISTINCT C.FIRST_NAME, C.LAST_NAME
FROM CUSTOMERS C
JOIN ORDERS O ON C.CUSTOMER_ID = O.CUSTOMER_ID
JOIN ORDER_ITEMS OI ON O.ORDER_ID = OI.ORDER_ID
GROUP BY C.CUSTOMER_ID, C.FIRST_NAME, C.LAST_NAME,
O.ORDER_ID
HAVING COUNT(OI.ORDER_ITEM_ID) > 1;
9. List customers who haven’t placed any orders.
10.List products that have never been ordered.
📊 Advanced Level
11.Find the most popular product by total quantity ordered.
12.List all orders that have been paid but not yet shipped.
13.Calculate the total revenue collected per product.
14.List customers who placed orders worth more than ₹150 in total.
15.Show average order value per customer.
🔍 Bonus Challenges
16.Get the top 3 most recent orders for each customer. (Use
ROW_NUMBER/PARTITION BY)
17.Find all customers who bought products from more than one
category.
18.Find customers who placed an order but didn’t make a payment.
19.Create a view for ORDER_SUMMARY showing order_id, customer
name, total_amount, and status.
20.Write a query to get number of orders by status (PLACED,
SHIPPED, DELIVERED, CANCELLED).
