Filling Gaps with a Date Spine

Concepts covered: Outer Joins, Null Defaulting, Cross Joins, Window Frames, Row Offsets

How the gap arrives

The request almost never uses the words "missing days." It arrives as "plot daily bookings for each host," "why does day-over-day growth spike every Monday," or "the rolling average looks too high for quiet markets." Underneath each one sits a sparse fact table: a day with no bookings writes no row, and every calculation built on it counts rows while the question counts days. The interviewer is checking whether you name that mismatch before touching the window function.

Put the dates on the left

The fix changes which side of the join owns the dates. A spine (a row for every calendar day, from a calendar table or a generator) sits on the left, the daily facts LEFT JOIN onto it, and COALESCE turns each unmatched day into a zero. The animation runs it over 4 days of a single market, where 2024-03-10 had no bookings.

SELECT c.cal_date, COALESCE(d.bookings, 0) AS bookings
FROM calendar c
LEFT JOIN daily_bookings d ON d.booking_date = c.cal_date
ORDER BY c.cal_date

On the 3 sparse rows the average is 11.3 bookings a day; across the 4 dense rows it is 8.5, a 33% overstatement from a single empty day. LAG on 03-11 compares 14 with 9 and reports +56%, when the day before had 0. Every one of those errors goes away once 03-10 has a row.

Zero versus unknown

The interviewer waits for this distinction. COALESCE(bookings, 0) asserts that nothing happened, and that assertion holds only if the source delivered data for that day. If ingestion failed on 03-10, the true value is unknown: filling 0 publishes a crash that never happened, and leaving the day out publishes an inflated average. The complete answer joins a load-status record into the spine and fills zero only where the pipeline confirms the day landed.

SELECT m.market,
       s.cal_date,
       CASE WHEN l.load_date IS NULL THEN NULL
            ELSE COALESCE(d.bookings, 0)
       END AS bookings
FROM dim_market m
JOIN dim_calendar s
  ON s.cal_date >= m.launch_date
 AND s.cal_date <  CURRENT_DATE
LEFT JOIN daily_bookings d
  ON d.market = m.market
 AND d.booking_date = s.cal_date
LEFT JOIN pipeline_loads l
  ON l.load_date = s.cal_date
 AND l.status = 'complete';

Size the spine before building it

A per-entity spine is a cross join, so its size is entities times days. Plotting 40,000 hosts over 2 years from a single global start date builds 29.2 million rows before any fact joins, most of them for hosts who hadn't listed yet. Bounding each entity by its own active range, from launch to churn, keeps the spine proportional to the data, and it is also the correct answer: a host who joined in June has no zero-booking days in January.

Keep facts sparse in storage and densify at read time, in the final model that feeds the chart. A fact table stored at host-by-day grain pays storage and scan cost for rows that carry no information. For the dates themselves, Postgres has generate_series, BigQuery GENERATE_DATE_ARRAY with UNNEST, Spark sequence with explode, and other engines a recursive CTE. A persisted calendar table beats all of them, because holiday logic needs one anyway.

• 36,525

The follow-up: can you skip the spine?

It comes often. A RANGE frame measures the window in calendar time instead of rows, so this rolling sum is correct on sparse rows without any fill:

SELECT booking_date,
       SUM(bookings) OVER (
         ORDER BY booking_date
         RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW
       ) AS bookings_7d
FROM daily_bookings;

Then name what it leaves broken. The output still has no row for 03-10, so the chart still skips it, and a 7-day average needs a fixed denominator of 7 rather than AVG over whatever rows land in the frame. Support varies too: Postgres accepts interval offsets in RANGE from version 11, while BigQuery requires a numeric ORDER BY such as UNIX_DATE(booking_date). In the room, say that the frame fixes the window and the spine fixes the output, and a dashboard usually needs both.

About this lesson

This section is part of the Date Arithmetic: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.