Timeplus, Materialize & RisingWave: Streaming SQL Engines Compared

streaming sql is the pick-one architectural decision that decides whether your continuous analytics ship in five lines of CREATE MATERIALIZED VIEW or five hundred lines of DataStream API code — and it is the single component senior data engineers get wrong most often because "just use Flink" is no longer the only answer in 2026. Every continuous computation your business runs — a live top-10 leaderboard, a sliding-window fraud score, a Kafka-fed dashboard, a real-time feature for online inference — has to be expressed once, kept incrementally consistent as new events arrive, and served to downstream consumers without re-scanning the entire input, without losing correctness on late data, and without saturating the state store as windows grow. The engineering trade-off does not live in "should we adopt streaming SQL" — every stack with sub-minute freshness requirements needs it — but in which streaming SQL engine you pick and what it costs the operations budget.

This guide is the senior-DE walkthrough you wished existed the first time an interviewer asked "walk me through Materialize, RisingWave and Timeplus and when each wins," or "what is differential dataflow and why does Materialize care about it?", or "explain how RisingWave decouples compute from state and what that buys you at cloud scale." It walks through the three modern contenders — Materialize (Rust + differential dataflow, strict-serializable incremental view maintenance), RisingWave (Rust + Postgres wire, K8s-native with S3-backed state), and Timeplus / Proton (ClickHouse foundation, unified streaming + historical SQL in one query) — the "four axes" interviewers actually probe (consistency, state storage, latency, unified stream+batch), the canonical templates for each, and the decision matrix that places all three against Flink SQL and ksqlDB. 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.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse on the streaming practice library →, and sharpen the aggregation muscle with the aggregation practice library →.

---

On this page

• Why streaming SQL matters in 2026
• Materialize — differential dataflow + strong consistency
• RisingWave — cloud-native + Postgres-wire streaming
• Timeplus — unified streaming + historical on the Proton engine
• Decision matrix + Flink SQL / ksqlDB positioning + interview signals
• Cheat sheet — streaming SQL recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why streaming SQL matters in 2026

Three engines, three wildly different bets on the streaming-SQL substrate — the choice binds you to a consistency, state, and topology story for years

The one-sentence invariant: streaming SQL is the pattern where a continuous query defined once — usually as CREATE MATERIALIZED VIEW ... AS SELECT ... — is kept incrementally consistent by the engine as new events arrive on the source stream, without the developer ever writing a KStream.map(...).aggregate(...) DataStream program — and the three modern Rust-and-C++ contenders (Materialize, RisingWave, Timeplus) each make a distinct bet on the consistency model, the state store, the latency floor, and whether streaming + historical live in the same engine or on separate stacks. The engine you pick in month one is the engine your entire dashboard, feature store, and alerting layer hard-codes assumptions against, because migrating a MATERIALIZED VIEW graph between engines is not pg_dump | psql — it is a re-architecture that touches every downstream consumer that hard-codes SUBSCRIBE semantics, output-topic partition keys, or EMIT CHANGES cadences.

The four axes interviewers actually probe.

• Consistency model. Materialize is strict serializable by construction — every write is fully ordered across all views, and every read observes a consistent point-in-time cut across the entire dataflow graph. RisingWave is eventually consistent per barrier — checkpoints define per-actor consistent cuts, but cross-view reads inside a barrier are not strict-serializable. Timeplus / Proton is streaming-eventual — the streaming query emits changes as they flow, historical joins are point-in-time snapshots. Interviewers open with this axis because it separates people who understand the correctness bill of streaming SQL from those who "just know it's fast."
• State storage. Materialize keeps arrangements in memory (with pluggable persistence via persist on S3 for durability). RisingWave stores state in an LSM tree on S3 (Hummock) — compute is stateless, state is remote. Timeplus / Proton stores state on local disk (ClickHouse-style MergeTree) with S3 tiering for older data. This is the axis that decides the operational bill — memory is fast but expensive, S3 is durable but adds latency, local NVMe is a middle ground with backup complexity.
• Latency. Materialize is sub-millisecond for arrangement lookups, single-digit-millisecond for view maintenance. RisingWave is 50-500 ms end-to-end (Kafka → view → sink) because barriers align state flushes to S3. Timeplus / Proton is sub-second for streaming views and ClickHouse-native for historical scans. Latency budgets drive product decisions; know yours before you pick an engine.
• Unified stream + batch. Materialize handles historical via bootstrap from a Postgres source (via CREATE SOURCE) and treats it as one continuous stream. RisingWave is streaming-first — historical is served via views over compacted state or via a warehouse sink. Timeplus / Proton is the only engine that lets you write one SQL query joining a stream (unbounded) to a table (bounded) as first-class citizens — the "unified" pitch.

The 2026 reality — streaming SQL has split into three architectural bets.

• Materialize is the correctness-first bet. Frank McSherry's differential dataflow paper (Naiad, 2013) is the foundational research; Materialize is the productionised, PostgreSQL-wire-compatible implementation. When your streaming query is a financial reconciliation, an ad-tech attribution, or anything where "the number must exactly match the batch pipeline" is the requirement, Materialize is the answer.
• RisingWave is the cloud-native bet. Compute is stateless containers on Kubernetes; state lives in S3-backed Hummock (an LSM tree written to object storage). The bet is that "compute is cheap, state is expensive, put state where storage is cheapest." RisingWave is what you pick when you need horizontal scaling to hundreds of vCPUs without capacity planning your state-store disks.
• Timeplus / Proton is the unified bet. Built on ClickHouse's storage engine, it lets you write SELECT ... FROM stream(events) s JOIN table(dim_users) d ON ... in one query. When your workload is fundamentally analytical with a streaming freshness requirement — real-time dashboards, live cohort analysis, streaming feature engineering — Timeplus removes the impedance mismatch between Flink jobs and ClickHouse warehouses.

What interviewers listen for.

• Do you name all three engines without prompting, and place each in one of the three architectural bets? — senior signal.
• Do you say "differential dataflow" in the first sentence when Materialize comes up, not just "incremental view maintenance"? — required answer for a senior role.
• Do you name "state on S3 via Hummock" as RisingWave's differentiator, not just "cloud-native"? — senior signal.
• Do you position Timeplus against ClickHouse ("Proton is the OSS streaming layer that shares ClickHouse's storage engine"), not against Flink? — senior signal.
• Do you describe streaming SQL as "an incrementally maintained materialised view over an unbounded input" rather than as vague "continuous querying"? — required answer.

Worked example — the four-axis comparison table

Detailed explanation. The single most useful artifact for a streaming SQL interview is a memorised 4×3 comparison table. Every senior streaming-engine discussion converges on this table within the first ten minutes; having it in your head is what separates a fluent answer from a stumbling one. Walk through building the table for a hypothetical live-leaderboard use case that needs to reach a dashboard, a feature store, and a warehouse.

• Source. Kafka topic events at 50k events/sec, JSON payload with user_id, score, event_ts.
• Query. SELECT user_id, SUM(score) FROM events WHERE event_ts >= now() - INTERVAL '1 hour' GROUP BY user_id — a rolling 1-hour top-N.
• Downstreams. Live dashboard (needs sub-second), feature store (needs seconds), warehouse (needs minutes).
• Correctness. The top-N must eventually match a batch pipeline running the same query on the same data.

Question. Build the three-engine comparison for the live leaderboard and pick the engine each downstream should subscribe to.

Input.

Axis	Materialize	RisingWave	Timeplus / Proton
Consistency	strict serializable	barrier-consistent	streaming-eventual
State store	in-memory + persist on S3	Hummock LSM on S3	ClickHouse MergeTree (local + S3 tier)
Latency (p95)	5-50 ms	100-500 ms	100 ms - 1 s
Unified stream+batch	source-bootstrapped only	streaming-first	native stream + table join

Code.

-- The same rolling-1h top-N query in each engine's dialect

-- Materialize
CREATE SOURCE events_src
    FROM KAFKA BROKER 'kafka:9092' TOPIC 'events'
    FORMAT BYTES ENVELOPE UPSERT;

CREATE MATERIALIZED VIEW top_users_1h AS
SELECT user_id,
       SUM(score) AS total_score
FROM   events_src
WHERE  event_ts >= mz_now() - INTERVAL '1 hour'
GROUP  BY user_id;

-- RisingWave (Postgres-wire compatible)
CREATE SOURCE events_src (
    user_id  BIGINT,
    score    BIGINT,
    event_ts TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic     = 'events',
    properties.bootstrap.server = 'kafka:9092'
) FORMAT PLAIN ENCODE JSON;

CREATE MATERIALIZED VIEW top_users_1h AS
SELECT user_id,
       SUM(score) AS total_score
FROM   events_src
WHERE  event_ts >= now() - INTERVAL '1 hour'
GROUP  BY user_id;

-- Timeplus / Proton
CREATE STREAM events_src (
    user_id  int64,
    score    int64,
    event_ts datetime64(3)
) SETTINGS type = 'kafka',
           brokers = 'kafka:9092',
           topic = 'events',
           data_format = 'JSONEachRow';

CREATE MATERIALIZED VIEW top_users_1h AS
SELECT user_id,
       sum(score) AS total_score
FROM   events_src
WHERE  event_ts >= now() - INTERVAL 1 HOUR
GROUP  BY user_id
EMIT   CHANGES;

Step-by-step explanation.

1. The three engines converge on the same surface — CREATE SOURCE + CREATE MATERIALIZED VIEW in a SQL dialect that any DE can read. The difference is under the hood: dataflow graph vs actor DAG vs streaming-Proton pipeline.
2. Materialize's mz_now() is the logical current time used by the differential dataflow engine; it advances as the source catches up and is what makes the view strict-serializable. RisingWave's now() is barrier-defined; Timeplus's now() is wall clock, which is fine because Proton's model is streaming-eventual anyway.
3. Materialize's ENVELOPE UPSERT handles Kafka's key-based upsert semantics natively — a repeated key overwrites the previous row. RisingWave's FORMAT PLAIN ENCODE JSON treats each Kafka record as an insert; upserts require FORMAT UPSERT. Timeplus streams are append-only by default.
4. All three views are queryable while streaming — you can SELECT * FROM top_users_1h from any Postgres or ClickHouse client, and get the current top-N. The engines differ on what "current" means: Materialize gives a strict-serializable snapshot, RisingWave gives the last barrier's snapshot, Timeplus gives eventually-consistent state.
5. The pick per downstream: Materialize for the dashboard (correctness matters and the query is small), RisingWave for the feature store (horizontal scaling matters as feature volume grows), Timeplus for warehouse (unified stream+batch means the same query serves both live and historical).

Output.

Consumer	Recommended engine	Why
Live dashboard	Materialize	strict-serializable; sub-50ms freshness
Feature store	RisingWave	scales horizontally; state on S3 = cheap
Warehouse + backfill	Timeplus / Proton	unified stream+batch; ClickHouse-native scans

Rule of thumb. Never pick a streaming SQL engine based on "which one is trendy." Pick it based on (consistency × state × latency × unified-batch) — the four axes. Write the table on a whiteboard first; the engine falls out of the constraints.

Worked example — what interviewers actually probe

Detailed explanation. The senior data-engineering streaming-SQL interview has a predictable structure: the interviewer opens with an ambiguous question ("how would you build a live top-N leaderboard for 50k events/sec?"), then progressively narrows to test whether you know the four axes. The candidates who name the engine in sentence one score highest; the candidates who describe "a Kafka pipeline" score lowest. Walk through the interview grading rubric.

• Ambiguous opener. "How would you compute a live top-N over a 50k-events/sec Kafka topic?" — invites you to name an engine.
• Follow-up 1. "What consistency does your engine give me?" — probes consistency axis.
• Follow-up 2. "Where does state live?" — probes state-store axis.
• Follow-up 3. "How does this compare to Flink SQL?" — probes ecosystem positioning.
• Follow-up 4. "How would you replay the last hour?" — probes replay semantics.

Question. Draft a 5-minute senior streaming SQL answer that covers all four axes without waiting to be asked.

Input.

Interview signal	Weak answer	Senior answer
Engine named	"we'd use Kafka Streams"	"Materialize for correctness; RisingWave for cloud-native scale; Timeplus for unified stream+batch"
Consistency	"eventually consistent"	"strict-serializable via differential dataflow in Materialize"
State store	"an in-memory cache"	"in-memory arrangements (Materialize); S3-backed Hummock LSM (RisingWave); MergeTree on local disk (Timeplus)"
vs Flink SQL	"Flink is JVM, these are Rust"	"Flink is mature and versatile but not IVM-first; the three Rust/C++ engines specialise"
Replay	"restart the connector"	"rebootstrap from source's upstream position (Kafka offset, Postgres LSN)"

Code.

Senior streaming SQL answer template (5 minutes)
================================================

Minute 1 — name the pattern up front
  "I'd default to a streaming SQL engine — the modern picks are
   Materialize, RisingWave, and Timeplus/Proton. The pick depends on
   whether correctness, cloud-scale, or unified stream+batch matters
   most for the workload."

Minute 2 — consistency model
  "Materialize is strict-serializable via differential dataflow.
   RisingWave is barrier-consistent — each Chandy-Lamport-style
   checkpoint defines a consistent cut, but sub-barrier reads can
   observe partial state. Timeplus is streaming-eventual — the
   materialised view emits changes as they arrive."

Minute 3 — state storage
  "Materialize keeps arrangements in memory with S3-backed durability
   via `persist`. RisingWave stores state in Hummock, an LSM tree
   directly on S3 — compute is stateless, which is huge for K8s
   autoscaling. Timeplus stores in ClickHouse-style MergeTree on
   local disk with S3 tiering."

Minute 4 — vs Flink SQL and ksqlDB
  "Flink SQL is mature, JVM-based, and covers batch+stream but is
   not IVM-first — it's a stateful stream processor with a SQL front
   end. ksqlDB is Confluent-locked and older-generation. The three
   Rust/C++ engines specialise: correctness, cloud-scale, unified
   analytics."

Minute 5 — replay + failure semantics
  "Every engine tracks upstream progress (Kafka offset, Postgres
   LSN, S3 file position). Replay is: reset the source position,
   drop the materialised view, re-create. Materialize's `persist`
   layer makes recovery incremental — the last checkpoint restores
   arrangements. RisingWave restores from the last Hummock
   checkpoint. Timeplus restores from MergeTree parts."

Step-by-step explanation.

1. Minute 1 is the crucial framing. Naming three engines and mapping each to one of the three bets (correctness, scale, unified) signals that you understand this is a pick per workload decision, not a "one true streaming DB" decision. Weak candidates commit to one engine before knowing the workload.
2. Minute 2 addresses consistency before the interviewer asks. This preempts the common trap where you commit to "streaming SQL" without owning the correctness bill. Naming differential dataflow specifically is what senior interviewers listen for.
3. Minute 3 covers state storage — the axis that drives the operational bill. "State on S3 via Hummock" is the RisingWave phrase every interviewer wants to hear; "arrangements in memory + persist" is the Materialize one.
4. Minute 4 handles the ecosystem-positioning probe. Flink is the elephant in the room; positioning the three new engines as "specialists relative to a versatile generalist" is the mature framing.
5. Minute 5 covers replay — the reliability axis. Every engine has different replay semantics; naming three concrete replay mechanisms shows you've operated at least one of them.

Output.

Grading criterion	Weak score	Senior score
Names three engines	rare	mandatory
Names differential dataflow	rare	required
Names Hummock / S3 state	rare	senior signal
Positions vs Flink	occasional	senior signal
Names replay mechanism	rare	senior signal

Rule of thumb. The senior streaming SQL answer is a 5-minute monologue that covers all four axes without waiting for the follow-ups. Rehearse it once; deploy it every time.

Worked example — the "pick the engine" decision tree

Detailed explanation. Given a new streaming workload, the senior architect runs a 4-question decision tree in their head. Codifying the tree makes the interview answer reproducible: any interviewer can hand you a scenario and you can walk the tree out loud. Walk through the tree with three canonical scenarios: financial reconciliation, cloud-scale ad tech, and unified stream+batch analytics.

• Q1. Is strict-serializable correctness a hard requirement? → yes = Materialize; no = go to Q2.
• Q2. Do you need horizontal scaling to hundreds of vCPUs without capacity-planning state disks? → yes = RisingWave; no = go to Q3.
• Q3. Do you need one SQL query joining a stream to a table as first-class citizens? → yes = Timeplus / Proton; no = go to Q4.
• Q4. Do you already have Flink infrastructure and a JVM team? → yes = Flink SQL; no = default to RisingWave for cloud scale.
• Q5 (parallel branch). Are you Confluent-locked and need a Kafka-first engine? → ksqlDB (with caveats about ecosystem maturity).

