SQL was designed for bounded relations. You have a table. You run a query. You get a result. The table does not change during query execution, the result set is finite, and the query terminates. These assumptions are so deeply embedded in how SQL engines work that most developers never think about them.
Streaming SQL breaks all three assumptions simultaneously. The input is unbounded. The query does not terminate; it runs continuously. And the result set evolves as new data arrives. If you take a batch SQL query and run it on a stream without adjusting it, you will get either a wrong result, an unbounded memory footprint, or both.
This post covers the practical patterns for writing SQL queries that work correctly in an incremental streaming context, and the common mistakes we have seen teams make when migrating from batch SQL to streaming.
How Incremental Execution Changes Your Query Semantics
In standard SQL, SELECT * FROM orders WHERE amount > 100 returns the current set of matching rows. In streaming SQL, the same query means: maintain a continuously updated view that contains exactly the rows that satisfy the predicate at any given moment. New rows that arrive and satisfy the predicate are added. Rows that are retracted (in a CDC stream) and that previously satisfied the predicate are removed.
Feldera compiles SQL queries to an operator graph of incremental operators. Each operator takes a stream of deltas (additions and retractions) as input and produces a stream of deltas as output. A filter operator applies the predicate to each incoming delta and forwards the delta if the predicate is satisfied. A projection operator applies the column transformation. An aggregation operator maintains running state and emits delta updates to the aggregate when the input changes.
The key insight is that the query output is defined as the view you would get if you ran the batch query on the complete input at any point in time. Feldera guarantees that the incrementally maintained result always matches this hypothetical batch result. This is the correctness property that makes streaming SQL usable for feature computation.
Queries That Compile Well to Incremental Operators
Most standard SQL patterns translate cleanly to incremental execution:
Stateless filters and projections. These are the simplest case. Each delta in the input produces at most one delta in the output. No state is required. Memory usage is constant regardless of stream volume.
CREATE MATERIALIZED VIEW high_value_orders AS
SELECT
order_id,
customer_id,
amount,
merchant_category
FROM orders
WHERE amount > 500;GROUP BY with standard aggregates. Counts, sums, min, max, and averages are all incrementally maintainable. The engine maintains per-group running state and emits a retraction for the old value and an addition for the new value when a group changes.
CREATE MATERIALIZED VIEW customer_spend AS
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(amount) AS total_spend,
MAX(amount) AS max_order
FROM orders
GROUP BY customer_id;Equi-joins between a stream and a table. Joining a stream of events against a slowly-changing lookup table (e.g., merchant category classifications) works well. The join operator maintains an index of the table side and probes it for each incoming event. Memory is proportional to the size of the table side, not the stream volume.
Patterns That Require More Care
Window functions and time-bounded aggregates. Standard SQL window functions like ROW_NUMBER() OVER (ORDER BY ...) require global ordering, which is expensive on streams. What most feature engineering actually needs is time-windowed aggregates: "count of events in the last hour per customer." Use tumbling or sliding window syntax rather than window functions for these cases.
-- Time-windowed aggregate: incremental-friendly
CREATE MATERIALIZED VIEW session_activity AS
SELECT
user_id,
COUNT(*) FILTER (WHERE event_ts >= NOW() - INTERVAL '30 minutes') AS events_30m,
COUNT(DISTINCT item_id) FILTER (WHERE event_ts >= NOW() - INTERVAL '30 minutes') AS distinct_items_30m
FROM events
GROUP BY user_id;Joins between two unbounded streams. Stream-stream joins require careful state management. When two streams are joined on a key, each side must buffer events waiting for a matching row from the other side. If matches arrive within a bounded time window, state is bounded and the join is tractable. If matches can arrive at arbitrary times, state grows without bound. We have written about this in detail in the streaming joins post; the short version is to always apply time bounds to stream-stream joins and to be explicit about what happens to late arrivals.
Subqueries that reference the same base table multiple times. A correlated subquery like WHERE amount > (SELECT AVG(amount) FROM orders) requires the engine to maintain both the outer scan and the inner aggregate, and to re-evaluate the outer predicate whenever the inner aggregate changes. This is correct but expensive. In practice, it is better to materialize the inner aggregate as a separate view and join against it.
The Retraction Problem
The biggest conceptual shift when writing streaming SQL is understanding retractions. In a batch system, rows are added and that is it. In a streaming system with CDC inputs or with time windows that expire, rows can be retracted. When a row is retracted, the engine needs to undo its contribution to any downstream aggregates.
For simple aggregates like SUM and COUNT, undo is straightforward: subtract the retracted value from the running sum or decrement the count. For MAX and MIN, undo is more complex because removing the current maximum requires knowing the new maximum from the remaining rows, which requires maintaining more state.
Feldera handles all of this transparently for the SQL constructs it supports. But it is worth understanding the implication: queries that use MAX or MIN on high-cardinality streams over large windows will use more memory than equivalent COUNT or SUM queries, because the engine must retain enough information to answer "what is the new max after this retraction?"
Debugging an Incremental Query
When a query produces unexpected results in incremental mode, the most useful debugging step is to check the output change stream rather than the materialized state. Feldera can expose the delta stream for any view: instead of seeing the current value of an aggregate, you see each update event (old value, new value) as it is produced.
A common mistake is writing a query that produces a large number of small updates when a single large update would suffice. For example, if you aggregate by (customer_id, minute_bucket) and your output layer is reading every update, you will see a separate delta for every customer for every minute. Introducing a coarser aggregation or a post-processing step to collapse updates improves throughput significantly.
What Streaming SQL Does Not Replace
Streaming SQL is not a replacement for ad-hoc query engines. Writing a query that runs incrementally over an unbounded stream requires committing to a query shape upfront. You cannot issue a one-off exploratory query against a streaming system and get results the way you can against a data warehouse. The query is compiled to a pipeline at definition time; changing it requires redeploying and potentially reprocessing historical state.
For exploratory analysis and feature discovery, batch SQL on a data warehouse or lakehouse is still the right tool. Streaming SQL comes in when you have identified the feature logic and need it to run continuously with sub-second freshness. Think of the streaming version as the production deployment of a feature that was developed and validated in batch mode first.