Streaming Databases: SQL Views Over Kafka

A streaming database is a database whose tables are maintained continuously from event streams: a query is registered once as a materialized view, and the engine updates the stored result incrementally as records arrive, instead of recomputing it at read time. Applications read the view like an ordinary table while the stream keeps feeding it.

The name is also applied to stream processors such as ksqlDB and Flink SQL, and to OLAP stores such as ClickHouse and Pinot. They work differently.

Streaming database vs stream processor vs OLAP store

Pick by the question being asked. The last row is not a database at all:

Question being asked	What fits	Examples
"Keep this aggregate live and let services read it"	Streaming database (incremental view maintenance)	Materialize, RisingWave, Feldera
"Turn topic A into topic B, continuously"	Stream processor with a SQL dialect	Flink SQL, ksqlDB, Kafka Streams
"Query months of events with ad-hoc filters"	OLAP store fed by Kafka	ClickHouse, Apache Pinot, Apache Druid
"Find one record in a topic during an incident"	Topic search or indexing tool	consumer seek by timestamp, an external index

- A stream processor with SQL maintains a pipeline whose output is another stream. Flink SQL and ksqlDB fit here, even though ksqlDB can also serve point lookups.

• A streaming database maintains a view. The output is a table that is queryable at any moment and whose content is defined as query(inputs so far).
• An OLAP store fed by Kafka, such as ClickHouse, Pinot, or Druid, copies events into indexed storage for ad-hoc queries over history.
• Topic search is not a streaming database. It answers a one-off question about raw records and keeps no live result.

A PostgreSQL materialized view looks similar but is not incrementally maintained: REFRESH MATERIALIZED VIEW re-runs the query and replaces the stored result. A streaming database applies each change as it arrives, so the view stays current without a refresh job.

A streaming database is the wrong tool for three common jobs: filtering or routing an append-only stream into another topic (a stream processor is simpler), exploring months of history with ad-hoc filters (an OLAP store indexes for that), and serving reads that are always by key. For key lookups, a compacted topic materialized into a Kafka Streams state store is usually enough: the topic stays the source of truth, the store is rebuilt by replaying it, and the application adds its own RPC layer to reach keys held by other instances. Use a streaming database once reads filter or join across keys, or other teams need a SQL client rather than an application API. When compacted topics are enough walks through the key-lookup pattern.

Why a Kafka topic is not a queryable table

A Kafka topic is indexed by offset and timestamp. These indexes help the broker find a position in a log segment; they do not make the record payload queryable.

There is no index by key or by fields in the payload. Finding an order by id requires scanning records from a chosen offset. Log compaction keeps the latest value per key, but it does not add a lookup API. A consumer must still read the partition and build its own state.

So every SQL-over-Kafka product stores its state outside the topic:

• ksqlDB materializes into RocksDB on each server, backed by compacted changelog topics.
• Materialize, RisingWave, Feldera consume the topic into their own storage and maintain views there.
• Topic search tools consume the topic and build an external index. Querying Kafka topics with SQL explains this pattern.

Incremental view maintenance: what a changed row costs

Batch systems answer a query by rerunning it over all the input. Incremental view maintenance (IVM) applies only the change. It needs two things.

The input must describe changes. An append-only topic contains inserts. Updates and deletes need an upsert or CDC envelope with a stable key. Engines such as Materialize and RisingWave use that key to replace or delete the current row. A view built from a CDC topic is complete only if the connector captured existing rows first; Debezium takes an initial snapshot by default (snapshot.mode=initial) before streaming changes.

Every operator must handle retractions. When an order amount changes from 40 to 25, a SUM applies -40 and +25. A MAX may need other values to find the next maximum, and a join must remove rows created from the old value. The engine keeps enough state to update the result without rerunning the full query.

An unbounded join between two busy streams may need to keep its state forever. A stream processor with explicit windows and watermarks is a better fit when old state can expire.

Consistency: whether two views agree at the same moment

Exactly-once does not mean two outputs agree with each other.

• Pipeline-level consistency. In a dataflow engine such as Flink or Kafka Streams, each pipeline commits its own progress. Checkpoints protect each pipeline, but outputs of separate pipelines may reflect different positions in their inputs.
• Snapshot consistency. A streaming database serves related views at one logical time. The snapshot may wait for recent input or be slightly stale, but its tables agree with one another.

The cost: a read either waits for every required input to reach the same time, or returns an older, consistent snapshot.

The guarantee stops at the engine. An upstream CDC connector can still lag, and an exported sink can still fall behind.

ksqlDB vs newer streaming databases

ksqlDB is a SQL layer over Kafka Streams. Two limits follow from that:

• State uses Kafka resources. Aggregations create changelog topics, and some joins or aggregations also create repartition topics. Local RocksDB stores add memory and disk use to each ksqlDB server.
• Pull queries are limited. Fast lookups use key equality. Range filters and non-key predicates require table scans. The pull query itself cannot contain JOIN, GROUP BY, or WINDOW clauses, though it can read tables materialized with them.

ksqlDB is not deprecated and is still supported in Confluent Platform and Confluent Cloud, but Confluent recommends Flink SQL for new stream processing workloads and keeps ksqlDB fully supported for existing applications.

Newer engines store and serve state differently:

