Building a Real-Time Lakehouse with Kafka, ClickHouse, Trino and Iceberg

Real-time analytics often begins with a deceptively simple requirement: keep every historical change, make the latest state available immediately and avoid locking all data inside an expensive proprietary warehouse.

A traditional data lake can store large volumes of historical data cheaply, but it does not automatically provide fast updates, consistent tables or sub-second dashboard queries. A real-time analytical database delivers speed, but keeping the complete history there may increase storage cost and tie the architecture to a single engine.

A better approach is to combine the two models:

• Kafka transports database changes in real time.
• ClickHouse serves hot data and low-latency analytical queries.
• S3 provides durable and economical object storage.
• Parquet stores the physical columnar data files.
• Apache Iceberg adds table metadata, transactions, snapshots and schema evolution.
• Trino provides distributed SQL access to the lakehouse and other enterprise data sources.

The result is not merely a data lake. It is a real-time lakehouse with separate hot and historical serving layers.

Understanding the role of each technology

S3, Parquet and Iceberg are sometimes described as competing technologies, but they solve different parts of the problem.

Technology	Primary responsibility
CDC tool	Captures inserts, updates and deletes from operational databases
Kafka	Durable event transport, buffering and replay
ClickHouse	Real-time analytical storage and low-latency querying
S3 or S3-compatible storage	Durable object storage for long-term data
Parquet	Columnar file format stored in the object store
Apache Iceberg	Open table format managing schemas, snapshots, partitions and data files
Iceberg catalog	Coordinates table metadata for readers and writers
Trino	Distributed SQL engine for Iceberg and federated data sources

Parquet defines how data is physically represented inside a file. Iceberg organizes those Parquet files into reliable analytical tables. S3 stores the files, while Trino and other compatible engines query them.

The Trino Iceberg connector supports Parquet, ORC and Avro data files and uses an Iceberg catalog such as a REST catalog, AWS Glue, Hive Metastore, JDBC or Nessie.

Reference architecture

flowchart TD
    A["Operational databases"] --> B["CDC connector"]
    B --> C["Kafka"]

    C --> D["ClickHouse Kafka engine"]
    D --> E["Materialized views"]
    E --> F["ClickHouse hot tables"]

    C --> G["Flink or Iceberg sink"]
    G --> H["Iceberg and Parquet on S3"]
    I["Iceberg catalog"] --- H

    F --> J["Real-time APIs and dashboards"]
    H --> K["Trino"]
    K --> L["BI and historical analytics"]

The architecture deliberately creates two paths from Kafka.

The hot path sends events directly into ClickHouse through its native Kafka integration. The lakehouse path writes durable CDC history and curated tables into Iceberg on S3. These paths serve different workloads and should not be forced into a single storage engine.

Step 1: Capture operational changes with CDC

A CDC connector reads the database transaction log instead of repeatedly polling application tables. Depending on the source database, this may be a PostgreSQL logical replication stream, a MySQL binlog, an Oracle SCN or a SQL Server CDC record.

Debezium is a common choice. It captures row-level inserts, updates and deletes and publishes change events to Kafka. A typical event contains:

• The source table and primary key
• The operation type
• The state before and after the change
• The database transaction timestamp
• The transaction or log position
• Connector and ingestion metadata

The database log position is especially important. Kafka arrival time alone is not sufficient for ordering updates because retries, network delays and partition rebalancing can cause events to arrive later than expected.

Every event should therefore have a stable version or ordering value such as an LSN, SCN, binlog position or source transaction sequence.

Step 2: Use Kafka as the replayable change log

Kafka is more than a connection between the source and analytical systems. It provides isolation between producers and consumers.

ClickHouse can temporarily stop consuming without affecting the source database. The Iceberg writer can be restarted and replay earlier messages. Additional consumers can later be introduced for data quality, search, machine learning or cache updates.

Kafka retention must be long enough to recover downstream systems. A short retention period reduces storage use, but it also reduces the available recovery window. The appropriate value depends on data volume and operational recovery objectives; several days is a common starting point, while critical systems may retain events longer or archive the raw stream into the lakehouse.

Step 3: Ingest Kafka directly with ClickHouse

ClickHouse does not require Flink for its Kafka ingestion path. In a self-managed deployment, its native pattern consists of three objects:

1. A Kafka table engine that consumes the topic
2. A permanent MergeTree-family target table
3. A materialized view that transforms and inserts incoming blocks

According to the ClickHouse Kafka table engine documentation, the materialized view continuously receives new records from the Kafka engine and can transform the stream before writing it to one or more target tables.

The following simplified example assumes the Debezium event has already been flattened into an analytical schema:

