Design a Data Model for a Feature Adoption Dashboard

WHAT THIS TESTS: This question tests your ability to apply dimensional modeling (specifically, the star schema) to a real-world business problem. The interviewer is evaluating your thought process: how you identify the core business event, choose the right 'grain' for your fact table, and design dimensions that enable flexible slicing and dicing of data. It distinguishes candidates who understand analytical (OLAP) modeling from those who only know transactional (OLTP) database design.

A GOOD ANSWER COVERS: First, identify the business process and the core event to be measured, which is 'a user interacts with a feature'. This becomes the foundation for the fact table. Second, define the fact table, for example fct_feature_usage. State its grain clearly: one row per feature usage event. This table should contain quantitative, additive measures like usage_count (always 1) and foreign keys like user_key, feature_key, and date_key. Third, define the dimension tables needed to provide context. Examples include dim_users (with attributes like signup date, user segment), dim_features (with feature name, release date, category), and a standard dim_date table (with month, year, day of week). Fourth, explain why this star schema is efficient for a dashboard, highlighting simple joins and fast aggregations.

COMMON WRONG ANSWERS: The most common mistake is proposing a single, wide, denormalized table containing all user and feature attributes on every event row. This is inefficient for storage and a nightmare to maintain when dimensional attributes change (e.g., a feature is renamed). Another red flag is describing a highly normalized (3NF) schema typical of an OLTP system. This is ill-suited for analytics because it requires many complex joins to aggregate data, leading to slow dashboard queries. Vague answers that just say 'a table for users and a table for events' without defining the grain, columns, and relationships also signal a lack of practical experience.

LIKELY FOLLOW-UPS: Expect questions like: 'How would you handle slowly changing dimensions? For example, if a user's pricing plan changes?' (Discuss SCD Type 2). Or, 'This fact table will have billions of rows. How would you ensure performance?' (Discuss partitioning the fact table by date, creating aggregate summary tables, and using columnar storage formats like Parquet).

ONE CONCRETE EXAMPLE: To calculate 'Weekly Active Users per feature', the query is simple and efficient: SELECT d.feature_name, w.week_start_date, COUNT(DISTINCT f.user_key) FROM fct_feature_usage f JOIN dim_features d ON f.feature_key = d.feature_key JOIN dim_date w ON f.date_key = w.date_key GROUP BY 1, 2;. This query's simplicity and performance directly result from the star schema design, which is the point you want to drive home.