Deep Dive into Stream SQL Principles and Incremental Query Execution in Apache Flink

The article begins by motivating the need for low‑latency incremental processing in data‑warehouse ETL pipelines and introduces Stream SQL as a natural extension of the well‑studied relational SQL language, highlighting its expressive limits compared to general computation models.

It then outlines three key research questions: the boundaries of SQL vs. Stream SQL, how to implement streamable versions of GROUP BY and JOIN, and the concrete implementation details in Flink.

Next, the concept of incremental SQL queries is explained: instead of re‑executing a full query on every data change, only the delta (incremental table) is processed, turning the problem into materialized view maintenance. Essential terminology such as view, materialized view, table, and index is defined.

The article discusses SQL optimization and execution planning, describing logical plans (relation algebra trees) and cost‑based optimizers that transform logical plans into physical plans, choosing operators like Hash Join or Sort Join based on estimated costs.

It presents a simple algorithm for incremental view maintenance, showing how operators generate incremental tables from three possible data sources: internal state, upstream deltas, or by scanning child sub‑trees.

Several challenges are examined, including query amplification (e.g., SELECT MAX(a, 42) FROM example, SELECT count(distinct a) FROM example, SELECT a FROM example ORDER BY a LIMIT 10, and theta‑join queries like SELECT T1.a, T2.a FROM T1 JOIN T2 ON T1.a * T2.a < 100), and possible mitigation strategies such as unique row IDs, extra operator state, approximate algorithms (HyperLogLog), or restricting semantics.

Modification amplification is illustrated with a cross‑join example ( SELECT A.a, B.b FROM A CROSS JOIN B) and the need for delayed refresh or semantic restrictions.

The notion of self‑maintainable operators is introduced: an operator that can compute its output solely from internal state and the incoming delta, which is crucial for stream processing and distributed query execution.

Time handling in stream systems is covered, describing event‑time vs. processing‑time, the role of triggers, watermarks, and punctuations. Watermarks advance monotonically based on processed event timestamps and help bound state size.

Windowing primitives (fixed, sliding, session) are explained, showing how they limit the scope of joins and aggregations, and how windows interact with watermarks and punctuations.

Various join semantics for streams are compared: stream‑static table join, stream‑dynamic table snapshot join, and stream‑stream join, with the latter often realized via windowed joins.

Finally, the article reviews Apache Flink’s capabilities and limitations: state management (in‑memory, filesystem, RocksDB with incremental checkpoints), exactly‑once delivery via checkpointing and Kafka integration, and Flink’s SQL/Table API built on Apache Calcite. It lists supported window functions (TUMBLE, HOP, SESSION), temporal table joins, and notes that Flink only supports equi‑joins, recommending windowed joins for scalability.

In summary, the piece highlights that while modern stream SQL systems can handle many practical workloads, fully generic, efficient incremental execution of arbitrary SQL remains an open research problem, with ongoing work on statistics collection, cost modeling, and adaptive optimization.