Design event-time click analytics

01Decide what the number means

A click analytics system turns identified events into counts and rankings. Begin by asking whether the metric is received requests, validated clicks, unique people or billable interactions. These require different identities and validation. We count validated clicks by when they occurred, with minute counts, campaign rollups and hourly top 100 ads. Financial settlement and fraud-model training are separate products.

Click C901 belongs to ad A7 and occurred at 09:00:58, but reaches the collector at 09:02:05. It must revise the 09:00 minute, taking the count from 99 to 100. A repeated delivery of C901 must leave 100 unchanged. A second legitimate click with a different accepted identity may count separately; uniqueness of transport identity does not prove legitimacy.

The complete initial flow is validate → durably append → identify the event-time window → atomically record identity and contribution → expose the count. A single database and processor can implement it. Distribution is justified later by throughput, state size and recovery time, while this meaning of the count remains unchanged.

02Functional requirements

Agree on what the service must do before choosing its components.

1. Accept validated clicks. Receive bounded batches with trusted event identity, ad identity and occurrence time; report acceptance after durable storage. Identity distinguishes a retry from another event, not a bot from a person.
2. Serve counts and rankings. Return event-time minute counts, campaign rollups and hourly top 100 ads under a named metric and validation policy.
3. Explain revisions. Expose metric/policy version, update time, revision, progress and preliminary/final status. Let allowed late events revise their original window.
4. Inspect delayed evidence. Retain beyond-policy events for investigation. Financial settlement, fraud-model training, historical audit snapshots and privileged historical rebuilds are separately controlled extensions.

03Non-functional requirements

Use these hypothetical requirements for the worked interview. Confirm the assumptions with the interviewer; the numerical targets require testing and are not measured results. For latency, p95 and p99 mean that 95% and 99% of measured delays, respectively, are no greater than the reported value.

1. Workload. Plan for one billion events/day, 200,000/s at peak and 200-byte payloads. Include duplicates, hot ads and catch-up work when testing capacity.
2. Latency and availability. Target normal accepted-to-dashboard delay within five seconds, ingestion p95 below 100 ms, narrow query p95 below 200 ms and 99.9% ingestion availability under admitted load.
3. Durability. Accepted events must survive one zone failure under the replicated-log policy. Retained input and recovery capacity are finite; stop admitting work before accepted input becomes unrecoverable.
4. Counting and finality. One trusted event identity contributes once within the supported replay window. Allow two minutes of ordinary lateness after the window end according to declared event-time progress. Final means closed under that policy, not that no older event can arrive. Reports expose incompleteness rather than treating a missing shard as zero; independent live counters do not form one global snapshot.
5. Retention and recovery. Retain raw envelopes for 30 days and online event identities for 24 hours. Never treat an expired identity as a fresh click on sender retry or internal replay; older reconstruction needs a controlled rebuild.
6. Security and privacy. Authenticate collectors and campaign queries, validate provenance and timestamp ranges, minimize user identifiers and restrict raw evidence. Define deletion treatment for derived data.

04Make one transactional counter recoverable

Start with a durable queue, one processor and one transactional database. The collector validates a request and acknowledges acceptance only after the queue durably stores it. The processor handles C901 as follows:

1. Begin the transaction. Check the trusted scoped identity and payload fingerprint.
2. Apply a new contribution atomically. Insert the processed identity and update A7’s 09:00 count from 99 to 100 in that transaction.
3. Commit. Make the identity and increment durable together.
4. Acknowledge input. Only after commit, acknowledge the input to the queue.

If the transaction commits but the input acknowledgement is lost, the queue can deliver C901 again. The saved identity makes the repeated transaction leave the count unchanged. If the transaction fails, neither the identity nor the increment survives, so replay can apply both. Recording identity and count in separate transactions could instead lose or duplicate a contribution.

Queries read committed counters. A small hourly report sums each ad’s minute counters and sorts the complete totals on that machine. Results remain preliminary until the chosen lateness policy closes their windows. No stream framework is needed to demonstrate this working flow.

