Microsoft Certified: Fabric Analytics Engineer AssociateExplore and analyze data (15-20%)Medium
A data scientist is performing exploratory data analysis on a large dataset in a Spark Delta table named `sensor_readings`. They need to identify the top 5 most frequent sensor IDs that have reported readings above a certain threshold (e.g., 100 degrees Celsius) in the last 24 hours. Which sequence of Spark SQL clauses should be used to achieve this?
- AFROM, SELECT, WHERE, GROUP BY, ORDER BY, LIMIT
- BSELECT, WHERE, FROM, GROUP BY, ORDER BY, TOP
- CSELECT, FROM, HAVING, GROUP BY, ORDER BY, LIMIT
- DSELECT, FROM, WHERE, GROUP BY, ORDER BY, LIMIT
Show answer & explanationAnswer & explanation
Correct answer: D. SELECT, FROM, WHERE, GROUP BY, ORDER BY, LIMIT
The standard logical processing order for a Spark SQL query that filters individual rows (WHERE), groups them (GROUP BY), aggregates (implicit in SELECT for GROUP BY), orders the groups (ORDER BY), and then limits the results (LIMIT) is SELECT, FROM, WHERE, GROUP BY, ORDER BY, LIMIT. The WHERE clause filters rows *before* grouping, which is necessary for the initial threshold filter.
Why the other options are wrong
- A. While FROM is often written first, the logical processing order for Spark SQL typically starts with SELECT (defining what's returned), then FROM, then WHERE for row filtering.
- B. The `TOP` clause is T-SQL specific, not standard Spark SQL. Also, FROM typically precedes WHERE.
- C. HAVING filters groups *after* aggregation, but the initial threshold filter needs to be applied to individual rows *before* grouping, making WHERE unsuitable here for the initial filter.
Spark SQL Query Logical Order
The conceptual sequence in which Spark SQL processes clauses in a query, which dictates where filtering, grouping, and ordering operations occur.
- FROM specifies the data source.
- WHERE filters individual rows before grouping.
- GROUP BY aggregates rows into groups.
- HAVING filters groups after aggregation.
- SELECT specifies output columns/expressions.
- ORDER BY sorts the final result set.
Memory trick: From Where Groups Have Selected Orders Limited.