Product Event Tracking and Analytics Pipeline With Flexible Schema and Retention

Scenario

You are tasked with building an event-tracking pipeline for a digital content platform. Features across the web and mobile apps emit JSON events containing a fixed header (timestamp, user_id, event_type, source) and a payload object containing feature-specific metadata. The system must support high ingestion rates and facilitate both real-time monitoring and complex analytical queries.

Requirements

1. Ingestion: Design an ingestion service that can handle 50k events per second. Explain how to buffer these events before processing.
2. Schema Flexibility: Since event types change frequently, describe how to store and query the payload field without forcing a rigid relational schema for every new feature.
3. Storage Strategy: Choose storage technologies for two use cases:
   • Investigative queries: Finding the last 10 actions of a specific user_id.
   • Analytical queries: Computing daily active users and feature adoption rates over the last 90 days.
4. Retention: Outline a strategy to manage costs by moving older data to cheaper storage while keeping it accessible for long-term trend analysis.

Constraints

• The system must ensure low-latency ingestion.
• Analytical queries should not block the ingestion pipeline.
• Data integrity and fault tolerance are required.

Discussion Points

• How do you handle schema evolution (e.g., adding or renaming fields in the JSON payload)?
• How would you implement efficient filtering by user_id versus aggregated metrics by event_type?
• Describe the trade-offs between using a columnar data warehouse (e.g., BigQuery/ClickHouse) and an operational database (e.g., PostgreSQL/DynamoDB).