Design Tables for a KPI Dashboard
Design the analytical data model to power a KPI dashboard for a large-scale photo-sharing application. The dashboard initially reports daily and must maintain fast performance even as the raw dataset grows significantly.
Raw Source Tables
users(user_id, user_name, joined_at)photos(photo_id, user_id, created_at)follows(follower_user_id, followed_user_id, created_at)user_events(event_id, user_id, event_type, occurred_at)
Assumptions:
- All timestamps are stored in UTC.
- A
followsrow indicates a follow relationship created atcreated_atthat remains active indefinitely. DAU(Distinct Active Users) is defined as distinct active users per local calendar day, based onuser_events.
Required Metrics
The dashboard must display the following metrics:
- New users
- New users: change versus 7 days earlier
- DAU (Distinct Active Users)
- DAU: change versus 7 days earlier
- New photos
- New photos per DAU
- New photos per DAU: change versus 7 days earlier
- Total followers
- % users with 0 followers
- % users with 1-2 followers
- % users with 3+ followers
Task
Design the table or tables that should power this dashboard. For your design, explain:
- The grain of the tables.
- The primary and foreign keys.
- What data should be precomputed versus derived at query time.
- How you would populate these tables incrementally from the raw data.
Follow-up Questions
Discuss the design changes needed for each of these scenarios:
- Add a week-1 retention metric.
- Support weekly rollups instead of daily rollups.
- Change reporting from
America/Los_AngelestoAmerica/New_York.
Focus on correctness, performance, and maintainability throughout your design.
AI interview prep
Core dilemma
Balancing the need for daily, local-timezone specific metrics and historical comparisons with efficient, large-scale data aggregation and incremental updates, while maintaining data freshness and query performance.
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 Tables for a KPI Dashboard
Design the analytical data model to power a KPI dashboard for a large-scale photo-sharing application. The dashboard initially reports daily and must maintain fast performance even as the raw dataset grows significantly.
Raw Source Tables
users(user_id, user_name, joined_at)photos(photo_id, user_id, created_at)follows(follower_user_id, followed_user_id, created_at)user_events(event_id, user_id, event_type, occurred_at)
Assumptions:
- All timestamps are stored in UTC.
- A
followsrow indicates a follow relationship created atcreated_atthat remains active indefinitely. DAU(Distinct Active Users) is defined as distinct active users per local calendar day, based onuser_events.
Required Metrics
The dashboard must display the following metrics:
- New users
- New users: change versus 7 days earlier
- DAU (Distinct Active Users)
- DAU: change versus 7 days earlier
- New photos
- New photos per DAU
- New photos per DAU: change versus 7 days earlier
- Total followers
- % users with 0 followers
- % users with 1-2 followers
- % users with 3+ followers
Task
Design the table or tables that should power this dashboard. For your design, explain:
- The grain of the tables.
- The primary and foreign keys.
- What data should be precomputed versus derived at query time.
- How you would populate these tables incrementally from the raw data.
Follow-up Questions
Discuss the design changes needed for each of these scenarios:
- Add a week-1 retention metric.
- Support weekly rollups instead of daily rollups.
- Change reporting from
America/Los_AngelestoAmerica/New_York.
Focus on correctness, performance, and maintainability throughout your design.