The baseline reaches its limit when database write throughput, identity storage or recovery time exceeds its budget. A hot ad can dominate one row while other counters remain idle. Partitioning and batching should address those measured limits while preserving the same transaction boundary.

05Understand event time and report finality

Event time is occurrence time; processing time is when a worker handles the event. A watermark is declared progress through event time used to decide when to emit or close windows. Allow two minutes of ordinary lateness after the window end according to that progress. Final means closed under this policy, not proof that no older event can ever arrive.

Beyond-policy data is retained for investigation or a separately controlled rebuild. An expired online identity must not silently become a fresh click merely because deduplication state was removed. This applies to internal queue replay as well as sender retries.

06Count raw bytes, identity state and recovery work

Assume one billion events/day, a 200,000/s peak and 200-byte payloads. Average rate is about 11,574/s, while peak payload is 40 MB/s. The large peak-to-average difference makes durable buffering useful, but retained input is finite and cannot absorb an unlimited outage.

The 32 GB estimate covers only raw identity payload, not database indexes, fingerprints and storage overhead. Larger batches reduce per-transaction work but may increase time before a count becomes visible. Country and device dimensions also multiply distinct counter keys. Bound supported dimensions and benchmark the database with realistic duplicates and skew before choosing batch size or the number of partitions.

07Separate evidence, identity and counters

Click ingestion

POST /clicks

Acceptance follows durable append; it does not mean the event is billable or already visible. Retry an uncertain response with the same identity and immutable payload. Reject conflicting content under one identity.

Identity and counter keys

Deduplicate by trusted event identity before a changed ad field could send a conflicting retry to another counter.

Stored evidence and results

Count-query response

This design promises timely useful reports, not one globally instantaneous snapshot across every independently updating shard. A stronger reproducible cross-shard report is a follow-up with additional coordination and retained snapshot costs.

08Partition independent ads and protect hot ones

First use a replicated durable queue or log so intake can continue through bounded processor downtime. Collectors acknowledge only its configured durable append. Retention and admitted input are finite; expose lag and reduce new admission before an outage can make accepted work unrecoverable.

Partition ordinary processing by ad so related window updates have a clear owner. Different ads can progress independently. Batch database work to amortize transaction overhead, while keeping each event identity and count contribution in the same committed transaction. A retrying batch checks every event identity rather than assuming the entire previous attempt failed.

Measure skew before splitting keys. If A7 receives 40,000 events/s, distribute it across 16 partial counter keys using a stable suffix derived from event identity. Each sees roughly 2,500/s on average. A report combines all required partial counts for A7 before presenting its total. Stable event identity still governs whether a retried click contributes again.

The cost is extra partial state and report work; ordinary ads should retain the simpler path. A counter shard outage can make a report late or incomplete, so disclose that state instead of interpreting a missing partial as zero. More API servers do not repair a hot database row; batching, placement and bounded admission address the actual write work.

09Use one stream framework with a defined output boundary

For the larger workload, Kafka can retain identified input and Flink can provide partitioned processing, event-time progress and recovery checkpoints. A checkpoint records processing state and the input positions from which it can resume. Explain what the counter does when recovery repeats an event; naming the framework does not answer that question.

Choose an idempotent transactional database sink for this design. For each accepted contribution, its transaction inserts the processed-event identity and changes the appropriate counter together. If that identity already exists with the same fingerprint, the sink makes no second change. Several events may share a batch transaction. Record that the input has been processed only after confirming the database transaction committed.

A crash after the counter transaction but before checkpoint/progress completion causes replay. The sink finds the saved identities and does not increment again. A crash before commit leaves neither effect nor identity, so replay applies the work. This is the same baseline proof at a larger processing scale.

Flink’s internal recovery guarantee does not make an arbitrary external write transactional. If we later replace the sink with bulk immutable output or another database, verify its checkpoint-aware or idempotent integration explicitly. Detailed generation-publication protocols are unnecessary to explain this selected sink contract.

