Backfill Missing Snapshots — Solution & ExplanationProblems / Backfill Missing Snapshots / EditorialApproach

Manufacture the grain. calendar × DISTINCT product_id is the dense grid the output requires. This step is what makes the problem tractable — window functions can only fill values *between existing rows*.

Attach the sparse facts. LEFT JOIN snapshots by (product, day); recorded days carry their stock, manufactured days carry NULL.

Carry state forward. last_value(stock IGNORE NULLS) over PARTITION BY product_id ORDER BY day ROWS UNBOUNDED PRECEDING → CURRENT ROW — the LOCF idiom. Pre-first-snapshot days have no non-NULL behind them and correctly remain NULL. (Under SQLFrame, express this via F.expr — the native F.last(..., ignorenulls=True) compiles to a FILTER clause DuckDB rejects.)

Key InsightDensification is a two-tool job: a spine join to *create* the missing observations, then a forward-fill to *value* them — and the choice of fill encodes real-world semantics. Fill counts with zero (nothing happened), fill *state* with the last observation (the shelf didn't empty because nobody counted it). Choosing wrong is invisible in the query and very visible in the inventory report. The composition also explains why snapshot tables in production warehouses are often stored sparse and densified on read: the spine is cheap to regenerate, the state semantics live in one window expression.Hints

You can't fill rows that don't exist — manufacture them first. CROSS JOIN the calendar with the distinct product list to get the complete (day, product) grid, then LEFT JOIN the sparse snapshots onto it.

After the join, missing days show NULL stock; last_value(stock IGNORE NULLS) over PARTITION BY product ORDER BY day with a running frame carries the last real snapshot into them.

Partitioning by product is the leak barrier: product B's leading NULLs (before its first snapshot) must not inherit product A's stock — and with IGNORE NULLS over an empty history, they stay NULL for free.

Zero-fill vs state-fill: counts on missing days are genuinely 0, but STOCK on an unrecorded day is 'whatever it was' — carrying forward, not zeroing, is the correct semantics here.

Solve Backfill Missing Snapshots yourself →