Context
An analytical storage decision should begin with the questions the system needs to answer. Long lookbacks, repeated aggregations and investigative queries have different requirements from transactional workloads. Evaluate a columnar store against those requirements, with a clear modelling approach and an explicit plan for data quality.
Practice
- Consider change data capture into ClickHouse while keeping Postgres for transactional integrity.
- Model analytical features with dbt and orchestrate refreshes through Airflow so analysis uses consistent definitions.
- Pair implementation with query templates, documentation for materialised views and agreed service expectations.
Takeaways
- Tie the storage choice to clear investigative questions and representative workloads.
- Treat documentation, training and observability as part of the analytical system.