min read

Propose and clarify with your interviewer functional requirements

Ad Click Aggregation

Written by

Staff Eng at Meta

Published on

April 12, 2025

Functional Requirements

(Don't do) Don't try to list every possible functional requirement. While demonstrating your product thinking and real-world experience is valuable, remember that interview time is limited. Pitfalls to avoid:

Avoid going deep into details for now, this question can vary a lot based on goal. Analyzing details can distract the interviewer.

(Do) Propose and clarify with your interviewer (i.e: what's important? what's should be "core features"?)

staff+: it is important to understand what is essential to the question? What should be added as bonus points to show off breath and depth of your knowledge.

mid - senior: propose functions you are most familiar. End to end working solution completeness should be always preferred.

Users can click ads and get redirected to their destinations
Log events collected from various client (web, ios, android)
Query metrics over time at a time period

Non-Functional Requirements

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.9% uptime for accepting job submission requests and handling job executions."
"Concrete metric/numbers". It's great to include specific numbers when describing non-functional requirements if you've aligned with your interviewer beforehand.
Ordering? 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.

Scale to handle peak traffic of >10k clicks per second
Process analytics queries with sub-second response times
Ensure zero data loss through fault-tolerant and accurate data collection
Provide near real-time data access, allowing advertisers to query results immediately after clicks occur
Implement idempotent processing to prevent duplicate click counts

High Level Design

Users can click ads and get redirected to their destinations

The Ad Placement Service will handle:

Ad Creation and Placement: Associates ads with metadata, including the advertiser's website URL. Ads appear on webpages with two key pieces of data:
- Ad ID: A unique identifier for the ad.
- Redirect URL: The URL of the advertiser's website.
Clickable Ad Link: Each ad contains an HTML anchor tag that points to the /click endpoint with the adId parameter.
Ad Click Tracking: Generates a unique identifier for each ad and links it to a redirect URL.

Backend: /click Endpoint

This endpoint performs two tasks:

Logs the click for analytics and tracking.
Redirects the user to the advertiser's website.

Request: GET /click?adId=<ad-id>
Process:
- Look up the adId to find the matching redirect URL.
- Log the click event for analytics.
- Redirect the user to the destination URL.

Option 1: Client-Side Rendering

**Approach:**Each ad contains a redirect URL. When clicked, the browser redirects to the target URL and sends a POST request to the /click endpoint for tracking. This approach is straightforward and eliminates server-side logic.

**Challenges:**Users can bypass tracking by accessing the advertiser's URL directly, leading to inaccurate click data. Browser extensions may also interfere with tracking, compromising ad performance metrics.

Option 2: Server-Side Rendering

**Approach:**When users click an ad, their request goes to our server, which logs the click and issues a 302 redirect to the advertiser's website. This method ensures reliable click tracking and enables URL parameter additions for advertisers (out of scope for now).

**Challenges:**This solution introduces additional complexity and potential latency that could affect user experience. It also demands a robust system infrastructure to manage increased server load.

Bonus Point

[Batch Process] One of the most effective strategies for handling high-volume writes is batching multiple operations together and processing them as a group. This approach reduces the overhead of handling individual writes and improves overall throughput.
1. Buffer or queue write data, then process it in batches rather than individually.
2. Ensure batching doesn't introduce excessive delay, especially for near-real-time processing requirements.
3. Fine-tune batch sizes and flush intervals to optimize the balance between latency and throughput.

Staff+ Bonus Point , if you want to demonstrate real production understanding and cross company scope to align with multiple team in the company.

Ad Targeting: Ad targeting delivers ads to specific audiences based on demographics, location, behavior, and interests. It uses data from user activity, cookies, and account information to match ads with relevant users.
Cross-Device Tracking: Cross-device tracking identifies and connects a user's activity across multiple devices (e.g., phone, tablet, laptop). It combines data sources like cookies, device IDs, or logged-in account information to create a unified profile of the user.
1. Deterministic Tracking: Uses unique identifiers, such as login credentials, to match users across devices.
2. Probabilistic Tracking: Relies on patterns like IP address, browser type, and behavior to make educated guesses about a user's identity.

Log events collected from various client (web, ios, android)

Asynchronous Writes:

Use message queues (e.g., Kafka, RabbitMQ) for handling asynchronous writes. Push data to a queue for later processing by workers.

Ensure the asynchronous pipeline has sufficient capacity to manage bursts in write traffic.
Implement robust retry mechanisms for failed writes.

Industry Best practice: Logging systems use buffering to batch-write logs, reducing individual write overhead. Real-time analytics platforms send user activity data asynchronously to Kafka topics, where workers consume and store messages. This approach enables scalability and high-volume writes without blocking the main application.

Kafka for message queue

Using Apache Kafka for data ingestion in an Ad Click Aggregation System involves efficiently collecting, buffering, and streaming ad click events into downstream processing systems (e.g., Apache Flink) for aggregation.

Benefit of Kafka: Kafka ensures high availability and fault tolerance by replicating partitions across brokers. Producers write events, and multiple consumers (e.g., Flink) can read the stream for real-time processing.
Topic Design: Create topics based on logical categories, e.g., ad-clicks, ad-impressions, or user-activity. Partition the topic to parallelize ingestion. For example, partition by ad_id or region (if data localization is a consideration) to ensure that events for the same ad or region are sent to the same partition.

Example of raw events sent by click processor to Kafka:

{
    "ad_id": "12345",
    "timestamp": "2024-11-23T10:00:00Z",
    "user_id": "67890",
    "region": "North America",
    "device_type": "mobile"
}

Data Format: Use formats like JSON or Avro for serialization. Avro is recommended for schema evolution and compactness.
Consuming Data in Real-Time: Stream processors (e.g., Flink or Spark Streaming) subscribe to the ad-clicks topic to process and aggregate events. Multiple consumers work in parallel through consumer groups, with each consumer handling a distinct set of partitions. Consumer processes track their position in the stream through offsets, preventing data loss during failures or reprocessing.
Staff+ how to handle Kafka failure, logging failure is generally considered Sev-0, but it happens.
- Broker failure
  - Replication: Set a replication factor (e.g., 3) to ensure partition replicas exist across brokers. Kafka's leader election automatically promotes a new leader from replicas during failure.
- Producer Failures
  - Retries: Enable retries (retries setting) for transient failures.
  - Idempotence: Ensure producers are idempotent (enable.idempotence=true) to avoid duplicate messages during retries.
  - Batching: Optimize batch size to reduce network calls while avoiding delays.
- Consumer Failures
  - Offset Management: Use manual offset commits (enable.auto.commit=false) to ensure at-least-once delivery.
  - Failover: Employ Flink's checkpointing to restart processing from the last saved state after a failure.

Raw Record and Pre-aggregation:

To address latency and scalability issues, stream for real-time processing is always prefer. This approach allows us to process events as they arrive, rather than waiting for a batch job to run. When a click occurs, our processing service immediately writes the event to a stream like Kafka or Kinesis. A stream processor such as Flink or Spark Streaming then reads these events from the stream and aggregates them in real-time.

This method maintains a running count of click totals in memory, updating them as new events arrive. At the end of each time window, we flush the aggregated data to our OLAP database. Pre-aggregation is necessary for several reasons:

Performance Optimization: Pre-aggregating data reduces the need to process raw click streams repeatedly, minimizing computational overhead and latency.
Storage Efficiency: Pre-aggregation reduces storage costs by summarizing large volumes of raw click data into compact summaries.
Dimension-Focused Queries: Pre-aggregating by dimensions (like ad ID, region, or device type) enables fast queries, particularly for data with many unique values.

Industry Best practice: Event with pre-aggregated data, raw data should still be stored (such as in a s3, data lake or cold storage) to support reconciliation, historical analysis, advanced processing, and debugging. Details about reconciliation can be found later.

Apache Flink converts raw records into pre-aggregated metrics in real time. Here's how it works:

Ingesting Raw Data: Flink ingests data streams, such as ad clicks, from sources (Kafka from example above). Each record contains fields like ad_id, timestamp, user_id, region, and device_type.
Keying and Partitioning: Using the .keyBy() function, Flink partitions streams by key dimensions like ad_id or (ad_id, region). This routes all records with the same key to the same processing task for efficient aggregation.
Time-Based Windowing: Flink groups events into fixed time intervals for aggregation:
- Tumbling Windows: Fixed, non-overlapping intervals (e.g., every 1 minute)
- Sliding Windows: Overlapping intervals (e.g., 5-minute windows sliding every 1 minute). Implemented using .window() with appropriate window types for different granularities
Real-Time Aggregation: Within each window, Flink uses stateful operations like .reduce() or .aggregate() to process events. These operations—counting clicks, summing impressions, or finding maximums—run incrementally to maintain low latency.
Flink flush intervals: it enables aggregation on minute boundaries while flushing results every few seconds. This provides optimal performance while maintaining accuracy—the current minute's data remains incomplete until the minute boundary is reached, which aligns with expected behavior.
Stateful Processing and Fault Tolerance: Flink keeps state locally for each window and periodically checkpoints it to external storage. This ensures fault tolerance—if a failure occurs, Flink recovers from the latest checkpoint without losing data.

Database Design

Designing a database for an Ad Click Aggregation System requires a schema that efficiently tracks, aggregates, and retrieves click data for reporting and analytics purposes.

Option 1: NoSQL Database:

Key Design Focus: Optimized for real-time, high-speed transactional workloads and flexible data models.
When to Use:
- Schema flexibility: Dynamic or semi-structured data that may change over time.
- Horizontal scalability: Need to handle large volumes of data with distributed architecture.
- High write/read throughput: Applications with frequent, low-latency read/write operations.
- Unstructured/JSON data: Documents, key-value pairs, graphs, or wide-column data storage.
Trade-offs:
- Typically lacks advanced analytical capabilities.+
- Querying might require custom logic or application-side joins.

Table: Ads: This table stores metadata for each ad.

ColumnTypeDescriptionad_idBIGINT (PK)Unique identifier for the ad.advertiser_idBIGINTReference to the advertiser.campaign_idBIGINTReference to the campaign this ad belongs to.redirect_urlTEXTURL where users are redirected.created_atTIMESTAMPAd creation timestamp.statusENUMActive, Paused, Archived, etc.

Table: Clicks This table stores individual click events using a time-series design for high-volume data handling.

ColumnTypeDescriptionclick_idBIGINT (PK)Auto-generated unique identifier for the click.ad_idBIGINT (FK)Reference to the clicked ad.clicked_atTIMESTAMPClick timestamp.user_ipINETUser's IP address.deviceVARCHAR(255)Device type (mobile, desktop).user_agentTEXTBrowser user agent string.geo_locationVARCHAR(255)Geographic location (optional).

Table: Aggregated Clicks: This table stores pre-computed click metrics for efficient reporting.

ColumnTypeDescriptionad_idBIGINT (FK)Reference to the ad.campaign_idBIGINTReference to the campaign.time_bucketTIMESTAMPAggregation time period (hourly).click_countINTTotal clicks in this time period.

Indexing Strategy

For Clicks Table:
- Index on ad_id for ad-specific queries.
- Index on clicked_at for time-based queries.
- Index on user_ip for fraud detection.
For Aggregated Clicks Table:
- Composite index on ad_id and time_bucket for fast aggregation lookups.

Sharding Strategy

Sharding partitions data across multiple database servers (shards) to distribute the write load. This enables write-heavy systems to scale horizontally by spreading writes across multiple nodes. Some databases and storage engines are optimized for write-heavy workloads, employing techniques like log-structured merge (LSM) trees and **write-ahead logging (WAL)**to efficiently handle high write throughput.

Select an appropriate sharding key that evenly distributes data across shards.
- Partition Clicks table by clicked_at using daily intervals.
- Partition Aggregated Clicks table by time_bucket.
Balance writes across shards to prevent overloading any single shard.
- Distribute load by sharding on ad_id.
Prepare for resharding as the system expands to maintain even distribution of new data.
Utilize databases like Cassandra, HBase, or LevelDB, which are engineered for high write throughput and employ LSM trees for efficient disk writes.
Implement write-ahead logs (WAL) to ensure write operations are safely recorded before commitment, guaranteeing durability even in the event of a failure.
Caching: Cache frequently accessed aggregated data using Redis.
Staff+ propose using Time-Series Databases: Consider TimescaleDB or ClickHouse for time-series data optimization.

`[Staff+ candidate]` Option 2: OLAP Engine:

Key Design Focus: Optimized for complex, analytical workloads on large datasets.
When to Use:
- Aggregations and analytics: Need to perform multidimensional queries, rollups, and drill-downs efficiently.
- Data warehousing: Centralized storage of historical data for business intelligence.
- Read-heavy: Optimized for batch queries and aggregations rather than frequent updates.
- Structured data: Use cases like sales analysis, user behavior trends, or financial reporting.
Trade-offs:
- Usually not suited for high-frequency writes or real-time data operations.
- Requires pre-processed, well-structured data pipelines with second level latency

Hybrid Use Case:

In modern architectures, NoSQL databases (like MongoDB or Cassandra) can act as the data ingestion layer for real-time operations, while an OLAP engine (like Snowflake or ClickHouse) can serve as the analytical layer. Tools like ETL pipelines (e.g., Airflow, Kafka) can bridge the gap.

Other Key Considerations

Fraud Detection:
- Monitor for rapid clicks from single user_ip addresses.
- Track suspicious patterns in a dedicated table.
GDPR/CCPA Compliance:
- Implement user data anonymization.
- Support data deletion requests.

Deep Dive

Scalability

Example follow up?

How to handle extremely popular ads?

Stream with Message Queue (Kafka or Kinesis): Kafka is inherently scalable distributed systems. Adding shards increases stream capacity, with each shard functioning independently. Sharding by ad_id distributes data across multiple shards for parallel processing, enabling efficient handling of high-volume event streams.

Pre-aggregation Stream Processor (e.g., Flink):Stream processors scale horizontally through additional tasks or jobs. By assigning each stream shard to a dedicated Flink job, ad_id values can be aggregated independently within shards.

NoSQL/OLAP :Scale horizontally by adding nodes and distributing data across them. Sharding by ad_id enhances query performance when advertisers need aggregated data across their ads. Regular monitoring and dynamic adjustment of sharding strategies maintain database performance as usage patterns change.

**Hot Shards:**To address the challenge of hot shards (e.g., a popular ad overwhelming its shard), further partitioning is applied. By appending a random number to the ad_idfor popular ads (e.g.,ad_id_N), data for a single ad is spread across multiple shards. This dynamic partitioning mitigates the risk of bottlenecks, ensuring that spikes in popularity do not impact performance or lead to data loss.

Accuracy

Example follow up?

How can we ensure that we don't lose any click data?

Click data is critical - lost clicks mean lost revenue. We need to ensure data accuracy while balancing it against low latency requirements. We can achieve this balance through periodic reconciliation.

Even with robust systems in place, issues can arise. Transient processing errors in Flink, problematic code deployments, or out-of-order stream events can create data inaccuracies. To address this, we implement an hourly or daily reconciliation process.

Alongside our stream processors, we store raw click events in a data lake like S3. Flink's FileSystem interface and connectors enable both real-time processing and direct storage in S3 buckets. Following the approach outlined in "Advertisers can query ad click metrics over time at 1-minute intervals," we run batch jobs to re-aggregate raw click events from the data lake. By comparing these results with the stream processor output, we can identify mismatches, investigate their causes, and correct the database values accordingly.

Durability by Replication

In write-heavy systems, ensuring data safety during failures is crucial. Replication creates multiple copies of data across different servers, providing durability and fault tolerance.

Implement synchronous replication for critical data, ensuring writes are replicated to multiple nodes before acknowledgment.
Use asynchronous replication for less critical data to reduce write latency while maintaining some fault tolerance.
Employ quorum-based writes (writing to a majority of replicas) to balance consistency and availability.

[Staff+] Ads abuse prevention and dedup

Log userId (from JWT or session cookie) with adId and deduplicate clicks before streaming to ensure accuracy across aggregation windows. It is not feasible to assumes all users are logged in. Fails to account for retargeting, where multiple clicks per user per ad instance should be tracked.

To handle deduplication across aggregation windows, we deduplicate clicks before they enter the message queue:

Cache-Based Deduplication:
- When a click is received, check if the impression ID exists in a cache.
- If it exists, ignore the click (it's a duplicate).
- If it doesn't exist, add the impression ID to the cache and process the click.
Mitigating Fake Clicks:
- The Ad Placement Service signs impression IDs with a secret key and sends them to the browser with each ad.
- When receiving a click, we verify the impression ID's signature before checking the cache.

Challenges

Added Complexity: Adding signature verification and cache integration makes the system more complex.
Cache Scaling: While the cache could become a bottleneck, the data size remains manageable (about 1.6 GB for 100M daily impressions). Distributed caches like Redis Cluster or Memcached provide effective scaling.
Cache Reliability: We need cache replicas and persistence to prevent data loss during failures.

This approach provides robust deduplication while protecting against malicious clicks.

[Note] As a staff eng, you should proactively discuss whether adding additional complexity worth or not.

[Staff+] How to optimize historical data storage to save cost?

Write-heavy systems often face write amplification, where multiple writes are needed to store a single piece of data. Compaction mitigates this issue by merging or compressing data after a certain number of writes.

Ensure efficient compaction, especially in systems with frequent updates or deletions.
Manage write amplification by optimizing data storage—minimize random writes and reduce redundant operations.

Example: Cassandra uses compaction to merge older data files, reducing storage space and preventing excessive write amplification.

‍

Coach + Mock

Practice with a Senior+ engineer who just get an offer from your dream (FANNG) companies.

Schedule Now

Content: