Microsoft Certified: Fabric Analytics Engineer AssociateExplore and analyze data (15-20%)Hard
A data scientist is performing exploratory data analysis on a large dataset stored in a Spark Delta table. They want to calculate the 90th percentile of a `response_time` column to understand typical high-end performance. Which Spark SQL aggregate function should they use?
- APERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY response_time)
- BQUARTILE(response_time, 3)
- CAPPROX_PERCENTILE(response_time, 0.9)
- DPERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY response_time)
Show answer & explanationAnswer & explanation
Correct answer: C. APPROX_PERCENTILE(response_time, 0.9)
`APPROX_PERCENTILE` is commonly used in Spark SQL for calculating approximate percentiles over large datasets, which is often sufficient for exploratory analysis and more performant than exact percentile calculations. The syntax requires the column and the percentile value (0.0 to 1.0).
Why the other options are wrong
- A. PERCENTILE_CONT is a standard SQL function for continuous percentiles, but `WITHIN GROUP` syntax is often not directly supported or less performant in Spark for large data without specific configurations.
- B. QUARTILE is not a standard Spark SQL aggregate function; quartiles are typically derived from percentiles (e.g., 25th, 50th, 75th percentiles).
- D. PERCENTILE_DISC is for discrete percentiles and also often not directly supported or performant in Spark for large data.
Spark SQL APPROX_PERCENTILE
The `APPROX_PERCENTILE(col, percentage)` function in Spark SQL calculates an approximate percentile of a numeric column, offering better performance for large datasets compared to exact methods.
- Provides an approximate result, not exact.
- More performant for large-scale data.
- Takes a column and a percentile value (0.0 to 1.0).
- Useful for exploratory analysis and metrics.
Memory trick: Approximate is fast, exact can be slow.