Forward-Fill Sensor Readings — Solution & ExplanationProblems / Forward-Fill Sensor Readings / EditorialApproach

Recognize the pattern. Forward-fill (LOCF, "last observation carried forward") means every row's answer depends on prior rows in the same series — a window function over PARTITION BY sensor_id ORDER BY reading_time.

Use last_value with IGNORE NULLS. Over the running frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, last_value(value) alone is useless — the last row of that frame is the current row, so it returns the current value, NULL and all. last_value(value IGNORE NULLS) skips NULLs and returns the most recent real reading.

Leading NULLs stay NULL. Before a sensor's first non-NULL reading the frame contains no non-NULL value at all, so IGNORE NULLS yields NULL — exactly the required behavior, no special-casing needed.

Order by sensor_id, reading_time.

In pandas this is df.groupby("sensor_id")["value"].ffill(); the grouping is what stops one sensor's last reading from leaking into the next sensor's leading gap.In the PySpark variant, F.last("value", ignorenulls=True).over(window) is the canonical Spark idiom, but SQLFrame compiles it to a FILTER (WHERE ...) clause that DuckDB's window parser rejects — the reliable route is F.expr with the native DuckDB window expression.Key InsightThe whole problem hinges on the window frame. With the default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) or a full-partition frame, last_value either returns the current row or the partition's final value — both wrong. The combination running-frame + IGNORE NULLS turns last_value into "most recent known value as of this row", which is the precise definition of forward-fill. Partitioning provides the second guarantee: sensor 2's leading NULLs must not inherit sensor 1's final reading.Hints

This is 'last observation carried forward': for each row, look backwards through the same sensor's history and grab the most recent non-NULL value. That backwards look is a window function with a running frame.

Plain last_value(value) over a running frame just returns the current row's value — NULL included. The IGNORE NULLS modifier makes it skip NULLs and reach back to the last real reading.

The frame must be ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, partitioned by sensor_id and ordered by reading_time. Leading NULLs fall out correctly for free: with no non-NULL value behind them, last_value(... IGNORE NULLS) returns NULL.

PySpark's F.last(col, ignorenulls=True).over(...) is the textbook answer, but SQLFrame translates it into a FILTER clause DuckDB's window syntax rejects — use F.expr('last_value(value IGNORE NULLS) OVER (...)') with the DuckDB expression instead.

Solve Forward-Fill Sensor Readings yourself →