Why real-time analytics on an open source lakehouse needs its own design
A lakehouse built for nightly batch loads cannot serve dashboards or customer-facing products that need second-level freshness without a design change. The answer to the page question is that one source of truth is achievable with a streaming path that writes every event to a durable record, usually Apache Kafka feeding Apache Iceberg, while a real-time OLAP store such as ClickHouse, Druid or Pinot serves the fast queries. Which serving store fits depends on query shape and concurrency: high-concurrency user-facing queries with many filters, upserts or joins point to different engines than wide ad-hoc aggregations run by analysts.
Freshness changes the architecture because streams deliver events late, out of order and sometimes twice. The serving store needs a deliberate duplicate strategy, such as Pinot upserts keyed on a primary key or ClickHouse deduplication at the table level, and the team must decide which copy is authoritative when the Kafka replay and the Iceberg batch disagree. Restore testing also changes. ClickHouse documentation notes that replication does not protect against human error or corruption and that a backup never test-restored will probably not restore properly, so the team should schedule restores of both the serving cluster and the Iceberg tables on a spare environment and confirm the recovered counts match.
Confirm early which ClickHouse release line you will run, because the ClickHouse security policy lists only certain versions as supported, and confirm the Keeper, ZooKeeper or Helix footprint each engine needs. A short platform answer for most teams is ClickHouse, Druid or Pinot for serving, with Kafka and Iceberg as the durable record that can rebuild the serving layer. Ask your partner to show the duplicate rule, the replay procedure and a passing restore test before go-live.
Which requirements shape a real-time lakehouse build
This table lists the requirements that decide how a second-level analytics build is designed, with the source that constrains each one and what it means for the build. Read it before choosing an ingestion path or serving store, because several rows, especially upserts, single source of truth and restore testing, change the architecture rather than just the configuration.
| Requirement | Constraint and source | What it means for the build |
|---|---|---|
| Freshness target | Druid and Pinot both read streaming data directly from Kafka through supervisors or real-time tables; batch loads use separate paths | Define the freshness target per dataset in writing. Streaming into a serving store and committing to Iceberg are separate paths with different latencies, so confirm each one in a proof of concept on your own data. |
| Event volume and ordering | Pinot reports millions of events per second at LinkedIn, a figure from its own README and not a sizing guide for your cluster | Measure your peak events per second and key cardinality. Ask the partner to prove ordering assumptions per Kafka partition before designing joins or deduplication. |
| Upserts and late-arriving data | Pinot lists upserts during real-time ingestion; ClickHouse uses ReplicatedMergeTree tables, which need a different merge and deduplication design | Decide which store owns the latest state of a record. Test late events against the chosen model, since upsert support and merge behavior differ by engine. |
| One source of truth between lake and serving store | Druid uses deep storage for segments, Pinot pushes batch-built segments to a deep store, and ClickHouse keeps its own replicated tables | Choose one system of record, such as the Iceberg tables, and rebuild serving tables from it. Two independently written copies will drift unless a reconciliation job checks them. |
| Retention and deletion | ClickHouse BACKUP and RESTORE cover tables, partitions and databases, and replication alone does not protect against human error or corruption | Set retention per table in both the lake and the serving store. Deletes must reach every copy, including backups, so document how erasure requests propagate. |
| Access control per dataset | ClickHouse supports LDAP, Kerberos, X.509 certificates, an HTTP authenticator and JWT as external authenticators, and access control is configured per user and role | Map each dataset to a role in the identity system and confirm the serving store enforces it, not only the dashboard layer. Test that each role sees only its allowed rows. |
| AI features consuming the data | EU AI Act Article 12(1) requires automatic event logging for high-risk systems, and Article 19(1) requires keeping those logs for at least six months unless other law provides otherwise | If a model or agent reads real-time data to make decisions, log the inputs and outputs with timestamps and retain them. Confirm with counsel whether the system is high-risk and which other laws apply. |
| Backup and restore testing | ClickHouse documentation warns that a backup never test-restored will probably not restore properly and recommends regular restore tests on spare clusters | Schedule restore tests against a spare cluster and record the results as evidence. Ask the partner to show a completed restore, not only a backup job log. |
Which open source engines fit which real-time pattern
Real-time analytics on a lakehouse usually splits into two paths: a serving engine that answers queries in seconds, and the Iceberg lake that keeps the durable, reproducible record. The cards below cover the engines and batch tools that typically sit around that split, with the decisions and checks each one raises for your environment.
ClickHouse for low-latency serving
ClickHouse is an Apache-2.0 server that suits dashboards and product features that need fast aggregations over fresh rows. Replicated tables are coordinated by ClickHouse Keeper, which the replication guide recommends running on at least three nodes on dedicated hosts in production. A recovered replica resyncs from a healthy peer, but replication does not protect against human error, so BACKUP and RESTORE must be tested on a spare cluster. Confirm how LDAP, Kerberos or JWT authenticators map to your existing identity groups.
Apache Druid for streaming ingestion
Druid reads Kafka topics directly through supervisors, which makes it a fit when ingestion lag and segment handoff matter more than joins across large tables. Its Master, Query and Data server roles (Coordinator, Overlord, Broker, Router, Historical and Middle Manager) add operational surface area. Druid also depends on deep storage, a metadata store such as PostgreSQL or MySQL, and ZooKeeper. Confirm who will run those three dependencies and how their backups are scheduled.
Apache Pinot for user-facing analytics
Pinot ingests from Kafka, Pulsar or Kinesis into real-time tables, supports upserts for keeping the latest state per key, and can combine batch and stream sources in one table. That combination helps when a product view must blend historical Parquet files with live events. Pinot services require JDK 25 or later, so confirm that your base images, patching cycle and security scanning support that runtime. Also confirm how Helix and ZooKeeper state will be backed up.
Apache Airflow and Dagster for backfill
Real-time paths rarely stay correct on their own. Late events, consumer outages and schema fixes need batch jobs that reload a time window from the Iceberg lake into the serving store and reconcile row counts. Airflow (Apache-2.0) and Dagster (Apache-2.0) both schedule and monitor these jobs. Choose one orchestrator per team, define idempotent backfill tasks, and confirm that reconciliation alerts reach the people who own the data.
Apache Superset for dashboards
Superset is an Apache-2.0 visualization tool that connects to ClickHouse, Druid and Pinot through their SQL interfaces, so dashboard definitions can stay independent of the serving engine. Row-level security rules should be defined in Superset and tested against each engine, since the engines enforce permissions differently. Confirm that dashboards are reading from the intended freshness tier and that cached query results do not hide new data from operators.
Iceberg lake as the durable record
In this pattern the Iceberg lake on self-hosted object storage holds every event in open table format, and the serving engine is a rebuildable projection of it. Keeping one source of truth means every real-time write also lands in the lake, and every serving table can be rebuilt from it. Confirm the catalog, compaction schedule and snapshot retention with the team that owns the lakehouse, and decide which engine is authoritative when the two disagree.
Deployment patterns for Kafka, the lake and the serving store
In this pattern, a real-time OLAP engine such as ClickHouse, Apache Druid or Apache Pinot consumes events straight from Kafka and serves dashboards and product queries from its own storage. It is the shortest path to second-level freshness and needs the fewest moving parts. The engine itself becomes the place where recent history is held, so its storage and replication design carry the weight of the record.
- Integration points: Druid reads Kafka through supervisors, and Pinot real-time tables ingest from Kafka (Pulsar and Kinesis are also supported sources). ClickHouse typically reads through its Kafka integration or a pipeline you operate. Confirm the exact ingestion mechanism and version with the vendor for your chosen engine.
- Failure modes: Consumer lag grows during broker or engine outages, and replay after a long outage can be slow or duplicate rows if offsets and upserts are not designed carefully. Schema changes in producers can break ingestion silently.
- Replication: ClickHouse ReplicatedMergeTree tables use ClickHouse Keeper, which the replication guide recommends running as at least three nodes on dedicated hosts in production. Replication protects against node loss, not against bad writes or deletes, which replicate too.
- What must be backed up: The engine's tables and metadata, the Kafka topic retention window that allows replay, and the ingestion configuration. Test restores on a spare cluster, since an untested backup will probably not restore when needed.
Before committing, confirm how long Kafka retention must cover a full rebuild and whether the engine can be re-ingested from scratch within the recovery time you need.
In this pattern, the Iceberg or Delta tables on object storage hold the record. Streams land in the lake first, transformations and deduplication run there, and the real-time OLAP store is loaded as a serving layer that can be dropped and rebuilt from the tables. The lake answers audit, reprocessing and long-history questions, while the OLAP store answers the fast queries. Any disagreement between the two is resolved in favor of the lake.
- Integration points: A landing path writes Kafka events into the lake, then batch or micro-batch jobs feed the serving store. Druid supports batch loads through native batch tasks and SQL INSERT and REPLACE, and Pinot offline tables load Parquet, Avro or CSV from object storage, and can combine offline and real-time data in one table.
- Failure modes: The serving store can lag the lake, so define the freshness contract per dashboard. Small files and unmaintained tables slow both layers. A rebuild that is never rehearsed may take longer than the business accepts.
- Rebuild path: Keep a documented, repeatable job that reloads the serving store from a chosen snapshot or partition range. Pinot pushes batch-built segments to a deep store that servers download from, which makes that store a critical dependency.
- What must be backed up: The lake storage and catalog metadata are the primary backup scope. The serving store's configuration and load jobs must be versioned so it can be rebuilt, though its data is derivable.
Confirm which catalog and deep storage you will run, and who owns table maintenance, before choosing the rebuild cadence.
In the dual-write pattern, the application or a consumer writes each event to both the lake and the OLAP store as two separate operations. It looks simple because both copies update quickly, but neither write is the record, and nothing guarantees they agree. This is the pattern that breaks the single source of truth, and it is worth naming in design reviews so it is not reintroduced under a different name.
- Why it diverges: One write can succeed while the other fails or is retried. Retries without idempotent keys create duplicates, and partial failures leave rows present in one store and missing in the other.
- Failure modes: Reports disagree depending on which store is queried, reconciliation is manual, and the bug surfaces only when someone compares totals. Auditors cannot tell which number is authoritative.
- Hidden cost: Teams add reconciliation jobs, custom repair scripts and per-store fixes, which become undocumented operational work owned by no one.
- What must be backed up: Both stores must be backed up, and their backups will not be mutually consistent with each other, which complicates restores and incident analysis.
- Safer alternative: Route all writes through one ingestion path, record the lake as the record, and treat the OLAP store as derived. The lake-first pattern is the usual remedy.
If a current design writes to two stores, ask the team to show which store is authoritative for each metric and how a mismatch is detected and resolved.
How a real-time analytics rollout moves from one dataset to production
The sequence takes one dataset from a freshness and query requirement to a monitored production pipeline, with evidence at each gate so the team can show that the lake and the serving store agree and that the data can be restored.
Scope freshness and query requirements per dataset
Start with one dataset that has a named consumer, such as an operational dashboard or an embedded product view. Record the freshness target, the query shapes and concurrency, the expected event volume, and the retention period for each dataset. Agree which system is the source of truth and which store serves queries. The output is a signed requirements sheet that the engineering and business owners both accept, and it is done when every query in the list has an owner and a latency expectation.
Build a pre-production environment with representative data
Stand up a non-production cluster in the client's own infrastructure with a representative copy of the event stream and the query workload, including late and out-of-order events and peak-hour volumes. Run the agreed queries against it and capture results and timings. Confirm the server version and its support status before you choose it, using the ClickHouse production guidance on stable releases and pre-production testing. It is done when the team has a repeatable test run and a documented result for each query.
Test ingestion and duplicate handling
Connect Kafka topics to the serving store (for example, a ClickHouse table or a Pinot real-time table with upserts, or a Druid supervisor) and replay known event sets. Inject duplicate messages, consumer restarts and delayed partitions. Check that the pipeline yields the expected row counts and that deduplication or upsert keys behave as designed. The output is an ingestion test report with pass or fail results per scenario, and the step closes only when duplicates are either eliminated or counted and explained.
Reconcile the lake and the serving store
Compare the serving store with the lakehouse tables that hold the same events, by partition and time window, on row counts, key totals and sampled records. Schedule the reconciliation job so drift is caught after launch, not only during testing. Record the tolerance for lag between the two stores. This step is done when reconciliation runs without manual steps and each mismatch has a root cause and a fix, so the team can state which store is authoritative for a given question.
Configure access control and authentication
Define which roles can read which tables and columns, then wire the serving store to the directory the organization already uses. ClickHouse supports LDAP, Kerberos, SSL X.509 certificates, an HTTP authenticator and JWT authentication as external authenticators, so confirm which of these match your identity setup before you build. Test that service accounts for the ingestion job have only write rights to their tables. The output is an access matrix and a test log showing that denied queries are denied.
Prove backup and restore on a spare cluster
Configure scheduled full and incremental backups with BACKUP to the storage target the client has chosen, such as local disk, S3-compatible storage or Azure Blob Storage. Then restore the backup to a spare cluster and run the reconciliation checks against the restored data. Replication alone does not cover human error or corruption, and the ClickHouse documentation warns that an untested backup will probably not restore when needed. The step is done when the restore has completed and the results match the source within the agreed tolerance, with timings recorded.
Promote to production and set the upgrade process
Move the validated configuration into production with monitoring on ingestion lag, replica health and query latency. Set an upgrade rule before go-live: use stable releases for most production systems, or an LTS line where the organization prefers longer support windows. Check the ClickHouse security policy for the currently supported versions and rehearse each upgrade in pre-production, reading the changelog for backward-incompatible changes. The output is a runbook, a named owner for each upgrade decision, and a sign-off record for the first dataset in production.
What auditors and regulators ask to see for live analytics data
This table maps the evidence an assessor typically asks for on a real-time analytics stack to the component that produces it. Read it as a checklist to agree with your auditor before the assessment starts, not as a list of certifications. DeployOpen prepares this evidence, but independent auditors and certification bodies decide whether controls are adequate.
| Evidence item | Producing component | What the assessor checks | Common gap |
|---|---|---|---|
| Architecture and data flow diagrams | Design documents for Kafka, the streaming ingestion path, ClickHouse, Druid or Pinot, and the Iceberg or object storage layer | Every hop where live data is copied, transformed or cached, and which copy is the source of truth | Diagrams show the dashboard store but not the Kafka topics or replay paths that feed it |
| Access control configuration and role grants | ClickHouse access control (users, roles, row policies) or equivalent grants in Druid and Pinot, plus the catalog permissions on the lakehouse | Who can read raw events, who can read aggregates, and whether grants match the approved access matrix | Roles created for a project and never reviewed, or a shared service account with broad read rights |
| Authentication logs | ClickHouse server logs for LDAP, Kerberos, X.509 or JWT logins, plus identity provider logs for upstream SSO | Each login is attributable to a person or service identity, and failed attempts are visible | Analytics users authenticate with local passwords, so no central record exists |
| Backup and restore test records | ClickHouse BACKUP and RESTORE jobs to S3-compatible or local storage, plus the restore test log kept by the operations team | Backups exist, cover the required tables, and have been restored on a spare cluster with a recorded result | Backups run on schedule, but no restore has ever been tested; replication alone is not counted as protection |
| Version and patch records against the vendor support policy | Change records for ClickHouse upgrades, checked against the ClickHouse security policy and the production FAQ guidance | Running versions are on the list of supported releases, and each upgrade was tested in a pre-production environment with the changelog reviewed | Clusters run a version that is no longer supported, with no documented exception or compensating control |
| Replication and Keeper health records | ClickHouse Keeper node status and replica sync logs for ReplicatedMergeTree tables | Keeper runs with the recommended minimum of three nodes on dedicated hosts in production, and replica lag is monitored | Keeper co-located with data nodes, or a single Keeper node in production |
| Streaming ingestion and offset records | Kafka consumer group offsets, Druid supervisor or Pinot real-time table status, and Iceberg commit history | Data loss and duplication are controlled, and the mapping from event to stored row can be traced | No record of which offsets were loaded, so a replay can double count events |
| Log retention for AI systems (where applicable) | Logging in any AI feature that uses the analytics data, retained by the AI gateway or observability layer | Logs are generated automatically and kept for at least six months under Article 19(1) of the EU AI Act, unless other law sets a longer period; Article 12(1) requires technical support for automatic event logs | AI features are added to dashboards later, but their logs are not included in the retention schedule |
What do buyers ask before real-time analytics work starts?
How much does a real-time analytics platform cost to run?
Cost depends mainly on the infrastructure you run, the people who operate it, and the implementation scope agreed in writing, and no single figure applies to every estate. The recurring components are compute and storage for the serving engine, the streaming layer that feeds it, and the staff time spent on upgrades, monitoring and on-call. The one-time components are design, build, integration and the first restore test. Event volume, query concurrency and retention period push each of these up, because more events mean more ingestion capacity, more concurrent queries mean more serving nodes, and longer retention means more deep storage. Ask any partner for a quote that splits these lines out, names who pays for each (your cloud account, your staff or a retainer) and states the sizing assumptions for events per second, peak concurrent queries and retention. DeployOpen does not publish prices, and a quote follows from the written scope.
How long does a real-time analytics implementation take?
The timeline depends on how many sources you connect, how much transformation the data needs, and how many dashboards or product features consume it, so confirm the duration in the written scope instead of assuming one. The work usually moves through sizing, a pilot on representative data, production hardening, and handover. Teams with a working Kafka cluster, agreed event schemas and a clear freshness target move faster than teams that still need to define those things. Pilot timing is also set by how quickly you can provide sample streams and access to the environments that will run the cluster. Ask your partner to list the dependencies it expects from your side, such as network access, identity integration and schema owners, and to tie each milestone to one of them.
What does the client team need to provide for the work?
Expect to supply a named owner for each source system, access to the Kafka topics or other streams, agreed event schemas, and the infrastructure the platform will run on. Your side should also define the freshness target in seconds, the query patterns the business needs, and the retention periods for raw and aggregated data. Security and compliance staff need to approve the identity model, network paths and where backups are stored. If these inputs arrive late, the schedule moves, so ask the partner for a written responsibility matrix before the engagement starts. A well-run pilot usually depends more on the availability of these people than on the engine itself.
Can our in-house team run a real-time analytics platform?
Yes, if the team already operates distributed data systems and has the capacity for on-call work, but the operating load is real and should be planned for. Running a cluster means monitoring ingestion lag, handling node failures, applying version upgrades and keeping backups tested. ClickHouse, for example, publishes a production FAQ that recommends a pre-production environment with representative data and reading the changelog before each upgrade, and it lists which versions receive security updates. Apache Pinot depends on JDK 25 or later and on Apache Helix with ZooKeeper, and Druid depends on deep storage, a metadata store and ZooKeeper. Each of these is another component your team must patch and monitor. A partner can run the platform inside your infrastructure, or train your team to run it, and the scope should say which.
How do we choose between ClickHouse, Druid and Pinot?
Choose by the workload and the skills you already have, not by brand. All three are open source (ClickHouse and Pinot are under Apache-2.0, and Druid is an Apache project under Apache License 2.0), and all three can serve low-latency queries over streaming data. Druid reads Kafka and Kinesis directly through supervisors and ships a built-in web console, which suits operational dashboards that need high concurrency. Pinot ingests Kafka, Pulsar and Kinesis into real-time tables, supports upserts, and can combine batch and streaming sources in one table, which suits user-facing products that need upserts and joins. ClickHouse uses ReplicatedMergeTree coordinated by ClickHouse Keeper and offers strong general-purpose SQL analytics, which suits teams that want one engine for ad hoc and streaming analysis. Run a proof of concept with your own event volume, your concurrency and your most demanding queries before you decide.
How do we prove a restore test was done?
Keep a dated record of each restore test that shows which backup was restored, where it was restored, how long the restore took, and whether row counts and key queries matched the source. ClickHouse BACKUP and RESTORE support full and incremental backups to local disk, S3-compatible storage or Azure Blob Storage, and its documentation notes that replication alone does not protect against human error or corruption. The same documentation warns that a backup that has never been test-restored will probably not restore properly, and it recommends regular restore tests on spare clusters. Store the restore logs, the reconciliation queries and the sign-off in your evidence repository, and make the restore test a scheduled control rather than a one-time event. Ask your partner which evidence it will hand over at the end of the engagement.
What decides how much event volume and retention the platform can handle?
Three inputs decide capacity: the rate of events arriving per second, the number of queries running at peak, and how long raw and aggregated data stays queryable. Each one drives a different part of the bill. Higher ingest rates need more streaming partitions and more ingestion capacity, higher query concurrency needs more serving nodes, and longer retention needs more storage, which for Druid and Iceberg-backed designs usually means deep or object storage. Pinot's own documentation describes deployments at LinkedIn ingesting millions of events per second, but your sizing will differ, so use a load test built from your own traffic. Ask any partner to state its sizing assumptions in the quote so you can change them when your volumes change.
What to ask an implementation partner before signing
Start with the source of truth. Ask the partner to show, in a diagram you can keep, which store owns each fact: the Iceberg table as the durable record, or the OLAP store (ClickHouse, Druid or Pinot) as a serving copy rebuilt from it. Ask how a bad record is corrected in both places, who approves a backfill, and how a dashboard number is traced back to a file in object storage. If the answer is "the two stores stay in sync," ask for the reconciliation job, its schedule and the alert it raises when row counts or sums diverge.
Next, ask how duplicate and late events are handled. Kafka delivers at least once in common configurations, so the partner should name the deduplication key, the window for late arrivals, and where that logic lives: an upsert key in Pinot, a ReplacingMergeTree or deduplication step in ClickHouse, or a supervisor setting in Druid. Ask what happens to a late event that arrives after its partition was already published, and whether the team can replay a time range without double counting.
Ask for restore tests and the people behind them. The ClickHouse backup documentation warns that a backup never test-restored will probably not restore, so request the restore test plan, the spare cluster it runs on, the last date it ran, and the measured restore time. Then ask who operates ClickHouse Keeper (at least three nodes on dedicated hosts in production, per the replication guide), the Kafka brokers and the Druid or Pinot ZooKeeper, and who is paged at 2 a.m. Also confirm which version lines the partner will patch. ClickHouse lists 26.9, 26.8, 26.7 and 26.3 as supported, so ask how upgrades are timed against that policy and who pays for the pre-production environment the production FAQ recommends. Finally, ask the partner to list what the written scope excludes, such as Kafka topic design owned by your platform team, dashboard content, source system changes, or query tuning after go-live.
How DeployOpen scopes and supports real-time analytics
DeployOpen plans, implements, integrates, hardens, documents and supports self-hosted open-source analytics platforms inside the client's own cloud accounts, data centers or air-gapped networks. For real-time analytics, the work starts with agreeing freshness targets, the Kafka topics in scope and which store serves which queries, before any cluster is built.
How we scope
- Written scope lists the Kafka topics, event volumes and freshness targets each dashboard or product needs.
- Scope defines which store serves which queries: Iceberg tables, ClickHouse, Druid or Pinot, with the reasons recorded.
- Engagements are fixed-scope, with deliverables and timelines agreed in writing before any work begins.
How we build
- Ingestion pipelines are built and tested against representative event data, including late and duplicate events.
- Replication, Keeper or ZooKeeper quorum, authentication and backups are configured to the client's own standards.
- Performance tests run on representative queries and concurrency before any dashboard or product depends on the platform.
How we hand over
- Handover covers runbooks for ingestion lag, failed consumers, replica recovery and restore tests on a spare cluster.
- Documentation records the single source of truth design, table definitions, retention rules and data lineage decisions.
- Client operators are trained on monitoring, upgrade procedures and the changelog review that precedes each version change.
How we support
- Ongoing support, upgrades and continuous testing are available as retainers, scoped separately from the build.
- Version upgrades are planned against vendor support windows and tested in a pre-production environment first.
- Periodic restore tests and security testing of ingestion endpoints and query interfaces can be included in the retainer scope.