Question. Walk the decision tree for the three scenarios and record the engine each ends up with.

Input.

Scenario	Q1 (correctness?)	Q2 (cloud scale?)	Q3 (unified?)	Q4 (Flink infra?)
Financial reconciliation	yes	—	—	—
Cloud-scale ad tech	no	yes	—	—
Real-time cohort analysis	no	no	yes	—

Code.

# Decision-tree helper (illustrative)
def pick_streaming_engine(
    needs_strict_serializable: bool,
    needs_horizontal_scale: bool,
    needs_unified_stream_batch: bool,
    has_flink_infra: bool,
    kafka_confluent_locked: bool,
) -> str:
    """Return the primary streaming SQL engine for a workload."""
    if needs_strict_serializable:
        return "Materialize"
    if needs_horizontal_scale:
        return "RisingWave"
    if needs_unified_stream_batch:
        return "Timeplus / Proton"
    if has_flink_infra:
        return "Flink SQL"
    if kafka_confluent_locked:
        return "ksqlDB (caveats)"
    return "RisingWave (safe default)"


# Walk the three scenarios
print(pick_streaming_engine(True,  False, False, False, False))
# → Materialize

print(pick_streaming_engine(False, True,  False, False, False))
# → RisingWave

print(pick_streaming_engine(False, False, True,  False, False))
# → Timeplus / Proton

Step-by-step explanation.

1. Scenario 1 — financial reconciliation where every row must exactly match a nightly batch pipeline. Q1 = yes → Materialize. Differential dataflow's strict-serializable guarantee is the deciding factor; a barrier-consistent engine could produce a top-N that differs from the batch answer by an in-flight barrier's worth of state.
2. Scenario 2 — cloud-scale ad tech serving 200k events/sec to a feature store, where compute nodes come and go with traffic. Q1 = no (attribution is eventually-consistent by definition), Q2 = yes → RisingWave. K8s-native compute + S3-backed state means autoscaling doesn't require restoring state to a new node.
3. Scenario 3 — real-time cohort analysis where the query joins a live event stream to a dimension table of user attributes. Q3 = yes → Timeplus / Proton. The stream + table join syntax makes this a one-query problem instead of a stream-enrichment-then-warehouse-scan pipeline.
4. If none of Q1-Q3 pass and you have Flink infrastructure, Flink SQL is the pragmatic answer — no new operator, existing on-call knowledge, but you pay in JVM footprint and less IVM-native semantics.
5. The parallel Q5 branch (ksqlDB) is orthogonal — pick it only when the org is Confluent-locked and the workload is small enough that ksqlDB's operational limitations don't bite. New streaming workloads in 2026 almost never start on ksqlDB.

Output.

Scenario	Primary engine	Reason
Financial reconciliation	Materialize	strict-serializable correctness
Cloud-scale ad tech	RisingWave	K8s + S3 state = horizontal scale
Real-time cohort analysis	Timeplus / Proton	unified stream + table join

Rule of thumb. The four-question decision tree is a whiteboard-friendly answer. Practice walking it end-to-end so an interviewer can hand you any scenario and get an engine name in under 60 seconds.

Senior interview question on streaming SQL engine selection

A senior interviewer often opens with: "You inherit a Kafka Streams pipeline computing a rolling 1-hour top-100 users by score for a live leaderboard. It's currently 800 lines of Java, keeps state in RocksDB per-instance, and rebalances take 90 seconds during which the leaderboard is stale. Walk me through the streaming SQL engine you'd migrate to, the source and view definitions, and the failure modes you'd guard against."

Solution Using Materialize with a Kafka source, an incremental materialised view, and SUBSCRIBE for dashboard delivery

-- Step 1 — create a strongly-typed Kafka source (Materialize handles decoding)
CREATE CONNECTION kafka_conn TO KAFKA (
    BROKER 'b-1.kafka.internal:9092',
    SASL MECHANISMS = 'PLAIN',
    SASL USERNAME = SECRET kafka_user,
    SASL PASSWORD = SECRET kafka_pass
);

CREATE SOURCE events_src
    FROM KAFKA CONNECTION kafka_conn (TOPIC 'events')
    KEY FORMAT BYTES
    VALUE FORMAT JSON
    ENVELOPE UPSERT
    WITH (SIZE = '2xsmall');

-- Materialize decodes the JSON envelope into columns
CREATE VIEW events AS
SELECT (data->>'user_id')::BIGINT       AS user_id,
       (data->>'score')::BIGINT          AS score,
       (data->>'event_ts')::TIMESTAMPTZ  AS event_ts
FROM   events_src;

-- Step 2 — incrementally-maintained materialised view (the leaderboard)
CREATE MATERIALIZED VIEW top_users_1h AS
SELECT user_id,
       SUM(score) AS total_score
FROM   events
WHERE  event_ts >= mz_now() - INTERVAL '1 hour'
GROUP  BY user_id;

-- Index for point lookups from the dashboard
CREATE DEFAULT INDEX ON top_users_1h;

-- Step 3 — SUBSCRIBE feeds the dashboard incremental deltas over psql wire
COPY (SUBSCRIBE (
    SELECT user_id, total_score
    FROM   top_users_1h
    ORDER  BY total_score DESC
    LIMIT  100
)) TO STDOUT;

-- Wire format is: mz_timestamp | mz_diff | user_id | total_score
-- Client applies +/- diffs to its local top-100 to stay in sync

Step-by-step trace.

Step	Before (Kafka Streams)	After (Materialize)
Code footprint	800 lines Java	~15 lines SQL
State store	per-instance RocksDB	in-memory arrangements + S3 persist
Consistency	eventually consistent	strict-serializable
Rebalance downtime	90 s stale leaderboard	~1 s (arrangement rehydration)
Dashboard delivery	HTTP polling of REST API	SUBSCRIBE over psql wire (push)
Bootstrap	replay full topic on new instance	initial arrangement rebuild from persist
Failure surface	RocksDB corruption, JVM heap pressure	Postgres-wire-familiar; small persist blast radius

After the migration, the leaderboard is defined by 15 lines of SQL — every senior DE on the team can read and modify it, and the dashboard receives push-based deltas over the Postgres wire protocol with strict-serializable freshness under 50 ms.

Output:

Metric	Before	After
Freshness p95	500 ms - 2 s (varies with rebalance)	30-50 ms
Code size	~800 lines Java	~15 lines SQL
Consistency	eventually consistent	strict-serializable
Correctness against batch pipeline	~99.5% row-parity	100%
Rebalance impact	90 s stale	~1 s
Operational surface	JVM heaps, RocksDB tuning	Postgres wire + persist

Why this works — concept by concept:

• Differential dataflow — Frank McSherry's operator algebra that Materialize is built on. Every input change is expressed as (row, timestamp, +1 | -1); every operator (map, join, group-by) transforms these (data, time, diff) triples incrementally. When the input top-100 changes because one user's score went up, only the affected rows are recomputed — not the entire top-N.
• Arrangements — the multi-versioned indexed state that differential dataflow operators share. Arrangements are the reason Materialize can serve SELECT queries against a materialised view at sub-millisecond latency: the arrangement is already indexed by the query key, so a lookup is O(log n) into a sorted trace.
• SUBSCRIBE + mz_diff — the push-based delivery mechanism. The client receives +1 / -1 diffs; applying them to a local snapshot keeps the client's view of the top-100 in sync without re-fetching the whole leaderboard. This is the Materialize equivalent of Kafka's log-compaction: incremental change events, not full snapshots.
• Strict serializable via logical timestamps — mz_now() returns a logical time that advances as sources catch up. Every read is served at a single logical time across the entire dataflow graph, which is what makes cross-view queries strict-serializable. This is not achievable in an eventually-consistent engine.
• Cost — one Materialize 2xsmall size (2 vCPU + 4 GB memory for arrangements) + S3 persist (a few dollars/month for the object storage). Compared to Kafka Streams' JVM cluster + RocksDB tuning + rebalance stormss, this is a step-change reduction in operational surface. O(1) SQL per new leaderboard; the underlying dataflow engine handles the incrementalisation.

SQL
Topic — sql
SQL problems on streaming views and top-N patterns

Practice →

Streaming Topic — streaming Streaming SQL and continuous-query problems

Practice →

---

2. Materialize — differential dataflow + strong consistency

CREATE MATERIALIZED VIEW ... SUBSCRIBE on the differential-dataflow substrate — the correctness-first streaming SQL engine

The mental model in one line: materialize streaming is the pattern where every streaming query is compiled into a differential-dataflow graph of operators that maintains its output incrementally as inputs change, with strict-serializable consistency guaranteed by a logical timestamp system, and the whole thing speaks the Postgres wire protocol so any psql, SUBSCRIBE, or JDBC client is a first-class consumer — it is the correctness-first choice when your streaming query must match a batch pipeline down to the row and the interviewer opens with "walk me through incremental view maintenance". Every senior DE building a streaming-analytics stack owes it to themselves to have the Materialize model in their head; the differential dataflow paper is one of the foundational readings of modern streaming.

The four axes for Materialize.

• Consistency. Strict serializable by construction. Every read at a given mz_now() observes a consistent point-in-time cut across the entire dataflow graph — even for cross-view joins that touch data from three different sources. This is the strongest guarantee in the streaming SQL space.
• State storage. In-memory arrangements are the primary substrate — differential dataflow's multi-versioned indexed traces that let operators share state. Durability is provided by the persist layer, which writes arrangement snapshots to S3 (or GCS / Azure Blob) at configurable intervals. On failure, the engine rehydrates from the last persist checkpoint plus the source's replayable log.
• Latency. Sub-millisecond for arrangement lookups (i.e. SELECT against an indexed materialised view); single-digit-millisecond for view maintenance under load. SUBSCRIBE clients receive diffs within one dataflow tick of an input change.
• Ecosystem. Postgres wire compatibility means every existing SQL tool works — psql, DataGrip, DBeaver, JDBC, the Postgres driver in every language. Sources include Kafka (with schema-registry Avro / Protobuf), Postgres CDC (via logical replication), Redpanda, Kinesis, S3, and webhook connectors. Sinks include Kafka, Snowflake, and Postgres.

The differential-dataflow substrate — what makes IVM tractable.

• (data, time, diff) triples. Every input change is expressed as a triple: the row payload, the logical timestamp, and a signed multiplicity (+1 for insert, -1 for delete). Operators consume triples and produce triples; the engine's job is to keep the mathematics correct as new triples arrive.
• Operators as functions on collections. map, filter, join, group_by, reduce, distinct all have differential-dataflow-native implementations that produce correct incremental outputs. Join is the interesting one: differential-dataflow join is a two-arrangement lookup that produces output triples as each input triple arrives, without ever materialising the full Cartesian product.
• Arrangements — the shared indexed state. An arrangement is a multi-versioned sorted trace indexed by a key. Operators that need to look up by that key share the arrangement; when a new triple arrives at time T, the arrangement's trace at time T-1 gives the operator the "before" state for correct incrementalisation.
• Logical timestamps. Every triple carries a timestamp; the engine advances a global logical time (mz_now()) as sources report their upstream progress (Kafka partition frontiers, Postgres LSN, etc.). All reads at a given mz_now() observe a consistent cut — this is the mechanism behind strict-serializable.

The persist layer — how Materialize survives crashes.

• What it is. A durable, S3-backed, log-structured storage layer that periodically snapshots arrangements and appends new input triples. Recovery replays the last snapshot + the appended log to reconstitute arrangement state.
• Bootstrap. On first start of a materialised view, persist writes the initial arrangement to S3. Subsequent restarts hydrate from S3 rather than re-consuming the source from the beginning.
• The trade-off. S3 writes add latency (measured in tens of milliseconds); Materialize batches writes to amortise the cost. For extremely low-latency workloads, tune the batch size; for cost-sensitive workloads, extend the batch interval.

Common interview probes on Materialize.

• "What is differential dataflow?" — required answer: an operator algebra where every input change is a (data, time, diff) triple, and operators produce correct incremental outputs.
• "What does Materialize guarantee across views?" — required answer: strict-serializable at any mz_now().
• "How does SUBSCRIBE differ from polling the view?" — SUBSCRIBE is push-based delta delivery; polling re-materialises the query result.
• "What's the failure story?" — persist snapshots to S3 + source replayability; recovery is bounded by the snapshot interval, not the source retention.
• "When does Materialize not win?" — extremely large state (hundreds of GB) is expensive because arrangements are memory-resident by default; RisingWave's S3-native state is cheaper at that scale.

Worked example — an incremental fraud-score view over a Kafka source

Detailed explanation. The canonical Materialize workload: a fraud team wants a live per-user fraud score defined as weighted_sum(recent_activity) + risk_flags that updates as new events arrive. Build the source, the intermediate views, and the final materialised view that a dashboard subscribes to.

• Source. Kafka activity topic with user_id, event_type, amount_cents, event_ts, ip_address.
• Intermediate. A weighted_activity view that applies event-type-specific weights.
• Final. A fraud_scores materialised view aggregating per user with a 24-hour tumbling window.

Question. Write the connection, source, intermediate view, and materialised view. Show a SUBSCRIBE session that a dashboard uses to receive live updates.

Input.

Layer	Object	Purpose
Connection	kafka_conn	authenticated Kafka endpoint
Source	activity_src	typed Kafka source
View	weighted_activity	non-materialised — just a projection
Materialized view	fraud_scores	IVM-maintained aggregate
Client	psql SUBSCRIBE	push delta consumer

Code.

-- 1. Kafka connection (SASL/SCRAM secrets managed by Materialize)
CREATE SECRET kafka_user AS 'materialize-reader';
CREATE SECRET kafka_pass AS '...';

CREATE CONNECTION kafka_conn TO KAFKA (
    BROKER 'b-1.kafka:9092,b-2.kafka:9092,b-3.kafka:9092',
    SASL MECHANISMS = 'SCRAM-SHA-512',
    SASL USERNAME = SECRET kafka_user,
    SASL PASSWORD = SECRET kafka_pass
);

-- 2. Source — Materialize decodes JSON on the fly
CREATE SOURCE activity_src
    FROM KAFKA CONNECTION kafka_conn (TOPIC 'activity')
    FORMAT JSON
    WITH (SIZE = 'small');

-- 3. Typed view over the source
CREATE VIEW activity AS
SELECT (data->>'user_id')::BIGINT       AS user_id,
       (data->>'event_type')::TEXT      AS event_type,
       (data->>'amount_cents')::BIGINT  AS amount_cents,
       (data->>'event_ts')::TIMESTAMPTZ AS event_ts,
       (data->>'ip_address')::TEXT      AS ip_address
FROM   activity_src;

-- 4. Weighted activity — non-materialised, just a projection
CREATE VIEW weighted_activity AS
SELECT user_id,
       event_ts,
       CASE event_type
            WHEN 'login_failed'   THEN 5 * amount_cents / 100 + 20
            WHEN 'password_reset' THEN 40
            WHEN 'txn'            THEN GREATEST(amount_cents / 100, 1)
            WHEN 'geo_mismatch'   THEN 100
            ELSE                       1
       END AS weight
FROM   activity;

-- 5. Materialised view — IVM incrementally maintained
CREATE MATERIALIZED VIEW fraud_scores AS
SELECT user_id,
       SUM(weight)  AS fraud_score,
       COUNT(*)     AS event_count,
       MAX(event_ts) AS last_event_at
FROM   weighted_activity
WHERE  event_ts >= mz_now() - INTERVAL '24 hours'
GROUP  BY user_id;

CREATE DEFAULT INDEX ON fraud_scores;

-- 6. Dashboard subscribes for push-based deltas
COPY (SUBSCRIBE (
    SELECT user_id, fraud_score
    FROM   fraud_scores
    WHERE  fraud_score > 500
    ORDER  BY fraud_score DESC
)) TO STDOUT;

-- Wire output:
-- mz_timestamp | mz_diff | user_id | fraud_score
-- 1721060000000 | +1 | 42 | 812
-- 1721060030000 | -1 | 42 | 812
-- 1721060030000 | +1 | 42 | 934

Step-by-step explanation.

1. The CREATE CONNECTION object separates authentication from source definitions — one connection can back many sources, and secret rotation happens in one place. This is a Materialize-specific convention that keeps DDL clean.
2. The activity_src source pulls raw Kafka records; the activity VIEW turns them into typed columns. Splitting parse from consume makes the pipeline debuggable — you can SELECT from either level.
3. The weighted_activity VIEW is not materialised — it's a projection that gets inlined into any downstream materialised view that references it. This is Materialize's dataflow-optimisation lever: view without MATERIALIZED = compile-time fusion; MATERIALIZED VIEW = maintained arrangement.
4. The fraud_scores materialised view is where the arrangement lives. Each new activity event flows through: source → typed → weighted → aggregate; the aggregate operator maintains a per-user arrangement that emits diffs when a user's sum changes.
5. The dashboard's SUBSCRIBE receives mz_timestamp | mz_diff | ... rows. On a fraud score change from 812 to 934 for user 42, the client sees two rows: -1 812 (retract the old value) and +1 934 (assert the new one). The client maintains a local snapshot by applying diffs.

