We use analytics cookies.We use cookies to analyze site traffic and improve your experience.
Loading...
System Design Masterclass · Stream Batch Processing
Free preview: the first 2 parts of this 6-part lesson.
Already bought the course? Sign in to read the whole lesson.
this lesson is part of
System Design Masterclass
Unlock this lesson and the 751 other paid lessons
$25 once, by PayPal
See the System Design courseread the rest of this lesson in
System Design Masterclass
Unlock this lesson and the 751 other paid lessons
$25 once, by PayPal
See the System Design coursein Stream Batch Processing
This lesson is part of the full System Design Masterclass. Unlock it along with 769 other lessons for a one-time payment.
One-time payment by card or PayPal
Get Lifetime AccessOne-time payment: UPI, card or netbanking
Get Lifetime Access18 Foundation lessons are free, no payment needed. Try them now
It is easy and good
“It is really nice and good Please join the course”
A stream is unbounded. You cannot compute an average of an infinite sequence. The whole point of a window is to carve a finite chunk out of the infinite stream so that aggregate questions make sense again. Every aggregation over a stream is really a SQL aggregation over a window, even when the syntax hides it.
The Google Dataflow paper (Akidau et al., 2015) formalized this model and it is the reason every modern streaming engine looks the same at the window layer. Before Dataflow, every system had its own window abstraction and they did not compose. After Dataflow, Flink, Streams, Beam, Spark Structured Streaming, and Materialize all converged on the same vocabulary: windows, triggers, watermarks, and allowed lateness. Tyler Akidau's Streaming 101 and 102 essays are the canonical teaching material and every team writing streaming SQL should read them.
SQL window functions have existed in OLTP databases since the early 2000s with the OVER clause. The streaming versions extend the same syntax with time based ranges and per key partitioning. A rolling 5 minute count per user in Flink SQL is written as COUNT(*) OVER (PARTITION BY user_id ORDER BY event_ts RANGE BETWEEN INTERVAL 5 MINUTES PRECEDING AND CURRENT ROW). The query looks identical to the batch version and the planner translates it to a keyed time window operator behind the scenes.
By the end of this lesson you will understand how the SQL OVER clause compiles to a dataflow graph with partitioned state, why PARTITION BY scales but global ordering does not, and why ranking functions are the expensive part.
The OVER clause is the friendliest way to ask window questions. It has four parts: the function (COUNT, SUM, AVG, RANK), the PARTITION BY clause that decides which key defines an independent window, the ORDER BY clause that decides how events are sorted within the window, and the RANGE or ROWS clause that decides the window boundary.
Flink SQL, Streams ksqlDB, Spark Structured Streaming, and Materialize all implement the OVER clause the same way. An analyst writes a query. The planner parses it, decides whether the window is time based or row based, whether the query is stateful, and compiles it to a dataflow graph. PARTITION BY becomes a keyBy that shards state across operator subtasks. The window boundary becomes a stateful operator that maintains per key buffers. The function becomes the aggregation logic that runs when the window fires.
The beautiful part is that the same query runs on batch data and streaming data with identical semantics, as long as the input has an event time column. Tyler Akidau calls this the unified batch and streaming model and it is the main idea the Dataflow paper sold to the industry. You no longer write a different query for your batch report and your streaming dashboard. You write one SQL query and let the engine pick the execution model.
Watch a real query flow through the planner into a keyed rolling window operator and then into a ranker.
Always include PARTITION BY when you can. A partitioned window scales linearly because each key's state stays on one subtask and there is no coordination cost. A window without PARTITION BY collapses all state onto a single operator and caps at what that one JVM can do. For anything beyond toy volumes, global windows are a throughput cliff.
Prefer RANGE over ROWS for streaming queries. RANGE reasons in event time which tolerates out of order arrivals through watermarks. ROWS counts literal rows which is well defined in batch SQL but fragile in streaming because row order depends on processing time. Flink supports both but the manual recommends RANGE for streaming.
Watch out for the frame specification. BETWEEN INTERVAL 5 MINUTES PRECEDING AND CURRENT ROW is a rolling window that updates on every event. UNBOUNDED PRECEDING and CURRENT ROW is a running total from the start of time, which in a streaming context means the state grows forever. Only use UNBOUNDED PRECEDING if you have a separate mechanism to clear state (a daily reset, a user churn event, a session close).
The common mistake is using SUM or AVG over a very wide window (hours or days) and discovering the state size blows up. Each key's window holds every event in the frame. A 1 hour window on a 100 event per second per key stream holds 360,000 events per key in state. Multiply by the number of active keys and you are out of RAM. The fix is incremental aggregation: switch to a reducer that stores only the running sum and count, not the raw events. Flink does this automatically if you use COUNT and SUM but not for custom aggregate functions.