Design a user tracking event system with a top-K API

Functional Requirements

1. Clients should be able to emit tracking events, at high volume, without the system losing them.
2. A caller should be able to ask for the top k event types by count within [start, end).
3. New event types should be able to appear, and old ones retire, without a deploy.
4. A user should be able to have their tracking data erased and exported.

Below the line (out of scope):

• Arbitrary group-bys and filters on event properties. That is a full OLAP warehouse and it is a different system with a different storage engine. Say the cut out loud, because "top k by type" is a single fixed query shape and that is the only reason any of this is cheap.
• Sessionization, funnels, and per-user journeys. They need the raw events joined and ordered per user, which the counting path deliberately never does.
• Real-time alerting on thresholds, bot and fraud filtering, and billing-grade accuracy. Ad billing would change the durability bar from "do not lose events" to "prove you did not lose events".

Non-Functional Requirements

• Do the cardinality number first, because it decides everything below it.
  • 1,000 event types is the entire key space, and it is fixed by the product, not by the traffic.
  • One minute of the whole firehose collapses to 1,000 counts. At 8 bytes a count that is 8 KB a minute and 11.5 MB a day, so a year would be about 4 GB at minute grain if you kept one, and retention means you never do.
  • So the counts are EXACT. Count-Min Sketch, Space-Saving, and lossy counting are answers to a problem I do not have here. Say this deliberately, because the interviewer is expecting you to reach for a sketch and the point is that you knew not to.
  • Name the condition that would flip it, because that is what shows you understand why: if the "type" became a URL, a productId, or a userId, cardinality goes to 10^8 and the exact-counts plan dies. Then you sketch, or you keep exact counts for a tracked head and approximate the tail.
• Then the volume number, which sizes a completely different system.
  • 10B events a day is about 115K events/sec average, and call it 350K/sec at peak with a 3x diurnal factor.
  • At roughly 500 bytes an event that is 5 TB a day of raw data, so 450 TB at 90 days retention, before replication.
  • The two numbers point at two different systems. The raw bytes are the expensive one to store, replicate, and erase. The counting is almost free. Most candidates spend the whole hour on the cheap half.
• Reads are rare, writes are constant, which is the inverse of a feed. Dashboards and scheduled reports, order of tens to hundreds of requests a second against 17.5K write requests a second, which is 350K events a second. Nothing here should be designed as if reads were the load.
• Query latency p99 under about 100 ms, and the cost must not grow with the traffic.
  • Ten billion events a day and a hundred billion a day should cost the same to answer, because the answer is 1,000 numbers either way. That is the property the rollup tiers buy, and it is the one worth promising.
  • Be precise rather than sweeping: cost does still grow with the calendar, just very slowly. A day-aligned 30-day range is 112 bucket reads and a ragged one of similar length is about 193; a year is 447 aligned and about 528 ragged. Claiming a flat ceiling is the kind of thing an interviewer checks with a pencil.
• Freshness: a minute's counts are readable about three minutes after that minute began, and the API says so.
  • Where the three minutes go: one minute for the window itself, plus a two-minute watermark to let ordinary stragglers in.
  • Readable is not the same as final, and conflating them is the mistake. A bucket stays restatable for the 15-minute lateness window and can be rewritten again by the nightly backfill, so "final" is really the next day.
  • The tradeoff is explicit: exact once a window has settled, versus instant and approximate. Pick exact and settled, and make the incompleteness visible rather than silent.
• Durability: no event is silently dropped. At-least-once transport plus idempotent aggregation is what gets exactly-once counts, and those are two separate mechanisms rather than one guarantee.
• Correctness under replay: reprocessing a window must produce the same number it produced the first time. This is a design constraint on the sink, not a property you get for free from the framework.
• GDPR: erasure honoured inside the statutory month, which the design runs as a 30 day internal SLA, export on request, and personal data confined to a small, named set of stores rather than sprinkled across every derived table.

Defining the Core Entities

• Event is one tracked action: eventId, eventType, userId, eventTime, receivedAt, and a properties blob.
  • eventId is a UUID the client generates before its first attempt, and it is the idempotency key. Nothing else can make a retry safe.
  • There are two timestamps and choosing between them is a design decision, not a detail.
    • eventTime is the client's clock: it is what the user actually did and when, and it is wrong, skewed, and user-settable.
    • receivedAt is the server's clock: it is trustworthy and it is not what anybody is asking about.
    • Count by eventTime, because a dashboard asking "what happened between 9 and 10" means the user's 9 to 10. That choice is what creates the late-event problem in deep dive 4, and taking it knowingly is the point.
