What Is Streaming SQL? How It Works, Engines & Use Cases

Streaming SQL is SQL that runs continuously over data in motion. Instead of querying a table once and returning a fixed result, a streaming SQL query subscribes to a stream of events, such as Kafka topics, database change logs, or sensor feeds, and updates its result every time new data arrives. The query keeps running until you stop it.

For data teams, that changes who can build real-time pipelines. Filtering, joining, and aggregating live events used to mean writing Java or Scala against a stream processing framework. With streaming SQL, an analyst or data engineer who already knows SQL can express the same logic declaratively, and the engine handles state, ordering, and fault tolerance underneath.

What is streaming SQL?

A traditional SQL query works on bounded data. The database reads the rows that exist at query time, computes an answer, and stops. A streaming SQL query works on unbounded data: the input never ends, so the query never "finishes." It produces results continuously and revises them as more events come in.

You will also see this called SQL stream processing, and the idea is older than most of the tools that use it today. Early products such as StreamBase's StreamSQL extended SQL for event streams, and modern engines such as Apache Flink, ksqlDB, and Spark Structured Streaming made the approach mainstream. Across all of them, the goal is the same: to give developers the familiar SQL model for stream processing instead of a new programming API.

Streaming SQL is not a separate language standard. Each engine implements its own dialect on top of standard SQL, adding constructs for time, windows, and continuous output. The core ideas are consistent across engines, which is why it pays to understand the concepts before choosing a tool.

How does streaming SQL work?

Streams as dynamic tables

The central idea is that a stream and a table are two views of the same data. Apache Flink calls this a dynamic table: a table whose contents change over time as events arrive. A continuous query reads from one or more dynamic tables and produces another dynamic table as its output, which the engine keeps up to date incrementally instead of recomputing from scratch.

That output can be written to another Kafka topic, a database, or a data lake, or held as a materialized view that applications query directly. Streaming databases build their whole product around this last pattern: you define a materialized view in SQL, and the database keeps it fresh as the underlying streams change.

Windows

Many streaming questions only make sense over a slice of time, such as "revenue per store in the last five minutes." Windows divide an unbounded stream into finite groups that an aggregation can work on. Most engines support three types:

• Tumbling windows: fixed-size, non-overlapping intervals, such as every five minutes
• Hopping windows: fixed-size intervals that overlap, such as a 10-minute window that advances every minute
• Session windows: variable-size windows that close after a period of inactivity, which suit user sessions and device activity

Event time, processing time, and watermarks

Events do not always arrive in the order they happened. A mobile app may buffer events offline, and network delays can reorder messages. Streaming SQL engines therefore separate event time, when something happened, from processing time, when the engine saw it.

To decide when a window is complete, engines use watermarks. A watermark is a signal that says event time has progressed to a certain point, so no earlier events are expected. You define how much lateness to tolerate, for example 10 seconds, and the engine closes windows and emits results once the watermark passes. This tradeoff between latency and completeness is one of the most important design decisions in any streaming SQL pipeline.

State and joins

Aggregations, deduplication, and joins all require the engine to remember earlier events. That memory is called state, and the engine stores and checkpoints it so a failure does not lose results. Joins are where state matters most: joining a stream of payments with a table of customer profiles is straightforward, while joining two unbounded streams requires a time bound, or the state would grow forever.

Standard SQL vs streaming SQL

The syntax looks familiar, but the execution model is different. The table below summarizes the main differences.

The practical consequence is that streaming SQL rewards thinking about time and state explicitly. A query that is cheap in a batch database, such as an unbounded GROUP BY on a high-cardinality key, can accumulate large state in a streaming engine.

How to query streaming data with SQL

The example below uses Flink SQL to read order events from a Kafka topic and calculate revenue per store in five-minute tumbling windows.

CREATE TABLE orders (
  order_id   STRING,
  store_id   STRING,
  amount     DECIMAL(10, 2),
  order_time TIMESTAMP(3),
  WATERMARK FOR order_time AS order_time - INTERVAL '10' SECOND
) WITH (
  'connector' = 'kafka',
  'topic' = 'orders',
  'properties.bootstrap.servers' = 'broker:9092',
  'format' = 'json',
  'scan.startup.mode' = 'earliest-offset'
);

SELECT window_start, window_end, store_id, SUM(amount) AS revenue
FROM TABLE(
  TUMBLE(TABLE orders, DESCRIPTOR(order_time), INTERVAL '5' MINUTES))
GROUP BY window_start, window_end, store_id;

