SnowPro Associate: Platform Question 182
Single answer○ LIMITA data analyst is validating a new sales dashboard in Snowflake and wants to quickly inspect only the 20 highest-value orders from the ORDERS table before sharing the results with business users. The analyst runs this query:
SELECT order_id, customer_id, order_total FROM orders LIMIT 20;
The analyst notices that the results are not consistently the highest-value orders between executions. Which change should be made to return the correct 20 rows reliably?
- A
Add ORDER BY order_total DESC before LIMIT 20
- B
Replace LIMIT 20 with SAMPLE (20 ROWS)
- C
Add DISTINCT so Snowflake returns the top 20 unique orders
- D
Use LIMIT 20 only after creating a clustering key on ORDER_TOTAL
Show answer and explanation
Correct answer: A
Explanation
In Snowflake, LIMIT caps the number of rows returned by a query, but by itself it does not imply any ordering. If a user needs the top N rows according to a business metric, the query should include ORDER BY followed by LIMIT. In this scenario, ORDER BY order_total DESC LIMIT 20 is the correct pattern for a deterministic top-20 result set. This aligns with SQL best practices and Snowflake documentation: result ordering is only guaranteed when ORDER BY is specified. Features like SAMPLE and DISTINCT serve different purposes, and physical storage optimizations such as clustering do not guarantee presentation order in query results.
- A. Correct.
Correct. LIMIT restricts the number of rows returned, but without ORDER BY, Snowflake does not guarantee which 20 rows are returned. Adding ORDER BY order_total DESC ensures the rows are sorted from highest to lowest order value before applying LIMIT 20, which reliably returns the 20 highest-value orders.
- B. Incorrect.
Incorrect. SAMPLE returns a random subset of rows, not the highest-value rows. Someone might choose this option because both SAMPLE and LIMIT reduce the number of rows returned, but SAMPLE is intended for random sampling rather than top-N queries.
- C. Incorrect.
Incorrect. DISTINCT removes duplicate rows from the result set based on the selected columns, but it does not sort data or identify the highest-value orders. This reflects a common misconception that DISTINCT can help select 'best' or 'top' rows.
- D. Incorrect.
Incorrect. Clustering can improve pruning and query performance in some cases, but it does not determine row order in query results. LIMIT without ORDER BY is still nondeterministic regarding which rows are returned, even if a clustering key exists.