• EventType is a registry row: typeId (a small int, under 1,000 today and hard-capped at 4,000), name, schema version, owner, piiFlag, and status (active or deprecated).
  • The registry is small enough to sit in memory in every process, refreshed on a timer.
  • The small integer is load-bearing: it is the slot index into the count vector, so a type is one array position rather than a string key. A retired type keeps its slot forever, because renumbering would silently rewrite history.
• CountBucket is the aggregate: key (granularity, bucketStart), value a slot vector of counts plus a monotonic revision.
  • One row per bucket, not a thousand rows per bucket. This is the single most important storage decision on the page and it falls straight out of the cardinality number.
  • Name the store, because the claim depends on it. Anything with a single-key atomic put of an opaque value works: DynamoDB, Cassandra, or plain Postgres. A conditional put on revision is the one primitive that is actually required, and all three have it.
  • The vector is sized to the registry high-water mark, not to 1,000. Today that is 1,000 slots and about 8 KB; at the 4,000-slot cap from dive 5 it is 32 KB and every read figure on this page scales with it. Buckets written at different times can therefore have different widths, so the reader treats a short vector as zero-padded rather than assuming a fixed length.
  • Granularity is one of minute, hour, day. Same shape at every tier, so the query code is written once.
• UserKey is the key store: keyId = HMAC(secret, userId) to a wrapped per-user data key. Keying it by the HMAC rather than the raw userId means the table itself holds no plaintext identifier. It exists so erasure can be a key delete instead of a rewrite of a petabyte-scale lake.
• DeletionRequest records userId, requestedAt, completedAt. It is the audit trail a regulator asks for, and it is also what makes a restored backup re-converge.

API or System Interface

POST   /v1/events                              // { events: [ { eventId, eventType, userId, eventTime, properties } ] }
                                               // → 202 { accepted, warned: [ { eventId, reason } ],
                                               //              rejected: [ { eventId, reason } ] }

GET    /v1/stats/top?start=&end=&k=            // → 200 { window, complete, results: [ { eventType, count } ] }
GET    /v1/stats/series?type=&start=&end=&granularity=  // one type over time, same buckets, no new storage

GET    /v1/event-types                         // the registry, cacheable
POST   /v1/event-types                         // { name, schema, piiFlag } → 201, no deploy needed

POST   /v1/privacy/erasure                     // { userId } → 202 { requestId, dueBy }
GET    /v1/privacy/export?userId=              // → 202, delivered out of band

• The write path is a batch endpoint, and that is a capacity decision rather than a convenience.
  • 350K events/sec as individual calls is 350K RPS of TLS handshakes, headers, and auth checks, where the overhead dwarfs the 500-byte payload.
  • At 20 events a batch it is 17.5K RPS, which is an ordinary fleet.
  • The cost is latency at the edge: the SDK holds events for a few seconds, so a user who closes the tab loses that buffer. Accept it and say so, because tracking is not transactional.
• 202, not 200. The server has durably accepted the batch and taken responsibility for it. It has not counted it yet, and pretending otherwise would be a lie the freshness section then has to walk back.
• Partial rejection is in the response shape, and rejected means malformed, not unfamiliar.
  • One bad event in a batch of twenty must not fail the other nineteen, and the client needs to know which one and why.
  • A schema violation or a nonsense timestamp is rejected. An unknown eventType is not: it is counted into a reserved overflow slot and returned in warned rather than rejected, for the reasons in deep dive 5.
• complete and asOf are first-class fields on the read, not footnotes.
  • complete is false while the range touches anything younger than the backfill horizon, so a caller knows the number is still moving.
  • Pin the horizon to a number or the flag is useless. Set it at 24 hours: the nightly backfill closes yesterday and nothing older is restated on the normal path. Leaving it implicitly equal to the 90-day clock clamp from dive 4 would mean complete: true only for ranges entirely older than 90 days, which is no dashboard anyone runs.
  • An event older than the horizon still gets counted, but through an explicit restatement that bumps the revision, so callers see it via asOf rather than silently.
  • complete: true is not a promise the number is frozen for ever, and the response has to admit that, because an event arriving after the horizon still triggers an explicit out-of-band restatement of a bucket that already read complete. Ordinary lateness and the nightly backfill cannot, since both run inside the horizon.
  • So the response also carries asOf, which is the MAXIMUM revision across every bucket the answer read, not the revision of the newest bucket.
  • That distinction is the whole point of the field. A late event restates an OLD bucket, which leaves the newest bucket's revision untouched, so a newest-bucket asOf would stay identical while the number changed. Taking the max, or a digest over the revision vector, is what actually detects it.