Output.

SUBSCRIBE event	mz_diff	user_id	fraud_score	Meaning
initial catch-up	+1	7	620	user 7 crossed threshold
initial catch-up	+1	42	812	user 42 already above
streaming delta	-1	42	812	retract old score
streaming delta	+1	42	934	assert new score
streaming delta	-1	7	620	user 7 dropped below threshold

Rule of thumb. For any Materialize workload, keep intermediate transformations as non-materialised VIEWs (so Materialize can fuse them into a single dataflow), and materialise only the leaf views the dashboard actually subscribes to. Over-materialising doubles arrangement memory without a latency benefit.

Worked example — Postgres CDC source + join to a live dimension

Detailed explanation. Materialize's Postgres CDC source uses logical replication to consume INSERT / UPDATE / DELETE events from a source database, exposing them as a table-shaped source that supports upserts and deletes natively. Joining this stream to another streaming source produces a live enriched view — the canonical "enrich activity with user tier" workload.

• Postgres source. users table with id, email, tier, created_at.
• Kafka source. activity topic as above.
• Join. activity JOIN users ON user_id = users.id produces a per-activity enriched stream.
• Delivery. A downstream materialised view for tier-specific fraud dashboards.

Question. Configure a Postgres CDC source, define an enriched view that joins Kafka activity to the Postgres users table, and show the SQL that a per-tier dashboard would run.

Input.

Component	Value
Postgres source	logical replication slot mz_slot
Postgres publication	mz_pub FOR TABLE users
Users table columns	id BIGINT PK, email TEXT, tier TEXT, created_at TIMESTAMPTZ
Kafka source	activity topic (as prior example)
Join semantics	Materialize IVM join — incremental on either side

Code.

-- 1. Postgres side (one-time setup on the primary)
-- postgresql.conf:  wal_level = logical, max_replication_slots = 10
CREATE ROLE mz_reader WITH LOGIN REPLICATION PASSWORD 'strong-secret';
GRANT SELECT ON public.users TO mz_reader;
CREATE PUBLICATION mz_pub FOR TABLE public.users;

-- 2. Materialize side — CDC source
CREATE SECRET pg_pass AS 'strong-secret';

CREATE CONNECTION pg_conn TO POSTGRES (
    HOST 'db-primary.internal',
    PORT 5432,
    USER 'mz_reader',
    PASSWORD SECRET pg_pass,
    DATABASE 'production',
    SSL MODE 'require'
);

CREATE SOURCE users_src
    FROM POSTGRES CONNECTION pg_conn (PUBLICATION 'mz_pub')
    FOR ALL TABLES;

-- users_src exposes `public.users` as a queryable, streaming table

-- 3. Enriched activity view — Materialize IVM join
CREATE VIEW enriched_activity AS
SELECT a.user_id,
       a.event_type,
       a.event_ts,
       a.weight,
       u.tier,
       u.email
FROM   weighted_activity a
JOIN   public.users u ON u.id = a.user_id;

-- 4. Per-tier fraud score — the leaf materialised view
CREATE MATERIALIZED VIEW fraud_scores_by_tier AS
SELECT tier,
       user_id,
       SUM(weight) AS fraud_score
FROM   enriched_activity
WHERE  event_ts >= mz_now() - INTERVAL '24 hours'
GROUP  BY tier, user_id;

CREATE DEFAULT INDEX ON fraud_scores_by_tier;

Step-by-step explanation.

1. The Postgres side needs wal_level = logical and a publication. This is standard log-based CDC setup; Materialize's Postgres source consumes the WAL via the standard logical-replication protocol (like any Debezium connector would).
2. The Materialize CREATE SOURCE ... FROM POSTGRES creates one source that exposes every table in the publication. Materialize snapshots the current state of users at slot creation time, then tails the WAL for subsequent changes.
3. The enriched_activity VIEW is a join between the Kafka-fed weighted_activity and the Postgres-fed users. Differential dataflow's incremental join is what makes this tractable — when a user's tier changes in Postgres, only that user's rows in the join output are updated, not the entire history.
4. The fraud_scores_by_tier materialised view is grouped by tier, user_id. As new activity events arrive, only the affected (tier, user_id) groups are recomputed. As a user's tier changes in Postgres, the join output emits a retraction of the old-tier row and an assertion of the new-tier row, and the aggregate updates both group sums.
5. The result: a dashboard filtering WHERE tier = 'gold' sees a strict-serializable, live-updated fraud score for gold-tier users, with no per-event join code — the entire pipeline is 20 lines of SQL.

Output.

Change	Effect on enriched_activity	Effect on fraud_scores_by_tier
New activity for user 42	+1 row appended	+1 diff on ('gold', 42) group
User 42's tier changes gold → platinum	-N (gold rows) + N (platinum rows)	retraction on ('gold', 42); assertion on ('platinum', 42)
Old activity ages out of 24h window	rows fall out of WHERE filter	negative diff on affected groups

Rule of thumb. When joining Kafka streams to Postgres CDC sources in Materialize, always let the differential dataflow join handle the join incrementally — never denormalise upstream in a stream processor. Materialize's IVM join gives you correct maintenance across both sides at zero per-event cost beyond the arrangement lookup.

Worked example — CLUSTER sizing + persist recovery semantics

Detailed explanation. Materialize deployments live on named CLUSTERS — logical compute pools sized independently from storage. A CREATE MATERIALIZED VIEW runs on the current cluster; you can move a view across clusters, run multiple views on one cluster, or run one view on multiple replicas for high availability. Understanding cluster sizing and persist recovery is the difference between "streaming SQL works" and "streaming SQL is production-grade."

• Cluster sizes. 2xsmall, xsmall, small, medium, large, xlarge — each doubles vCPU and memory.
• Replicas. A cluster can have 1 replica (default) or N replicas; queries are served from any replica; state is replicated per-replica.
• persist interval. Configurable; default is 1 second. Lower = faster recovery, higher WAL rate; higher = slower recovery, lower cost.

Question. Size a production cluster for the fraud-scoring pipeline and design the recovery test that proves the SLA.

Input.

Requirement	Target
Sources	2 (Kafka activity, Postgres users)
Materialized views	4 (weighted, enriched, fraud_scores, fraud_scores_by_tier)
Peak input rate	20k events/sec Kafka, ~10 users/sec Postgres
p95 freshness	< 100 ms
Recovery SLA	< 60 s to serve queries after replica loss

Code.

-- 1. Create a dedicated cluster for the fraud pipeline
CREATE CLUSTER fraud_cluster SIZE 'medium' REPLICATION FACTOR 2;

-- 2. Point new sources and views at that cluster
SET cluster = fraud_cluster;

-- (recreate the sources and views on this cluster; DDL as above)

-- 3. Recovery test — kill one replica; measure time-to-serve
-- (executed via CLI or automated chaos test)
mz-cli cluster kill-replica fraud_cluster --replica 1

-- Immediate SELECT still works — served by replica 2
SELECT COUNT(*) FROM fraud_scores_by_tier;

-- Recreate the replica; measure hydrate time
mz-cli cluster spawn-replica fraud_cluster
-- Materialize rehydrates from `persist` snapshot + WAL tail
-- Log line: "cluster fraud_cluster replica 3 caught up in 42s"

-- 4. Configure `persist` retention for the fraud pipeline
ALTER SYSTEM SET persist_stats_audit_interval TO '5s';
ALTER SYSTEM SET persist_compaction_minimum_timestamp_lag TO '30s';
-- Lower compaction lag = faster recovery, more S3 writes

Step-by-step explanation.

1. CREATE CLUSTER fraud_cluster SIZE 'medium' REPLICATION FACTOR 2 creates a dedicated compute pool for the fraud pipeline with two replicas. Isolating this workload from analytics workloads on other clusters prevents noisy-neighbour interference. medium is 8 vCPU + 32 GB memory per replica.
2. Every DDL after SET cluster = fraud_cluster binds new objects to this cluster. Sources, materialized views, and indexes all live on the current cluster. You can ALTER MATERIALIZED VIEW ... SET (CLUSTER) to move objects across clusters.
3. Replication factor 2 means the same arrangements are maintained on both replicas concurrently. Kill one replica, and queries continue on the surviving replica — no dropped queries, no reroute delay. This is the availability dimension of the SLA.
4. Recovery of a new replica is bounded by the persist snapshot age plus the WAL tail. On a well-tuned deployment, recovery is under 60 seconds for medium-sized clusters. Longer for large state; instrument the persist_stats_audit_interval to see snapshot progress.
5. persist_compaction_minimum_timestamp_lag controls the trade-off: lower = the persist layer compacts sooner and offers shorter recovery, but writes more to S3; higher = fewer writes but longer recovery. Start with 30 seconds; tune based on the actual replica-loss SLA measurement.

Output.

Configuration	Freshness p95	Recovery p95	Monthly cost
small size, 1 replica	100-200 ms	90-120 s	$
medium size, 2 replicas	30-80 ms	40-60 s	$$$
large size, 3 replicas	15-40 ms	20-40 s	$$$$$

Rule of thumb. Size the cluster for peak input rate + 30% headroom, then set REPLICATION FACTOR based on the recovery SLA — 1 for internal tools, 2 for customer-facing, 3+ for financial-grade. Adjust persist_compaction_minimum_timestamp_lag down for stricter recovery SLAs, up for cost.

Senior interview question on Materialize

A senior interviewer might ask: "Design a Materialize pipeline for a real-time recommendation service that joins a Kafka impressions stream (100k events/sec) to a Postgres CDC products table and outputs the top-100 trending products per category, updated every second. Cover the sources, the intermediate views, the materialised view, the SUBSCRIBE consumer, the cluster sizing, and the failure story."

Solution Using Materialize with Kafka + Postgres CDC sources, an incremental top-N per group, and a SUBSCRIBE dashboard

-- 1. Sources — Kafka impressions + Postgres CDC products
CREATE CONNECTION kafka_conn TO KAFKA (
    BROKER 'b-1.kafka:9092',
    SASL MECHANISMS = 'SCRAM-SHA-512',
    SASL USERNAME = SECRET kafka_user,
    SASL PASSWORD = SECRET kafka_pass
);

CREATE SOURCE impressions_src
    FROM KAFKA CONNECTION kafka_conn (TOPIC 'impressions')
    FORMAT JSON
    WITH (SIZE = 'medium');

CREATE VIEW impressions AS
SELECT (data->>'product_id')::BIGINT       AS product_id,
       (data->>'user_id')::BIGINT          AS user_id,
       (data->>'event_ts')::TIMESTAMPTZ    AS event_ts
FROM   impressions_src;

CREATE CONNECTION pg_conn TO POSTGRES (
    HOST 'db-primary.internal', PORT 5432,
    USER 'mz_reader', PASSWORD SECRET pg_pass,
    DATABASE 'production', SSL MODE 'require'
);

CREATE SOURCE products_src
    FROM POSTGRES CONNECTION pg_conn (PUBLICATION 'mz_pub_products')
    FOR TABLES ('public.products');

-- 2. Intermediate — impressions per (category, product) in last hour
CREATE VIEW impressions_by_product AS
SELECT p.category,
       i.product_id,
       COUNT(*) AS impression_count
FROM   impressions i
JOIN   public.products p ON p.id = i.product_id
WHERE  i.event_ts >= mz_now() - INTERVAL '1 hour'
GROUP  BY p.category, i.product_id;

-- 3. Top-100 per category — the LATERAL trick for top-N per group
CREATE MATERIALIZED VIEW top_100_per_category AS
SELECT category, product_id, impression_count, rn
FROM  (SELECT category, product_id, impression_count,
              ROW_NUMBER() OVER (PARTITION BY category ORDER BY impression_count DESC) AS rn
       FROM   impressions_by_product)
WHERE rn <= 100;

CREATE DEFAULT INDEX ON top_100_per_category;

-- 4. Cluster sizing — dedicated cluster for the recommendation pipeline
CREATE CLUSTER reco_cluster SIZE 'large' REPLICATION FACTOR 2;
ALTER MATERIALIZED VIEW top_100_per_category SET (CLUSTER = reco_cluster);

-- 5. Dashboard SUBSCRIBE (push deltas)
COPY (SUBSCRIBE (
    SELECT category, product_id, impression_count
    FROM   top_100_per_category
    WHERE  category = 'electronics'
    ORDER  BY impression_count DESC
)) TO STDOUT;

Step-by-step trace.

Layer	Config	Result
Kafka source size	medium (8 vCPU)	100k events/sec ingest headroom
CDC source	Postgres logical replication	live category changes propagate
Top-N per group	ROW_NUMBER partitioned by category	100 rows per category maintained incrementally
Cluster	large size, 2 replicas	30-80ms freshness; 40s recovery
Delivery	psql SUBSCRIBE	push deltas to dashboard
Failure story	replica loss → surviving replica serves	zero dropped queries

After deployment, the recommendation service sees top_100_per_category update within 50 ms of new impressions arriving, and product-catalog changes in Postgres propagate through the join within one WAL flush. The dashboard receives push-based diffs — the top-100 list stays sorted with no re-fetch.

Output:

Metric	Value
End-to-end latency (Kafka commit → dashboard)	30-80 ms p95
Materialised view arrangement size	~2 GB (100 categories × 100 products × metadata)
Cluster cost	2 × large replicas
Replica loss recovery	40-60 s to serving
Correctness vs batch pipeline	100% row parity

Why this works — concept by concept:

• Differential dataflow join — the join between impressions and products is maintained incrementally in the differential-dataflow substrate. As a new impression arrives, the engine looks up the product in the products arrangement (O(log n)) and emits one output triple; no full re-scan.
• ROW_NUMBER window over PARTITION BY — Materialize compiles the window function into an operator that maintains the top-N per group as an arrangement. When an impression count changes, only the affected category's top-100 is recomputed.
• Dedicated cluster + replication factor 2 — isolates the recommendation workload from other Materialize workloads; two replicas give zero-downtime replica loss and 40s recovery for a new replica.
• SUBSCRIBE push delivery — the dashboard receives mz_diff +/- rows, not full snapshots. The client applies diffs to its local top-100 without re-fetching. Bandwidth scales with change rate, not with query result size.
• Cost — 2 × large replicas (32 vCPU + 128 GB each), one medium source, S3 persist (~$10/month). Compared to a Flink SQL cluster on 5 TaskManagers with RocksDB tuning, this is comparable capacity at half the operational surface. O(1) SQL per new top-N query; the underlying dataflow engine handles the incrementalisation and the join.

SQL
Topic — sql
SQL problems on materialised views and incremental aggregates

Practice →

Streaming Topic — streaming Streaming problems on IVM and continuous joins

Practice →

---

3. RisingWave — cloud-native + Postgres-wire streaming

Postgres-wire streaming SQL with Hummock S3-native state — the cloud-native pick when horizontal scale trumps strict-serializable

The mental model in one line: risingwave streaming is the pattern where a stateless compute layer (Rust, K8s-native) consumes Kafka/Kinesis/CDC sources and maintains materialised views whose state lives in Hummock, an LSM tree that reads and writes directly to S3-compatible object storage — the engine speaks the Postgres wire protocol so any psql/JDBC client is a first-class consumer, and the compute/state separation means autoscaling is a K8s HorizontalPodAutoscaler away rather than a state-migration project — it is the cloud-native pick when your streaming workload doesn't need strict-serializable and you'd rather pay S3 pennies than local NVMe dollars for state. Every senior DE building on Kubernetes should have RisingWave in their toolkit; the architecture is the modern take on "what if we designed a streaming SQL engine cloud-native from scratch."

The four axes for RisingWave.

• Consistency. Barrier-consistent — Chandy-Lamport-style checkpoints define consistent cuts of the streaming DAG. Reads at a barrier see a fully consistent state; reads between barriers may observe partial state. This is weaker than Materialize's strict-serializable but stronger than "eventually consistent."
• State storage. Hummock — an LSM tree that persists SSTables directly to S3 (or GCS, or Azure Blob, or MinIO for on-prem). Compute nodes hold a hot cache; cold reads go to S3. This is the key architectural bet: state is remote-durable by default; compute is stateless.
• Latency. 100-500 ms end-to-end from Kafka commit to view update. Barrier interval (default 1 second) is the primary knob; lower = fresher, more S3 writes; higher = staler, cheaper.
• Ecosystem. Postgres wire compatibility (like Materialize). Sources include Kafka, Kinesis, Pulsar, Redpanda, MySQL/Postgres CDC (via Debezium-compatible input), Google Pub/Sub, S3. Sinks include Kafka, Iceberg, Delta Lake, Postgres, MySQL, ClickHouse, Redis, Elasticsearch, Snowflake. The connector surface is one of the widest in the streaming SQL space.

