One of the **most asked system design interview questions** at companies like **Meta, Google, and beyond** is how to design large-scale aggregation systems — because they test how you reason under constraints, scale reliably, and handle failure gracefully. Before diving into the material, take a moment to ask yourself: - **Ingestion at Scale:** How do you handle a Super Bowl-level spike of millions of clicks per second across diverse sources without overloading the pipeline? - **Exactly-Once Counting:** How do you prevent double-taps or retry storms from causing advertisers to be overbilled or underbilled? - **Late Data & Time Semantics:** How do you correctly account for offline clicks that arrive hours late while still finalizing billing on time? - **Hot Partitions:** How do you prevent a viral ad campaign from creating a hot key that overwhelms a single shard? You don't need to answer all of these right away. A **Senior/Staff+ Engineer** doesn't stop at basic functionality — they **anticipate edge cases**, **design for resilience**, and **push for production-grade reliability**. But **great systems start with great questions.** What would *you* ask next? ## Problem Statement Design an ad click aggregation system that collects click events from multiple platforms, processes them in real-time, and provides advertisers with accurate metrics at per-minute granularity. The system should: 1. Track ad clicks and redirect users to advertiser destinations. 2. Collect and ingest click events from web, iOS, and Android clients. 3. Aggregate metrics in real-time for advertiser dashboards. And the scale is 100,000+ clicks per second with sub-second query latency. **Propose and Clarify with Your Interviewer** Before diving into design, align on what's **essential vs. nice-to-have**. This helps you frame the solution, prioritize trade-offs, and show structured thinking. - **Mid–Senior Level:** Focus on proposing features you're confident implementing end-to-end; interviewers value a complete, working solution over an incomplete "fancy" one. - **Staff+ Level:** Demonstrate judgment by clarifying what's truly core to the problem and what could be layered on as advanced features to highlight your breadth and depth. Key points to clarify with your interviewer: - **Target user:** Is this for internal analytics teams, advertisers, or system monitoring? - **Initial query scope:** Should we start with simple aggregates (e.g., raw counts in a time window such as *Top-K ads viewed yesterday*)? - **Granularity requirements:** Do we need to support multiple levels — near real-time, 1-minute, 1-hour, or daily rollups? ## Functional Requirements **FR1 – Click Tracking and Redirection** Users can click ads and get redirected to their destinations while the system logs the click event. **FR2 – Multi-Platform Event Collection** Log events are collected from various clients (web, iOS, Android) and ingested into a unified pipeline. **FR3 – Real-Time Metrics** Ad click metrics are available to advertisers with **per-minute granularity** for dashboards and reporting. ## Non-Functional Requirements **NFR1 – High Throughput** Scale to handle peak traffic of **>100,000 clicks per second** with sustained ingest capacity. **NFR2 – Low Query Latency** Process analytics queries with **sub-second response times** (P95 < 800ms for filtered aggregates). **NFR3 – Near Real-Time Freshness** Provide near real-time data access with **≤ 15 seconds** end-to-end data freshness SLA. **NFR4 – Zero Data Loss** Ensure fault-tolerant and accurate data collection with Flink checkpoint recovery **< 60 seconds**. **NFR5 – Idempotent Processing** Implement idempotent processing to prevent duplicate click counts with target duplication rate **< 0.01%**. **Pro Tips for NFRs** 1. Avoid using vague buzzwords like "availability" or "consistency" without context. Instead, apply these concepts to specific scenarios with measurable metrics. For example, instead of saying "The system should be highly available," say "The system should maintain 99.99% uptime for accepting click events and processing aggregations." 2. Prioritize functionality over scalability. Ensure your system can perform its core tasks correctly before focusing on how to scale it. A solid foundation is essential for effective scaling. **Requirement Summary** 100K clicks/sec sustained ingest capacity." }, { name: "2. Low Query Latency", comment: "P95 < 800ms for filtered aggregates." }, { name: "3. Near Real-Time Freshness", comment: "≤ 15 seconds end-to-end data freshness." }, { name: "4. Zero Data Loss", comment: "Flink checkpoint recovery < 60 seconds." }, { name: "5. Idempotent Processing", comment: "Duplication rate < 0.01% across pipeline." } ]} /> ## Core Entities To tie these requirements together into a coherent architecture, we begin with the foundational data structures. **Ad — The Advertisement Definition** An **Ad** represents an advertisement that users can click. It contains metadata for tracking and redirection. **Click Event — Raw Click Record** A **Click Event** represents a single user click, stored for auditing and reconciliation. **Aggregated Clicks — Pre-Computed Metrics** **Aggregated Clicks** contain pre-computed metrics per time bucket for efficient querying. ## High Level Design Designing an ad click aggregation system addresses a set of uniquely challenging problems rooted in scale, accuracy, and timeliness. Click data is high-volume and bursty, often spiking unpredictably due to traffic surges, marketing campaigns, or bot activity. The system must process this data reliably without introducing duplicates or losing events—despite failures, retries, or network glitches. Accuracy is paramount: even a small error can lead to revenue miscalculation or advertiser mistrust. Meanwhile, advertisers expect immediate visibility into performance metrics, demanding near real-time access with low-latency querying. **Ingestion: Kafka + Idempotent Producers** - Backend `/click` endpoint generates `click_id`, enriches metadata. - Write to **Kafka** (replication factor 3) instead of DB directly. - Enable `enable.idempotence=true` for producer deduplication. - Estimated ingest throughput: **100K events/sec sustained**. **Stream Processing: Apache Flink** - Real-time aggregation: `.keyBy(ad_id)` + 1-min tumbling windows. - Store pre-aggregates locally (RocksDB), checkpoint to S3. - Enables **exactly-once** processing via checkpoints and offset sync. - Recovery SLA from failure: **< 60 seconds**. - Data freshness target: **≤ 15 seconds** from click to queryable. **Storage: Hot / Warm / Cold Tiers** - **ClickHouse / TimescaleDB**: for hot pre-aggregates (last 7 days). - **S3**: raw logs in Avro/Parquet for long-term cold storage + reconciliation. - Write volume reduced by ~99.9% via streaming aggregation. **Query Layer** - OLAP store supports ad-hoc queries, dashboards, and hourly rollups. - P95 query latency: **< 800 ms** for filtered top-K views. - Partition by `time_bucket` + `ad_id` for performance. **Reliability & Deduplication** - Layered dedup: `click_id` used across Kafka, Flink, DB. - Flink TTL-state filters dupes in-stream (e.g., 6h retention). - OLTP stores use `click_id` as PK or UPSERT semantics. - Target duplication rate: **< 0.01%**. ### FR1: Users can click ads and get redirected to their destinations The **core user flow** is simple: a user sees an ad, clicks it, and is redirected to the advertiser's website. But behind the scenes, this flow must balance **accuracy of tracking**, **low latency user experience**, and **operational scalability**. To achieve this, the system introduces an **Ad Placement Service** responsible for three key steps: 1. **Ad Creation & Placement** – Each ad is created with metadata that includes an **Ad ID** (unique identifier) and a **Redirect URL** (the advertiser's site). These are injected into webpages where ads are displayed. 2. **Clickable Ad Link** – Ads are rendered with an HTML anchor tag that points to a backend `/click` endpoint, parameterized with `adId`. 3. **Click Tracking & Redirection** – When the user clicks, the system first logs the event, then issues a redirect to the advertiser's site. **Backend: /click Endpoint** The `/click` endpoint is the linchpin of this design. - **Input**: `GET /click?adId=` - **Process**: 1. Validate the `adId` and look up the corresponding redirect URL. 2. Log the click event (with metadata such as timestamp, user/session ID, device, and referrer). 3. Redirect the user to the advertiser's destination URL via an HTTP **302 redirect**. This ensures tracking is **authoritative** and cannot be bypassed by users directly visiting the advertiser's URL. ### FR2: Log events collected from Clients In a multi-platform environment, the first design consideration in logging is identifying **where events originate**. Modern systems collect data from web browsers, iOS apps, and Android apps—each with its own limitations in terms of network reliability, client-side control, and observability. **Event Logging Strategies** To ensure consistent tracking across all surfaces, the logging system must support multiple collection strategies that differ in fidelity, trustworthiness, and operational guarantees. A canonical event schema ensures consistent logging across web, mobile, and backend systems. It supports schema evolution to avoid breaking consumers, simplifies ingestion and analytics, and enforces data quality through clear contracts between producers and consumers. ```json { "event_name": "ad_click", "event_version": "v1", "event_time": "2025-08-28T17:40:00Z", "event_id": "evt-456abc-20250828", "trace_id": "trace-abc123", "source": "ios", "client_ip": "192.0.2.1", "user_agent": "Mozilla/5.0...", "user_id": "user-123", "session_id": "sess-xyz789", "geo": { "country": "US", "region": "CA", "city": "San Francisco" }, "event_data": { "ad_click": { "ad_id": "ad123", "campaign_id": "camp456", "placement_id": "home_banner", "clicked_coordinates": [134, 290], "creative_id": "crea789" } } } ``` **Schema Design Principles:** - **Versioning:** Always include `event_version`; deprecate old fields, never delete. - **Flat over Nested:** Prefer flat schemas for easier OLAP indexing; use nesting only when necessary. - **Timestamp Semantics:** Use ISO-8601 or epoch millis; specify if it's client-side or server-side time. - **Required vs Optional:** Clearly define which fields are mandatory vs optional. - **Privacy-Aware:** Avoid PII; use `user_id` or anonymized identifiers instead of names or emails. **Asynchronous Ingestion via Message Queue** Once events are collected, they must be reliably ingested without coupling log collection to downstream processing. The collector service writes validated logs to a **durable, append-only message queue** (e.g., Kafka), enabling asynchronous, backpressure-tolerant ingestion. **Why We Avoid Simpler Alternatives** - **Direct-to-Database Writes**: Tightly couples log producers with storage. Introduces backpressure under load, lacks resilience to downstream failures, and limits horizontal scaling. - **Periodic Batch Uploads**: Suitable for low-fidelity use cases, but introduces significant delays. Events arrive too late for real-time analytics, reducing observability and operational agility. A **message queue** is designed to transmit lightweight, often transient messages between services in a decoupled way. It's commonly used in event-driven systems where producers emit events (like user clicks or logs), and one or more consumers process them independently. Message queues like Kafka, RabbitMQ, or Google Pub/Sub emphasize throughput, ordering, and delivery guarantees (e.g., at-least-once), but they don't manage job lifecycle or retries by default. In contrast, a **job queue** is purpose-built for executing background tasks that may be long-running, failure-prone, or require retries. It's ideal for asynchronous workflows like sending emails, generating reports, or image processing. Systems like SQS or Bull manage job states (e.g., queued, in-progress, failed), offer built-in retry policies, and are often backed by a persistent store like Redis for reliability and scheduling. ### FR3: Ad click metrics with per-minute granularity In addition to collecting raw click events, ad systems must **aggregate metrics at fine granularity**—typically per minute—to power real-time dashboards, anomaly detection, and billing pipelines. **Storage Options Comparison** A **time-series database (TSDB)** is optimized for storing and querying timestamped data points. Unlike general-purpose relational DBs, TSDBs are tailored to time-series workloads: 1. **Optimized for Sequential Writes:** TSDBs use append-friendly storage engines and compression, enabling millions of writes per second. 2. **Efficient Compression:** Consecutive values are often similar, allowing aggressive compression (1 billion readings may fit in gigabytes). 3. **Built-In Time Functions:** Moving averages, rate of change, and windowed aggregations are first-class features. 4. **Retention Policies & Downsampling:** Automatically expire old data or downsample (e.g., 1s resolution for 7 days, then 1m averages for 90 days). 5. **High-Performance Time-Range Queries:** Indexed by time and organized in chunks for fast "last 7 days" queries. Aggregate incoming ad click events into 1-minute windows by `ad_id`, producing a stream of `(ad_id, timestamp, click_count)` records. **Flink Pre-Aggregation Logic:** 1. **Ingest** events from Kafka using `FlinkKafkaConsumer`. 2. **Parse and extract** event timestamp and assign as event time. 3. **Key by `ad_id`** to group clicks by ad campaign. 4. **Apply tumbling window** of 1 minute (event time). 5. **Count** number of events per window. 6. **Emit aggregate** to downstream system. ```java DataStream events = env .addSource(kafkaConsumer) .assignTimestampsAndWatermarks(new BoundedOutOfOrdernessTimestampExtractor(Time.seconds(10)) { public long extractTimestamp(ClickEvent e) { return e.getTimestamp().toEpochMilli(); } }); events .keyBy(ClickEvent::getAdId) .window(TumblingEventTimeWindows.of(Time.minutes(1))) .aggregate(new ClickCountAggregator()) .addSink(new ClickAggregateSink()); ``` **Output:** ```json { "ad_id": "ad123", "time_bucket": "2024-11-23 10:00", "click_count": 87 } ``` **Benefits:** - Reduces write volume to downstream DBs by ~99.9%. - Enables near real-time dashboards (e.g., top ads in the last minute). - Robust to out-of-order events (thanks to event time + watermarking). - Scales horizontally—each `ad_id` partition is processed in parallel. In an ad-click system, raw events are written at extremely high rates, but the majority of queries are **not about individual events**. Advertisers ask questions like: - "How many clicks did campaign X receive in the past hour, by device and region?" - "What are the top ten ads by click-through rate today?" - "Which campaigns are trending abnormally fast right now?" These are **analytical queries**: multi-dimensional, aggregative, spanning billions of rows. A transactional or time-series store struggles here. **OLAP engines (ClickHouse, BigQuery, Snowflake)** are purpose-built: - **Columnar format** — scan only needed fields - **Vectorized execution** — parallel processing across nodes - **Compression** — efficient storage of similar values **Pros**: High-performance analytics, rich SQL, scalable to petabytes. **Cons**: Not ideal for ultra-high-frequency writes; best downstream of streaming aggregators. **Option 3: Raw + Pre-Aggregated Hybrid Model (Recommended)** Industry best practice is to keep **both raw and aggregated data**: - **Raw events** (stored in S3 + Iceberg) preserve fidelity for historical analysis, debugging, and compliance. - **Pre-aggregated metrics** accelerate the majority of queries at a fraction of the cost. This dual-tier design ensures **cost efficiency without sacrificing auditability**. ## Deep Dives ### DD1: Scale to support 100K+ clicks per second Scaling writes isn't just about throwing more hardware at the problem. Systems hit bottlenecks on disk I/O, CPU, or network. The fundamental tensions are: - **Writes vs Reads trade-off**: Optimizing for one often degrades the other. - **Bursty workloads**: Peak events (Black Friday, Super Bowl) overwhelm steady-state designs. - **Skew in distribution**: Bad partition keys lead to overloaded shards. - **Overhead per write**: Individual writes carry expensive transaction and index costs. **Solution 1: Vertical Scaling and Database Choices** - Max out existing hardware before adding complexity (NVMe SSDs, high-speed interconnects). - Pick write-optimized databases: Cassandra (log-structured), TimescaleDB (time-series), ClickHouse (columnar). **Solution 2: Sharding and Partitioning** - **Horizontal Sharding**: Spread writes across servers. Good keys: `customerId`. Bad keys: `productId` (creates hot items). - **Vertical Partitioning**: Split by workload type (trip details vs driver pings vs analytics logs). **Solution 3: Queues and Load Shedding** - Use Kafka/SQS as buffers to absorb spikes. - Drop less critical writes under pressure (discard redundant position updates, preserve clicks). **Solution 4: Batching and Hierarchical Aggregation** - Group small writes into larger commits. - Aggregate in stages (edge devices pre-aggregate before sending to central DB). **Our Scaling Strategy:** **1. Ingestion: Decoupling with Kafka** The `/click` endpoint validates and enriches events, then pushes to **Kafka** instead of writing directly to database. Kafka provides a durable buffer that smooths spikes and insulates downstream systems. **2. Streaming: Pre-Aggregation with Flink** Rather than persisting every raw event, Flink consumes Kafka, partitions by `.keyBy(ad_id, region)`, and performs **1-minute tumbling window aggregations**. Instead of writing 100,000 rows/second, we emit pre-aggregated metrics like `click_count` per `(ad_id, minute)`, **reducing write volume by ~99.9%**. **3. Storage Tiers: Hot, Warm, Cold** **Performance vs. Accuracy** - Stream pre-aggregation reduces latency and write amplification. - Final metrics committed after window expires, allowing late events. - Results eventually consistent within bounded delay (10-30 seconds). **Scalability vs. Complexity** - Each component (Kafka, Flink, ClickHouse) scales independently. - Failures are isolated, not cascading. - Easier partial upgrades and debugging. **Cost vs. Durability** - ClickHouse for fast queries (higher cost). - S3 for raw logs (cheap, compressed Parquet). - TTL policies roll off old data from hot stores. - Pre-aggregation reduces long-term storage costs. ### DD2: Near real-time data access (≤ 15 seconds freshness) Let advertisers query click metrics within seconds of user interaction—enabling fast feedback loops for campaign performance. **Design Strategy: Stream → Aggregate → Query** **1. Streaming Aggregation (Flink)** Consume click events from Kafka and group into **1-minute buckets** by `ad_id`, `region`. Use watermarks to handle out-of-order events with allowed delay (e.g., 30s). **2. Low-Latency Storage Backends** **3. Query Interface** Wrap storage with REST/GraphQL API or embed in dashboards. Results are **eventually consistent**, typically reflecting clicks from the **last 10-30 seconds**. **Realistic Latency Profile:** ### DD3: Idempotent processing (< 0.01% duplication) In any high-throughput ad infrastructure, **ensuring clicks are only counted once** is critical. Retries, duplicate submissions, and race conditions can easily cause **overcounting** unless the system has end-to-end **idempotency guarantees**. **Layer 1: Ingress — Assigning Unique `click_id`** When a click lands at `/click?adId=...`, immediately generate a **globally unique ID** (UUID/ULID). This `click_id` becomes the **canonical fingerprint** and deduplication key throughout the pipeline. ```json { "click_id": "ad123-click-20250828-abc123xyz", "user_id": "u456", "ad_id": "ad123", "timestamp": "2025-08-28T17:40:00Z" } ``` **Layer 2: Kafka — Idempotent Producers** **Layer 3: Flink — TTL State Deduplication** Even with idempotent Kafka ingestion, Flink implements its own deduplication using **stateful functions** that track seen `click_id`s with TTL (e.g., 6 hours). **Layer 4: Storage — Write-Time Deduplication** ### DD4: Zero data loss and fault tolerance Ensuring **zero data loss** means anticipating delayed clients, retries, duplicates, traffic spikes, and failures—then designing graceful handling. **Multi-Layered Safeguards:** **Zero data loss** isn't one feature—it's the sum of design decisions across the entire ingestion-to-analytics lifecycle. ### DD5: Optimize historical data storage for cost In high-volume write systems, **efficient storage of historical data** is a major concern. We need reliability and durability without wasteful patterns. **Write Amplification Problem** A single logical write can lead to **multiple physical writes** at the storage layer, especially with frequent updates, deletes, or TTL expiry. **Solution: Compaction Strategies** ## Final Thoughts Designing a production-grade Ad Click Aggregation system goes far beyond simple event counting. It's about building a **resilient, accurate, and real-time analytics backbone** that handles real-world challenges—bursty traffic, exactly-once semantics, late-arriving data, and cost-efficient storage—while maintaining advertiser trust and billing accuracy. In this article, we walked through every major building block: from click tracking and multi-platform collection, to Kafka-based ingestion, to Flink streaming aggregation, to tiered storage architecture. More importantly, we explored **deep-dive design tradeoffs** that help you answer the toughest ad aggregation challenges: 1. **High Throughput (DD1)**: Kafka buffering, Flink pre-aggregation, and tiered storage reduce write amplification by 99.9%. 2. **Near Real-Time (DD2)**: Stream → Aggregate → Query pipeline with sub-15-second data freshness. 3. **Idempotent Processing (DD3)**: Layered deduplication from ingress to storage achieves < 0.01% duplication. 4. **Zero Data Loss (DD4)**: Watermarking, checkpointing, reconciliation, and anomaly detection. 5. **Cost Optimization (DD5)**: Compaction, columnar storage, TTL policies, and tiered architecture. Together, these form the backbone of a trustworthy, scalable ad analytics platform. Each deep dive covered trade-offs between performance, accuracy, cost, and complexity—laying out concrete, production-ready solutions. **How ShowOffer can help you?** We've included **callouts** and **open-ended design prompts** throughout this write-up — perfect for self-practice or interview discussion. If you'd like feedback or want to walk through this system design in a mock interview setting, **book a session with us at [ShowOffer.io](https://showoffer.io/)**. We're here to help you sharpen your skills, gain confidence, and land your next big offer.