Skip to main content

๐Ÿงช Snowflake SQL Hands-On Demo

1. This guide demonstrates a Snowflake-compatible SQL example for creating and querying a customer_profile table using Indian-style sample data.โ€‹
2. demonstrates key Snowflake SQL capabilities including DDL, DML, and commonly used built-in functions.โ€‹

๐Ÿ—๏ธ 1. Create Tableโ€‹

CREATE OR REPLACE TABLE customer_profile (
customer_id INT,
full_name STRING,
email STRING,
phone_number STRING,
date_of_birth DATE,
city STRING,
state STRING,
account_balance NUMBER(10,2),
is_active BOOLEAN,
registered_at TIMESTAMP
);


๐Ÿงพ 2. Insert 10 Records - Method 1โ€‹

INSERT INTO customer_profile (customer_id, full_name, email, phone_number, date_of_birth, city, state, account_balance, is_active, registered_at) VALUES
(1, 'Ravi Kumar', 'ravi.kumar@example.in', '9876543210', '1990-05-15', 'Chennai', 'Tamil Nadu', 12000.50, TRUE, CURRENT_TIMESTAMP),
(2, 'Priya Sharma', 'priya.sharma@example.in', '9123456780', '1988-11-20', 'Delhi', 'Delhi', 8000.75, FALSE, CURRENT_TIMESTAMP),
(3, 'Amit Singh', 'amit.singh@example.in', '9988776655', '1992-07-10', 'Lucknow', 'Uttar Pradesh', 15000.00, TRUE, CURRENT_TIMESTAMP),
(4, 'Sneha Nair', 'sneha.nair@example.in', '9123409876', '1991-04-18', 'Kochi', 'Kerala', 13450.25, TRUE, CURRENT_TIMESTAMP),
(5, 'Vikram Reddy', 'vikram.reddy@example.in', '9001234567', '1985-09-30', 'Hyderabad', 'Telangana', 5600.90, FALSE, CURRENT_TIMESTAMP),
(6, 'Anjali Mehta', 'anjali.mehta@example.in', '9876004321', '1993-01-05', 'Ahmedabad', 'Gujarat', 10300.00, TRUE, CURRENT_TIMESTAMP),
(7, 'Rajesh Iyer', 'rajesh.iyer@example.in', '9012345678', '1987-12-12', 'Mumbai', 'Maharashtra', 7600.60, TRUE, CURRENT_TIMESTAMP),
(8, 'Kavita Das', 'kavita.das@example.in', '9345678910', '1994-08-22', 'Kolkata', 'West Bengal', 2000.00, FALSE, CURRENT_TIMESTAMP),
(9, 'Manish Tiwari', 'manish.tiwari@example.in', '9456123780', '1990-03-03', 'Bhopal', 'Madhya Pradesh', 9200.20, TRUE, CURRENT_TIMESTAMP),
(10,'Divya Jain', 'divya.jain@example.in', '8765432109', '1995-06-25', 'Jaipur', 'Rajasthan', 11800.00, TRUE, CURRENT_TIMESTAMP);

โž• 3. Insert Using INSERT INTO ... SELECT - Method 2โ€‹

-- Duplicate customer_id=1 as customer_id=11

INSERT INTO customer_profile
SELECT 11, full_name, email, phone_number, date_of_birth, city, state,
account_balance + 100, is_active, CURRENT_TIMESTAMP
FROM customer_profile
WHERE customer_id = 1;

๐Ÿ“˜ 4. Basic Select Queriesโ€‹

SELECT * FROM customer_profile;

SELECT full_name, city FROM customer_profile WHERE state = 'Tamil Nadu';

๐Ÿงพ 2. Aliasing (Column & Table) Notationโ€‹

SELECT cp.full_name AS name, cp.account_balance AS balance
FROM customer_profile cp;

๐Ÿ”Ž 3. Filtering: WHERE, IN, LIKE, BETWEEN, IS NULLโ€‹

-- Basic filtering
SELECT * FROM customer_profile WHERE is_active = TRUE;

-- LIKE pattern
SELECT * FROM customer_profile WHERE full_name LIKE 'A%';

-- IN clause
SELECT * FROM customer_profile WHERE city IN ('Mumbai', 'Chennai', 'Hyderabad');

-- BETWEEN clause
SELECT * FROM customer_profile WHERE account_balance BETWEEN 5000 AND 10000;

-- IS NULL
SELECT * FROM customer_profile WHERE email IS NULL;

๐Ÿ“Š 4. Aggregations & Groupingโ€‹

-- COUNT, AVG, MIN, MAX, SUM
SELECT state, COUNT(*) AS customer_count
FROM customer_profile
GROUP BY state;

-- HAVING clause
SELECT city, COUNT(*) AS customers
FROM customer_profile
GROUP BY city
HAVING COUNT(*) > 2;


๐Ÿ” 5. Subqueriesโ€‹

-- Inline subquery
SELECT * FROM (
SELECT full_name, state FROM customer_profile
) AS sub;

-- Scalar subquery
SELECT full_name
FROM customer_profile
WHERE account_balance > (
SELECT AVG(account_balance) FROM customer_profile
);

-- Customers With Above-Average Balance
SELECT *
FROM customer_profile
WHERE account_balance > (
SELECT AVG(account_balance) FROM customer_profile
);

๐Ÿ” 6. Correlated Subquery โ€“ Balance Compared to State Averageโ€‹