CREATE TABLE customer_cdc_queue
(
    customer_id UInt64,
    customer_name String,
    customer_status LowCardinality(String),
    source_version UInt64,
    is_deleted UInt8,
    event_time DateTime64(3)
)
ENGINE = Kafka
SETTINGS
    kafka_broker_list = 'kafka-1:9092,kafka-2:9092',
    kafka_topic_list = 'customer_cdc',
    kafka_group_name = 'clickhouse_customer_current',
    kafka_format = 'JSONEachRow',
    kafka_num_consumers = 4;

The Kafka engine is a streaming interface rather than the final analytical table. The permanent current-state table can use ReplacingMergeTree:

CREATE TABLE customer_current
(
    customer_id UInt64,
    customer_name String,
    customer_status LowCardinality(String),
    source_version UInt64,
    is_deleted UInt8,
    event_time DateTime64(3)
)
ENGINE = ReplacingMergeTree(source_version, is_deleted)
ORDER BY customer_id;

The materialized view connects the stream to the target:

CREATE MATERIALIZED VIEW customer_current_mv
TO customer_current
AS
SELECT
    customer_id,
    customer_name,
    customer_status,
    source_version,
    is_deleted,
    event_time
FROM customer_cdc_queue;

ReplacingMergeTree models an update as another immutable insert with a higher version. Background merges eventually remove older versions for the same sorting key. This is efficient for analytical CDC workloads, but deduplication does not happen synchronously. Queries that require guaranteed current results must account for this behavior, for example by using FINAL selectively or a version-aware query pattern. The ClickHouse ReplacingMergeTree guide describes this asynchronous process.

The current-state table should also use a stable partition strategy. Partitioning it by the event month can place two versions of the same customer into different partitions, preventing them from being merged together. An unpartitioned table or a stable partition derived from the entity key is usually safer for this model.

For append-only historical events, a separate MergeTree table can be partitioned by event date because those rows do not need to replace one another.

Step 4: Persist the durable history in Iceberg

The second Kafka branch writes the CDC stream to Iceberg tables stored as Parquet files on S3. This can be implemented with Apache Flink, Flink CDC or a Kafka Connect Iceberg sink.

Flink is valuable on this branch because it can provide stateful transformations, event ordering, deduplication and controlled Iceberg commits. The Apache Iceberg Flink sink supports batch and streaming writes, exactly-once commits and upserts into Iceberg v2 tables.

The lakehouse should normally contain at least three logical data models.

Raw CDC events

The raw table is an immutable record of every captured operation.

source_system
source_schema
source_table
primary_key
operation
before_payload
after_payload
source_timestamp
source_version
transaction_id
kafka_partition
kafka_offset
ingested_at

This is the reconstruction layer. If transformation logic changes or a downstream table becomes inconsistent, current-state and historical tables can be rebuilt from the raw CDC history.

Current-state tables

These tables contain the latest known state for every business key. Iceberg v2 supports row-level updates and deletes, making it suitable for durable current-state tables. However, frequent streaming upserts produce delete files and metadata that must be maintained.

Historical version tables

Historical tables preserve each business version as a separate row. They are naturally append-oriented and therefore fit object storage better than continuously updating earlier files.

Modelling SCD Type 2 from CDC

A conventional slowly changing dimension Type 2 table contains:

business_key
dimension_attributes
valid_from
valid_to
is_current
source_version
record_hash

When an attribute changes, the existing row is closed by setting valid_to, and a new current row is inserted. This pattern works well in a transactional warehouse, but performing both operations for every event can generate a large number of Iceberg delete files.

A more lakehouse-friendly approach is to separate the immutable history from the presentation model:

1. Append every dimension version with valid_from and source_version.
2. Preserve delete events rather than silently removing them.
3. Derive valid_to using the next version's valid_from.
4. Materialize a fully prepared SCD Type 2 table on a schedule when downstream tools require physical valid_to and is_current columns.

This design keeps the raw history replayable and reduces continuous modifications to older Parquet files.

For dimensions that must be queried in real time, ClickHouse can maintain a current-state ReplacingMergeTree table while Iceberg preserves the complete version history. The two representations do not need to be identical internally as long as their business definitions and reconciliation rules are consistent.

Trino and ClickHouse serve different queries

Trino and ClickHouse should not be treated as interchangeable components.

Requirement	Preferred query path
Sub-second operational dashboard	ClickHouse native tables
High-volume event aggregation	ClickHouse native tables
Latest entity state	ClickHouse current-state table
Large historical scan	Trino over Iceberg
SCD history analysis	Trino over Iceberg
Joining lakehouse data with PostgreSQL or another platform	Trino federation
Data science and exploratory SQL	Trino over Iceberg
Occasional cold-table access from ClickHouse	ClickHouse Iceberg integration

