How would you design a data model for a feature adoption dashboard?

WHAT THIS TESTS: Your practical ability to apply dimensional modeling (like the Kimball approach) to a common business problem. It's not about reciting definitions. The interviewer wants to see if you can translate "feature adoption" into a concrete, queryable star schema, identifying the core facts and the contextual dimensions needed for analysis.

A GOOD ANSWER COVERS: Four key points in order. First, identify the business process, which is a user interacting with a feature. Second, define the central fact table based on this event; its grain should be "one row per feature interaction per user". This table, fact_feature_usage, will contain foreign keys and numeric measures (like an interaction count, which is usually just 1). Third, define the necessary dimension tables that provide context, such as dim_user, dim_feature, dim_date, and potentially dim_organization for B2B products. Fourth, explain how these tables form a star schema, with the fact table at the center, optimized for fast aggregations required by a dashboard.

COMMON WRONG ANSWERS: The most common mistake is designing a transactional (OLTP) model instead of an analytical (OLAP) one. This often looks like a highly normalized schema with many joins, which is slow for aggregations. Another red flag is a vague answer like "I'd use a star schema" without specifying the tables, columns, and grain. Putting descriptive attributes (like user_email or feature_name) directly into the fact table is also incorrect, as it bloats the table and misses the benefits of the star schema. Finally, forgetting to include a dedicated dim_date table is a frequent omission; it's critical for any time-based analysis.

LIKELY FOLLOW-UPS: Be ready for "How would you handle a user changing their subscription plan?" which tests knowledge of Slowly Changing Dimensions (SCDs, typically Type 2 for history). Another is "How would you calculate weekly active users for a feature?" which tests your understanding of querying the model (COUNT(DISTINCT user_key) grouped by week). For scale, expect "How would this handle 100 million events per day?" The answer involves partitioning the fact table by date and using columnar storage formats like Parquet.

ONE CONCRETE EXAMPLE: The fact table would be fact_feature_usage with columns: feature_key (FK), user_key (FK), date_key (FK), organization_key (FK), and interaction_count (metric, value is 1). It would be joined to dimensions like dim_feature (feature_key, feature_name, category), dim_user (user_key, user_id, plan_type, signup_date), and dim_date (date_key, full_date, day_of_week, is_weekend).