Streaming and Real-Time SQL

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.

Learn Streaming and Real-Time SQL in our free SQL course — an interactive lesson with worked examples, a practice exercise and a quick reference.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary