Forward-fill readings per device

PySpark data engineering interview problem. Difficulty: intermediate. Pattern: Window Functions. About 16 minutes. Part of the Pro drill bank.

Replace NULL readings with the last non-null reading of the same device, in time order. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.

Sensors sometimes miss a reading, and the value is NULL. A common fix is to forward fill: copy the last known value forward until a new value arrives. The table readings has device, ts (a timestamp as text) and reading. The rows are not in time order. Add a column filled: the most recent reading that is not NULL, for the same device, at or before the row's ts. If a device has no earlier non-NULL reading, filled stays NULL. Keep all the original columns. The editor creates the table readings and ends with result = readings. Change the last line to build your answer. Use F.last(..., ignorenulls=True) over a window.

Requirements

• Use a window partitioned by device and ordered by ts.
• Set the frame from the start of the group to the current row.
• Add filled with F.last("reading", ignorenulls=True).over(w).
• Keep device, ts, reading and add filled, then store the DataFrame in result.

Constraints

• Each device is filled on its own. A value never moves from one device to another.
• ts is a text timestamp that sorts correctly in time order.
• Only earlier or equal rows can fill a row. The future is not used.

Examples

Input: readings device | ts | reading d1 | 2024-01-01 09:20:00 | NULL d1 | 2024-01-01 09:00:00 | 10.5 d1 | 2024-01-01 09:30:00 | 11 d1 | 2024-01-01 09:10:00 | NULL d2 | 2024-01-01 09:00:00 | NULL d2 | 2024-01-01 09:10:00 | 7.5 d2 | 2024-01-01 09:20:00 | NULL d3 | 2024-01-01 09:00:00 | NULL Output: device | ts | reading | filled d1 | 2024-01-01 09:00:00 | 10.5 | 10.5 d1 | 2024-01-01 09:10:00 | NULL | 10.5 d1 | 2024-01-01 09:20:00 | NULL | 10.5 d1 | 2024-01-01 09:30:00 | 11 | 11 d2 | 2024-01-01 09:00:00 | NULL | NULL d2 | 2024-01-01 09:10:00 | 7.5 | 7.5 d2 | 2024-01-01 09:20:00 | NULL | 7.5 d3 | 2024-01-01 09:00:00 | NULL | NULL d1 at 09:10 and 09:20 take 10.5, d2 at 09:20 takes 7.5, and the leading NULL rows (d2 at 09:00 and all of d3) stay NULL.

Topics: lakebench, pyspark, last, ignorenulls, window.

More PySpark interview questions · All interview problems · Learn data engineering