Design analytical tracking database
Design an Analytical Database for User Actions
Context
You are tasked with designing an analytics data store to track user behavior and commerce events. This system will enable product and business teams to run reliable and fast queries. The system must accommodate web and mobile clients emitting events at a significant scale, specifically millions of events per day. The target latency for analytical queries is in minutes, not milliseconds.
Events to Track
The system should be capable of tracking the following types of user and commerce events:
loginsearch_itemview_itemadd_to_cartview_cartpurchaseview_order
Example Queries to Support
The analytical database must efficiently support queries similar to these examples:
- How many users from Asia viewed item A this month?
- What are the three highest-value orders at present?
Task
Describe the proposed schema, the event model, and the overall data flow for an analytical database designed to efficiently answer the example queries and other similar pre-written analytical queries.
AI interview prep
Core dilemma
Balancing high-volume, continuous event ingestion (millions/day) with the need for fast, complex analytical queries over potentially large historical datasets, all while maintaining a 'minutes' latency target for analytics.
3 tradeoffs and 3 stress tests locked
Interview prep
Unlock the interviewer X-Ray rubric, tradeoff matrix, stress tests, clarifying questions, and key concepts.
Design analytical tracking database
Design an Analytical Database for User Actions
Context
You are tasked with designing an analytics data store to track user behavior and commerce events. This system will enable product and business teams to run reliable and fast queries. The system must accommodate web and mobile clients emitting events at a significant scale, specifically millions of events per day. The target latency for analytical queries is in minutes, not milliseconds.
Events to Track
The system should be capable of tracking the following types of user and commerce events:
loginsearch_itemview_itemadd_to_cartview_cartpurchaseview_order
Example Queries to Support
The analytical database must efficiently support queries similar to these examples:
- How many users from Asia viewed item A this month?
- What are the three highest-value orders at present?
Task
Describe the proposed schema, the event model, and the overall data flow for an analytical database designed to efficiently answer the example queries and other similar pre-written analytical queries.