Snowflake SQL Interview Questions – Quick Notes

SNOWFLAKE SQL INTERVIEW QUESTIONS – QUICK NOTES

1. What is Snowflake? Snowflake is a cloud-based data warehouse that separates compute and storage, allowing both to scale independently.

Key Features:
• Cloud-native architecture
• Separation of Compute & Storage
• Virtual Warehouses
• Time Travel
• Zero-Copy Cloning
• Secure Data Sharing
• Automatic Scaling

---

1. Explain Snowflake Architecture.

Snowflake has three layers:

1. Database Storage Layer

   • Stores compressed data.

2. Compute Layer

   • Virtual Warehouses execute SQL queries.

3. Cloud Services Layer

   • Handles authentication, metadata, query optimization, and security.

---

1. What is a Virtual Warehouse?

A Virtual Warehouse is a compute cluster that executes SQL queries. Multiple warehouses can access the same data simultaneously.

---

1. Difference between CTE and Subquery

CTE

• Easier to read
• Can be reused
• Supports recursive queries

Subquery

• Less readable for complex queries
• Usually used once
• Cannot be reused easily

---

1. Types of Joins

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL OUTER JOIN
• CROSS JOIN
• SELF JOIN

---

1. Window Functions

Common functions:
• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• LAG()
• LEAD()
• SUM() OVER()
• AVG() OVER()

Example:

SELECT employee_id,
salary,
ROW_NUMBER() OVER(ORDER BY salary DESC) AS rn
FROM employees;

---

1. QUALIFY Clause (Snowflake Specific)

Filters rows after applying window functions.

Example:

SELECT *
FROM employees
QUALIFY ROW_NUMBER() OVER
(PARTITION BY department ORDER BY salary DESC)=1;

---

1. Difference between ROW_NUMBER(), RANK(), and DENSE_RANK()

ROW_NUMBER()

• Assigns unique numbers.
• No duplicates.

RANK()

• Same rank for ties.
• Skips rank numbers.

DENSE_RANK()

• Same rank for ties.
• Does not skip numbers.

---

1. Find Second Highest Salary

SELECT salary
FROM employees
QUALIFY DENSE_RANK() OVER(ORDER BY salary DESC)=2;

---

1. What is Time Travel?

Allows recovery of deleted or modified data within the configured retention period.

---

1. What is Zero Copy Cloning?

Creates an instant copy of tables, schemas, or databases without duplicating physical storage.

---

1. Temporary vs Transient vs Permanent Tables

Temporary Table

• Exists only for the current session.

Transient Table

• No Fail-safe.
• Lower storage cost.

Permanent Table

• Supports Time Travel and Fail-safe.

---

1. Common SQL Interview Questions

2. Find duplicate records.

3. Remove duplicate records.

4. Find the second highest salary.

5. Find the top 3 salaries in each department.

6. Calculate running totals.

7. Calculate rolling averages.

8. Find employees earning above department average.

9. Get the latest record for each customer.

10. Count customers by month.

11. Find missing dates.

---

1. Performance Tips

• Avoid SELECT *.
• Filter data as early as possible.
• Use appropriate warehouse size.
• Use clustering keys when necessary.
• Use QUALIFY instead of nested subqueries where applicable.

---

1. Frequently Asked Snowflake Questions

• What are Micro-partitions?
• Explain Snowpipe.
• What are Streams and Tasks?
• What is Fail-safe?
• Difference between COPY INTO and Snowpipe.
• What is VARIANT data type?
• Explain FLATTEN().
• Explain RBAC in Snowflake.
• How do you optimize slow queries?
• What is Query Profile?

---

Interview Tips

✔ Master Joins, CTEs, Window Functions, and QUALIFY.

✔ Practice writing SQL without an editor.

✔ Learn Snowflake-specific features:

• Time Travel
• Zero-Copy Cloning
• Streams
• Tasks
• Snowpipe
• VARIANT
• FLATTEN
• Virtual Warehouses

✔ Explain your SQL logic clearly during the interview.