• No userId appears anywhere in the query path. That is deliberate: it is what keeps the aggregate store outside the scope of an erasure request, and it is worth saying out loud when you get to GDPR.
• The two halves have different threat models on purpose. The write endpoint is public and takes data from untrusted clients, so it needs per-key quotas and validation. The read endpoint is internal and authenticated. Same system, and treating them the same is how a cardinality attack gets in.

1) Clients should be able to emit events without the system losing them

• The Collector does four things and nothing that can be deferred: authenticate the writing key (and enforce its new-type quota), validate each event against the registry and clamp its clock, encrypt userId and the properties blob with the user's data key, and stamp receivedAt before appending the batch to the log.
  • It stamps receivedAt and it does not enrich, join, or count. Everything it might do instead is something that can fail, and this is the one component that must not.
  • The encrypt step is the one that spoils the clean story, so own it rather than hiding it. It means the Collector is not truly stateless: it holds a TTL-bounded cache of per-user data keys, because a KMS call per event at 350K/sec is not a thing.
  • That cache is a dependency inside the component that must not fail, so it fails open in the only safe direction: a key that cannot be fetched means the batch is 503'd and retried, never written in the clear.
  • The cache TTL is also the real erasure latency, which is why it is short and measured. It is the number to quote when someone asks how fast erasure takes effect.
• The durable log is the contract between ingest and everything downstream.
  • The Collector returns 202 only after the append is acknowledged with acks=all, so an ack means the data survives a broker loss.
  • Partition by hash(eventId), not by eventType. This is the trap: keying the log by event type would send 40 percent of all traffic to a single partition, which is the hot-key problem created on purpose at the one place it is hardest to fix. Hashing the event id spreads perfectly by construction.
  • Two independent consumers read the same stream at their own offsets: the archive and the aggregator. Neither can block the other, and adding a third later costs nothing.
• The raw lake is the system of record for its 90-day retention, and it is what the counts are rebuilt from.
  • Hourly Parquet in object storage, partitioned by date and hour, about 5 TB a day, 90 days hot.
  • It exists for three reasons that are worth naming separately: replay after an aggregation bug, backfill of late data, and the legal obligations in requirement 4.
• Loss and duplication are handled by two different mechanisms. The log plus acks=all is what stops loss. The client-generated eventId is what stops a retry becoming a second event. Conflating them is the most common mistake here.

2) Counting the stream exactly

• Aggregation is two stages, and the reason is the skew.
  • Event traffic is Zipf-shaped in every real product: page_view alone is around 40 percent of everything.
  • A plain keyBy(eventType) on the raw stream routes all of it to one task: 140K events/sec, roughly 70 MB/sec, on a single core. The job does not fall over gracefully, it just develops permanent lag on the one key everybody looks at.
• Stage one is a local pre-aggregate with no keyBy at all.
  • Each task keeps a plain map of at most 1,000 counters for the current minute, over whatever slice of partitions it happens to own.
  • 1,000 counters is nothing. This is only possible because of the cardinality number from step 1, which is why that number came first.
  • That map must be checkpointed operator state, and the reason is the sink. If a task dies mid-window with an uncheckpointed local map, its slice is lost, and because the sink overwrites rather than increments, the too-low number becomes permanent. Increment would have healed itself on replay; overwrite does not. Idempotence on the write side moves the durability burden onto the aggregation state.
• Stage two is where the keyBy finally happens, and by then the volume is gone.
  • At the close of each minute every task emits one partial per event type it saw.
  • 200 tasks times 1,000 types over 60 seconds is about 3,300 partials/sec crossing the shuffle, instead of 350,000 raw events/sec.
  • The hottest key in the second stage receives 200 partials a minute, which is not a hotspot in any sense.
  • This is local-global aggregation, and the task index is doing the job people usually do with a random salt, without the salt's downside of having to choose a fan-out factor in advance.