• Flink materialized tables use a freshness target (optional; defaults apply when omitted) to choose between continuous updates and scheduled refreshes.
• Materialize and RisingWave persist state in object storage such as S3 and expose views through the PostgreSQL protocol.
• Feldera compiles SQL into an incremental dataflow, based on the DBSP model, that computes only the changes to each view.
• Managed views for AI agents. Confluent's Real-Time Context Engine materializes a topic into a table in a low-latency serving layer and exposes it to agents over MCP.

Licenses differ, as of October 2026: RisingWave is Apache 2.0, Feldera's open-source edition is MIT, ksqlDB is under the Confluent Community License, and Materialize is under the Business Source License 1.1, free below a 24 GiB memory and 48 GiB disk limit per installation and converting to Apache 2.0 four years after each release.

Operating a streaming database: ownership, schema drift, PII

A materialized view runs as a long-lived stateful consumer and needs the same care:

• Ownership. Give each view an owner, consumer group, alerts, and capacity plan.
• Schema evolution. A source declares column names and types. Enforce compatibility in the Schema Registry and prefer additive changes for topics with downstream views.
• PII and erasure. A materialized view creates another copy of the data, so a deletion request has to reach it. On an upsert source, a tombstone retracts the row from the views built on it; on an append-only source, the delete never arrives. Keep delete.retention.ms on compacted sources longer than any view may stay stopped, or the tombstone is cleaned before the view reads it (GDPR and Kafka: right to erasure covers the downstream cascade). Apply masking or encryption before the engine stores it. Tools such as Conduktor Gateway encrypt fields on produce or mask them per consumer.
• Freshness. Decide the latency the reader needs before choosing the engine. When hourly or daily is acceptable, a scheduled refresh (a Postgres materialized view, or a Flink materialized table in full refresh mode) avoids keeping state and compute live around the clock. Practitioners at Current 2026 said the same about Kafka spend.

Example: a windowed materialized view in RisingWave

This RisingWave example reads JSON records from Kafka and maintains revenue per merchant in one-minute windows.

-- 1. Attach the topic. CREATE SOURCE does not store the raw records;
--    use CREATE TABLE ... PRIMARY KEY ... FORMAT UPSERT if the topic
--    carries corrections that should be applied as updates.
CREATE SOURCE payments (
    payment_id   VARCHAR,
    merchant_id  VARCHAR,
    amount       DECIMAL,
    paid_at      TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'payments',
    properties.bootstrap.server = 'kafka-1:9092,kafka-2:9092',
    scan.startup.mode = 'earliest'
) FORMAT PLAIN ENCODE JSON;

-- 2. Register the query once. RisingWave backfills history, then
--    updates the result incrementally as records arrive.
CREATE MATERIALIZED VIEW merchant_revenue_1m AS
SELECT merchant_id,
       window_start,
       window_end,
       COUNT(payment_id) AS payments,
       SUM(amount)       AS revenue
FROM TUMBLE(payments, paid_at, INTERVAL '1 MINUTE')
GROUP BY merchant_id, window_start, window_end;

-- 3. Read it like a table, from any Postgres client.
SELECT merchant_id, window_start, payments, revenue
FROM merchant_revenue_1m
WHERE merchant_id = 'mrc_4821'
ORDER BY window_start DESC
LIMIT 2;

Illustrative output after a few minutes of traffic:

merchant_id |      window_start      | payments | revenue
-------------+------------------------+----------+---------
 mrc_4821    | 2026-09-01 09:14:00+00 |        7 |  412.50
 mrc_4821    | 2026-09-01 09:13:00+00 |       12 |  898.00

The query reads from the stored view instead of scanning a Kafka partition. If the source were an upsert table, correcting an existing payment would remove the old amount and add the new one rather than count both records.

Related Pages

• ksqlDB for Real-Time Data Processing: the SQL layer over Kafka Streams, and its push and pull query model.
• Flink SQL and Table API: dynamic tables, continuous queries, and materialized tables in Flink.
• Kafka vs Postgres: the log-versus-table comparison this article builds on.
• Kafka Log Compaction Explained: latest value per key on the broker, and why it is not a lookup index.
• Change Data Capture Fundamentals: where the before and after images that drive retractions come from.
• Real-Time Analytics with Streaming Data: the wider architecture in which views, pipelines, and OLAP stores sit.
• Kafka Streams vs Apache Flink: the two dataflow engines behind most SQL pipelines.
• Data Masking and Anonymization for Streaming: keeping PII out of downstream copies such as materialized views.

Sources and References

• Apache Kafka documentation: Log implementation
• KIP-33: Add a time based log index, Apache Kafka wiki
• Materialize documentation: Isolation level
• Materialize documentation: CREATE SOURCE, Kafka
• RisingWave documentation: CREATE MATERIALIZED VIEW
• Feldera documentation
• PostgreSQL documentation: REFRESH MATERIALIZED VIEW
• Apache Flink documentation: Materialized table statements
• Confluent documentation: ksqlDB for Confluent Platform
• Confluent documentation: Real-Time Context Engine
• ksqlDB documentation: Pull queries
• ksqlDB documentation: Capacity planning
• Apache Kafka documentation: Interactive queries in Kafka Streams
• Apache Kafka documentation: Topic configs, delete.retention.ms
• Debezium documentation: PostgreSQL connector snapshots
• ClickHouse documentation: Kafka table engine