Design a data warehouse model for tracking feature adoption

What's really being asked

This question tests your understanding of data modeling for analytics (OLAP) versus for applications (OLTP). The interviewer is looking for you to recognize that a business requirement for flexible, fast, aggregate queries points directly to a dimensional model, specifically a star schema. It's a test of your ability to design for read-heavy, aggregative workloads, not write-heavy, transactional ones.

The full answer

A strong answer proposes a star schema and explains its benefits. First, state the goal is to enable fast, flexible queries for a Product Manager. Second, define the central fact table, fact_feature_events, containing foreign keys (user_id, feature_id, date_key), a timestamp, and metrics. Third, define the dimension tables that radiate from the fact table: dim_users (user attributes like plan, signup date), dim_features (feature metadata like name, category), and dim_date (time attributes like day, month, year). Finally, explain how this structure makes the PM's query simple and fast by minimizing complex joins and allowing for easy slicing and dicing of the event data.

The mistakes people make

The most common mistake is designing a schema for a production database, not a data warehouse. This includes proposing a highly normalized (3rd Normal Form) model, which would require many expensive joins for a simple feature adoption query. Another red flag is suggesting a single, massive, denormalized table with user and feature details repeated in every row. This leads to huge data duplication and slow scan times. Simply listing tables without explaining why the star schema is the right choice also indicates a lack of senior-level understanding.

What usually comes next

Expect questions on scalability and evolution. For example: "How would this model scale to 10 billion events?" (Answer: Partition the fact table by date and use a columnar storage format like Parquet). "What if a user's subscription plan changes?" (Answer: Discuss Slowly Changing Dimensions, especially Type 2 to preserve historical state). "How would you handle custom properties for different events?" (Answer: Use a semi-structured data type like JSON or STRUCT in the fact table).

A concrete example

To find users who used 'Feature A' but not 'Feature B', the star schema enables a clean SQL query. You would join fact_feature_events with dim_features, then group by user_id. The key is using a HAVING clause with conditional aggregation, like HAVING SUM(CASE WHEN feature_name = 'Feature A' THEN 1 ELSE 0 END) > 0 AND SUM(CASE WHEN feature_name = 'Feature B' THEN 1 ELSE 0 END) = 0. This is far more efficient than self-joining a massive single table.