• The sink overwrites, it does not increment, and this is the exactly-once story.
  • There is a third stage, and leaving it out is the mistake that makes "overwrite" a slogan instead of a mechanism.
    • GlobalCombine is keyed by eventType, so it produces 1,000 independent results for one minute, spread over many tasks.
    • If each of those wrote itself into the shared bucket row, that is 1,000 concurrent read-modify-writes on one row, which is exactly the non-idempotent operation overwrite was introduced to remove.
    • So after GlobalCombine, re-key by WINDOW. One task assembles the whole 1,000-slot vector for that minute and does a single atomic put of the entire row.
    • The write is then "the vector for minute M is V", one writer, one key, one put. That is what makes replay a no-op.
  • Fence the put with a monotonic revision, because there is more than one writer over the life of a bucket.
    • The streaming restatement and the nightly backfill both write the same bucket. Pure last-writer-wins is ordered by arrival, not by correctness, so a straggling streaming write landing after the backfill makes the worse number permanent.
    • Every put carries a revision and is conditional: reject if the incoming revision is not greater than the stored one. Backfill revisions are drawn above streaming ones, so the batch recomputation always wins.
    • Note the asymmetry that makes this necessary: with increment a lost update is one wrong number, recoverable. With overwrite a wrong write is permanent by construction. That is the price of idempotence and it has to be paid with a fence.
  • So replaying the same window after a checkpoint restore produces the same row rather than double counting it, and no distributed transaction is required to get there.
  • Deep dive 3 is where you defend the boundary of that claim, because a replayed window is only deterministic if the same events land in it.

3) Answering topK(start, end, k)

• Three tiers of the same shape: minute, hour, day.
  • A rollup job folds 60 settled minutes into an hour and 24 settled hours into a day. It reads only buckets past the 15-minute lateness window, and reruns for any bucket a later restatement dirties.
  • Retention differs per tier because size differs wildly: minutes for 60 days, hours for 2 years, days forever at 365,000 counts a year.
  • Say what retention does to the query, because it is not free: a range older than 60 days has no minute rows for its ragged edges, so it degrades to whole hours, and past 2 years to whole days. That is a fine answer, but it has to be said rather than discovered.
• The query decomposes the range into the fewest aligned buckets that exactly cover it.
  • Whole days in the middle, hours at the shoulders, minutes at the two ragged edges.
  • Say whose day, because "aligned" is meaningless without it and nobody asks until it is wrong. Buckets are UTC-aligned, which is what makes "24 hours folds into a day" true all year.
  • Align to a local calendar instead and the rollup breaks twice a year: a US Eastern local day is 23 hours in March and 25 in November, so a 24-hour fold undercounts one and double-counts the other, permanently, because the sink overwrites.
  • The cost of UTC is paid at the product edge: a caller wanting local "yesterday" gets 24 hour buckets instead of 1 day bucket, and a half-hour zone such as India or Nepal can never use the day tier at all. Exact and bounded either way, so it is a product decision, not a correctness one.
  • The edges are bounded by the calendar: at most 118 minute buckets and 46 hour buckets, no matter how long the range is. Those two terms never grow.
  • The day term does grow, so do not oversell the bound. An arbitrary range up to a month is about 193 reads, up to a year about 528.
  • Say which range you mean, because a whole-day-aligned one is much cheaper and the difference is not obvious. If the span is an exact multiple of a day, the two edges complement: the minutes sum to 60 and the hours to 23, not 118 and 46. So exactly 30 days is at most 112 reads and exactly 365 days is at most 447. The caps apply to ragged spans, not to "yesterday" or "the last 30 days".
  • Retention pulls the real ceiling down further. A year-long range has no minute rows at its old edge, since minutes age out at 60 days, so that edge degrades to hours and the reachable worst case is about 469 rather than 528.
  • If a year has to look like a week, add a month tier and a year tier, but do the edge arithmetic before promising a bound: the fixed overhead becomes 118 minutes + 46 hours + 60 days + 22 months = 246 buckets before a single year bucket, so a decade is about 255 and a century about 345. The ladder helps, it does not flatten.
• Each bucket is one key read, because a bucket is one row holding all 1,000 counts.
  • So a ragged month-long query is about 193 reads of 8 KB, roughly 1.5 MB moved, rather than 193,000 row reads. A day-aligned 30-day query is 112 reads. A year is about 469 in practice, under 4 MB either way.
  • Compare the naive version honestly: a 30-day range read as raw minutes is 43,200 buckets holding 43 million counts to add up, and 43 million row reads as well if each type is stored as its own row. Scanning the raw lake instead is 300 billion events.
