Product Event Tracking and Analytics Pipeline With Flexible Schema and Retention

Design an event tracking and analytics system for product features. Features in a web application emit JSON events. Every event has a `source` field (which feature emitted it) and a `type` field (which action occurred). Different features also attach their own extra key-value pairs. For example, a document chat feature ("Doc Chat") also records the user's `question` and the returned `answer`. Design the whole path from collection through storage to analysis. The interviewer deliberately leaves the problem vague and expects you to ask questions. The constraints below are the answers they gave when asked. ### Constraints and Clarifications - **Volume:** about 10 events per second today, possibly 100–200 events per second in the future. - **Timestamps:** the web application team can add an event timestamp to every event. - **Schema growth:** new `source` and `type` values will keep appearing, so the schema must be extensible. - **Retention:** you must propose a retention policy yourself. The product has been running for many years, so keeping everything forever would add up. - **Query latency:** seconds are acceptable; the system is for internal use only. - **Existing context:** the team already operates Spark-based batch processing, and asynchronous processing built on queues and serverless functions. - **Queries to support:** 1. Time-window aggregates, such as the number of unique users last week, and unique-user counts filtered by `source` and/or `type`. 2. Investigative queries, such as finding Doc Chat records whose `answer` is empty or shorter than 10 characters. ### Clarifying Questions - Are events emitted from the browser, from backend services, or from both, and is a user identifier always present? - How quickly after an event occurs must it be queryable, as distinct from how quickly a query must return? - Do free-text fields such as the Doc Chat question and answer have privacy or retention rules that differ from ordinary events? - Is losing or duplicating a small fraction of events acceptable, and does the web application retry failed sends? ### Part 1 — Ingestion Path How does an event get from the web application into the topic or stream in your design? What processes the events after that, and how does the processing scale? ```hint What the topic carries Consider whether the event body itself can travel through your messaging layer, and what sits between the topic and the processor to absorb bursts and failures. ``` #### What This Part Should Cover - The concrete publish mechanism from the web application. - Buffering, delivery guarantees, and handling of bad events. - How the processor scales from 10 to 200 events per second and beyond. ### Part 2 — Event Schema Design the stored schema so that it supports new sources, new types, and feature-specific fields without a schema migration for every new feature. The interviewer's reaction to a design with dedicated `question` and `answer` columns for Doc Chat was: "Not wrong, but we want flexibility." ```hint Common versus specific Separate what every event shares from what only some features send, and ask how the investigative query would still reach a feature-specific field. ``` #### What This Part Should Cover - Fixed envelope fields versus a flexible properties structure. - How feature-specific fields remain queryable and indexable when needed. - How the set of sources and types is documented or validated. ### Part 3 — Storage and Query Engine You chose Postgres. What about the data warehouse options: Redshift, S3 with Parquet files, or Spark? If you could choose only one, which would it be and why? Show how the two query types run on your choice. ```hint Let the constraints decide Compare the options against the stated volume, latency, retention, and existing tooling rather than against general popularity. ``` #### What This Part Should Cover - A single committed choice justified by the stated constraints. - How both query types execute efficiently on that choice. - What change in requirements would make you switch. ### Part 4 — Retention Policy Propose a retention policy and explain how it is enforced. ```hint Not all data ages the same way Consider which data needs row-level detail, which needs only summaries, and which carries user content. ``` #### What This Part Should Cover - Different retention for raw events, aggregates, and sensitive free text. - A cheap enforcement mechanism. - How long-range trend questions remain answerable after raw data expires. ### What a Strong Answer Covers - Clarifying questions asked before designing, given the intentionally vague prompt. - A concrete and correct ingestion path with buffering, a place for invalid events, and a clear scaling story. - A flexible envelope-plus-properties schema that still supports the investigative query. - A committed storage choice argued from the numbers, with a size estimate. - A retention policy that controls cost and handles user-generated text. ### Follow-up Questions 1. Distinct-user counts cannot simply be added across days. How would you precompute weekly and monthly unique users? 2. Events arrive late or out of order because of client retries. How do time-window queries stay correct? 3. A feature starts sending a malformed property that breaks downstream queries. How do you detect and contain it? 4. At what event rate or data size would you move away from your chosen store, and what would the migration look like?