Start here

How to use this workbook

This workbook takes a learner with zero SQL to job-ready Snowflake SQL in 12 short modules, all practised on one small retail dataset you create in Module 1.

It is written for working professionals from non-IT backgrounds (4–8 years of experience) who have little time. Every module follows the same pattern: plain-English notes → copy-paste examples → exercises (solutions are shared on request). Learners should type the examples, not just read them.

Suggested pace (about 1 hour a day, 3–4 weeks)

Rules for learners

• Run every example in a Snowsight worksheet. Select one statement and press Ctrl/Cmd + Enter; use Ctrl/Cmd + Shift + Enter to run all.
• Try each exercise for at least 10 minutes on your own. Solutions are available from Jitendra on request.
• If something breaks, re-run the Module 1 setup script — it resets all data.
• Keep the warehouse at X-Small with auto-suspend at 60 seconds so the trial credits last.

Learners with no SQL at all should spend extra time on "1.1 Database words in plain English" — everything else builds on it.

After the 12 modules (weeks 5–8): build the four practice databases, work through the 200-question exercise bank (Levels 1 → 5, simple to complex), then complete the five real-time projects. Each project mirrors work a Snowflake data engineer does on the job.

Module 1 of 12

Snowflake basics, context and the practice dataset

By the end of this module the learner has a warehouse, a database and five practice tables, and knows how to tell Snowflake where to work.

1.1 Database words in plain English

1.2 What Snowflake is

Snowflake is a cloud data platform with three separate layers:

• Storage — where table data lives (you pay per TB stored).
• Compute = Virtual Warehouse — the engine that runs queries (you pay credits per second while it runs). No running warehouse → no query results (except metadata commands like SHOW).
• Cloud Services — login, security, metadata, query optimisation.

1.3 Object hierarchy

Every object lives inside a container, and its full address is DATABASE.SCHEMA.OBJECT.

• Organization → Account
• Account-level objects: Users, Roles, Warehouses, Databases, Resource monitors
• Database (e.g. TRAINING_DB)
  • Schema (e.g. RETAIL)
  • Tables, Views, Stages, File formats, Sequences, Functions (UDFs), Stored procedures, Streams, Tasks, Pipes

1.4 SQL writing rules

• End each statement with a semicolon ;.
• Keywords and unquoted names are case-insensitive and stored in UPPER case: customers = CUSTOMERS.
• Names in double quotes are case-sensitive: "Customers" ≠ CUSTOMERS. Avoid quoting your own names.
• Text values go in single quotes: 'Mumbai'. Text comparison is case-sensitive: 'mumbai' ≠ 'Mumbai'.
• Comments: -- one line and /* many lines */.

1.5 Context — telling Snowflake where to work

A session always has a current role, warehouse, database and schema.

USE ROLE SYSADMIN;
USE WAREHOUSE TRAINING_WH;
USE DATABASE TRAINING_DB;
USE SCHEMA RETAIL;               -- or in one go: USE SCHEMA TRAINING_DB.RETAIL;

SELECT CURRENT_ROLE(), CURRENT_WAREHOUSE(), CURRENT_DATABASE(), CURRENT_SCHEMA();
SELECT CURRENT_USER(), CURRENT_ACCOUNT(), CURRENT_REGION(), CURRENT_VERSION();
SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP;
SELECT CURRENT_AVAILABLE_ROLES();  -- every role you are allowed to use

Without USE, write the full name: SELECT * FROM TRAINING_DB.RETAIL.CUSTOMERS;

1.6 Setup script — run once (re-run any time to reset)

USE ROLE SYSADMIN;

CREATE WAREHOUSE IF NOT EXISTS TRAINING_WH
  WAREHOUSE_SIZE = 'XSMALL' AUTO_SUSPEND = 60 AUTO_RESUME = TRUE INITIALLY_SUSPENDED = TRUE;
CREATE DATABASE IF NOT EXISTS TRAINING_DB;
CREATE SCHEMA IF NOT EXISTS TRAINING_DB.RETAIL;
USE WAREHOUSE TRAINING_WH;
USE SCHEMA TRAINING_DB.RETAIL;

CREATE OR REPLACE TABLE CUSTOMERS (
  CUSTOMER_ID INT, FIRST_NAME VARCHAR(50), LAST_NAME VARCHAR(50), EMAIL VARCHAR(100),
  CITY VARCHAR(50), STATE VARCHAR(50), SIGNUP_DATE DATE, SEGMENT VARCHAR(20));
INSERT INTO CUSTOMERS VALUES
 (1,'Amit','Sharma','amit.sharma@mail.com','Delhi','Delhi','2024-01-15','Retail'),
 (2,'Priya','Verma','priya.v@mail.com','Mumbai','Maharashtra','2024-02-20','Corporate'),
 (3,'Rahul','Gupta',NULL,'Agra','Uttar Pradesh','2024-03-05','Retail'),
 (4,'Sneha','Iyer','sneha.iyer@mail.com','Chennai','Tamil Nadu','2024-03-18','Corporate'),
 (5,'Vikram','Singh','vikram.s@mail.com','Jaipur','Rajasthan','2024-05-02','Retail'),
 (6,'Anjali','Mehta','anjali.m@mail.com','Mumbai','Maharashtra','2024-06-11','Small Business'),
 (7,'Karan','Patel','karan.p@mail.com','Ahmedabad','Gujarat','2024-07-25',NULL),
 (8,'Neha','Reddy','neha.r@mail.com','Hyderabad','Telangana','2024-09-09','Retail');

CREATE OR REPLACE TABLE PRODUCTS (
  PRODUCT_ID INT, PRODUCT_NAME VARCHAR(50), CATEGORY VARCHAR(30), UNIT_PRICE NUMBER(10,2));
INSERT INTO PRODUCTS VALUES
 (101,'Laptop','Electronics',55000),(102,'Mouse','Electronics',650),
 (103,'Office Chair','Furniture',8500),(104,'Desk','Furniture',12000),
 (105,'Notebook Pack','Stationery',250),(106,'Pen Set','Stationery',180),
 (107,'Monitor','Electronics',14000);

CREATE OR REPLACE TABLE EMPLOYEES (
  EMPLOYEE_ID INT, EMP_NAME VARCHAR(50), DEPARTMENT VARCHAR(30),
  MANAGER_ID INT, SALARY NUMBER(10,2), HIRE_DATE DATE);
INSERT INTO EMPLOYEES VALUES
 (200,'Ravi Kumar','Sales',NULL,150000,'2018-04-01'),
 (201,'Meena Joshi','Sales',200,85000,'2020-06-15'),
 (202,'Arjun Nair','Sales',200,92000,'2019-11-01'),
 (203,'Pooja Das','Sales',201,60000,'2022-01-10'),
 (204,'Suresh Rao','Sales',202,58000,'2023-03-20'),
 (205,'Lata Kapoor','Support',NULL,70000,'2021-08-05');

CREATE OR REPLACE TABLE ORDERS (
  ORDER_ID INT, CUSTOMER_ID INT, ORDER_DATE DATE, STATUS VARCHAR(20), EMPLOYEE_ID INT);
INSERT INTO ORDERS VALUES
 (1001,1,'2024-08-01','DELIVERED',201),(1002,2,'2024-08-03','DELIVERED',202),
 (1003,1,'2024-08-15','CANCELLED',201),(1004,3,'2024-09-02','DELIVERED',203),
 (1005,4,'2024-09-10','SHIPPED',202),  (1006,5,'2024-09-21','DELIVERED',201),
 (1007,2,'2024-10-05','PENDING',203),  (1008,6,'2024-10-12','DELIVERED',202),
 (1009,9,'2024-10-15','DELIVERED',201),(1010,4,'2024-11-01','DELIVERED',204);
-- note: customer 9 does not exist (used in the JOIN module); customers 7 and 8 have no orders

CREATE OR REPLACE TABLE ORDER_ITEMS (ORDER_ID INT, PRODUCT_ID INT, QUANTITY INT);
INSERT INTO ORDER_ITEMS VALUES
 (1001,101,1),(1001,102,2),(1002,103,4),(1002,104,2),(1003,107,1),(1004,105,10),
 (1004,106,5),(1005,101,2),(1006,102,1),(1006,107,1),(1007,104,1),(1008,103,1),
 (1008,105,20),(1009,106,3),(1010,107,2),(1010,102,2);

Save money when you stop for the day: ALTER WAREHOUSE TRAINING_WH SUSPEND;

Exercises — Module 1

1. Run the setup script. Then show your current role, warehouse, database and schema in one query.
2. Switch to role PUBLIC, then check CURRENT_ROLE(). Switch back to SYSADMIN.
3. Without any USE DATABASE, query the PRODUCTS table using its fully qualified name.
4. Run SELECT * FROM "customers"; — why does it fail while SELECT * FROM customers; works?
5. Change TRAINING_WH to auto-suspend after 120 seconds, then resize it to SMALL and back to XSMALL.

Module 2 of 12

Exploring any account: SHOW, DESCRIBE, GET_DDL, RESULT_SCAN

These commands answer "what exists here and how is it built?" — the first thing anyone does in a new project. Most of them run without a warehouse and cost no credits.

2.1 SHOW — list objects

SHOW DATABASES;
SHOW SCHEMAS IN DATABASE TRAINING_DB;
SHOW TABLES;                              -- in current schema
SHOW TABLES IN SCHEMA TRAINING_DB.RETAIL;
SHOW TABLES IN ACCOUNT;                   -- everything you can see
SHOW TABLES LIKE '%ORDER%';               -- LIKE is case-insensitive here
SHOW TABLES STARTS WITH 'CUST';           -- case-sensitive, faster
SHOW TERSE TABLES;                        -- fewer columns
SHOW TABLES HISTORY;                      -- includes dropped tables (see Module 3)
SHOW TABLES LIMIT 3;

SHOW COLUMNS IN TABLE CUSTOMERS;
SHOW VIEWS;            SHOW SEQUENCES;     SHOW STAGES;        SHOW FILE FORMATS;
SHOW FUNCTIONS;        SHOW USER FUNCTIONS;  SHOW PROCEDURES;
SHOW WAREHOUSES;       SHOW ROLES;          SHOW USERS;         -- users needs admin rights
SHOW GRANTS TO ROLE SYSADMIN;
SHOW GRANTS ON TABLE CUSTOMERS;
SHOW GRANTS TO USER <your_user>;
SHOW PARAMETERS IN SESSION;
SHOW PARAMETERS LIKE '%TIMEZONE%' IN ACCOUNT;
SHOW PRIMARY KEYS IN TABLE CUSTOMERS;
SHOW OBJECTS IN SCHEMA RETAIL;            -- tables + views together

Useful SHOW TABLES columns: name, database_name, schema_name, kind, rows, bytes, owner, retention_time, created_on, comment.

2.2 DESCRIBE (DESC) — structure of one object

DESC TABLE CUSTOMERS;          -- column names, types, nullability, defaults
DESCRIBE TABLE ORDERS;         -- DESC and DESCRIBE are the same
DESC VIEW <view_name>;
DESC WAREHOUSE TRAINING_WH;
DESC DATABASE TRAINING_DB;     -- lists its schemas
DESC SCHEMA TRAINING_DB.RETAIL; -- lists its objects
DESC USER <your_user>;
DESC FUNCTION <fn_name>(NUMBER);   -- argument types are part of the name
DESC RESULT LAST_QUERY_ID();   -- columns of the previous result

2.3 GET_DDL — get the CREATE statement back

SELECT GET_DDL('TABLE', 'CUSTOMERS');
SELECT GET_DDL('SCHEMA', 'TRAINING_DB.RETAIL');   -- every object in the schema
SELECT GET_DDL('DATABASE', 'TRAINING_DB');
SELECT GET_DDL('VIEW', '<view_name>');

Use it to copy a table's design to another environment or to see exactly how a view was written.

2.4 RESULT_SCAN and LAST_QUERY_ID — query the output of SHOW

You cannot put WHERE or ORDER BY on a SHOW command. Instead, run SHOW, then treat its result as a table.

SHOW TABLES IN SCHEMA TRAINING_DB.RETAIL;

SELECT "name", "rows", "bytes", "created_on"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "rows" > 6
ORDER BY "rows" DESC;

Key points:

• SHOW output column names are lower case, so wrap them in double quotes: "name", not name.
• LAST_QUERY_ID() = the previous query in this session; LAST_QUERY_ID(-2) = two back; LAST_QUERY_ID(1) = the first query of the session.
• Any query ID works: copy one from Query History and use RESULT_SCAN('01b2c3d4-...').
• Results are kept for 24 hours, so you can re-read a result without re-running the query.
• RESULT_SCAN works on any query, not only SHOW — useful to save the output of DESC or a CALL to a stored procedure.

Save a SHOW result as a real table:

SHOW WAREHOUSES;
CREATE OR REPLACE TABLE WH_SNAPSHOT AS
SELECT "name", "size", "state", "auto_suspend" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Newer accounts also support the flow (pipe) operator ->>, which chains SHOW and SELECT in one statement. If your account rejects it, use RESULT_SCAN.

SHOW TABLES ->> SELECT "name", "rows" FROM $1 WHERE "rows" > 6;

2.5 INFORMATION_SCHEMA — metadata you can query with plain SELECT

Every database has a read-only schema called INFORMATION_SCHEMA. It needs a running warehouse but supports normal WHERE/JOIN.

SELECT TABLE_NAME, ROW_COUNT, BYTES, CREATED
FROM TRAINING_DB.INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'RETAIL';

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, ORDINAL_POSITION
FROM TRAINING_DB.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'RETAIL' ORDER BY TABLE_NAME, ORDINAL_POSITION;

-- recent queries you ran (last 7 days)
SELECT QUERY_ID, QUERY_TEXT, EXECUTION_STATUS, TOTAL_ELAPSED_TIME, START_TIME
FROM TABLE(TRAINING_DB.INFORMATION_SCHEMA.QUERY_HISTORY())
ORDER BY START_TIME DESC LIMIT 10;

For whole-account history (up to 1 year, with 45 min–3 hr delay) admins use SNOWFLAKE.ACCOUNT_USAGE views such as QUERY_HISTORY, TABLES, WAREHOUSE_METERING_HISTORY.

2.6 EXPLAIN — see the plan without running

EXPLAIN SELECT * FROM ORDERS WHERE STATUS = 'DELIVERED';

Exercises — Module 2

1. List all schemas in TRAINING_DB.
2. List only tables whose name contains ORDER.
3. Show the columns and data types of EMPLOYEES in two different ways.
4. Run SHOW TABLES, then with RESULT_SCAN return only name and rows, sorted by rows descending.
5. Run SHOW WAREHOUSES;, then return the name, size and state of warehouses that are SUSPENDED.
6. Get the CREATE statement for the ORDERS table.
7. Using INFORMATION_SCHEMA, count how many columns each table in RETAIL has.
8. Find the query ID of your last 5 queries and re-read the result of one of them with RESULT_SCAN.
9. Show all session parameters that contain the word DATE.
10. Run SELECT * FROM PRODUCTS; then SELECT 1;. Now fetch the PRODUCTS result again without querying PRODUCTS.

Module 3 of 12

DDL: creating and changing objects

DDL (Data Definition Language) builds the containers: CREATE, ALTER, DROP, UNDROP, CLONE. It changes structure, not the rows inside.