• Constrain the input, because the decomposition silently rounds. The finest bucket is a minute, so a 30-second range would be answered with a whole minute and the caller would never know. Snap start and end to minute boundaries and say so in the response, or reject anything finer with a 400. Do not let the API accept a precision it cannot honour.
• Then it is arithmetic, not a database problem. Sum the vectors elementwise into one 1,000-entry array, and take the top k with a partial selection. At 1,000 entries a full sort is also fine, and saying "I would just sort a thousand numbers" is a better answer than reciting a heap.
• Cache aggressively, but define "closed" as past the 24-hour backfill horizon, not merely past the window.
  • A bucket can still be rewritten by allowed lateness for 15 minutes, and again by the nightly backfill. "Immutable once the window shuts" is the trap.
  • So the cache key is (granularity, bucketStart, revision), and a restatement bumps the revision, which evicts by construction rather than by invalidation message.
  • That key is circular unless you add one thing, and the omission is easy to miss: you cannot build it without knowing the current revision, and reading the bucket to learn the revision is the read you were trying to avoid. So keep a separate revision manifest, one small row per day holding the revisions of the buckets under it. Without it the revision in the key is decorative and you are really just running a TTL. Count its cost honestly: it is one extra round trip before the batch, and one manifest row per day of range, so 30 for a month and 365 for a year on top of the bucket reads.
  • Anything younger than the backfill horizon gets a short TTL. Anything older is cached indefinitely.

4) Erasing a user's data on request

• Start by saying where personal data actually lives, and be exact, because "one place" is a claim people overreach on.
  • The Collector encrypts userId and properties before the append, so the log and the lake both hold ciphertext and neither is readable without the key. That ordering is the whole trick and it is easy to get backwards.
  • The count vectors hold neither field, in any form.
  • What is not ciphertext, and must be named: the deletion ledger, the export index, and the sampled unknown-type-name table, which is exactly where an identifier-shaped type name would land. The key store is keyed by an HMAC, so it holds no plaintext identifier.
  • Two of those three are deletable by ordinary means; the ledger is not, and the distinction matters. The export index and the type-name sample go with the erasure request. The deletion ledger has to survive it: it is replayed after every restore and it is the audit trail a regulator asks for, so it stores the keyId and the timestamps, never the raw identifier or any event data.
  • Inventory the whole field list, not just userId, because a tracking event is full of personal data people forget: IP address, device id, session id and user agent are all personal data under GDPR. They go inside the encrypted blob with everything else, and saying the list out loud is what convinces a DPO you have actually looked.
  • Confining it deliberately is the design. A system that sprinkles userId into six derived tables has six erasure problems.
• Deleting rows out of the lake is the obvious plan and it is not viable at this size.
  • The lake is immutable Parquet, so "delete one user" means finding and rewriting every file that touched them, across 450 TB and 90 days.
  • One active user's events can be spread over thousands of files, and you would repeat that work for every request.
• So encrypt per user at ingest and erase by destroying the key.
  • Each user has a data key, wrapped by a KMS master key, standard envelope encryption. The Collector encrypts userId and properties with it before the row is ever written.
  • Two details decide whether this is real erasure or theatre, and they are the ones people skip.
    • Use a random IV per row. Deterministic ciphertext would leave an identical token on every one of that user's rows, so after the key is destroyed you could still single out one person's entire behavioural trace, which is precisely what erasure is supposed to prevent.
    • Derive the key id as an HMAC under a secret, not a bare hash(userId). User ids are enumerable, so an unsalted hash is a rainbow table away from plaintext and is a reversible pseudonym rather than anonymisation.
  • eventType and eventTime stay in the clear, which is what lets replay, backfill and audit keep working after a mass erasure. It also means an erased user's events keep contributing to the counts, which is consistent with the aggregates argument below.
  • Erasure destroys that one key, which is a single small write. Every row for that user, in every file, in every replica and every region, becomes undecryptable, because none of them can read ciphertext without it.
  • Not literally at the same instant, and the gap is worth owning: Collectors cache data keys, because a KMS call per event at 350K/sec is not a thing. Erasure is effective once the caches expire, so keep that TTL short, measure it, and quote it as the real number.
  • The dead ciphertext is removed later by ordinary compaction on the 90-day retention cycle. The key delete is what meets the deadline, not the cleanup.
  • The cost, which the 5 TB a day figure hides: per-user ciphertext is unique, so the widest column stops dictionary-compressing. That number is wire size, and this design gives up most of the compression a plain Parquet lake would get.
