> On the clickhouse query side, we end up wanting denormalized data for query performance and to avoid JOINs. It's frequently not a great idea to query in clickhouse using the same table structure as you're using in your transactional db.
> In our experience we sync a few tables with PeerDB but mostly end up using app-level custom code to sync denormalized data into Clickhouse for our core use-cases.
Have you explored dbt? You may find that using custom code is not scalable, and that dbt solves this exact problem.
This is exactly a use case a streaming processing like Timeplus excels, which help users do 20s streaming to dynamic table joins (imagine the hand side tables are dynamically updates) to denormalize data before the data lands in ClickHouse. This helps a lot for warehouse like ClickHouse to save the joins in them. Disclaimer, I am one of the Timepluer core engine engineer.
> In our experience we sync a few tables with PeerDB but mostly end up using app-level custom code to sync denormalized data into Clickhouse for our core use-cases.
Have you explored dbt? You may find that using custom code is not scalable, and that dbt solves this exact problem.