Snowflake is a fully ANSI-SQL-compliant cloud data platform, so your standard SQL knowledge transfers directly. On top of that, it ships rich Snowflake-specific SQL extensions: VARIANT-based semi-structured data processing, Time Travel, and more.
This article delivers hands-on Snowflake SQL practice questions, organized by difficulty. From basic SELECT/JOIN through window functions, CTEs, and Snowflake-specific FLATTEN/VARIANT, the progression is designed to grow your skill step by step. It doubles as solid SnowPro exam prep.
Snowflake is fully ANSI SQL compliant, so the SQL skills you built on other RDBMSs carry over directly. Every standard construct — SELECT, JOIN, GROUP BY, window functions, subqueries — is supported. Migrations from Oracle, PostgreSQL, MySQL, and so on are smooth from a SQL-syntax standpoint.
On top of standard SQL, Snowflake ships the following extensions. These are also high-frequency topics on the SnowPro exams.
Snowflake's query engine automatically prunes by micro-partition, reading only the data you actually need. Setting CLUSTER BY (clustering keys) appropriately dramatically improves scan efficiency on large tables. The Result Cache also returns results for repeated identical queries instantly, without consuming any warehouse.
First up: questions on the fundamentals of Snowflake SQL. Check your grasp of standard SQL constructs like SELECT, JOIN, GROUP BY, and HAVING.
Basic SQL - SELECT / JOIN
問題 1
Which option correctly describes the result of the following Snowflake SQL query? SELECT o.order_id, c.customer_name, o.total_amount FROM orders o INNER JOIN customers c ON o.customer_id = c.customer_id WHERE o.total_amount > 10000 ORDER BY o.total_amount DESC LIMIT 5;
正解: B
The query INNER JOINs orders and customers, then the WHERE clause filters with total_amount > 10000. ORDER BY o.total_amount DESC sorts in descending order by amount, and LIMIT 5 returns only the top 5. A is wrong because it ignores the amount filter. C is wrong because this is a row-level filter, not a GROUP BY aggregation. D is wrong because the join is INNER JOIN, not LEFT JOIN.
Basic SQL - GROUP BY / HAVING
問題 2
Which option correctly describes the following Snowflake SQL query? SELECT department, COUNT(*) AS employee_count, AVG(salary) AS avg_salary FROM employees GROUP BY department HAVING COUNT(*) >= 5 ORDER BY avg_salary DESC;
正解: B
GROUP BY department aggregates per department, and HAVING COUNT(*) >= 5 keeps only departments with at least 5 employees. HAVING filters the aggregated results after GROUP BY — its timing differs from WHERE. ORDER BY avg_salary DESC sorts by average salary in descending order. A is wrong because it ignores the HAVING filter. C confuses the COUNT condition with a salary condition. D is wrong because ORDER BY uses avg_salary, not the employee count.
Window functions are a high-frequency, must-know topic in Snowflake SQL. ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM OVER, and friends are the backbone of analytic SQL — and they show up reliably on the SnowPro exams.
Window Functions - ROW_NUMBER / RANK
問題 3
For the following Snowflake SQL query, which option correctly describes the difference between ROW_NUMBER() and RANK()? SELECT employee_id, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_num FROM employees;
正解: B
ROW_NUMBER() always assigns a unique sequential number (1, 2, 3, 4 ...) even when values tie — which row gets which number is non-deterministic. RANK() gives tied rows the same rank and skips the next rank by that amount (e.g., 1, 2, 2, 4). DENSE_RANK() does not skip and produces consecutive numbers (e.g., 1, 2, 2, 3). A is wrong whenever salary has NULLs or ties. D is wrong because both functions number within the PARTITION BY scope.
CTEs (Common Table Expressions) and recursive queries are advanced SQL features used for complex transformations and tree traversal. Snowflake fully supports recursive CTEs, so you can lean on them for hierarchical data.
Advanced SQL - CTE / Recursive Query
問題 4
Which option correctly describes the following Snowflake recursive CTE query? WITH RECURSIVE org_tree AS ( SELECT employee_id, manager_id, employee_name, 1 AS level FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.employee_id, e.manager_id, e.employee_name, ot.level + 1 FROM employees e INNER JOIN org_tree ot ON e.manager_id = ot.employee_id ) SELECT * FROM org_tree ORDER BY level, employee_name;
正解: B
A recursive CTE consists of an anchor member (the seed) UNION ALL with a recursive member. The anchor selects rows where manager_id IS NULL (the top of the org, e.g., CEO) as level = 1, and the recursive member walks down to direct reports via an INNER JOIN, incrementing level by 1 each step. The result flattens the entire org tree into a table where every employee carries their hierarchy level. In Snowflake the default maximum recursion depth for recursive CTEs is 100.
The FLATTEN function and the VARIANT type sit at the core of Snowflake's headline feature: semi-structured data processing. With SQL alone, you can flexibly expand and query nested data like JSON, Avro, and Parquet. These are mandatory SnowPro exam topics.
Snowflake-Specific - FLATTEN / VARIANT
問題 5
An events table stores a VARIANT column event_data with the following JSON structure: {"user_id": 101, "tags": ["premium", "active", "japan"]} Which query correctly expands each tag into its own row?
正解: A
To expand a nested VARIANT array into rows in Snowflake, use the LATERAL FLATTEN function. FLATTEN(input => event_data:tags) expands each element of the tags array into its own row, and the expanded value is referenced via the value pseudo-column. The colon notation (event_data:user_id) is the VARIANT key-access syntax. SPLIT (B) is a string-splitting function and does not work on VARIANT arrays. UNNEST (C) is not supported in Snowflake. JSON_EXTRACT (D) is not Snowflake syntax.
The logical execution order in SQL is FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT. Internalizing this order lets you reason through most questions cleanly — including the difference between WHERE and HAVING, and why SELECT-clause aliases are usable in ORDER BY.
Snowflake's VARIANT type supports three access notations: colon (data:key), bracket (data['key']), and dot (data.key). Array access uses indexes, e.g., data:array[0]. Questions often combine these with explicit type casts (data:key::STRING), so make sure you have them down cold.
A window function has three parts: the function (ROW_NUMBER, RANK, etc.), PARTITION BY (the grouping), and ORDER BY (the ordering). Omit PARTITION BY and the entire result becomes a single window; omit ORDER BY and the order within the window becomes undefined. Once you understand how these three parts combine, most window function questions fall into place.
Time Travel uses two keywords: AT (data as of the specified point) and BEFORE (data immediately before the specified point). If you memorize the three patterns — timestamp (AT(TIMESTAMP =>'...')), offset (AT(OFFSET => -60*5)), and statement ID (BEFORE(STATEMENT => '...')) — you can reliably answer any Time Travel question.
How does Snowflake SQL differ from standard SQL?
Snowflake is ANSI SQL compliant, so your standard SQL knowledge transfers directly. On top of that, it ships Snowflake-specific extensions such as FLATTEN (for semi-structured data), the VARIANT type, Time Travel (AT/BEFORE clauses), the QUALIFY clause, and LATERAL joins. SnowPro exams focus heavily on these Snowflake-specific features.
How much of the SnowPro exam is direct SQL?
Direct SQL questions make up roughly 10-15% of the SnowPro Core exam, but many questions on warehouses, stages, and tasks assume SQL fluency, so SQL skill indirectly drives your pass/fail outcome. SnowPro Advanced: Data Engineer dedicates an even larger share to practical SQL.
Can SQL beginners tackle Snowflake questions?
Yes. If you know the basics of SELECT, WHERE, JOIN, and GROUP BY, you can work through the NicheeLab question bank step by step. Each explanation walks through the syntax and behavior so you can build skill while you study. Save window functions and CTEs for the intermediate level and beyond.
Solve more Snowflake SQL questions
Detailed explanations help you grow your Snowflake SQL skills efficiently
Try free questions →Related Snowflake Certification Articles
Practice with certification-focused question sets
Try free practice questionsNicheeLab Editorial Team
NicheeLab editorial team focused on data engineering and cloud certification learning. Content is structured around practical study needs and official exam domains.
Snowflake Certifications: All 11 Exams Explained (2026)
Every SnowPro certification — Associate, Core, Specialty, Ad...
Snowflake Exam Difficulty Ranking: All 11 Certs Compared (2026)
All 11 SnowPro exams ranked by difficulty with study-time es...
Snowflake Study Guide: Fastest Pass Route by Exam (2026)
How to pass SnowPro certifications efficiently — official ma...
SnowPro Core (COF-C03): Complete Exam Guide (2026)
Pass the SnowPro Core exam — six domains, scope, sample ques...
SnowPro Associate Platform (SOL-C01): Complete Guide (2026)
The entry-level SnowPro Associate exam — scope, weighting, s...