3.1 Data types you will actually use

3.2 Create databases, schemas, tables

CREATE DATABASE PRACTICE_DB;
CREATE DATABASE IF NOT EXISTS PRACTICE_DB;   -- no error if it already exists
CREATE OR REPLACE SCHEMA PRACTICE_DB.SANDBOX; -- drops and re-creates (careful!)
USE SCHEMA PRACTICE_DB.SANDBOX;

CREATE OR REPLACE TABLE STUDENTS (
  STUDENT_ID   INT AUTOINCREMENT START 1 INCREMENT 1,   -- also: IDENTITY(1,1)
  STUDENT_NAME VARCHAR(100) NOT NULL,
  COURSE       VARCHAR(50)  DEFAULT 'Snowflake',
  FEES         NUMBER(10,2),
  JOIN_DATE    DATE DEFAULT CURRENT_DATE,
  IS_ACTIVE    BOOLEAN DEFAULT TRUE,
  CREATED_AT   TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT PK_STUDENTS PRIMARY KEY (STUDENT_ID)
) COMMENT = 'Practice table for Module 3';

Important: Snowflake only enforces NOT NULL. PRIMARY KEY, UNIQUE and FOREIGN KEY are stored as documentation for tools — duplicates are still allowed.

3.3 Other ways to create a table

-- CTAS: structure + data from a query
CREATE OR REPLACE TABLE MUMBAI_CUSTOMERS AS
SELECT * FROM TRAINING_DB.RETAIL.CUSTOMERS WHERE CITY = 'Mumbai';

-- LIKE: structure only, no rows
CREATE OR REPLACE TABLE CUSTOMERS_EMPTY LIKE TRAINING_DB.RETAIL.CUSTOMERS;

-- CLONE: instant zero-copy copy (structure + data, no extra storage until changed)
CREATE OR REPLACE TABLE CUSTOMERS_BKP CLONE TRAINING_DB.RETAIL.CUSTOMERS;
CREATE DATABASE TRAINING_DB_DEV CLONE TRAINING_DB;   -- whole database

3.4 Table types

CREATE TRANSIENT TABLE STG_ORDERS (ORDER_ID INT, RAW_DATA VARCHAR);
CREATE TEMPORARY TABLE TMP_CALC (X INT);      -- invisible to other sessions

Snowflake also has external, dynamic, Iceberg and hybrid tables — out of scope for beginners.

3.5 ALTER — change structure

ALTER TABLE STUDENTS ADD COLUMN EMAIL VARCHAR(100);
ALTER TABLE STUDENTS ADD COLUMN CITY VARCHAR(50), PHONE VARCHAR(15);
ALTER TABLE STUDENTS RENAME COLUMN PHONE TO MOBILE;
ALTER TABLE STUDENTS DROP COLUMN MOBILE;
ALTER TABLE STUDENTS ALTER COLUMN COURSE SET DATA TYPE VARCHAR(100); -- only widening allowed
ALTER TABLE STUDENTS ALTER COLUMN EMAIL SET NOT NULL;   -- fails if NULLs exist
ALTER TABLE STUDENTS ALTER COLUMN EMAIL DROP NOT NULL;
ALTER TABLE STUDENTS ALTER COLUMN FEES COMMENT 'Fees in INR';
ALTER TABLE STUDENTS SET COMMENT = 'Students master';
ALTER TABLE STUDENTS SET DATA_RETENTION_TIME_IN_DAYS = 1;
ALTER TABLE STUDENTS RENAME TO LEARNERS;
ALTER TABLE CUSTOMERS_BKP SWAP WITH CUSTOMERS_EMPTY;   -- exchange names instantly
ALTER SCHEMA SANDBOX RENAME TO PLAYGROUND;

3.6 DROP and UNDROP

DROP TABLE IF EXISTS MUMBAI_CUSTOMERS;
SHOW TABLES HISTORY LIKE 'MUMBAI%';   -- "dropped_on" is filled in
UNDROP TABLE MUMBAI_CUSTOMERS;         -- back, within the retention period
DROP SCHEMA PLAYGROUND;               UNDROP SCHEMA PLAYGROUND;
DROP DATABASE PRACTICE_DB;             UNDROP DATABASE PRACTICE_DB;

UNDROP fails if a table with the same name already exists — rename the new one first.

Exercises — Module 3

1. Create database PRACTICE_DB and schema SANDBOX, and set them as context.
2. Create table COURSES with: COURSE_ID (auto-increment), COURSE_NAME (text, not null), DURATION_DAYS (number), PRICE (number with 2 decimals), LAUNCH_DATE (date, default today).
3. Add a column LEVEL (text) with default 'Beginner'. Then rename DURATION_DAYS to DURATION.
4. Check with DESC that your changes worked.
5. Create table ELECTRONICS as a copy of only Electronics products from TRAINING_DB.RETAIL.PRODUCTS.
6. Make a zero-copy clone of TRAINING_DB.RETAIL.ORDERS called ORDERS_CLONE in SANDBOX.
7. Create a transient table and a temporary table; run SHOW TABLES and look at the kind column.
8. Drop ELECTRONICS, confirm it appears in SHOW TABLES HISTORY, then UNDROP it.
9. Try inserting a NULL COURSE_NAME into COURSES — what happens? Then insert two rows with the same COURSE_ID — what happens and why?
10. Get the DDL of COURSES.

Module 4 of 12

DML: adding, changing and removing rows

DML (Data Manipulation Language) changes the rows: INSERT, UPDATE, DELETE, MERGE, TRUNCATE, COPY. Practise on clones so the RETAIL data used in later modules stays clean.

USE SCHEMA PRACTICE_DB.SANDBOX;
CREATE OR REPLACE TABLE CUST CLONE TRAINING_DB.RETAIL.CUSTOMERS;
CREATE OR REPLACE TABLE PROD CLONE TRAINING_DB.RETAIL.PRODUCTS;

4.1 INSERT

-- all columns, in table order
INSERT INTO PROD VALUES (108, 'Keyboard', 'Electronics', 1200);

-- named columns (safer; missing columns get DEFAULT or NULL)
INSERT INTO PROD (PRODUCT_ID, PRODUCT_NAME) VALUES (109, 'Stapler');

-- many rows at once
INSERT INTO PROD VALUES (110,'Whiteboard','Stationery',2200), (111,'Webcam','Electronics',3500);

-- from a query
INSERT INTO PROD SELECT PRODUCT_ID + 100, PRODUCT_NAME || ' (Refurb)', CATEGORY, UNIT_PRICE * 0.6
FROM TRAINING_DB.RETAIL.PRODUCTS WHERE CATEGORY = 'Electronics';

-- replace ALL rows with the query result (truncate + insert in one step)
INSERT OVERWRITE INTO PROD SELECT * FROM TRAINING_DB.RETAIL.PRODUCTS;

Multi-table insert — one read, several targets:

CREATE OR REPLACE TABLE CHEAP_PROD LIKE PROD;
CREATE OR REPLACE TABLE COSTLY_PROD LIKE PROD;

INSERT ALL                                   -- every matching WHEN fires
  WHEN UNIT_PRICE <  5000 THEN INTO CHEAP_PROD
  WHEN UNIT_PRICE >= 5000 THEN INTO COSTLY_PROD
SELECT * FROM PROD;
-- INSERT FIRST ... stops at the first WHEN that matches

4.2 UPDATE

UPDATE PROD SET UNIT_PRICE = 700 WHERE PRODUCT_ID = 102;
UPDATE PROD SET UNIT_PRICE = UNIT_PRICE * 1.10, CATEGORY = UPPER(CATEGORY)
WHERE CATEGORY = 'Furniture';
UPDATE CUST SET SEGMENT = 'Unknown' WHERE SEGMENT IS NULL;

-- update using another table
UPDATE CUST c SET SEGMENT = 'Has Orders'
FROM TRAINING_DB.RETAIL.ORDERS o
WHERE c.CUSTOMER_ID = o.CUSTOMER_ID;

Always write the WHERE first. UPDATE PROD SET UNIT_PRICE = 0; changes every row. Run the matching SELECT ... WHERE ... before any UPDATE or DELETE.

4.3 DELETE and TRUNCATE

DELETE FROM PROD WHERE PRODUCT_ID = 109;
DELETE FROM PROD WHERE CATEGORY IS NULL;

-- delete using another table
DELETE FROM CUST c USING TRAINING_DB.RETAIL.ORDERS o
WHERE c.CUSTOMER_ID = o.CUSTOMER_ID AND o.STATUS = 'CANCELLED';

TRUNCATE TABLE CHEAP_PROD;   -- removes all rows, keeps structure

4.4 MERGE — insert-or-update in one statement (upsert)

CREATE OR REPLACE TABLE PRICE_FEED (PRODUCT_ID INT, NEW_PRICE NUMBER(10,2), PRODUCT_NAME VARCHAR, CATEGORY VARCHAR);
INSERT INTO PRICE_FEED VALUES
 (101, 52000, 'Laptop', 'Electronics'),     -- existing → update
 (106, 0,     'Pen Set', 'Stationery'),     -- existing, price 0 → delete
 (120, 999,   'Mouse Pad', 'Electronics');  -- new → insert

MERGE INTO PROD t
USING PRICE_FEED s
  ON t.PRODUCT_ID = s.PRODUCT_ID
WHEN MATCHED AND s.NEW_PRICE = 0 THEN DELETE
WHEN MATCHED THEN UPDATE SET t.UNIT_PRICE = s.NEW_PRICE
WHEN NOT MATCHED THEN INSERT (PRODUCT_ID, PRODUCT_NAME, CATEGORY, UNIT_PRICE)
                      VALUES (s.PRODUCT_ID, s.PRODUCT_NAME, s.CATEGORY, s.NEW_PRICE);

The result row shows "number of rows inserted / updated / deleted". If the source has two rows for the same key, MERGE errors — de-duplicate the source first (Module 10).

4.5 Loading files — COPY INTO (overview)

Real data arrives as files (CSV, JSON, Parquet). The path is: file → stage → COPY INTO table.

CREATE OR REPLACE FILE FORMAT CSV_FF TYPE = CSV SKIP_HEADER = 1
  FIELD_OPTIONALLY_ENCLOSED_BY = '"' NULL_IF = ('', 'NULL');
CREATE OR REPLACE STAGE MY_STAGE FILE_FORMAT = CSV_FF;   -- internal stage