The compute/state separation — what RisingWave gets that Materialize doesn't.

• Stateless compute nodes. A RisingWave compute node holds arrangement-like state in memory as a cache, not as the source of truth. Losing a compute node loses only the cache; the durable state is on S3.
• Horizontal scaling. Adding compute nodes redistributes actors across the new nodes; the new nodes pull state from S3 on demand. No re-shuffling of state files, no long rebalance downtime — HPA can spin new nodes in seconds and they start participating in the streaming DAG.
• Storage-compute decoupling. Pay for compute during business hours, spin down at night. State on S3 costs pennies per GB per month regardless of whether compute is running. This is the "cloud-native" pitch made concrete.
• The trade-off. S3 round-trips add latency. Hummock uses an in-memory shared buffer and block cache to hide this for hot data, but cold reads pay the S3 tax. Barrier interval interacts with S3 write cost — lower barrier = more Sstable writes.

The Hummock LSM tree — the state-store deep dive.

• Shared buffer. Actors write to an in-memory shared buffer between barriers. On barrier commit, the buffer flushes to an SSTable in S3.
• SSTable levels. Standard LSM levels (L0, L1, ..., Ln) with compaction between levels. Compaction runs on dedicated compactor nodes (separate from compute).
• Block cache. Hot SSTable blocks are cached on compute nodes; a compute node with warm cache is comparable to a Materialize node with in-memory arrangements.
• Point lookup latency. Warm cache: ~1 ms. Cold cache from S3: ~30-100 ms (S3 GET). Range scans stream from S3 with prefetch.

The barrier system — the consistency mechanism.

• What it is. A synchronous "cut" of the streaming DAG — every actor pauses, flushes its shared buffer, checkpoints state, then resumes. All actors process the same barrier in coordination; reads at that barrier see a consistent cross-actor state.
• Barrier interval. Default 1 second. Configurable via barrier_interval_ms. Lower = fresher, more S3 write amp; higher = staler, cheaper.
• What barriers guarantee. Point-in-time consistent cross-view reads (at the barrier boundary). Between barriers, reads may observe in-flight state.

Common interview probes on RisingWave.

• "Where does RisingWave store state?" — required answer: Hummock, an LSM tree on S3.
• "How is compute autoscaled?" — required answer: K8s HPA; compute nodes are stateless; new nodes pull state from S3 on demand.
• "What consistency does RisingWave give?" — barrier-consistent, weaker than strict-serializable, stronger than eventual.
• "How does RisingWave compare to Materialize?" — cloud-native scale vs strict correctness; S3 state vs in-memory arrangements; barrier vs logical timestamp.
• "What's the failure story?" — compute node loss → K8s spawns a replacement → new node hydrates from Hummock; no data loss because state is on S3.

Worked example — Kafka source + a rolling window materialised view

Detailed explanation. The canonical RisingWave workload: a Kafka source feeds a materialised view that computes a 5-minute tumbling window aggregate per (product, region), and the view is sinked to Iceberg for the warehouse. Build the source, the view, and the sink.

• Source. Kafka sales topic in Avro (schema registry).
• View. 5-minute tumbling window SUM(amount) per (product_id, region).
• Sink. Iceberg table on S3, partitioned by window_start.

Question. Write the source, materialised view, and sink DDL. Show a sample query result.

Input.

Component	Value
Source connector	kafka
Format	Avro
Schema registry	http://schema-registry:8081
View kind	MATERIALIZED VIEW (tumbling window)
Sink connector	iceberg
Barrier interval	1000ms (default)

Code.

-- 1. Kafka source (Avro with schema registry)
CREATE SOURCE sales_src (
    order_id   BIGINT,
    product_id BIGINT,
    region     VARCHAR,
    amount     DECIMAL(18,2),
    event_ts   TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic     = 'sales',
    properties.bootstrap.server = 'b-1.kafka:9092,b-2.kafka:9092',
    scan.startup.mode           = 'earliest'
) FORMAT PLAIN ENCODE AVRO (
    schema.registry = 'http://schema-registry:8081'
);

-- 2. Watermark on event_ts (needed for tumbling windows)
ALTER SOURCE sales_src ADD WATERMARK event_ts AS event_ts - INTERVAL '30 seconds';

-- 3. Tumbling window materialised view
CREATE MATERIALIZED VIEW sales_5m AS
SELECT window_start,
       product_id,
       region,
       SUM(amount) AS total_amount,
       COUNT(*)    AS event_count
FROM   TUMBLE(sales_src, event_ts, INTERVAL '5 minutes')
GROUP  BY window_start, product_id, region;

-- 4. Sink to Iceberg on S3
CREATE SINK sales_5m_sink FROM sales_5m
WITH (
    connector             = 'iceberg',
    warehouse.path        = 's3://analytics-lake/warehouse/',
    catalog.type          = 'rest',
    catalog.uri           = 'http://polaris:8181',
    database.name         = 'analytics',
    table.name            = 'sales_5m',
    primary_key           = 'window_start,product_id,region',
    type                  = 'upsert'
);

Step-by-step explanation.

1. The CREATE SOURCE DDL declares the schema explicitly (columns + types) plus the connector config. Avro schema comes from the schema registry, so ADD/DROP columns propagate automatically. scan.startup.mode = 'earliest' bootstraps from topic head; alternatives are latest and timestamp.
2. ADD WATERMARK event_ts AS event_ts - INTERVAL '30 seconds' declares that late events are tolerated up to 30 seconds — the tumbling window will emit a result 30 seconds after the wall-clock end of the window. This is the standard watermark pattern for handling out-of-order events.
3. The TUMBLE(sales_src, event_ts, INTERVAL '5 minutes') function generates window-start and window-end columns per event. The materialised view groups by (window_start, product_id, region) — one row per (window, product, region).
4. RisingWave compiles this into a streaming DAG: SourceExecutor → WatermarkFilter → HashAggregate → MaterializeExecutor. State (the running SUM per key) lives in Hummock; on barrier commit, the shared buffer flushes to S3.
5. The CREATE SINK ... type = 'upsert' writes each updated aggregate row to Iceberg as an upsert (keyed on the primary key). Downstream analytical queries against the Iceberg table see one row per (window, product, region) with the correct SUM.

Output.

window_start	product_id	region	total_amount	event_count
2026-07-27 09:00:00	42	us-east	15,432.10	87
2026-07-27 09:00:00	42	eu-west	8,910.50	42
2026-07-27 09:00:00	99	us-east	3,210.00	12
2026-07-27 09:05:00	42	us-east	18,222.30	104

Rule of thumb. For RisingWave tumbling windows, always declare a watermark on the event-time column with a lag that matches your out-of-order tolerance (typically 30 s to 5 min). Without a watermark, RisingWave cannot emit final window results — it will keep the window open indefinitely.

Worked example — K8s deployment + autoscaling

Detailed explanation. RisingWave's compute layer is a K8s Deployment or StatefulSet with an HPA. Because state lives on S3, adding compute nodes is trivial — the meta node redistributes actors, and new nodes pull state from Hummock. Walk through a Helm-based deployment and an HPA that scales on CPU.

