How to fill missing days before a running total in Snowflake (date spine)

A sales table has rows only for the days with sales. Day-by-day calculations, though, assume one row per day: a running total skips the empty days, and LAG compares a day with the previous day that had sales, not with the day before. A date spine, a list of every day in the period, fixes both.

A small example

Orders on four days in the first week of May 2026, and nothing on 3, 5, 6 and 8 May.

CREATE TEMP TABLE Sales (OrderDate DATE, Amount NUMBER(10,2));
INSERT INTO Sales VALUES
  ('2026-05-01', 40), ('2026-05-02', 120),
  ('2026-05-04', 60), ('2026-05-07', 30);

Without a spine

SELECT OrderDate, Amount,
       SUM(Amount) OVER (ORDER BY OrderDate
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal,
       LAG(Amount) OVER (ORDER BY OrderDate) AS PreviousDay
FROM Sales
ORDER BY OrderDate;

The totals are right, but the report is not: four rows for eight days, a chart that draws a straight line from 4 to 7 May, and a PreviousDay for 7 May that is really 4 May.

With a spine

WITH Params AS (
  SELECT '2026-05-01'::DATE AS StartDate,
         '2026-05-08'::DATE AS EndDate   -- CURRENT_DATE() in a live report
),
Calendar AS (   -- one row per day of the report
  SELECT DATEADD(day, f.value::INT, p.StartDate) AS CalendarDate
  FROM Params p,
       LATERAL FLATTEN(input => ARRAY_GENERATE_RANGE(
         0, DATEDIFF(day, p.StartDate, p.EndDate) + 1)) f
),
Daily AS (
  SELECT OrderDate, SUM(Amount) AS Amount
  FROM Sales
  GROUP BY OrderDate
),
Filled AS (
  SELECT c.CalendarDate, COALESCE(d.Amount, 0) AS Amount
  FROM Calendar c
  LEFT JOIN Daily d ON d.OrderDate = c.CalendarDate
)
SELECT CalendarDate, Amount,
       SUM(Amount) OVER (ORDER BY CalendarDate
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal,
       LAG(Amount) OVER (ORDER BY CalendarDate) AS PreviousDay
FROM Filled
ORDER BY CalendarDate;

The running total now stays flat on the empty days, and the day before 7 May is 6 May. Step by step:

• Calendar builds the spine. ARRAY_GENERATE_RANGE(0, 8) returns the numbers 0 to 7, FLATTEN turns them into rows, and DATEADD turns each number into a day from 1 to 8 May. The range comes from Params, not from the data, so 8 May is in the report although it has no orders.
• Daily reduces the sales to one row per day. Joining the orders directly would give a day with two orders two rows. If OrderDate is a timestamp, group by OrderDate::DATE.
• Filled keeps every day of the spine and gives the days without orders a 0.

With GENERATOR

Another common way to build a spine uses GENERATOR:

SELECT DATEADD(day, ROW_NUMBER() OVER (ORDER BY SEQ4()) - 1, '2026-01-01'::DATE) AS CalendarDate
FROM TABLE(GENERATOR(ROWCOUNT => 365));

ROWCOUNT must be a constant, so the number of days is fixed in the query. Generate more rows than you need and filter them with WHERE if the end of the period varies. Number the rows with ROW_NUMBER(): the Snowflake documentation warns that sequences generated this way can have gaps, and a gap would be a missing day.

A permanent calendar table

If many reports need a spine, create it once:

CREATE TABLE Calendar AS
SELECT DATEADD(day, ROW_NUMBER() OVER (ORDER BY SEQ4()) - 1, '2000-01-01'::DATE) AS CalendarDate
FROM TABLE(GENERATOR(ROWCOUNT => 18263));   -- 2000-01-01 to 2049-12-31

Then add the columns your reports need, such as the weekday, the ISO week, the month start or the fiscal year, and join every report to it. The same spine also fills the missing months before a rolling 12-month total or a running total that resets every year.