-- upload: Snowsight → Data → Databases → stage → "+ Files"   (or SnowSQL: PUT file://C:\data\cust.csv @MY_STAGE;)
LIST @MY_STAGE;
SELECT $1, $2, $3 FROM @MY_STAGE (FILE_FORMAT => 'CSV_FF');   -- peek before loading

COPY INTO CUST FROM @MY_STAGE FILE_FORMAT = (FORMAT_NAME = 'CSV_FF') ON_ERROR = 'CONTINUE';
COPY INTO CUST FROM @MY_STAGE VALIDATION_MODE = 'RETURN_ERRORS';   -- check only, load nothing

-- export a table to the stage
COPY INTO @MY_STAGE/export/ FROM PROD FILE_FORMAT = (TYPE = CSV) HEADER = TRUE;

For beginners, Snowsight's Load Data wizard does the same in a few clicks.

4.6 Transactions in one line

Each statement auto-commits. To group statements, use BEGIN; ... COMMIT; or undo with ROLLBACK; (see Module 12).

Exercises — Module 4 (in PRACTICE_DB.SANDBOX)

1. Insert yourself into CUST as customer 9 (any city, today's date as signup).
2. Insert two products in one statement.
3. Increase the price of all Stationery products by 5%.
4. Set SEGMENT to 'Retail' for customers whose segment is NULL.
5. Delete customers who have no email.
6. Create table CUST_DELHI as an empty copy of CUST, then fill it with Delhi customers using INSERT ... SELECT.
7. Use INSERT ALL to send Retail customers to RETAIL_CUST and all others to OTHER_CUST.
8. Write a MERGE that loads table CUST_UPDATES (create it with 2 rows: one existing customer with a new city, one new customer) into CUST.
9. Empty CHEAP_PROD without dropping it, then check its row count.
10. Replace all rows of PROD with the original RETAIL.PRODUCTS rows in one statement.

Module 5 of 12

SELECT: reading, filtering and sorting

SELECT is 80% of daily SQL work. From here on, run everything in USE SCHEMA TRAINING_DB.RETAIL;.

5.1 The order you write vs the order Snowflake runs

5.2 Choosing columns

SELECT * FROM CUSTOMERS;                                  -- all columns
SELECT FIRST_NAME, CITY FROM CUSTOMERS;                   -- some columns
SELECT FIRST_NAME AS NAME, CITY "Customer City" FROM CUSTOMERS;  -- aliases
SELECT PRODUCT_NAME, UNIT_PRICE, UNIT_PRICE * 0.18 AS GST FROM PRODUCTS;  -- calculated
SELECT 'Hello' AS GREETING, 10 + 5 AS TOTAL;              -- no table needed

-- Snowflake-only conveniences
SELECT * EXCLUDE (EMAIL, SIGNUP_DATE) FROM CUSTOMERS;     -- all but these
SELECT * RENAME (FIRST_NAME AS FNAME) FROM CUSTOMERS;
SELECT * ILIKE '%NAME%' FROM CUSTOMERS;                   -- columns whose name matches

5.3 Filtering rows — WHERE

SELECT * FROM PRODUCTS WHERE CATEGORY = 'Electronics';
SELECT * FROM PRODUCTS WHERE UNIT_PRICE > 10000;
SELECT * FROM PRODUCTS WHERE CATEGORY <> 'Furniture';           -- also !=
SELECT * FROM PRODUCTS WHERE UNIT_PRICE BETWEEN 500 AND 9000;  -- both ends included
SELECT * FROM CUSTOMERS WHERE CITY IN ('Mumbai','Delhi','Agra');
SELECT * FROM CUSTOMERS WHERE CITY NOT IN ('Mumbai','Delhi');
SELECT * FROM ORDERS WHERE ORDER_DATE >= '2024-09-01' AND ORDER_DATE < '2024-10-01';

-- pattern matching:  %  = any characters,  _  = exactly one character
SELECT * FROM CUSTOMERS WHERE FIRST_NAME LIKE 'A%';          -- starts with A
SELECT * FROM CUSTOMERS WHERE EMAIL LIKE '%mail.com';
SELECT * FROM CUSTOMERS WHERE FIRST_NAME LIKE '_a%';         -- 2nd letter a
SELECT * FROM CUSTOMERS WHERE CITY ILIKE 'mumbai';           -- case-insensitive
SELECT * FROM CUSTOMERS WHERE CITY LIKE ANY ('M%', 'D%');
SELECT * FROM CUSTOMERS WHERE CITY RLIKE '^[A-C].*';        -- regular expression

-- AND is evaluated before OR: use brackets
SELECT * FROM CUSTOMERS
WHERE (CITY = 'Mumbai' OR CITY = 'Delhi') AND SEGMENT = 'Retail';

5.4 NULL — the "unknown" value

NULL = NULL is not TRUE (it is NULL), so WHERE EMAIL = NULL returns nothing.

SELECT * FROM CUSTOMERS WHERE EMAIL IS NULL;
SELECT * FROM CUSTOMERS WHERE SEGMENT IS NOT NULL;
SELECT * FROM CUSTOMERS WHERE SEGMENT IS DISTINCT FROM 'Retail';   -- includes NULL rows
SELECT FIRST_NAME, COALESCE(EMAIL, 'no email') AS EMAIL FROM CUSTOMERS; -- first non-null
SELECT FIRST_NAME, NVL(SEGMENT, 'Unknown') FROM CUSTOMERS;            -- same as IFNULL
SELECT NULL + 5, 'A' || NULL, ZEROIFNULL(NULL);   -- NULL, NULL, 0

Note SEGMENT <> 'Retail' silently drops NULL rows; IS DISTINCT FROM keeps them.

5.5 Sorting and limiting

SELECT * FROM PRODUCTS ORDER BY UNIT_PRICE DESC;
SELECT * FROM PRODUCTS ORDER BY CATEGORY ASC, UNIT_PRICE DESC;
SELECT PRODUCT_NAME, UNIT_PRICE FROM PRODUCTS ORDER BY 2 DESC;   -- by position
SELECT * FROM CUSTOMERS ORDER BY SEGMENT NULLS FIRST;            -- NULLs are LAST by default in ASC

SELECT * FROM PRODUCTS ORDER BY UNIT_PRICE DESC LIMIT 3;         -- top 3
SELECT * FROM PRODUCTS ORDER BY UNIT_PRICE DESC LIMIT 3 OFFSET 3; -- rows 4–6 (paging)
SELECT TOP 3 * FROM PRODUCTS ORDER BY UNIT_PRICE DESC;
SELECT * FROM PRODUCTS ORDER BY UNIT_PRICE FETCH FIRST 3 ROWS ONLY;

5.6 Removing duplicates

SELECT DISTINCT CITY FROM CUSTOMERS;
SELECT DISTINCT CITY, SEGMENT FROM CUSTOMERS;   -- unique combinations
SELECT COUNT(DISTINCT CITY) FROM CUSTOMERS;

5.7 Aliases in later clauses (Snowflake feature)

Snowflake lets you reuse a column alias in WHERE, GROUP BY and HAVING:

SELECT PRODUCT_NAME, UNIT_PRICE * 1.18 AS PRICE_WITH_GST
FROM PRODUCTS WHERE PRICE_WITH_GST > 10000;

Exercises — Module 5

1. Show product name and price for all Furniture products.
2. List customers from Mumbai or Chennai who are Corporate.
3. Find customers whose first name ends with 'a'.
4. Find customers whose email is missing, and show 'NOT PROVIDED' instead of NULL.
5. Show the 3 cheapest products.
6. List all distinct order statuses.
7. Show orders placed in September 2024, newest first.
8. Show every column of CUSTOMERS except EMAIL and STATE.
9. List customers whose segment is anything other than 'Retail', including those with no segment.
10. Show product name, price and price after a 15% discount, only where the discounted price is above 5,000.
11. Show customers who signed up in the first half of 2024 and live in a city starting with A, B, C or D.
12. Show products ranked 3rd to 5th by price (highest first).

Module 6 of 12

Functions: cleaning and calculating values

Functions work on one value per row and return one value. Learn them by running the examples and changing the inputs.

6.1 Text (string) functions

SELECT
  UPPER('agra'), LOWER('AGRA'), INITCAP('rahul gupta'),          -- AGRA, agra, Rahul Gupta
  LENGTH('Snowflake'), LEN('  hi  '),                              -- 9, 6
  TRIM('  hi  '), LTRIM('xxhi','x'), RTRIM('hi  '),                -- hi, hi, hi
  SUBSTR('Snowflake', 1, 4), LEFT('Snowflake',4), RIGHT('Snowflake',5), -- Snow, Snow, flake
  CONCAT('Snow','flake'), 'Snow' || 'flake',                       -- Snowflake
  CONCAT_WS('-', 'IN', 'UP', 'AGRA'),                               -- IN-UP-AGRA
  REPLACE('2024/08/01','/','-'),                                    -- 2024-08-01
  POSITION('@' IN 'amit@mail.com'), CHARINDEX('@','amit@mail.com'), -- 5, 5
  SPLIT_PART('amit@mail.com','@',2),                                -- mail.com
  LPAD('7', 3, '0'), RPAD('AB', 5, '*'),                            -- 007, AB***
  REVERSE('abc'), REPEAT('ab',3),                                   -- cba, ababab
  CONTAINS('Snowflake','flake'), STARTSWITH('Snowflake','Snow'),     -- TRUE, TRUE
  REGEXP_LIKE('9876543210','^[0-9]{10}$'),                          -- TRUE
  REGEXP_SUBSTR('Order #1001 shipped','[0-9]+'),                     -- 1001
  REGEXP_REPLACE('98-765-43','[^0-9]','');                          -- 9876543

-- on real data
SELECT FIRST_NAME || ' ' || LAST_NAME AS FULL_NAME,
       UPPER(LEFT(FIRST_NAME,1)) || UPPER(LEFT(LAST_NAME,1)) AS INITIALS,
       SPLIT_PART(EMAIL,'@',1) AS USERNAME
FROM CUSTOMERS;

6.2 Number functions

SELECT
  ROUND(1234.567, 2), ROUND(1234.567, -2),  -- 1234.57, 1200
  TRUNC(1234.567, 1), CEIL(4.1), FLOOR(4.9), -- 1234.5, 5, 4
  ABS(-15), MOD(17, 5), 17 % 5,              -- 15, 2, 2
  POWER(2, 10), SQRT(81), SIGN(-3),          -- 1024, 9, -1
  10 / 4, DIV0(10, 0), DIV0NULL(10, NULL),   -- 2.5, 0, 0
  UNIFORM(1, 100, RANDOM());                 -- random integer 1–100

10/0 raises an error; DIV0 returns 0 instead — handy in ratio reports.

6.3 Date and time functions

SELECT
  CURRENT_DATE, CURRENT_TIMESTAMP, SYSDATE(),
  DATEADD(DAY, 30, '2024-08-01'::DATE),        -- 2024-08-31
  DATEADD(MONTH, -1, CURRENT_DATE),            -- one month ago
  ADD_MONTHS('2024-01-31'::DATE, 1),           -- 2024-02-29
  DATEDIFF(DAY,  '2024-08-01', '2024-10-01'),  -- 61
  DATEDIFF(MONTH,'2024-08-31', '2024-09-01'),  -- 1 (counts boundaries crossed)
  DATE_TRUNC(MONTH, '2024-08-17'::DATE),       -- 2024-08-01
  LAST_DAY('2024-02-10'::DATE),                -- 2024-02-29
  YEAR('2024-08-17'::DATE), MONTH('2024-08-17'::DATE), DAY('2024-08-17'::DATE),
  QUARTER('2024-08-17'::DATE), WEEKOFYEAR('2024-08-17'::DATE),
  DAYNAME('2024-08-17'::DATE), MONTHNAME('2024-08-17'::DATE),  -- Sat, Aug
  DATE_PART(DAYOFWEEK, '2024-08-17'::DATE), EXTRACT(YEAR FROM CURRENT_DATE),
  NEXT_DAY('2024-08-17'::DATE, 'Monday'),
  TO_CHAR('2024-08-17'::DATE, 'DD-MON-YYYY'),  -- 17-Aug-2024
  TO_DATE('17/08/2024', 'DD/MM/YYYY'),
  CONVERT_TIMEZONE('UTC', 'Asia/Kolkata', CURRENT_TIMESTAMP);

-- on real data: days since signup and signup month
SELECT FIRST_NAME, SIGNUP_DATE,
       DATEDIFF(DAY, SIGNUP_DATE, CURRENT_DATE) AS DAYS_AS_CUSTOMER,
       TO_CHAR(SIGNUP_DATE, 'MON-YYYY') AS SIGNUP_MONTH
FROM CUSTOMERS;

Common format codes: YYYY year, MM month number, MON short month, MMMM full month, DD day, DY short weekday, HH24:MI:SS time.

6.4 Conversion (casting)

SELECT
  CAST('123' AS INT), '123'::INT, '45.6'::NUMBER(5,1),
  '2024-08-01'::DATE, '2024-08-01 10:30:00'::TIMESTAMP,
  123::VARCHAR, TO_VARCHAR(1234567.891, '9,999,999.99'),
  TO_NUMBER('1,500', '9,999'), TO_BOOLEAN('yes'),
  TRY_CAST('abc' AS INT),         -- NULL instead of an error
  TRY_TO_NUMBER('12a'),           -- NULL
  TRY_TO_DATE('31-02-2024','DD-MM-YYYY');   -- NULL (no 31 Feb)

TRY_ versions return NULL instead of failing — the standard way to find bad values in raw data: SELECT * FROM STG WHERE TRY_TO_DATE(DT_TEXT) IS NULL;. TRY_CAST accepts only text input.

6.5 Conditional logic

-- searched CASE: first true WHEN wins
SELECT PRODUCT_NAME, UNIT_PRICE,
  CASE WHEN UNIT_PRICE >= 20000 THEN 'Premium'
       WHEN UNIT_PRICE >= 5000  THEN 'Mid'
       ELSE 'Budget' END AS PRICE_BAND
FROM PRODUCTS;

-- simple CASE: compare one column with values
SELECT ORDER_ID,
  CASE STATUS WHEN 'DELIVERED' THEN 'Closed' WHEN 'CANCELLED' THEN 'Closed' ELSE 'Open' END AS STAGE
FROM ORDERS;

SELECT PRODUCT_NAME, IFF(UNIT_PRICE > 10000, 'High', 'Low') FROM PRODUCTS;  -- 2-way IF
SELECT DECODE(STATUS, 'DELIVERED','D', 'SHIPPED','S', 'Other') FROM ORDERS;
SELECT NVL2(EMAIL, 'Has email', 'No email') FROM CUSTOMERS;   -- if not null / if null
SELECT NULLIF(10, 10), NULLIF(10, 5);                       -- NULL, 10
SELECT GREATEST(5, 9, 2), LEAST(5, 9, 2);                   -- 9, 2
SELECT EQUAL_NULL(NULL, NULL);                             -- TRUE (NULL-safe =)

Exercises — Module 6

1. Show each customer's full name in UPPER case and the length of the full name.
2. Extract the email domain (part after @) for every customer; show 'N/A' if email is NULL.
3. Show product names padded to 15 characters with dots on the right.
4. Show each order with the day name and month name of ORDER_DATE.
5. Show how many days ago each order was placed.
6. For each order, show the expected delivery date = order date + 7 days, and the last day of that month.
7. Format ORDER_DATE as 01-Aug-2024.
8. Convert the text values '2024-12-25', '25/12/2024' and 'hello' to dates without errors.
9. Classify employees: salary ≥ 90,000 → 'Senior', ≥ 60,000 → 'Mid', else 'Junior'.
10. Show order status as 1 when DELIVERED and 0 otherwise, using IFF.
11. Round every product price to the nearest thousand.
12. Mask emails to show only the first 2 characters, e.g. am*****@mail.com.

Module 7 of 12

Summarising: aggregates, GROUP BY, HAVING

Aggregate functions turn many rows into one number — the SQL version of an Excel pivot table.

7.1 Aggregate functions

SELECT
  COUNT(*)              AS TOTAL_ROWS,        -- counts every row
  COUNT(EMAIL)          AS WITH_EMAIL,        -- ignores NULLs
  COUNT(DISTINCT CITY)  AS CITIES,
  COUNT_IF(SEGMENT = 'Retail') AS RETAIL_CUSTOMERS
FROM CUSTOMERS;

SELECT SUM(SALARY), AVG(SALARY), MIN(SALARY), MAX(SALARY),
       MEDIAN(SALARY), MODE(DEPARTMENT), STDDEV(SALARY)
FROM EMPLOYEES;

SELECT MIN(ORDER_DATE) AS FIRST_ORDER, MAX(ORDER_DATE) AS LAST_ORDER FROM ORDERS;

-- combine values into one cell
SELECT LISTAGG(PRODUCT_NAME, ', ') WITHIN GROUP (ORDER BY PRODUCT_NAME) FROM PRODUCTS;
SELECT ARRAY_AGG(PRODUCT_NAME) FROM PRODUCTS;
SELECT APPROX_COUNT_DISTINCT(CUSTOMER_ID) FROM ORDERS;   -- fast estimate on huge tables

All aggregates except COUNT(*) ignore NULLs. AVG of (10, NULL, 20) is 15, not 10.

7.2 GROUP BY — one result row per group

SELECT CATEGORY, COUNT(*) AS PRODUCTS, AVG(UNIT_PRICE) AS AVG_PRICE
FROM PRODUCTS
GROUP BY CATEGORY;

SELECT CITY, SEGMENT, COUNT(*) FROM CUSTOMERS GROUP BY CITY, SEGMENT;
SELECT CITY, SEGMENT, COUNT(*) FROM CUSTOMERS GROUP BY 1, 2;     -- by position
SELECT CITY, SEGMENT, COUNT(*) FROM CUSTOMERS GROUP BY ALL;      -- every non-aggregate column

-- group by a calculated value
SELECT DATE_TRUNC(MONTH, ORDER_DATE) AS ORDER_MONTH, COUNT(*) AS ORDERS
FROM ORDERS GROUP BY ORDER_MONTH ORDER BY ORDER_MONTH;

-- conditional counts in one pass (pivot-style)
SELECT EMPLOYEE_ID,
       COUNT(*)                             AS ALL_ORDERS,
       COUNT_IF(STATUS = 'DELIVERED')       AS DELIVERED,
       SUM(IFF(STATUS = 'CANCELLED', 1, 0)) AS CANCELLED
FROM ORDERS GROUP BY EMPLOYEE_ID;

The golden rule: every column in SELECT must either be inside an aggregate or listed in GROUP BY. Otherwise: "... is not a valid group by expression".

7.3 HAVING — filter after grouping

SELECT CITY, COUNT(*) AS CUSTOMERS
FROM CUSTOMERS
GROUP BY CITY
HAVING COUNT(*) > 1;

-- WHERE filters rows before grouping; HAVING filters groups after
SELECT EMPLOYEE_ID, COUNT(*) AS DELIVERED_ORDERS
FROM ORDERS
WHERE STATUS = 'DELIVERED'
GROUP BY EMPLOYEE_ID
HAVING COUNT(*) >= 2
ORDER BY DELIVERED_ORDERS DESC;

7.4 Subtotals and grand totals — ROLLUP, CUBE, GROUPING SETS

-- revenue by category and product + category subtotal + grand total
SELECT p.CATEGORY, p.PRODUCT_NAME, SUM(i.QUANTITY * p.UNIT_PRICE) AS REVENUE
FROM ORDER_ITEMS i JOIN PRODUCTS p ON i.PRODUCT_ID = p.PRODUCT_ID
GROUP BY ROLLUP (p.CATEGORY, p.PRODUCT_NAME)
ORDER BY 1, 2;

SELECT CITY, SEGMENT, COUNT(*) FROM CUSTOMERS GROUP BY CUBE (CITY, SEGMENT);  -- every combination
SELECT CITY, SEGMENT, COUNT(*) FROM CUSTOMERS GROUP BY GROUPING SETS ((CITY), (SEGMENT), ());
SELECT CITY, GROUPING(CITY) AS IS_TOTAL_ROW, COUNT(*) FROM CUSTOMERS GROUP BY ROLLUP (CITY);

(The join in the first query is explained in Module 8.)

Exercises — Module 7

1. How many orders are there in total, and how many distinct customers placed them?
2. Count orders by status.
3. Find the most and least expensive price in each product category.
4. Show total quantity sold per product ID, highest first.
5. Show the number of customers per state, only states with more than one customer.
6. Show the number of orders per month (use DATE_TRUNC) in date order.
7. For each employee, show total orders, delivered orders and cancelled orders in one row.
8. List all product names per category in one comma-separated cell.
9. Show the average salary per department, rounded to 0 decimals, only where it exceeds 75,000.
10. Count customers per segment, showing NULL segment as 'Unassigned'.
11. Show order count by STATUS with a grand-total row at the bottom.

Module 8 of 12

Joins and set operators: combining tables

A join puts columns from two tables side by side by matching a key (like VLOOKUP). A set operator stacks rows from two queries on top of each other.

8.1 Join types at a glance

The dataset is built to show the differences: customers 7 and 8 have no orders, and order 1009 belongs to customer 9, who does not exist.

8.2 Join syntax

-- INNER: customers with their orders
SELECT c.CUSTOMER_ID, c.FIRST_NAME, o.ORDER_ID, o.ORDER_DATE, o.STATUS
FROM CUSTOMERS c
INNER JOIN ORDERS o ON c.CUSTOMER_ID = o.CUSTOMER_ID;   -- "INNER" is optional

-- LEFT: all customers, orders if any
SELECT c.FIRST_NAME, o.ORDER_ID
FROM CUSTOMERS c LEFT JOIN ORDERS o ON c.CUSTOMER_ID = o.CUSTOMER_ID;

-- RIGHT and FULL OUTER
SELECT c.FIRST_NAME, o.ORDER_ID FROM CUSTOMERS c RIGHT JOIN ORDERS o ON c.CUSTOMER_ID = o.CUSTOMER_ID;
SELECT c.FIRST_NAME, o.ORDER_ID FROM CUSTOMERS c FULL OUTER JOIN ORDERS o ON c.CUSTOMER_ID = o.CUSTOMER_ID;

-- USING: when the key column has the same name in both tables
SELECT * FROM CUSTOMERS JOIN ORDERS USING (CUSTOMER_ID);

-- CROSS: all combinations (e.g. every customer × every category)
SELECT c.FIRST_NAME, p.CATEGORY FROM CUSTOMERS c CROSS JOIN (SELECT DISTINCT CATEGORY FROM PRODUCTS) p;

Table aliases (c, o) keep queries short. When both tables have a column with the same name, you must prefix it: c.CUSTOMER_ID.

8.3 Joining 3+ tables

-- order line report: who bought what, for how much, sold by whom
SELECT o.ORDER_ID, o.ORDER_DATE,
       c.FIRST_NAME || ' ' || c.LAST_NAME AS CUSTOMER,
       p.PRODUCT_NAME, i.QUANTITY, p.UNIT_PRICE,
       i.QUANTITY * p.UNIT_PRICE AS LINE_AMOUNT,
       e.EMP_NAME AS SALES_REP
FROM ORDERS o
JOIN CUSTOMERS   c ON o.CUSTOMER_ID = c.CUSTOMER_ID
JOIN ORDER_ITEMS i ON o.ORDER_ID    = i.ORDER_ID
JOIN PRODUCTS    p ON i.PRODUCT_ID  = p.PRODUCT_ID
LEFT JOIN EMPLOYEES e ON o.EMPLOYEE_ID = e.EMPLOYEE_ID
ORDER BY o.ORDER_ID;

8.4 Special patterns

-- SELF JOIN: employee with their manager (same table twice)
SELECT e.EMP_NAME AS EMPLOYEE, m.EMP_NAME AS MANAGER
FROM EMPLOYEES e LEFT JOIN EMPLOYEES m ON e.MANAGER_ID = m.EMPLOYEE_ID;

-- ANTI JOIN: customers with NO orders
SELECT c.* FROM CUSTOMERS c LEFT JOIN ORDERS o ON c.CUSTOMER_ID = o.CUSTOMER_ID
WHERE o.ORDER_ID IS NULL;

-- ORPHANS: orders whose customer is missing (data-quality check)
SELECT o.* FROM ORDERS o LEFT JOIN CUSTOMERS c ON o.CUSTOMER_ID = c.CUSTOMER_ID
WHERE c.CUSTOMER_ID IS NULL;

-- NON-EQUI JOIN: match on a range
CREATE OR REPLACE TEMPORARY TABLE PRICE_BANDS (BAND VARCHAR, LO NUMBER, HI NUMBER);
INSERT INTO PRICE_BANDS VALUES ('Budget',0,4999),('Mid',5000,19999),('Premium',20000,999999);
SELECT p.PRODUCT_NAME, p.UNIT_PRICE, b.BAND
FROM PRODUCTS p JOIN PRICE_BANDS b ON p.UNIT_PRICE BETWEEN b.LO AND b.HI;

ASOF JOIN (Snowflake): match each row to the closest earlier row in another table — e.g. each order to the most recent price change. FROM ORDERS o ASOF JOIN PRICE_HISTORY h MATCH_CONDITION (o.ORDER_DATE >= h.CHANGE_DATE) ON o.PRODUCT_ID = h.PRODUCT_ID.

Watch out for row explosion: joining on a non-unique key multiplies rows. Always check COUNT(*) before and after a join.

8.5 Set operators — stacking results

Both queries must have the same number of columns with compatible types; column names come from the first query.

SELECT CITY FROM CUSTOMERS WHERE SEGMENT = 'Retail'
UNION                 -- combined, duplicates removed
SELECT CITY FROM CUSTOMERS WHERE SEGMENT = 'Corporate';

SELECT CITY FROM CUSTOMERS WHERE SEGMENT = 'Retail'
UNION ALL             -- combined, duplicates kept (faster)
SELECT CITY FROM CUSTOMERS WHERE SEGMENT = 'Corporate';

SELECT CUSTOMER_ID FROM CUSTOMERS
INTERSECT             -- in both
SELECT CUSTOMER_ID FROM ORDERS;

SELECT CUSTOMER_ID FROM CUSTOMERS
EXCEPT                -- in first but not second (MINUS is the same)
SELECT CUSTOMER_ID FROM ORDERS;

Newer accounts also support UNION ALL BY NAME, which matches columns by name instead of position.

Exercises — Module 8

1. List every order with the customer's full name and city.
2. List all customers and the number of orders each placed (0 for none).
3. Find customers who have never placed an order — once with a LEFT JOIN and once with EXCEPT.
4. Find orders that point to a customer who does not exist.
5. Show each order's total value (sum of quantity × unit price).
6. Show total revenue per product category, only for DELIVERED orders.
7. Show every employee with their manager's name; show 'No manager' for top bosses.
8. Show each sales rep's name with the total value of orders they handled.
9. Which products have never been ordered? (Hint: insert a new product into a clone first if all are ordered.)
10. List cities that have both Corporate and Small Business customers using INTERSECT (answer: Mumbai).
11. Create a list of all people (customers and employees) with a column saying which type they are.

Module 9 of 12

Subqueries and CTEs: queries inside queries

A subquery is a query in brackets used by another query. A CTE (WITH) gives that inner query a name so complex logic reads top-to-bottom like steps in a recipe.

9.1 Subquery types

-- scalar subquery: returns ONE value
SELECT PRODUCT_NAME, UNIT_PRICE
FROM PRODUCTS
WHERE UNIT_PRICE > (SELECT AVG(UNIT_PRICE) FROM PRODUCTS);

-- scalar subquery in SELECT
SELECT PRODUCT_NAME, UNIT_PRICE,
       UNIT_PRICE - (SELECT AVG(UNIT_PRICE) FROM PRODUCTS) AS DIFF_FROM_AVG
FROM PRODUCTS;

-- list subquery with IN / NOT IN
SELECT * FROM CUSTOMERS
WHERE CUSTOMER_ID IN (SELECT CUSTOMER_ID FROM ORDERS WHERE STATUS = 'DELIVERED');

-- table subquery (derived table) in FROM
SELECT t.CUSTOMER_ID, t.ORDER_COUNT
FROM (SELECT CUSTOMER_ID, COUNT(*) AS ORDER_COUNT FROM ORDERS GROUP BY CUSTOMER_ID) t
WHERE t.ORDER_COUNT > 1;

9.2 Correlated subqueries and EXISTS

A correlated subquery refers to the outer row, so it is evaluated per row.

-- products priced above the average of THEIR OWN category
SELECT p.PRODUCT_NAME, p.CATEGORY, p.UNIT_PRICE
FROM PRODUCTS p
WHERE p.UNIT_PRICE > (SELECT AVG(p2.UNIT_PRICE) FROM PRODUCTS p2 WHERE p2.CATEGORY = p.CATEGORY);

-- EXISTS: is there at least one matching row?
SELECT c.* FROM CUSTOMERS c
WHERE EXISTS (SELECT 1 FROM ORDERS o WHERE o.CUSTOMER_ID = c.CUSTOMER_ID);

SELECT c.* FROM CUSTOMERS c
WHERE NOT EXISTS (SELECT 1 FROM ORDERS o WHERE o.CUSTOMER_ID = c.CUSTOMER_ID);

NOT IN trap: if the subquery returns even one NULL, NOT IN returns no rows at all. Prefer NOT EXISTS.

9.3 CTEs — WITH

WITH order_values AS (                      -- step 1: value of each order
    SELECT i.ORDER_ID, SUM(i.QUANTITY * p.UNIT_PRICE) AS ORDER_VALUE
    FROM ORDER_ITEMS i JOIN PRODUCTS p ON i.PRODUCT_ID = p.PRODUCT_ID
    GROUP BY i.ORDER_ID
),
customer_spend AS (                         -- step 2: spend per customer
    SELECT o.CUSTOMER_ID, SUM(v.ORDER_VALUE) AS TOTAL_SPEND, COUNT(*) AS ORDERS
    FROM ORDERS o JOIN order_values v ON o.ORDER_ID = v.ORDER_ID
    WHERE o.STATUS <> 'CANCELLED'
    GROUP BY o.CUSTOMER_ID
)
SELECT c.FIRST_NAME, c.CITY, s.TOTAL_SPEND, s.ORDERS,      -- step 3: report
       ROUND(s.TOTAL_SPEND / s.ORDERS, 2) AS AVG_ORDER_VALUE
FROM customer_spend s JOIN CUSTOMERS c ON s.CUSTOMER_ID = c.CUSTOMER_ID
ORDER BY s.TOTAL_SPEND DESC;

Why CTEs: each step can be tested alone, a CTE can be used several times, and this is exactly how dbt models are written.

9.4 Recursive CTE — walking a hierarchy

WITH RECURSIVE org AS (
    SELECT EMPLOYEE_ID, EMP_NAME, MANAGER_ID, 1 AS LVL, EMP_NAME::VARCHAR AS PATH
    FROM EMPLOYEES WHERE MANAGER_ID IS NULL                      -- anchor: the bosses
    UNION ALL
    SELECT e.EMPLOYEE_ID, e.EMP_NAME, e.MANAGER_ID, o.LVL + 1, o.PATH || ' > ' || e.EMP_NAME
    FROM EMPLOYEES e JOIN org o ON e.MANAGER_ID = o.EMPLOYEE_ID  -- repeat: their reports
)
SELECT * FROM org ORDER BY PATH;

-- Oracle-style alternative, also supported
SELECT EMP_NAME, LEVEL, SYS_CONNECT_BY_PATH(EMP_NAME, ' > ') AS PATH
FROM EMPLOYEES START WITH MANAGER_ID IS NULL CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;

Exercises — Module 9

1. Find employees who earn more than the average salary.
2. Find the most expensive product(s) using a subquery (not ORDER BY/LIMIT).
3. List customers who have at least one CANCELLED order, using IN.
4. List customers with no orders, using NOT EXISTS.
5. Show each employee's salary and the difference from their department's average (correlated subquery).
6. Using a derived table, find categories whose total quantity sold is above 10.
7. Rewrite exercise 6 as a CTE.
8. Using two CTEs, find the customer with the highest total spend on DELIVERED orders.
9. Using a recursive CTE, list everyone who reports (directly or indirectly) to Ravi Kumar, with their level.
10. Show products whose price is higher than every Stationery product (use > ALL).

Module 10 of 12

Window functions: ranking, running totals, previous/next row

A window function calculates across a group of rows without collapsing them — every row stays, and gets an extra column. This is the most-asked topic in data interviews.

10.1 Anatomy

FUNCTION() OVER (PARTITION BY <group> ORDER BY <sort> ROWS BETWEEN <frame>)
--               restart per group  order inside group  which rows to include

-- GROUP BY collapses: one row per department
SELECT DEPARTMENT, AVG(SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT;

-- window keeps every employee and adds the department average
SELECT EMP_NAME, DEPARTMENT, SALARY,
       AVG(SALARY) OVER (PARTITION BY DEPARTMENT) AS DEPT_AVG,
       SALARY - AVG(SALARY) OVER (PARTITION BY DEPARTMENT) AS DIFF,
       SUM(SALARY) OVER () AS COMPANY_TOTAL,
       ROUND(100 * RATIO_TO_REPORT(SALARY) OVER (), 1) AS PCT_OF_TOTAL
FROM EMPLOYEES;

10.2 Ranking

SELECT PRODUCT_NAME, CATEGORY, UNIT_PRICE,
  ROW_NUMBER() OVER (ORDER BY UNIT_PRICE DESC) AS RN,           -- 1,2,3,4 (always unique)
  RANK()       OVER (ORDER BY UNIT_PRICE DESC) AS RNK,          -- 1,2,2,4 (gaps after ties)
  DENSE_RANK() OVER (ORDER BY UNIT_PRICE DESC) AS DRNK,         -- 1,2,2,3 (no gaps)
  ROW_NUMBER() OVER (PARTITION BY CATEGORY ORDER BY UNIT_PRICE DESC) AS RN_IN_CAT,
  NTILE(3)     OVER (ORDER BY UNIT_PRICE DESC) AS PRICE_TERTILE, -- split into 3 buckets
  PERCENT_RANK() OVER (ORDER BY UNIT_PRICE) AS PCT_RANK
FROM PRODUCTS;

10.3 QUALIFY — filter on a window function (Snowflake)

WHERE runs before window functions, so you cannot write WHERE ROW_NUMBER() ... = 1. QUALIFY solves it without a subquery.

-- most expensive product in each category
SELECT * FROM PRODUCTS
QUALIFY ROW_NUMBER() OVER (PARTITION BY CATEGORY ORDER BY UNIT_PRICE DESC) = 1;

-- each customer's latest order
SELECT * FROM ORDERS
QUALIFY ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID ORDER BY ORDER_DATE DESC) = 1;

-- DE-DUPLICATION pattern (keep the newest copy of each key)
CREATE OR REPLACE TEMPORARY TABLE CUST_DUPS AS
SELECT * FROM CUSTOMERS UNION ALL SELECT * FROM CUSTOMERS WHERE CUSTOMER_ID <= 3;
SELECT * FROM CUST_DUPS
QUALIFY ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID ORDER BY SIGNUP_DATE DESC) = 1;

10.4 Previous / next row — LAG and LEAD

SELECT CUSTOMER_ID, ORDER_ID, ORDER_DATE,
  LAG(ORDER_DATE)  OVER (PARTITION BY CUSTOMER_ID ORDER BY ORDER_DATE) AS PREV_ORDER,
  LEAD(ORDER_DATE) OVER (PARTITION BY CUSTOMER_ID ORDER BY ORDER_DATE) AS NEXT_ORDER,
  DATEDIFF(DAY, LAG(ORDER_DATE) OVER (PARTITION BY CUSTOMER_ID ORDER BY ORDER_DATE), ORDER_DATE)
      AS DAYS_SINCE_PREV
FROM ORDERS ORDER BY CUSTOMER_ID, ORDER_DATE;
-- LAG(col, 2, 0): two rows back, 0 if none

10.5 FIRST_VALUE, LAST_VALUE, NTH_VALUE

SELECT EMP_NAME, DEPARTMENT, SALARY,
  FIRST_VALUE(EMP_NAME) OVER (PARTITION BY DEPARTMENT ORDER BY SALARY DESC) AS TOP_EARNER,
  LAST_VALUE(EMP_NAME)  OVER (PARTITION BY DEPARTMENT ORDER BY SALARY DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LOWEST_EARNER,
  NTH_VALUE(EMP_NAME, 2) OVER (PARTITION BY DEPARTMENT ORDER BY SALARY DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS SECOND_EARNER
FROM EMPLOYEES;

10.6 Running totals and moving averages — window frames

WITH daily AS (
  SELECT o.ORDER_DATE, SUM(i.QUANTITY * p.UNIT_PRICE) AS SALES
  FROM ORDERS o JOIN ORDER_ITEMS i ON o.ORDER_ID = i.ORDER_ID
  JOIN PRODUCTS p ON i.PRODUCT_ID = p.PRODUCT_ID
  GROUP BY o.ORDER_DATE)
SELECT ORDER_DATE, SALES,
  SUM(SALES) OVER (ORDER BY ORDER_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RUNNING_TOTAL,
  AVG(SALES) OVER (ORDER BY ORDER_DATE ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MOVING_AVG_3,
  COUNT(*)   OVER (ORDER BY ORDER_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ORDER_SEQ
FROM daily ORDER BY ORDER_DATE;

Exercises — Module 10

1. Rank all employees by salary (highest = 1) using RANK and DENSE_RANK side by side.
2. Number each customer's orders 1, 2, 3 in date order.
3. Show the cheapest product in each category with QUALIFY.
4. For each order, show the order date of that customer's previous order.
5. Show each employee's salary as a percentage of their department's total.
6. Show each product's price next to the highest price in its category, and the gap.
7. Show a running total of the number of orders by order date.
8. Find the 2nd highest salary in the company (classic interview question).
9. Remove duplicates from CUST_DUPS keeping one row per customer, and save the result as CUST_CLEAN.
10. Split customers into 4 groups by signup date with NTILE and show which group each falls in.
11. For each sales rep, show their orders with the value of the previous order they handled and the change.

Module 11 of 12

Semi-structured data: JSON with VARIANT and FLATTEN

Snowflake stores JSON in a VARIANT column and lets you query it with plain SQL — no pre-defined columns needed. Most modern data (APIs, app events, logs) arrives this way.

11.1 Load sample JSON

USE SCHEMA TRAINING_DB.RETAIL;
CREATE OR REPLACE TABLE WEB_EVENTS (EVENT VARIANT);

INSERT INTO WEB_EVENTS SELECT PARSE_JSON(column1) FROM VALUES
('{"event_id": 1, "user": {"id": 1, "name": "Amit", "city": "Delhi"},
   "device": "mobile", "ts": "2024-10-01 10:15:00",
   "cart": [{"sku": 101, "qty": 1}, {"sku": 102, "qty": 2}], "tags": ["sale", "new"]}'),
('{"event_id": 2, "user": {"id": 4, "name": "Sneha", "city": "Chennai"},
   "device": "desktop", "ts": "2024-10-02 18:40:00",
   "cart": [{"sku": 107, "qty": 2}], "tags": []}'),
('{"event_id": 3, "user": {"id": 6, "name": "Anjali"},
   "device": "mobile", "ts": "2024-10-03 09:05:00",
   "cart": [{"sku": 103, "qty": 1}, {"sku": 105, "qty": 20}, {"sku": 106, "qty": 3}],
   "tags": ["bulk"]}');

SELECT * FROM WEB_EVENTS;

(PARSE_JSON cannot be used inside INSERT ... VALUES directly, hence INSERT ... SELECT ... FROM VALUES.)

11.2 Reading values

SELECT
  EVENT:event_id                  AS RAW_ID,      -- still VARIANT, shown as 1
  EVENT:event_id::INT             AS EVENT_ID,    -- cast with ::
  EVENT:user.name::STRING         AS USER_NAME,   -- dot for nested keys
  EVENT:user['city']::STRING      AS CITY,        -- brackets inside a path; NULL if missing
  EVENT['device']::STRING         AS DEVICE,      -- bracket notation
  EVENT:ts::TIMESTAMP             AS EVENT_TS,
  EVENT:cart[0].sku::INT          AS FIRST_SKU,   -- arrays start at 0
  ARRAY_SIZE(EVENT:cart)          AS CART_LINES,
  TYPEOF(EVENT:cart)              AS TYPE_OF_CART  -- ARRAY
FROM WEB_EVENTS;

• Key names are case-sensitive: EVENT:Device returns NULL.
• Without ::STRING, text shows with double quotes ("Amit").

11.3 FLATTEN — one row per array element

SELECT
  e.EVENT:event_id::INT    AS EVENT_ID,
  e.EVENT:user.name::STRING AS USER_NAME,
  c.INDEX                  AS LINE_NO,
  c.VALUE:sku::INT         AS SKU,
  c.VALUE:qty::INT         AS QTY
FROM WEB_EVENTS e,
     LATERAL FLATTEN(INPUT => e.EVENT:cart) c;

-- join the flattened JSON to a normal table
SELECT e.EVENT:user.name::STRING AS USER_NAME, p.PRODUCT_NAME, c.VALUE:qty::INT AS QTY,
       c.VALUE:qty::INT * p.UNIT_PRICE AS CART_VALUE
FROM WEB_EVENTS e, LATERAL FLATTEN(INPUT => e.EVENT:cart) c
JOIN PRODUCTS p ON p.PRODUCT_ID = c.VALUE:sku::INT;

-- empty arrays disappear unless OUTER => TRUE
SELECT e.EVENT:event_id::INT, t.VALUE::STRING AS TAG
FROM WEB_EVENTS e, LATERAL FLATTEN(INPUT => e.EVENT:tags, OUTER => TRUE) t;

FLATTEN output columns: SEQ, KEY, PATH, INDEX, VALUE, THIS.

11.4 Building JSON from tables

SELECT OBJECT_CONSTRUCT('id', CUSTOMER_ID, 'name', FIRST_NAME, 'city', CITY) AS CUST_JSON
FROM CUSTOMERS;

SELECT ARRAY_CONSTRUCT(1, 2, 3), ARRAY_CONTAINS(2::VARIANT, ARRAY_CONSTRUCT(1,2,3));

SELECT CATEGORY, ARRAY_AGG(OBJECT_CONSTRUCT('name', PRODUCT_NAME, 'price', UNIT_PRICE)) AS ITEMS
FROM PRODUCTS GROUP BY CATEGORY;

SELECT TRY_PARSE_JSON('{bad json');   -- NULL instead of error

Loading JSON files: create a file format with TYPE = JSON (add STRIP_OUTER_ARRAY = TRUE when the file is one big array), then COPY INTO WEB_EVENTS FROM @stage.

Exercises — Module 11

1. Show event_id, user name, device and timestamp (as TIMESTAMP) for every event.
2. Show the city of each user; display 'Unknown' when it is missing.
3. Count the events per device.
4. Flatten the cart: one row per event per SKU with quantity.
5. Find the total quantity ordered per SKU across all events.
6. Join the flattened cart with PRODUCTS and show each event's total cart value.
7. List every tag with its event_id, including events with no tags.
8. Build a JSON object per order containing order_id, status and the customer's first name.

Module 12 of 12

Snowflake specials every engineer uses

These features are where Snowflake differs from Oracle/SQL Server and come up in every Snowflake interview.

12.1 Time Travel — query or restore the past

USE SCHEMA PRACTICE_DB.SANDBOX;
CREATE OR REPLACE TABLE TT_DEMO CLONE TRAINING_DB.RETAIL.PRODUCTS;
UPDATE TT_DEMO SET UNIT_PRICE = 0;                 -- oops!
SET bad_query = LAST_QUERY_ID();

SELECT * FROM TT_DEMO AT(OFFSET => -60*2);          -- as it was 2 minutes ago
SELECT * FROM TT_DEMO AT(TIMESTAMP => DATEADD(MINUTE, -2, CURRENT_TIMESTAMP)::TIMESTAMP_LTZ);
SELECT * FROM TT_DEMO BEFORE(STATEMENT => $bad_query);  -- just before the bad update

-- restore option 1: overwrite from the past version
INSERT OVERWRITE INTO TT_DEMO SELECT * FROM TT_DEMO BEFORE(STATEMENT => $bad_query);
-- restore option 2: clone the past into a new table
CREATE OR REPLACE TABLE TT_RESTORED CLONE TT_DEMO BEFORE(STATEMENT => $bad_query);

Retention: default 1 day; up to 90 days on Enterprise (DATA_RETENTION_TIME_IN_DAYS). After that, 7 days of Fail-safe — recoverable only by Snowflake Support.

12.2 Views

CREATE OR REPLACE VIEW TRAINING_DB.RETAIL.V_ORDER_LINES AS
SELECT o.ORDER_ID, o.ORDER_DATE, o.STATUS, c.FIRST_NAME, p.PRODUCT_NAME,
       i.QUANTITY, i.QUANTITY * p.UNIT_PRICE AS LINE_AMOUNT
FROM TRAINING_DB.RETAIL.ORDERS o
JOIN TRAINING_DB.RETAIL.ORDER_ITEMS i ON o.ORDER_ID = i.ORDER_ID
JOIN TRAINING_DB.RETAIL.PRODUCTS p ON i.PRODUCT_ID = p.PRODUCT_ID
LEFT JOIN TRAINING_DB.RETAIL.CUSTOMERS c ON o.CUSTOMER_ID = c.CUSTOMER_ID;

SELECT * FROM TRAINING_DB.RETAIL.V_ORDER_LINES WHERE STATUS = 'DELIVERED';
SHOW VIEWS IN SCHEMA TRAINING_DB.RETAIL;
SELECT GET_DDL('VIEW', 'TRAINING_DB.RETAIL.V_ORDER_LINES');

12.3 Session variables

SET min_price = 5000;
SET (cat, city) = ('Electronics', 'Mumbai');
SELECT * FROM TRAINING_DB.RETAIL.PRODUCTS WHERE UNIT_PRICE > $min_price AND CATEGORY = $cat;
SHOW VARIABLES;

SET tbl = 'TRAINING_DB.RETAIL.CUSTOMERS';
SELECT * FROM IDENTIFIER($tbl);          -- variable as an object name
UNSET min_price;

12.4 Transactions

BEGIN;                         -- or BEGIN TRANSACTION
  UPDATE TT_DEMO SET UNIT_PRICE = UNIT_PRICE * 2;
  SELECT * FROM TT_DEMO;       -- you see the change; others don't yet
ROLLBACK;                      -- undo everything since BEGIN (COMMIT would save it)
SHOW TRANSACTIONS;

DDL statements (CREATE, ALTER, DROP) auto-commit any open transaction.

12.5 Sequences

CREATE OR REPLACE SEQUENCE INVOICE_SEQ START = 1000 INCREMENT = 1;
SELECT INVOICE_SEQ.NEXTVAL, INVOICE_SEQ.NEXTVAL;
CREATE OR REPLACE TABLE INVOICES (INVOICE_NO INT DEFAULT INVOICE_SEQ.NEXTVAL, AMOUNT NUMBER);
INSERT INTO INVOICES (AMOUNT) VALUES (500), (750);
SELECT * FROM INVOICES;   -- numbers are unique but may have gaps

12.6 PIVOT and UNPIVOT

-- rows → columns: order count per employee per status
SELECT * FROM (SELECT EMPLOYEE_ID, STATUS, ORDER_ID FROM TRAINING_DB.RETAIL.ORDERS)
PIVOT (COUNT(ORDER_ID) FOR STATUS IN ('DELIVERED','SHIPPED','PENDING','CANCELLED'))
AS p (EMPLOYEE_ID, DELIVERED, SHIPPED, PENDING, CANCELLED);

-- dynamic list of values
SELECT * FROM (SELECT EMPLOYEE_ID, STATUS, ORDER_ID FROM TRAINING_DB.RETAIL.ORDERS)
PIVOT (COUNT(ORDER_ID) FOR STATUS IN (ANY ORDER BY STATUS));

-- columns → rows
CREATE OR REPLACE TEMPORARY TABLE QTR_SALES (REGION VARCHAR, Q1 INT, Q2 INT, Q3 INT, Q4 INT);
INSERT INTO QTR_SALES VALUES ('North',100,120,90,150),('South',80,95,110,130);
SELECT * FROM QTR_SALES UNPIVOT (SALES FOR QUARTER IN (Q1, Q2, Q3, Q4));

12.7 Sampling and generating test data

SELECT * FROM TRAINING_DB.RETAIL.CUSTOMERS SAMPLE (50);        -- ~50% of rows
SELECT * FROM TRAINING_DB.RETAIL.CUSTOMERS SAMPLE (3 ROWS);     -- exactly 3 rows

-- 1,000 fake rows in one statement
CREATE OR REPLACE TABLE FAKE_SALES AS
SELECT SEQ4() + 1 AS ID,
       UNIFORM(101, 107, RANDOM()) AS PRODUCT_ID,
       UNIFORM(1, 10, RANDOM()) AS QTY,
       DATEADD(DAY, -UNIFORM(0, 365, RANDOM()), CURRENT_DATE) AS SALE_DATE
FROM TABLE(GENERATOR(ROWCOUNT => 1000));

12.8 Access control basics (RBAC)

USE ROLE SECURITYADMIN;
CREATE ROLE IF NOT EXISTS ANALYST;
GRANT USAGE ON WAREHOUSE TRAINING_WH TO ROLE ANALYST;
GRANT USAGE ON DATABASE TRAINING_DB TO ROLE ANALYST;
GRANT USAGE ON SCHEMA TRAINING_DB.RETAIL TO ROLE ANALYST;
GRANT SELECT ON ALL TABLES IN SCHEMA TRAINING_DB.RETAIL TO ROLE ANALYST;
GRANT SELECT ON FUTURE TABLES IN SCHEMA TRAINING_DB.RETAIL TO ROLE ANALYST;
GRANT ROLE ANALYST TO USER <your_user>;
SHOW GRANTS TO ROLE ANALYST;
REVOKE SELECT ON ALL TABLES IN SCHEMA TRAINING_DB.RETAIL FROM ROLE ANALYST;

System roles: ACCOUNTADMIN (top, use sparingly) → SECURITYADMIN (grants) → USERADMIN (users/roles); SYSADMIN (objects, warehouses); PUBLIC (everyone).

12.9 Session settings, caching and tagging

ALTER SESSION SET TIMEZONE = 'Asia/Kolkata';
ALTER SESSION SET DATE_OUTPUT_FORMAT = 'DD-MM-YYYY';
ALTER SESSION SET QUERY_TAG = 'batch_jan_module12';   -- find your queries later
ALTER SESSION SET USE_CACHED_RESULT = FALSE;           -- force real execution (testing speed)
ALTER SESSION UNSET DATE_OUTPUT_FORMAT;

Identical queries on unchanged data return from the result cache for 24 hours at no compute cost.

12.10 A first UDF and stored procedure

CREATE OR REPLACE FUNCTION GST(AMT NUMBER) RETURNS NUMBER AS 'AMT * 0.18';
SELECT PRODUCT_NAME, UNIT_PRICE, GST(UNIT_PRICE) FROM TRAINING_DB.RETAIL.PRODUCTS;

CREATE OR REPLACE PROCEDURE CLEANUP_CANCELLED()
RETURNS VARCHAR LANGUAGE SQL AS
$$
BEGIN
  DELETE FROM PRACTICE_DB.SANDBOX.ORDERS_CLONE WHERE STATUS = 'CANCELLED';
  RETURN 'Rows deleted: ' || SQLROWCOUNT;
END;
$$;
CALL CLEANUP_CANCELLED();

Exercises — Module 12

1. Clone CUSTOMERS into SANDBOX, delete all Mumbai customers, then restore them using Time Travel with the statement ID.
2. Query the clone as it was 5 minutes ago.
3. Create a view V_CUSTOMER_SPEND showing each customer's total spend on non-cancelled orders; query it for the top 3.
4. Store a city name in a variable and use it to filter CUSTOMERS.
5. Start a transaction, delete all rows from a clone, check the count, then roll back and check again.
6. Create a sequence starting at 500 and use it as the default ID of a new table; insert 3 rows.
7. Pivot total quantity per product category (columns) per order status (rows).
8. Generate 10,000 rows of fake orders and count them by month.
9. Create a role REPORT_READER that can only SELECT from V_CUSTOMER_SPEND.
10. Write a SQL UDF that returns the full name from first and last name.

Final round

Capstone — 15 business questions and cheat sheet

The capstone mixes every module, phrased the way a manager would ask. Give learners 2–3 hours; each answer needs several concepts together.

Business questions

1. Account audit: list every table in TRAINING_DB.RETAIL with its row count, biggest first (no INFORMATION_SCHEMA allowed).
2. Revenue headline: total revenue from DELIVERED orders, and the number of delivered orders.
3. Monthly trend: revenue per month (all non-cancelled orders) with month-over-month change.
4. Best sellers: top 3 products by quantity sold, with their category and revenue.
5. Category share: each category's revenue and its % share of total revenue.
6. Customer 360: for every customer: full name, city, number of orders, total spend, first and last order date. Customers with no orders must appear with 0.
7. Inactive customers: customers who signed up but never ordered — the marketing team wants their emails.
8. Data quality report: one query returning counts of (a) customers with no email, (b) customers with no segment, (c) orders with an unknown customer.
9. Repeat buyers: customers with more than one non-cancelled order and the average days between their orders.
10. Rep leaderboard: rank sales reps by delivered revenue; show their manager's name too.
11. Biggest order per customer: for each customer, the single order with the highest value (use QUALIFY).
12. Price review: products priced above their category average, with the % above.
13. Status pivot: one row per city, columns = number of orders in each status.
14. Reporting layer: create a secure view V_SALES_SUMMARY (order date, city, category, revenue) for a reporting team.
15. Disaster drill: in a clone of ORDERS, someone set every STATUS to 'CANCELLED'. Restore the correct statuses without re-running the setup script.

Command cheat sheet

Setup scripts

Practice datasets — four realistic databases

Four scripts build the data used by the exercise bank (Levels 2–5) and all five projects. Run each one once, top to bottom, on an X-Small warehouse. Together they take about 2–3 minutes and create roughly 700,000 rows.

The scripts use UNIFORM(lo, hi, HASH(ID, n)) instead of RANDOM(). HASH always returns the same number for the same input, so every student gets identical data and the same answers. Dates are fixed to 2025, so results do not change with the day you run them.

D1. ECOM_DB — e-commerce (run first)

USE ROLE SYSADMIN;
USE WAREHOUSE TRAINING_WH;
CREATE DATABASE IF NOT EXISTS ECOM_DB;
CREATE SCHEMA IF NOT EXISTS ECOM_DB.RAW;        -- data exactly as received from source
CREATE SCHEMA IF NOT EXISTS ECOM_DB.STG;        -- cleaned (built in Project 1)
CREATE SCHEMA IF NOT EXISTS ECOM_DB.MART;       -- star schema (built in Project 1)
CREATE SCHEMA IF NOT EXISTS ECOM_DB.PRACTICE;   -- clean views for the exercise bank
USE SCHEMA ECOM_DB.RAW;

CREATE OR REPLACE TABLE RAW_CUSTOMERS AS
SELECT ID AS CUSTOMER_ID,
  ARRAY_CONSTRUCT('Aarav','Vivaan','Aditya','Diya','Isha','Kabir','Meera','Rohan','Sara','Tanvi','Yash','Zoya')[UNIFORM(0,11,HASH(ID,1))]::STRING AS FIRST_NAME,
  ARRAY_CONSTRUCT('Sharma','Verma','Iyer','Khan','Patel','Reddy','Das','Nair','Singh','Gupta')[UNIFORM(0,9,HASH(ID,2))]::STRING AS LAST_NAME,
  CASE WHEN UNIFORM(1,100,HASH(ID,3)) <= 5  THEN NULL                                   -- 5% missing
       WHEN UNIFORM(1,100,HASH(ID,3)) <= 10 THEN '  CUST' || ID || '@MAIL.COM '          -- 5% messy
       ELSE 'cust' || ID || '@mail.com' END AS EMAIL,
  ARRAY_CONSTRUCT('Delhi','Mumbai','Bengaluru','Chennai','Kolkata','Hyderabad','Pune','Jaipur','Lucknow','Agra')[UNIFORM(0,9,HASH(ID,4))]::STRING AS CITY,
  DATEADD(DAY, UNIFORM(0,1095,HASH(ID,5)), '2022-01-01'::DATE) AS SIGNUP_DATE
FROM (SELECT ROW_NUMBER() OVER (ORDER BY SEQ4()) AS ID FROM TABLE(GENERATOR(ROWCOUNT => 2000)));

CREATE OR REPLACE TABLE RAW_PRODUCTS AS
SELECT ID AS PRODUCT_ID,
  IFF(ID % 50 = 0, NULL,                                                                -- 4 products with no category
      ARRAY_CONSTRUCT('Electronics','Fashion','Home & Kitchen','Books','Sports','Beauty')[UNIFORM(0,5,HASH(ID,11))]::STRING) AS CATEGORY,
  ARRAY_CONSTRUCT('Zenith','Aurora','Kraft','Nimbus','Vista','Orbit','Lotus','Pebble')[UNIFORM(0,7,HASH(ID,12))]::STRING AS BRAND,
  BRAND || ' ' || NVL(CATEGORY,'Misc') || ' #' || ID AS PRODUCT_NAME,
  (CASE CATEGORY WHEN 'Electronics'    THEN UNIFORM(999, 79999, HASH(ID,13))
                 WHEN 'Fashion'        THEN UNIFORM(299, 4999,  HASH(ID,13))
                 WHEN 'Home & Kitchen' THEN UNIFORM(199, 14999, HASH(ID,13))
                 WHEN 'Books'          THEN UNIFORM(149, 1499,  HASH(ID,13))
                 WHEN 'Sports'         THEN UNIFORM(299, 19999, HASH(ID,13))
                 WHEN 'Beauty'         THEN UNIFORM(99,  2999,  HASH(ID,13))
                 ELSE UNIFORM(99, 999, HASH(ID,13)) END)::NUMBER(10,2) AS UNIT_PRICE,
  ROUND(UNIT_PRICE * UNIFORM(55, 80, HASH(ID,14)) / 100, 2) AS COST_PRICE
FROM (SELECT ROW_NUMBER() OVER (ORDER BY SEQ4()) AS ID FROM TABLE(GENERATOR(ROWCOUNT => 200)));

CREATE OR REPLACE TABLE RAW_ORDERS AS
SELECT 100000 + ID AS ORDER_ID,
  IFF(ID % 1000 = 7, 2000 + UNIFORM(1,50,HASH(ID,21)), UNIFORM(1,2000,HASH(ID,21))) AS CUSTOMER_ID,  -- 60 orders with unknown customer
  DATEADD(MINUTE, UNIFORM(0,525599,HASH(ID,22)),
          IFF(ID % 1500 = 3, '2030-01-01', '2025-01-01')::TIMESTAMP_NTZ) AS ORDER_TS,        -- 40 orders dated 2030 (clock bug)
  CASE WHEN UNIFORM(1,100,HASH(ID,23)) <= 78 THEN 'DELIVERED'
       WHEN UNIFORM(1,100,HASH(ID,23)) <= 87 THEN 'CANCELLED'
       WHEN UNIFORM(1,100,HASH(ID,23)) <= 94 THEN 'RETURNED'
       ELSE 'SHIPPED' END AS STATUS,
  ARRAY_CONSTRUCT('UPI','UPI','UPI','CARD','CARD','COD','WALLET','NETBANKING')[UNIFORM(0,7,HASH(ID,24))]::STRING AS PAYMENT_MODE,
  UNIFORM(1,4,HASH(ID,25)) AS ITEM_COUNT,
  '2026-01-01 02:00:00'::TIMESTAMP_NTZ AS LOADED_AT
FROM (SELECT ROW_NUMBER() OVER (ORDER BY SEQ4()) AS ID FROM TABLE(GENERATOR(ROWCOUNT => 60000)));

-- planted data-quality problems
UPDATE RAW_ORDERS SET STATUS = LOWER(STATUS) || ' ' WHERE ORDER_ID % 40 = 0;     -- 'delivered '
UPDATE RAW_ORDERS SET STATUS = INITCAP(STATUS)       WHERE ORDER_ID % 55 = 0;     -- 'Cancelled'
INSERT INTO RAW_ORDERS                                                             -- status updates arriving a day later
SELECT ORDER_ID, CUSTOMER_ID, ORDER_TS, 'DELIVERED', PAYMENT_MODE, ITEM_COUNT, '2026-01-02 02:00:00'
FROM RAW_ORDERS WHERE STATUS = 'SHIPPED' AND ORDER_ID % 3 = 0;
INSERT INTO RAW_ORDERS SELECT * FROM RAW_ORDERS WHERE ORDER_ID % 500 = 0;          -- exact duplicate rows

CREATE OR REPLACE TABLE RAW_ORDER_ITEMS AS
SELECT o.ORDER_ID, l.LINE_NO,
  UNIFORM(1,200,HASH(o.ORDER_ID,l.LINE_NO,31)) AS PRODUCT_ID,
  IFF(HASH(o.ORDER_ID,l.LINE_NO) % 997 = 0, 0, UNIFORM(1,3,HASH(o.ORDER_ID,l.LINE_NO,32))) AS QTY,   -- a few zero quantities
  ARRAY_CONSTRUCT(0,0,0,5,10,15,20)[UNIFORM(0,6,HASH(o.ORDER_ID,l.LINE_NO,33))]::NUMBER(5,2) AS DISCOUNT_PCT
FROM (SELECT DISTINCT ORDER_ID, ITEM_COUNT FROM RAW_ORDERS) o
JOIN (SELECT ROW_NUMBER() OVER (ORDER BY SEQ4()) AS LINE_NO FROM TABLE(GENERATOR(ROWCOUNT => 4))) l
  ON l.LINE_NO <= o.ITEM_COUNT;

-- clean views used by the exercise bank (Project 1 builds proper tables instead)
CREATE OR REPLACE VIEW ECOM_DB.PRACTICE.CUSTOMERS AS
SELECT CUSTOMER_ID, FIRST_NAME, LAST_NAME, LOWER(TRIM(EMAIL)) AS EMAIL, CITY, SIGNUP_DATE FROM ECOM_DB.RAW.RAW_CUSTOMERS;

CREATE OR REPLACE VIEW ECOM_DB.PRACTICE.PRODUCTS AS
SELECT PRODUCT_ID, PRODUCT_NAME, NVL(CATEGORY,'Uncategorised') AS CATEGORY, BRAND, UNIT_PRICE, COST_PRICE
FROM ECOM_DB.RAW.RAW_PRODUCTS;

CREATE OR REPLACE VIEW ECOM_DB.PRACTICE.ORDERS AS
SELECT ORDER_ID, CUSTOMER_ID, ORDER_TS, ORDER_TS::DATE AS ORDER_DATE, UPPER(TRIM(STATUS)) AS STATUS, PAYMENT_MODE
FROM ECOM_DB.RAW.RAW_ORDERS
WHERE YEAR(ORDER_TS) = 2025
QUALIFY ROW_NUMBER() OVER (PARTITION BY ORDER_ID ORDER BY LOADED_AT DESC) = 1;

CREATE OR REPLACE VIEW ECOM_DB.PRACTICE.ORDER_ITEMS AS
SELECT i.ORDER_ID, i.LINE_NO, i.PRODUCT_ID, i.QTY, i.DISCOUNT_PCT, p.UNIT_PRICE,
       ROUND(i.QTY * p.UNIT_PRICE * (1 - i.DISCOUNT_PCT / 100), 2) AS LINE_AMOUNT
FROM ECOM_DB.RAW.RAW_ORDER_ITEMS i JOIN ECOM_DB.RAW.RAW_PRODUCTS p ON i.PRODUCT_ID = p.PRODUCT_ID
WHERE i.QTY > 0;

CREATE OR REPLACE VIEW ECOM_DB.PRACTICE.SALES AS          -- one row per order line, header columns included
SELECT o.ORDER_ID, o.ORDER_TS, o.ORDER_DATE, o.CUSTOMER_ID, o.STATUS, o.PAYMENT_MODE,
       i.LINE_NO, i.PRODUCT_ID, p.CATEGORY, p.BRAND, i.QTY, i.DISCOUNT_PCT, i.LINE_AMOUNT,
       i.QTY * p.COST_PRICE AS COST_AMOUNT
FROM ECOM_DB.PRACTICE.ORDERS o
JOIN ECOM_DB.PRACTICE.ORDER_ITEMS i ON o.ORDER_ID = i.ORDER_ID
JOIN ECOM_DB.PRACTICE.PRODUCTS p ON i.PRODUCT_ID = p.PRODUCT_ID;

SHOW TABLES IN SCHEMA ECOM_DB.RAW;   -- check row counts

Revenue rule used everywhere: revenue = SUM(LINE_AMOUNT) of orders with STATUS = 'DELIVERED', unless an exercise says otherwise.

D2. BANK_DB — retail banking

USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS BANK_DB;
CREATE SCHEMA IF NOT EXISTS BANK_DB.CORE;
USE SCHEMA BANK_DB.CORE;

CREATE OR REPLACE TABLE ACCOUNTS AS
SELECT 5000 + ID AS ACCOUNT_ID,
  ARRAY_CONSTRUCT('Anil','Bhavna','Chetan','Deepa','Farhan','Gita','Harsh','Jaya','Kunal','Lata','Manoj','Nisha')[UNIFORM(0,11,HASH(ID,41))]::STRING || ' ' ||
  ARRAY_CONSTRUCT('Rao','Mehta','Bose','Kulkarni','Joshi','Saxena','Pillai','Chopra')[UNIFORM(0,7,HASH(ID,42))]::STRING AS CUSTOMER_NAME,
  ARRAY_CONSTRUCT('Delhi-CP','Mumbai-Fort','Pune-FC Road','Agra-Sanjay Place','Chennai-T Nagar','Bengaluru-MG Road')[UNIFORM(0,5,HASH(ID,43))]::STRING AS BRANCH,
  IFF(UNIFORM(1,100,HASH(ID,44)) <= 80, 'SAVINGS', 'CURRENT') AS ACCOUNT_TYPE,
  DATEADD(DAY, UNIFORM(0,1460,HASH(ID,45)), '2021-01-01'::DATE) AS OPEN_DATE,
  UNIFORM(1000, 200000, HASH(ID,46))::NUMBER(14,2) AS OPENING_BALANCE,      -- balance on 1-Jan-2025
  IFF(ID % 40 = 0, 'DORMANT', 'ACTIVE') AS ACCOUNT_STATUS
FROM (SELECT ROW_NUMBER() OVER (ORDER BY SEQ4()) AS ID FROM TABLE(GENERATOR(ROWCOUNT => 1000)));

CREATE OR REPLACE TABLE TRANSACTIONS AS
SELECT ID AS TXN_ID,
  5000 + UNIFORM(1,1000,HASH(ID,51)) AS ACCOUNT_ID,
  DATEADD(SECOND, UNIFORM(0,31535999,HASH(ID,52)), '2025-01-01'::TIMESTAMP_NTZ) AS TXN_TS,
  IFF(UNIFORM(1,100,HASH(ID,53)) <= 52, 'CREDIT', 'DEBIT') AS TXN_TYPE,
  ARRAY_CONSTRUCT('UPI','UPI','UPI','CARD','ATM','NEFT','IMPS','BRANCH')[UNIFORM(0,7,HASH(ID,54))]::STRING AS CHANNEL,
  (IFF(UNIFORM(1,1000,HASH(ID,55)) <= 5, UNIFORM(200000,900000,HASH(ID,56)), UNIFORM(50,25000,HASH(ID,56)))
     + UNIFORM(0,99,HASH(ID,64)) / 100)::NUMBER(14,2) AS AMOUNT,
  IFF(TXN_TYPE = 'DEBIT' AND CHANNEL IN ('UPI','CARD'),
      ARRAY_CONSTRUCT('Swiggy','Zomato','Amazon','Flipkart','BigBasket','IRCTC','Uber','Reliance Digital','DMart','Croma')[UNIFORM(0,9,HASH(ID,57))]::STRING,
      NULL) AS MERCHANT,
  OBJECT_CONSTRUCT(
    'ip',  '10.' || UNIFORM(0,255,HASH(ID,58)) || '.' || UNIFORM(0,255,HASH(ID,59)) || '.' || UNIFORM(1,254,HASH(ID,60)),
    'os',  ARRAY_CONSTRUCT('Android','iOS','Web')[UNIFORM(0,2,HASH(ID,61))]::STRING,
    'geo', OBJECT_CONSTRUCT('city',
             ARRAY_CONSTRUCT('Delhi','Mumbai','Pune','Agra','Chennai','Bengaluru','Dubai','Singapore')
               [IFF(UNIFORM(1,100,HASH(ID,62)) <= 2, UNIFORM(6,7,HASH(ID,63)), UNIFORM(0,5,HASH(ID,63)))]::STRING)
  )::VARIANT AS DEVICE_INFO
FROM (SELECT ROW_NUMBER() OVER (ORDER BY SEQ4()) AS ID FROM TABLE(GENERATOR(ROWCOUNT => 150000)));

-- planted fraud burst: 20 accounts, 6 card debits 45 seconds apart from Singapore at 2 AM
INSERT INTO TRANSACTIONS
SELECT 900000 + ROW_NUMBER() OVER (ORDER BY a.ACCOUNT_ID, g.N), a.ACCOUNT_ID,
       DATEADD(SECOND, g.N * 45, '2025-11-14 02:10:00'::TIMESTAMP_NTZ), 'DEBIT', 'CARD', 9999.00, 'Unknown Merchant',
       OBJECT_CONSTRUCT('ip', '185.220.' || g.N || '.9', 'os', 'Web', 'geo', OBJECT_CONSTRUCT('city', 'Singapore'))
FROM (SELECT ACCOUNT_ID FROM ACCOUNTS WHERE ACCOUNT_ID % 50 = 7) a
CROSS JOIN (SELECT ROW_NUMBER() OVER (ORDER BY SEQ4()) AS N FROM TABLE(GENERATOR(ROWCOUNT => 6))) g;

-- planted double charges: 30 transactions recorded twice with a new id
INSERT INTO TRANSACTIONS
SELECT TXN_ID + 1000000, ACCOUNT_ID, TXN_TS, TXN_TYPE, CHANNEL, AMOUNT, MERCHANT, DEVICE_INFO
FROM TRANSACTIONS WHERE TXN_ID % 5000 = 0;

-- dormant accounts: remove most activity after Feb 2025, keep a few suspicious ones
DELETE FROM TRANSACTIONS t USING ACCOUNTS a
WHERE t.ACCOUNT_ID = a.ACCOUNT_ID AND a.ACCOUNT_STATUS = 'DORMANT'
  AND t.TXN_TS >= '2025-03-01' AND t.TXN_ID % 25 <> 0;

-- daily app logins for 200 accounts, with a 45-day gap for 20 of them
CREATE OR REPLACE TABLE LOGINS AS
SELECT a.ACCOUNT_ID, d.LOGIN_DATE
FROM (SELECT ACCOUNT_ID FROM ACCOUNTS WHERE ACCOUNT_ID <= 5200) a
CROSS JOIN (SELECT DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY SEQ4()) - 1, '2025-01-01'::DATE) AS LOGIN_DATE
            FROM TABLE(GENERATOR(ROWCOUNT => 365))) d
WHERE UNIFORM(1,100,HASH(a.ACCOUNT_ID, d.LOGIN_DATE)) <= 55
  AND NOT (a.ACCOUNT_ID % 10 = 3 AND d.LOGIN_DATE BETWEEN '2025-06-01' AND '2025-07-15');

SHOW TABLES IN SCHEMA BANK_DB.CORE;

D3. HR_DB — people and payroll

USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS HR_DB;
CREATE SCHEMA IF NOT EXISTS HR_DB.CORE;
USE SCHEMA HR_DB.CORE;

CREATE OR REPLACE TABLE DEPARTMENTS (DEPT_ID INT, DEPT_NAME VARCHAR(50), LOCATION VARCHAR(30));
INSERT INTO DEPARTMENTS VALUES
 (10,'Leadership','Gurugram'), (20,'Engineering','Bengaluru'), (30,'Data & Analytics','Pune'),
 (40,'Sales','Mumbai'), (50,'Customer Support','Noida'), (60,'Finance','Gurugram'), (70,'HR','Gurugram');

-- 1 CEO (id 1) > 6 department heads (ids 2-7) > 53 managers (ids 8-60) > 1,140 staff
CREATE OR REPLACE TABLE EMPLOYEES AS
SELECT ID AS EMP_ID,
  ARRAY_CONSTRUCT('Aakash','Bina','Chirag','Divya','Eshan','Fatima','Gaurav','Hema','Imran','Jyoti','Kartik','Lavanya','Mohit','Nandini','Om','Prachi')[UNIFORM(0,15,HASH(ID,71))]::STRING
    || ' ' || ARRAY_CONSTRUCT('Agarwal','Bhat','Chauhan','Desai','Ghosh','Hegde','Jain','Kapoor','Menon','Rana')[UNIFORM(0,9,HASH(ID,72))]::STRING AS EMP_NAME,
  IFF(UNIFORM(1,100,HASH(ID,73)) <= 58, 'M', 'F') AS GENDER,
  CASE WHEN ID = 1 THEN 10 WHEN ID <= 7 THEN ID * 10 ELSE 20 + 10 * UNIFORM(0,5,HASH(ID,74)) END AS DEPT_ID,
  CASE WHEN ID = 1 THEN NULL WHEN ID <= 7 THEN 1
       WHEN ID <= 60 THEN 2 + UNIFORM(0,5,HASH(ID,74))          -- manager reports to own department head
       ELSE UNIFORM(8,60,HASH(ID,75)) END AS MANAGER_ID,
  CASE WHEN ID = 1 THEN 'L7' WHEN ID <= 7 THEN 'L6' WHEN ID <= 60 THEN 'L5'
       ELSE ARRAY_CONSTRUCT('L1','L1','L2','L2','L2','L3','L3','L4')[UNIFORM(0,7,HASH(ID,76))]::STRING END AS JOB_LEVEL,
  CASE WHEN ID = 1  THEN '2012-04-01'::DATE
       WHEN ID <= 7 THEN DATEADD(DAY, UNIFORM(0,1200,HASH(ID,77)), '2013-01-01'::DATE)
       WHEN ID <= 60 THEN DATEADD(DAY, UNIFORM(0,1800,HASH(ID,77)), '2015-01-01'::DATE)
       ELSE DATEADD(DAY, UNIFORM(0,2900,HASH(ID,77)), '2018-01-01'::DATE) END AS HIRE_DATE,
  IFF(ID > 7 AND UNIFORM(1,100,HASH(ID,78)) <= 18,
      LEAST(DATEADD(DAY, UNIFORM(90,1100,HASH(ID,79)), HIRE_DATE), '2025-12-31'::DATE), NULL) AS EXIT_DATE,
  NULL::VARCHAR(30) AS CITY
FROM (SELECT ROW_NUMBER() OVER (ORDER BY SEQ4()) AS ID FROM TABLE(GENERATOR(ROWCOUNT => 1200)));

-- staff sit in their manager's department; 15% work remotely
UPDATE EMPLOYEES e SET DEPT_ID = m.DEPT_ID
FROM (SELECT EMP_ID, DEPT_ID FROM EMPLOYEES WHERE EMP_ID BETWEEN 8 AND 60) m
WHERE e.MANAGER_ID = m.EMP_ID AND e.EMP_ID > 60;
UPDATE EMPLOYEES e SET CITY = IFF(UNIFORM(1,100,HASH(e.EMP_ID,80)) <= 85, d.LOCATION, 'Remote')
FROM DEPARTMENTS d WHERE e.DEPT_ID = d.DEPT_ID;

-- one salary row per year of service (about 9% a year)
CREATE OR REPLACE TABLE SALARY_HISTORY AS
SELECT e.EMP_ID,
  DATEADD(YEAR, y.N, e.HIRE_DATE) AS EFFECTIVE_FROM,
  ROUND(CASE e.JOB_LEVEL WHEN 'L1' THEN 450000 WHEN 'L2' THEN 750000 WHEN 'L3' THEN 1200000
                         WHEN 'L4' THEN 1800000 WHEN 'L5' THEN 2800000 WHEN 'L6' THEN 4500000 ELSE 9000000 END
        * (1 + UNIFORM(0,20,HASH(e.EMP_ID,81)) / 100)
        * POWER(1.09, y.N) * (1 + UNIFORM(0,3,HASH(e.EMP_ID,y.N,82)) / 100), -3) AS ANNUAL_SALARY
FROM EMPLOYEES e
JOIN (SELECT ROW_NUMBER() OVER (ORDER BY SEQ4()) - 1 AS N FROM TABLE(GENERATOR(ROWCOUNT => 15))) y
  ON DATEADD(YEAR, y.N, e.HIRE_DATE) <= NVL(e.EXIT_DATE, '2025-12-31'::DATE);

-- 2025 attendance on weekdays: P = present, WFH, L = leave, A = absent
CREATE OR REPLACE TABLE ATTENDANCE AS
SELECT e.EMP_ID, d.WORK_DATE,
  CASE WHEN e.EMP_ID % 97 = 0 AND DAYOFWEEKISO(d.WORK_DATE) IN (1,5)
            AND UNIFORM(1,100,HASH(e.EMP_ID,d.WORK_DATE,7)) <= 35 THEN 'A'   -- Monday/Friday pattern
       WHEN UNIFORM(1,100,HASH(e.EMP_ID,d.WORK_DATE)) <= 5  THEN 'A'
       WHEN UNIFORM(1,100,HASH(e.EMP_ID,d.WORK_DATE)) <= 11 THEN 'L'
       WHEN UNIFORM(1,100,HASH(e.EMP_ID,d.WORK_DATE)) <= 35 THEN 'WFH'
       ELSE 'P' END AS STATUS
FROM EMPLOYEES e
JOIN (SELECT DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY SEQ4()) - 1, '2025-01-01'::DATE) AS WORK_DATE
      FROM TABLE(GENERATOR(ROWCOUNT => 365))) d
  ON d.WORK_DATE >= e.HIRE_DATE AND d.WORK_DATE <= NVL(e.EXIT_DATE, '2025-12-31'::DATE)
WHERE DAYOFWEEKISO(d.WORK_DATE) <= 5;

SHOW TABLES IN SCHEMA HR_DB.CORE;

D4. FOOD_DB — app clickstream (JSON)

Each session walks part of the funnel app_open → search → view_restaurant → add_to_cart → order_placed; 30% stop after opening the app and 12% place an order.

USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS FOOD_DB;
CREATE SCHEMA IF NOT EXISTS FOOD_DB.APP;
USE SCHEMA FOOD_DB.APP;

CREATE OR REPLACE TABLE APP_EVENTS AS
WITH s AS (
  SELECT ID AS SESSION_NO,
    UNIFORM(1,500,HASH(ID,81)) AS USER_ID,
    DATEADD(SECOND, UNIFORM(0,2678399,HASH(ID,82)), '2025-10-01'::TIMESTAMP_NTZ) AS START_TS,
    CASE WHEN UNIFORM(1,100,HASH(ID,83)) <= 30 THEN 1 WHEN UNIFORM(1,100,HASH(ID,83)) <= 55 THEN 2
         WHEN UNIFORM(1,100,HASH(ID,83)) <= 75 THEN 3 WHEN UNIFORM(1,100,HASH(ID,83)) <= 88 THEN 4 ELSE 5 END AS DEPTH,
    ARRAY_CONSTRUCT('Biryani Blues','Pizza Point','Dosa Plaza','Burger Hub','Chai Sutta','Punjabi Dhaba','Wok Express','Healthy Bowl')[UNIFORM(0,7,HASH(ID,84))]::STRING AS RESTAURANT,
    ARRAY_CONSTRUCT('Delhi','Mumbai','Pune','Bengaluru','Agra')[UNIFORM(0,4,HASH(ID,85))]::STRING AS CITY,
    ARRAY_CONSTRUCT('Android','Android','iOS')[UNIFORM(0,2,HASH(ID,86))]::STRING AS OS
  FROM (SELECT ROW_NUMBER() OVER (ORDER BY SEQ4()) AS ID FROM TABLE(GENERATOR(ROWCOUNT => 8000)))
),
st AS (SELECT ROW_NUMBER() OVER (ORDER BY SEQ4()) AS STEP FROM TABLE(GENERATOR(ROWCOUNT => 5)))
SELECT OBJECT_CONSTRUCT(
    'event_id',    s.SESSION_NO * 10 + st.STEP,
    'session_id',  'S' || s.SESSION_NO,
    'user_id',     s.USER_ID,
    'event_ts',    TO_VARCHAR(DATEADD(SECOND, st.STEP * 90 + UNIFORM(0,60,HASH(s.SESSION_NO,st.STEP)), s.START_TS), 'YYYY-MM-DD HH24:MI:SS'),
    'event_type',  ARRAY_CONSTRUCT('app_open','search','view_restaurant','add_to_cart','order_placed')[st.STEP - 1]::STRING,
    'device',      OBJECT_CONSTRUCT('os', s.OS, 'app_version', IFF(s.OS = 'iOS', '5.2.0', '5.1.3')),
    'city',        s.CITY,
    'search_text', IFF(st.STEP = 2, ARRAY_CONSTRUCT('biryani','pizza','dosa','burger','paneer','momos')[UNIFORM(0,5,HASH(s.SESSION_NO,87))]::STRING, NULL),
    'restaurant',  IFF(st.STEP >= 3, s.RESTAURANT, NULL),
    'items',       IFF(st.STEP >= 4, ARRAY_CONSTRUCT_COMPACT(
                      OBJECT_CONSTRUCT('dish','Main course','qty',UNIFORM(1,3,HASH(s.SESSION_NO,88)),'price',UNIFORM(149,449,HASH(s.SESSION_NO,89))),
                      IFF(UNIFORM(1,100,HASH(s.SESSION_NO,90)) <= 60, OBJECT_CONSTRUCT('dish','Dessert','qty',1,'price',UNIFORM(79,199,HASH(s.SESSION_NO,91))), NULL),
                      IFF(UNIFORM(1,100,HASH(s.SESSION_NO,92)) <= 40, OBJECT_CONSTRUCT('dish','Beverage','qty',UNIFORM(1,2,HASH(s.SESSION_NO,93)),'price',60), NULL)),
                    NULL),
    'payment',     IFF(st.STEP = 5, OBJECT_CONSTRUCT(
                      'mode',   ARRAY_CONSTRUCT('UPI','CARD','COD')[UNIFORM(0,2,HASH(s.SESSION_NO,94))]::STRING,
                      'coupon', IFF(UNIFORM(1,100,HASH(s.SESSION_NO,95)) <= 30, 'FEAST50', NULL)), NULL)
  )::VARIANT AS RAW,
  'events_' || TO_VARCHAR(s.START_TS::DATE, 'YYYYMMDD') || '.json' AS FILE_NAME,
  '2025-11-01 01:00:00'::TIMESTAMP_NTZ AS LOADED_AT
FROM s JOIN st ON st.STEP <= s.DEPTH;

SELECT RAW FROM APP_EVENTS LIMIT 5;   -- keys with NULL values are left out of the JSON

Check that everything was built

SELECT 'ECOM orders' AS ITEM, COUNT(*) AS ROWS_FOUND FROM ECOM_DB.RAW.RAW_ORDERS
UNION ALL SELECT 'ECOM clean orders', COUNT(*) FROM ECOM_DB.PRACTICE.ORDERS
UNION ALL SELECT 'BANK transactions', COUNT(*) FROM BANK_DB.CORE.TRANSACTIONS
UNION ALL SELECT 'HR employees', COUNT(*) FROM HR_DB.CORE.EMPLOYEES
UNION ALL SELECT 'HR attendance', COUNT(*) FROM HR_DB.CORE.ATTENDANCE
UNION ALL SELECT 'FOOD events', COUNT(*) FROM FOOD_DB.APP.APP_EVENTS;

Exercise bank · Level 1 of 5

Level 1: first queries (exercises 1–40)

Level 1 builds confidence with single-table questions on the small RETAIL dataset from Module 1. A learner who can do all 40 without notes is ready for Level 2.

Run USE SCHEMA TRAINING_DB.RETAIL; first. Exercises 37–40 use PRACTICE_DB.SANDBOX from Module 3.

Exercise bank · Level 2 of 5

Level 2: functions and summaries (exercises 41–80)

Level 2 cleans and summarises data with functions, GROUP BY and HAVING. Exercises 41–60 use RETAIL; 61–80 use the bigger ECOM_DB from dataset D1, still one table at a time.

Exercise bank · Level 3 of 5

Level 3: combining tables (exercises 81–120)

Level 3 answers questions that need two or more tables. 81–100 use RETAIL, 101–113 use ECOM_DB.PRACTICE and 114–120 use HR_DB.CORE. When a query feels hard, write one join at a time and check the row count after each.

Exercise bank · Level 4 of 5

Level 4: analytics (exercises 121–160)

Level 4 is the work analysts do every day: rankings, running totals, period-over-period change, pivots and JSON. 121–138 use ECOM_DB.PRACTICE (mostly the SALES view), 139–147 HR_DB, 148–156 BANK_DB and 157–160 FOOD_DB.

Exercise bank · Level 5 of 5

Level 5: real-world scenarios (exercises 161–200)

Level 5 is the problems interviewers ask and projects actually hit: duplicates, fraud patterns, streaks and gaps, sessions, cohorts, slowly changing dimensions, pipelines and metadata automation. Each exercise names its database. Expect 15–30 minutes per question.

Project 1 of 5

E-commerce sales data warehouse

Build the complete warehouse for an online retailer: raw data in, cleaned staging, a star schema, reporting views, an automated daily load and secured access. This is the most common shape of a real Snowflake data-engineering project. Expected effort: 8–10 hours. Uses ECOM_DB from dataset D1.

The brief. Leadership wants one trusted source for 2025 sales. Source extracts land every night in ECOM_DB.RAW and contain duplicates, bad dates and messy text. Analysts must only see clean, modelled data, emails must be masked, and new data must flow in without anyone running scripts.

Target design

Step-by-step tasks

1. Profile the raw data. Write one query that reports every data problem with a count (re-use exercise 199). Decide a rule for each: drop, fix, or keep and flag.
2. Build staging. Clean customers (trim, lower-case emails, blank → NULL) and products (missing category → 'Uncategorised'). De-duplicate orders keeping the latest LOADED_AT, standardise STATUS, drop orders outside 2025, and map unknown customers to -1. Drop order lines with QTY ≤ 0 and log every dropped row in DQ_REJECTS with a reason.
3. Reconcile. Show raw count = staged count + rejected count + removed duplicates.
4. Build the mart. DIM_DATE for 2024–2026 with the Indian fiscal year (April–March), DIM_PRODUCT with a price band, DIM_CUSTOMER as SCD Type 2 with a -1 'Unknown customer' row, and FACT_SALES at order-line grain with gross, discount, net, cost and profit.
5. Build reporting views. V_MONTHLY_KPI (orders, customers, revenue, profit, AOV, cancel %, return %), V_CATEGORY_PERFORMANCE (revenue, margin and rank by quarter) and V_CUSTOMER_360 (one row per customer).
6. Automate the daily load. Create append-only streams on RAW_ORDERS and RAW_ORDER_ITEMS, a procedure that MERGEs new rows into staging, a procedure that MERGEs into FACT_SALES, and a two-task chain. Run the "next-day batch" script and prove the new orders reach FACT_SALES.
7. Track history. Change the city of 20 customers in RAW_CUSTOMERS, rebuild STG_CUSTOMERS and apply SCD Type 2 to DIM_CUSTOMER. Write a query that shows each sale against the customer's city at the time of the order.
8. Secure it. Create role ECOM_ANALYST with read access to MART only, and mask emails for everyone except SYSADMIN (masking needs Enterprise edition).
9. Hand over. Write a one-page runbook: objects, schedule, how to re-run a failed load, and how to check task history.

Done when: FACT_SALES row count reconciles to staging, the monthly KPI view matches exercise 200, the task history shows two successful runs, and ECOM_ANALYST cannot read RAW or STG.

Project 2 of 5

Banking: statements, balances and fraud monitoring

Build the analytics a retail bank's operations and risk teams use every morning: customer statements, daily balances, a rule-based fraud alert table, interest calculation and branch-level security. Expected effort: 6–8 hours. Uses BANK_DB from dataset D2.

The brief. The risk team wants every suspicious transaction from 2025 flagged with a rule name and severity. Operations wants month-end statements and a daily balance table for interest. Each branch manager may only see their own branch's accounts.

Step-by-step tasks

1. Account statement. For any account and month (use session variables), produce a statement: date, description, withdrawal, deposit, running balance. The first balance must start from the correct opening balance for that month.
2. Daily balance table. Build DAILY_BALANCE with one row per account per day of 2025 (365,000 rows), carrying the balance forward on days with no transactions.
3. Fraud rules engine. Create FRAUD_ALERTS and load it with five rules: (a) 5+ debits in 10 minutes — HIGH, (b) a debit above 10× the account's median debit — MEDIUM, (c) a transaction from outside India — MEDIUM, (d) a possible duplicate charge — LOW, (e) a debit on a DORMANT account — HIGH.
4. Alert triage. Summarise alerts by rule and severity, and list accounts hit by two or more different rules: these go to investigators first.
5. Monthly account summary. Per account per month: transactions, credits, debits, most-used channel and the merchant of the biggest debit.
6. Interest. Calculate quarterly interest for SAVINGS accounts at 3.5% a year on the daily closing balance (negative balances earn nothing).
7. Dormancy audit. An account is dormant if its last transaction is before 1 July 2025. Compare this rule with ACCOUNT_STATUS and list mismatches.
8. Branch security. With a row access policy, let role BRANCH_MGR_PUNE see only Pune-FC Road accounts, while SYSADMIN still sees all (Enterprise edition).

Done when: DAILY_BALANCE has 365,000 rows, FRAUD_ALERTS contains the 20 planted burst accounts under rule (a), and BRANCH_MGR_PUNE sees exactly one branch.

Project 3 of 5

HR and payroll analytics

Build the people-analytics layer an HR business partner asks for: one trusted employee view, a monthly dashboard table, attrition and pay-equity analysis, org-structure checks and attendance patterns. Expected effort: 5–7 hours. Uses HR_DB from dataset D3.

The brief. The CHRO's monthly review needs headcount, hires and exits by department, where and when people leave, whether pay is fair across levels and gender, whether any team has lost its manager, and who shows worrying absence patterns.

Step-by-step tasks

1. Employee master view. Create V_EMP_CURRENT: name, gender, level, department, location, city, manager name, hire and exit dates, Active/Exited, tenure in years and current salary.
2. Monthly dashboard. Build HR_MONTHLY_DASHBOARD with month, department, month-end headcount, hires and exits for 2025.
3. Attrition deep-dive. Exit % by tenure bucket (0–1, 1–3, 3–5, 5+ years) and first-year attrition by hiring year.
4. Pay analysis. Average hike % by level and year; compa-ratio (salary ÷ level median) for every active employee with a flag below 0.8 or above 1.2; gender gap by level (exercise 178).
5. Org health. Count people at each depth of the hierarchy; list managers with more than 25 active direct reports; list managers who have left but still have active reports.
6. Attendance. Monthly attendance % and WFH share; employees with 10+ absences where 60% or more fall on Mondays or Fridays.
7. Point-in-time payroll. Turn SALARY_HISTORY into periods (EFFECTIVE_FROM / EFFECTIVE_TO) and calculate the annual payroll by department as on 1 April 2024.

Done when: the dashboard has 84 rows (12 months × 7 departments), the orphan-team query returns the exited managers' teams, and the Monday/Friday query finds the planted employees (EMP_ID divisible by 97).

Project 4 of 5

Food-delivery app events: JSON pipeline and product analytics

Turn raw app clickstream JSON into typed tables and answer the product team's questions: where users drop off, how long ordering takes, which restaurants and dishes perform, and what happens to broken records. You also export and reload the data through a stage, the same path real JSON files take. Expected effort: 5–6 hours. Uses FOOD_DB from dataset D4.

The brief. The app team sends one JSON document per event. Product managers want a conversion funnel by city and phone OS, restaurant performance, popular searches and order-time patterns. Engineering wants bad records quarantined instead of breaking the load.

Step-by-step tasks

1. Explore the JSON. List every top-level key with its data type and how many events contain it. Count events per source file.
2. Typed event table. Create EVENTS_FLAT with one typed column per field, including nested device and payment fields.
3. Order lines. Create ORDER_LINES with one row per dish in every placed order (FLATTEN the items array).
4. Funnel by segment. Sessions, searches, add-to-carts and orders by city and OS, with overall conversion and cart-to-order %.
5. Sessions from behaviour. Rebuild sessions with a 30-minute inactivity rule and compare the count with the app's own session_id. Explain the difference.
6. Restaurant and menu insights. Orders, gross merchandise value (GMV), average basket and coupon % per restaurant; most popular search per city; an order heatmap of weekday × hour.
7. Bad records. Load four raw text lines (two broken) and classify each as OK, Invalid JSON or Bad field values; keep the bad ones in a QUARANTINE table.
8. File round trip. Export APP_EVENTS as JSON files to an internal stage, inspect the files with SQL, reload them with COPY INTO (capturing the file name), and show that a second COPY loads nothing.

Done when: EVENTS_FLAT has the same row count as APP_EVENTS, the funnel shows roughly 70% → 12% from open to order, and the reload count equals the original.

Project 5 of 5

Big data on the TPC-H sample: analytics and performance lab

Work on millions of rows that ship free with every Snowflake account, and learn what makes queries fast or slow: warehouse size, caching, pruning, and reading the Query Profile. Expected effort: 4–5 hours.

The data. SNOWFLAKE_SAMPLE_DATA.TPCH_SF1 is the industry-standard TPC-H benchmark at scale factor 1: LINEITEM about 6 million rows, ORDERS 1.5 million, CUSTOMER 150,000, plus PART, SUPPLIER, PARTSUPP, NATION and REGION. Orders run from 1992 to 1998. TPCH_SF10 is ten times bigger if you want a stretch exercise.

Step-by-step tasks

1. Find the data. Confirm SNOWFLAKE_SAMPLE_DATA exists (create it from the share if not), then list TPCH_SF1 tables with row counts and size using SHOW + RESULT_SCAN.
2. Business analytics. Revenue by region and year; top 3 customers per nation; late-delivery % by ship mode; year-over-year growth per market segment; the official TPC-H Query 1 (pricing summary report).
3. Warehouse sizing. With the result cache off, run TPC-H Q1 on X-Small and on Small. Compare elapsed time and estimate cost (Small uses twice the credits per hour).
4. Caching. Run the same query twice with the result cache on and compare.
5. Pruning. Filter LINEITEM on one month of L_SHIPDATE. In the Query Profile, note partitions scanned vs total. Then copy LINEITEM into your own table sorted by L_SHIPDATE and run the same filter again.
6. Column pruning. Compare bytes scanned for SELECT * versus selecting two columns.
7. Limits of shared data. Try to clone a sample table and read the error. Explain why shared data is read-only.
8. Cost check. Show credits used by TRAINING_WH in the last 7 days.

Done when: you can explain, with numbers from your own runs, how much faster Small was than X-Small, why the second identical query was instant, and how sorting the copy changed partitions scanned.