I’m interested in understanding how people are building the data pipeline/ETL/CDC layer between Frappe/MariaDB and ClickHouse when the dataset becomes large.
For example:
Are you using CDC, periodic batch jobs, queues, Kafka, or something else?
How do you handle initial data migration/backfilling when there are millions or billions of rows?
How do you handle incremental updates, deletes, and schema changes?
Where do you perform transformations/denormalization — before ClickHouse or inside ClickHouse?
How are you handling batching, retries, duplicate records, and failed jobs?
What tools/architecture have worked well for you in production?
I’d especially appreciate examples from anyone running this at large scale or near-real-time, and any lessons learned from building and operating the pipeline.
Instead of batch jobs or scheduled ETL, we went with log-based CDC — basically letting the changes flow out of MariaDB in near real-time as they happen, rather than polling the database.
The setup is Debezium → Kafka (we used Redpanda) → ClickHouse. Debezium has a native MariaDB connector that reads MariaDB’s binlog and streams every insert/update/delete into Kafka, and a ClickHouse sink connector loads it in. Since Frappe stores each DocType as a tab<DocType> table, you just point it at the few tables you actually report on.
A couple of things that made it painless: the connector can do the initial backfill of existing data and then switch to live streaming automatically — no separate migration step. And on the ClickHouse side, using a ReplacingMergeTree table means updates just collapse to the latest version on their own, so you don’t have to manage dedup yourself. Kafka in the middle handles retries and replay if anything goes down.
we’ve only tried this on a trial setup so far, not full production, but it worked out really well and was surprisingly easy to stand up.