Building an incremental SQL engine differs fundamentally from building a batch query engine. A batch engine must produce correct results once from static input. An incremental engine must produce correct results continuously as inputs change, preserving enough state to calculate each new result from the previous one without starting over. Database research has established the algorithmic foundations. Making those algorithms reliable at production scale took us longer to engineer than we expected.
This post looks back on what we learned during Feldera's open beta, which ran from early this year through the public launch. We ran production workloads with beta customers, extensively instrumented each pipeline, and gathered substantial operational data. Some challenges we expected were easier than anticipated. Others that seemed straightforward had more depth than we had accounted for.
Query compilation was harder than the literature suggested
The database literature has described incremental view maintenance since the 1980s. Its basic delta rules for COUNT, SUM, and JOIN are well documented. It gives less attention to arbitrary SQL written by practitioners who are not considering incrementalizability. Users write correlated subqueries, nested aggregations, and multi-way joins that are technically incrementalizable but need significant rewriting before compilation into an efficient incremental operator graph.
Our query rewriter handles eighty percent of these cases automatically. It identifies patterns with known incremental equivalents and rewrites them before compilation. The remaining twenty percent required users to rewrite their SQL manually. We built an error messaging layer that clearly explains what the rewriter could not handle and proposes alternative phrasings. This saved more user time than the rewriter itself because the suggestions were specific enough to use without understanding the theory behind incremental computation.
-- Correlated subquery pattern: common in analyst-written features
SELECT
user_id,
(
SELECT COUNT(*)
FROM transactions t2
WHERE t2.user_id = t1.user_id
AND t2.ts >= t1.ts - INTERVAL '1 hour'
) AS velocity_1h
FROM transactions t1;
-- Rewritten to a join for O(1) incremental cost per new event
SELECT
t1.user_id,
COUNT(t2.txn_id) AS velocity_1h
FROM transactions t1
LEFT JOIN transactions t2
ON t2.user_id = t1.user_id
AND t2.ts BETWEEN t1.ts - INTERVAL '1 hour' AND t1.ts
GROUP BY t1.user_id, t1.txn_id;The incremental execution cost for correlated subqueries is quadratic in the worst case. We added a query rewriter that converts the most common correlated subquery shapes to join-based equivalents automatically. That resolved around eighty percent of cases in beta. The remaining twenty percent, mostly non-equijoin correlations and non-linear temporal predicates, required query rewrites from the user. This was more friction than we expected. We are still working on expanding the rewriter's coverage.
State management turned out to be the operational bottleneck
Feature pipelines for fraud and recommendation typically maintain months of behavioral history per entity. An incremental engine that holds all of that state in memory runs into practical limits on workloads with tens of millions of distinct entities. During beta, the most common operational issue we saw was not correctness failures but memory pressure on pipelines with high entity cardinality and long retention windows.
We spent substantial time on spill-to-disk strategies that let the engine maintain correctness while keeping the hot working set in memory. The basic idea is that entities with no recent activity can have their state serialized to disk and evicted from memory, with a read-back on the next event for that entity. The tricky part is doing this without introducing correctness issues for windowed aggregations when state is recalled.
The lesson is that incremental computation shifts the resource bottleneck from CPU to memory. Batch recompute is expensive in CPU cycles. Incremental computation is cheap in CPU but requires memory proportional to the state that needs to be maintained across ticks. For pipelines with unbounded entity cardinality, you need explicit state size controls: time-bounded windows that allow old state to be freed, LRU eviction for cold entities, or explicit compaction steps for long-tail keys.
Users wanted operational tooling more than SQL features
Going into beta, we assumed the primary feedback would be about SQL compatibility. Which standard SQL constructs did we support, which were missing, and where did our behavior diverge from PostgreSQL semantics. That feedback came in, but it was not the dominant theme.
The dominant theme was operational experience. How do you debug a pipeline that is producing unexpected output? How do you handle a source system that replays events after a backpressure incident? How do you migrate a pipeline to a new SQL version without dropping state? How do you tell whether a lag in feature availability is caused by the compute layer or the Kafka consumer?
These are the same questions that come up in any production streaming deployment, whether Flink or Kafka Streams or Spark Structured Streaming. What is different with Feldera is that pipelines are expressed as SQL, so the debugging experience can be built on familiar tooling. You can query the engine's current output state with a SQL SELECT and compare it to what you expected. We invested heavily in this observability layer based on beta feedback: query-level output inspection, per-operator throughput metrics, and a replayable event log for diagnosing unexpected state transitions.
Exactly-once semantics are often not what users actually need
One of the design decisions we revisited during beta was our approach to exactly-once processing guarantees. Feldera's engine uses a transactional commit protocol to ensure that each input event is reflected exactly once in the output, even across restarts and failures. This is the right guarantee for certain workloads, particularly fraud scores where double-counting a transaction could produce an artificially elevated velocity feature.
For recommendation features, the cost of maintaining exactly-once guarantees, checkpoint overhead and recovery latency, was frequently higher than the cost of tolerating occasional duplicate processing. A session affinity feature that double-counts one page view in a thousand is not meaningfully less accurate than a perfectly deduplicated one. We added a processing mode that relaxes the guarantee to at-least-once, with significant checkpoint overhead reduction. It is not appropriate for all workloads, but for latency-sensitive recommendation features it is often the right tradeoff.
What we did not anticipate: SQL as the onboarding bottleneck
We expected SQL to lower the barrier to entry. In practice, writing correct incremental SQL requires understanding which constructs the engine handles efficiently and which do not incrementalize well. That knowledge is not intuitive from the SQL syntax itself. A query that looks simple, like a multi-window aggregation over a self-join, can have surprising state growth in an incremental execution model.
We have addressed part of this with static analysis warnings during query compilation: if the planner detects a pattern likely to produce large state, it surfaces a warning before the pipeline starts. But there is no substitute for documentation that explains the cost model clearly. We have been rewriting our docs to focus on the incremental execution model, not just the SQL syntax, and early feedback suggests this is the right investment.
The open beta was a useful forcing function for understanding which parts of our original design held up and which needed rethinking. The core bet, that incremental SQL is the right abstraction for streaming feature computation, held up well. The state management and operational tooling work was larger than we budgeted, which is about as honest as a post-beta reflection gets. More engineering capacity going into year two is going to operational quality and pipeline debuggability, less on expanding the SQL feature surface.