ClickHouse should normally power dashboards and APIs that have strict latency requirements. Trino should handle large scans, historical analysis and federated joins.

Modern ClickHouse versions can also query Iceberg tables directly. This is useful for occasional hot-and-cold queries, but it does not eliminate the need for native ClickHouse storage when predictable sub-second latency and high dashboard concurrency are required.

Reliability: exactly-once is not the complete answer

Even if an individual sink provides exactly-once commits, the ClickHouse and Iceberg branches do not form a single distributed transaction. One branch may succeed while the other is temporarily unavailable.

The architecture must therefore be designed for replay and reconciliation:

• Use a deterministic event identifier.
• Preserve the source log position.
• Make consumers idempotent or version-aware.
• Retain Kafka data for the recovery window.
• Track processed offsets and source versions.
• Compare record counts and maximum source positions between layers.
• Send invalid or incompatible events to a dead-letter topic.
• Monitor consumer lag independently for ClickHouse and Iceberg.

The ClickHouse Kafka table engine uses at-least-once delivery behavior, so duplicates can occur around failure and retry scenarios. A stable key combined with source_version allows ReplacingMergeTree to converge on the correct latest state.

Iceberg maintenance is part of the architecture

Streaming systems write frequently. Without maintenance, this can create many small Parquet files, delete files, manifests and table snapshots.

A production Iceberg platform needs scheduled jobs for:

• Data-file compaction
• Delete-file compaction
• Manifest rewriting
• Snapshot expiration
• Orphan-file cleanup
• Partition and sort-order optimization
• Catalog and metadata monitoring

Every Iceberg write creates a new snapshot. The Iceberg maintenance documentation recommends regularly expiring old snapshots to keep table metadata and unneeded files under control. Trino also exposes Iceberg procedures such as optimize, expire_snapshots and remove_orphan_files through its Iceberg connector.

Maintenance is not an optional cleanup activity. It is part of the normal operating model of a streaming lakehouse.

Hot and historical retention

The separation between ClickHouse and Iceberg makes tiered retention possible.

Layer	Example retention strategy
Kafka	Several days or enough time for downstream recovery
ClickHouse detailed events	Recent hot window, such as 30–180 days
ClickHouse aggregates	Retain while frequently queried
Iceberg raw CDC	Long-term, according to governance policy
Iceberg current state	Long-term
Iceberg historical and SCD tables	Long-term

The exact numbers depend on query frequency, regulatory requirements and cost. The principle is more important than a fixed duration: recent data remains close to compute, while complete history remains in the open lakehouse.

Other real-time warehouse options

ClickHouse is a strong fit for high-volume, aggregation-heavy analytical workloads, but the hot layer can be replaced when the workload has different requirements.

Platform	Strongest fit
ClickHouse	High-throughput event analytics and low-latency aggregations
SingleStore	Mutable real-time data, SQL transactions, joins and application-serving workloads
StarRocks	Primary-key updates, real-time ingestion and analytical joins
Apache Pinot or Druid	User-facing streaming metrics and time-oriented dashboards

For an organization already operating SingleStore, it can replace ClickHouse as the hot real-time warehouse while Kafka, Iceberg, S3 and Trino keep the same broader roles. The architectural principle remains unchanged: the real-time warehouse is the serving layer, not the only copy of the historical truth.

Final architecture principles

A reliable implementation can be summarized in eight rules:

1. Treat Kafka as a replayable change log, not only a message transport.
2. Use ClickHouse's Kafka engine and materialized views for direct real-time ingestion.
3. Store immutable raw CDC history before depending on derived tables.
4. Use Parquet as the file format and Iceberg as the table format on S3.
5. Use ClickHouse for hot queries and Trino for broad historical and federated queries.
6. Model current state and SCD history separately.
7. Design every consumer for duplicate events, late arrival and replay.
8. Operate compaction, snapshot expiration and reconciliation as first-class platform services.

Conclusion

No single technology needs to solve every part of the architecture.

Kafka provides the durable stream of changes. ClickHouse converts that stream into fast, queryable hot tables through its native Kafka engine and materialized views. Iceberg organizes long-term Parquet data on S3 into reliable analytical tables. Trino opens that historical data to SQL, BI and federation across the broader data platform.

Together, these components provide immediate analytics without sacrificing historical depth, open storage or replayability. That separation is the foundation of a scalable real-time lakehouse.

References

• Debezium architecture
• ClickHouse Kafka table engine
• ClickHouse ReplacingMergeTree
• Apache Iceberg table specification
• Apache Iceberg Flink writes
• Apache Iceberg maintenance
• Trino Iceberg connector