KPI Dashboard Design for Nextagram
You are tasked with designing a fast KPI dashboard for "Nextagram". The dashboard needs to report daily metrics based on raw event data.
Raw Data Tables
The following raw tables are available, with timestamps initially stored in PST:
- users:
user_id(Primary Key)usernamecreated_at(TIMESTAMP)
- photos:
photo_id(Primary Key)user_idcreated_at(TIMESTAMP)
- follows:
follower_user_idfollowed_user_idcreated_at(TIMESTAMP)
Daily KPIs
The dashboard reports daily (one row per report date) and requires the following Key Performance Indicators:
- new_users: Count of users created on that specific report date.
- new_users_change_vs_7d_ago:
new_users(today) - new_users(today - 7 days). - DAU (Daily Active Users): Distinct count of users who performed at least one "active" action on that day. An "active" action is defined as posting a photo OR following someone.
- DAU_change_vs_7d_ago:
DAU(today) - DAU(today - 7 days). - new_photos: Count of photos created on that specific report date.
- new_photos_per_DAU:
new_photos / DAU. - new_photos_change_vs_7d_ago:
new_photos(today) - new_photos(today - 7 days). - followers: Total cumulative count of follow relationships as of the end of that report date (i.e.,
COUNT(*) FROM follows WHERE created_at <= end_of_day). - %_users_with_0_followers: Percentage of users whose inbound follower count is 0 as of the end of that date.
- %_users_with_1_2_followers: Percentage of users whose inbound follower count is between 1 and 2 (inclusive) as of the end of that date.
- %_users_with_3plus_followers: Percentage of users whose inbound follower count is 3 or more as of the end of that date.
Tasks
A) Data Model / Table Design
Design the derived table(s) that will power this dashboard, optimized for fast reads on a huge dataset. Specify:
- Table schemas (columns, grain, and keys).
- What data is precomputed.
- Key considerations such as partitioning/clustering, incremental loads, idempotency, handling late-arriving data, and the trade-off between correctness and cost.
B) Daily Population Strategy
Describe the high-level ETL steps (SQL/pseudocode is acceptable) to populate/refresh these derived tables daily from the raw tables.
C) Add 1-Week Retention
Extend the dashboard to include a 1-week retention metric: for each user cohort (users who joined on day D), what fraction of them are active on day D+7 (using the same "active" definition as DAU). Describe any required table changes and how to compute this metric efficiently.
D) Switch to Weekly Rollups
The dashboard needs to support weekly rollups instead of daily. Describe the necessary changes in table design and computation, considering:
- Date grain adjustments.
- Redefinition of "change vs 7d ago" (now "change vs 1 week ago").
- Handling partial weeks.
E) Report in a Different Timezone
A New York office requires metrics to be reported in EST instead of PST. Describe the necessary changes, including:
- Timestamp storage considerations.
- Date boundary definitions.
- ETL window adjustments.
- Backfill strategies.
- How to avoid double-counting at day boundaries during transitions.
AI interview prep
Core dilemma
Designing a robust, performant, and accurate data pipeline and model for complex time-series KPIs, balancing data freshness, consistency across timezones and granularities, and computational cost for a large, growing dataset. Specifically, handling cumulative metrics and retention efficiently alongside daily aggregates is challenging.
3 tradeoffs and 3 stress tests locked
Interview prep
Unlock the interviewer X-Ray rubric, tradeoff matrix, stress tests, clarifying questions, and key concepts.
KPI Dashboard Design for Nextagram
You are tasked with designing a fast KPI dashboard for "Nextagram". The dashboard needs to report daily metrics based on raw event data.
Raw Data Tables
The following raw tables are available, with timestamps initially stored in PST:
- users:
user_id(Primary Key)usernamecreated_at(TIMESTAMP)
- photos:
photo_id(Primary Key)user_idcreated_at(TIMESTAMP)
- follows:
follower_user_idfollowed_user_idcreated_at(TIMESTAMP)
Daily KPIs
The dashboard reports daily (one row per report date) and requires the following Key Performance Indicators:
- new_users: Count of users created on that specific report date.
- new_users_change_vs_7d_ago:
new_users(today) - new_users(today - 7 days). - DAU (Daily Active Users): Distinct count of users who performed at least one "active" action on that day. An "active" action is defined as posting a photo OR following someone.
- DAU_change_vs_7d_ago:
DAU(today) - DAU(today - 7 days). - new_photos: Count of photos created on that specific report date.
- new_photos_per_DAU:
new_photos / DAU. - new_photos_change_vs_7d_ago:
new_photos(today) - new_photos(today - 7 days). - followers: Total cumulative count of follow relationships as of the end of that report date (i.e.,
COUNT(*) FROM follows WHERE created_at <= end_of_day). - %_users_with_0_followers: Percentage of users whose inbound follower count is 0 as of the end of that date.
- %_users_with_1_2_followers: Percentage of users whose inbound follower count is between 1 and 2 (inclusive) as of the end of that date.
- %_users_with_3plus_followers: Percentage of users whose inbound follower count is 3 or more as of the end of that date.
Tasks
A) Data Model / Table Design
Design the derived table(s) that will power this dashboard, optimized for fast reads on a huge dataset. Specify:
- Table schemas (columns, grain, and keys).
- What data is precomputed.
- Key considerations such as partitioning/clustering, incremental loads, idempotency, handling late-arriving data, and the trade-off between correctness and cost.
B) Daily Population Strategy
Describe the high-level ETL steps (SQL/pseudocode is acceptable) to populate/refresh these derived tables daily from the raw tables.
C) Add 1-Week Retention
Extend the dashboard to include a 1-week retention metric: for each user cohort (users who joined on day D), what fraction of them are active on day D+7 (using the same "active" definition as DAU). Describe any required table changes and how to compute this metric efficiently.
D) Switch to Weekly Rollups
The dashboard needs to support weekly rollups instead of daily. Describe the necessary changes in table design and computation, considering:
- Date grain adjustments.
- Redefinition of "change vs 7d ago" (now "change vs 1 week ago").
- Handling partial weeks.
E) Report in a Different Timezone
A New York office requires metrics to be reported in EST instead of PST. Describe the necessary changes, including:
- Timestamp storage considerations.
- Date boundary definitions.
- ETL window adjustments.
- Backfill strategies.
- How to avoid double-counting at day boundaries during transitions.