Sessions and the cross-midnight backfill

Sessions are where "rebuild a day at a time" earns its complications. A session — a run of one device's events with no 30-minute gap — is defined by relationships between rows, and those relationships don't respect your partitions:

• A session that starts at 23:47 and keeps going belongs to one day's partition but is built from two days' events.
• Worse: nothing in the definition stops a session from going on forever. One kiosk display, background sync, or misbehaving client that never pauses for 30 minutes produces a session with no end — a row that is never final, and that only a full-history scan can rebuild.

So any sessionizer that you want to maintain incrementally has to cut long sessions somewhere. That's not a smelt rule; it's arithmetic. The design question is what the cut is anchored to. This page builds the answer that keeps partitions independent — anchor it to the clock — and closes with the other answer, which costs more than it looks like it should.

The cut rule

silver.sessions keeps the ordinary 30-minute gap rule (plus a platform change starting a new session) and adds one deadline on top. The gap rule ends a session when the user pauses; the deadline exists so a session that never pauses still ends: a session dies at the first midnight it fails to reach into, where "reaching into" a day means having an event in its first 30 minutes. A session that genuinely crosses a midnight always reaches into the new day (its gaps are under 30 minutes, so some event lands within 30 minutes of the boundary), which means the deadline only ever fires at the next midnight after that. Two consequences, and everything on this page leans on them: a session can cross at most one midnight, so every session spans at most two calendar days — and whether it must end is computable from a timestamp alone, with no memory of the session's history.

The model

---
materialization: table
refresh: incremental
grain: partition
timeseries:
  event_time_column: session_start_date
  partition_column: session_start_date
  granularity: day
---
WITH sessionized AS (
    SELECT
        device_id,
        event_ts,
        event_date,
        platform,
        utm_campaign,
        session_start_ts,
        CAST(session_start_ts AS DATE) AS session_start_date
    FROM smelt.functions.sessionize(
        source => smelt.silver.events_deduped,
        partition_col => device_id,
        ts_col => event_ts,
        platform_col => platform
    )
)
SELECT
    CONCAT(CAST(device_id AS VARCHAR), '-', CAST(session_start_ts AS VARCHAR)) AS session_id,
    device_id,
    session_start_ts,
    session_start_date,
    MIN(event_ts) AS session_start,
    MAX(event_ts) AS session_end,
    COUNT(*) AS event_count,
    ANY_VALUE(platform) AS platform,
    ARG_MAX(utm_campaign, -epoch_us(event_ts)) FILTER (
        WHERE utm_campaign IS NOT NULL
            AND event_ts <= session_start_ts + INTERVAL '5 minutes'
    ) AS utm_campaign
FROM sessionized
WHERE event_date
    BETWEEN session_start_date
        AND session_start_date + INTERVAL '1 day'
GROUP BY device_id, session_start_ts, session_start_date
HAVING MAX(event_ts) - MIN(event_ts) < INTERVAL '2 days' -- max_session_span: explicit, checkable cap assertion

Three things in here deserve names:

• The sessionization is a reusable function. smelt.functions.sessionize (source) assigns each event its session's start timestamp using window functions. (The source => syntax is smelt's named-argument form for function calls — not standard SQL.) Its RANGE BETWEEN INTERVAL '2 days' PRECEDING frames are not just implementation: smelt reads them as the function's declared reach into the past. Functions expand transparently into the caller, so the planner analyzes the real SQL, not an opaque call. (More: functions guide.)
• The WHERE filter is another declaration. event_date BETWEEN session_start_date AND session_start_date + INTERVAL '1 day' states the two-calendar-day rule in column terms: a session's events live on its start day or the day after, never further. The HAVING clause restates the same cap as a per-row assertion the emitted SQL enforces. And, same move as the lateness filter on the previous page, smelt derives windows from it — this time in the opposite direction.
• The attribution expression is just SQL. ARG_MAX(utm_campaign, -epoch_us(event_ts)) FILTER (WHERE …) picks the earliest non-NULL campaign within the session's first five minutes — negating the timestamp turns "value at the maximum" into "value at the earliest event." It plays no role in the maintenance derivation; it's here so the pipeline computes something a marketer would recognize.

The write window inverts the filter

