DEV Community

Cover image for ksqlDB: Streaming SQL on Kafka — Still Worth It in 2026?
Gowtham Potureddi
Gowtham Potureddi

Posted on

ksqlDB: Streaming SQL on Kafka — Still Worth It in 2026?

#python #sql #dataengineering #interview

ksqldb is the streaming-SQL surface Confluent bolted on top of Kafka in 2019 with the promise of "SQL on your log" — and it is the single project whose 2026 relevance is most hotly contested inside senior data-engineering interviews. In 2019 ksqlDB looked like it might be to Kafka what Hive was to Hadoop; by 2023 the momentum had slowed, Confluent had quietly folded Flink into its cloud story, and by 2026 the honest answer to "should I start a new project on ksqlDB?" is a genuine "it depends" rather than an easy yes or no. The engineering trade-off lives in which mental model you use for the ksqlDB runtime and which alternative you can defend against — not in whether streaming SQL on Kafka is worth doing at all.

This guide is the senior-DE walkthrough you wished existed the first time an interviewer asked "walk me through what happens when you type CREATE STREAM in ksqldb" or "explain the difference between a push query and a pull query against a ksqldb materialized view" or "when do you pick ksqldb vs flink SQL versus rewriting the pipeline in raw kafka streams?" It walks through the ksqlDB server plus Kafka Streams runtime under the hood, the streams-and-tables abstraction that maps SQL onto persistent topologies, the push and pull query modes and their access patterns against CREATE TABLE AS SELECT materialized views, the bundled kafka connect for I/O and the windowed operators that give you tumbling / hopping / session semantics, and the sharp real limits versus Flink SQL — batch, state, connectors, exactly-once. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

PipeCode blog header for ksqlDB — bold white headline 'ksqlDB in 2026?' with subtitle 'Streaming SQL on Kafka' and a stylised SQL scroll on the left converging with a Kafka log-stack on the right at a central purple wax seal marked 'still worth it', on a dark gradient with purple, orange, green, and blue accents and a small pipecode.ai attribution.

When you want hands-on reps immediately after reading, drill the streaming practice library →, rehearse on the SQL practice library →, and sharpen the pipeline-throughput axis with the ETL practice library →.

On this page

1. Why ksqlDB's 2026 relevance is genuinely up for debate

From 2019 peak to 2026 niche — the mature, Confluent-tied streaming-SQL engine that still ships production traffic

The one-sentence invariant: ksqlDB is a mature, feature-frozen streaming-SQL engine that wraps Kafka Streams under the hood, ships as a stable Confluent product, and remains the fastest way to put SQL over Kafka topics — but its 2026 competition (Flink SQL, Materialize, RisingWave) has grown far broader and, in some axes, deeper. The interview signal is not "ksqlDB is dead" or "ksqlDB is fine" — it's the ability to name which axis pushes you to ksqlDB and which axis pushes you off it, without hedging.

The four "must-answer" axes interviewers actually probe.

  • Engine. ksqlDB does not have its own execution runtime — it compiles SQL to a Kafka Streams topology and runs that topology inside a ksqlDB server JVM. Every capability and limit of Kafka Streams is inherited by ksqlDB; every optimisation it doesn't have (cost-based planning, adaptive parallelism) is one it can't magically add. Senior candidates open the answer with "ksqlDB is Kafka Streams with a SQL parser and a REST API in front."
  • Latency. Because the runtime is Kafka Streams and the storage is Kafka + RocksDB state stores, end-to-end latency is on the order of tens of milliseconds for typical windowed aggregations — competitive with Flink SQL for the same shape of workload and significantly faster than Kafka Connect → batch-warehouse patterns. The push-query model gives sub-second delivery of new results as events arrive; the pull-query model gives millisecond point-in-time lookups against materialized state.
  • Ecosystem. ksqlDB ships with bundled Kafka Connect — source and sink connectors are first-class, not an afterthought. The trade-off is that the ecosystem is Confluent-tied — connector coverage, cloud pricing, and the operational story assume you are living inside Confluent Cloud or Confluent Platform. Flink SQL has a broader OSS ecosystem; Materialize and RisingWave are OSS-first with less connector coverage but stronger semantics.
  • Alternative. The strongest 2026 alternative depends on the constraint. Flink SQL for broad ecosystem + batch + stream in one engine; Materialize / RisingWave for strict-serialisable streaming SQL semantics; raw Kafka Streams for full JVM control with no SQL layer. The senior signal is naming which one you'd pick given a specific constraint, not "Flink is more popular."

What the 2019 → 2023 → 2026 arc looked like.

  • 2019 peak. ksqlDB (originally KSQL) rebrands, ships materialized views and pull queries, positions itself as "the event-streaming database" — the answer to "how do I put SQL on top of Kafka without operating a separate Flink cluster?" Confluent invests heavily; the community grows fast.
  • 2020–2022 plateau. Feature velocity slows. Major additions (query upgrades, connect integration) are incremental. Meanwhile Flink 1.13+ ships SQL that closes the feature gap. Materialize goes GA with its differential-dataflow-backed streaming SQL. RisingWave launches as a Postgres-compatible streaming database.
  • 2023 pivot. Confluent acquires Immerok (Flink) and positions managed Flink as the flagship streaming compute in Confluent Cloud. ksqlDB continues to ship but is no longer the marquee streaming-SQL story. The community reads this as "ksqlDB in maintenance mode."
  • 2024–2026 reality. ksqlDB is stable, mature, and not going away — Confluent still ships it, customers still run it in production, and for simple stream-processing SQL on Kafka it remains the fastest path to production. But new greenfield projects at Confluent-native customers are increasingly Flink SQL; at non-Confluent customers, ksqlDB was never the natural choice to begin with.

What interviewers listen for.

  • Do you say "ksqlDB compiles SQL to a Kafka Streams topology" in the first two sentences when asked how it works? — senior signal.
  • Do you know the push vs pull query distinction without prompting? — required answer.
  • Do you push back on "just use ksqlDB for everything streaming" with the ecosystem + limits argument? — senior signal.
  • Do you frame the choice as "ksqlDB versus Flink SQL versus Materialize versus raw Streams" rather than as a binary "ksqlDB yes/no"? — required answer.

Worked example — the 2019 vs 2026 positioning question

Detailed explanation. A common opener: "Give me the two-minute version of how ksqlDB's positioning has changed from 2019 to 2026, and what that means for a team considering it today." The senior answer walks the arc without hedging, names the pivotal 2023 event (Confluent + Flink), and lands on a defensible decision framework. Do it in three beats: 2019 promise, 2023 pivot, 2026 reality.

  • 2019 promise. SQL over Kafka topics without a separate cluster; materialized views + pull queries as the "event streaming database" pitch.
  • 2023 pivot. Confluent acquires Immerok; Flink becomes the marquee compute; ksqlDB moves to mature-product mode.
  • 2026 reality. ksqlDB is stable and still shipped, but the marquee greenfield answer at Confluent-native shops is now Flink SQL; the ksqlDB niche is "simple streaming SQL, fast start, Confluent stack, tight scope."

Question. A team asks: "Should we start a new streaming pipeline on ksqlDB in July 2026?" Walk through the three-beat positioning and produce the decision framework.

Input.

Beat Year Positioning Signal
Promise 2019 "SQL on Kafka; event streaming DB" Confluent invests heavily
Pivot 2023 Confluent + Immerok → Flink flagship ksqlDB → maintenance mode
Reality 2026 Stable, niche, Confluent-tied Pick per constraint

Code.

-- The "fast start" that made ksqlDB famous in 2019 (still works verbatim in 2026)
CREATE STREAM pageviews (
  user_id VARCHAR,
  url     VARCHAR,
  ts      BIGINT
) WITH (
  KAFKA_TOPIC='pageviews',
  VALUE_FORMAT='JSON',
  TIMESTAMP='ts'
);

-- One SQL statement to materialize a running per-user pageview count
CREATE TABLE user_pageview_counts AS
  SELECT user_id, COUNT(*) AS views
  FROM   pageviews
  WINDOW TUMBLING (SIZE 1 MINUTE)
  GROUP  BY user_id
  EMIT   CHANGES;

-- Pull the current snapshot for a single user
SELECT views FROM user_pageview_counts WHERE user_id = 'u42';
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. The CREATE STREAM binds a Kafka topic (pageviews) to a schema. Nothing runs yet — this is metadata registration inside ksqlDB.
  2. The CREATE TABLE AS SELECT ... WINDOW TUMBLING ... EMIT CHANGES compiles a Kafka Streams topology: a group-by aggregation over 1-minute tumbling windows, materialized into a state store backed by RocksDB. The topology runs as a persistent query inside the ksqlDB server; the output topic is auto-created.
  3. EMIT CHANGES marks this as a push query at DDL time — every window update produces a new row into the output topic. Downstream consumers see continuous change events.
  4. The pull query SELECT ... WHERE user_id = 'u42' is a point-in-time lookup against the materialized state store — millisecond latency, no scan of Kafka. This is the "database" side of the "event streaming database" pitch.
  5. All of this still works verbatim in 2026. What changed is not the API; it's the surrounding ecosystem — Flink SQL now has feature parity for the same workload plus batch, and Confluent Cloud markets Flink harder than ksqlDB.

Output.

Question 2019 answer 2026 answer
Is ksqlDB the marquee streaming SQL on Kafka? yes no — Flink SQL is
Does the code above still work? yes yes
Is ksqlDB in maintenance mode? no effectively yes
Should new greenfield projects use it? usually yes only when the constraints match (Confluent + simple + fast)

Rule of thumb. ksqlDB in 2026 is a matured product, not a deprecated product. Pick it when the constraints match (Confluent stack, simple SQL, fast start); pick Flink SQL when you need broader ecosystem, batch + stream, or you're outside the Confluent ecosystem. Do not pick ksqlDB for its 2019 hype nor reject it for its 2023 slowdown; pick it against your 2026 constraints.

Worked example — the "which axis pushes you off ksqlDB?" probe

Detailed explanation. Another classic senior probe: "Give me three concrete constraints where you would not pick ksqlDB in 2026, and name what you'd pick instead for each." The trap is answering with generic FUD ("Flink is better") instead of specific constraints tied to specific alternatives. The right answer is a three-column matrix: constraint → why ksqlDB fails → what wins.

  • Constraint 1: you need batch + stream in one engine. ksqlDB is stream-only; Flink SQL wins.
  • Constraint 2: you need strict-serialisable streaming semantics. ksqlDB gives eventually consistent windowed aggregations; Materialize gives strict-serialisable.
  • Constraint 3: you are not on Confluent stack. ksqlDB is Confluent-tied; the ecosystem cost is high; Flink SQL or raw Kafka Streams is a better bet.

Question. For each of three constraints, produce (a) the ksqlDB limitation, (b) the alternative, (c) the migration or greenfield cost.

Input.

Constraint ksqlDB limitation Alternative Greenfield cost
Batch + stream unified stream-only Flink SQL equal
Strict-serialisable semantics eventually consistent Materialize / RisingWave equal (Postgres-compat)
Non-Confluent stack Confluent-tied Flink SQL / raw Streams Flink cheaper

Code.

-- ksqlDB — the pattern that FAILS "strict-serialisable"
-- Two events arriving out-of-order can produce inconsistent intermediate
-- outputs to a push subscriber before the final windowed aggregation converges.
CREATE STREAM orders_stream (
  order_id VARCHAR, user_id VARCHAR, amount DECIMAL(10,2), ts BIGINT
) WITH (KAFKA_TOPIC='orders', VALUE_FORMAT='JSON', TIMESTAMP='ts');

CREATE TABLE per_user_total AS
  SELECT user_id, SUM(amount) AS total_spend
  FROM   orders_stream
  GROUP  BY user_id
  EMIT   CHANGES;

-- A push subscriber will see intermediate outputs where per_user_total
-- can decrease then increase again as late events reshuffle windows.
Enter fullscreen mode Exit fullscreen mode 
-- Materialize / RisingWave (Postgres-compatible) — the same workload with
-- strict-serialisable outputs. The engine guarantees that every subscriber
-- observation is a valid snapshot of a consistent past state.
CREATE MATERIALIZED VIEW per_user_total AS
  SELECT user_id, SUM(amount) AS total_spend
  FROM   orders
  GROUP  BY user_id;

-- SUBSCRIBE (Materialize) or a Postgres LISTEN (RisingWave) gives push
-- semantics; every observation is strictly serialisable.
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. The ksqlDB example is a straightforward SUM per user. Under the hood, ksqlDB compiles this to a Kafka Streams aggregation. Late events, out-of-order events, and window closure logic can all produce visible intermediate outputs to push subscribers. This is fine for dashboards; it is not strict-serialisable.
  2. Materialize and RisingWave use differential dataflow (or an equivalent incremental view maintenance engine) to guarantee that every published output represents a consistent snapshot of the input. Late events cause a coordinated update, not a visible flicker.
  3. For a use case like "streaming views for dashboards," ksqlDB is fine. For a use case like "streaming views that back an API where callers must see a consistent state," Materialize / RisingWave is the correct engine.
  4. The migration cost from ksqlDB to Materialize is not zero — the SQL dialects differ (Materialize is Postgres-compatible, ksqlDB is Kafka-Streams-flavoured SQL), and the connector story is different. But the conceptual migration is straightforward: same DDL shape, same aggregations, different execution guarantees.
  5. The lesson: name the constraint (semantics) and the alternative (Materialize) with specifics. "Flink is better" is not the answer; "if you need strict-serialisable, Materialize wins on the semantics axis" is.

Output.

Axis ksqlDB Best alternative
Batch + stream stream only Flink SQL
Strict semantics eventual Materialize / RisingWave
Non-Confluent tied Flink SQL / Streams
Fast start wins —
Confluent-native SQL wins —

Rule of thumb. For every "should we use ksqlDB" question, name the constraint and the alternative that beats it on that constraint. Never answer with a global ranking; the choice is per-constraint.

Worked example — the "one paragraph elevator pitch for ksqlDB in 2026" answer

Detailed explanation. Sometimes the interviewer just wants a crisp positioning paragraph — no code, no comparison table — to see if you can compress the full story into a coherent 90-second answer. The senior signal is a paragraph that hits four beats: engine, primary use case, honest limits, positioning. Practice the paragraph until it comes out on demand.

  • Beat 1: engine. ksqlDB = Kafka Streams + SQL parser + REST + persistent-query manager.
  • Beat 2: primary use case. SQL over Kafka topics without a separate cluster; push queries for continuous output; pull queries for point-in-time lookups against materialized views.
  • Beat 3: honest limits. Stream-only; Confluent-tied ecosystem; feature-frozen; not strict-serialisable.
  • Beat 4: positioning. Pick it when constraints match (Confluent + simple + fast); pick Flink SQL / Materialize otherwise.

Question. Deliver a one-paragraph elevator pitch for ksqlDB in 2026 that a senior interviewer would accept as an opener. Bound at ~120 words.

Input.

Beat Words to hit Words to avoid
Engine "Kafka Streams", "compiled topology" "magic", "database" (loose)
Use case "push query", "pull query", "materialized" "any streaming SQL"
Limits "stream-only", "Confluent-tied", "not strict-serialisable" "deprecated", "dead"
Positioning "Flink SQL", "Materialize", "raw Streams" "always best", "always worst"

Code.

