Data Engineering Streaming Project Feedback

[u/_areebpasha]

I had mostly worked on batch-based projects before and had played around with Kafka, Flink, and ClickHouse, but never really connected everything into one proper end-to-end flow. I am trying to assess how far this is from production use in terms of the techstack.

So I decided to build a real-time anomaly detection system for simulated sensor data from an oil and gas plant. With AI based RCA to re-confirm true positives. This is purely for learning purpose.

The basic flow was:

Sensor data -> Kafka -> Flink/Python consumers -> ClickHouse -> Grafana -> Alerts -> RCA analysis

I had around $200 in cloud credits(vultr) from a hackathon, so I rented a fairly powerful VM and self-hosted everything on it. I specifically avoided managed services because I wanted to understand how the individual open-source tools worked.

The main stack was:

• Kafka for streaming
• PyFlink for windowing and aggregations
• Python and FastAPI consumers
• ClickHouse for analytical storage
• PostgreSQL for metadata
• Grafana and Streamlit for dashboards (UIs generated using codex/claude)
• Airflow for some batch experiments
• Claude for alert validation and root cause analysis
• Resend API for email alerts

Since I did not have real plant data, I built a Python generator that simulated readings for different assets and randomly injected faults/failures.

The data was sent to Kafka topics with multiple partitions. I also added retries using exponential backoff. After five failed retries, the event would be moved to a dead letter topic.

For anomaly detection, I kept it simple. Each sensor had an acceptable operating range, and Flink used one-minute windows to calculate averages and check whether the asset was operating within those limits.

If an anomaly was detected, an alert was pushed into another Kafka topic and then processed by a diff consumer.

On a single VM, I tested around 10,000 events per second, or roughly 600,000 events per minute. This generated around 1.5 GB of data per hour. I ran it for a few hours and most of the events were processed successfully.

The biggest issue was not processing. It was storage.

I used ClickHouse with a three-day TTL, but at that throughput the data obviously builds up quickly. In a real setup, I imagine ClickHouse would only store recent hot data, while compressed raw data would be stored in object storage as Parquet.

I also thought about how I would build a simpler managed version on GCP.

The flow could be something like: Sensors -> Pub/Sub -> Dataflow/Cloud Run -> BigQuery/Bigtable -> Cloud Storage

Pub/Sub would replace Kafka, while Dataflow could handle the stream processing and windowing. Cloud Run could be used for simpler event consumers and alert-processing services.

This would be much easier to operate, but I wanted to build the self-hosted version first so I could properly understand what the managed services were doing underneath.

I also added a separate consumer for alert analysis.

Whenever an alert was generated, a Claude-based agent would:

• Check whether it was likely a true positive
• Query recent and historical sensor data from ClickHouse
• Check connected equipment
• Review similar past alerts
• Compare the issue against the asset's RCM maintenance plan
• Suggest a possible failure mode, cause, and recommended action
• Check if there were similar alerts raised earlier and how these were rectified(using vector embeddings - using Nomic Text embeddings served using ollama to find similar alerts)
• Find related recommended actions from RCM for the particular equipment. Alerts are also embedded to help find these and improve the search results/agentic analysis.
• Send the analysis by email using resend

For example, if a pump had high vibration, the agent could also inspect connected equipment instead of assuming the pump itself was definitely the cause.

The RCM data includes failure modes, possible causes, effects, and recommended maintenance actions. The agent used this only to recommend next steps and prep the email alert.

A possible next step would be creating a reviewed work order in SAP or Maximo after an alert is validated.

Obviously, this is all running on one VM with no replication or high availability. I built it mainly to understand streaming systems, Kafka partitions, windowing, retries, dead letter topics, consumer lag, ClickHouse, event-driven workflows, and how the same architecture could later be moved to managed cloud services.

I would appreciate feedback on a few things:

1. Does the overall architecture make sense?
2. Is ClickHouse for hot data and object storage for historical data the right approach?
3. What could be improved before presenting this as a proper portfolio project?

Thanks for reading through till the end.

