Windows

TUMBLE, HOP and SESSION, the window_start output column, and the malformed calls the planner refuses before it binds.

An event-time window turns an unbounded stream into a finite group. Ubik has three, all driven by the watermark rather than by wall-clock time: TUMBLE, HOP and SESSION.

Every example below runs against this file.

cat > events.ndjson <<'EOF'
{"merchant":"acme","amount":250,"event_time":"2026-07-18 10:00:03"}
{"merchant":"globex","amount":42,"event_time":"2026-07-18 10:00:07"}
{"merchant":"acme","amount":980,"event_time":"2026-07-18 10:01:12"}
{"merchant":"acme","amount":110,"event_time":"2026-07-18 10:01:48"}
{"merchant":"globex","amount":7,"event_time":"2026-07-18 10:02:20"}
EOF

The table name is the source basename, so this file is queried as events.

TUMBLE

TUMBLE(ts, INTERVAL '1 MINUTE') assigns each row to one fixed, non-overlapping bucket. A row belongs to exactly one window, so rows_out never exceeds the number of distinct groups.

ubik --from file://events.ndjson \
  "SELECT merchant,
          TUMBLE(event_time, INTERVAL 1 MINUTE) AS w,
          count(*) AS n,
          sum(amount) AS total
   FROM events
   GROUP BY merchant, TUMBLE(event_time, INTERVAL 1 MINUTE)"

{"merchant":"acme","w":"2026-07-18 10:00:00","n":1,"total":250}
{"merchant":"globex","w":"2026-07-18 10:00:00","n":1,"total":42}
{"merchant":"acme","w":"2026-07-18 10:01:00","n":2,"total":1090}
{"merchant":"globex","w":"2026-07-18 10:02:00","n":1,"total":7}

HOP(ts, slide, size) is the sliding window: a new window opens every slide, each one size wide. A row lands in every window that covers it, so a 30 second slide over a 1 minute size puts each row in two windows and roughly doubles the output.

ubik --from file://events.ndjson \
  "SELECT merchant,
          HOP(event_time, INTERVAL '30 SECOND', INTERVAL '1 MINUTE') AS w,
          count(*) AS n
   FROM events
   GROUP BY merchant, HOP(event_time, INTERVAL '30 SECOND', INTERVAL '1 MINUTE')"

{"merchant":"acme","w":"2026-07-18 09:59:30","n":1}
{"merchant":"globex","w":"2026-07-18 09:59:30","n":1}
{"merchant":"acme","w":"2026-07-18 10:00:00","n":1}
{"merchant":"globex","w":"2026-07-18 10:00:00","n":1}
{"merchant":"acme","w":"2026-07-18 10:00:30","n":1}
{"merchant":"acme","w":"2026-07-18 10:01:00","n":2}
{"merchant":"acme","w":"2026-07-18 10:01:30","n":1}
{"merchant":"globex","w":"2026-07-18 10:01:30","n":1}
{"merchant":"globex","w":"2026-07-18 10:02:00","n":1}

Five input rows, nine output rows. The first window starts at 09:59:30, before any data, because that is the last slide boundary whose 1 minute span still covers the 10:00:03 row.

SESSION

SESSION(ts, gap) is data-dependent: a session stays open while rows keep arriving within gap of each other, and closes once the stream goes quiet for longer than that. Windows merge as late rows bridge two open sessions, so the window boundary is not known until the session ends.

ubik --from file://events.ndjson \
  "SELECT merchant,
          SESSION(event_time, INTERVAL '1 MINUTE') AS w,
          count(*) AS n
   FROM events
   GROUP BY merchant, SESSION(event_time, INTERVAL '1 MINUTE')"

{"merchant":"acme","w":"2026-07-18 10:00:03","n":1}
{"merchant":"globex","w":"2026-07-18 10:00:07","n":1}
{"merchant":"acme","w":"2026-07-18 10:01:12","n":2}
{"merchant":"globex","w":"2026-07-18 10:02:20","n":1}

The window starts at the first event of the session, not on a bucket boundary. acme's rows at 10:01:12 and 10:01:48 are 36 seconds apart, inside the gap, so they share a session; the 10:00:03 row is 69 seconds earlier and gets its own.

The output column

The window function projects the window's start. Unaliased, that column is named window_start.

ubik --from file://events.ndjson \
  "SELECT merchant, TUMBLE(event_time, INTERVAL 1 MINUTE)
   FROM events
   GROUP BY merchant, TUMBLE(event_time, INTERVAL 1 MINUTE)"

{"merchant":"acme","window_start":"2026-07-18 10:00:00"}
{"merchant":"globex","window_start":"2026-07-18 10:00:00"}
{"merchant":"acme","window_start":"2026-07-18 10:01:00"}
{"merchant":"globex","window_start":"2026-07-18 10:02:00"}

window_start is not filterable

It is an output column, not an input column, so it cannot appear in WHERE. Filter the timestamp column instead, or filter the aggregate in HAVING.

Refusals

Two malformed calls are caught before the query binds, so the error names the mistake rather than the column it happens to touch.

An interval written as a bare number:

ubik --from file://events.ndjson \
  "SELECT count(*) FROM events GROUP BY TUMBLE(event_time, 60)"

{"error":{"code":"NOT_SUPPORTED","message":"the window interval must be an INTERVAL literal with a unit: write INTERVAL '2 SECOND' (or MINUTE/HOUR/DAY), not a bare number"}}

Wrong arity, one message per function:

TUMBLE takes a timestamp column and one INTERVAL: TUMBLE(ts, INTERVAL '1 MINUTE')
HOP takes a timestamp column, a slide and a size: HOP(ts, INTERVAL '30 SECOND', INTERVAL '1 MINUTE')
SESSION takes a timestamp column and a gap: SESSION(ts, INTERVAL '5 MINUTE')

Anything still failing to bind after those two checks is a time column that is not a TIMESTAMP.

Late events

A window closes when the watermark passes its end. A row arriving after that is dropped, counted, and reported in the run's final stats line as late_dropped.

Late rows are never silent

A late event is never folded into a closed window without a trace. It is counted and surfaced in late_dropped, so a drift in that number is visible in the run's own output rather than hidden in wrong results.

{"stats":{"rows_in":5,"rows_out":4,"late_dropped":0,"windows_closed":3,"rows_folded":5}}

To keep late rows, widen the grace period with --watermark-delay, which holds a window open until end + interval.