Filling gaps with fill() and interpolate in Flux
A rooftop pyranometer fleet on forty commercial buildings streams global horizontal irradiance into a solar_irradiance_raw bucket every five seconds, and a one-minute rollup feeds both an operations dashboard and a downstream energy-yield calculation. The moment a cumulus edge slides across a roof, or a LoRaWAN backhaul stutters, whole one-minute windows arrive empty. The dashboard line breaks into disconnected fragments, and the yield integrator — which multiplies irradiance by panel area and elapsed time — receives null where it expects a number and silently drops the interval from the daily total. The reflex is to reach for a fill function, but there are three distinct tools with three different truth-claims, and picking the wrong one fabricates data that looks perfectly plausible on a chart while corrupting every downstream number. This page is a decision guide for that choice, sitting under the density-auditing discipline of fallback chains for missing data: once a window is known to be sparse, you still have to decide what value, if any, to publish for it.
Prerequisites
Solution walkthrough
1. Surface the gaps before you can fill them
The single most common mistake is running fill() and finding it does nothing. fill() only rewrites null values in rows that already exist; it cannot conjure a row for a window that produced no data. Whether a missing window becomes a fillable null row is decided upstream, by the createEmpty argument of aggregateWindow. With createEmpty: false (the default in most rollup examples) the empty window is simply absent, and every later fill operates on a table that has no idea the gap exists. Turn it on so the sparse minute becomes an explicit null-valued row.
from(bucket: "solar_irradiance_raw")
|> range(start: -6h)
|> filter(fn: (r) => r._measurement == "irradiance" and r._field == "ghi")
|> aggregateWindow(every: 1m, fn: mean, createEmpty: true)
|> yield(name: "with_null_windows")
createEmpty: true emits one row per one-minute boundary across the whole range, inserting _value: null wherever no raw points landed. Only now is the gap a first-class row that the next stage can act on. This is also why the empty-window flag matters for arithmetic correctness downstream — the same reasoning that governs decimal contracts in precision mapping and rounding strategies applies here: a missing row and a zero row are different assertions, and you must choose one deliberately.
2. Hold the last reading with fill(usePrevious: true)
fill(usePrevious: true) carries the most recent non-null value forward across each null. Use it when the physical quantity genuinely holds between observations — a thermostat setpoint, a valve state, a battery state-of-charge that barely moves in a minute. Irradiance during a brief radio dropout on a clear morning is a reasonable candidate: the sun did not move, so last-known is a defensible estimate for a sixty-second hole.
from(bucket: "solar_irradiance_raw")
|> range(start: -6h)
|> filter(fn: (r) => r._measurement == "irradiance" and r._field == "ghi")
|> aggregateWindow(every: 1m, fn: mean, createEmpty: true)
|> fill(usePrevious: true)
|> to(bucket: "irradiance_1m", org: "energy-ops")
The critical parameter is usePrevious: true versus a static value. usePrevious propagates the last observed reading, so it adapts to the signal’s current level; a fixed value (next step’s alternative) ignores it. Note the failure it cannot handle: if the very first rows of a table are null, there is no previous value to carry, and those leading rows stay null. Guard against that by widening the range start earlier than your reporting window so a real reading precedes the boundary you care about.
3. Draw a straight line with interpolate.linear
When the quantity ramps rather than holds, a held value produces an ugly staircase that misstates everything in between. interpolate.linear fits a straight line between the bracketing non-null points and evaluates it at a fixed interval. For a value at time $t$ between known samples $(t_0, v_0)$ and $(t_1, v_1)$:
$$ v(t) = v_0 + (v_1 - v_0),\frac{t - t_0}{t_1 - t_0} $$
Import the package and apply it after the windowing stage.
import "interpolate"
from(bucket: "solar_irradiance_raw")
|> range(start: -6h)
|> filter(fn: (r) => r._measurement == "irradiance" and r._field == "ghi")
|> aggregateWindow(every: 1m, fn: mean, createEmpty: true)
|> interpolate.linear(every: 1m)
|> to(bucket: "irradiance_1m", org: "energy-ops")
every: 1m must match the window cadence so interpolated points land exactly on the reporting grid. Unlike fill, interpolate.linear needs a real value on both sides of a gap — it will not extrapolate past the last known point, so a gap that runs to the end of the range is left null. That restraint is a feature: it refuses to invent a trend it cannot bracket.
4. Know when not to fill — flag instead of fabricate
There is a fourth option that is frequently the correct one: publish nothing but a marker. If irradiance genuinely dropped because a storm cell parked over the array for six minutes, then fill(usePrevious) reports a bright sky that was not there, and interpolate.linear invents a smooth ramp straight through a real weather event — both corrupt the energy integral by overstating generation. When the quantity is undefined while unobserved, or when the gap is long enough that any estimate is a guess, leave the value null and attach a quality tag so downstream consumers can exclude it explicitly.
from(bucket: "solar_irradiance_raw")
|> range(start: -6h)
|> filter(fn: (r) => r._measurement == "irradiance" and r._field == "ghi")
|> aggregateWindow(every: 1m, fn: mean, createEmpty: true)
|> map(fn: (r) => ({r with quality: if exists r._value then "observed" else "gap"}))
|> to(bucket: "irradiance_1m", org: "energy-ops")
The exists r._value predicate distinguishes a measured minute from an empty one, and the quality tag makes the distinction queryable. A dashboard can now draw gaps as gaps, and the yield calculator can decide — as a documented policy, not an accident — whether to skip gap rows or apply a conservative estimate. This kind of explicit routing is exactly what the broader downsampling and aggregation pipeline treats as a first-class pipeline state rather than an afterthought.
Gotchas and edge cases
fill() cannot resurrect a row that was never created. If your rollup uses createEmpty: false, there is no null row for the empty minute, so fill(usePrevious: true) returns the identical sparse table and you conclude, wrongly, that fill is broken. The empty windows must exist as rows first. Always pair a fill or interpolate stage with createEmpty: true in the preceding aggregateWindow, and verify the row count matches the number of boundaries in your range.
usePrevious carries a stale value across an unbounded outage. fill(usePrevious: true) has no sense of time — it will happily propagate a single 08:59 reading across a two-hour comms failure, so at 11:00 your dashboard still shows the morning’s irradiance under a sensor that has been dark for hours. Cap the staleness: either restrict the fill to short gaps by filtering on a computed age since the last real point, or fall back to a gap flag once a window count threshold is exceeded, so a held value never outlives its credibility.
Interpolation smooths over the very events you monitor for. A straight line between a pre-gap and post-gap sample erases whatever spike, dropout, or transient happened in between — for irradiance that means a passing cloud shadow vanishes, and for anomaly detection it means the outage itself becomes invisible. Never interpolate a signal whose short-term excursions are the thing you care about; reserve interpolate.linear for slowly varying quantities where a linear segment is a faithful model of the missing interval.
Verification
After writing the filled series, confirm two things: that no unexpected null rows survived, and that the fill did not distort the aggregate you rely on. Count residual nulls in the destination window.
from(bucket: "irradiance_1m")
|> range(start: -6h)
|> filter(fn: (r) => r._measurement == "irradiance" and r._field == "ghi")
|> filter(fn: (r) => not exists r._value)
|> count()
|> yield(name: "residual_nulls_should_be_zero")
A zero result means every window carries a value from an observed reading, a hold, or an interpolation; a non-zero result points at leading gaps (no previous value) or open-ended gaps (nothing to interpolate toward) that your strategy left untouched. Cross-check by summing the quality tag counts — the ratio of gap to observed rows is the honest measure of how much of the published series is real versus reconstructed, and it belongs on the same health panel as your fill logic.
FAQ
Does fill() change the timestamps of my data?
No. fill() only rewrites null values in the _value column of rows that already exist; the _time of each row is fixed by the preceding aggregateWindow boundary. It is createEmpty: true that determines which timestamps exist in the first place, and fill simply populates their values.
Can I use interpolate.linear and fill(usePrevious) together?
Yes, and it is a common belt-and-braces pattern. Run interpolate.linear first to ramp across interior gaps that have values on both sides, then apply fill(usePrevious: true) to catch any trailing or leading gaps that interpolation cannot bracket. Order matters — interpolate before the hold — so that genuine ramps are drawn before a flat carry-forward is used as the last resort.
Should I fill in the raw bucket or only in the rollup?
Fill in the rollup, never the raw bucket. Raw storage should remain a faithful record of what the sensors actually reported, gaps included, so you can always recompute with a different policy. Filling is a presentation and downstream-math decision that belongs in the aggregate tier, applied as data flows out toward dashboards and calculators.
Related
- Fallback Chains for Missing Data — the density-audit layer that decides whether a window is trustworthy before you decide how to fill it.
- Precision Mapping & Rounding Strategies — deterministic value handling for the aggregates you publish, complementing the null-handling choices here.
- Downsampling & Aggregation Pipeline Design — the end-to-end lifecycle these gap-filling stages plug into.
Up one level: Fallback Chains for Missing Data