SELECT customer_id, full_name, state, account_balance,
CASE
WHEN account_balance > (
SELECT AVG(account_balance)
FROM customer_profile AS cp2
WHERE cp2.state = cp1.state
) THEN 'Above State Avg'
ELSE 'Below State Avg'
END AS balance_status
FROM customer_profile AS cp1;

๐Ÿ” 7. Pattern Matchingโ€‹

-- ILIKE for case-insensitive
SELECT * FROM customer_profile WHERE full_name ILIKE '%kumar%';

-- REGEXP
SELECT * FROM customer_profile
WHERE REGEXP_LIKE(email, '^[a-z]+\.nair@example\.in$');


๐Ÿ”„ 8. Set Operations: UNION, INTERSECT, EXCEPTโ€‹

-- UNION removes duplicates
SELECT city FROM customer_profile
UNION
SELECT city FROM archived_customers;

-- INTERSECT common values
SELECT city FROM customer_profile
INTERSECT
SELECT city FROM archived_customers;

-- EXCEPT (minus)
SELECT city FROM customer_profile
EXCEPT
SELECT city FROM archived_customers;


๐Ÿช„ 9. PIVOT & UNPIVOTโ€‹

-- Pivot: Convert rows to columns
SELECT * FROM (
SELECT state, is_active, customer_id FROM customer_profile
)
PIVOT (COUNT(customer_id) FOR is_active IN (TRUE, FALSE));


-- Unpivot: Convert columns to rows
SELECT * FROM (
SELECT customer_id, account_balance, is_active FROM customer_profile
)
UNPIVOT (val FOR col IN (account_balance, is_active));

๐Ÿ•ต๏ธโ€โ™‚๏ธ 10. LIMIT, ORDER BY, DISTINCTโ€‹

-- Limit rows
SELECT * FROM customer_profile LIMIT 5;

-- Order rows
SELECT * FROM customer_profile ORDER BY created_at DESC;

-- Unique records
SELECT DISTINCT city FROM customer_profile;


๐Ÿง  Conditional Expressions in Snowflake โ€“ Descriptions & Examples


1. CASEโ€‹

Description: Evaluates conditions in order and returns the result for the first WHEN condition that is true. Optional ELSE provides a fallback.

Example:

SELECT name,
CASE
WHEN age < 18 THEN 'Minor'
WHEN age BETWEEN 18 AND 60 THEN 'Adult'
ELSE 'Senior'
END AS age_group
FROM people;

2. IFโ€‹

Description: Simple conditional logic that returns one value if true, another if false. Use when only one condition check is needed.

Example:

SELECT salary,
IF(salary > 50000, 'High', 'Low') AS salary_band
FROM employees;

3. IFFโ€‹

Description: Snowflake-specific equivalent to IF, but with cleaner syntax. Often preferred for performance and readability.

Example:

SELECT is_verified,
IFF(is_verified, 'Verified', 'Unverified') AS status
FROM users;

4. DECODEโ€‹

Description: Oracle-style function that maps a column's values to result values like a switch-case. Good for direct value comparisons.

Example:

SELECT city,
DECODE(city,
'Chennai', 'South India',
'Delhi', 'North India',
'Mumbai', 'West India',
'Other') AS region
FROM branches;

5. NULLIFโ€‹

Description: Compares two values and returns NULL if they are equal, otherwise returns the first value. Useful to eliminate certain values.

Example:

SELECT order_id,
NULLIF(discount, 0) AS discount_applied
FROM orders;

6. COALESCEโ€‹

Description: Returns the first non-null expression among its arguments. Useful for handling fallback/default values.

Example:

SELECT user_id,
COALESCE(primary_phone, secondary_phone, 'N/A') AS contact_number
FROM customer_contact;

7. TRY_CAST / TRY_TO_<TYPE>โ€‹

Description: Attempts to convert a value to the specified type. Returns NULL instead of throwing an error if conversion fails.

Example:

SELECT raw_salary,
TRY_TO_NUMBER(raw_salary) AS clean_salary
FROM payroll_data;

8. Boolean Logic in SELECTโ€‹

Description: You can directly use boolean expressions that return TRUE, FALSE, or NULL.

Example:

SELECT user_name,
(login_count > 5) AS is_engaged
FROM login_stats;

9. Nested CASE for Advanced Logicโ€‹

Description: Embeds a CASE inside another CASE for more complex decision trees.

Example:

SELECT name,
CASE
WHEN age > 60 THEN
CASE
WHEN city = 'Chennai' THEN 'Senior-South'
ELSE 'Senior-Other'
END
ELSE 'Non-Senior'
END AS category
FROM citizens;

10. IF / IFF in ORDER BY, GROUP BY, etc.โ€‹

Example:

-- Group by condition
SELECT
IFF(active, 'Active', 'Inactive') AS user_status,
COUNT(*)
FROM users
GROUP BY user_status;

-- Order with condition
SELECT name, age
FROM students
ORDER BY CASE WHEN age < 18 THEN 1 ELSE 2 END;

๐Ÿงช DESCRIBE / SHOW / EXPLAIN Queriesโ€‹


---
-- Describe structure
DESCRIBE TABLE customer_profile;

-- Show objects
SHOW TABLES;
SHOW SCHEMAS;

-- Explain plan
EXPLAIN SELECT * FROM customer_profile WHERE account_balance > 5000;