Never automatically replay input beyond the retained identity coverage. If recovery needs older input, stop and rebuild the affected counters from retained raw evidence under the controlled rebuild process before resuming. Otherwise a previously committed count could be incremented again after its identity expired. Retention is part of this recovery contract, not just storage cleanup.

10Explain a late event to a dashboard reader

Late-event timeline

With several active inputs, the slowest relevant watermark limits combined progress. Otherwise one delayed partition would be excluded while a result falsely claims completeness. An idle-input policy can permit progress, but data arriving when that input returns still follows the late-data rule. Idleness is not proof that old events no longer exist.

A query authenticates campaign scope and reads compatible committed metric/policy rows. It returns their update time, revision and finality. Paginate a saved report when stable pages matter; otherwise disclose that live counts can change between requests. Do not imply that a cursor alone freezes the dataset.

When the window closes under policy, later evidence enters a correction stream. Do not advance watermarks just to improve a freshness chart. A lagging honest result is different from an apparently final result produced by suppressing inconvenient inputs.

11Build useful reports from complete per-ad totals

The dashboard reads committed minute counters, then groups compatible metric/policy values for campaign summaries. A periodic report job sums an ad’s minute and partial counters for the requested hour before sorting ads for top 100. Keep deterministic tie handling and state the report’s build time and completeness. Those independently changing counters may have been read at different times.

Do not rank partial contributors before summing each ad. If Red has 6 on each of two workers, it totals 12 even though local Blue = 7 and Green = 7 beat it separately. The report needs Red’s complete total first. This simple example is enough to explain why a fast local winner list is not automatically a valid hourly report.

Closed windows give more stable reports, but final still means closed under the stated lateness policy. A missing shard or unresolved input delay cannot be silently counted as zero. Return an older labeled completed report, expose incompleteness or fail the precise report request.

Beyond-policy events remain in a correction queue for investigation or a controlled historical rebuild from retained raw evidence. Keep the main product’s live counting path simple. A separately versioned, reproducible global correction/report system is a worthwhile extension when audit requirements demand it, with its ownership and publication protocol designed explicitly.

12Recover input, control cost and audit revisions

Monitor accepted-to-visible lag, oldest needed input offset, checkpoint duration, watermark age, late-event rates, duplicate conflicts, hot-key skew and correction differences. A high ingestion success rate can coexist with a stalled dashboard. Alert before retention deletes input still needed for recovery, and ensure processors have net capacity to drain backlog.

Authenticate collectors and campaign queries. Validate timestamp ranges and provenance so forged far-future events cannot drive progress. Minimize user identifiers, restrict raw-event access and define deletion treatment for derived datasets. A valid event ID is not an anti-bot mechanism: a malicious client can invent fresh IDs unless an upstream trusted contract limits them.

Test crashes before and after the sink transaction, duplicate collector batches, an idle source returning late data and a counter shard disappearing during report construction. Compare totals with an offline recount of a fixed retained source interval under the same policy.

Costs follow raw retention, identity lifetime, checkpoint state and dimensionality. Seven days of 32-GB/day raw identity payload would be 224 GB before indexes. Limiting live retries and correcting older data in a controlled batch can cost less than retaining every event identity forever.

13Check the design against its requirements

Before closing, check the final design against the agreed requirements. FR means functional requirement and NFR means non-functional requirement; the numbers refer to the lists above. These are proposed validation checks, not test results.

14Rapid revision

Rehearse the numbered functional requirements and non-functional targets first. Use this table to recall the mechanisms, then close with the requirements check above.

Remember: Save identity with the count; retries cannot count twice.

Close with: “I define validated event-time clicks, then start with a durable log and a transactional counter that records each processed identity. At scale I partition and batch that same work, using one stream framework with a verified idempotent sink. Late data updates its original window under a clear policy. Reports combine complete per-ad totals and honestly show age or incompleteness.”

Recall the key ideas

Answer from memory before opening each card. Explain why the choice works and what it costs. Revisit missed cards tomorrow.