SQL aggregations are among the most common data pipeline operations: COUNT, SUM, AVG, and COUNT DISTINCT over a GROUP BY key. Query engines understand and optimize these operations, which are widely used for feature engineering. The challenge is not one execution, but thousands of executions over an ever-growing table.
When a feature pipeline refreshes a feature store with an hourly aggregation, it reads the entire table, or at best a full partition, each time. The first run may process 10 million rows in a few seconds. Six months later, the same query processes 500 million rows and takes three minutes. The query and logic are unchanged, but execution costs have grown 50 times because batch query engines do not retain state from previous runs.
How incremental view maintenance works
Incremental view maintenance, or IVM, keeps a materialized view current as underlying tables change without rebuilding the view from scratch. When an input delta arrives, whether a new, deleted, or updated row, the view applies a delta to its current output state instead of rerunning the complete query.
For COUNT(*) GROUP BY, the delta rule is direct: increase the count for the new row's group key. For SUM, add the new value to that group's existing sum. For AVG, maintain the count and sum separately, then recompute the average only for the affected group. These trivially incrementalizable aggregations cover a large share of feature engineering use cases, with O(1) delta cost for COUNT, SUM, and AVG.
Time-windowed aggregations with expiry are less straightforward. A 30-day rolling window gains new rows while old rows leave as the window advances. Incremental maintenance must track both additions, from new events, and deletions, from expired events. The engine needs sufficient state to determine what to subtract when an event ages out.
-- This view is maintained incrementally by Feldera
-- Each new event: O(1) work for the affected group key
-- Each expired event: O(1) subtraction for the same key
CREATE MATERIALIZED VIEW user_activity_30d AS
SELECT
user_id,
COUNT(*) AS event_count,
SUM(amount) AS total_amount,
MIN(amount) AS min_amount,
MAX(amount) AS max_amount,
AVG(amount) AS avg_amount
FROM events
WHERE ts >= NOW() - INTERVAL '30 days'
GROUP BY user_id;
-- Note: MIN and MAX require special handling for deletions
-- When the min-value row expires, the new minimum must be
-- recomputed from remaining rows in the window -- O(window_size)
-- for that specific entity. COUNT/SUM/AVG have O(1) delta cost.The distinction involving MIN and MAX is important and often overlooked. SUM and COUNT are trivially reversible: when a row with value V enters SUM, add V; when it leaves, subtract V. MIN and MAX are not reversible in the same way. If an expiring row supplied the current minimum, the remaining window must be scanned to find the new minimum. Thus, MIN and MAX have O(window_size) worst-case delta cost for deletion events, rather than O(1). This matters when MIN/MAX are important feature pipeline signals.
Compiling SQL to an incremental operator graph
Feldera compiles SQL views into a directed acyclic graph, or DAG, of incremental operators. Each operator represents a SQL primitive such as scan, filter, project, join, or aggregate. Implemented as an incremental function, each operator accepts a delta input and emits a delta output. A new event enters at the source, propagates through the relevant operators, and produces delta outputs at the sinks.
Compilation determines which SQL constructs are supported and how efficiently they run incrementally. Standard aggregations, including COUNT, SUM, MIN, MAX, and AVG over a GROUP BY, compile directly into efficient incremental operators. HAVING clauses over aggregated results work because they operate on aggregate output rather than the base table. Multi-level aggregations, or aggregations of aggregations, need intermediate views but are fully supported.
Plans that do not compile efficiently are those involving iterative or recursive computation, such as transitive closure, graph reachability, or iterative convergence. SQL does not natively express these patterns. For the GROUP BY and standard aggregation functions commonly used in ML feature pipelines over time-windowed base tables, incremental compilation is well-supported and delivers O(delta) cost per input event.
JOIN incremental maintenance and state
JOIN incremental maintenance is more complex. A JOIN between a streaming transactions table and a static merchant metadata table is well-behaved: each new transaction triggers a merchant-table lookup, updates the relevant join output rows, and propagates the delta downstream. The merchant table stays fully in memory, or spills to disk if large, making the join essentially a hash lookup for each new event.
A JOIN between two streaming tables is more difficult. A new row in table A must match existing rows in table B, while a new row in table B must match existing rows in table A. Both sides need state, and each state can grow without bound without a time-bounded window or explicit eviction policy. Most streaming joins in feature pipelines are time-bounded in practice: joining transactions from the same hour, or events with the most recent entity state. These joins have bounded state size and are the recommended pattern for production incremental pipelines.
When to use incremental SQL vs. alternatives
Incremental SQL fits continuous input streams, SQL-expressible aggregation logic, and freshness requirements that batch pipelines cannot meet. It is not suitable for every workload.
When feature aggregations use only static historical data and freshness is unnecessary, batch SQL is simpler to reason about and avoids state management overhead. If feature logic needs Python transformations, custom model inference, or operations without SQL equivalents, an incremental SQL layer can handle the aggregation while a separate downstream step performs the non-SQL computation. Forcing computation that genuinely needs custom code through an incremental SQL engine is the wrong tradeoff. The incremental layer is most valuable for the aggregation-heavy, SQL-expressible portion of the feature computation graph, which is most of the feature work in ML pipelines.