For events_parsed on the previous page, day D's output depended on earlier source days, so the read widened backward. A session table skews the other way: this table is partitioned by session_start_date, and an event arriving on day D can extend a session that started on day D−1. New data for day D can change yesterday's partition.

smelt gets that by inverting the declared filter: if a session's events reach at most one day past its start, then day D's events reach back to sessions starting on D−1. A run over [D, D+1) must therefore rewrite partitions [D−1, D+1) — yesterday's and today's — and it does:

-- trigger: Backfill
BEGIN
  DELETE FROM main.silver_sessions WHERE session_start_date >= '2026-04-09' AND session_start_date < '2026-04-11'
  INSERT INTO main.silver_sessions SELECT * FROM (
  -- … model SELECT body (see the full SQL below) …

  ) AS _smelt_output_clamp WHERE session_start_date >= '2026-04-09' AND session_start_date < '2026-04-11'
COMMIT

-- trigger: NewData { source: "silver.events_deduped" }
BEGIN
  DELETE FROM main.silver_sessions WHERE session_start_date >= '2026-04-09' AND session_start_date < '2026-04-11'
  INSERT INTO main.silver_sessions SELECT * FROM (
  -- … model SELECT body (see the full SQL below) …

  ) AS _smelt_output_clamp WHERE session_start_date >= '2026-04-09' AND session_start_date < '2026-04-11'
COMMIT

Full emitted SQL — smelt explain silver.sessions --show-sql --period 2026-04-10..2026-04-11

-- trigger: Backfill
BEGIN
  DELETE FROM main.silver_sessions WHERE session_start_date >= '2026-04-09' AND session_start_date < '2026-04-11'
  INSERT INTO main.silver_sessions SELECT * FROM (
  WITH sessionized AS (
      SELECT
          device_id,
          event_ts,
          event_date,
          platform,
          utm_campaign,
          session_start_ts,
          CAST(session_start_ts AS DATE) AS session_start_date
      FROM ((
      WITH _marked AS (
          SELECT
              *,
              LAG(event_ts) OVER (
                  PARTITION BY device_id ORDER BY event_ts
                  RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW  -- max_lookback
              ) AS _prev_ts,
              LAG(platform
      ) OVER (
                  PARTITION BY device_id ORDER BY event_ts
                  RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW  -- max_lookback
              ) AS _prev_platform
          FROM (SELECT * FROM main.silver_events_deduped WHERE first_seen_date >= '2026-04-07' AND first_seen_date < '2026-04-11') AS source
      ),
      _bounded AS (
          SELECT
              *,
              CASE
                  WHEN _prev_ts IS NULL THEN event_ts
                  WHEN epoch_us(event_ts) - epoch_us(_prev_ts) > 30 * 60 * 1000000 THEN event_ts
                  WHEN _prev_platform != platform
       THEN event_ts
                  ELSE NULL
              END AS _boundary_ts
          FROM _marked
      ),
      _candidate AS (
          SELECT
              *,
              MAX(_boundary_ts) OVER (
                  PARTITION BY device_id ORDER BY event_ts
                  RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW  -- max_lookback
              ) AS _candidate_root_ts
          FROM _bounded
      ),
      _deadlined AS (
          SELECT
              *,
              CASE
                  WHEN _candidate_root_ts IS NULL THEN NULL
                  WHEN CAST(_candidate_root_ts AS TIME) < TIME '00:30:00'
                      THEN CAST(CAST(_candidate_root_ts AS DATE) AS TIMESTAMP) + INTERVAL '1 day'
                  ELSE CAST(CAST(_candidate_root_ts AS DATE) AS TIMESTAMP) + INTERVAL '2 days'
              END AS _deadline
          FROM _candidate
      )
      SELECT
          *,
          CASE
              WHEN _candidate_root_ts IS NOT NULL AND event_ts < _deadline THEN _candidate_root_ts
              ELSE MIN(event_ts) OVER (PARTITION BY device_id, CAST(event_ts AS DATE))
          END AS session_start_ts
      FROM _deadlined
  )) AS __smelt_t2529)
  SELECT
      CONCAT(CAST(device_id AS VARCHAR), '-', CAST(session_start_ts AS VARCHAR)) AS session_id,
      device_id,
      session_start_ts,
      session_start_date,
      MIN(event_ts) AS session_start,
      MAX(event_ts) AS session_end,
      COUNT(*) AS event_count,
      ANY_VALUE(platform) AS platform,
      ARG_MAX(utm_campaign, -epoch_us(event_ts)) FILTER (
          WHERE utm_campaign IS NOT NULL
              AND event_ts <= session_start_ts + INTERVAL '5 minutes'
      ) AS utm_campaign
  FROM sessionized
  WHERE event_date
      BETWEEN session_start_date
          AND session_start_date + INTERVAL '1 day'
  GROUP BY device_id, session_start_ts, session_start_date
  HAVING MAX(event_ts) - MIN(event_ts) < INTERVAL '2 days' -- max_session_span: explicit, checkable cap assertion

  ) AS _smelt_output_clamp WHERE session_start_date >= '2026-04-09' AND session_start_date < '2026-04-11'