• Chart. Official risingwavelabs/risingwave Helm chart.
• Nodes. 1 meta, 3 compute, 1 frontend, 1 compactor (initial).
• HPA. Scale compute on CPU utilisation > 70%.
• State. S3-compatible (minio for dev; s3:// for prod).

Question. Provide the Helm values file and the HPA manifest.

Input.

Component	Value
Chart	risingwavelabs/risingwave
K8s namespace	streaming
Meta store	Postgres (external)
Object store	S3 (s3://acme-streaming/hummock/)
HPA target	70% CPU on compute pods
Barrier interval	1000ms

Code.

# values.yaml — Helm chart
image:
  repository: risingwavelabs/risingwave
  tag: v2.0.0

metaStore:
  postgresql:
    host: risingwave-meta-pg.streaming.svc.cluster.local
    port: 5432
    database: metadata
    authentication:
      existingSecretName: rw-meta-pg-secret

stateStore:
  dataDirectory: hummock001
  s3:
    bucket: acme-streaming
    endpoint: https://s3.us-east-1.amazonaws.com
    region: us-east-1
    authentication:
      existingSecretName: rw-s3-secret

compute:
  replicaCount: 3
  resources:
    requests: { cpu: 2, memory: 8Gi }
    limits:   { cpu: 4, memory: 16Gi }

frontend:
  replicaCount: 2

compactor:
  replicaCount: 1
  resources:
    requests: { cpu: 2, memory: 4Gi }

meta:
  replicaCount: 1
  extraEnvVars:
    - name: RW_BARRIER_INTERVAL_MS
      value: "1000"

# hpa.yaml — autoscale compute pods on CPU
apiVersion: autoscaling/v2
kind: HorizontalPodAutoscaler
metadata:
  name: rw-compute-hpa
  namespace: streaming
spec:
  scaleTargetRef:
    apiVersion: apps/v1
    kind: StatefulSet
    name: risingwave-compute
  minReplicas: 3
  maxReplicas: 20
  metrics:
    - type: Resource
      resource:
        name: cpu
        target:
          type: Utilization
          averageUtilization: 70
  behavior:
    scaleUp:
      stabilizationWindowSeconds: 60
      policies:
        - type: Pods
          value: 2
          periodSeconds: 60
    scaleDown:
      stabilizationWindowSeconds: 300
      policies:
        - type: Pods
          value: 1
          periodSeconds: 120

Step-by-step explanation.

1. The Helm chart separates the four RisingWave services — meta (control plane), compute (streaming actors), frontend (Postgres wire), compactor (LSM compaction). Each scales independently; compute is the one you typically autoscale.
2. Meta store is external Postgres — RisingWave's control-plane state (source definitions, view catalog, cluster membership) lives there. Postgres itself is HA'd via standard means (managed RDS, Patroni, etc.).
3. State store is S3. dataDirectory is the key prefix inside the bucket. All Hummock SSTables land under s3://acme-streaming/hummock001/. RisingWave computes and compactors read/write this prefix; the bucket needs s3:GetObject / PutObject / DeleteObject / ListObjectsV2 grants.
4. The HPA targets risingwave-compute StatefulSet, scaling from 3 to 20 pods on CPU. Behavior policies rate-limit scaling — scale up 2 pods per 60 s, scale down 1 pod per 120 s. This prevents flapping under bursty load.
5. When the HPA scales up, the RisingWave meta node reshards actors across the new pods. New pods hydrate their portion of state from S3 (Hummock reads) — hydrate time is ~30-90 s for typical warm-cache workloads. Scale-down drains actors from the departing pod first, then the pod terminates.

Output.

Load level	Compute pods	State on S3	Cost regime
Idle (nights)	3 (HPA min)	~100 GB	baseline
Business hours	6-10 (autoscale)	~100 GB	2-3× baseline
Peak (marketing burst)	15-20 (HPA max)	~100 GB	5-7× baseline

Rule of thumb. In a RisingWave K8s deployment, set the HPA minReplicas ≥ 3 for redundancy, maxReplicas 5-10× minReplicas for burst headroom, and rate-limit scale-up (2 pods per 60 s) to prevent thundering-herd Hummock reads that can throttle S3. State cost is flat; compute cost scales with utilisation.

Worked example — replay + backfill from Kafka

Detailed explanation. RisingWave's replay story: because sources track upstream offsets and materialised views are re-computable from source, a bad view definition or corrupted state is fixed by dropping and re-creating the view with scan.startup.mode = 'earliest'. For source-level bootstrap, RisingWave supports START_OFFSET per Kafka partition.

• Bad view fix. DROP MATERIALIZED VIEW; CREATE MATERIALIZED VIEW (with corrected SQL).
• Full replay. Source with scan.startup.mode = 'earliest' reads the topic from head.
• Partial replay. START_OFFSET per partition — resume from a specific offset per partition.
• State reset. Drop Hummock objects with a specific dataDirectory prefix; MV will re-hydrate on next start.

Question. Show the replay workflow for a scenario where a bug in the view SQL produced incorrect aggregates for the last 6 hours; you need to restate the view without re-consuming from topic head.

Input.

Step	Action
1	pause the sink (or downstream consumer)
2	DROP the buggy materialised view
3	CREATE the view with corrected SQL and scan.startup.mode = 'timestamp'
4	wait for MV to catch up
5	resume the sink

Code.

-- 1. Pause the sink (either DROP or set a filter)
DROP SINK sales_5m_sink;

-- 2. Drop the buggy materialised view
DROP MATERIALIZED VIEW sales_5m;

-- 3. Re-create the source with timestamp-based startup
--    (Alternative: DROP + CREATE SOURCE; here we re-use the source
--     and just re-create the MV with a fresh scan)
DROP SOURCE sales_src CASCADE;

CREATE SOURCE sales_src (
    order_id   BIGINT,
    product_id BIGINT,
    region     VARCHAR,
    amount     DECIMAL(18,2),
    event_ts   TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic     = 'sales',
    properties.bootstrap.server = 'b-1.kafka:9092',
    scan.startup.mode           = 'timestamp',
    scan.startup.timestamp.millis = '1721016000000'   -- 6h ago
) FORMAT PLAIN ENCODE AVRO (
    schema.registry = 'http://schema-registry:8081'
);

ALTER SOURCE sales_src ADD WATERMARK event_ts AS event_ts - INTERVAL '30 seconds';

-- 4. Re-create the MV with corrected SQL
CREATE MATERIALIZED VIEW sales_5m AS
SELECT window_start,
       product_id,
       region,
       SUM(amount) AS total_amount,          -- was accidentally COUNT(amount) before
       COUNT(*)    AS event_count
FROM   TUMBLE(sales_src, event_ts, INTERVAL '5 minutes')
GROUP  BY window_start, product_id, region;

-- 5. Wait for backfill; monitor via SHOW JOBS
SHOW JOBS;

-- 6. Re-create the sink pointed at the (now-correct) MV
CREATE SINK sales_5m_sink FROM sales_5m
WITH (
    connector             = 'iceberg',
    warehouse.path        = 's3://analytics-lake/warehouse/',
    catalog.type          = 'rest',
    catalog.uri           = 'http://polaris:8181',
    database.name         = 'analytics',
    table.name            = 'sales_5m',
    primary_key           = 'window_start,product_id,region',
    type                  = 'upsert'
);

Step-by-step explanation.

1. Dropping the sink first prevents partial / mixed data from landing in Iceberg while the MV is being rebuilt. This is a data-hygiene step, not a correctness step — the upsert sink would eventually converge, but pausing avoids intermediate weirdness.
2. Dropping the buggy MV releases its state (SSTables are compacted away eventually) and its actors. Downstream views that reference this MV must also be dropped (or CASCADE).
3. Recreating the source with scan.startup.mode = 'timestamp' + scan.startup.timestamp.millis = <6h ago> positions the Kafka consumer offsets to the timestamp closest to 6 hours ago. This is Kafka's native offsetsForTimes API under the hood.
4. The re-created MV starts consuming from 6 hours ago, aggregating into fresh Hummock state. Backfill time is roughly input_rate × 6h ÷ compute_throughput. Monitor via SHOW JOBS — the MV shows a progress percentage until it catches up to real-time.
5. Once the MV is caught up, re-create the sink. The upsert sink will re-write the last 6 hours of window rows to Iceberg — downstream Iceberg queries see the corrected values within one commit interval.

Output.

Metric	Value
Source reset target	6h ago
Backfill duration (@ 20k events/s, 8 compute pods)	~15 min
Hummock state churn	full re-population of sales_5m state
Iceberg rows corrected	~14,400 window rows (5-min windows over 6h × products × regions)
Sink recovery	1 barrier interval (1 s) once MV catches up

Rule of thumb. For RisingWave replay, prefer scan.startup.mode = 'timestamp' over earliest — timestamp-based replay bounds backfill time and cost. Always drop sinks first, then MVs, then sources; re-create in reverse order.

Senior interview question on RisingWave

A senior interviewer might ask: "You're building a real-time ad-tech attribution pipeline at 200k events/sec. It joins impressions (Kafka) to clicks (Kafka) to conversions (Kafka), computes revenue attribution windows per campaign, and sinks to both Iceberg (for analytics) and Redis (for the ad-serving cache). Design the RisingWave deployment — the sources, joins, materialised views, sinks, K8s topology, and autoscaling — and cover the failure story."

Solution Using RisingWave on K8s with Kafka sources, temporal joins, Iceberg + Redis sinks, and HPA-driven autoscaling

-- 1. Three Kafka sources with watermarks
CREATE SOURCE impressions_src (
    imp_id      BIGINT,
    campaign_id BIGINT,
    user_id     BIGINT,
    event_ts    TIMESTAMPTZ
) WITH (
    connector = 'kafka', topic = 'impressions',
    properties.bootstrap.server = 'b-1.kafka:9092',
    scan.startup.mode = 'earliest'
) FORMAT PLAIN ENCODE AVRO (schema.registry = 'http://sr:8081');
ALTER SOURCE impressions_src ADD WATERMARK event_ts AS event_ts - INTERVAL '30 seconds';

CREATE SOURCE clicks_src (...) ... ;         -- symmetric definition
CREATE SOURCE conversions_src (...) ... ;    -- symmetric definition

-- 2. Interval join — impression → click within 30 min → conversion within 24 h
CREATE MATERIALIZED VIEW attributed_conversions AS
SELECT i.campaign_id,
       i.user_id,
       i.imp_id,
       c.click_id,
       v.conversion_id,
       v.revenue,
       i.event_ts AS imp_ts,
       v.event_ts AS conv_ts
FROM   impressions_src i
JOIN   clicks_src      c ON c.user_id = i.user_id
                        AND c.event_ts BETWEEN i.event_ts AND i.event_ts + INTERVAL '30 minutes'
JOIN   conversions_src v ON v.user_id = i.user_id
                        AND v.event_ts BETWEEN c.event_ts AND c.event_ts + INTERVAL '24 hours';

-- 3. Windowed revenue per campaign (1-hour tumbling)
CREATE MATERIALIZED VIEW revenue_per_campaign_1h AS
SELECT window_start,
       campaign_id,
       COUNT(DISTINCT user_id) AS attributed_users,
       SUM(revenue)            AS total_revenue
FROM   TUMBLE(attributed_conversions, conv_ts, INTERVAL '1 hour')
GROUP  BY window_start, campaign_id;

-- 4. Iceberg sink (analytics)
CREATE SINK revenue_iceberg FROM revenue_per_campaign_1h
WITH (
    connector = 'iceberg',
    warehouse.path = 's3://analytics-lake/warehouse/',
    catalog.type = 'rest', catalog.uri = 'http://polaris:8181',
    database.name = 'analytics', table.name = 'revenue_per_campaign_1h',
    primary_key = 'window_start,campaign_id',
    type = 'upsert'
);

-- 5. Redis sink (ad-serving hot cache) — current-hour revenue only
CREATE SINK campaign_revenue_redis FROM (
    SELECT campaign_id, total_revenue
    FROM   revenue_per_campaign_1h
    WHERE  window_start >= now() - INTERVAL '1 hour'
) WITH (
    connector = 'redis',
    redis.url = 'redis://redis-ads.ads.svc.cluster.local:6379/0',
    primary_key = 'campaign_id',
    type = 'upsert'
);

# 6. K8s topology (Helm values excerpt)
compute:
  replicaCount: 8           # HPA scales this from 8 to 40
  resources:
    requests: { cpu: 4, memory: 16Gi }

compactor:
  replicaCount: 4

frontend:
  replicaCount: 3

meta:
  replicaCount: 3           # HA for control plane
  extraEnvVars:
    - name: RW_BARRIER_INTERVAL_MS
      value: "500"          # tighter barrier for ad-tech freshness

Step-by-step trace.

Layer	Config	Result
Sources	3 Kafka topics with 30s watermark	out-of-order events tolerated
Interval join	impression → click 30m → conversion 24h	native RisingWave temporal join
Aggregation	1h tumbling window per campaign	attributed revenue rollup
Iceberg sink	upsert on (window_start, campaign_id)	analytics warehouse
Redis sink	upsert on campaign_id (current hour)	ad-server hot lookup
Compute HPA	8 → 40 pods on CPU	200k events/s ceiling with headroom
Barrier	500 ms	fresher output; 2x S3 write rate

After deployment, the pipeline processes 200k events/sec through the three-way interval join, aggregates per hour per campaign, and delivers to both Iceberg (analytics) and Redis (ad serving). The K8s HPA scales compute from 8 to 40 pods as traffic bursts. State (interval-join buffers, aggregate state) lives entirely on S3 via Hummock; pod loss triggers automatic hydration from S3 in ~60 seconds.

Output:

Metric	Value
Ingest rate sustained	200k events/s
End-to-end latency (Kafka commit → Redis)	500-1500 ms (barrier + join buffer)
Compute cost (steady)	8 × 4-vCPU pods
Compute cost (peak)	40 × 4-vCPU pods
State cost	~$200/month S3 for ~2 TB Hummock
Recovery on pod loss	~60 s (Hummock rehydration)

Why this works — concept by concept:

• Interval join — RisingWave's native event_ts BETWEEN ... AND ... join uses time-windowed state — only tuples within the join interval are retained. State size is bounded by (join rate × interval width), not by total input volume.
• Barrier at 500 ms — the tighter barrier interval (vs 1 s default) doubles freshness for ad-tech's cache-hit requirements at 2× the S3 write cost. Explicit trade-off; document the choice for on-call.
• Hummock on S3 — the 2 TB of join-buffer + aggregate state lives on S3 at ~$200/month. If we ran the same workload on Materialize we'd need ~2 TB of memory across replicas — order-of-magnitude more expensive per GB.
• Two sinks from one view — Iceberg for analytics (correct history), Redis for ad-serving (current-hour hot cache). One materialised view feeds both; RisingWave's sink DAG handles fan-out.
• Cost — 8-40 compute pods, 4 compactor pods, 3 frontend pods, 3 meta pods, plus S3 for state. Compared to a Flink cluster on 20 TaskManagers with RocksDB local state (backup complexity, capacity planning), this is one-third the operational headache at comparable throughput. O(1) SQL per new attribution rule; the underlying streaming DAG handles the temporal joins and windowing.

Streaming
Topic — streaming
Streaming problems on windowed joins and attribution

Practice →

Aggregation Topic — aggregation Aggregation problems on tumbling and sliding windows

Practice →

---

4. Timeplus — unified streaming + historical on the Proton engine

timeplus proton — one SQL query joining a stream to a table, on a ClickHouse-adjacent columnar engine

The mental model in one line: timeplus proton is the pattern where a single ClickHouse-derived engine (Proton is the OSS core; Timeplus Enterprise / Cloud is the managed variant) treats streams and tables as first-class types in one SQL dialect — you can SELECT ... FROM stream(events) s JOIN table(dim_users) d ON ... in one query, run the same query as SELECT (batch snapshot) or SELECT ... EMIT CHANGES (streaming), and reuse ClickHouse's MergeTree storage engine for historical scans — it is the unified pick when your workload sits on the boundary between analytical (batch) and operational (streaming) and you'd rather write one query than maintain two pipelines (Flink for streaming + ClickHouse for historical). Every senior DE who has hand-written the same aggregation twice — once in Flink for real-time, once in ClickHouse for the backfill — owes Timeplus a serious look.

The four axes for Timeplus / Proton.

• Consistency. Streaming-eventual — a streaming materialised view emits changes as they arrive; historical joins are point-in-time snapshots at query execution. Not strict-serializable (Materialize territory) and not strictly barrier-consistent (RisingWave territory); Proton's model is closest to "ClickHouse batch semantics for tables, best-effort stream semantics for streams."
• State storage. ClickHouse MergeTree — a columnar, sorted, disk-based storage engine with S3 tiering support. Streaming state (aggregation buffers, join buffers) is kept in memory + spilled to MergeTree parts. Historical tables use standard MergeTree with the columnar goodness ClickHouse users already know.
• Latency. Sub-second for streaming views; ClickHouse-native (10-100 ms per query) for historical scans. EMIT CHANGES cadence controls freshness.
• Ecosystem. ClickHouse SQL dialect (with streaming extensions). Sources: Kafka, Kinesis, Pulsar, Redpanda, Iceberg, S3, HTTP webhooks, MySQL/Postgres CDC. Sinks: Kafka, Iceberg, S3, HTTP, JDBC. Because it's ClickHouse-derived, the query engine, data types, table engines, and dictionary features are all inherited.

The unified stream+table model — what Timeplus gets that others don't.

• Two first-class types. STREAM (unbounded, append-only, with _tp_time timestamp) and TABLE (bounded, MergeTree, standard ClickHouse). A STREAM supports SELECT ... FROM stream_name (streaming) and SELECT ... FROM table(stream_name) (batch snapshot).
• Cross-type joins. SELECT ... FROM stream_events s JOIN table_dim d ON ... — the join is a stream-table lookup; each streaming row triggers a dictionary lookup into the table.
• EMIT CHANGES clause. A materialised view with EMIT CHANGES produces a change-stream output; without it, it's a batch materialisation.
• Historical replay. Streams have a _tp_time field; WHERE _tp_time BETWEEN ... AND ... runs a historical scan against the stream's underlying MergeTree parts.

The Proton vs Timeplus Cloud split.

• Proton (OSS). Apache-2.0 licensed; single-node or small-cluster deployment; the streaming SQL core. GitHub: timeplus-io/proton. Everything covered in this section is available on Proton.
• Timeplus Enterprise / Cloud. Managed, multi-node, HA, plus enterprise features (RBAC, audit, connector marketplace, dashboards). Same SQL dialect as Proton.
• When to pick which. Proton for a single team's streaming analytics on a couple of nodes; Cloud for org-wide deployment.

Common interview probes on Timeplus.

• "What's the unified stream+batch pitch?" — required answer: one query joins a stream to a table; same SQL for real-time and historical.
• "How is Timeplus related to ClickHouse?" — Proton is built on the ClickHouse storage engine; MergeTree parts, columnar, S3 tiering — all inherited.
• "What consistency does EMIT CHANGES give?" — streaming-eventual; changes are emitted as computed; not strict-serializable.
• "When does Timeplus win over Flink + ClickHouse?" — when the workload lives on the stream/batch boundary and you want one engine instead of two.
• "When does Materialize or RisingWave beat Timeplus?" — Materialize for strict correctness; RisingWave for K8s-native cloud scale.

Worked example — stream + table join for enriched clickstream

Detailed explanation. A clickstream feeds Timeplus via Kafka; user dimension data lives in a Timeplus table (loaded nightly from Postgres). The query joins the stream to the table and computes a per-tier click count. Show the CREATE STREAM, CREATE TABLE, and the joined view.

• Stream. clicks_stream from Kafka; _tp_time auto-populated.
• Table. dim_users MergeTree table; columns id, tier, country.
• Query. Stream + table join; group by tier + minute; EMIT CHANGES.

Question. Write the stream, table, and materialised view. Show sample streaming output.

Input.

Object	Purpose
clicks_stream	Kafka-fed stream of click events
dim_users	MergeTree table of user tiers
clicks_by_tier_1m	materialised view: tier × 1-min click count

Code.

-- 1. Streaming source from Kafka
CREATE STREAM clicks_stream (
    click_id   uint64,
    user_id    uint64,
    url        string,
    event_ts   datetime64(3)
) SETTINGS type = 'kafka',
           brokers = 'b-1.kafka:9092,b-2.kafka:9092',
           topic = 'clicks',
           data_format = 'JSONEachRow';

-- 2. Historical user dimension table (standard MergeTree)
CREATE TABLE dim_users (
    id       uint64,
    tier     string,
    country  string,
    updated_at datetime
) ENGINE = MergeTree
ORDER BY id;

-- Load once from Postgres (or on a nightly schedule)
INSERT INTO dim_users
SELECT * FROM postgres('db.internal', 'production', 'users', 'reader', 'pw');

-- 3. Stream + table join with 1-minute tumbling aggregation, EMIT CHANGES
CREATE MATERIALIZED VIEW clicks_by_tier_1m AS
SELECT window_start,
       d.tier,
       count()               AS click_count,
       uniq(c.user_id)       AS distinct_users
FROM   tumble(clicks_stream, event_ts, INTERVAL 1 MINUTE) c
JOIN   dim_users d ON d.id = c.user_id
GROUP  BY window_start, d.tier
EMIT CHANGES;

-- 4. Sample streaming output — a live subscribe
SELECT * FROM clicks_by_tier_1m EMIT CHANGES;
-- window_start          | tier     | click_count | distinct_users
-- 2026-07-27 09:00:00   | free     | 1,240       | 620
-- 2026-07-27 09:00:00   | pro      |   380       | 210
-- 2026-07-27 09:00:00   | free     | 1,245       | 622  (updated)
-- 2026-07-27 09:01:00   | free     |    18       |  15  (new window)

Step-by-step explanation.

1. CREATE STREAM declares a streaming input backed by Kafka. Proton auto-populates a _tp_time column with the event arrival time; if the source has an event-time column (event_ts), Proton uses that instead when specified. Under the hood, the stream persists to MergeTree parts (so historical queries work on the same table).
2. CREATE TABLE dim_users ... ENGINE = MergeTree ORDER BY id is a standard ClickHouse table — columnar, sorted, on disk. INSERT INTO dim_users SELECT * FROM postgres(...) uses ClickHouse's Postgres table function to snapshot the source. Refresh via a scheduled Airflow job or a CDC connector.
3. The materialised view joins the streaming clicks_stream (via tumble(...)) to the batch dim_users — this is a stream-table lookup, Proton evaluates the dictionary lookup per streaming row. The window function produces window_start for the 1-minute tumble.
4. EMIT CHANGES is the streaming output clause — the materialised view emits row-level change events as they're computed. Without EMIT CHANGES, the materialised view is a one-shot batch materialisation (snapshot at CREATE time).
5. Downstream SELECT * FROM clicks_by_tier_1m EMIT CHANGES opens a subscription-like channel: rows arrive as changes are computed. Consumers can filter, aggregate further, or sink to Kafka via CREATE EXTERNAL STREAM outputs.

Output.

Timestamp	Event
09:00:03	+1: (09:00:00, free, 1240, 620)
09:00:04	+1: (09:00:00, pro, 380, 210)
09:00:15	update: (09:00:00, free, 1245, 622)
09:01:00	+1: (09:01:00, free, 18, 15) (new window opens)
09:01:00	window close: (09:00:00, ...) finalised

Rule of thumb. For Timeplus stream+table joins, always JOIN with the stream on the LEFT and the table on the RIGHT — Proton optimises the streaming-row-triggers-dictionary-lookup shape. Reverse the join order and you'll accidentally trigger a table scan per streaming row.

Worked example — historical replay of a stream

Detailed explanation. Because Proton persists streams to MergeTree parts, historical queries against a stream are just standard ClickHouse SELECT with a WHERE _tp_time BETWEEN ... clause. This is the "unified stream+batch" pitch made concrete: the same table serves live queries (via SELECT ... EMIT CHANGES) and backfill queries (via SELECT ... WHERE _tp_time BETWEEN ...).

• Live. SELECT ... FROM clicks_stream EMIT CHANGES — subscription.
• Historical. SELECT ... FROM table(clicks_stream) WHERE _tp_time BETWEEN ... — batch scan.
• Retention. MergeTree TTL controls how far back the stream data is retained.

Question. Show the query patterns for a live consumer and a historical backfill against the same stream, plus the TTL configuration.

Input.

Query type	Syntax pattern	Latency
Live	SELECT ... FROM stream_name EMIT CHANGES	streaming
Historical (bounded)	SELECT ... FROM table(stream_name) WHERE _tp_time BETWEEN ...	ClickHouse-native
Retention	MergeTree TTL clause	policy-driven

Code.

-- 1. Configure retention on the stream (7 days)
ALTER STREAM clicks_stream
    MODIFY TTL toDate(_tp_time) + INTERVAL 7 DAY;

-- 2. Live subscription
SELECT tier, count() AS clicks
FROM  (SELECT c.user_id, d.tier
       FROM   clicks_stream c JOIN dim_users d ON d.id = c.user_id) c
GROUP BY tier
EMIT CHANGES;

-- 3. Historical backfill — same query, batch mode
SELECT tier, count() AS clicks
FROM  (SELECT c.user_id, d.tier
       FROM   table(clicks_stream) c
              JOIN dim_users d ON d.id = c.user_id
       WHERE  c._tp_time BETWEEN '2026-07-20' AND '2026-07-27') c
GROUP BY tier;

-- 4. Recover a specific 6-hour window (backfill for a bug fix)
SELECT window_start, tier, count() AS clicks
FROM  (SELECT c.user_id, d.tier,
              tumbleStart(c._tp_time, INTERVAL 1 MINUTE) AS window_start
       FROM   table(clicks_stream) c
              JOIN dim_users d ON d.id = c.user_id
       WHERE  c._tp_time BETWEEN '2026-07-27 03:00:00'
                              AND '2026-07-27 09:00:00') c
GROUP BY window_start, tier
ORDER BY window_start, tier;

Step-by-step explanation.

1. MODIFY TTL toDate(_tp_time) + INTERVAL 7 DAY sets the stream's retention. Proton's background merge job drops MergeTree parts whose max _tp_time is older than 7 days. Longer TTL = more historical scan range, more disk usage.
2. The live query uses the stream identifier bare (FROM clicks_stream), triggering the streaming execution path. EMIT CHANGES produces a subscription output. This is the query the dashboard runs.
3. The historical query wraps the stream in table(clicks_stream) — this is the Proton hint that says "treat this stream as a bounded table, run in batch mode." Combined with WHERE _tp_time BETWEEN ..., it becomes a standard ClickHouse scan against MergeTree parts.
4. The 6-hour backfill query is functionally identical to the live query, just parameterised on the time range and executed in batch mode. The same aggregation logic — no separate "backfill code path" — because Proton's engine handles both semantics natively.
5. The unified model means bug fixes are simple: fix the SQL, replay the last 6 hours as a batch job to correct downstream (e.g. Iceberg sink), and the same corrected SQL powers the live view going forward. In a Flink + ClickHouse stack, this would be two separate re-implementations of the same logic.

Output.

Query	Execution mode	Latency
FROM clicks_stream EMIT CHANGES	streaming	sub-second per change
FROM table(clicks_stream) WHERE _tp_time BETWEEN ...	batch (MergeTree scan)	ClickHouse-native (10-100 ms)
Retention TTL	background merge	continuous
6h backfill	batch	seconds (7-day retention × 6 hours slice)

Rule of thumb. For any Timeplus workload, use EMIT CHANGES for live subscriptions and table(stream_name) for historical scans. The unified engine means you write the SQL once and get both semantics — never duplicate business logic across two engines.

Worked example — Iceberg sink + downstream warehouse query

Detailed explanation. Timeplus writes streaming results to Iceberg via the built-in Iceberg sink. Downstream analytical engines (Trino, Spark, Snowflake) query the Iceberg table directly. This closes the unified loop: streaming input → Timeplus computation → Iceberg output → multi-engine analytics.

• Sink. CREATE EXTERNAL STREAM with type = 'iceberg'.
• Table format. Iceberg on S3, Polaris/Glue catalog.
• Query. Trino / Snowflake / Spark reads the Iceberg table as any other Iceberg table.

Question. Write the Iceberg sink DDL and a Trino query that consumes the Iceberg output.

Input.

Component	Value
Sink connector	iceberg
Iceberg catalog	Polaris (Iceberg REST)
Warehouse path	s3://analytics-lake/warehouse/
Table	analytics.clicks_by_tier_1m
Downstream	Trino ad-hoc queries

Code.

-- 1. External stream sink → Iceberg
CREATE EXTERNAL STREAM clicks_by_tier_1m_sink (
    window_start   datetime,
    tier           string,
    click_count    uint64,
    distinct_users uint64
) SETTINGS
    type              = 'iceberg',
    catalog_type      = 'rest',
    catalog_uri       = 'http://polaris:8181',
    warehouse         = 's3://analytics-lake/warehouse/',
    database          = 'analytics',
    table             = 'clicks_by_tier_1m',
    primary_key       = 'window_start,tier';

-- 2. Materialised view writes into the sink
CREATE MATERIALIZED VIEW clicks_by_tier_1m_writer INTO clicks_by_tier_1m_sink AS
SELECT window_start, tier, click_count, distinct_users
FROM   clicks_by_tier_1m;

-- 3. Downstream Trino query (unchanged Iceberg table access)
SELECT tier,
       date_trunc('hour', window_start) AS hour,
       SUM(click_count) AS hourly_clicks
FROM   iceberg.analytics.clicks_by_tier_1m
WHERE  window_start >= current_date - INTERVAL '7' DAY
GROUP  BY tier, hour
ORDER  BY hour DESC, tier;

Step-by-step explanation.

1. CREATE EXTERNAL STREAM ... SETTINGS type = 'iceberg' declares an outbound sink to an Iceberg table. Proton handles the Iceberg REST catalog calls, the S3 writes (Parquet), the Iceberg snapshot commits — the DE writes zero connector code.
2. The primary_key = 'window_start,tier' setting makes the sink use Iceberg's upsert semantics — updates to an already-written window row overwrite (via the Iceberg merge protocol) rather than append duplicates.
3. CREATE MATERIALIZED VIEW ... INTO clicks_by_tier_1m_sink AS SELECT ... is the writer: it consumes from the upstream materialised view and writes to the sink. This pattern (view + writer) decouples the computation from the sink protocol.
4. Downstream, Trino reads iceberg.analytics.clicks_by_tier_1m as any other Iceberg table — no knowledge of Timeplus required. The Iceberg catalog (Polaris) is the vendor-neutral shared layer. Snowflake, Spark, Athena all work the same way.
5. The full pipeline: Kafka → Timeplus stream → Timeplus join → Timeplus materialised view → Iceberg sink → Trino / Spark / Snowflake analytics. Streaming SQL + open lakehouse — one query language across both halves.

Output.

Layer	Output
Timeplus MV	streaming rows in clicks_by_tier_1m
External stream sink	Parquet files in s3://analytics-lake/warehouse/analytics/clicks_by_tier_1m/
Iceberg catalog	committed snapshots on Polaris
Trino / Spark / Snowflake	standard Iceberg reads

Rule of thumb. For any Timeplus deployment that feeds a warehouse, use the Iceberg sink + Polaris catalog — this keeps the downstream engine choice open and gives every analytical consumer the same table view. Avoid direct ClickHouse-to-ClickHouse sinks if any downstream might be Trino or Snowflake.

Senior interview question on Timeplus / Proton

A senior interviewer might ask: "Design a Timeplus pipeline that computes a real-time 15-minute rolling revenue per merchant plus a historical monthly aggregate for finance, both from the same Kafka orders stream, with a dimension join to a Postgres-fed merchants table. Cover the stream + table setup, the materialised view, the EMIT CHANGES vs batch query, the Iceberg sink, and how the unified engine avoids duplicating logic between a streaming and a batch pipeline."

Solution Using Proton with a stream + dimension join, EMIT CHANGES for live view, batch query for historical, and Iceberg sink

-- 1. Kafka source stream
CREATE STREAM orders_stream (
    order_id     uint64,
    merchant_id  uint64,
    amount_cents uint64,
    event_ts     datetime64(3)
) SETTINGS type = 'kafka',
           brokers = 'b-1.kafka:9092',
           topic = 'orders',
           data_format = 'JSONEachRow';

ALTER STREAM orders_stream MODIFY TTL toDate(_tp_time) + INTERVAL 90 DAY;

-- 2. Dimension table (loaded via Postgres table function nightly)
CREATE TABLE dim_merchants (
    id           uint64,
    name         string,
    category     string,
    country      string,
    activated_at datetime
) ENGINE = MergeTree ORDER BY id;

-- 3. The one query — 15-minute rolling revenue per merchant per category
CREATE MATERIALIZED VIEW revenue_15m AS
SELECT window_start,
       d.category,
       d.country,
       o.merchant_id,
       sum(o.amount_cents) / 100.0 AS revenue,
       count()                      AS order_count
FROM   tumble(orders_stream, event_ts, INTERVAL 15 MINUTE) o
JOIN   dim_merchants d ON d.id = o.merchant_id
GROUP  BY window_start, d.category, d.country, o.merchant_id
EMIT CHANGES;

-- 4. Live consumer (dashboard)
SELECT window_start, category, sum(revenue) AS category_revenue
FROM   revenue_15m
WHERE  window_start >= now() - INTERVAL 1 HOUR
GROUP  BY window_start, category
EMIT CHANGES;

-- 5. Historical query (finance monthly aggregate) — same table, batch mode
SELECT toStartOfMonth(window_start) AS month,
       category,
       sum(revenue) AS monthly_revenue
FROM   table(revenue_15m)
WHERE  window_start BETWEEN '2026-01-01' AND '2026-07-01'
GROUP  BY month, category
ORDER  BY month, category;

-- 6. Iceberg sink for the warehouse layer
CREATE EXTERNAL STREAM revenue_15m_iceberg (
    window_start datetime,
    category     string,
    country      string,
    merchant_id  uint64,
    revenue      float64,
    order_count  uint64
) SETTINGS
    type              = 'iceberg',
    catalog_type      = 'rest',
    catalog_uri       = 'http://polaris:8181',
    warehouse         = 's3://analytics-lake/warehouse/',
    database          = 'finance',
    table             = 'revenue_15m',
    primary_key       = 'window_start,merchant_id';

CREATE MATERIALIZED VIEW revenue_15m_iceberg_writer INTO revenue_15m_iceberg AS
SELECT window_start, category, country, merchant_id, revenue, order_count
FROM   revenue_15m;

Step-by-step trace.

Layer	Component	Purpose
Stream	orders_stream on Kafka, 90-day TTL	ingest + retain 3 months for backfill
Table	dim_merchants MergeTree	dimensional join target
MV	revenue_15m with EMIT CHANGES	live rolling aggregate
Live consumer	SELECT ... EMIT CHANGES	dashboard subscription
Historical query	SELECT ... FROM table(revenue_15m)	monthly finance report
Sink	Iceberg on Polaris	vendor-neutral warehouse layer

After deployment, the merchant dashboards see 15-minute revenue updates within one Proton tick of the Kafka commit, finance runs the monthly report as a batch query against the same materialised view (no duplicate SQL), and the Iceberg sink lands the same data in the warehouse for Trino / Snowflake ad-hoc analytics. The finance team writes zero streaming code and the streaming team writes zero batch code.

Output:

Metric	Value
Streaming freshness	100 ms - 1 s
Historical query latency	200-800 ms (MergeTree scan)
Retention	90 days on stream, unlimited in Iceberg
Unified logic count	1 materialised view serves both live + historical
Iceberg commit interval	30 s (Proton default)
Downstream engines supported	Trino, Snowflake, Spark, Athena via Polaris

Why this works — concept by concept:

• Unified stream + table types — orders_stream is a STREAM (unbounded), dim_merchants is a TABLE (bounded); joining them in one MATERIALIZED VIEW is native to Proton. Neither Materialize nor RisingWave has this exact syntactic unification.
• EMIT CHANGES vs batch mode — the same materialised view supports both SELECT ... EMIT CHANGES (streaming subscription) and SELECT ... FROM table(mv_name) (batch scan) — one SQL, two execution modes. This is the unified pitch made concrete.
• ClickHouse MergeTree substrate — the historical scan uses standard ClickHouse columnar reads (10-100 ms per query). The streaming state uses in-memory buffers spilling to MergeTree parts. Same storage, both semantics.
• Iceberg sink to Polaris — keeps the warehouse layer engine-neutral. Trino, Snowflake, Spark all consume the same Iceberg table with no Proton-specific knowledge required.
• Cost — one Proton cluster (or Timeplus Cloud tenant), one Postgres for the dim table refresh, S3 for MergeTree tiering and Iceberg storage. Compared to running Flink SQL (streaming) + ClickHouse (historical) + a bespoke sync job to keep the two aligned, this is roughly one-half the operational complexity and eliminates the "we have two versions of the aggregation SQL and they disagree" class of bug. O(1) SQL per business rule; the unified engine handles both cadences.

SQL
Topic — sql
SQL problems on stream-table joins and rolling aggregates

Practice →

Streaming Topic — streaming Streaming problems on unified stream+batch analytics

Practice →

---

5. Decision matrix + Flink SQL / ksqlDB positioning + interview signals

The five-engine landscape — Materialize, RisingWave, Timeplus, Flink SQL, ksqlDB — how to pick per workload and what senior interviewers listen for

The mental model in one line: the 2026 streaming SQL landscape is not one engine but five: three modern specialists (Materialize for correctness, RisingWave for cloud scale, Timeplus for unified stream+batch), one mature generalist (Flink SQL — JVM-based, versatile, less IVM-native), and one legacy Kafka-only option (ksqlDB — Confluent-locked, older-generation) — and senior architects pick per workload against a matrix of consistency, state storage, latency, unified stream+batch, and existing infrastructure, rather than committing to a single engine for every workload; every interview signal converges on whether you can position all five against a specific scenario without hedging or hand-waving.

The five-engine decision matrix.

Axis	Materialize	RisingWave	Timeplus / Proton	Flink SQL	ksqlDB
Consistency	strict serializable	barrier-consistent	streaming-eventual	barrier-consistent (checkpoints)	eventually consistent
State store	in-mem + persist on S3	Hummock LSM on S3	ClickHouse MergeTree (local + S3)	RocksDB local (or state backend)	RocksDB local + Kafka
Latency (p95)	5-50 ms	100-500 ms	100 ms - 1 s	100 ms - 1 s	500 ms - 5 s
Unified stream+batch	via bootstrap	streaming-first	native stream + table	mature (bounded + unbounded APIs)	streaming-only
Wire protocol	Postgres	Postgres	ClickHouse HTTP + Postgres	Flink SQL Gateway	REST + KSQL DSL
Language / runtime	Rust	Rust	C++ (ClickHouse core)	Java / JVM	Java / JVM
Ecosystem breadth	narrow, deep	wide, growing	wide (ClickHouse-inherited)	widest	Kafka-only
Best for	correctness-critical	cloud-native scale	unified analytics	mixed batch+stream on JVM	Kafka-only pipelines
Worst for	huge state (memory expensive)	strict-serializable	strict-serializable	boilerplate for pure IVM	non-Kafka sources

Flink SQL — why it still matters in 2026.

• The mature generalist. Flink has been production-hardened since ~2015 (as a stream processor) and Flink SQL matured over 2019-2023. Every large enterprise data platform has Flink somewhere; the operational knowledge, tooling (Flink UI, savepoints), and connectors are unmatched.
• The batch+stream story. Flink's bounded and unbounded APIs share a runtime; you can write a Flink SQL query that runs once (batch) or continuously (stream) with essentially the same SQL. Timeplus's unified pitch is roughly the same idea, ~10 years later, in a lighter package.
• The state backend flexibility. Flink supports RocksDB (local), Filesystem (checkpoints on S3), and pluggable state backends. Not S3-native like Hummock, but battle-tested.
• When Flink SQL beats the specialists. When you already have Flink infrastructure and JVM operational knowledge on the team, when your workload is a mix of streaming SQL and DataStream API code, or when you need connectors that the specialists don't yet have.
• When the specialists beat Flink. When your workload is pure streaming SQL and you'd rather not maintain a Flink cluster — Materialize for correctness, RisingWave for cloud scale, Timeplus for unified stream+batch.

ksqlDB — why it exists and when to avoid it.

• The Confluent-bundled option. ksqlDB is a Confluent product that adds a SQL layer on top of Kafka Streams. It's Kafka-native by design; state is stored as compacted Kafka topics.
• Why it existed. In 2018-2020, ksqlDB was the easy-onramp answer to "how do I add streaming SQL to my Kafka deployment without adopting Flink." That value proposition has been eroded by the Rust-based specialists.
• Why to avoid it in 2026. No sources beyond Kafka; older-generation architecture (per-instance RocksDB, rebalance stalls); Confluent-controlled roadmap; ecosystem shrinkage as Materialize, RisingWave, and Timeplus have taken mindshare.
• When to use it. Only if you're already 100% Confluent-locked, the workload is small, and adopting a specialist is politically impossible.

The interview probes — what senior interviewers listen for.

• Naming all three specialists. Weak candidates name one engine (usually the one they know). Senior candidates name all three and place each in one of the three bets (correctness, scale, unified).
• Positioning against Flink. Weak candidates say "Flink is legacy" (wrong). Senior candidates say "Flink is the mature generalist; the specialists win when you don't need Flink's breadth."
• The consistency answer. Weak candidates conflate "streaming" with "eventually consistent." Senior candidates distinguish strict-serializable (Materialize), barrier-consistent (RisingWave, Flink), and streaming-eventual (Timeplus, ksqlDB).
• The state storage answer. Weak candidates don't know where state lives. Senior candidates name in-memory arrangements (Materialize), Hummock on S3 (RisingWave), MergeTree (Timeplus), RocksDB (Flink), Kafka-topics (ksqlDB).
• The migration story. Weak candidates say "we'd migrate everything." Senior candidates say "we'd pick per workload and pay migration cost only when the workload's axes change."

Interview signals — what wins the senior round.

• Naming differential dataflow as Materialize's substrate — instant senior credit.
• Naming Hummock as RisingWave's S3-native state store — instant senior credit.
• Naming Proton as Timeplus's OSS core, and pointing out the ClickHouse foundation — senior signal.
• Positioning Flink SQL as "the mature generalist" not "the legacy option" — mature framing.
• Refusing to pick one engine for all workloads — senior signal (nobody-picks-one-tool).

Worked example — the "which engine wins this workload" flashcards

Detailed explanation. The fastest way to internalise the decision matrix is to memorise a set of "workload → engine" flashcards. Every senior streaming SQL interview will hand you 3-5 workloads and expect a per-workload answer within 30 seconds. Walk through eight canonical flashcards.

• Financial reconciliation. Correctness ≥ everything. → Materialize.
• Ad-tech attribution at 200k events/s. Cloud scale > correctness. → RisingWave.
• Real-time cohort analysis + monthly backfill. Unified stream+batch. → Timeplus.
• Existing Flink cluster; add one more SQL job. JVM infra + team. → Flink SQL.
• Confluent-locked small pipeline. Political constraint. → ksqlDB (grudgingly).
• Live IoT telemetry dashboard. Sub-second, moderate scale. → Materialize or RisingWave.
• Streaming feature store for online ML. Sub-second reads, growing state. → RisingWave.
• CDC → warehouse feed. Batch-tolerant, delete-preserving. → Timeplus or Flink SQL.

Question. Build the flashcard table and add a one-line reasoning per card.

Input.

Workload	Axis in play	Engine
Financial reconciliation	correctness	Materialize
Ad-tech attribution	scale	RisingWave
Real-time cohort + monthly backfill	unified	Timeplus
Add-on to existing Flink	JVM infra	Flink SQL
Confluent-locked small pipeline	political	ksqlDB
Live IoT dashboard	latency	Materialize / RisingWave
Streaming feature store	scale + latency	RisingWave
CDC → warehouse	delete-preserving	Timeplus / Flink SQL

Code.

# Flashcard engine — memorise before the interview
FLASHCARDS = [
    ("financial reconciliation",         "Materialize",         "strict-serializable IVM matters"),
    ("ad-tech attribution 200k/s",       "RisingWave",          "S3-native state, K8s autoscale"),
    ("cohort + monthly backfill",        "Timeplus/Proton",     "unified stream+batch in one SQL"),
    ("add-on to existing Flink cluster", "Flink SQL",           "reuse JVM infra + team"),
    ("Confluent-locked small pipeline",  "ksqlDB",              "political constraint only"),
    ("live IoT dashboard",               "Materialize",         "sub-50ms freshness, correctness matters"),
    ("streaming feature store",          "RisingWave",          "growing state; S3 = cheap"),
    ("CDC → warehouse batch feed",       "Timeplus/Proton",     "unified query + ClickHouse historical"),
]

def pick_engine(workload_desc: str) -> str:
    for keyword, engine, reason in FLASHCARDS:
        if keyword.split()[0] in workload_desc.lower():
            return f"{engine} — {reason}"
    return "clarify axes: consistency / scale / unified / infra / political"

Step-by-step explanation.

1. Financial reconciliation demands strict-serializable — Materialize is the only engine that guarantees cross-view point-in-time consistency at read time. Anything else risks the streaming top-N differing from the batch top-N by the amount of state in flight.
2. Ad-tech attribution runs at 200k+ events/s; the state (interval-join buffers, per-user attribution state) grows to hundreds of GB. RisingWave's S3-native Hummock keeps this affordable and lets compute autoscale on K8s.
3. Real-time cohort analysis with monthly finance backfill is the unified stream+batch workload — one SQL query serving both. Timeplus is the engine designed for exactly this pattern.
4. If Flink infrastructure and JVM team already exist, adding a Flink SQL job is cheaper than adopting a new engine. Weight the operational tax of "one more thing to run" against the specialist wins.
5. ksqlDB is a political answer more than a technical one — pick it only when you have no choice and the workload is small enough to survive its limitations.
6. Live IoT dashboards depend on the correctness bar — if the metric must exactly match a batch reference, Materialize; if not, RisingWave for cheaper state.
7. Streaming feature stores grow state fast; S3-native state is the deciding factor. RisingWave.
8. CDC → warehouse feeds are batch-tolerant and delete-preserving; Timeplus's ClickHouse-native historical layer handles the warehouse side natively; Flink SQL works too if you have Flink infra.

Output.

Workload	Engine	Deciding axis
Financial reconciliation	Materialize	correctness
Ad-tech attribution	RisingWave	scale
Cohort + backfill	Timeplus	unified
Add-on to Flink	Flink SQL	existing infra
Confluent-locked	ksqlDB	political
IoT dashboard	Materialize / RisingWave	latency + correctness
Feature store	RisingWave	state growth
CDC → warehouse	Timeplus / Flink SQL	delete-preserving

Rule of thumb. Have these 8 flashcards memorised before any senior streaming SQL interview. The interviewer will pick 3 and expect a 30-second answer per card. Hedge on none of them; commit to the pick + one-line reasoning.

Worked example — the migration cost matrix

Detailed explanation. Once you've picked an engine, you must also know what it costs to change engines later — every streaming SQL choice has a migration cost that grows with the number of downstream consumers, the volume of state, and the depth of engine-specific features used. Walk through the migration cost matrix.

• Between the three Rust/C++ specialists. All three speak Postgres wire (Materialize, RisingWave) or ClickHouse SQL (Timeplus). Migration cost = re-write the DDL + re-bootstrap state.
• To or from Flink SQL. Flink's SQL dialect differs; savepoints don't port. Migration is a rewrite with a bootstrapping cutover.
• From ksqlDB. Migration off is common in 2026 and well-trodden; ksqlDB → RisingWave is the most common path.

Question. Estimate the engineer-week cost of each pairwise migration for a 50-view deployment.

Input.

From → To	Effort	Notes
Materialize → RisingWave	~4 weeks	Postgres wire; DDL close; bootstrap from Kafka
Materialize → Timeplus	~6 weeks	dialect gap (ClickHouse vs Postgres)
RisingWave → Materialize	~4 weeks	reverse of above
RisingWave → Timeplus	~5 weeks	dialect gap
Timeplus → RisingWave	~5 weeks	dialect gap
Any → Flink SQL	~8-12 weeks	dialect gap + savepoint bootstrap
Flink SQL → any specialist	~6-10 weeks	rewrite + Flink savepoint replay
ksqlDB → RisingWave	~4-6 weeks	most common 2026 migration

Code.

# Migration cost estimator (engineer-weeks) for 50-view deployment
COST_MATRIX = {
    ("materialize", "risingwave"): 4,
    ("materialize", "timeplus"):   6,
    ("risingwave",  "materialize"): 4,
    ("risingwave",  "timeplus"):    5,
    ("timeplus",    "risingwave"):  5,
    ("timeplus",    "materialize"): 6,
    ("materialize", "flink"):       10,
    ("risingwave",  "flink"):       10,
    ("timeplus",    "flink"):       10,
    ("flink",       "materialize"): 8,
    ("flink",       "risingwave"):  6,
    ("flink",       "timeplus"):    8,
    ("ksqldb",      "risingwave"):  5,
    ("ksqldb",      "flink"):       6,
}

def migration_cost(src: str, dst: str, views: int = 50) -> int:
    """Return engineer-weeks for a src → dst migration."""
    base = COST_MATRIX.get((src.lower(), dst.lower()))
    if base is None:
        return -1
    return int(base * (views / 50))

print(migration_cost("materialize", "risingwave"))  # → 4
print(migration_cost("ksqldb",      "risingwave"))  # → 5
print(migration_cost("flink",       "materialize")) # → 8

Step-by-step explanation.

1. Migrations between the Rust/C++ specialists are cheapest because two of them (Materialize, RisingWave) share the Postgres wire protocol, and all three have similar CREATE SOURCE + CREATE MATERIALIZED VIEW patterns. The dialect gap is small; the state has to be re-bootstrapped either way.
2. Timeplus's ClickHouse SQL dialect adds ~1-2 weeks of translation vs the Postgres-wire duo. Native stream / table primitives don't have direct equivalents in Materialize/RisingWave and need to be rewritten as separate source + view definitions.
3. Migrations to or from Flink SQL are the most expensive because Flink's DDL, state backend, and savepoint semantics are all different. Plan 8-12 engineer-weeks for a 50-view deployment; the savepoint-vs-source-bootstrap gap alone is a week of planning.
4. Migrations off ksqlDB are common enough in 2026 that most engines have documented paths. The most common path is ksqlDB → RisingWave (Postgres wire, K8s-native), which is roughly a 4-6 week project for a small deployment.
5. In all cases, the migration cost scales sub-linearly with view count (once you've built the migration tooling, adding view #51 is a few days, not a full week). The 50-view baseline is a rough anchor; halve for 10 views, double for 200.

Output.

Migration	Effort	Common in 2026
Materialize ↔ RisingWave	4 weeks	occasional
Materialize ↔ Timeplus	6 weeks	rare
RisingWave ↔ Timeplus	5 weeks	occasional
Any specialist ↔ Flink SQL	8-12 weeks	rare (JVM tax)
ksqlDB → RisingWave	4-6 weeks	very common
ksqlDB → Flink SQL	6 weeks	common

Rule of thumb. Pick your streaming SQL engine on the primary axis (correctness, scale, unified, infra); factor migration cost only when two engines score equally on the primary axis. A 5-week migration is cheap compared to years of running the wrong engine.

Worked example — 60-second interview elevator pitch

Detailed explanation. Senior interviewers frequently open with "give me your 60-second take on the streaming SQL landscape." This is a screening question — the candidate who names all five engines with a one-sentence position for each moves to the deeper technical rounds. Walk through the exact script.

• Sentence 1. Set the frame — "streaming SQL is IVM over unbounded input."
• Sentence 2. Name Materialize + its bet (correctness).
• Sentence 3. Name RisingWave + its bet (cloud scale).
• Sentence 4. Name Timeplus + its bet (unified).
• Sentence 5. Position Flink SQL + ksqlDB.
• Sentence 6. State the picking heuristic.

Question. Write the 60-second script exactly as you'd deliver it.

Input.

Beat	Content	Duration
Frame	streaming SQL = incrementally-maintained materialised views over unbounded input	10s
Materialize	Rust + differential dataflow + strict serializable + Postgres wire	10s
RisingWave	Rust + Hummock on S3 + K8s-native + Postgres wire	10s
Timeplus	ClickHouse-derived + unified stream+table + EMIT CHANGES	10s
Flink SQL + ksqlDB	mature JVM generalist + Confluent-locked legacy	10s
Heuristic	pick per workload on the four-axis matrix	10s

Code.

60-second streaming SQL landscape pitch
========================================

"Streaming SQL is the pattern where a `CREATE MATERIALIZED VIEW`
is kept incrementally consistent over an unbounded input — no
DataStream API code. The 2026 landscape has three modern
specialists and two established options.

Materialize is Rust-based, built on differential dataflow — Frank
McSherry's operator algebra from the Naiad paper — and gives
strict-serializable consistency across every view. Postgres wire,
in-memory arrangements with S3 durability via `persist`. Pick it
when correctness matters, like financial reconciliation.

RisingWave is Rust-based and cloud-native — compute is stateless
on Kubernetes, state lives in Hummock, an LSM tree directly on
S3. Postgres wire. Barrier-consistent. Pick it when horizontal
scale matters — hundreds of thousands of events per second with
K8s autoscaling.

Timeplus, on the Proton OSS engine, is built on ClickHouse's
storage layer and lets you write one query joining a stream to a
table, with `EMIT CHANGES` for streaming and standard SELECT for
historical. Pick it when the workload sits on the stream+batch
boundary.

Flink SQL is the mature JVM generalist — versatile, less
IVM-native, but every large enterprise already has it running.
ksqlDB is the older-generation Kafka-only option, worth avoiding
outside Confluent-locked environments.

The picking heuristic: consistency, state, latency, unified
stream+batch. Score every workload against those four axes,
overlay existing infra, and the engine falls out."

Step-by-step explanation.

1. The 60-second script is a screening artefact — deliver it verbatim (or nearly so) and you'll be moved to deeper rounds. Fumbling it signals a candidate who hasn't kept up with the 2024-2026 streaming landscape.
2. The frame sentence establishes vocabulary — "incrementally-maintained materialised views over unbounded input" is the precise definition of streaming SQL and the phrase every senior interviewer wants to hear.
3. Naming five engines with a specific bet for each is what separates senior from mid-level answers. The interviewer is scoring "does this candidate know all the options" as much as "does this candidate know their favourite option."
4. The Flink SQL + ksqlDB positioning is deliberately compact — you're not diving into Flink details; you're proving you know it exists and can position it. If the interviewer digs, follow up with the "mature generalist" framing.
5. The heuristic sentence at the end is the pivot to the technical round — it invites the interviewer to hand you a workload so you can walk the decision tree. This is where the deeper conversation starts.

Output.

Delivery signal	Weak	Senior
Names 5 engines	rare	mandatory
Names differential dataflow	never	required
Names Hummock / S3 state	never	required
Positions Flink SQL	often as "legacy"	as "mature generalist"
Ends with picking heuristic	never	required

Rule of thumb. Rehearse the 60-second pitch until it's automatic. Deliver it verbatim in the opening minute of every senior streaming SQL interview. The pitch tells the interviewer you've thought about the whole landscape, not just the one engine you happen to use.

Senior interview question on picking a streaming SQL engine

A senior interviewer might ask: "You're the tech lead of a new data platform team at a growing fintech. The founding CEO wants sub-second freshness on the merchant dashboard, the CFO wants monthly financial reconciliation to be exact to the cent, and the ML team wants a real-time feature store that grows to hundreds of GB. The company already has a small Confluent Kafka cluster and no Flink experience. Walk me through your streaming SQL engine picks per workload and defend each choice against the alternatives."

Solution Using a per-workload split — Materialize for finance, RisingWave for ML features, Timeplus for the merchant dashboard, and no ksqlDB despite Confluent

# Per-workload engine picks
Workload                          Engine        Reason
------------------------------------------------------------------------
Merchant dashboard (sub-second)   Timeplus      unified stream+batch;
                                                one SQL for live + monthly
Financial reconciliation          Materialize   strict-serializable IVM;
                                                exact-to-cent guarantee
ML feature store (100s of GB)     RisingWave    Hummock S3 state = cheap
                                                at scale; K8s autoscale
Kafka source layer                Confluent     already exists; no change

-- 1. Materialize for financial reconciliation
CREATE MATERIALIZED VIEW daily_reconciliation AS
SELECT date_trunc('day', event_ts)          AS day,
       merchant_id,
       SUM(amount_cents)                     AS total_cents,
       COUNT(*)                              AS txn_count
FROM   transactions_stream
WHERE  status = 'settled'
  AND  event_ts >= mz_now() - INTERVAL '90 days'
GROUP  BY day, merchant_id;

-- Strict-serializable: matches the batch reconciliation SQL to the cent

-- 2. RisingWave for the ML feature store
CREATE MATERIALIZED VIEW user_features_v1 AS
SELECT user_id,
       AVG(amount_cents)                                       AS avg_txn_1h,
       COUNT(*)                                                AS txn_count_1h,
       APPROX_COUNT_DISTINCT(merchant_id)                      AS distinct_merchants_1h,
       AVG(CASE WHEN status = 'declined' THEN 1 ELSE 0 END)   AS decline_rate_1h
FROM   HOPPING(transactions_src, event_ts, INTERVAL '1 minute', INTERVAL '1 hour')
GROUP  BY user_id;

-- State in Hummock (S3); K8s autoscales on load; 100+ GB is affordable

-- 3. Timeplus / Proton for the merchant dashboard
CREATE MATERIALIZED VIEW merchant_dashboard_5m AS
SELECT window_start,
       m.name         AS merchant_name,
       m.category,
       sum(t.amount_cents) / 100.0 AS revenue,
       count()                      AS txn_count
FROM   tumble(transactions_stream, event_ts, INTERVAL 5 MINUTE) t
JOIN   dim_merchants m ON m.id = t.merchant_id
GROUP  BY window_start, m.name, m.category
EMIT CHANGES;

-- Historical monthly report — same materialised view, batch mode
SELECT toStartOfMonth(window_start) AS month, category, sum(revenue) AS monthly_rev
FROM   table(merchant_dashboard_5m)
WHERE  window_start BETWEEN '2026-01-01' AND '2026-08-01'
GROUP  BY month, category;

Step-by-step trace.

Workload	Engine	Winning axis	Loss vs alternative
Finance	Materialize	strict-serializable	small state; memory-cheap
Feature store	RisingWave	S3 state cost	not strict-serializable but ML tolerates
Merchant dashboard	Timeplus	unified stream+batch	one SQL vs two engines
Kafka source	existing Confluent	already deployed	no change
ksqlDB	rejected	political fit only	limits future evolution

After deployment, three engines cover the three workloads with strict correctness where it's needed (finance), cloud-scale state where it's needed (ML), and unified stream+batch where it's needed (merchant dashboard). Every workload has one clear winner on its primary axis; no engine is over-scoped to a workload it's not the best fit for.

Output:

Workload	Engine	Freshness	Correctness	State budget	Notes
Merchant dashboard	Timeplus	100 ms	streaming-eventual	~10 GB local	live + monthly one query
Financial reconciliation	Materialize	30-50 ms	strict-serializable	~20 GB memory	matches batch to cent
ML feature store	RisingWave	500 ms	barrier-consistent	~200 GB S3	K8s autoscale
Kafka	Confluent	—	—	—	existing
Operational tax	3 engines	—	—	—	tolerable at DE team of 5+

Why this works — concept by concept:

• Per-workload engine choice — one engine per axis-winning workload beats one engine for everything. The operational tax of three engines is real but bounded; the alternative is over-scoping every workload to a single engine that only wins on one axis.
• Materialize for finance — strict-serializable is the only guarantee that "the streaming top-N matches the batch top-N to the cent." RisingWave and Timeplus can't offer this without additional reconciliation code.
• RisingWave for the feature store — the state grows to 100+ GB; keeping that in Materialize's memory-first arrangements costs more than the entire Materialize cluster. Hummock on S3 is the correct answer at this scale.
• Timeplus for the merchant dashboard — the CEO wants live and the CFO wants monthly, from the same data. One Timeplus materialised view serves both cadences; the alternative is a Flink pipeline plus a ClickHouse warehouse plus a reconciliation script.
• Cost — three specialist engines (2 vCPU each idle; scale on demand) + one Confluent Kafka + one Postgres for dim tables. Compared to one Flink cluster covering all three workloads sub-optimally, the three-engine split delivers correctness where required, scale where required, and unification where required, at 1.2-1.5× the operational surface but ~0.5× the correctness / scale debt. O(1) SQL per workload; the engines specialise.

Streaming
Topic — streaming
Streaming problems on multi-engine architectures

Practice →

SQL
Topic — sql
SQL problems on cross-engine streaming patterns

Practice →

---

Cheat sheet — streaming SQL recipes

• Which engine when. Materialize when strict-serializable correctness is a hard requirement (financial reconciliation, ad-tech attribution) — differential dataflow + Postgres wire + in-memory arrangements with S3 persist. RisingWave when cloud-native scale is the axis (200k+ events/sec, growing state, K8s autoscale) — stateless compute + Hummock LSM on S3 + Postgres wire. Timeplus / Proton when unified stream+batch is the axis (real-time dashboards + historical backfill in one SQL) — ClickHouse foundation + stream + table first-class types + EMIT CHANGES clause. Flink SQL when you already have Flink infra + JVM team; ksqlDB only when Confluent-locked and no alternative is politically possible. Never pick one engine for all workloads; the operational tax of running two or three specialists beats over-scoping.
• Materialize source + view + SUBSCRIBE template. CREATE CONNECTION kafka_conn TO KAFKA (BROKER '...' SASL ...); CREATE SOURCE events_src FROM KAFKA CONNECTION kafka_conn (TOPIC 'events') FORMAT JSON WITH (SIZE = 'medium'); CREATE VIEW events AS SELECT (data->>'user_id')::BIGINT AS user_id, ... FROM events_src; CREATE MATERIALIZED VIEW top_users AS SELECT user_id, SUM(score) AS score FROM events WHERE event_ts >= mz_now() - INTERVAL '1 hour' GROUP BY user_id; CREATE DEFAULT INDEX ON top_users; — SUBSCRIBE via COPY (SUBSCRIBE (SELECT ...)) TO STDOUT for push deltas.
• RisingWave source + view + sink template. CREATE SOURCE events_src (cols...) WITH (connector='kafka', topic='events', properties.bootstrap.server='b-1.kafka:9092', scan.startup.mode='earliest') FORMAT PLAIN ENCODE JSON; ALTER SOURCE events_src ADD WATERMARK event_ts AS event_ts - INTERVAL '30 seconds'; CREATE MATERIALIZED VIEW top_users AS SELECT user_id, SUM(score) FROM TUMBLE(events_src, event_ts, INTERVAL '5 minutes') GROUP BY user_id, window_start; CREATE SINK top_users_sink FROM top_users WITH (connector='iceberg', warehouse.path='s3://...', catalog.type='rest', catalog.uri='http://polaris:8181', primary_key='window_start,user_id', type='upsert');
• Timeplus / Proton stream + table + EMIT CHANGES template. CREATE STREAM events_stream (cols...) SETTINGS type='kafka', brokers='b-1.kafka:9092', topic='events', data_format='JSONEachRow'; CREATE TABLE dim_users (id uint64, tier string) ENGINE=MergeTree ORDER BY id; CREATE MATERIALIZED VIEW enriched AS SELECT window_start, d.tier, sum(e.score) AS score FROM tumble(events_stream, event_ts, INTERVAL 5 MINUTE) e JOIN dim_users d ON d.id = e.user_id GROUP BY window_start, d.tier EMIT CHANGES; — historical: SELECT ... FROM table(enriched) WHERE window_start BETWEEN ....
• Consistency model comparison. Materialize = strict-serializable (any read at mz_now() observes a consistent cross-view cut). RisingWave = barrier-consistent (checkpoints define consistent cuts; sub-barrier reads may observe partial state). Timeplus / Proton = streaming-eventual (EMIT CHANGES produces row-level updates as computed; historical scans are ClickHouse batch semantics). Flink SQL = barrier-consistent (Flink checkpoints). ksqlDB = eventually consistent. Print this on a sticky note; use it in every interview.
• State storage comparison. Materialize = in-memory arrangements (multi-versioned indexed traces) + persist on S3 for durability; expensive per GB, fast for lookups. RisingWave = Hummock (LSM tree written directly to S3); cheap per GB, slower for cold reads. Timeplus / Proton = ClickHouse MergeTree parts on local disk with S3 tiering; ClickHouse-native (columnar, sorted, compressed). Flink = RocksDB local (or Filesystem/S3 backend); mature but capacity-planning heavy. ksqlDB = per-instance RocksDB + compacted Kafka topics; older-generation.
• Latency budgets. Materialize: sub-ms arrangement lookup, single-digit-ms view maintenance. RisingWave: 100-500 ms (bounded by barrier interval, typically 1 s). Timeplus: sub-second for EMIT CHANGES; ClickHouse-native for historical. Flink: 100 ms - 1 s (checkpoint-bound). ksqlDB: 500 ms - 5 s. Match the engine to the freshness SLA; do not over-buy latency.
• Decision matrix (4 axes × 5 engines). Axes: consistency, state, latency, unified-stream+batch. Winners per axis: consistency = Materialize (strict-serializable); state = RisingWave (S3 cheapest per GB); latency = Materialize (single-digit ms); unified stream+batch = Timeplus (native). Overlay existing infra + team knowledge; the engine falls out. Score every workload against the matrix; commit to the pick.
• Postgres CDC source patterns. Materialize: CREATE SOURCE ... FROM POSTGRES CONNECTION pg_conn (PUBLICATION 'mz_pub') — native logical replication. RisingWave: CREATE SOURCE ... WITH (connector = 'postgres-cdc', hostname='...', username='...', database.name='...', schema.name='...', table.name='...', slot.name='rw_slot'). Timeplus: INSERT INTO t SELECT * FROM postgres('host', 'db', 'table', 'user', 'pw') for one-shot; CDC connector via Kafka Connect Debezium pipeline. Consistency: Materialize gives strict-serializable across the Postgres source + other sources; RisingWave gives barrier-consistent; Timeplus streams the CDC as EMIT CHANGES from an intermediate Kafka topic.
• Kubernetes deployment cheatsheet. Materialize: Helm chart, single-writer with configurable clusters; scale by cluster size + replication factor. RisingWave: Helm chart with 4 components (meta, compute, frontend, compactor); scale compute via K8s HPA (state is on S3, so scaling is trivial). Timeplus / Proton: Docker or Helm; managed variant is Timeplus Cloud. Flink: Kubernetes Operator; JobManager + TaskManager pods; state backend is the operational headache. ksqlDB: standalone ksqlDB Server; small deployments only.
• Replay / bootstrap patterns. Materialize: source has a Kafka offset; drop + recreate view triggers re-hydration from persist snapshot + source tail. RisingWave: source scan.startup.mode = 'earliest' | 'latest' | 'timestamp'; drop + recreate view triggers hydration from Hummock or source. Timeplus / Proton: stream _tp_time field lets you query historical via table(stream_name) with WHERE _tp_time BETWEEN ...; retention set via MergeTree TTL. Flink: savepoint or checkpoint restore; savepoints are the durable equivalent of Materialize's persist.
• Sink patterns. Iceberg (all three specialists have native Iceberg sinks with upsert semantics keyed on a primary key). Kafka (all support Kafka sinks; Materialize + RisingWave via CREATE SINK, Timeplus via CREATE EXTERNAL STREAM). Snowflake / warehouse (via Iceberg is the modern pattern; direct sinks exist too). Redis / online cache (RisingWave has a native Redis sink; the others typically go via Kafka + a downstream sync worker).
• Interview signals summary. Naming all three specialists + Flink SQL + ksqlDB (not just one). Naming differential dataflow for Materialize (not just "IVM"). Naming Hummock for RisingWave (not just "S3-native"). Positioning Timeplus against ClickHouse (Proton is the streaming layer on ClickHouse's storage). Refusing to pick one engine for all workloads. Naming migration costs on the four-axis matrix. Naming the 60-second landscape pitch in the opening minute.
• When each engine is not the answer. Materialize is not the answer at huge state scale (100s of GB in memory-first arrangements is expensive). RisingWave is not the answer when strict-serializable is required (barrier consistency is weaker). Timeplus is not the answer when strict-serializable is required or when the workload is purely streaming (no batch component). Flink SQL is not the answer when you want to avoid the JVM operational tax. ksqlDB is not the answer for anything greenfield in 2026.

Frequently asked questions

What is streaming SQL in one sentence?

Streaming SQL is the pattern where a continuous query defined once — typically as CREATE MATERIALIZED VIEW view_name AS SELECT ... over an unbounded input stream — is kept incrementally consistent by the engine as new events arrive, without the developer ever writing a DataStream / KStream program by hand. The engine compiles the SQL into a dataflow graph (differential dataflow in Materialize, actor DAG in RisingWave, ClickHouse-derived streaming plan in Timeplus / Proton), maintains per-operator state (memory arrangements or Hummock LSM or MergeTree parts), and emits change events downstream as the maintained result changes. Every senior data-engineering stack in 2026 needs streaming SQL somewhere because the alternative — imperative stream-processing code — is expensive to write, hard to reason about, and consistently loses correctness in ways SQL doesn't.

Materialize vs Flink SQL — when do I pick each?

Default to Materialize when correctness is the primary requirement — the strict-serializable guarantee via differential dataflow means every read observes a consistent point-in-time cut across every materialised view in the deployment, and the streaming answer to any query exactly matches the batch answer to the same query. Materialize is Rust-based, Postgres-wire compatible, and keeps state as in-memory arrangements with S3 persist durability; it is the modern IVM-first specialist. Default to Flink SQL when you already have Flink infrastructure and JVM operational knowledge on the team, when your workload is a mix of streaming SQL and DataStream API code, or when you need connectors that Materialize does not yet have. Flink is the mature generalist — its bounded and unbounded APIs share a runtime, its state backends (RocksDB, Filesystem, S3) are battle-tested, and its ecosystem is the widest in the streaming space. The specialist wins on correctness and DE-friendliness; the generalist wins on ecosystem breadth and existing infra. In a greenfield deployment where correctness matters more than ecosystem, pick Materialize; in a brownfield deployment with existing Flink, add another Flink SQL job.

What is differential dataflow?

Differential dataflow is an incremental-computation model (published by Frank McSherry and collaborators as the Naiad paper in 2013) where every input change is represented as a (data, time, diff) triple — the row payload, a logical timestamp, and a signed multiplicity (+1 for insert, -1 for delete) — and every operator (map, filter, join, group_by, reduce) is implemented as a function on collections of these triples that produces correct incremental outputs. When a new triple arrives, the operator consults its shared indexed state (an arrangement — a multi-versioned sorted trace) at the appropriate logical time, computes the delta, and emits output triples that propagate downstream. This is the substrate Materialize is built on; it is what makes strict-serializable IVM tractable at production scale. The key win over ad-hoc streaming implementations is that joins, aggregations, and window functions all compose correctly — the operator algebra is closed, so a complex SQL query with multiple joins and window functions compiles into a single differential dataflow graph that maintains its output incrementally with per-event work bounded by the arrangement depth, not by the input rate.

RisingWave vs Materialize on cost?

RisingWave typically wins on cost at scale because state lives in Hummock, an LSM tree written directly to S3 (or GCS, Azure Blob, MinIO) — object storage is roughly 100× cheaper per GB per month than the memory Materialize's arrangements require. For a workload with, say, 500 GB of state, Materialize needs ~500 GB of memory distributed across replicas (multiple thousand dollars per month at cloud prices) while RisingWave needs 500 GB of S3 (tens of dollars per month) plus a modest compute footprint. Materialize wins on cost at small state scale (up to ~50 GB) because the memory-first arrangements are dramatically faster per query — sub-millisecond lookups vs 30-100 ms cold reads from Hummock — and you can size the compute smaller because fewer replicas are needed for correctness. The crossover point is roughly (state size × query latency requirement) — at high state and looser latency, RisingWave wins; at low state and tight latency, Materialize wins. Add in the K8s-native autoscale story (RisingWave scales compute independently of state) and RisingWave becomes the clear pick for elastic ad-tech / feature-store workloads; Materialize stays the pick for tight, correct, low-latency dashboards.

Does Timeplus replace ClickHouse?

No — Timeplus and Proton extend ClickHouse with streaming SQL, they do not replace it. Proton is the OSS core (Apache-2.0) that adds STREAM types, EMIT CHANGES, and streaming windowing on top of ClickHouse's storage engine, query planner, and columnar execution. If you already run ClickHouse, adopting Proton (or the managed Timeplus Cloud) adds streaming SQL as a new capability on top of the same query dialect, the same MergeTree storage, the same operational conventions — you do not need to migrate historical data or rewrite historical SQL. If you already run Flink for streaming + ClickHouse for historical, Timeplus is the consolidation pitch: one engine handles both cadences with one SQL query, and the maintenance burden of keeping the Flink and ClickHouse aggregations in sync goes away. In interviews, the senior framing is "Timeplus is the streaming-first extension of ClickHouse, not a replacement" — position it as ClickHouse-adjacent, not ClickHouse-competing, and interviewers will nod because that's the correct architectural placement.

Are streaming SQL engines production-ready in 2026?

Yes, unambiguously, for all three modern specialists. Materialize has been GA since 2022, is used at production at customers including Ramp, Ridecell, and multiple financial-services firms; the operational story around clusters, replicas, and persist is well-documented and community-tested. RisingWave hit v1.0 in 2023 and v2.0 in 2024-2025; it is used in production at Netflix, ByteDance, and multiple ad-tech companies for their K8s-native streaming feature stores and attribution pipelines. Timeplus / Proton has been production-hardened since 2023; Proton (the OSS core) is on GitHub with an active community, and Timeplus Cloud is the managed variant with SLAs. Flink SQL is a decade old and hasn't been "not production-ready" since 2018. The genuinely still-maturing corner of the space is around cross-engine tooling (federation, schema migration between engines, unified observability) — but each individual engine is production-grade. The 2026 interview answer is unambiguously "yes, pick any of the three specialists on primary-axis fit; the maturity gap that existed in 2022 has closed."

Practice on PipeCode

• Drill the SQL practice library → for the materialised-view, top-N, and rolling-aggregate problems that dominate senior streaming SQL interviews.
• Rehearse on the streaming practice library → for the Kafka-source + windowed-join + sink patterns every Materialize / RisingWave / Timeplus deployment lives on.
• Sharpen the aggregation axis with the aggregation practice library → for the tumble / hop / session-window shapes streaming SQL engines make cheap.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the four-axis decision matrix against real graded inputs.

Lock in streaming sql muscle memory

Docs explain engines. PipeCode drills explain the decision — when strict-serializable correctness demands Materialize, when Hummock's S3 state makes RisingWave the K8s-native choice, when Timeplus's unified stream+table syntax removes two engines from your stack, when Flink SQL is still the pragmatic answer, and when ksqlDB is a political-only pick. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice streaming problems →
Practice SQL problems →