SnowPro Associate: Platform Question 184
Single answer○ LIMITA data analyst is validating a newly loaded SALES table that contains hundreds of millions of rows. They want to quickly inspect only a few rows in Snowflake before writing additional transformations. Which query is the best choice to return a small subset of rows with the least amount of unnecessary sorting work?
- A
SELECT * FROM SALES LIMIT 20;
- B
SELECT * FROM SALES ORDER BY SALE_DATE LIMIT 20;
- C
SELECT TOP 20 * FROM SALES ORDER BY SALE_DATE;
- D
SELECT * FROM SALES SAMPLE (20 ROWS) ORDER BY SALE_DATE;
Show answer and explanation
Correct answer: A
Explanation
For a quick data preview in Snowflake, LIMIT is commonly used to restrict the number of rows returned by a query. If no specific ordering is required, using LIMIT by itself is typically the best choice because it avoids unnecessary sorting overhead. By contrast, ORDER BY ... LIMIT is appropriate only when the user needs the top rows according to a defined sort order. Snowflake documentation notes that LIMIT restricts the number of rows returned, and ORDER BY determines result ordering; combining them is useful for ranked outputs but not for a basic preview. TOP is valid Snowflake syntax, but in this scenario the added ORDER BY still makes it less efficient than a simple LIMIT-only query.
- A. Correct.
Correct. Using LIMIT 20 returns only a small number of rows and avoids the extra work of sorting the result set. For a quick inspection when no particular row order is required, this is the most efficient and practical option.
- B. Incorrect.
Incorrect. This query does return 20 rows, but ORDER BY requires Snowflake to sort rows by SALE_DATE before applying the limit. If the analyst only wants a quick preview and does not need ordered results, the sort adds unnecessary processing.
- C. Incorrect.
Incorrect. Snowflake does support the TOP
syntax as an alternative to LIMIT, but this query still includes ORDER BY SALE_DATE, which introduces unnecessary sorting work for a simple preview. The issue is not syntax validity but the extra sort step. - D. Incorrect.
Incorrect. SAMPLE (20 ROWS) is used to retrieve a random sample, not simply the first small subset for quick inspection. Adding ORDER BY SALE_DATE also introduces sorting work, making it less appropriate than a simple LIMIT query when randomness and ordering are not required.