• Write a tombstone to a deletion ledger, because backups come back.
  • Restoring a snapshot from before the request would otherwise resurrect a live key, so the ledger is replayed after every restore.
  • The backup that matters is the KEY STORE's, not the lake's. Restoring lake files changes nothing, since they are unreadable. Restoring the key store is the only event that can undo an erasure, which is the part usually missed.
• Export needs an index, and admitting that is better than pretending erasure and export are the same shape.
  • Erasure is one key delete. Export has to FIND the rows, and encrypted userId is not searchable, so without help it is a 450 TB scan.
  • So the archive sink maintains a small index from the keyId to the files and row groups that user appears in. Export reads the index, decrypts, and assembles.
  • It has to be the keyId and not a fresh hash of the userId, for two reasons. The sink sits downstream of the log, where the identifier is already ciphertext, so a plaintext hash is not available to it. And a bare hash of an enumerable id is the reversible pseudonym ruled out above. So the keyId, which is already an HMAC under a secret, travels in the clear on the row and is the join key.
  • It is a second store keyed by a pseudonymous id, so it is in scope too and the same request deletes its entries. Say it out loud, because "personal data lives in exactly one store" is the tidy claim candidates leave standing here, and the export index is what makes it false.
• The aggregates are untouched, and you should defend that rather than apologise for it. A count with no identifier is not personal data, cannot be re-identified at any useful aggregation width, and removing one user's contribution to "page_view: 8,400,000" is neither required nor meaningful. The second of the three short dives covers where that argument runs out.

1) How is topK fast for any range, from a minute to a year?

2) One event type is 40 percent of the traffic. Where does that melt?

3) At-least-once delivery, exactly-once counts

4) Late and out-of-order events

5) Event type handling and the cardinality attack

Three more dives, briefly

• Replication and partitioning, per store, because the answer is different for each.
  • Log: RF 3, acks=all, min.insync.replicas=2, spread across availability zones. It tolerates one broker loss with no acknowledged write lost, and rejects writes rather than accepting unreplicated ones when two are down.
  • Counts store: partition by bucketStart so it is time-ordered and retention is a partition drop rather than a mass delete. The whole thing is small, so replicate it fully to every region and serve reads locally.
    • Full replication is only affordable because of the cardinality number. Worth saying, since it is the third distinct thing that number bought.
  • Raw lake: partitioned by date and hour, which is what makes a backfill read one hour instead of the whole lake. Cross-region replication for durability, and the object store handles the redundancy inside a region.
  • Multi-region ingest: write to the local region and replicate asynchronously, because a tracking event must never wait on a cross-ocean round trip. Aggregate per region, then sum the regional vectors: they are 8 KB each, so the global rollup is trivial.
    • Do the merge once, in a job, not per query. Summing regional vectors at read time multiplies every query's read count by the number of regions. A merge job writes a global bucket whose revision advances only when every region has reported, so the query path stays single-tier.
    • That gives the honest cost: a global number lags the slowest region, and a region that is down leaves the global bucket incomplete rather than silently short. complete: false is doing the work here too.
• The privacy edge case that the "aggregates are anonymous" argument does not cover.
  • A count of 1 for a rare type in a one-minute window is close to identifying a single person, especially combined with anything else the caller knows.
  • Suppress or widen: refuse ranges under a minimum width, or drop entries below a minimum count, for any caller without a legitimate reason to see raw precision.
  • Also enforce purpose limitation and consent at ingest: if a user has not consented to analytics, the event should not be written, which is cheaper and safer than erasing it later.
  • Data residency is a partitioning question in disguise: EU events stay in EU storage, so the regional aggregation above is doing double duty.
• What changes at 100x cardinality, which is the most likely follow-up of all.
  • If the key becomes a URL or a productId, 1,000 becomes 10^8 and the vector, the local map, and the full-scan sum all die together.
  • Then you split: keep exact counts for a tracked head of a few thousand known-hot keys, and a Count-Min Sketch or Space-Saving summary for the tail.
  • Top-k over sketches is approximate and, importantly, is not mergeable across buckets without error accumulating, so the rollup design changes too.
  • The reason to have this ready is that it shows the exact-counts choice was a judgement about this problem, not the only tool you have.

