Microsoft Certified: Fabric Analytics Engineer AssociateExplore and analyze data (15-20%)Hard
A data engineer is working with a Delta Lake table named `sensor_readings` in Microsoft Fabric. The table contains columns `device_id` (string), `timestamp` (timestamp), and `temperature` (double). The engineer needs to calculate the average temperature for each device, considering only the readings from the *previous 30 minutes* relative to the current reading. Which PySpark window function clause should be used to define this time-based window?
- A.rowsBetween(F.lit(-30), F.lit(0))
- B.rangeBetween(F.lit(-30), F.lit(0))
- C.rowsBetween(-30, 0)
- D.rangeBetween(F.expr("INTERVAL -30 MINUTES"), F.lit(0))
Show answer & explanationAnswer & explanation
Correct answer: D. .rangeBetween(F.expr("INTERVAL -30 MINUTES"), F.lit(0))
To define a time-based window in PySpark, `rangeBetween` is used with interval expressions. `F.expr("INTERVAL -30 MINUTES")` correctly specifies a time offset, while `F.lit(0)` represents the current row's timestamp. `rowsBetween` is for row-based offsets, not time intervals.
Why the other options are wrong
- A. `rowsBetween` is incorrect for time-based windows, and `F.lit()` for interval values is not the correct way to specify a time interval.
- B. `rangeBetween` is correct for time-based windows, but `F.lit(-30)` is not the correct way to specify a time interval; `F.expr("INTERVAL -30 MINUTES")` is required.
- C. `rowsBetween` defines a window based on a number of rows, not a time interval. The argument `-30` would mean 30 rows prior, not 30 minutes.
PySpark rangeBetween() for Time Windows
The `rangeBetween()` window function clause in PySpark defines a window based on a range of values, often used for time-based intervals relative to the current row's value in an ordered column.
- Requires an ordered column (e.g., timestamp or numeric ID).
- Arguments specify the lower and upper bounds relative to the current row's value.
- For time intervals, use `F.expr("INTERVAL ...")` to define offsets.
- Often used with `F.lit(0)` for the current row's value.
Memory trick: Range between times, rows between numbers, always remember your order!