COMMIT

-- trigger: NewData { source: "silver.events_deduped" }
BEGIN
  DELETE FROM main.silver_sessions WHERE session_start_date >= '2026-04-09' AND session_start_date < '2026-04-11'
  INSERT INTO main.silver_sessions SELECT * FROM (
  WITH sessionized AS (
      SELECT
          device_id,
          event_ts,
          event_date,
          platform,
          utm_campaign,
          session_start_ts,
          CAST(session_start_ts AS DATE) AS session_start_date
      FROM ((
      WITH _marked AS (
          SELECT
              *,
              LAG(event_ts) OVER (
                  PARTITION BY device_id ORDER BY event_ts
                  RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW  -- max_lookback
              ) AS _prev_ts,
              LAG(platform
      ) OVER (
                  PARTITION BY device_id ORDER BY event_ts
                  RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW  -- max_lookback
              ) AS _prev_platform
          FROM (SELECT * FROM main.silver_events_deduped WHERE first_seen_date >= '2026-04-07' AND first_seen_date < '2026-04-11') AS source
      ),
      _bounded AS (
          SELECT
              *,
              CASE
                  WHEN _prev_ts IS NULL THEN event_ts
                  WHEN epoch_us(event_ts) - epoch_us(_prev_ts) > 30 * 60 * 1000000 THEN event_ts
                  WHEN _prev_platform != platform
       THEN event_ts
                  ELSE NULL
              END AS _boundary_ts
          FROM _marked
      ),
      _candidate AS (
          SELECT
              *,
              MAX(_boundary_ts) OVER (
                  PARTITION BY device_id ORDER BY event_ts
                  RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW  -- max_lookback
              ) AS _candidate_root_ts
          FROM _bounded
      ),
      _deadlined AS (
          SELECT
              *,
              CASE
                  WHEN _candidate_root_ts IS NULL THEN NULL
                  WHEN CAST(_candidate_root_ts AS TIME) < TIME '00:30:00'
                      THEN CAST(CAST(_candidate_root_ts AS DATE) AS TIMESTAMP) + INTERVAL '1 day'
                  ELSE CAST(CAST(_candidate_root_ts AS DATE) AS TIMESTAMP) + INTERVAL '2 days'
              END AS _deadline
          FROM _candidate
      )
      SELECT
          *,
          CASE
              WHEN _candidate_root_ts IS NOT NULL AND event_ts < _deadline THEN _candidate_root_ts
              ELSE MIN(event_ts) OVER (PARTITION BY device_id, CAST(event_ts AS DATE))
          END AS session_start_ts
      FROM _deadlined
  )) AS __smelt_t2529)
  SELECT
      CONCAT(CAST(device_id AS VARCHAR), '-', CAST(session_start_ts AS VARCHAR)) AS session_id,
      device_id,
      session_start_ts,
      session_start_date,
      MIN(event_ts) AS session_start,
      MAX(event_ts) AS session_end,
      COUNT(*) AS event_count,
      ANY_VALUE(platform) AS platform,
      ARG_MAX(utm_campaign, -epoch_us(event_ts)) FILTER (
          WHERE utm_campaign IS NOT NULL
              AND event_ts <= session_start_ts + INTERVAL '5 minutes'
      ) AS utm_campaign
  FROM sessionized
  WHERE event_date
      BETWEEN session_start_date
          AND session_start_date + INTERVAL '1 day'
  GROUP BY device_id, session_start_ts, session_start_date
  HAVING MAX(event_ts) - MIN(event_ts) < INTERVAL '2 days' -- max_session_span: explicit, checkable cap assertion

  ) AS _smelt_output_clamp WHERE session_start_date >= '2026-04-09' AND session_start_date < '2026-04-11'