ksqlDB is Confluent's streaming-SQL layer on Kafka — a SQL parser, a REST
API, and a persistent-query manager that compiles SQL to Kafka Streams
topologies and runs them inside a ksqlDB-server JVM. You get push queries
that emit change events as new data arrives, pull queries that hit
RocksDB-backed materialized views for millisecond point-in-time reads,
and bundled Kafka Connect for source/sink I/O. It is stream-only, tied to
the Confluent ecosystem, feature-frozen since ~2023, and gives eventually
consistent windowed aggregations rather than strict-serialisable
semantics. In 2026 pick it when you're on Confluent, want SQL, want fast
start, and your scope stays inside those constraints. Pick Flink SQL for
broader ecosystem or batch+stream, Materialize/RisingWave for strict
semantics, or raw Kafka Streams for full JVM control.
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. The paragraph opens with engine: "SQL parser, REST API, persistent-query manager that compiles SQL to Kafka Streams topologies." This lands the "ksqlDB is Kafka Streams underneath" mental model immediately.
  2. The middle names the two access modes (push, pull) and the materialized-view story. This shows you know the user-facing shape without launching into DDL.
  3. The limits are named in one sentence — stream-only, Confluent-tied, feature-frozen, not strict-serialisable. Four honest constraints, no hand-waving.
  4. The positioning names three specific alternatives and the axis that pushes to each. This is what separates a senior candidate ("Flink SQL for batch + stream") from a mid-level candidate ("Flink is more popular").
  5. The paragraph is deliberately short (~120 words) — a real interview answer, not a monologue. Rehearse it until the four beats come out in order without fillers.

Output.

Beat Present in paragraph?
Engine (Streams + parser) yes
Push + pull + materialized yes
Four honest limits yes
Named alternatives per axis yes
Bounded ~120 words yes

Rule of thumb. Have a memorised, tight ksqlDB positioning paragraph. Interviewers who ask "give me the two-minute version" reward the candidate who can compress the full story without hedging. The compressed version is the tell that you understand the topic.

Senior interview question on ksqlDB's 2026 positioning

A senior interviewer often opens with: "It's July 2026. Your team is picking the streaming-SQL layer for a new Kafka-based analytics pipeline. Walk me through when you would pick ksqlDB, when you would pick Flink SQL, when you would pick Materialize or RisingWave, and when you would drop SQL entirely and write raw Kafka Streams — with the axes that drive each choice."

Solution Using a four-axis decision matrix with named winners per axis 

Streaming-SQL 2026 decision matrix
==================================

Axes:
  A. Ecosystem      = "already on Confluent stack?"
  B. Scope          = "stream-only OR batch+stream?"
  C. Semantics      = "eventually consistent OK OR strict-serialisable required?"
  D. SQL vs code    = "SQL surface required OR raw JVM control preferred?"

Winner per axis:
  A: Confluent-native  → ksqlDB (fastest start) OR Flink SQL (broader)
  A: non-Confluent     → Flink SQL (OSS ecosystem)
  B: stream-only       → ksqlDB / Materialize / RisingWave / Flink SQL
  B: batch + stream    → Flink SQL (only serious answer)
  C: eventual OK       → ksqlDB / Flink SQL
  C: strict-serialisable → Materialize / RisingWave
  D: SQL surface       → ksqlDB / Flink SQL / Materialize / RisingWave
  D: raw JVM control   → Kafka Streams (skip SQL entirely)

Pick ksqlDB when:  A=Confluent + B=stream-only + C=eventual + D=SQL + fast start needed
Pick Flink SQL when: A=non-Confluent OR B=batch+stream OR broadest ecosystem
Pick Materialize when: C=strict-serialisable + Postgres-compat wanted
Pick Kafka Streams when: D=raw code + custom stateful logic
Enter fullscreen mode Exit fullscreen mode 
-- The reference ksqlDB workload that WINS on the four axes
-- (Confluent + stream-only + eventual + SQL)
CREATE STREAM impressions (
  campaign_id VARCHAR,
  user_id     VARCHAR,
  ts          BIGINT
) WITH (
  KAFKA_TOPIC='impressions',
  VALUE_FORMAT='JSON',
  TIMESTAMP='ts'
);

CREATE TABLE impressions_per_minute AS
  SELECT campaign_id, COUNT(*) AS n
  FROM   impressions
  WINDOW TUMBLING (SIZE 1 MINUTE)
  GROUP  BY campaign_id
  EMIT   CHANGES;

-- Push subscriber for a live dashboard
SELECT campaign_id, n
FROM   impressions_per_minute
EMIT   CHANGES;

-- Pull query for a per-campaign summary API
SELECT n
FROM   impressions_per_minute
WHERE  campaign_id = 'C123';
Enter fullscreen mode Exit fullscreen mode

Step-by-step trace.

Axis Value Winner
A. Ecosystem Confluent Cloud ksqlDB (fast) or Flink SQL (broader)
B. Scope stream-only any streaming SQL
C. Semantics eventual OK for dashboards ksqlDB / Flink SQL
D. SQL surface SQL required (no JVM team) ksqlDB / Flink SQL
Fast start priority high ksqlDB
Final — ksqlDB

After the decision, the team runs a 1-week spike on ksqlDB: three DDL statements (a stream, a windowed table, a stream-table join with a users dim), a push subscriber for the dashboard, and a pull-query API for the summary endpoint. All three ship inside the week. The Flink SQL equivalent would take roughly the same effort but with more operational surface (job cluster, checkpoint config, state backend); the ksqlDB approach is genuinely simpler when the constraints match.

Output:

Metric Value
Time to first working query ksqlDB: 1 day; Flink SQL: 2–3 days
Time to production dashboard ksqlDB: 1 week; Flink SQL: 1–2 weeks
Ongoing ops surface ksqlDB: 1 server; Flink SQL: JobManager + TaskManagers + state backend
Ecosystem breadth ksqlDB: narrow (Confluent); Flink SQL: broad
Choice under stated constraints ksqlDB

Why this works — concept by concept:

  • Four-axis decomposition — the four axes (ecosystem, scope, semantics, SQL-vs-code) are orthogonal, and each has a clear winner. This decomposition prevents the "Flink is more popular so use Flink" fallacy and pushes you to name why per constraint.
  • Confluent-native fast-start — ksqlDB's operational simplicity (one server type, SQL over REST, bundled Connect) is genuinely faster to ship than Flink SQL for a small pipeline. This is the primary axis on which ksqlDB still wins.
  • Stream-only scope — ksqlDB and Flink SQL both handle streaming aggregations. Flink SQL adds batch as a separate execution mode; ksqlDB does not. If your roadmap includes batch, that's a Flink SQL push.
  • Semantics honesty — ksqlDB gives eventually consistent windowed aggregations. This is fine for dashboards but not for "the API must return a strict-serialisable state." Naming this axis is the senior signal.
  • Cost — ksqlDB's operational cost is O(1) — one server type. Flink SQL is O(job cluster + state backend + checkpoint storage). Materialize is O(single-instance for OSS; managed cost for the SaaS). Kafka Streams is O(your JVM ops). The choice picks the cheapest option that meets the constraints, not the most powerful.

Streaming
Topic — streaming
Streaming SQL and event-processing problems

Practice →

SQL Topic — sql SQL windowing and aggregation problems

Practice →

2. ksqlDB architecture — server + Kafka Streams runtime

The ksqlDB server is a thin SQL + REST + persistent-query manager wrapping a Kafka Streams runtime under the hood

The mental model in one line: the ksqlDB server is a JVM process that (1) parses SQL, (2) compiles each persistent query into a Kafka Streams topology, (3) runs the topology inside its own runtime, and (4) exposes REST + WebSocket endpoints for push/pull queries and DDL — with Kafka topics as the only durable state layer plus RocksDB state stores as local caches. Every architectural question about ksqlDB has an answer that starts with "under the hood, Kafka Streams does that."

Iconographic ksqlDB architecture diagram — a ksqlDB server card with REST + SQL parser + Kafka Streams runtime layers on the left, feeding Kafka topic stacks on the right with input, changelog, and output topics, on a light PipeCode card.

The four "must-know" axes for the architecture.

  • The server layers. REST/HTTP endpoint on top, SQL parser + planner in the middle, Kafka Streams runtime at the bottom. The server is stateless (state lives in Kafka + local RocksDB); you can run multiple servers as a cluster with shared work distribution via consumer-group rebalancing.
  • Streams vs Tables. ksqlDB inherits the Kafka Streams KStream / KTable duality — a stream is a sequence of independent events; a table is the evolving state derived from that sequence. CREATE STREAM binds to a raw Kafka topic; CREATE TABLE binds to a compacted Kafka topic (or to a materialized query result).
  • Persistent queries as compiled topologies. CREATE STREAM AS SELECT and CREATE TABLE AS SELECT create persistent queries — long-running Kafka Streams topologies that keep running until you TERMINATE them. The query manager tracks them, schedules them across the cluster, and writes their outputs to Kafka topics.
  • Storage layout. Every input, intermediate, and output stream is a Kafka topic. Every table with state uses a changelog topic for durability plus a local RocksDB state store per partition for fast reads. The pattern is "Kafka is the source of truth; RocksDB is the cache" — same as Kafka Streams itself.

The ksqldb server process, in detail.

  • Ingress. REST endpoint on port 8088 by default. Accepts DDL (CREATE STREAM ...), push queries (SELECT ... EMIT CHANGES), pull queries (SELECT ... WHERE key = ...), and admin commands (SHOW QUERIES, TERMINATE).
  • Compile. Incoming SQL is parsed, resolved against the metadata store, and compiled into a Kafka Streams topology (a directed graph of processor nodes with source, transform, join, aggregate, and sink nodes).
  • Run. The topology starts as an active Kafka Streams application. It consumes from the source topics, processes the events, writes to changelog topics (for stateful operators), and produces to sink topics. Multiple servers in a cluster share partitions via consumer-group rebalancing.
  • Serve. Push queries subscribe to running topologies and stream their output via WebSocket to the client. Pull queries query the local RocksDB state store on whichever server owns the target key's partition (with a proxy hop if needed).

The Streams-and-Tables abstraction.

  • KStream. An append-only event sequence — every record is treated as a new fact. Analogous to an insert-only Kafka topic. Operations: map, filter, flatMap, join with KTable, groupBy (produces KTable).
  • KTable. An evolving state — every record is treated as an update to the state for its key. Analogous to a compacted Kafka topic where the latest value per key wins. Operations: filter, mapValues, join with KStream, join with KTable, groupBy (produces KTable).
  • Duality. Every stream can be aggregated into a table; every table changelog is a stream. This duality is the source of ksqlDB's power — you can freely convert between event sequences and materialized state with SQL.
  • When to use which. Use CREATE STREAM for source topics with append-only semantics (page views, orders, click events). Use CREATE TABLE for source topics with update semantics (user profiles, product catalog) or for query results (aggregations).

Persistent queries — the runtime unit.

  • Definition. A persistent query is a long-running Kafka Streams topology created by CREATE STREAM AS SELECT or CREATE TABLE AS SELECT. It keeps running until explicitly terminated.
  • Lifecycle. SHOW QUERIES lists all persistent queries; TERMINATE <query_id> stops one. Terminating does not drop the output topic; you DROP STREAM/TABLE for that.
  • State. Persistent queries with aggregations or joins have local state (RocksDB) + a changelog topic (Kafka). On restart, the state is rebuilt from the changelog.
  • Parallelism. Determined by the source topic's partition count. Each partition maps to a stream task inside a Streams runtime instance; more partitions = more parallelism (up to the cluster's total task capacity).

Common interview probes on the architecture.

  • "How does ksqlDB run SQL?" — compiles to Kafka Streams topology; runs inside ksqlDB server JVM.
  • "What is the difference between a stream and a table?" — append-only events vs evolving state per key.
  • "What's a persistent query?" — long-running Streams topology from CREATE ... AS SELECT.
  • "Where is the state stored?" — Kafka changelog topics + local RocksDB per partition.

Worked example — the 4-line CREATE STREAM + SELECT

Detailed explanation. The canonical "hello world" for ksqlDB — a single CREATE STREAM DDL followed by a push query. Nothing shows the parser + compiler + runtime + REST stack more compactly. Walk an interviewer through what happens at every stage: parse → resolve → compile → run → subscribe → deliver.

  • DDL. CREATE STREAM pageviews (...) WITH (KAFKA_TOPIC='pageviews', VALUE_FORMAT='JSON') — parsed and registered in the metadata store; no runtime activity yet.
  • Query. SELECT * FROM pageviews EMIT CHANGES; — compiled to a Kafka Streams source topology; runs as a transient query; delivers to the client via WebSocket.
  • State. None — pure passthrough. The topology has one source node, no state stores, one WebSocket sink.

Question. Walk through the 4-line ksqlDB program and describe (a) what the server does at each stage, (b) which Kafka topics it touches, (c) the latency of the first result reaching the client.

Input.

Component Value
Source topic pageviews (JSON, 3 partitions)
Server 1 ksqlDB server, default config
Client ksqlDB CLI or REST client
Event rate 1000 events/sec

Code.

-- Step 1 — DDL: register the stream (no runtime yet)
CREATE STREAM pageviews (
  user_id VARCHAR KEY,
  url     VARCHAR,
  ts      BIGINT
) WITH (
  KAFKA_TOPIC='pageviews',
  VALUE_FORMAT='JSON',
  TIMESTAMP='ts'
);

-- Step 2 — a transient push query (starts a topology; runs until client disconnects)
SELECT user_id, url, ts
FROM   pageviews
EMIT   CHANGES;
Enter fullscreen mode Exit fullscreen mode 
# What the ksqlDB server does under the hood (annotated)
parse:
  input: "SELECT user_id, url, ts FROM pageviews EMIT CHANGES;"
  output: parsed AST with source=pageviews, projection=[user_id, url, ts], emit=CHANGES

resolve:
  metadata: pageviews → (topic=pageviews, format=JSON, columns=[user_id, url, ts])
  schema: matches; projection valid

compile:
  topology:
    - source: KafkaConsumer(topic="pageviews", partitions=[0,1,2])
    - projection: (user_id, url, ts) → same
    - sink: WebSocketPublisher(client=<connection>)

run:
  streams-app: creates Kafka consumer group "_confluent-ksql-<hash>-transient-<queryid>"
  assignment: 3 partitions assigned across available tasks
  poll-loop: begins polling; each poll fetches ~500 records; emits to WebSocket

