Sign in to keep reading
“Ad Click Aggregator” is free with an account. Your progress and bookmarks follow you across devices.
Sign in to continueNEW: ML Mock & Coaching with a 20+ year experience coach. Save $20 on sessions for Premium.ML Mock & Coaching now available
NEW: ML Mock & Coaching with a 20+ year experience coach. Save $20 on sessions for Premium.ML Mock & Coaching now available
Design Ad Click Aggregator - This system design covers high-throughput event ingestion, streaming aggregation with Flink, exactly-once processing, and tiered storage architecture.
“Ad Click Aggregator” is free with an account. Your progress and bookmarks follow you across devices.
Sign in to continueThink Beyond the Happy Path
Could you answer these today? Tick what you already know.
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:
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.
Key points to clarify with your interviewer:
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.
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
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."
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
| Functional Requirements (FRs) | |
|---|---|
| Name | Description |
| 1. Click Tracking and Redirection | Track ad clicks and redirect users to advertiser destinations. |
| 2. Multi-Platform Event Collection | Collect events from web, iOS, and Android into unified pipeline. |
| 3. Real-Time Metrics | Provide per-minute granularity metrics for advertiser dashboards. |
| Non-Functional Requirements (NFRs) | |
|---|---|
| Name | Description |
| 1. High Throughput | >100K clicks/sec sustained ingest capacity. |
| 2. Low Query Latency | P95 < 800ms for filtered aggregates. |
| 3. Near Real-Time Freshness | ≤ 15 seconds end-to-end data freshness. |
| 4. Zero Data Loss | Flink checkpoint recovery < 60 seconds. |
| 5. Idempotent Processing | Duplication rate < 0.01% across pipeline. |
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.
| Ads Table | |||
|---|---|---|---|
| Field | Type | Key | Description |
ad_id | BIGINT | PK | Unique identifier for the ad. |
advertiser_id | BIGINT | FK | Reference to the advertiser. |
campaign_id | BIGINT | ||
Click Event — Raw Click Record
A Click Event represents a single user click, stored for auditing and reconciliation.
| Click Events Table | |||
|---|---|---|---|
| Field | Type | Key | Description |
click_id | BIGINT | PK | Auto-generated unique identifier for the click. |
ad_id | BIGINT | FK | Reference to the clicked ad. |
clicked_at | TIMESTAMP | ||
Aggregated Clicks — Pre-Computed Metrics
Aggregated Clicks contain pre-computed metrics per time bucket for efficient querying.
| Aggregated Clicks Table | |||
|---|---|---|---|
| Field | Type | Key | Description |
ad_id | BIGINT | FK | Reference to the ad. |
campaign_id | BIGINT | FK | Campaign grouping. |
time_bucket | TIMESTAMP | ||
| FK |
| Reference to the campaign this ad belongs to. |
redirect_url | TEXT | NOT NULL | URL where users are redirected. |
created_at | TIMESTAMP | NOT NULL | Ad creation timestamp. |
| status | ENUM | NOT NULL | active/paused/archived. |
| NOT NULL |
| Click timestamp. |
user_ip | INET | NOT NULL | User's IP address. |
| device | VARCHAR(255) | NOT NULL | Device type (mobile, desktop). |
user_agent | TEXT | NOT NULL | Browser user agent string. |
geo_location | VARCHAR(255) | NULLABLE | Geographic location. |
| NOT NULL |
| Minute-level window (e.g., 2024-11-23 10:00). |
click_count | INT | NOT NULL | Number of clicks in this bucket. |
unique_users | INT | NULLABLE | Distinct user count (optional). |