Analytics Data Platform

Summary

An OLAP-oriented analytics architecture that ingests operational changes from PostgreSQL via WAL-based CDC into Kafka, then routes them to a columnar analytics store (ClickHouse or Snowflake) for product analytics, business intelligence, and operational reporting. The CQRS separation ensures analytical queries never degrade transactional write performance, and materialized views provide pre-aggregated query acceleration for the most expensive analytical patterns.

Problem Statement

Analytical queries (large scans, GROUP BY aggregations, time-range rollups) impose fundamentally different access patterns than OLTP writes. Running both on the same PostgreSQL primary leads to lock contention, I/O competition, and unpredictable query latency. As analytics query complexity grows, the only scalable solution is to route analytical workloads to a dedicated columnar store that is optimised for scan throughput, not point-lookup latency. Ingestion must be streaming (not batch ETL) to meet sub-minute freshness requirements at scale.