deliver:
  latency: end-to-end p50 ~50ms (Kafka commit + poll interval)
  latency: end-to-end p99 ~200ms (rebalance edge cases)
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. Parse. The CREATE STREAM is a DDL statement — the parser produces an AST, the metadata store persists the definition (ksql_command_topic in Kafka), and the server returns immediately. No runtime activity yet.
  2. Resolve + compile. The SELECT ... EMIT CHANGES is a query. The parser produces an AST, resolves pageviews against the metadata store, and the planner compiles the query into a Kafka Streams topology — a source node (Kafka consumer on the pageviews topic), a projection node (identity here), and a sink node (a WebSocket publisher back to the client).
  3. Run. The topology starts as a Kafka Streams application inside the ksqlDB server. It creates a consumer group with a unique ID, joins the group, and gets assignments for the 3 partitions. Each partition is processed by one stream task in parallel.
  4. Deliver. The topology polls Kafka every 100 ms (configurable), processes each batch of records, and pushes them to the client's WebSocket. End-to-end latency is dominated by the poll interval + Kafka commit; p50 ~50 ms, p99 ~200 ms for typical configs.
  5. Cleanup. When the client disconnects, the transient query is stopped, the consumer group leaves, and the topology tears down. The consumer offsets are discarded (transient queries don't persist offsets).

Output.

Stage Duration State touched
Parse <1 ms AST in memory
DDL persist ~5 ms ksql_command_topic
Query compile ~10 ms Streams topology object
Consumer join ~500 ms (one-time) consumer group metadata
First result to client ~50 ms after join Kafka poll + WebSocket send

Rule of thumb. The 4-line ksqlDB program is the shortest complete demonstration of the parser + compiler + runtime + REST layers. Rehearse it — interviewers who ask "walk me through what happens when you type SELECT ... EMIT CHANGES" reward the candidate who can name every stage.

Worked example — Streams vs Tables in a real pipeline

Detailed explanation. The Streams-vs-Tables distinction is the mental model that makes ksqlDB (and Kafka Streams) intuitive. A concrete example: an orders topic (append-only events) and a users topic (compacted, one row per user). The user profile is a table; the orders are a stream. A stream-table join enriches each order with the current user profile. Walk through the DDL and the join semantics.

  • orders topic. Append-only; every record is a distinct order event. Use CREATE STREAM.
  • users topic. Compacted; the latest record per user is the current profile. Use CREATE TABLE.
  • Join semantics. For each order event, look up the current user profile by user_id and produce an enriched output. This is a stream-table join — the stream drives the output, the table provides the lookup.

Question. Model the orders and users topics correctly as stream vs table, write the stream-table join, and describe what happens if the user profile updates after an order has been processed.

Input.

Topic Semantics Modeling in ksqlDB
orders append-only events CREATE STREAM
users compacted, current profile CREATE TABLE
orders_enriched append-only join result CREATE STREAM AS SELECT ...

Code.

-- The stream — orders events (append-only)
CREATE STREAM orders (
  order_id VARCHAR KEY,
  user_id  VARCHAR,
  amount   DECIMAL(10,2),
  ts       BIGINT
) WITH (
  KAFKA_TOPIC='orders',
  VALUE_FORMAT='JSON',
  TIMESTAMP='ts'
);

-- The table — user profiles (compacted, key = user_id)
CREATE TABLE users (
  user_id  VARCHAR PRIMARY KEY,
  name     VARCHAR,
  country  VARCHAR,
  tier     VARCHAR
) WITH (
  KAFKA_TOPIC='users',
  VALUE_FORMAT='JSON'
);

-- The stream-table join — enrich each order with the current user profile
CREATE STREAM orders_enriched AS
  SELECT o.order_id,
         o.user_id,
         o.amount,
         u.name    AS user_name,
         u.country AS user_country,
         u.tier    AS user_tier,
         o.ts
  FROM   orders o
  LEFT JOIN users u ON o.user_id = u.user_id
  EMIT   CHANGES;
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. CREATE STREAM orders declares the orders topic as an append-only stream. The KEY on order_id tells ksqlDB the Kafka record key; the value is the JSON payload with user_id, amount, ts. Every record is treated as a new fact.
  2. CREATE TABLE users declares the users topic as a table keyed by user_id. The Kafka topic must be log-compacted for this to work correctly — Kafka's compaction keeps only the latest value per key, and ksqlDB relies on that to serve the "current" profile.
  3. CREATE STREAM orders_enriched AS ... LEFT JOIN compiles to a Kafka Streams stream.leftJoin(table) topology. Every incoming order event triggers a lookup against the local RocksDB copy of the users table by user_id; the enriched record is emitted to the output topic.
  4. What happens if the profile updates after the order. The stream-table join uses the current value of the table at the time the stream event arrives. If a user's tier updates from silver to gold at 10:00 and an order arrives at 10:01, the enriched order shows gold. An order at 09:59 was enriched with silver and stays that way in the output topic — the join is not retroactive.
  5. Why not a stream-stream join. A stream-stream join would require a windowing predicate ("orders and profiles within N minutes of each other"). That's the wrong semantics for "current profile at order time" — profiles aren't events; they're state. The stream-table model is the correct one.

Output.

Order event Users table state at that moment orders_enriched output
order-1 (user=u42, ts=09:59) u42: name=Ada, tier=silver order-1, Ada, silver
profile update (u42, tier=gold, ts=10:00) u42: name=Ada, tier=gold (no order event; no output)
order-2 (user=u42, ts=10:01) u42: name=Ada, tier=gold order-2, Ada, gold
order-3 (user=u99, ts=10:02) u99: not present order-3, NULL, NULL (LEFT JOIN)

Rule of thumb. Use CREATE STREAM for append-only event topics; use CREATE TABLE for compacted state topics. The stream-table join is the correct primitive for "enrich each event with current state." If your topic isn't compacted, either compact it or model it as a stream + aggregate to a table.

Worked example — persistent query lifecycle

Detailed explanation. Every persistent query in ksqlDB is a long-running Kafka Streams topology. Understanding its lifecycle — creation, monitoring, upgrade, termination — is the single most operationally important skill for running ksqlDB in production. Walk through the four lifecycle stages against a real query.

  • Create. CREATE STREAM|TABLE AS SELECT ... EMIT CHANGES starts a persistent query. The query gets a generated ID; the output topic is auto-created; the topology begins running.
  • Monitor. SHOW QUERIES lists queries; DESCRIBE <name> shows the topology; consumer-lag metrics on the query's consumer group show throughput and freshness.
  • Upgrade. ksqlDB has query upgrade support (limited): schema-compatible changes can hot-swap; incompatible changes require terminate + recreate.
  • Terminate. TERMINATE <query_id> stops the topology. The output topic and RocksDB state are not dropped; use DROP STREAM|TABLE ... DELETE TOPIC for a full cleanup.

Question. Walk through the full lifecycle of a persistent query — creation, monitoring, upgrade attempt, and clean termination — and describe what breaks and what persists at each step.

Input.

Query DDL Kind
pageview_counts CREATE TABLE ... AS SELECT COUNT(*) FROM pageviews GROUP BY user_id persistent
Kafka partitions 6 (source topic) —
ksqlDB cluster 2 servers —

Code.

-- Step 1 — CREATE (starts a persistent query, generates a query ID)
CREATE TABLE pageview_counts AS
  SELECT user_id, COUNT(*) AS views
  FROM   pageviews
  GROUP  BY user_id
  EMIT   CHANGES;

-- Response includes: "CREATED_TABLE: CTAS_PAGEVIEW_COUNTS_0"

-- Step 2 — MONITOR
SHOW QUERIES;
-- Query ID                     | Query Type | Status  | Sink Name       | Sink Kafka Topic
-- CTAS_PAGEVIEW_COUNTS_0       | PERSISTENT | RUNNING | PAGEVIEW_COUNTS | PAGEVIEW_COUNTS

DESCRIBE PAGEVIEW_COUNTS EXTENDED;
-- Shows: schema, topology, consumer group, per-partition offsets, task metrics

-- Step 3 — UPGRADE attempt (add a column — schema-incompatible for this DDL shape)
-- ksqlDB rejects incompatible in-place upgrades; workflow is terminate + recreate.
TERMINATE CTAS_PAGEVIEW_COUNTS_0;

DROP TABLE pageview_counts DELETE TOPIC;

CREATE TABLE pageview_counts AS
  SELECT user_id,
         COUNT(*) AS views,
         MAX(ts)  AS last_view_ts       -- new column
  FROM   pageviews
  GROUP  BY user_id
  EMIT   CHANGES;

-- Step 4 — CLEAN TERMINATE
TERMINATE CTAS_PAGEVIEW_COUNTS_1;
DROP TABLE pageview_counts DELETE TOPIC;
-- Now: no persistent query, no output topic, no changelog topic, no local RocksDB state.
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. Create. The CREATE TABLE ... AS SELECT returns a query ID (CTAS_PAGEVIEW_COUNTS_0). Under the hood, ksqlDB (a) creates the PAGEVIEW_COUNTS output topic, (b) creates a changelog topic for the RocksDB state store, (c) compiles the topology, (d) starts it as a Kafka Streams application in a new consumer group.
  2. Monitor. SHOW QUERIES lists the query and its status. DESCRIBE ... EXTENDED shows the topology graph, the consumer group offsets per partition, and per-task metrics (records/sec, state store size, restore progress). The consumer-group lag on the source topic is the primary freshness indicator.
  3. Upgrade attempt. ksqlDB's in-place upgrade support is limited — some cases work (adding a null-safe column projection), most don't (adding an aggregate column). The safe workflow is: terminate old, drop with DELETE TOPIC, recreate. This is a destructive migration; you must be OK with reprocessing the source from the retention window.
  4. Terminate. TERMINATE <query_id> stops the topology. Critically, the output topic and RocksDB state persist. The output topic remains for downstream consumers; the RocksDB state can be restored if the query is re-created with the same name. Use DROP ... DELETE TOPIC for full cleanup.
  5. What breaks and what persists. During upgrade-via-recreate, downstream consumers of the output topic see a gap (or reprocessing) — plan for that. During TERMINATE alone, the output topic freezes at its last update. During DROP ... DELETE TOPIC, everything is gone: topology, output topic, changelog topic, RocksDB state.

Output.

Action Persistent query Output topic Changelog topic RocksDB state Downstream
CREATE starts created created populates starts receiving
SHOW QUERIES — — — — —
TERMINATE stops frozen frozen frozen frozen
DROP ... DELETE TOPIC gone deleted deleted discarded broken until recreated

Rule of thumb. Treat every persistent query as a long-lived infrastructure component with a documented lifecycle. Terminate + drop before recreate; account for reprocessing time from the source topic retention window; test the upgrade path in staging before production. Interview-level candidates know the four lifecycle stages by heart.

Senior interview question on the ksqlDB architecture

A senior interviewer might ask: "Draw the ksqlDB architecture from client REST call through Kafka topics, name the four layers of the ksqlDB server, and walk me through what happens end-to-end when I run CREATE TABLE user_totals AS SELECT user_id, SUM(amount) FROM orders GROUP BY user_id EMIT CHANGES; on a 3-server cluster with a 6-partition source topic."

Solution Using the four-layer architecture + partition-parallel topology walkthrough 

ksqlDB architecture — four layers, one JVM per server
=====================================================

┌───────────────────────────────────────────────────┐
│                ksqlDB server (JVM)                │
│                                                   │
│  1. REST/WebSocket layer (port 8088)              │
│     ├── DDL endpoint                              │
│     ├── push-query WebSocket endpoint             │
│     └── pull-query REST endpoint                  │
│                                                   │
│  2. SQL parser + planner                          │
│     ├── ANTLR grammar                             │
│     ├── logical plan                              │
│     └── physical plan (Kafka Streams topology)    │
│                                                   │
│  3. Persistent-query manager                      │
│     ├── metadata store (Kafka: _confluent_ksql)   │
│     ├── query registry (SHOW QUERIES)             │
│     └── query lifecycle (start/stop/upgrade)      │
│                                                   │
│  4. Kafka Streams runtime                         │
│     ├── source nodes (KafkaConsumer per topic)    │
│     ├── processor nodes (map/filter/join/agg)    │
│     ├── state stores (RocksDB + changelog)        │
│     └── sink nodes (KafkaProducer per topic)      │
│                                                   │
└───────────────────────────────────────────────────┘
                        │
              Kafka cluster (brokers)
              ├── source topic: orders (6 partitions)
              ├── changelog:   _confluent-ksql-...-changelog (6 partitions)
              └── sink topic:  USER_TOTALS (6 partitions)
Enter fullscreen mode Exit fullscreen mode 
-- The query
CREATE TABLE user_totals AS
  SELECT user_id, SUM(amount) AS total
  FROM   orders
  GROUP  BY user_id
  EMIT   CHANGES;
Enter fullscreen mode Exit fullscreen mode

Step-by-step trace.

Stage Layer What happens
1 REST Client POSTs SQL to /ksql; server responds with query ID after compile
2 Parser ANTLR parses SQL → AST
3 Planner Logical plan: Scan(orders) → GroupBy(user_id) → Sum(amount) → Sink(USER_TOTALS)
4 Planner Physical plan: Kafka Streams topology with source, aggregation, state store, sink
5 Query mgr Persists DDL to _confluent-ksql-command_topic (durable audit log)
6 Streams Creates consumer group _confluent-ksql-...-CTAS_USER_TOTALS_0
7 Streams Joins group; 6 partitions distributed across 3 servers (~2 per server)
8 Streams Each task consumes its partition; groups by user_id (repartition if needed); aggregates in RocksDB
9 Streams Every update writes to (a) RocksDB local, (b) changelog topic (durability), (c) USER_TOTALS sink topic
10 REST Push subscribers get updates via WebSocket; pull queries hit RocksDB on the owning server

After the query starts, the cluster processes the source topic in parallel across 6 partitions distributed over 3 servers. Each server handles ~2 partitions worth of state in local RocksDB. The changelog topic is the durability layer — on any server crash, the surviving servers can restore state by replaying the changelog. The sink topic (USER_TOTALS) is the final materialized state, updated in real time as source events arrive.

Output:

Property Value
Consumer group _confluent-ksql-<cluster-id>-CTAS_USER_TOTALS_0
Source partitions 6
Tasks 6 (one per partition)
Tasks per server (3-server cluster) 2
Changelog topic partitions 6 (matches source)
Sink topic partitions 6 (matches source)
End-to-end p99 latency ~200 ms (Kafka commit + Streams poll)
State store type RocksDB (per partition, per server)
Restore mechanism changelog replay

Why this works — concept by concept:

  • Four layers, one JVM — REST + parser + query manager + Streams runtime all live in one process. The server is stateless (state is Kafka + local RocksDB); this makes horizontal scaling trivial — add more servers to the cluster, and the consumer-group rebalance redistributes partitions.
  • ANTLR-parsed SQL → physical plan — the compile stage produces a Kafka Streams topology, not a proprietary plan. This is why "ksqlDB is Kafka Streams with a SQL parser" is literally true.
  • Persistent-query manager — the durable audit log (_confluent-ksql-command_topic) is how every server in the cluster learns about every query. On server restart, the manager replays the command topic to recreate the query set.
  • Kafka Streams runtime + RocksDB — state per partition lives in local RocksDB, with the changelog topic as the durability layer. Restore = replay changelog. This is the same pattern as every other Kafka Streams app.
  • Cost — one JVM per server plus Kafka brokers. State cost is dominated by RocksDB size, which is proportional to the cardinality of aggregation keys. Network cost is 3× the source data (source → changelog → sink) — plan for this in Kafka broker sizing.

Streaming
Topic — streaming
Streaming architecture and topology problems

Practice →

SQL Topic — sql SQL DDL and query-compilation problems

Practice →

3. Push queries vs pull queries + materialized views

push query streams change events; pull query snapshots a materialized view — same table, two access modes

The mental model in one line: a push query subscribes to a running query's output stream and receives every update as it happens; a pull query executes once against the current state of a materialized view (a ksqldb materialized view) and returns a point-in-time snapshot — the same underlying CREATE TABLE AS SELECT result is accessible in both modes, and picking the right mode per use case is the second-most-important skill after picking the right pool mode. Push is for continuous consumers; pull is for on-demand queries.

Iconographic push vs pull query diagram — a subscribe-glyph on the left labelled push and a snapshot-glyph on the right labelled pull, both accessing a materialized-table card in the middle backed by RocksDB, on a light PipeCode card.

The two access modes side by side.

  • Push query — subscribe. SELECT ... EMIT CHANGES; opens a WebSocket subscription to a running topology and delivers every new output record as it's produced. Runs indefinitely until the client disconnects. Latency = time between event arrival and delivery = tens of milliseconds.
  • Pull query — snapshot. SELECT ... WHERE key = ...; executes a single point-in-time lookup against the materialized RocksDB state and returns the current value. Latency = single-digit milliseconds.
  • Same underlying table. Both modes access the same materialized view (CREATE TABLE ... AS SELECT ...). The mode is chosen by the client per query, not by the table's DDL.

Push queries — when and how.

  • Use case. Live dashboards, streaming exports, real-time monitoring, downstream stream-processing consumers.
  • API. Client opens a WebSocket to /query-stream (v2 endpoint) or /query (v1); server holds the connection open and pushes records as they're produced.
  • Failure mode. If the client disconnects and reconnects, it does not automatically resume — the client must re-issue the query. There is no offset checkpointing at the client level; downstream consumers that need durability should read the underlying Kafka topic directly.
  • Latency budget. End-to-end p50 ~50 ms, p99 ~200 ms — dominated by Kafka poll interval and WebSocket send.

Pull queries — when and how.

  • Use case. Point-in-time lookups for an API endpoint, on-demand debugging, snapshot exports.
  • API. HTTP POST to /query with a SELECT ... WHERE key = ... query.
  • Constraints. The WHERE clause must key by the primary key of the materialized table (or match a well-defined key range for the query planner to route to the right partition owner). Non-key WHERE clauses are limited or unsupported.
  • Server routing. The pull query is routed to the ksqlDB server that owns the partition for the target key. If it lands on a different server first, the server proxies internally. All of this is transparent to the client.
  • Latency budget. p50 ~5 ms, p99 ~20 ms — RocksDB local read + one network hop (or two if a proxy is needed).

Materialized views — CREATE TABLE AS SELECT under the hood.

  • DDL. CREATE TABLE t AS SELECT ... FROM source GROUP BY key ... creates a persistent query. The persistent query aggregates the source into a KTable; the KTable is backed by a RocksDB state store per partition; every update writes to (a) RocksDB local, (b) a changelog topic, (c) the output sink topic.
  • Storage. RocksDB inside each ksqlDB server's local disk. Sized by cardinality of aggregation keys × average value size. Backed by the changelog topic for durability.
  • Consistency. Eventually consistent per key — updates propagate in Kafka's order. Not strict-serialisable; two clients pulling different keys at nearly the same wall clock time may see different logical timestamps.
  • Recovery. On server restart, the RocksDB state is rebuilt from the changelog topic. This can take minutes for large tables; downtime windows should account for restore time.

The rolling-window aggregation pattern.

  • DDL. WINDOW TUMBLING (SIZE 5 MINUTES) groups events into fixed 5-minute windows. Alternatives: HOPPING (SIZE 5 MINUTES, ADVANCE BY 1 MINUTE) for overlapping windows; SESSION (INACTIVITY GAP 30 SECONDS) for user-session windows.
  • Semantics. Every window emits changes as new events arrive — a window "closes" when the grace period elapses beyond its end. Push subscribers see intermediate updates.
  • Time. Uses event-time (TIMESTAMP='ts') by default; falls back to processing-time if no event timestamp is defined.

Common interview probes on push/pull/materialize.

  • "What's a push query?" — subscribe to a running query's output stream.
  • "What's a pull query?" — point-in-time snapshot query against a materialized state store.
  • "Why is a materialized view fast to query?" — RocksDB local read, keyed lookup, single-digit millisecond latency.
  • "What's the durability story for the materialized view?" — changelog topic in Kafka.

Worked example — rolling-window aggregation with push + pull

Detailed explanation. The single most common ksqlDB pattern: a 1-minute tumbling window per user aggregation with (a) a push subscriber for a live dashboard and (b) a pull query for a per-user summary API. Walk through the DDL and both access modes.

  • Source. pageviews stream — one event per pageview, keyed by user_id.
  • Materialized view. Per-user pageview count, 1-minute tumbling windows.
  • Push consumer. Live dashboard that shows the counts updating in real time.
  • Pull consumer. A summary API that returns "how many pageviews did user X have in the current window?"

Question. Write the DDL for the materialized view, the push query for the dashboard, and the pull query for the API — with a walk through what each does at query time.

Input.

Component Value
Source topic pageviews (6 partitions, keyed by user_id)
Window 1-minute tumbling
Aggregation COUNT(*) per user per window
Dashboard live push subscriber
API on-demand pull query

Code.

-- 1. The source stream
CREATE STREAM pageviews (
  user_id VARCHAR KEY,
  url     VARCHAR,
  ts      BIGINT
) WITH (
  KAFKA_TOPIC='pageviews',
  VALUE_FORMAT='JSON',
  TIMESTAMP='ts'
);

-- 2. The materialized view — 1-minute tumbling window per user
CREATE TABLE pageviews_per_minute AS
  SELECT user_id,
         WINDOWSTART AS window_start,
         WINDOWEND   AS window_end,
         COUNT(*)    AS views
  FROM   pageviews
  WINDOW TUMBLING (SIZE 1 MINUTE)
  GROUP  BY user_id
  EMIT   CHANGES;

-- 3. Push query — subscribe to the live stream of updates for the dashboard
SELECT user_id, window_start, views
FROM   pageviews_per_minute
EMIT   CHANGES;

-- 4. Pull query — snapshot the current window's count for user 'u42'
SELECT views
FROM   pageviews_per_minute
WHERE  user_id = 'u42'
  AND  WINDOWSTART = '2026-07-06T14:30:00.000';
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. The stream pageviews binds to the source Kafka topic. Every record is an append-only event; keyed by user_id.
  2. The materialized view pageviews_per_minute compiles to a Kafka Streams topology: source (pageviews) → group-by-key (user_id) → windowed aggregate (1-minute tumbling COUNT) → sink (PAGEVIEWS_PER_MINUTE topic) + state store (RocksDB, keyed by (user_id, window_start)). Every incoming pageview increments the count for the (user, window) key and emits an update.
  3. The push query for the dashboard opens a WebSocket subscription. The dashboard receives every update as it happens — user X's count for the current window ticks up in real time. If the dashboard disconnects and reconnects, it re-issues the query and starts from the current state (there is no client-side offset checkpoint).
  4. The pull query for the API takes a user_id and a WINDOWSTART and returns the current count. Under the hood, ksqlDB routes the query to the server that owns the partition for u42, does a keyed RocksDB read ((u42, 14:30) → count), and returns the value. Latency is a few milliseconds.
  5. Why pull queries need the window key. The state store is keyed by (user_id, window_start). A pull query without the window key would need a range scan over all windows for the user — which ksqlDB does support with WINDOWSTART >= X AND WINDOWSTART < Y but at higher cost. For a single-window lookup, provide both keys.

Output.

Query type Access pattern Latency Use case
Push (EMIT CHANGES) subscribe to WebSocket p50 50 ms, p99 200 ms live dashboard
Pull single-window keyed RocksDB read p50 5 ms, p99 20 ms summary API
Pull range-scan RocksDB range scan p50 20 ms, p99 100 ms user history API
No window key not supported without range — —

Rule of thumb. For every materialized view, plan both access modes explicitly. Push is for continuous consumers; pull is for on-demand APIs. Design the view's key + window to match your pull-query pattern, not just your push-subscriber use case.

Worked example — pull-query routing across the cluster

Detailed explanation. In a multi-server ksqlDB cluster, the materialized state is partitioned — server A owns partitions 0–1, server B owns partitions 2–3, server C owns partitions 4–5. When a pull query arrives, it hits whichever server the client's load balancer chose, and that server may or may not own the target partition. The internal routing story (proxy hop or direct answer) is a common interview question.

  • Partitioning. By source-topic partitioning (typically hash(user_id) % 6).
  • Routing. The receiving server computes the partition for the query's key, looks up which server owns that partition (via the ksqlDB cluster metadata), and either answers directly or proxies to the owning server.
  • Cost. One extra network hop for the ~2/3 of queries that don't land on the right server (in a 3-server cluster).

Question. Design the routing story for a 3-server cluster with a 6-partition source topic, and walk through the two paths (direct answer vs proxy hop) with latency numbers.

Input.

Component Value
Servers ksql-1, ksql-2, ksql-3
Source topic partitions 6
Partitioning hash(user_id) % 6
Ownership ksql-1: 0-1, ksql-2: 2-3, ksql-3: 4-5
Load balancer round-robin

Code.

-- Client-side pull query
SELECT views
FROM   pageviews_per_minute
WHERE  user_id = 'u42';
Enter fullscreen mode Exit fullscreen mode 
# What the ksqlDB server does under the hood
resolve:
  input_key: "u42"
  partition: hash("u42") % 6 = 3
  owner:     ksql-2

path_A_direct:
  # 1/3 of the time — load balancer sent to ksql-2 directly
  server: ksql-2
  action: read RocksDB (partition 3, key "u42")
  latency_p50: 5 ms
  latency_p99: 20 ms

path_B_proxy:
  # 2/3 of the time — load balancer sent to ksql-1 or ksql-3
  server_received: ksql-1
  server_owning:   ksql-2
  action_1: ksql-1 forwards HTTP call to ksql-2
  action_2: ksql-2 reads RocksDB (partition 3, key "u42")
  action_3: ksql-2 returns to ksql-1; ksql-1 returns to client
  latency_p50: 8 ms   # +1 internal hop
  latency_p99: 30 ms  # +1 internal hop with tail
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. The client's pull query hits the load balancer, which round-robins to one of the three servers. In a 3-server cluster, roughly 1/3 of queries land on the right server (direct answer) and 2/3 land on a wrong server (proxy hop).
  2. Every server has the ksqlDB cluster metadata — it knows which partitions each server owns. When a query arrives, the receiving server (a) parses the SQL, (b) extracts the key, (c) computes the partition, (d) looks up the owner.
  3. Path A (direct). The receiving server owns the partition. It performs a RocksDB read on the local state store and returns immediately. Latency is bounded by RocksDB read + response serialisation.
  4. Path B (proxy). The receiving server doesn't own the partition. It opens an internal HTTP call to the owning server, forwards the query, receives the answer, and returns it to the client. Latency has one extra network hop (~1–5 ms in a typical cluster).
  5. Why not client-side routing. ksqlDB could expose the partition-ownership map to clients so they could route directly — but the operational simplicity of "any server can answer any query" is worth the extra hop. Client-side routing is planned/optional; the default is server-side proxy.

Output.

Path Probability (3-server) Latency p50 Latency p99
Direct 33% 5 ms 20 ms
Proxy (1 hop) 67% 8 ms 30 ms
Weighted avg 100% 7 ms 27 ms

Rule of thumb. For latency-sensitive pull-query APIs, be aware that ~2/3 of your queries pay a proxy-hop cost on a small cluster. If p99 pull-query latency matters, either (a) accept the ~30 ms p99 as the design point, (b) run more servers to reduce the miss rate, or (c) route on the client side using ksqlDB's partition-ownership map. For most APIs the extra hop is invisible.

Worked example — pull-query range scans and their cost

Detailed explanation. A common follow-up: "Can I pull-query a whole range of keys, not just a single key?" The answer is yes but: ksqlDB supports keyed range scans with WHERE key >= X AND key < Y, but the cost is proportional to the number of matched keys, and the latency stops being millisecond-fast. Understand where the cliff is.

  • Single-key pull. RocksDB seek to key; return single value. O(log N) with tiny constants; single-digit ms.
  • Range-scan pull. RocksDB seek to start key; iterate to end key. O(matched keys) with cache-friendly iteration; tens of ms to seconds depending on match size.
  • Full-scan. Not supported — a pull query without a key predicate returns an error. Use a push query or a downstream Kafka consumer for full-scan patterns.

Question. Given the materialized view pageviews_per_minute (keyed by (user_id, WINDOWSTART)), write pull queries for (a) a single window for a single user, (b) all windows for a single user in the last hour, (c) a top-K query — and describe which one ksqlDB supports and the latency cost.

Input.

Query type Predicate Support Latency
Single (user, window) user_id = X AND WINDOWSTART = T yes ~5 ms
User range user_id = X AND WINDOWSTART >= T1 AND WINDOWSTART < T2 yes ~20 ms per matched window
Top-K per window ORDER BY views DESC LIMIT 10 no (not a pull-query pattern) —

Code.

-- (a) Single window for a single user — the ideal pull
SELECT views
FROM   pageviews_per_minute
WHERE  user_id = 'u42'
  AND  WINDOWSTART = '2026-07-06T14:30:00.000';
-- ~5 ms p50

-- (b) All windows for a single user in the last hour — range scan
SELECT WINDOWSTART, views
FROM   pageviews_per_minute
WHERE  user_id = 'u42'
  AND  WINDOWSTART >= '2026-07-06T13:30:00.000'
  AND  WINDOWSTART <  '2026-07-06T14:30:00.000';
-- 60 windows expected; ~60 × 5 ms = ~300 ms p50 for the scan
-- Actually ~50–100 ms in practice because RocksDB iteration is cache-friendly

-- (c) Top-K per window — NOT supported as a pull query
-- Solutions:
--   1. Materialize a "top-K per window" table with a persistent query and pull it
--   2. Use a push query and rank client-side
--   3. Consume the sink topic externally and rank there

-- Solution 1 — pre-materialize the top-K
CREATE TABLE top_k_per_window AS
  SELECT window_start, TOPK_DISTINCT(user_id, 10) AS top10
  FROM   pageviews_per_minute
  GROUP  BY window_start
  EMIT   CHANGES;

-- Then pull-query by window_start
SELECT top10
FROM   top_k_per_window
WHERE  window_start = '2026-07-06T14:30:00.000';
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. Query (a) — single key. RocksDB does a keyed seek and returns the value. The cost is dominated by the RocksDB read + one server hop; ~5 ms is a reasonable p50.
  2. Query (b) — range scan. RocksDB iterates from the start key to the end key. Because RocksDB iteration is cache-friendly (LSM SSTable reads are sequential), the cost is closer to 50–100 ms for 60 rows rather than 60 × 5 ms. Still much slower than a single-key lookup.
  3. Query (c) — top-K. Not supported as a pull query in ksqlDB — the state store is keyed by (user_id, window_start), not by any global ranking. You cannot express "top 10 users by views in window T" as a pull.
  4. Solution 1 — pre-materialize. The senior pattern: if you want a specific query shape, materialize it. Create a second table top_k_per_window that materializes the top-10 users per window; then pull-query that table by window_start. The cost is paid at write time (during the aggregation) rather than at read time.
  5. Solutions 2 and 3. For one-off analyses or exploratory queries, push subscribers can rank client-side. For downstream batch processing, the sink topic can be consumed externally and ranked there. Pull queries are for pre-planned key-based access, not ad-hoc analysis.

Output.

Query Support Latency p50 Latency p99
Single (user, window) yes 5 ms 20 ms
User × 60 windows range yes 50 ms 200 ms
Top-K per window (direct) no — —
Top-K per window (materialized) yes (pre-materialized) 5 ms 20 ms

Rule of thumb. Pull queries are for keyed access to pre-materialized state. If a query pattern isn't keyed, pre-materialize the shape you need. Do not try to make ksqlDB a general-purpose analytical query engine — that's not the design point; use Flink SQL or a warehouse for ad-hoc analytics.

Senior interview question on push/pull semantics + materialize

A senior interviewer might ask: "Design a real-time leaderboard API for a game where players earn points. Requirements: (1) live update stream of the top 10 for a dashboard, (2) API endpoint that returns a player's current rank on demand, (3) API endpoint that returns the current top 10. Model this with ksqlDB — DDL, push query, pull queries, and describe the routing story on a 3-server cluster."

Solution Using a two-table materialization + push and pull mix 

-- 1. Source stream — every score event as it arrives
CREATE STREAM score_events (
  player_id VARCHAR KEY,
  points    INT,
  ts        BIGINT
) WITH (
  KAFKA_TOPIC='score_events',
  VALUE_FORMAT='JSON',
  TIMESTAMP='ts'
);

-- 2. Materialized view — running total per player (unbounded aggregation)
CREATE TABLE player_totals AS
  SELECT player_id,
         SUM(points) AS total_points
  FROM   score_events
  GROUP  BY player_id
  EMIT   CHANGES;

-- 3. Materialized view — top 10 globally (pre-materialized for pull-query use)
--    Keyed by a constant so pull query is a single keyed read.
CREATE TABLE global_top10 AS
  SELECT 'GLOBAL' AS scope,
         TOPK(player_id, total_points, 10) AS top10
  FROM   player_totals
  GROUP  BY 'GLOBAL'
  EMIT   CHANGES;

-- 4. Push query — dashboard subscription for the live top 10
SELECT scope, top10
FROM   global_top10
EMIT   CHANGES;

-- 5. Pull query — get a specific player's current total
SELECT total_points
FROM   player_totals
WHERE  player_id = 'p123';

-- 6. Pull query — get the current top 10 on demand
SELECT top10
FROM   global_top10
WHERE  scope = 'GLOBAL';
Enter fullscreen mode Exit fullscreen mode

Step-by-step trace.

Access Query Under the hood Latency
Live dashboard SELECT ... FROM global_top10 EMIT CHANGES WebSocket subscription; delivers each top-10 update as it happens ~50 ms per update
Player rank API SELECT total_points FROM player_totals WHERE player_id = 'p123' Routed to owning server; RocksDB read ~5–8 ms
Top-10 API SELECT top10 FROM global_top10 WHERE scope = 'GLOBAL' Routed to owning server; RocksDB read of the single 'GLOBAL' key ~5–8 ms
Routing (3-server) any pull query 1/3 direct; 2/3 proxy hop; transparent to client +2–3 ms average

The two-table pattern is the key: the first table (player_totals) is a natural per-player aggregation. The second table (global_top10) pre-materializes the top-10 shape as a single row keyed by a constant 'GLOBAL'. Pull-querying by that constant is a single RocksDB read on whichever server owns the constant's partition. The dashboard subscribes to the top-10 push stream; both APIs pull-query the pre-materialized tables.

Output:

API Query type Latency p50 Latency p99
Live dashboard push 50 ms per update 200 ms
Player rank pull (keyed) 6 ms 25 ms
Global top 10 pull (keyed by constant) 6 ms 25 ms
Cluster size — 3 servers —
Source partitions — 6 —
Pre-materialization overhead — ~2× write amp —

Why this works — concept by concept:

  • Two-table materialization — the per-player table is the natural aggregation; the global-top-10 table pre-shapes the specific query you need. Pull queries are for keyed reads; if you want a shape that isn't keyed, materialize it into a table whose key is what you want to look up.
  • Constant-keyed top-K — grouping by a constant 'GLOBAL' produces a single-row table. Pull-querying the constant is a single RocksDB read; the TOPK function inside the aggregation does the heavy lifting.
  • Push + pull mix — the dashboard uses push (continuous updates); the APIs use pull (on-demand snapshots). Both hit the same underlying materialized state.
  • Server routing — the client is agnostic to which server owns which partition. The ksqlDB cluster handles the routing internally; the load balancer can round-robin all requests.
  • Cost — two persistent queries mean two Streams topologies, two changelog topics, two output topics. The write amplification is roughly 2×; for most workloads this is fine. State size is bounded by (unique players + 1) rows in RocksDB per server.

Streaming
Topic — streaming
Streaming materialized-view and windowing problems

Practice →

SQL Topic — sql SQL aggregation and top-K problems

Practice →

4. Connectors + operators + limits

Bundled Kafka Connect covers I/O; SQL operators cover computation; three real limits versus Flink SQL bound the design space

The mental model in one line: ksqlDB ships with bundled kafka connect for source and sink I/O (Postgres, S3, Snowflake, Elasticsearch, etc.), a rich SQL operator surface for streaming computation (windowed aggregations, stream-stream and stream-table joins, filters, projections, UDFs), and three real limits versus Flink SQL — no batch execution, limited stateful operators, and Confluent-ecosystem coupling — that senior engineers must name before recommending ksqlDB for a new pipeline. The operators are strong; the limits are strong; know both.

Iconographic connectors and limits diagram — Kafka Connect strip on top with source/sink connectors, a windowed-join gear in the middle, and three warning chips at the bottom labelled 'no batch', 'limited state', 'Confluent-tied', on a light PipeCode card.

Bundled Kafka Connect — the I/O surface.

  • Model. ksqlDB ships a Kafka Connect worker embedded in the server (or as a separate process depending on deployment). You install connectors on the classpath; ksqlDB manages them via SQL DDL (CREATE SOURCE CONNECTOR, CREATE SINK CONNECTOR).
  • Common source connectors. Debezium (CDC for Postgres, MySQL, Mongo, SQL Server), JDBC source (poll-based), S3 source, MQTT, IBM MQ.
  • Common sink connectors. JDBC sink (Postgres, MySQL, Snowflake), S3 sink, Elasticsearch sink, HTTP sink, BigQuery sink, Redis sink.
  • Config. DDL-driven: CREATE SOURCE CONNECTOR pg_orders WITH (...); The connector configuration is stored in the ksqlDB metadata + Kafka Connect's own config topic.
  • Ops. SHOW CONNECTORS, DESCRIBE CONNECTOR, DROP CONNECTOR. Failure modes: connector plugin missing, credentials misconfigured, source table schema change.

The SQL operator surface — what ksqlDB can compute.

  • Projections + filters. SELECT col1, col2 FROM stream WHERE ... — trivially compiled to a map + filter in Kafka Streams.
  • Aggregations. COUNT, SUM, AVG, MIN, MAX, TOPK, LATEST_BY_OFFSET, EARLIEST_BY_OFFSET, plus user-defined aggregate functions (UDAFs).
  • Windowed aggregations. WINDOW TUMBLING, WINDOW HOPPING, WINDOW SESSION — the three canonical window types. Fixed non-overlapping, fixed overlapping, and inactivity-gap-based respectively.
  • Joins. Stream-stream (windowed), stream-table (per-event lookup), table-table (state-to-state). Foreign-key joins are supported for table-table.
  • User-defined functions. UDFs (scalar), UDAFs (aggregate), UDTFs (table — produces multiple rows). Written in Java; loaded onto the ksqlDB classpath.

Window types in detail.

  • Tumbling. Fixed-size, non-overlapping. WINDOW TUMBLING (SIZE 5 MINUTES). Every event lands in exactly one window; windows close after their end + grace period. The canonical pattern for time-bucketed metrics.
  • Hopping. Fixed-size, overlapping. WINDOW HOPPING (SIZE 5 MINUTES, ADVANCE BY 1 MINUTE). Every event may land in multiple windows. Useful for rolling averages / smoothing.
  • Session. Dynamic-size, gap-based. WINDOW SESSION (INACTIVITY GAP 30 SECONDS). Groups events that arrive within N seconds of each other; the window ends when the gap elapses. Useful for user-session analytics.

The three real limits versus Flink SQL.

  • No batch execution. ksqlDB is stream-only. Flink SQL has a unified execution model — the same SQL runs as streaming or batch depending on the source table's boundedness. ksqlDB cannot execute a bounded query as a batch job; you'd read from a Kafka topic with an offset range but that's still streaming semantics.
  • Limited stateful operators. ksqlDB supports the Kafka Streams state-store primitives (aggregations, joins with windows, TOPK). It doesn't have the richer stateful primitives that Flink SQL offers — pattern matching (MATCH_RECOGNIZE), user-defined stateful process functions with fine-grained state control, or event-time triggers with arbitrary custom logic.
  • Confluent-ecosystem coupling. ksqlDB is a Confluent product. Non-Confluent Kafka deployments can run ksqlDB (it works against any Kafka cluster), but the operational ecosystem (Confluent Control Center, cloud pricing, connector marketplace) is Confluent-first. Flink SQL is genuinely OSS-first.

What ksqlDB does well vs Flink SQL.

  • Simplicity. One JVM per server; no separate JobManager / TaskManager topology. For a small pipeline, this is a real win.
  • Fast start. DDL + SELECT works out of the box against a Kafka cluster; no state-backend config, no checkpoint config, no separate resource manager.
  • Bundled Connect. Kafka Connect is first-class in ksqlDB; in Flink SQL, connectors are separate table sources/sinks with their own setup story.
  • REST-native. ksqlDB exposes SQL over REST; Flink SQL requires the SQL client or the Table API.

Common interview probes on connectors + operators + limits.

  • "What connectors does ksqlDB ship with?" — Debezium, JDBC, S3, Elasticsearch, Snowflake, etc. via Kafka Connect.
  • "What window types does ksqlDB support?" — Tumbling, Hopping, Session.
  • "What's the biggest limit vs Flink SQL?" — Pick one: no batch, limited stateful, or Confluent-tied.
  • "When would you use a stream-table join vs a stream-stream join?" — Table for current-state lookup; stream-stream for time-windowed correlation.

Worked example — enrich clickstream with user dim via stream-table join

Detailed explanation. The canonical enrichment pattern: a clickstream topic (append-only events) joined against a users dim (compacted table) via a stream-table join, with a JDBC source connector pulling the users table from Postgres via Debezium CDC. Every layer — source, table, join, sink — is DDL.

  • Source topic (CDC). users table in Postgres → Debezium → postgres.public.users Kafka topic (compacted).
  • Stream. clickstream in Kafka (append-only click events).
  • Table. users_table — declared over postgres.public.users.
  • Join. stream-table left join, per event enrichment.
  • Sink. Elasticsearch via Kafka Connect for search / dashboards.

Question. Write the DDL for the connectors, the stream, the table, the enrichment, and the sink — end to end. Describe what happens in each layer and how the join behaves for events that arrive before the user is created in Postgres.

Input.

Component Value
Postgres users table public.users
CDC Debezium
Click topic clickstream
Enrichment target Elasticsearch
Join stream-table LEFT JOIN

Code.

-- 1. Debezium source connector — CDC from Postgres users table
CREATE SOURCE CONNECTOR pg_users WITH (
  'connector.class'          = 'io.debezium.connector.postgresql.PostgresConnector',
  'database.hostname'        = 'db.internal',
  'database.port'            = '5432',
  'database.user'            = 'debezium',
  'database.password'        = '${DEBEZIUM_PW}',
  'database.dbname'          = 'app',
  'topic.prefix'             = 'postgres',
  'table.include.list'       = 'public.users',
  'plugin.name'              = 'pgoutput'
);

-- 2. The stream — clickstream events (append-only)
CREATE STREAM clickstream (
  click_id   VARCHAR KEY,
  user_id    VARCHAR,
  page       VARCHAR,
  ts         BIGINT
) WITH (
  KAFKA_TOPIC='clickstream',
  VALUE_FORMAT='JSON',
  TIMESTAMP='ts'
);

-- 3. The table — users profile from Postgres CDC (compacted topic)
CREATE TABLE users_table (
  user_id  VARCHAR PRIMARY KEY,
  name     VARCHAR,
  country  VARCHAR,
  tier     VARCHAR
) WITH (
  KAFKA_TOPIC='postgres.public.users',
  VALUE_FORMAT='JSON'
);

-- 4. The enrichment — stream-table left join
CREATE STREAM clicks_enriched AS
  SELECT c.click_id,
         c.user_id,
         c.page,
         c.ts,
         u.name    AS user_name,
         u.country AS user_country,
         u.tier    AS user_tier
  FROM   clickstream c
  LEFT JOIN users_table u ON c.user_id = u.user_id
  EMIT   CHANGES;

-- 5. Sink connector — Elasticsearch
CREATE SINK CONNECTOR es_clicks_enriched WITH (
  'connector.class'    = 'io.confluent.connect.elasticsearch.ElasticsearchSinkConnector',
  'connection.url'     = 'http://es.internal:9200',
  'topics'             = 'CLICKS_ENRICHED',
  'type.name'          = '_doc',
  'key.ignore'         = 'true'
);
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. The Debezium source connector watches the Postgres WAL for changes to public.users and writes each change (insert / update / delete) as a record to the postgres.public.users Kafka topic. The topic is compacted (Debezium configures this), so the latest state per user_id survives compaction.
  2. The clickstream stream binds to the raw clickstream topic — append-only, timestamped, keyed by click_id.
  3. The users table binds to the Debezium-produced topic. Because the topic is compacted and keyed by user_id, ksqlDB models it as a KTable — the latest record per key is the current profile.
  4. The stream-table join compiles to a stream.leftJoin(table) Kafka Streams topology. For each incoming click, ksqlDB looks up the user profile in the local RocksDB copy of the users table by user_id, and produces an enriched record. LEFT JOIN means clicks by unknown users still flow through with NULL user fields.
  5. Late-arriving user. If a click arrives before the user's CDC record has been processed, the LEFT JOIN produces the enriched click with NULL user fields (this is the semantics of LEFT JOIN under stream-table). If the user record arrives later, historical clicks are not re-enriched — the join is not retroactive. This is a common source of confusion in interviews; name it explicitly.

Output.

Event Users table state clicks_enriched output
click-1 (user=u42, ts=09:59) u42 not present click-1, u42, NULL, NULL, NULL
Debezium: INSERT users(u42, Ada, US, silver) u42: Ada, US, silver (no click event; no output)
click-2 (user=u42, ts=10:01) u42: Ada, US, silver click-2, u42, Ada, US, silver
Debezium: UPDATE users(u42, tier=gold) u42: Ada, US, gold (no click event; no output)
click-3 (user=u42, ts=10:02) u42: Ada, US, gold click-3, u42, Ada, US, gold

Rule of thumb. For enrichment pipelines, use CDC + compacted topics + stream-table left join. Accept that late-arriving user records don't re-enrich historical events. If you need re-enrichment, either (a) run a batch reprocessing job, (b) delay clicks with a buffer, or (c) drop LEFT JOIN semantics and use INNER JOIN, dropping clicks with unknown users.

Worked example — three window types on the same source

Detailed explanation. The three window types (tumbling, hopping, session) on the same pageviews source produce dramatically different aggregations. Show all three side by side to demonstrate the semantics and pick the right one per use case.

  • Tumbling 5-minute. Non-overlapping buckets. Each event in exactly one bucket. Canonical for time-bucketed metrics.
  • Hopping 5-minute / 1-minute advance. Overlapping — each event in up to 5 buckets. Canonical for rolling averages / smoothing.
  • Session 30-second inactivity. Dynamic — events within 30s form a session; the session ends when 30s of silence pass. Canonical for user-session analytics.

Question. Write DDL for the three window types on the same pageviews source and name the use case for each. Show what happens to a specific event under each window.

Input.

Window type DDL Use case
Tumbling WINDOW TUMBLING (SIZE 5 MINUTES) Metrics per 5-min bucket
Hopping WINDOW HOPPING (SIZE 5 MINUTES, ADVANCE BY 1 MINUTE) Rolling 5-min average, updated every 1 min
Session WINDOW SESSION (INACTIVITY GAP 30 SECONDS) User-session pageview counts

Code.

-- Assume: CREATE STREAM pageviews already exists

-- (1) Tumbling — 5-minute buckets, non-overlapping
CREATE TABLE pv_tumbling AS
  SELECT user_id, WINDOWSTART, WINDOWEND, COUNT(*) AS views
  FROM   pageviews
  WINDOW TUMBLING (SIZE 5 MINUTES)
  GROUP  BY user_id
  EMIT   CHANGES;

-- (2) Hopping — 5-minute windows advancing every 1 minute
--     Each event lands in ceil(SIZE/ADVANCE) = 5 windows
CREATE TABLE pv_hopping AS
  SELECT user_id, WINDOWSTART, WINDOWEND, COUNT(*) AS views
  FROM   pageviews
  WINDOW HOPPING (SIZE 5 MINUTES, ADVANCE BY 1 MINUTE)
  GROUP  BY user_id
  EMIT   CHANGES;

-- (3) Session — 30-second inactivity gap
CREATE TABLE pv_session AS
  SELECT user_id, WINDOWSTART, WINDOWEND, COUNT(*) AS session_views
  FROM   pageviews
  WINDOW SESSION (30 SECONDS)
  GROUP  BY user_id
  EMIT   CHANGES;
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. Tumbling. WINDOW TUMBLING (SIZE 5 MINUTES) produces buckets like [10:00, 10:05), [10:05, 10:10), ... An event at 10:03 lands in [10:00, 10:05) only. Perfect for time-series metrics ("pageviews per user per 5-minute bucket").
  2. Hopping. WINDOW HOPPING (SIZE 5 MINUTES, ADVANCE BY 1 MINUTE) produces overlapping windows: [10:00, 10:05), [10:01, 10:06), [10:02, 10:07), ... An event at 10:03 lands in 5 windows: [09:59, 10:04), [10:00, 10:05), [10:01, 10:06), [10:02, 10:07), [10:03, 10:08). Perfect for rolling averages ("5-minute rolling pageview count, updated every minute").
  3. Session. WINDOW SESSION (30 SECONDS) groups events with less than 30 seconds between them. If a user has events at 10:00:00, 10:00:20, and 10:00:35, they form one session [10:00:00, 10:00:35]. A 4th event at 10:01:10 (35 seconds later) starts a new session. Perfect for user-session analytics.
  4. State cost. Tumbling stores one row per (user, 5-min window) — bounded by cardinality × window count. Hopping stores 5× more rows (one per overlapping window). Session's state is bounded by concurrent open sessions per user. In descending state-cost order: hopping > tumbling > session (usually).
  5. Latency. Tumbling emits final results after the window's grace period elapses. Hopping emits continuous updates across multiple windows per event. Session emits when the session closes (after the inactivity gap). Push subscribers see intermediate updates for all three.

Output.

Event at 10:03 (user=u42) Windows updated
Tumbling 5min [10:00, 10:05) — 1 window
Hopping 5min/1min [09:59, 10:04), [10:00, 10:05), [10:01, 10:06), [10:02, 10:07), [10:03, 10:08) — 5 windows
Session 30s current or new session — 1 window

Rule of thumb. Tumbling for bucketed metrics; hopping for rolling/smoothing; session for user-session analytics. Hopping's state cost is proportional to SIZE / ADVANCE; keep the ratio small (5×–10×) or state grows unbounded fast.

Worked example — naming the three real limits vs Flink SQL

Detailed explanation. A senior interviewer often probes the limits directly: "What can Flink SQL do that ksqlDB cannot?" The senior answer names three specific limits with a concrete example for each. This is the answer that separates candidates who've read the docs from candidates who've deployed both.

  • Limit 1: no batch. Flink SQL runs the same SQL as streaming or batch; ksqlDB is stream-only.
  • Limit 2: limited stateful operators. Flink has MATCH_RECOGNIZE, custom ProcessFunction, fine-grained state APIs; ksqlDB does not.
  • Limit 3: Confluent-tied ecosystem. Flink is genuinely OSS-first; ksqlDB is a Confluent product.

Question. Name the three limits, give a concrete example of a workload that fails in ksqlDB but succeeds in Flink SQL for each limit, and propose the migration cost.

Input.

Limit ksqlDB behaviour Flink SQL behaviour Migration cost
Batch execution stream only unified stream + batch rewrite SQL for Flink dialect
MATCH_RECOGNIZE not supported supported translate pattern to Flink dialect
Non-Confluent ecosystem works but Confluent-first OSS-first ops rewrite; SQL mostly compatible

Code.

-- (1) Batch execution — Flink SQL supports; ksqlDB does not
-- Example: reprocess yesterday's orders from S3 into a warehouse table

-- Flink SQL
CREATE TABLE orders_s3 (
  order_id STRING, user_id STRING, amount DECIMAL(10,2), event_time TIMESTAMP(3),
  WATERMARK FOR event_time AS event_time - INTERVAL '5' SECOND
) WITH (
  'connector' = 'filesystem',
  'path'      = 's3://bucket/orders/2026-07-05/',
  'format'    = 'parquet'
);

-- Same SQL runs as batch (bounded source) or streaming (unbounded source)
SELECT user_id, SUM(amount) AS total FROM orders_s3 GROUP BY user_id;

-- ksqlDB — cannot express this as a batch job.
-- Workaround: sink S3 → Kafka topic; consume in ksqlDB with a bounded offset range.
-- But this is streaming semantics with a start/end offset, not batch execution.
Enter fullscreen mode Exit fullscreen mode 
-- (2) MATCH_RECOGNIZE — Flink SQL supports; ksqlDB does not
-- Example: detect a click → view → purchase sequence within 5 minutes

-- Flink SQL
SELECT * FROM events
MATCH_RECOGNIZE (
  PARTITION BY user_id
  ORDER BY ts
  MEASURES A.ts AS click_ts, B.ts AS view_ts, C.ts AS purchase_ts
  ONE ROW PER MATCH
  PATTERN (A B C)
  DEFINE A AS A.event_type = 'click',
         B AS B.event_type = 'view',
         C AS C.event_type = 'purchase'
);

-- ksqlDB — no pattern-matching syntax. Workarounds:
--   1. UDAF-based state machine (complex to write)
--   2. Downstream external stream processor (defeats the "SQL layer" goal)
--   3. Multiple joins with time constraints (verbose, error-prone)
Enter fullscreen mode Exit fullscreen mode 
# (3) Non-Confluent ecosystem — install story
# Flink SQL: apt install / docker pull / kubectl apply — works against any Kafka
# ksqlDB:  supported on any Kafka but Confluent Platform / Cloud is the
#          first-class deployment story; connector plugins are Confluent-hosted;
#          managed offering is Confluent-only.
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. Limit 1 — no batch. Flink SQL's unified batch + stream model is a genuine differentiator. If your team wants "write the SQL once; run as streaming for real-time and as batch for backfill," Flink SQL wins outright. ksqlDB has no batch mode; the only workaround is streaming from a Kafka topic with an offset range, which is not the same thing.
  2. Limit 2 — MATCH_RECOGNIZE. Complex event processing patterns ("click within 30s of a view followed by a purchase within 5 min") are expressible in Flink SQL via MATCH_RECOGNIZE. In ksqlDB, the workaround is a custom UDAF that implements the state machine manually — expensive to write, hard to test, easy to get wrong.
  3. Limit 3 — Confluent-tied. ksqlDB works against any Kafka cluster technically. Practically, the connector marketplace is Confluent-hosted, the managed offering is Confluent Cloud only, and the tooling (Control Center) is Confluent Platform. Flink is genuinely OSS-first with community-hosted managed offerings.
  4. Migration cost — SQL dialect. ksqlDB SQL and Flink SQL both look like SQL but differ in DDL (Flink: CREATE TABLE ... WITH ('connector' = '...'); ksqlDB: CREATE STREAM ... WITH (KAFKA_TOPIC='...')), window syntax (Flink: TUMBLE(TABLE, DESCRIPTOR, INTERVAL); ksqlDB: WINDOW TUMBLING), and function names. Migration is a rewrite of DDL + light rewrite of DML.
  5. When the limits push you off. Batch execution → Flink SQL. Complex pattern matching → Flink SQL. Non-Confluent stack → Flink SQL. Two out of three limits push to Flink SQL for anything beyond simple streaming aggregations.

Output.

Workload ksqlDB? Flink SQL? Winner
Simple windowed count yes yes tie (ksqlDB simpler)
Batch backfill of history no yes Flink SQL
MATCH_RECOGNIZE pattern no (UDAF workaround) yes Flink SQL
CDC → enrichment → sink yes yes tie
OSS-first stack works, not native native Flink SQL

Rule of thumb. Name the limits before recommending ksqlDB. If none of the three limits apply, ksqlDB is a great fit. If any one applies, evaluate Flink SQL seriously — the migration cost is significantly lower before you ship than after.

Senior interview question on connectors, operators, limits

A senior interviewer might ask: "You're designing a CDC-driven analytics pipeline: Postgres source → CDC → real-time enrichment with a users dim → windowed aggregations → Elasticsearch sink for search + Snowflake sink for BI. Walk me through the ksqlDB design, name where each Kafka Connect connector lives, name the window semantics, and name the specific limit that would push you off ksqlDB to Flink SQL for this workload."

Solution Using Debezium + bundled Connect + windowed CTAS + dual sinks 

-- 1. Source — Debezium CDC from Postgres
CREATE SOURCE CONNECTOR pg_source WITH (
  'connector.class'       = 'io.debezium.connector.postgresql.PostgresConnector',
  'database.hostname'     = 'db.internal',
  'database.port'         = '5432',
  'database.user'         = 'debezium',
  'database.password'     = '${DEBEZIUM_PW}',
  'database.dbname'       = 'app',
  'topic.prefix'          = 'pg',
  'table.include.list'    = 'public.orders,public.users',
  'plugin.name'           = 'pgoutput'
);

-- 2. Model the CDC topics as ksqlDB streams / tables
CREATE STREAM orders_stream (
  order_id  VARCHAR KEY,
  user_id   VARCHAR,
  amount    DECIMAL(10,2),
  ts        BIGINT
) WITH (KAFKA_TOPIC='pg.public.orders', VALUE_FORMAT='JSON', TIMESTAMP='ts');

CREATE TABLE users_dim (
  user_id  VARCHAR PRIMARY KEY,
  name     VARCHAR,
  country  VARCHAR,
  tier     VARCHAR
) WITH (KAFKA_TOPIC='pg.public.users', VALUE_FORMAT='JSON');

-- 3. Enrichment — stream-table LEFT JOIN
CREATE STREAM orders_enriched AS
  SELECT o.order_id, o.user_id, o.amount, o.ts,
         u.name, u.country, u.tier
  FROM   orders_stream o
  LEFT JOIN users_dim u ON o.user_id = u.user_id
  EMIT   CHANGES;

-- 4. Windowed aggregation — 5-min tumbling revenue per country/tier
CREATE TABLE revenue_per_country_tier AS
  SELECT country, tier, WINDOWSTART AS window_start, SUM(amount) AS revenue
  FROM   orders_enriched
  WINDOW TUMBLING (SIZE 5 MINUTES)
  GROUP  BY country, tier
  EMIT   CHANGES;

-- 5. Sink 1 — Elasticsearch for search + live dashboards
CREATE SINK CONNECTOR es_sink WITH (
  'connector.class' = 'io.confluent.connect.elasticsearch.ElasticsearchSinkConnector',
  'connection.url'  = 'http://es.internal:9200',
  'topics'          = 'ORDERS_ENRICHED,REVENUE_PER_COUNTRY_TIER',
  'type.name'       = '_doc'
);

-- 6. Sink 2 — Snowflake for BI (via Kafka Connect Snowflake connector)
CREATE SINK CONNECTOR snowflake_sink WITH (
  'connector.class'         = 'com.snowflake.kafka.connector.SnowflakeSinkConnector',
  'snowflake.url.name'      = 'xy12345.snowflakecomputing.com',
  'snowflake.user.name'     = 'ksqldb_writer',
  'snowflake.private.key'   = '${SNOWFLAKE_KEY}',
  'snowflake.database.name' = 'ANALYTICS',
  'snowflake.schema.name'   = 'STREAMING',
  'topics'                  = 'ORDERS_ENRICHED,REVENUE_PER_COUNTRY_TIER'
);
Enter fullscreen mode Exit fullscreen mode

Step-by-step trace.

Layer ksqlDB primitive What it does
Ingress CREATE SOURCE CONNECTOR (Debezium) Postgres WAL → 2 Kafka topics
Model CREATE STREAM, CREATE TABLE Bind topics to schemas
Enrich CREATE STREAM ... AS ... LEFT JOIN Per-order enrichment with current user dim
Aggregate CREATE TABLE ... AS SELECT ... WINDOW TUMBLING 5-min tumbling revenue per (country, tier)
Egress 1 CREATE SINK CONNECTOR (Elasticsearch) Push enriched + windowed to ES
Egress 2 CREATE SINK CONNECTOR (Snowflake) Push enriched + windowed to Snowflake
Limit check — If batch backfill of 2 years' orders needed → Flink SQL wins

After the pipeline ships, Postgres orders flow through Debezium into Kafka, ksqlDB enriches each order with the current user profile, aggregates 5-minute revenue by country and tier, and dual-sinks the results to Elasticsearch (for search + real-time dashboards) and Snowflake (for BI + historical analysis). All of this is SQL DDL; no application code. The pipeline is production-ready in ~1 day of engineering.

Output:

Metric Value
Time to first working pipeline ~1 day
Total DDL statements 6
Connectors installed 3 (Debezium, ES, Snowflake)
Persistent queries 2 (enrichment, windowed agg)
End-to-end latency (Postgres → ES) ~1–2 seconds (CDC + Kafka + ksqlDB + ES)
Limit that would push off ksqlDB batch backfill of history → Flink SQL

Why this works — concept by concept:

  • Bundled Connect — the connector DDL (CREATE SOURCE CONNECTOR, CREATE SINK CONNECTOR) is first-class ksqlDB. No separate Kafka Connect deployment; the ksqlDB server manages the workers. This is a genuine simplicity win over Flink SQL for CDC-driven pipelines.
  • Stream-table LEFT JOIN — the correct primitive for per-event enrichment against a dim table. Handles late-arriving keys with NULL (not retroactive; document this in the SLA).
  • Windowed tumbling — 5-minute buckets are the canonical time-bucketed aggregation. WINDOW TUMBLING is the simplest window and the right choice for revenue-by-bucket dashboards.
  • Dual sinks — one topic can drive multiple sinks. Elasticsearch + Snowflake from the same source; no fan-out logic in application code.
  • Cost — the pipeline runs on one ksqlDB cluster + the underlying Kafka cluster. Write amplification is ~4× (source → enriched → aggregated → 2 sinks). For a 1000 orders/sec workload this is trivial; at 100k orders/sec plan the Kafka broker capacity. The push-off point (batch backfill) is a concrete migration trigger; document it in the design doc.

Streaming
Topic — streaming
Streaming enrichment and windowing problems

Practice →

ETL Topic — etl ETL problems on CDC and sink fan-out

Practice →

5. Alternatives + when to pick each

Four medallions, one decision tree — pick per constraint, not per popularity

The mental model in one line: the four serious 2026 alternatives for streaming SQL on Kafka are ksqlDB (Confluent + fast start), Flink SQL (broad + batch too), Materialize / RisingWave (strict-serialisable Postgres-compatible), and raw Kafka Streams (JVM control, no SQL) — and the correct choice is driven by (a) whether you're on Confluent stack, (b) whether you need batch + stream, (c) whether you need strict semantics, and (d) whether your team wants SQL or code. Pick per constraint. The migration cost between them is 2–6 senior-engineer-months; choose once, choose well.

Iconographic alternatives diagram — four medallions ksqlDB / Flink SQL / Materialize / Kafka Streams arranged in a 2x2 grid with a decision-tree diamond selecting between them based on constraints, on a light PipeCode card.

The four alternatives, one paragraph each.

  • ksqlDB. Confluent's streaming-SQL layer. Fastest start for simple SQL over Kafka. Feature-frozen since ~2023; Confluent-ecosystem-tied; not strict-serialisable. Pick when constraints match (Confluent + simple + fast).
  • Flink SQL. Apache Flink's SQL API. Broad OSS ecosystem, unified batch + stream, richer stateful operators (MATCH_RECOGNIZE, custom ProcessFunctions), robust exactly-once story. Operational surface is larger (JobManager + TaskManagers + state backend). Pick when you need any of: broader ecosystem, batch too, complex event processing, or Kubernetes-native deployment.
  • Materialize / RisingWave. Streaming SQL databases with strict-serialisable semantics. Postgres wire compatibility means any Postgres client works out of the box. Differential dataflow (Materialize) or similar IVM (RisingWave) engines. Pick when strict semantics matter or when you want a "streaming Postgres" surface for API backends.
  • Kafka Streams (raw JVM). Full-control JVM library, no SQL layer. The engine that ksqlDB uses under the hood — but exposed directly. Best for stateful business logic that doesn't fit SQL, custom state stores, custom serializers. Pick when your team is Java/Scala/Kotlin native and SQL is a straightjacket.

Flink SQL — the breadth win.

  • Ecosystem. Genuinely OSS-first; large community; many managed offerings (Confluent Cloud, AWS Kinesis Data Analytics, Ververica).
  • Unified batch + stream. Same SQL runs as batch (bounded sources) or streaming (unbounded sources). Migration between the two is a source-config change.
  • Stateful richness. MATCH_RECOGNIZE, custom ProcessFunction, fine-grained state APIs, RocksDB or heap state backends, checkpoint / savepoint semantics.
  • Ops cost. Higher than ksqlDB — JobManager + TaskManagers + state backend + checkpoint store. On Kubernetes with Flink Kubernetes Operator, the ops story is manageable but non-trivial.

Materialize / RisingWave — the semantics win.

  • Postgres wire. Any Postgres client (psql, JDBC, ORMs) works. Enormous ecosystem win — the tools you already have just work.
  • Strict semantics. Every SUBSCRIBE / LISTEN observation is a valid snapshot of a consistent past state. No visible flicker from late events; no intermediate inconsistent outputs.
  • Trade-offs. Kafka is a source but not the source; source coverage is broader (Kafka, Postgres CDC, Kinesis, files). Ecosystem is smaller than Flink; connector story is newer. Materialize is a SaaS; RisingWave is OSS + SaaS.
  • When to pick. API backends where callers must see a consistent state; migration from a Postgres materialized view to a streaming Postgres materialized view; strict-serialisable-semantics-required workloads.

Kafka Streams — the JVM-control win.

  • API. Java/Scala/Kotlin. Fluent builder API for topologies. Same processor primitives that ksqlDB compiles to under the hood.
  • When SQL is the wrong tool. Custom state machines, custom serializers, custom time semantics, custom windowing logic, application-specific business rules that don't compress to SQL.
  • Trade-offs. No SQL surface — your team writes and maintains a JVM app. Deploy as a normal JVM service; scale by increasing the consumer-group instance count.
  • When to pick. Team is JVM-native; the workload has significant custom stateful logic; SQL is a straightjacket rather than an enabler.

The decision tree in one path.

  • Q1: Are you on Confluent stack + need fast start + simple SQL + eventual OK? → ksqlDB.
  • Q2: Do you need batch + stream, broader ecosystem, or complex event processing? → Flink SQL.
  • Q3: Do you need strict-serialisable semantics or Postgres wire compatibility? → Materialize / RisingWave.
  • Q4: Is SQL the wrong tool for your custom stateful logic? → Kafka Streams (raw JVM).

Common senior interview signals on alternatives.

  • Engine-choice math. Name the four axes and the winner per axis; don't rank globally.
  • Migration path. Name the cost of a migration between two of the four (2–6 senior-engineer-months); the SQL dialect differences matter.
  • EOS story. All four support exactly-once semantics under specific configurations; know the differences (ksqlDB: EOS via Kafka Streams; Flink SQL: checkpoint-based EOS; Materialize: strict-serialisable subsumes EOS; Kafka Streams: same as ksqlDB).
  • Roadmap sensitivity. ksqlDB in 2026 is stable but not the marquee; if your roadmap depends on continuous engine improvement, weight this into the choice.

Worked example — the Flink SQL migration cost

Detailed explanation. A team runs a mature ksqlDB pipeline (3 servers, 15 persistent queries, Postgres CDC + windowed aggregations + ES sink) and evaluates a migration to Flink SQL because a new requirement (nightly batch backfill of historical data) has entered the roadmap. Walk through the migration story — SQL rewrite, ops rewrite, cutover — and quantify the cost.

  • Trigger. Batch backfill of 2 years of historical data as part of a data-model change.
  • Current stack. ksqlDB on Confluent Platform, 3 servers, 15 persistent queries.
  • Target. Flink SQL on Kubernetes (Flink Operator + RocksDB state backend + S3 checkpoint store).

Question. Estimate the migration cost across (a) SQL rewrite, (b) connector re-wiring, (c) ops platform, (d) cutover. Produce a rough schedule.

Input.

Component ksqlDB Flink SQL Migration cost
SQL dialect ksqlDB-flavoured Flink SQL ~2 weeks per 15 queries
Windowing WINDOW TUMBLING (SIZE 5 MINUTES) TUMBLE(TABLE t, DESCRIPTOR(ts), INTERVAL '5' MINUTE) rewrite each windowed CTAS
Connectors ksqlDB CREATE SOURCE/SINK CONNECTOR Flink SQL CREATE TABLE ... WITH ('connector' = ...) rewrite ~5 connectors
Ops 3 ksqlDB servers Flink Operator + JobManager + TaskManagers ~4 weeks
Cutover shadow-run for 1 week dual-write, compare, cut ~2 weeks

Code.

-- ksqlDB version (before)
CREATE STREAM orders (...) WITH (KAFKA_TOPIC='orders', VALUE_FORMAT='JSON', TIMESTAMP='ts');

CREATE TABLE revenue_5min AS
  SELECT user_id, WINDOWSTART, SUM(amount) AS rev
  FROM   orders
  WINDOW TUMBLING (SIZE 5 MINUTES)
  GROUP  BY user_id
  EMIT   CHANGES;
Enter fullscreen mode Exit fullscreen mode 
-- Flink SQL version (after)
CREATE TABLE orders (
  order_id STRING, user_id STRING, amount DECIMAL(10,2), ts TIMESTAMP_LTZ(3),
  WATERMARK FOR ts AS ts - INTERVAL '5' SECOND
) WITH (
  'connector' = 'kafka',
  'topic'     = 'orders',
  'properties.bootstrap.servers' = 'kafka:9092',
  'format'    = 'json',
  'scan.startup.mode' = 'earliest-offset'
);

CREATE TABLE revenue_5min (
  user_id STRING, window_start TIMESTAMP_LTZ(3), rev DECIMAL(20,2),
  PRIMARY KEY (user_id, window_start) NOT ENFORCED
) WITH (
  'connector' = 'upsert-kafka',
  'topic'     = 'revenue_5min',
  'properties.bootstrap.servers' = 'kafka:9092',
  'key.format' = 'json',
  'value.format' = 'json'
);

INSERT INTO revenue_5min
SELECT user_id, window_start, SUM(amount)
FROM   TABLE(TUMBLE(TABLE orders, DESCRIPTOR(ts), INTERVAL '5' MINUTES))
GROUP  BY user_id, window_start;
Enter fullscreen mode Exit fullscreen mode 
# Ops migration — Flink Operator on Kubernetes
apiVersion: flink.apache.org/v1beta1
kind: FlinkDeployment
metadata:
  name: streaming-sql
spec:
  image: flink:1.19.0
  flinkVersion: v1_19
  flinkConfiguration:
    state.backend.type: "rocksdb"
    state.checkpoints.dir: "s3://bucket/flink-checkpoints/"
    execution.checkpointing.interval: "60s"
  serviceAccount: flink
  jobManager:
    resource:
      memory: "2048m"
      cpu: 1
  taskManager:
    replicas: 3
    resource:
      memory: "4096m"
      cpu: 2
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. SQL rewrite. ksqlDB's WINDOW TUMBLING (SIZE 5 MINUTES) becomes Flink SQL's TABLE(TUMBLE(TABLE orders, DESCRIPTOR(ts), INTERVAL '5' MINUTES)). Same semantics, different syntax. Every persistent CTAS in ksqlDB becomes an INSERT INTO ... SELECT in Flink SQL. Budget ~2 hours per query for the rewrite + testing; 15 queries ≈ 4 days of focused work + 1 week of validation.
  2. Connector re-wiring. ksqlDB's CREATE SOURCE CONNECTOR (which invokes Kafka Connect internally) becomes Flink SQL's CREATE TABLE ... WITH ('connector' = 'kafka' | 'jdbc-cdc' | ...). Each connector is a table definition; source/sink is by INSERT INTO. Budget 3–4 days for 5 connectors + validation.
  3. Ops platform. ksqlDB's "one server type" becomes Flink's JobManager + TaskManagers + state backend + checkpoint store. On Kubernetes with the Flink Operator, this is a manageable deployment but requires learning the operator, tuning state backend + checkpoints, and building the dashboards. Budget 3–4 weeks including team training.
  4. Cutover. Shadow-run both pipelines for 1 week; compare outputs; cut over incrementally per query. Budget 2 weeks for the cutover work.
  5. Total. ~9–10 senior-engineer-weeks for a 15-query pipeline migration. Significant but not enormous. The trigger (batch backfill requirement) justifies the cost; the alternative (build a separate batch pipeline alongside ksqlDB) has its own long-term cost.

Output.

Migration phase Effort Calendar time
SQL rewrite (15 queries) 4–5 days work 2 weeks (with reviews)
Connector rewrite (5) 3–4 days work 1 week
Ops platform (Flink on K8s) 3–4 weeks 4 weeks
Shadow + cutover 2 weeks 2 weeks
Total ~9–10 senior-engineer-weeks ~9–10 weeks calendar

Rule of thumb. Budget 2–3 months for a mature ksqlDB → Flink SQL migration. The SQL rewrite is the easiest part; the ops platform swap is the biggest cost. Do the migration when a specific requirement (batch, complex CEP, non-Confluent stack) triggers it — not on speculation about ksqlDB's roadmap.

Worked example — Materialize as the strict-semantics answer

Detailed explanation. A team building a fraud-detection API needs streaming aggregations (running per-user transaction totals + rolling 5-min risk scores) exposed as a consistent API — callers must not observe an intermediate state where a user's total goes down then up as late events arrive. ksqlDB's eventually-consistent windowed outputs are the wrong choice. Materialize (or RisingWave) is the correct answer.

  • Requirement. API callers see strict-serialisable snapshots — no visible flicker from late events.
  • Constraint. Postgres wire compatibility desirable (existing Postgres clients).
  • Options. Materialize (SaaS), RisingWave (OSS + SaaS), or "build it yourself with a lot of glue."

Question. Design the fraud-detection state layer with Materialize, and explain why ksqlDB is the wrong choice for this specific use case.

Input.

Requirement ksqlDB result Materialize result
Running per-user total eventually consistent strict-serialisable
Rolling 5-min risk score intermediate outputs visible strict-serialisable
Postgres client compatibility no (native ksqlDB client) yes (any pg driver)
Source Kafka transactions topic Kafka transactions topic

Code.

-- Materialize (Postgres-compatible; the code IS Postgres SQL)

-- 1. Kafka source table
CREATE SOURCE transactions
  FROM KAFKA CONNECTION kafka_conn (TOPIC 'transactions')
  FORMAT BYTES;

-- 2. Materialized view — running per-user total (strict-serialisable)
CREATE MATERIALIZED VIEW user_totals AS
  SELECT user_id, SUM(amount) AS total
  FROM   transactions
  GROUP  BY user_id;

-- 3. Materialized view — 5-min rolling risk score
CREATE MATERIALIZED VIEW rolling_risk AS
  SELECT user_id,
         SUM(amount) FILTER (WHERE ts > mz_now() - INTERVAL '5' MINUTE) AS recent_sum,
         COUNT(*)    FILTER (WHERE ts > mz_now() - INTERVAL '5' MINUTE) AS recent_count
  FROM   transactions
  GROUP  BY user_id;

-- 4. API access — any Postgres client works
-- psql -h materialize.internal -p 6875 -U user -d db
SELECT total FROM user_totals WHERE user_id = 'u42';
SELECT recent_sum, recent_count FROM rolling_risk WHERE user_id = 'u42';

-- 5. Push subscription — via Postgres SUBSCRIBE
COPY (SUBSCRIBE user_totals) TO STDOUT;
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. Source. Materialize connects to Kafka via a CREATE SOURCE DDL. The source is a bounded ingestion channel; the internal engine reads Kafka in order and feeds the differential-dataflow processor.
  2. Materialized view. CREATE MATERIALIZED VIEW ... is standard Postgres syntax. Materialize compiles it to a differential-dataflow plan; the view is continuously maintained as new source records arrive. Strict-serialisable: every observation is a consistent snapshot.
  3. API access. Any Postgres client — psql, JDBC, ORMs (Prisma, SQLAlchemy, etc.) — can SELECT from the materialized view. This is the biggest ecosystem win over ksqlDB's native client: your existing pg tooling just works.
  4. Push subscription. Materialize supports SUBSCRIBE view — a native push semantics similar to ksqlDB's EMIT CHANGES, but with strict-serialisable delivery.
  5. Why not ksqlDB. ksqlDB's windowed aggregations produce visible intermediate outputs to push subscribers as late events reshuffle windows. Pull queries also see the latest RocksDB state, which can move backwards momentarily as compensating updates arrive. For a fraud-detection API where "user total went up, then down, then up again" is a confusing surface, ksqlDB is genuinely the wrong tool. Materialize's guarantees eliminate the flicker.

Output.

Property ksqlDB Materialize
Strict semantics no (eventual) yes (strict-serialisable)
Postgres wire no yes
Push subscriptions yes (EMIT CHANGES) yes (SUBSCRIBE)
Source connectors Kafka Connect native Kafka / PG / etc.
Best fit dashboards, simple SQL strict-consistency APIs

Rule of thumb. When the API requires strict semantics and the team wants Postgres tooling, Materialize (or RisingWave) is the correct engine. ksqlDB is not "worse" for this workload — it's the wrong tool. Naming the mismatch explicitly is a senior signal.

Worked example — dropping SQL entirely and running raw Kafka Streams

Detailed explanation. Some workloads have significant custom stateful logic that doesn't fit SQL cleanly — for example, a complex user-onboarding state machine with many event types, custom retry logic, and per-tenant configuration lookups. Attempting to express this in SQL becomes a UDF-heavy nightmare. The right answer is often "drop SQL and write raw Kafka Streams."

  • Signal. SQL expressiveness is limiting; UDFs proliferate; every new business rule adds UDF complexity.
  • Decision. Rewrite in raw Kafka Streams (Java/Scala/Kotlin).
  • Cost. JVM team required; SQL surface gone; but full control over topology, state stores, and semantics.

Question. Sketch a user-onboarding state machine as raw Kafka Streams, name the primitives, and describe why SQL was the wrong tool.

Input.

State Trigger Next state
SIGNUP signup event PENDING_VERIFICATION
PENDING_VERIFICATION email verified VERIFIED
PENDING_VERIFICATION 24h timeout ABANDONED
VERIFIED first_login ACTIVE

Code.

// Raw Kafka Streams topology (Java)
StreamsBuilder builder = new StreamsBuilder();

// Source stream — every user event (signup, verify, login, timeout)
KStream<String, UserEvent> events = builder.stream("user_events");

// State store — per-user state machine
builder.addStateStore(
  Stores.keyValueStoreBuilder(
    Stores.persistentKeyValueStore("user_state"),
    Serdes.String(), userStateSerde
  )
);

// Processor — apply the state machine
KStream<String, UserStateTransition> transitions = events.transformValues(
  () -> new UserStateProcessor(), "user_state"
);

// Sink — the transitions become an output topic
transitions.to("user_state_transitions");

// Extra: 24h timeout side channel using punctuator
class UserStateProcessor implements ValueTransformerWithKey<String, UserEvent, UserStateTransition> {
  private KeyValueStore<String, UserState> store;
  private ProcessorContext ctx;

  @Override public void init(ProcessorContext ctx) {
    this.ctx = ctx;
    this.store = ctx.getStateStore("user_state");
    // Punctuate every 5 minutes to check for 24h timeouts
    ctx.schedule(Duration.ofMinutes(5), PunctuationType.WALL_CLOCK_TIME, this::checkTimeouts);
  }

  @Override public UserStateTransition transform(String userId, UserEvent event) {
    UserState current = store.get(userId);
    UserState next = current.apply(event);            // custom state machine logic
    store.put(userId, next);
    return new UserStateTransition(userId, current, next, event);
  }

  private void checkTimeouts(long timestamp) {
    try (KeyValueIterator<String, UserState> iter = store.all()) {
      while (iter.hasNext()) {
        KeyValue<String, UserState> kv = iter.next();
        if (kv.value.isPendingVerification() && kv.value.signupTs() < timestamp - Duration.ofHours(24).toMillis()) {
          UserState next = kv.value.transitionTo(UserState.ABANDONED);
          store.put(kv.key, next);
          ctx.forward(kv.key, new UserStateTransition(kv.key, kv.value, next, null));
        }
      }
    }
  }
}
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. Source + state store. The Kafka Streams KStream sources from the user_events topic. A named state store user_state (RocksDB + changelog) holds the current per-user state.
  2. Processor. ValueTransformerWithKey gives per-event access to the state store. For each event, we compute the state machine transition (current.apply(event)), update the store, and emit a transition record.
  3. Punctuator for timeouts. ctx.schedule(...) sets up a wall-clock punctuator that runs every 5 minutes. Inside, we iterate the state store looking for PENDING_VERIFICATION entries older than 24 hours and transition them to ABANDONED. This is the piece SQL has no clean equivalent for.
  4. Sink. Transitions go to the user_state_transitions output topic, ready for downstream consumers (analytics, notifications, dashboards).
  5. Why SQL was wrong. Expressing the state machine + 24h timeout logic in SQL would require: multiple stream-table joins (current + timeout), a UDAF for the state machine transitions, a separate persistent query for the timeout logic, and complex time handling. The Java version is ~100 lines and directly expresses the domain logic. For workloads where domain complexity dominates, code beats SQL.

Output.

Property ksqlDB / SQL Raw Kafka Streams
Expression of state machine UDF-heavy, awkward direct, ~100 LOC
Timeout handling ad-hoc SQL joins native punctuator
Test story UDF unit tests + query tests plain JVM unit tests
Ops surface 1 ksqlDB cluster 1 JVM app + Kafka
Team requirement SQL only JVM (Java/Scala/Kotlin)

Rule of thumb. When domain complexity dominates and SQL becomes a straightjacket (UDFs everywhere, timeouts hacked in via joins, complex state transitions), drop SQL and use raw Kafka Streams. The engine is the same as ksqlDB's underneath; you just skip the SQL layer. Trade the SQL surface for domain expressiveness.

Senior interview question on picking the right engine

A senior interviewer might close with: "You have four candidate engines — ksqlDB, Flink SQL, Materialize, raw Kafka Streams — for three different pipelines. Pipeline A: simple 5-min windowed COUNT on Confluent Cloud, ship in 1 week. Pipeline B: fraud API needing strict semantics via Postgres wire. Pipeline C: complex onboarding state machine with 24h timeouts. Walk me through which engine wins for each pipeline and why."

Solution Using the four-axis decision tree applied to three real pipelines 

Decision framework — 4 engines, 3 pipelines
============================================

Pipeline A: Confluent, simple 5-min windowed COUNT, ship in 1 week
  A. Ecosystem      = Confluent (match)
  B. Scope          = stream-only (match)
  C. Semantics      = eventual OK (dashboards)
  D. SQL vs code    = SQL required
  Fast start?       = YES
  → ksqlDB

Pipeline B: Fraud API, strict-serialisable, Postgres wire
  A. Ecosystem      = Kafka + Postgres tooling
  B. Scope          = stream-only
  C. Semantics      = strict-serialisable REQUIRED
  D. SQL vs code    = SQL required (via pg wire)
  → Materialize (or RisingWave)

Pipeline C: Onboarding state machine, 24h timeouts, complex logic
  A. Ecosystem      = mixed
  B. Scope          = stream-only
  C. Semantics      = eventual OK
  D. SQL vs code    = CODE (state machine + timeouts)
  → Kafka Streams (raw JVM)
Enter fullscreen mode Exit fullscreen mode

Step-by-step trace.

Pipeline Winner Why
A: Confluent + simple + fast ksqlDB Fastest start on Confluent for simple SQL
B: strict + PG wire Materialize Only engine with strict-serialisable + PG wire
C: state machine + timeouts Kafka Streams SQL wrong tool; JVM primitives fit domain
Trap answer for all three Flink SQL Overkill for A; wrong semantics for B; SQL wrong for C

The exercise demonstrates the core senior signal: pick per pipeline, not per engine reputation. Flink SQL is a magnificent engine — but not the right answer for any of these three pipelines. ksqlDB, Materialize, and raw Kafka Streams each win one axis.

Output:

Pipeline Engine Ship time Ongoing ops
A ksqlDB ~1 week 1 server type
B Materialize ~2–3 weeks 1 Materialize instance or SaaS
C Kafka Streams ~3–4 weeks 1 JVM app + Kafka

Why this works — concept by concept:

  • Pipeline-first framing — the decision is per-pipeline, not per-team-standardisation. Different pipelines can use different engines; the ops cost of running two is smaller than the mismatch cost of shoehorning both into one.
  • ksqlDB wins pipeline A — fast start, Confluent stack, simple SQL, eventual OK. This is the sweet spot; ksqlDB genuinely wins here.
  • Materialize wins pipeline B — strict-serialisable + Postgres wire is a real requirement, and ksqlDB / Flink SQL cannot deliver strict-serialisable at all. The engine's core guarantee matches the workload's core requirement.
  • Kafka Streams wins pipeline C — when SQL is the wrong tool, the answer isn't "hack SQL"; it's "drop SQL." The raw JVM API gives domain-first expressiveness.
  • Cost — running three engines has a real ops cost. Weigh the alternative: standardising on one engine (typically Flink SQL) that mostly-covers all three. The mostly-covers option costs more in workarounds; the per-pipeline option costs more in ops. Pick with your team size and roadmap in mind.

Streaming
Topic — streaming
Streaming engine-choice and design problems

Practice →

Optimization
Topic — optimization
Optimization problems on streaming pipeline design

Practice →

Cheat sheet — ksqlDB recipes

  • CREATE STREAM template. CREATE STREAM name (col1 TYPE KEY, col2 TYPE, ...) WITH (KAFKA_TOPIC='topic_name', VALUE_FORMAT='JSON'|'AVRO'|'PROTOBUF', TIMESTAMP='ts_col', PARTITIONS=6, REPLICAS=3);. Use for append-only event topics. KEY marks the Kafka record key; TIMESTAMP marks the event-time column. PARTITIONS and REPLICAS are only for topics ksqlDB will create; for existing topics they're ignored.
  • CREATE TABLE template. CREATE TABLE name (col1 TYPE PRIMARY KEY, col2 TYPE, ...) WITH (KAFKA_TOPIC='compacted_topic', VALUE_FORMAT='JSON');. Use for compacted state topics (CDC of a source-of-truth table). The topic must be log-compacted; otherwise the latest-value-per-key semantics don't hold. PRIMARY KEY binds the Kafka record key.
  • CREATE TABLE AS SELECT with window. CREATE TABLE agg AS SELECT key, WINDOWSTART, WINDOWEND, COUNT(*) AS n FROM stream WINDOW TUMBLING (SIZE 5 MINUTES) GROUP BY key EMIT CHANGES;. The three window types: TUMBLING (SIZE X) for non-overlapping buckets; HOPPING (SIZE X, ADVANCE BY Y) for overlapping windows (state cost ∝ X/Y); SESSION (INACTIVITY GAP X) for gap-based user sessions.
  • Push vs pull query examples. Push: SELECT * FROM agg EMIT CHANGES; (WebSocket subscription; ~50 ms per update). Pull single-key: SELECT n FROM agg WHERE key = 'k' AND WINDOWSTART = 'T'; (~5 ms). Pull range: SELECT n FROM agg WHERE key = 'k' AND WINDOWSTART >= 'T1' AND WINDOWSTART < 'T2'; (~50–100 ms for tens of rows). Pull without a key predicate errors — pre-materialize a table keyed by what you want to look up.
  • Stream-table join for enrichment. CREATE STREAM enriched AS SELECT s.col1, t.col2 FROM stream s LEFT JOIN table t ON s.key = t.key EMIT CHANGES;. The table must be compacted; the join uses the current table state at stream-event time; late-arriving table updates do not retroactively re-enrich. For INNER JOIN, unknown-key stream events are dropped.
  • Windowed stream-stream join. CREATE STREAM joined AS SELECT * FROM stream_a a INNER JOIN stream_b b WITHIN 5 MINUTES ON a.key = b.key;. The WITHIN clause is required and defines the time correlation window. State cost proportional to the window size × source rate; keep windows small.
  • ksqlDB → Flink SQL migration recipe. (a) Rewrite DDL: CREATE STREAM ... WITH (KAFKA_TOPIC='...') → CREATE TABLE ... WITH ('connector' = 'kafka', 'topic' = '...'). (b) Rewrite windows: WINDOW TUMBLING (SIZE 5 MINUTES) → TABLE(TUMBLE(TABLE t, DESCRIPTOR(ts), INTERVAL '5' MINUTES)). (c) Rewrite persistent queries: CREATE TABLE AS SELECT ... EMIT CHANGES → INSERT INTO sink_table SELECT .... (d) Move connectors from CREATE SOURCE/SINK CONNECTOR to Flink SQL table sources/sinks. Budget 2 hours per query + 1 week for ops platform + 2 weeks cutover.
  • ksqlDB CLI + REST snippet. CLI: ksql http://ksqldb:8088 opens the interactive shell. REST DDL: POST /ksql { "ksql": "CREATE STREAM ..." }. REST push query: POST /query-stream { "sql": "SELECT ... EMIT CHANGES;" } — returns a chunked HTTP response. REST pull query: POST /query { "sql": "SELECT ... WHERE key = 'k';" } — returns a single JSON response. SHOW QUERIES lists persistent queries; TERMINATE query_id stops one; DROP STREAM|TABLE name DELETE TOPIC cleans up state.
  • Exactly-once semantics setup. Set processing.guarantee = exactly_once_v2 in ksql-server.properties. This enables Kafka Streams' EOS-v2 (transactional producer + isolation-level read_committed on consumers). Every persistent query gets EOS across its source-to-sink hop. Cost: modest CPU + latency overhead; usually worth it for financial workloads.
  • Server sizing rule. Start with 3 ksqlDB servers for HA. Size RAM for RocksDB state (~2× state size + JVM overhead); size CPU for Kafka Streams processing (~2 cores per active task at typical event rates). One task per source partition; more partitions = more parallelism up to the total task count in the cluster.
  • When NOT to pick ksqlDB. (1) You need batch + stream unified execution → Flink SQL. (2) You need strict-serialisable streaming semantics or Postgres wire → Materialize / RisingWave. (3) Your custom stateful logic is UDF-heavy or SQL-awkward → raw Kafka Streams. (4) You're outside Confluent stack and want native OSS support → Flink SQL. (5) Your roadmap depends on continuous engine improvement — ksqlDB is stable but feature-frozen since ~2023.

Frequently asked questions

Is ksqlDB still maintained in 2026?

Yes — Confluent still ships ksqlDB in Confluent Platform, still supports it in Confluent Cloud, and still cuts stable releases. But the honest reading of the roadmap is that ksqlDB is in mature-product mode rather than active-innovation mode: since Confluent's Immerok/Flink acquisition in 2023, the marquee streaming-SQL story at Confluent has been Flink. ksqlDB continues to serve its 2019-designed sweet spot (SQL over Kafka topics on Confluent stack) reliably, and existing production deployments are safe. New feature velocity is low — expect stability and bug fixes rather than significant new capabilities. If your roadmap depends on continuous engine improvement, plan for a migration; if you need a stable streaming-SQL surface that "just works" on Confluent stack, ksqlDB is a defensible choice through 2026 and beyond.

ksqldb vs flink SQL — which one should I pick for a new project?

The five-question decision tree: (1) Are you on Confluent stack and want the fastest start for simple SQL? → ksqlDB. (2) Do you need unified batch + stream execution? → Flink SQL. (3) Do you need complex event processing (MATCH_RECOGNIZE) or custom stateful process functions? → Flink SQL. (4) Are you deploying on Kubernetes and want native OSS operator support? → Flink SQL. (5) Do you want a broader connector ecosystem outside Confluent? → Flink SQL. For simple 5-minute windowed aggregations on Confluent Cloud shipped in a week, ksqlDB wins on time-to-production. For anything with batch requirements, complex CEP, or non-Confluent stack, Flink SQL is the clear answer. The ops cost of Flink SQL is genuinely higher (JobManager + TaskManagers + state backend + checkpoint store); if the extra requirements don't apply, the ksqlDB simplicity is a real win. Do not pick on popularity — pick on which of the five axes actually applies to your workload.

What is a push query in ksqlDB and how is it different from a pull query?

A push query subscribes to a running query's output stream and receives every update as it happens over a WebSocket connection — the syntax is SELECT ... EMIT CHANGES; and it runs until the client disconnects, with each new record delivered in tens of milliseconds. It's the streaming primitive: live dashboards, real-time consumers, downstream stream-processing subscribers. A pull query executes once against the current state of a materialized view (a CREATE TABLE AS SELECT result) and returns a point-in-time snapshot — the syntax is SELECT ... WHERE key = 'k'; and it responds in a single-digit millisecond RocksDB read. It's the database primitive: on-demand APIs, ad-hoc lookups, snapshot queries. Both modes access the same underlying materialized state; the mode is chosen by the client per query, not by the table's DDL. Rule of thumb: push for continuous consumers; pull for on-demand snapshots. Design your materialized views to support both patterns explicitly — key the view by what you want to pull-query, and let push subscribers see the full change stream.

Do I need Confluent to use ksqlDB, or can I run it on any Kafka cluster?

Technically ksqlDB works against any Kafka cluster — it's an open-source project (Apache 2.0 licensed) and the server is a JVM that speaks the Kafka protocol like any other Streams application. Practically the operational ecosystem is Confluent-first: the managed offering is Confluent Cloud only; the connector marketplace is Confluent-hosted (though most connectors are also available OSS); the monitoring / management tooling (Confluent Control Center) is Confluent Platform; and the community support and documentation assume Confluent stack. You can run ksqlDB against MSK, Aiven Kafka, Strimzi on Kubernetes, or bare-metal Kafka — but you're operating a Confluent-designed product outside its intended ecosystem, and the operational polish drops noticeably. If you're not on Confluent stack and want native OSS ergonomics, Flink SQL is the more natural choice. If you're on Confluent stack, ksqlDB's ecosystem is a real productivity win.

Can ksqlDB do exactly-once semantics?

Yes — set processing.guarantee = exactly_once_v2 in the ksqlDB server configuration and every persistent query gets Kafka Streams' EOS-v2 across its source-to-sink hop. Under the hood this uses Kafka's transactional producer (atomic multi-partition writes) plus consumer isolation.level = read_committed (skip aborted transactions). The scope of "exactly-once" is the ksqlDB persistent query itself: a source event is processed exactly once and its effect on the sink topic and state store is applied exactly once. External sinks via Kafka Connect need to be exactly-once compatible themselves — the JDBC sink can do exactly-once with idempotent writes; the Elasticsearch sink uses primary-key upserts to approximate it. The cost is a modest CPU + latency overhead (transactional producer flushes on transaction boundaries). For financial workloads, fraud detection, or any pipeline where duplicate processing is a correctness bug, EOS is worth turning on. For dashboard-style workloads where a duplicate row every million events is invisible, it's optional.

Should I start a new project on ksqlDB in 2026?

Only if the constraints match. ksqlDB is the right choice when: (a) you're on Confluent stack (Confluent Cloud or Confluent Platform); (b) you want SQL over Kafka topics with the fastest start; (c) your workload is stream-only (no batch requirement); (d) your semantics needs are eventually consistent (dashboards, aggregations, enrichment); (e) your stateful logic fits SQL cleanly (no complex state machines or custom timeouts). If all five apply, ksqlDB is a defensible choice in 2026 — it ships in ~1 week, the ops surface is small, and the code above still works verbatim. Don't pick ksqlDB when: you need batch + stream unified (Flink SQL), strict-serialisable semantics (Materialize / RisingWave), complex event processing patterns (Flink SQL MATCH_RECOGNIZE), or custom stateful logic that SQL awkwardly expresses (raw Kafka Streams). The honest 2026 answer is neither "ksqlDB is dead" nor "ksqlDB is always the right choice" — it's "ksqlDB is a stable, niche, Confluent-tied tool that wins on a specific set of constraints, and loses to Flink SQL / Materialize / raw Streams outside those constraints."

Practice on PipeCode

  • Drill the streaming practice library → for the ksqlDB-style windowed-aggregation, stream-table join, and materialized-view problems senior interviewers love.
  • Rehearse on the SQL practice library → for the DDL, aggregation, top-K, and window-function patterns that map directly onto ksqlDB SQL and Flink SQL.
  • Sharpen the pipeline-throughput axis with the ETL practice library → for the CDC-driven enrichment, sink fan-out, and end-to-end pipeline problems that motivate streaming SQL in the first place.
  • Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the push/pull, materialized-view, and engine-choice intuition against real graded inputs.

Lock in streaming-SQL decision muscle

Confluent docs explain the API. PipeCode drills explain the decision — when push queries are the right primitive, when pull queries beat scans, when Flink SQL replaces ksqlDB, when Materialize wins on semantics. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the streaming trade-offs senior data engineers actually face.

Practice streaming problems →
Practice SQL problems →

Top comments (0)