Streaming and Real-Time SQL

Reviewed & published by Brayan K

Traditional SQL runs once over a finite table and stops. Streaming SQL runs forever over an endless stream of events, continuously updating its answers. By the end of this lesson you'll understand bounded vs unbounded data, continuous queries, event-time vs processing-time, the three windowing styles (tumbling, sliding, session), watermarks for late data, incrementally-updated materialized views, and the tools — Flink SQL, ksqlDB, Materialize, RisingWave — that power real-time dashboards, fraud detection, and alerting.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

What You'll Learn

• Bounded (batch) vs unbounded (stream) data
• Continuous queries that never finish
• Event-time vs processing-time
• Tumbling, sliding, and session windows
• Watermarks for late and out-of-order events
• Incremental materialized views and the streaming tool landscape

Our Sample Stream: events

Imagine click events flowing in. As a finite snapshot (a bounded table) it looks like this — but a real stream would keep appending rows forever. Each row has a user, the minute bucket it fell in, and its event-time.

1. Bounded vs Unbounded Data

Bounded data is finite: a table, a file, yesterday's logs — it has a definite end, so a query can read all of it and produce a final answer. This is classic batch processing. Unbounded data is an endless stream of events that never stops arriving. A query over it can never see "all the data," so it must run continuously and keep revising its output as new events flow in.

Batch is a lake: a fixed body of water you can measure end to end. A stream is a river: always flowing past you. You can't measure "all" of a river — you can only measure what passes a point in a window of time. Streaming SQL is how you put a measuring station on the river.

-- The table this lesson uses. Every later block queries it, and the
-- "Try it Yourself" button carries this setup along so each snippet runs.
CREATE TABLE events (
    id            INTEGER PRIMARY KEY,
    kind          TEXT,
    minute_bucket TEXT,
    user_id       INTEGER
);

INSERT INTO events (id, kind, minute_bucket, user_id) VALUES
    (1, 'click', '2026-03-01 09:00', 11),
    (2, 'click', '2026-03-01 09:00', 12),
    (3, 'view',  '2026-03-01 09:00', 11),
    (4, 'click', '2026-03-01 09:01', 13),
    (5, 'click', '2026-03-01 09:02', 11),
    (6, 'click', '2026-03-01 09:02', 12),
    (7, 'view',  '2026-03-01 09:02', 14);

-- BATCH (bounded) thinking — the SQL you already know.
-- A finite "events" table: count clicks per minute. It runs once,
-- scans the whole table, returns a FINAL answer, and stops.
SELECT
    minute_bucket,
    COUNT(*) AS clicks
FROM events
GROUP BY minute_bucket
ORDER BY minute_bucket;

-- The dataset is BOUNDED: it has a definite end, so "GROUP BY" can
-- see every row before producing a result. Streaming flips this:
-- the data never ends, so the query must run forever and keep
-- updating its answer as new events arrive.

-- ✅ Expected result:
-- minute_bucket | clicks
-- 2026-03-01 09:00 | 3
-- 2026-03-01 09:01 | 1
-- 2026-03-01 09:02 | 3

2. Continuous Queries

In batch SQL, you run a query and it returns once. A continuous query (also called a standing query) is registered once and then runs indefinitely, emitting updated results every time relevant events arrive. The same mental model as a spreadsheet cell that recalculates whenever its inputs change — except the inputs are an infinite event stream.

3. Event-Time vs Processing-Time

Every event really has two clocks. Event-time is when the event actually happened — a timestamp carried inside the record (when the user clicked). Processing-time is when your system happens to handle it. They diverge constantly: a phone goes offline and uploads its clicks an hour late, or a network hiccup reorders messages.

For correct analytics you almost always want event-time: "how many clicks happened at 10:01" should mean when they occurred, not when they reached the server. Processing-time is simpler and lower-latency, but gives wrong window counts whenever data is delayed or out of order.

4. Windowing — Tumbling, Sliding, Session

Since a stream never ends, you aggregate over windows — finite slices of time. There are three classic shapes:

• Tumbling: fixed-size, non-overlapping buckets. Every event falls into exactly one (e.g. counts per 1-minute bucket).
• Sliding (hopping): fixed-size windows that advance by a smaller step, so they overlap and an event can belong to several (e.g. "clicks in the last 5 minutes," updated every minute).
• Session: variable-length windows defined by gaps of inactivity — a burst of activity becomes one window, closing after a configured quiet period.

