u/Novel-Information776

Hey there! We have Postgres, already a replica of the operational transactional Postgres, and also ClickHouse. We are treating the replica Postgres as our analytics dwh and are running dbt in it. And our BI layer is connected to it.
We have events data stored in ClickHouse, but it is not in use at the moment.
Moving forward, what is my best long-term solution? I need to bring in the events data into our analytics dwh, so it becomes a natural decision point if we want to continuously commit to Postgres, or move analytics work and dbt over to ClickHouse, or explore other possibilities. We only want self-hosting options.
Thanks!

Hey there! A bit of background on myself first: I have been working in the field of data and analytics for over 7 years, but started as an analyst, and then gradually transitioned into an analytics engineer and now a data engineer. I don't have much hands-on knowledge and experience in building the data infra. I have mostly worked with BigQuery, dbt, and data ingestion tools like Fivetran.

I just started in a new company and there is a need for me to rebuild the data infra. I am the only data engineer in a medium-sized company. The company is self-hosting, and they are very determined on that. We use Postgres for operational transactional data, and then we have a replica of that, also a Postgres dwh, for analytics usage. We also have ClickHouse which currently only stores events data and is not being consumed.

After some researching and reading, I wonder if the below architecture will be a solid setup moving forward? Any better ideas? I really appreciate any of your help and advice! We do not want to move to any cloud-based managed data warehouse. We also do not want any third-parties to read directly into our Postgres nor our replica Postgres.

Thank you very much!

Postgres ──┐

├──→ DuckDB server (dedicated machine, NVMe)

ClickHouse ──┘ │

dbt runs here

Quack serves here

│

BI tool (read-only, modelled data only)

Hey there! A bit of background on myself first: I have been working in the field of data and analytics for over 7 years, but started as an analyst, and then gradually transitioned into an analytics engineer and now a data engineer. I don't have much hands-on knowledge and experience in building the data infra. I have mostly worked with BigQuery, dbt, and data ingestion tools like Fivetran.

I just started in a new company and there is a need for me to rebuild the data infra. I am the only data engineer in a medium-sized company. The company is self-hosting, and they are very determined on that. We use Postgres for operational transactional data, and then we have a replica of that, also a Postgres dwh, for analytics usage. We also have ClickHouse which currently only stores events data and is not being consumed.

After some researching and reading, I wonder if the below architecture will be a solid setup moving forward? Any better ideas? I really appreciate any of your help and advice! We do not want to move to any cloud-based managed data warehouse. We also do not want any third-parties to read directly into our Postgres nor our replica Postgres.

Thank you very much!

Postgres ──┐

├──→ DuckDB server (dedicated machine, NVMe)

ClickHouse ──┘ │

dbt runs here

Quack serves here

│

BI tool (read-only, modelled data only)