SnowPro Associate: Platform Question 181
Single answer○ LIMITA data analyst is investigating recent order activity in a Snowflake table named ORDERS that contains billions of rows. She wants to quickly preview the 100 most recent orders before building a dashboard. Which query will reliably return the 100 most recent rows?
- A
SELECT * FROM ORDERS LIMIT 100;
- B
SELECT * FROM ORDERS ORDER BY ORDER_TIMESTAMP DESC LIMIT 100;
- C
SELECT TOP 100 * FROM ORDERS;
- D
SELECT * FROM ORDERS SAMPLE (100 ROWS) ORDER BY ORDER_TIMESTAMP DESC;
Show answer and explanation
Correct answer: B
Explanation
In Snowflake, LIMIT reduces the number of rows returned, but it does not define which rows are returned unless it is paired with ORDER BY. To get the most recent records, the query must first sort by the timestamp column in descending order and then apply LIMIT. This is a common real-world pattern when previewing recent activity or validating incoming data. Snowflake documentation and SQL best practices emphasize that result order is not guaranteed without ORDER BY, so using LIMIT alone is insufficient when a specific ordering is required.
- A. Incorrect.
Incorrect. LIMIT 100 restricts the result set to 100 rows, but without an ORDER BY clause Snowflake does not guarantee which 100 rows are returned. A common misconception is that the database will return the newest or first-loaded rows by default, but relational result sets are unordered unless explicitly sorted.
- B. Correct.
Correct. ORDER BY ORDER_TIMESTAMP DESC sorts the rows from newest to oldest, and LIMIT 100 then restricts the output to the first 100 rows from that ordered set. This is the correct way to reliably return the 100 most recent orders in Snowflake.
- C. Incorrect.
Incorrect. TOP 100 is not the standard syntax used in Snowflake SQL for limiting rows in this context. Snowflake commonly uses LIMIT or FETCH to restrict the number of rows returned. Even if someone is familiar with TOP from other database platforms, it is not the expected approach here for Snowflake certification knowledge.
- D. Incorrect.
Incorrect. SAMPLE (100 ROWS) returns an arbitrary sample of 100 rows, not specifically the most recent 100. Sorting a sampled subset afterward only orders those sampled rows, which means many newer rows could be excluded before the ORDER BY is applied. This reflects a misunderstanding of how sampling differs from limiting ordered results.