How you would know it is wrong

• A design whose entire pitch is the word "exact" needs a number that goes red, and this is the thing most candidates never bring up.
  • Reconcile, do not assume. A daily job recounts one hour straight from the lake and diffs it against the stored HourCounts. Any non-zero diff is a bug, not a rounding artefact, because both sides claim to be exact.
  • Count at every stage boundary and require them to agree: accepted at the Collector, appended to the log, counted by the aggregator, archived to the lake. A gap between two adjacent counters localises the loss immediately.
  • Alarm on watermark lag and on a stuck rollup, since a bucket that is never written looks exactly like a quiet minute.
  • Alarm on the __other slot, which is the early warning for both a bad release and a cardinality attack.
  • Track the conditional-put rejection rate. It should be near zero; a spike means two writers are fighting over the same bucket.

Final Design

• SDKs batch events with a client-generated eventId and POST them to a Collector, which validates against the registry, encrypts the personal fields, and returns 202 only after a durable append. It is stateless apart from a short-TTL cache of per-user data keys.
• The log is partitioned by hash(eventId) with RF 3 and acks=all, so ingest cannot hot-spot and an ack means the event survives a broker loss.
• Two independent consumers read it: an archive sink landing hourly Parquet in the raw lake, and the aggregator.
• The aggregator is local pre-aggregate then global combine over one-minute tumbling windows, and a third stage re-keys by window so one task does a single atomic put of the whole 1,000-slot vector, fenced on a monotonic revision.
• A rollup job folds settled minutes into hours and hours into days, and a backfill job restates buckets when late events land.
• The query service decomposes a range into aligned buckets, 112 for a day-aligned 30-day range and about 193 for a ragged one, reads one row per bucket in parallel, sums 1,000 counts, and partial-sorts for k, serving anything past the backfill horizon from cache.
• The privacy service holds per-user data keys and a deletion ledger, so erasure is a key destroy that never touches the aggregates.
• The invariant that makes the whole thing defensible, stated with its expiry date: for the last 90 days the lake is the truth and every aggregate is a derived view that can be rebuilt from it, so nothing in the counting or query path can permanently lose data.
• Past 90 days that invariant is gone, and it is worth saying before the interviewer says it. The lake ages out at 90 days while DayCounts is kept forever, so the old day tier is unrebuildable and unauditable: it is the truth because it is all that is left. If that is unacceptable, the fix is to keep a compacted per-day archive alongside it, which is small, rather than to keep 5 TB a day forever.

What is Expected at Each Level

• Mid
  • Gets to a working pipeline: collector, queue, stream aggregation into per-minute counts, a query that sums buckets.
  • Names a top-k selection and can implement it.
  • Usually reaches for a sketch without first checking whether the cardinality needs one.
  • Usually stores a row per type per bucket, and does not notice the read amplification that creates.
• Senior
  • Does the cardinality arithmetic unprompted and concludes that exact counts are affordable.
  • Identifies the hot event type and fixes it with two-stage aggregation.
  • Builds the rollup tiers so a long range does not read every minute.
  • Handles late events with a watermark rather than ignoring them.
  • Separates at-least-once transport from idempotent counting, and says why they are two mechanisms.
• Staff
  • Leads with the cardinality number and uses it as the reason for at least three separate decisions: exact counts, one row per bucket, and full replication of the counts store.
  • Separates the two systems in the problem out loud, the cheap counting one and the expensive raw-storage one, and spends time proportional to that rather than to which one the question emphasised.
  • Makes query cost independent of traffic, and is precise that it still grows slowly with the calendar rather than claiming a flat ceiling, including that a day-aligned range is much cheaper than a ragged one.
  • Notices that "overwrite, do not increment" is only a mechanism once there is a single writer per bucket and a revision fence, and that the design has two writers.
  • Brings up reconciliation unprompted, because a system that claims exactness owes you the number that would go red.
  • Treats GDPR as an architecture constraint solved at ingest with crypto-shredding, not as a delete job bolted on afterwards, and can defend leaving the aggregates alone as well as name where that defence stops.
  • Identifies the cardinality attack on the event-type registry as the thing that invalidates the whole design, and defends it with a stable typeId, an overflow slot, and a per-key quota.
  • Names what the design does not do: no arbitrary group-bys, three-minute freshness with restatement after that, a straddling retry counted twice, and export needing an index that erasure does not.