The CREATE TABLE statement does not copy any data. It maps the Kafka topic to a dynamic table and tells Flink that order_time is the event time column. The WATERMARK clause tolerates events that arrive up to 10 seconds late before a window closes.

The SELECT statement applies a tumbling window function over that table and groups by window and store. Every five minutes of event time, it emits one row per store with that window's revenue. In production, you would typically write this result to another topic or a database table with an INSERT INTO statement so downstream systems can use it.

Streaming SQL engines compared

Choosing a streaming SQL engine depends on latency needs, state size, the platforms you already run, and whether you want to serve query results directly to applications.

Flink SQL is the usual choice for general-purpose streaming SQL, and Confluent's managed stream processing centers on it. ksqlDB remains a simple choice for teams that live entirely in Kafka. Spark Structured Streaming suits organizations that want one engine for batch and streaming, with latency in the range of seconds rather than milliseconds. Streaming databases trade some processing flexibility for the convenience of querying results with a PostgreSQL-compatible interface.

Streaming SQL use cases

Fraud and risk detection

Banks and payment providers use streaming SQL to compare each transaction against recent activity for the same card or account. A windowed aggregation can flag an unusual number of transactions in a short period, and a join with reference data can check merchant risk before the payment completes.

Real-time inventory and operations

Retailers and manufacturers maintain live views of stock levels, order backlogs, and machine status. Instead of waiting for a nightly batch, operations teams see changes within seconds, and alerts fire when a metric crosses a threshold.

Change data capture pipelines

Change data capture streams every insert, update, and delete from operational databases. Streaming SQL can clean, mask, and join those changes in flight before they land in a warehouse or lakehouse, which keeps analytical copies current without extra load on the source systems.

Fresh context for AI agents

AI agents and machine learning models are only as good as the data they act on. Streaming SQL can compute features, such as a customer's spend in the last hour, and keep them current, so models and agents work from what is happening now rather than yesterday's snapshot. Mimacom's streamhouse on IBM Confluent applies this idea to live data for AI agents.

For the analytics layer that sits on top of these pipelines, see our guide to streaming analytics.

Limitations and when not to use it

Streaming SQL covers a large share of real-time workloads, but not all of them. Complex event logic, such as multi-step pattern detection with custom rules, can be awkward to express in SQL, and some teams move that logic into the DataStream API or application code. Very large state, such as joins over months of history, needs careful design to keep memory, checkpoint size, and cost under control.

Operating continuous queries is also different from running reports. A query that runs for months needs monitoring, versioning, and a plan for changing its logic without losing state. Dialects differ between engines, so moving a pipeline from ksqlDB to Flink SQL means rewriting queries, not copying them. Teams that want to avoid code altogether can look at no-code stream processing, which builds Flink jobs through a visual canvas.

How Mimacom can help

Mimacom has worked with Confluent since 2017 and has been a Confluent training partner since 2021. Following IBM's acquisition of Confluent, we deliver as an IBM partner, designing and running event streaming platforms and Flink SQL pipelines on IBM Confluent for enterprises in banking, insurance, manufacturing, and retail.

We start with the use case, not the engine. Our architects assess your latency, state, and governance requirements, recommend the right streaming SQL approach, and then build and operate the pipelines with your team. Learn more about our Confluent consulting services or download The Data Streaming Foundation for AI whitepaper.

FAQs

Does SQL support streaming?

Standard SQL was designed for bounded tables, so it has no built-in concept of unbounded streams, event time, or watermarks. Streaming engines such as Flink SQL, ksqlDB, and Spark Structured Streaming extend SQL with these concepts, so you can write familiar SELECT, JOIN, and GROUP BY statements that run continuously over streams.

What is a streaming database?

A streaming database is a database that ingests streams and keeps materialized views up to date incrementally as new events arrive. RisingWave and Materialize are two examples. Unlike a stream processing engine that writes results to other systems, a streaming database also serves those results directly, so applications can query them with standard SQL clients.

Is streaming SQL a good fit for OLTP workloads?

No, streaming SQL complements OLTP databases rather than replacing them. Transactional systems handle individual reads and writes with strict consistency, while streaming SQL processes the flow of changes those systems produce. A common pattern is to capture changes from the OLTP database with change data capture and process them with streaming SQL downstream.

Streaming SQL makes real-time data accessible to every SQL user

Streaming SQL brings the most widely known data language to real-time pipelines. Once your team understands continuous queries, windows, watermarks, and state, choosing between Flink SQL, ksqlDB, Spark, and streaming databases becomes a question of fit, not of learning a new paradigm. Start with one high-value use case, design for time and state from the beginning, and expand from there.