Review: Ad Click Aggregator
Count ad clicks per campaign and time window while accepting retries, late arrivals, and reprocessing.
Clarify this prompt before choosing components
Ask the highest-impact questions first, agree on priorities, and record the interviewer’s answers. If a detail is unspecified, state an assumption and explain which decision depends on it. The numbers in this worked lesson are exercise assumptions, not company facts.
| Ask the interviewer | Why the answer changes the design |
|---|---|
| Billing-grade exact accounting or exploratory analytics? | Choose a declared accuracy and correction contract. |
| Duplicate/fraud clicks and event-time attribution windows? | Stable identities, validation and late corrections become requirements. |
| Peak events/s, reporting lag and backfill duration? | Partition sizing, retained replay and serving freshness follow these limits. |
Use the Interview Playbook for the shared 45-minute sequence and fresher, intermediate, senior and staff evidence. Start with one complete operation. Extend the answer with the failure/recovery traces below, then defend a workload-specific alternative and a measurable rollout. Scope and evidence matter more than a large component list.
Ad Click Aggregator: Understand the building blocks
Choose a mechanism because it satisfies a requirement. These explanations describe the exercise design, not undisclosed implementation choices at the named company.
Ad Click Aggregator: Quick vocabulary
| Term | Meaning and role in this exercise |
|---|---|
| API: Application Programming Interface | The contract for requests, responses and errors between components. An API route does not itself enforce every business invariant. |
Ad Click Aggregator: Event time, watermark and windows
What it means: Event time is when an event happened; arrival time is when it reached this processor. A window defines the interval grouped for computation, and a watermark expresses progress under a lateness policy.
Why this problem needs it: Billing/reporting must distinguish event time and arrival time.
How to use it: Bind each event to its window identity, deduplicate under a declared horizon, and emit allowed revisions or reject/quarantine events past the finality boundary.
Example in this design: A permitted late click revises its original window instead of silently counting in the next one.
When it fits: Useful when delayed delivery should not silently move activity into the wrong reporting period.
Alternative and limits: Processing-time windows are simpler if arrival time is the intended business meaning. A watermark is not proof no older event can ever arrive.
Ad Click Aggregator: Deduplication identity and business qualification
What it means: Deduplication distinguishes repeated delivery of one event from genuinely separate events. Qualification decides whether a real event is eligible to count.
Why this problem needs it: Repeated transport and an invalid real click require different decisions.
How to use it: Use a stable event identity and atomic replay-safe sink, then evaluate the business rule with its own revision/window policy.
Example in this design: Duplicate e1 plus invalid e2 yields one qualified click under the exercise rule.
When it fits: Needed when delivery may repeat and reports/billing must not count the same event twice.
Alternative and limits: An append-only raw log preserves evidence but needs a later qualified projection. Transport deduplication alone cannot detect fraud or define billable behavior.
Ad Click Aggregator: Kafka partitioned event log
What it means: Kafka retains ordered records within partitions; consumers track offsets and can replay retained history. Partitioning distributes work.
Why this problem needs it: A retained stream can feed independent reports and replay.
How to use it: Pick the business ordering key, replication/acknowledgement policy and consumer checkpoint boundary. Coordinate replay with the output sink’s actual commit protocol.
Example in this design: The checkpoint/output boundary must handle a worker dying after its sink commit.
When it fits: Useful when multiple consumers need retained event history or ordered stream processing.
Alternative and limits: A task queue fits job claiming without general history replay. Kafka transactions do not automatically make an external email/payment side effect exactly once.
Ad Click Aggregator: Ledger and immutable revisions
What it means: A ledger is authoritative history of recorded monetary or obligation changes. Immutable revisions keep corrections auditable rather than overwriting the past.
Why this problem needs it: If qualified events become invoices, financial finality needs a separate auditable boundary.
How to use it: Commit an expense/revision and its balanced deltas together, use integer minor units with a currency policy, and reconstruct balances from accepted facts.
Example in this design: Correct a billed interval through an explicit revision; transport deduplication is not the invoice policy.
When it fits: Useful when edits, retries and reconciliation must preserve an explainable balance.
Alternative and limits: Mutable balances can be faster to read but still need safe revision history and reconciliation. Recording a settlement is not proof an external payment occurred.
Interview scope and guarantees
Ingest click events, deduplicate transport retries, aggregate by campaign and time, and expose provisional and finalized totals. Fraud classification is a separate versioned process. Define whether billing uses these totals or a separately reconciled ledger.
Capacity worksheet
Assume 2 billion events/day: 23,148/s average and 115,741/s at 5× peak. At 200 bytes/event that is 400 GB/day raw. A 30-day replay log is 12 TB before replicas. Partitioning only by campaign can overload the shard for one major advertiser.
Concrete API contract
POST /clicks {eventId,campaignId,adId,eventTime,impressionId}
GET /campaigns/{id}/counts?from=...&to=...
Return count, asOf, watermark, status and aggregationVersion.Data model and access paths
raw_clicks(event_id,campaign_id,event_time,ingest_time,payload_version)
partial_counts(campaign_id,bucket,salt,count,checkpoint)
final_counts(campaign_id,bucket,version,count,status)
dedup(event_id,expires_at)Evolve a solution and explain each change
Read the diagram
Record raw clicks / Accept validated event identities into a durable log. / Synchronous counter writes are vulnerable to retries and hot campaigns.; Aggregate event windows / Key state by campaign/window and checkpoint progress with output semantics. / Late events and reprocessing can double count materialized totals.; Separate estimates from billing / Expose provisional dashboards; finalize billable records with corrections and auditability. / A displayed count must not silently become an invoice.
Step 1: Record raw clicks
Accept validated event identities into a durable log. Synchronous counter writes are vulnerable to retries and hot campaigns.
Step 2: Aggregate event windows
Key state by campaign/window and checkpoint progress with output semantics. Late events and reprocessing can double count materialized totals.
Step 3: Separate estimates from billing
Expose provisional dashboards; finalize billable records with corrections and auditability. A displayed count must not silently become an invoice.
Responsibility overview
Read the diagram
Click client to Ingest API: click event ID; Ingest API to Raw event log: commit raw event; Raw event log to Window worker: replay keyed events; Window worker to Aggregate store: idempotent window revision; Advertiser to Aggregate store: read provisional totals; Window worker to Raw event log: checkpoint progress
Worked end-to-end scenario
Click e17 arrives twice after a client retry. Ingestion validates the ad/campaign association and retains enough identity to deduplicate within policy. An aggregator updates the ten-minute campaign window. It crashes after writing a total but before checkpointing its input offset. On replay, a naive increment counts e17 again. Either coordinate checkpoint and output through the selected processing model or write deterministic versioned aggregates that replace the same window result idempotently. A click arriving after the watermark revises the provisional dashboard or becomes a correction under the billing contract; it does not disappear without explanation.
Why these access paths matter
Keep immutable raw events partitioned for replay and aggregate rows by campaign, window, and revision. Event time and ingestion time answer different questions. Dedup retention must cover the retry/replay horizon. Hot campaigns may need partial aggregates merged into an exact total; the merge key must include the input range or revision to avoid adding the same partial twice.
Build the baseline first
Read the diagram
Validated event → durable log → partition consumer; Event-ID dedup → time bucket → counter update; Bucket totals → query store → dashboard
- Validated event → durable log → partition consumer.
- Event-ID dedup → time bucket → counter update.
- Bucket totals → query store → dashboard.
Evolve the design under load
Read the diagram
Hot campaign → salted partial aggregates; Partial merge → campaign totals → versioned publication; Raw archive → bounded replay → replacement generation
- Hot campaign → salted partial aggregates.
- Partial merge → campaign totals → versioned publication.
- Raw archive → bounded replay → replacement generation.
Defend the hardest decision
Use event time for business windows and ingestion time to diagnose delay. A watermark expresses progress, not proof that no late event can ever arrive. Define an allowed lateness period, then publish corrections or quarantine later events. If an event ID is retained for only one day but the log can be replayed for thirty, a replay can double count unless the sink uses a generation or source-position transaction.
Failure and recovery analysis
A processor commits an aggregate and crashes before advancing its offset. Replay must not increment the same contribution again. Options include transactional state/checkpoints or storing idempotent per-batch deltas keyed by source ranges. Reprocessing fraud rules should create a new version rather than silently rewriting billing history.
Security and privacy boundary
Validate campaign ownership for queries, rate-limit ingestion abuse, and minimize personal tracking fields.
Interview follow-ups with reasoning
Question: How do you handle campaign skew?
Show answer and explanation
Answer: Salt partial keys, then merge bounded subaggregates.
Question: How do you validate correctness?
Show answer and explanation
Answer: Compare exact raw-event totals for sampled buckets with the published generation.
Question: How do you recover from a bad deployment?
Show answer and explanation
Answer: Replay into a parallel generation and switch queries after reconciliation.
Operate and verify the design
Source lag, dedup hits, late-event rate and aggregate-to-raw discrepancies.
Crash after aggregate commit but before offset advancement and confirm no double increment.
A second scenario to test transfer
Three events arrive at 10:04: a new click that occurred at 10:03, a retry of an already counted 10:03 click, and a delayed click from 10:02. The first increments the 10:03 window. The second changes nothing. The third updates 10:02 according to the late-arrival policy. Simply incrementing the current minute for all three would get both identity and time wrong.
Walk through a duplicate arriving after the normal dedupe retention window. What can the service honestly guarantee?
Show answer and explanation
Answer: The short-lived dedupe state alone cannot recognize it. Either retain durable identity evidence for the billing/replay horizon, restrict the accepted replay contract, or reconcile against an authoritative ledger. State the limitation rather than promising unbounded exactly-once counting.
Compare alternatives
| Choice | Advantage | Consequence |
|---|---|---|
| Event-time windows | Reflect actual event time | Late-data policy required |
| Ingestion-time windows | Simple operational timing | Delayed events shift periods |
| Provisional totals | Fast feedback | Visible corrections |
| Billing finalization | Stronger audit boundary | More latency and reconciliation |
A design-changing exercise
A processor advertises exactly-once state updates. Are external invoice writes automatically exactly once?
Show answer and explanation
Answer: No. The guarantee must include the sink boundary. Use sink transactions, idempotent records, or reconciliation with a durable billing ledger.
Design workshop: checkpoint one aggregate revision
Core scope is eligible click ingestion, minute/hour reporting and reconciled billable totals. Fraud scoring is upstream. Choose provisional dashboard refresh every ten seconds, two-minute event-time lateness and durable raw history for a 30-day reconciliation horizon. A billing invoice is a separately finalized object, not the latest dashboard number.
Choose a transactional batch sink: each partition processes a contiguous source range, writes event dedup identities and aggregate deltas, and advances its durable offset in the same database transaction. On restart, seek from that database offset. Do not both commit an unrelated broker offset early and claim the database output cannot be lost. If a stream engine owns the checkpoint, its external-sink integration needs an equivalent recoverable contract.
BEGIN;
SELECT next_offset FROM source_progress WHERE partition=:p FOR UPDATE;
-- Require the batch to start at the recorded offset.
-- Insert unique event IDs; apply only newly inserted eligible deltas.
-- Update campaign/window counters and their revision IDs.
UPDATE source_progress SET next_offset=:end_exclusive WHERE partition=:p;
COMMIT;This transaction belongs to one owned input/sink shard, not one global database. Partition source ranges so their state keys and progress record co-reside, and scale sink shards independently. A range spanning independent sinks needs a different checkpoint protocol; it cannot reuse this single-transaction proof.
Dedup rows retain identities for the accepted replay horizon. If operators replay older history after short dedup retention, rebuild a new projection from authoritative events rather than append again to the live counters. Stable batch-range uniqueness prevents duplicate batch commits but does not by itself remove producer-created duplicates carrying different offsets with the same event ID.
Read the diagram
Batch offsets 100..119; Dedup events, apply deltas, set next_offset=120; Commit succeeds but acknowledgement lost; Read next_offset after restart: 120; Resume at 120, not increment 100..119 again; Read campaign/window revision and provisional flag
Salt a hot campaign into S subkeys by event identity. If a minute has salt totals [400,300,200,100], merge to 1,000 before campaign reporting. Checkpoints must identify complete salt revisions; adding an old partial total to a new one produces an invalid window. Negative fraud corrections reference the original eligible click and are applied once, yielding a new aggregate revision.
Read the diagram
Input / Window effect / Displayed status; 10:03 click arrives 10:04 / 10:03 +1 / Provisional revision increases; Same event ID replayed / No change / Duplicate counted as processing metric only; 10:02 click arrives before watermark closure / 10:02 +1 / Late but accepted correction; Fraud reversal of one counted click / Original window -1 once / Revised total with audit identity; Invoice finalized at 1,000 then valid correction -1 / Adjustment of -1 / Do not silently rewrite an issued invoice
Use the minimum of active input watermarks to close a window; an idle partition needs explicit idle detection and re-entry late-data policy. A stalled partition cannot silently be ignored without changing completeness. Store rollups by campaign and window for advertiser reads, with currency/rate version only at billing boundaries. Reconcile: accepted eligible IDs − reversed IDs = finalized billable count; compare independently derived raw-history totals against projection totals.
For one million events/s at 100 bytes, raw ingestion is 100 MB/s and 8.64 TB/day before compression/replication. State size depends on active campaigns, dedup horizon and buckets, not only event throughput. Alert on offset lag, watermark lag, duplicate fraction, correction age and reconciliation delta; keep dashboard freshness separate from billing finality.
Exercise: Batch commit succeeds but the worker dies before a broker offset commit. Will replay double bill?
Show answer and explanation
Answer: This selected design resumes from the sink's transactional next_offset and deduplicates stable event IDs. If another consumer incorrectly follows only a stale broker offset, the source-progress guard rejects the already committed range. Invoice creation has its own unique finalized billing identity.
Qualify a click after retry deduplication
Transport deduplication removes repeated event identities before a versioned policy decides whether a click qualifies.
Read the diagram
Raw event keeps a stable event ID and validated schema → Transport dedup rejects a repeated ID inside its replay horizon → Versioned campaign and fraud rules decide business qualification → Idempotent sink applies the qualified window revision → Checkpoint records input progress; replay preserves the result → Dashboard totals can be provisional; billing needs versioned reconciliation
Requirement trace and verification
| Requirement | Implementing design block | Verification link |
|---|---|---|
| R1 — Complete the primary user journey within the stated scope. | P01–P04; P09–P10. | V1 — Exercise the successful journey and the excluded/boundary case stated in the scope. |
| R2 — Preserve the problem's authoritative data invariant under concurrency. | P05–P06; P11–P12; DD1. | V2 — Run the race/replay case below and assert the invariant at the durable authority. |
| R3 — Meet the declared scale and API behavior without unbounded work. | P05–P08; P13–P14; relevant overload deep dives. | V3 — Exercise the API errors/retry identity and recompute the workload assumption that drives partition or capacity. |
| R4 — Recover from the named failure without losing or duplicating the user-visible outcome. | P15–P16; deep dives; Operations contract. | V4 — Deliver the same click repeatedly, restart after a sink write, and send late events; reconciled aggregates match qualified raw events. |
Operational contract
| Area | Decision and signals |
|---|---|
| SLIs | Source lag; dedupe hit rate; late-event rate; dropped-event count; aggregate-to-raw reconciliation delta. |
| Overload limits | Bound redirect event admission, queue depth, per-ad key throughput, and replay batch size. |
| Recovery | Replay immutable event IDs from checkpoints and reconcile aggregates against raw partitions before publishing corrections. |
| Access controls | Validate signed click tokens; minimize user identifiers and restrict raw-event access/retention. |
| Dominant cost | Event ingestion/storage and long-term aggregate retention. |
Senior-level interviewer follow-ups
Question: A sink write commits, then the worker crashes before its input offset advances. How do you avoid double counting?
Show answer and explanation
Answer: Commit the aggregate with a stable input range or event identity and advance the owned offset in the same sink transaction where possible. Otherwise use an idempotent output key and reconcile replay; state the boundary when multiple sinks cannot be atomic.
Question: How do you represent corrections after a late click changes a billed window?
Show answer and explanation
Answer: Emit a versioned delta or replacement aggregate keyed by campaign and window, retain lineage to the source watermark, and make downstream invoices consume one chosen version.
A 60-second design answer
I would ingest durable click events with event IDs and partition ownership, then aggregate by campaign and event-time window. Each worker checkpoints only the input range whose output is durable; deduplication and versioned corrections handle replay and late data. Separate serving aggregates from billing-grade reconciliation. I would watch lag, duplicate rate, watermark delay, correction volume, and output-to-input lineage.
Related concepts to revisit
Technical references
Kafka processing and external-sink boundaries.
PostgreSQL transaction isolation and concurrent updates.
Count a replay once and preserve correction provenance
Valid impression i7 produces click c7; i8 produces c8. Replaying c7 should leave two counted clicks, not three. Store a trusted impression/click identity and validity policy, then aggregate into the intended event-time minute. Invalidating a fraudulent click later is a correction with provenance, not an untracked decrement.
raw: c7/i7, c8/i8, replay c7/i7
valid unique clicks: c7,c8 -> count 2
window result: revision 1
later invalidation c8 -> revision 2, count 1
Read the diagram
raw: c7/i7, c8/i8, replay c7/i7; valid unique clicks: c7,c8 -> count 2; window result: revision 1; later invalidation c8 -> revision 2, count 1
Redirect latency and durable analytics acceptance are separate user promises. A provider/client timestamp can be untrusted; state how occurrence time is established and late input handled. Query-serving aggregates and raw retained events have different costs and recovery roles. Rebuild from retained valid facts under compatible deduplication horizons, and cap tenant/advertiser query fanout.
Show answer and explanation
Answer: A client-supplied user ID does not prove a real impression or defeat automated abuse. The aggregator's durable identity protects retries; a validity/fraud policy protects what is counted.
Decision review: Ad Click Aggregator
Ad Click Aggregator: What it is
A click aggregation exercise separates raw event acceptance, transport deduplication and qualified business counts.
Ad Click Aggregator: Why it is needed
A unique event can still be invalid or non-billable, while a repeated valid event must not increment a sink twice.
Ad Click Aggregator: When to use it
Use versioned qualification and event-time correction when analytics or billing needs reproducible results under replay and late input.
Ad Click Aggregator: How to use it
Keep raw event identity and provenance, apply declared fraud/campaign rules, publish aggregate revisions idempotently and reconcile the sink against its checkpoint and replay policy.
Ad Click Aggregator: Compare an alternative
A provisional dashboard can tolerate correction; settled billing may require a stronger reconciliation and version contract. Do not use the dashboard’s freshness policy as the billing rule.
Ad Click Aggregator: Worked decision exercise
The following inputs and policies are original exercise assumptions; they are not measurements or undisclosed company behavior.
Events e1 and e2 are distinct. e1 arrives twice and is eligible; e2 is unique but fails qualification. Transport dedup leaves two identities, but the qualified count is one. A later rule revision must be represented as an explicit correction.
Read the diagram
Raw inputs: e1, repeated e1, and e2; Transport dedup leaves e1 and e2; e2 fails declared qualification: count is 1; Billing uses versioned qualification and sink identity
Does an exactly-once stream checkpoint prove an arbitrary external billing API ran once?
Show answer and explanation
Answer: No. The external effect needs a compatible transactional/idempotent protocol or reconciliation. Keep progress commits from skipping unfinished earlier effects, and preserve the qualification version used for a charge.
Ad Click Aggregator: Check the design
Monitor raw, deduplicated and qualified totals separately, event-time lag and correction revisions. Replay a checkpoint with a sink response lost after success.