-- STREAMING tumbling window (Flink SQL style). Illustrative — NOT runnable here.
-- Fixed 1-minute, NON-overlapping buckets over an unbounded stream.
-- Each event lands in exactly ONE window; results emit per minute.

-- SELECT
--     window_start,
--     window_end,
--     COUNT(*) AS clicks
-- FROM TABLE(
--     TUMBLE(TABLE clicks, DESCRIPTOR(event_time), INTERVAL '1' MINUTE)
-- )
-- GROUP BY window_start, window_end;

-- Unlike batch, this query never finishes: as the stream advances,
-- each completed minute emits its count and the next window opens.

-- STREAMING sliding (hopping) window. Illustrative — NOT runnable here.
-- A 5-minute window that advances every 1 minute, so windows OVERLAP.
-- Use it for smooth rolling metrics ("clicks in the last 5 minutes").

-- SELECT
--     window_start,
--     window_end,
--     COUNT(*) AS clicks_last_5m
-- FROM TABLE(
--     HOP(TABLE clicks, DESCRIPTOR(event_time),
--         INTERVAL '1' MINUTE,    -- slide/hop step
--         INTERVAL '5' MINUTE)    -- window size
-- )
-- GROUP BY window_start, window_end;

-- Size > slide => overlap, so a single event can count in several windows.

-- STREAMING session window. Illustrative — NOT runnable here.
-- Groups a user's activity into a "session" that ends after a gap
-- of inactivity (here 30 seconds). Window length is VARIABLE.

-- SELECT
--     user_id,
--     SESSION_START(event_time, INTERVAL '30' SECOND) AS session_start,
--     COUNT(*) AS actions
-- FROM clicks
-- GROUP BY user_id, SESSION(event_time, INTERVAL '30' SECOND);

-- A burst of clicks 5s apart = one session; a 30s+ silence closes it
-- and the next click starts a brand-new session.

5. Watermarks — Handling Late Data

If you aggregate by event-time, here's the hard question: when is a window done? You can't wait forever for stragglers, but closing too early loses late events. A watermark is the answer — a moving marker that says "event-time has advanced to T; we believe all events older than T have arrived." When the watermark passes a window's end, that window is finalized and emitted.

A watermark bounds how long you wait. Allow more lateness (e.g. 30s) and results are more complete but slower; allow less and they're faster but may drop late events. Events arriving after the allowed lateness are typically dropped or sent to a side output for separate handling.

-- WATERMARKS + event-time. Illustrative — NOT runnable here.
-- Networks deliver events LATE and out of order, so we trust the
-- event_time IN the data, not the clock. A watermark says
-- "event-time has advanced to T; events older than T are likely all here".

-- CREATE TABLE clicks (
--     user_id    INT,
--     event_time TIMESTAMP(3),
--     -- allow up to 5 seconds of lateness before finalizing a window:
--     WATERMARK FOR event_time AS event_time - INTERVAL '5' SECOND
-- ) WITH ( 'connector' = 'kafka', ... );

-- When the watermark passes a window's end, that window is CLOSED and
-- emitted. Events later than the allowed lateness are dropped or routed
-- to a side output. Watermarks bound how long you wait for stragglers.

6. Incrementally-Maintained Materialized Views

A normal materialized view stores a query's result but goes stale until you refresh it (recomputing the whole thing). Streaming databases like Materialize and RisingWave take a different approach: you write a familiar SELECT once, and the engine keeps the result continuously up to date by applying only the deltas as new events arrive — never recomputing from scratch. You then query the view like any table and always get a millisecond-fresh answer.

This is the magic that makes streaming feel like ordinary SQL: the standing query is hidden behind a view. Your dashboard just runs SELECT * FROM clicks_per_user and the numbers update themselves.

-- INCREMENTALLY-MAINTAINED MATERIALIZED VIEW (Materialize / RisingWave).
-- Illustrative — NOT runnable here.
-- Define a query ONCE; the engine keeps the result fresh by applying
-- only the CHANGES (deltas), never recomputing from scratch.

-- CREATE MATERIALIZED VIEW clicks_per_user AS
-- SELECT user_id, COUNT(*) AS clicks
-- FROM clicks
-- GROUP BY user_id;

-- Now: SELECT * FROM clicks_per_user;  -- always up to date, millisecond-fresh.
-- As new click events stream in, the per-user counts update automatically.
-- This is a standing query disguised as an ordinary view.

7. Where Streaming SQL Shines

• Real-time dashboards: live metrics (active users, revenue, error rates) that update by the second instead of via nightly batch.
• Fraud detection: flag suspicious patterns — say, many transactions from one card across regions in a short window — the instant they happen.
• Alerting & monitoring: trigger when a streaming aggregate crosses a threshold (error rate spikes, a queue backs up).
• Personalization: update recommendations or feature flags from a user's behavior in the current session.
• ETL / data movement: continuously transform and route events between systems (change data capture into a warehouse).

Your Turn: the batch version of a tumbling window

Fill in the blanks to count events per minute bucket. In a streaming engine this becomes a tumbling-window query that emits each minute forever — but the aggregation logic is exactly what you write here.

-- 🎯 YOUR TURN — fill in the blanks, then press "Try it Yourself"
-- Goal (batch baseline): count events per minute bucket.
-- This is the bounded version of a tumbling-window stream query.

SELECT minute_bucket, COUNT(*) AS clicks
FROM events
GROUP BY ___                 -- 👉 the column that defines each 1-minute bucket
ORDER BY ___;                -- 👉 sort the buckets in time order

-- ✅ Expected: one row per minute bucket with its click count, in order.

Common Pitfalls (and the fix)

• Aggregating a stream without a window: a plain GROUP BY over an unbounded stream has unbounded state and never "finishes." Window the aggregation (tumbling/sliding/session) so state stays bounded and results emit.
• Using processing-time when you meant event-time: if data can arrive late or out of order, processing-time windows put events in the wrong bucket. Carry an event timestamp and window on it.
• Forgetting watermarks: without a watermark the engine doesn't know when a window is complete, so event-time windows may never emit. Define a watermark with a sensible allowed lateness.
• Expecting exactly-once for free: end-to-end correctness needs the right delivery guarantees and idempotent sinks. Streaming engines offer exactly-once, but you must configure sources and sinks to honor it.
• Treating an incremental view like a batch refresh: incremental materialized views update via deltas — design queries the engine can maintain incrementally; some operations are expensive or unsupported to maintain.

Frequently Asked Questions

Q: Is streaming SQL just normal SQL run repeatedly?

No. Re-running a batch query on a schedule (micro-batch) is one approximation, but true streaming SQL maintains a continuous query with persistent state, event-time semantics, windows, and watermarks, emitting incremental updates as events arrive.

Q: Tumbling vs sliding vs session — how do I choose?

Use tumbling for clean, non-overlapping periods ("revenue per hour"). Use sliding for smooth rolling metrics ("error rate over the last 5 minutes, updated each minute"). Use session when activity comes in bursts and you want to group each burst (user sessions, device activity).

Q: What happens to events that arrive very late?

It depends on the allowed lateness in your watermark. Within the bound, late events still update their window; beyond it, they're usually dropped or routed to a side output so you can handle them separately rather than silently losing them.

Q: Do I need Kafka to do streaming SQL?

Not strictly, but a durable log like Apache Kafka (or Pulsar/Kinesis) is the common backbone — it's where the unbounded streams live. ksqlDB targets Kafka directly; Flink, Materialize, and RisingWave connect to Kafka and other sources.

🎉 Lesson Complete

• ✅ Bounded batch data ends; unbounded streams never do, so queries run forever
• ✅ A continuous query is registered once and keeps emitting updated results
• ✅ Prefer event-time over processing-time for correct windowing of late/out-of-order data
• ✅ Tumbling, sliding, and session windows slice an endless stream into finite aggregates
• ✅ Watermarks decide when a window is complete and bound how long you wait for stragglers
• ✅ Incremental materialized views (Materialize, RisingWave) keep results fresh via deltas; Flink SQL and ksqlDB power real-time dashboards, fraud detection, and alerting
• ✅ You've reached the end of the advanced track — congratulations!

Practice quiz

Continue this course

• Previous: SQL vs NoSQL: When to Use Each
• Next: MERGE & UPSERT — Idempotent writes with INSERT ON CONFLICT, ON DUPLICATE KEY UPDATE and MERGE
• Quick reference: SQL cheat sheet

Related lessons

• GROUP BY aggregates
• window functions
• materialized views