Project Name
Billion-Row Ad-Tech Analytics Rebuilt on ClickHouse- Ingestion Lag Cut 97% and Queries Now Sub-Second
A digital ad-tech platform processing billions of ad impressions, clicks, and bid events daily had its analytics stack built on a traditional relational database. As event volume crossed several billion rows per month, query latency degraded from seconds to minutes, campaign dashboards lagged hours behind real bidding activity, and ingestion lag exceeded 20 minutes during peak hours. Applying its AI-First approach, Ksolves delivered a ClickHouse analytical backbone ingesting tens of millions of events per hour, serving sub-second aggregate queries across billions of rows, and powering both internal BI and advertiser-facing reporting APIs at a fraction of the previous data warehouse cost.
- Query Latency at Scale: As event volume crossed several billion rows per month, OLTP-style queries began timing out, especially for group-by aggregations across campaign, advertiser, and geography dimensions. Reports that took seconds began taking minutes.
- Ingestion Could Not Keep Up: Row-by-row writes became the primary bottleneck during peak ad-serving hours, causing 20+ minute ingestion lag and campaign dashboards lagging hours behind real bidding activity.
- Expensive, Rigid Scaling: Scaling meant vertically upgrading a single expensive instance. No practical path existed to scale out horizontally without a costly and risky migration.
- Single-Node Storage Risk: Existing analytics ran on a single node with no replication - total data loss risk on any node failure.
- Choosing the Right Table Engine Per Use Case: Raw event logs, deduplicated fact tables, and pre-aggregated summary tables each required a different MergeTree variant. Getting it wrong meant wasted storage or incorrect query results.
- Partition and Primary Key Strategy for Real Query Patterns: Naive partitioning by ingestion date alone caused full-partition scans until the ORDER BY key was redesigned around actual advertiser query predicates.
- Deduplication Without Sacrificing Ingestion Speed: Ad events arrived more than once from retry-prone upstream systems. Any synchronous deduplication at write time would have destroyed ingestion throughput.
- Correctly Interpreting Aggregated State: Pre-aggregated rollup tables store intermediate aggregate states, not final numbers. Naive SUM()/AVG() queries silently produced wrong totals until merge-aware query patterns were adopted.
- Cluster Resilience Without Manual Intervention: Single-node ClickHouse is fast but not fault-tolerant. Replication and automatic failover were required without depending on manual intervention during node failures.
Ksolves designed and built a ClickHouse analytical backbone replacing the relational database, with each architectural decision driven by the specific query patterns, ingestion characteristics, and reliability requirements of the ad-tech platform. The governing principle: purpose-fit every table engine to its workload, and let the cluster handle failures and scaling without operational intervention.
- Sharded and Replicated ClickHouse Cluster: 6-node cluster (3 shards x 2 replicas) coordinated via ClickHouse Keeper with Distributed table routing queries and inserts transparently across shards - horizontal scalability without changing how applications query the data.
- ReplacingMergeTree for Clean Deduplication: Adopted ReplacingMergeTree for core event tables, silently collapsing duplicate events from retry-prone upstream systems during background merges - FINAL and argMax-based queries where immediate consistency matters, standard queries where eventual consistency is acceptable for speed.
- AggregatingMergeTree Materialized Views: Campaign-level and hourly rollups updated incrementally and automatically as new data lands - no scheduled batch recomputation jobs, no stale rollups. Intermediate aggregate states stored correctly so merge-aware queries return accurate totals.
- Query-Pattern-Driven ORDER BY and Partitioning: Primary keys restructured around (advertiser_id, campaign_id, event_date) to match actual advertiser filtering patterns. Partitioned by month to balance partition count against query pruning efficiency.
- Kafka-Backed Ingestion Pipeline: Kafka native table engine layer in front of ClickHouse decouples upstream ad-serving services from the analytical database entirely, allowing ClickHouse to consume events at its own optimal batch size and pace - smoothing traffic spikes during peak bidding windows.
Technology Stack
| Category | Technology |
|---|---|
| Storage | ClickHouse (6-Node, 3 Shards x 2 Replicas) |
| Deduplication | ReplacingMergeTree |
| Aggregation | AggregatingMergeTree Materialized Views |
| Streaming | Kafka Native Table Engine |
| Query Design | ORDER BY and Partition Key Optimisation |
| Visualization | Internal BI + Advertiser Reporting APIs |
- Ingestion Lag Cut From 20+ Minutes to Under 30 Seconds: Kafka-backed batch ingestion eliminated the row-by-row write bottleneck. End-to-end ingestion lag reduced from 20+ minutes to under 30 seconds during peak ad-serving windows.
- Sub-Second Queries Across Billions of Rows: ClickHouse columnar storage with query-pattern-driven keys delivers sub-second aggregate responses across tables holding billions of historical event rows. Reports that took minutes now return in milliseconds.
- 100% Data Availability Through Node Failures: 3 shards x 2 replicas ensures every shard has a live replica. Node failures cause zero data loss or query unavailability - handled automatically with no manual intervention.
- Zero Manual Campaign Rollup Recomputation: AggregatingMergeTree materialized views update campaign and hourly rollups automatically as events land. Scheduled batch jobs eliminated.
- 90-Day Raw Event Retention With Automatic Expiry: TTL rules enforce 90-day retention with zero manual delete operations. Storage growth bounded by policy.
- Horizontal Scalability Without Application Changes: Distributed table abstraction means new shards can be added as event volume grows without changing internal BI tools or advertiser-facing APIs.
“Our finance team was reconciling campaign spend the next morning. Now they do it in real time. The advertiser dashboards that used to lag hours are now current to within 30 seconds. ClickHouse gave us the query speed and the ingestion throughput on one platform that our relational database could never have delivered at this scale.”
– Head of Data Engineering / VP Analytics.
A digital ad-tech platform processing billions of daily events, suffering 20-minute ingestion lag, minute-long query timeouts, and campaign dashboards hours behind real-world bidding activity was transformed through Ksolves Big Data services. A 6-node ClickHouse cluster with ReplacingMergeTree deduplication, AggregatingMergeTree materialized views, Kafka-backed ingestion, and query-pattern-driven storage design now delivers sub-second queries across billions of rows, 30-second ingestion lag, zero manual recomputation jobs, and 100% data availability through node failures.
Still waiting minutes for ad analytics queries that should take seconds?