COMMIT

Read the frame: the run window was one day, the DELETE covers session_start_date in [2026-04-09, 2026-04-11), and the events read widened to cover both the session span and the sessionizer's two-day lookback. Every bound traces to something declared in SQL you can point at. (The output also lists a second statement group under a NewData trigger — the same work, run when upstream data changes rather than when you ask for a window; the changing-things page puts that to use.)

The payoff: a midnight-straddling session, handled by a one-day run

In the generated dataset there's a device with an event at 2026-05-03 23:47 and its next at 2026-05-04 00:03 — a 16-minute gap, one session, started on May 3rd. Suppose you've already built everything through May 3rd, and today's job runs May 4th:

-- trigger: Backfill
BEGIN
  DELETE FROM main.silver_sessions WHERE session_start_date >= '2026-05-03' AND session_start_date < '2026-05-05'
  INSERT INTO main.silver_sessions SELECT * FROM (
  -- … model SELECT body (see the full SQL below) …

  ) AS _smelt_output_clamp WHERE session_start_date >= '2026-05-03' AND session_start_date < '2026-05-05'
COMMIT

-- trigger: NewData { source: "silver.events_deduped" }
BEGIN
  DELETE FROM main.silver_sessions WHERE session_start_date >= '2026-05-03' AND session_start_date < '2026-05-05'
  INSERT INTO main.silver_sessions SELECT * FROM (
  -- … model SELECT body (see the full SQL below) …

  ) AS _smelt_output_clamp WHERE session_start_date >= '2026-05-03' AND session_start_date < '2026-05-05'
COMMIT

The May 4th run rewrites the May 3rd partition, folding the midnight event into the existing session's row instead of minting a fragment session at 00:03. This is the bug class — sessions split at partition boundaries, session counts inflated — that hand-built day-at-a-time session jobs get wrong by default, and that you otherwise fix by remembering to over-rebuild ("always redo yesterday too") in a place far from the session logic. Here the over-rebuild is derived, minimal, and proven against the same filter the query enforces.

An end-to-end test in the repo (per_partition_equivalence.rs) pins the property all of this serves: building this table day by day, in any order, produces results identical to building it from scratch in one pass.

A different cut, a different execution shape

The clock-anchored deadline is a design choice, and a reasonable person might prefer the other one: "a session ends roughly two days after it started," measured from the session's own start. The full example builds that too, as silver.sessions_chained, with the same gap rule and attribution; the two tables differ only in where the cap's timing comes from.

That one change transforms the execution. "When did the session I'm continuing start?" cannot be answered from any bounded window of new events — for a long-lived session, the start could be arbitrarily far back. The model must consult its own prior output, and smelt, seeing the self-reference, proves a different property: the table still converges, but only if its partitions are built strictly in time order. smelt enforces that ordering itself (backfills run oldest-first, one partition at a time, never in parallel), and — as the changing-things page shows — the self-reference also opts the table out of automatic change propagation. The deep dive walks the model and its emitted plan.

What makes the choice worth a page of its own is how differently the three plausible designs treat one pathological input — a device emitting an event every 29 minutes for nine days straight, so the gap rule never fires and only the cap decides:

Design	Result on the never-idle device	Execution
Clock-anchored cap (silver.sessions)	9 sessions (~1/day)	partitions independent
Root-anchored cap (silver.sessions_chained)	5 sessions (~1/2 days)	strictly ordered, sequential
Cap inside the window frame only (this example's original design, since replaced)	~50 single-event sessions per day	"independent," and wrong

(If you build streaming pipelines: the clock-anchored table is roughly what session_window can express; the root-anchored one is loosely the shape you'd otherwise reach for stateful processing for.)

The third row is the cautionary one. A cap enforced only by a window frame's reach looks partition-independent — no self-reference, nothing for an analyzer to object to — but under the never-idle input the frame simply stops containing what it needs, and session counts inflate 50×. Session count is a headline metric. The difference between the first two designs and the third is exactly the difference between a bound that is true of the data and one that is merely present in the code.