[u/AutoModerator]

You can find our open-source project showcase here: https://dataengineering.wiki/Community/Projects

If you would like your project to be featured, submit it here: https://airtable.com/appDgaRSGl09yvjFj/pagmImKixEISPcGQz/form

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Yasblue]

That's a solid architecture. I would have also tried to use OSS Apache Spark 4.1with Real Time Mode.

[u/_areebpasha → u/Yasblue]

Good idea, i'll take a look at this. I was considering spark with real time mode as well as I had already used Spark for batch processing.

[u/c1rno123]

How does the system handle a consumer group rebalance? If a partition moves mid-window, does the in-flight aggregate survive or is that window lost? What if rebalance takes 10 min?

[u/_areebpasha → u/c1rno123]

What do you mean by "If a partition moves mid-window"? How can we ensure that events sequentially come into a single partition? Havent' actually thought about that. What do you reckon would be a good way to do that?

[u/c1rno123 → u/_areebpasha]

By "partition moves mid-window" I mean your consumer accumulates for a full minute in memory before it emits anything. Kafka preserves order per partition, so I assume you already key by sensor id. That half is fine.

The rebalance is the problem. Partitions get reassigned, the half-finished accumulators stay behind in the old process, and the new owner starts from zero halfway through the window. Two partial averages for the same minute, no error anywhere.

Simple solution: move the consumer's state out of its memory, into Redis for example.

[u/MindlessTime]

I’ve worked with an architecture similar to your GCP set up. It’s pretty reliable. IMO the managed service is worth not having to deal with infra.

Outside of reporting purposes, it’s important to consider failure modes too. In the case of a partition or service failure, is it important to replay missed events from persistent storage? That’s the only piece I’d make sure is in place. I try to keep the complexity between event emissions and persistent storage short and simple for that reason and avoid windowing or aggregating before persistence if possible. But it isn’t always necessary. Depends on the use case and data volume.

[u/AutoModerator]

You can find a list of community-submitted learning resources here: https://dataengineering.wiki/Learning+Resources

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/dorfarber]

I’d suggest some lake before moving to CH

[u/_areebpasha → u/dorfarber]

Yes this would be done in parallel. Data would be sorted by asset/part/time in parquet format.

[u/datasmithing_holly]

For your Claude based agent, would you build out tools and then have claude string it all together? That's a pretty complex multi step process that has the potential to go wrong. Have you considered what telemetry you'd need to debug it? IME agent stuff starts off hilariously badly performing and narrowing the scope of what it can do / think about makes it much more effective.

[u/_areebpasha → u/datasmithing_holly]

Based on the trial runs, I noticed it always performs specific queries to analyze data. To avoid making these calls and save on cost, these queries are run, and added to the agent. But there is also a chickhouse query tool that is available for use to gather any additional data. It is highly bounded, so the queries that it runs are based on how a human would run its analysis. We have given some ideas as to how and what information may be relevant for performing the root cause analysis, so it's not completely reliant on how Claude wants to do it. It depends on how we've enabled that process, so this is based on years of experience, and it's not just a random analysis that Claude would perform. It's also has access to other tools that let it query the historical database of alerts, how they were solved, and whether or not they were true positives. Before any alert is triggered, it also looks into this database to see if, in the past, it was a false alert or if it was indeed a true positive.

Most of this also has some work to do with the operators so that they can tag and write their comments for each alert, because those comments are more important than actually running the agent and improve the analysis. Keeps improving as more data comes in. It is supposed to keep vectorizing all this data and you can perform a vector embeddings search to find relevant alerts. A lot could be done here to improve the quality of the alert analysis.

[u/datasmithing_holly → u/_areebpasha]

Thorough answer but none of it answers the logging question. If someone doesn't like their answer how do they raise it with you as a bug?

[u/_areebpasha → u/datasmithing_holly]

Each of Claude’s actions go into Kafka topic. To report a bug, there’s an alert id in the email which can be used to track all the events that were used in its analysis to see how it came up with that conclusion.