Analytics Data Platform
Near-real-time analytics pipeline using Debezium CDC from PostgreSQL OLTP through Kafka, Flink stream processing, into ClickHouse for operational queries and Snowflake for business intelligence. Zero direct OLTP load for analytics workloads.
Description
The analytics data platform solves the OLTP/OLAP impedance mismatch: complex analytics queries run on dedicated analytical stores, not on the PostgreSQL primary that serves production traffic. The pipeline is CDC-driven: every INSERT, UPDATE, and DELETE to the OLTP system is captured as a change event via Debezium reading PostgreSQL WAL through a logical replication slot.
Flink provides stream processing for enrichment, deduplication, windowed aggregation, and routing to multiple sinks. ClickHouse provides sub-second queries on hundreds of millions of rows via columnar storage with Z-order or MergeTree indexing. Snowflake serves business analysts running ad-hoc multi-table joins and dbt transformations where query latency is acceptable at 5–60 seconds.
The critical operational risk is WAL accumulation on PostgreSQL: if the Debezium replication slot consumer falls behind (Kafka down, connector failure), PostgreSQL retains WAL indefinitely, filling the data disk. This must be monitored and the slot dropped under sustained failure.
Use Cases
- ·Real-time operational dashboards with sub-second query latency
- ·Product analytics pipelines processing 1M–1B events/day
- ·Business intelligence with ETL separation from OLTP
- ·Fraud detection and anomaly detection on event streams
- ·Systems requiring CDC-based data lake population
Scale Profile
Entry Point
100k events/day: simpler aggregation pipelines suffice below this
Sweet Spot
1M–500M events/day, 1TB–100TB analytical data
Scaling Ceiling
ClickHouse handles petabytes with proper sharding. Snowflake auto-scales. Pipeline bottleneck is typically Flink checkpoint storage I/O.
Typical RPS
Write path: 1k–50k events/second ingestion; Read path: 1–100 concurrent analytical queries
Architecture Nodes (8)
Production OLTP database serving application traffic. Source of all change events. Logical replication slot held by Debezium connector: slot lag must be monitored.
Reads PostgreSQL WAL via logical replication slot. Converts row-level changes to structured Kafka messages. Tracks offset in Kafka Connect offsets topic. Restarts from last committed offset on failure.
Durable event log receiving all CDC change events. One topic per source table (e.g. postgres.public.orders). Retention: 7 days. Replication factor: 3, min ISR: 2.
Stateful stream processor. Enriches events with reference data, deduplicates on primary key within a 5-minute window, computes windowed aggregates, and routes to ClickHouse and Snowflake sinks. Checkpoints to S3 every 30 seconds.
Columnar OLAP store for operational analytics. MergeTree engine with primary key optimized for query patterns. Handles sub-second queries on 100M–1B row tables. Avoid UPDATE/DELETE: use ReplacingMergeTree for upserts.
Cloud data warehouse for business intelligence. Receives hourly microbatch from Flink Snowflake sink or dbt-managed S3 stage. Supports complex multi-table joins, historical trend analysis, and dbt transformations.
Flink checkpoint storage and Snowflake stage for batch loads. Also serves as long-term event archive beyond Kafka retention window.
Query proxy serving dashboards and product analytics. Routes latency-sensitive queries to ClickHouse. Routes ad-hoc business queries to Snowflake. Caches common query results.
Dependencies (8)
4 critical path edges. Failure on these directly degrades user-facing requests.
WAL logical replication
Debezium holds a PostgreSQL logical replication slot. WAL retained until Debezium confirms offset consumption. Slot lag = undrained WAL = disk risk.
Change events
Debezium publishes INSERT/UPDATE/DELETE events as Kafka messages. Schema embedded or registered with Schema Registry. At-least-once delivery.
Event consumption
Flink Kafka source consumer reads from all CDC topics. Watermarks based on event timestamps. Exactly-once via Flink checkpointing + Kafka transactional producer.
Processed events
Flink ClickHouse sink. Bulk inserts every 10 seconds. ClickHouse INSERT is append-only: Flink uses ReplacingMergeTree for effective upserts.
Batch microbatch
Flink writes Parquet files to S3 staging area every 1 hour. Snowflake COPY INTO loads staged files. dbt transforms raw stage into business models.
Checkpoint writes
Flink writes state snapshots to S3 every 30 seconds. Recovery from checkpoint is the only way to guarantee exactly-once after a job failure.
Operational queries
SQL queries for real-time dashboards. ClickHouse HTTP interface. P95 target: <500ms for queries with proper primary key filtering.
Timeout: 5s
BI queries
Business intelligence and ad-hoc analytical queries. Acceptable latency: 5–60 seconds. Snowflake auto-scaling handles query concurrency.
Timeout: 60s
Failure Propagation
How a failure in one component cascades through the system. Each path documents the mechanism and the mitigation that breaks the chain.
Mechanism
If Debezium stops consuming the replication slot, PostgreSQL retains all WAL since the slot's confirmed_flush_lsn. Under 10k writes/second, 1 hour of downtime accumulates ~36GB WAL. Disk fills → PostgreSQL crashes.
Mitigation
Alert on pg_replication_slots.lag_in_bytes > 1GB. Drop the slot automatically if consumer is down > 30 minutes. Restart from Debezium snapshot if slot is dropped.
Mechanism
Flink job failure halts event processing. Analytics data goes stale. On recovery, Flink replays from last checkpoint: events since checkpoint are reprocessed. At-least-once delivery to sinks until exactly-once semantics confirmed.
Mitigation
Configure checkpoint interval to 30s. Use incremental checkpoints for large state. Monitor Flink job manager health. Implement dead-letter queue for unprocessable events.
Mechanism
Kafka broker failure or partition leadership change causes consumer rebalance. Flink pauses consumption during rebalance (typically 5–30s). Analytics data lags by rebalance duration plus replay time.
Mitigation
3-broker Kafka cluster with rack awareness. Monitor consumer group lag. Prefer incremental cooperative rebalancing to reduce pause duration.
Scaling Transitions
Inflection points where this architecture begins to degrade and what the recommended evolution looks like.
Single ClickHouse server cannot handle concurrent query load plus merge operations. Part merge pressure degrades INSERT throughput.
Recommended Action
Deploy ClickHouse cluster with sharding on high-cardinality key. Distributed table on top of per-shard ReplicatedMergeTree tables.
Single Debezium connector becomes bottleneck. WAL volume exceeds connector throughput. Kafka topic partition count limits parallelism.
Recommended Action
Partition CDC by table group. Deploy separate connectors per table group. Increase Kafka topic partitions proportionally.
Patterns Applied
Architectural Notes
- ·ClickHouse is append-optimized: frequent UPDATE/DELETE operations destroy performance. Model all updates as new rows with ReplacingMergeTree or AggregatingMergeTree. Never mutate historical rows.
- ·Flink exactly-once requires both Flink checkpointing AND idempotent/transactional sinks. ClickHouse is not transactional: use ReplacingMergeTree deduplication as the idempotency layer.
- ·Monitor pg_replication_slots every 60 seconds in production. A forgotten slot with no consumer is one of the top causes of unexpected PostgreSQL disk saturation.
- ·Snowflake compute is billed per second: use auto-suspend with a 60-second timeout on warehouse definitions. dbt runs should use a dedicated warehouse separate from ad-hoc queries.
Confidence
StrongCDC-to-Kafka-to-ClickHouse pipeline is production-proven at Uber, Cloudflare, and major e-commerce platforms. Flink stream processing at this scale is documented by Netflix and LinkedIn.