Design a data model for tracking feature adoption

What's really being asked

This question evaluates your understanding of data modeling for analytics (OLAP) versus transactional systems (OLTP). The interviewer is looking for you to propose a star or snowflake schema, the standard for data warehouses. The key is to demonstrate that you can design a data structure optimized for the specific query pattern of a non-technical user, like a PM, who needs fast, flexible access to aggregated data.

The full answer

First, clarifying requirements like user scale (e.g., 10M users), event volume (e.g., 50M events/day), and query latency expectations. Second, proposing a star schema with a central fact_events table and at least two dimension tables, dim_users and dim_features. Third, detailing the table columns; for example, fact_events would have user_id (FK), feature_id (FK), timestamp, and maybe an event_properties JSON column for flexibility. dim_users would hold user attributes. Fourth, explaining how this schema easily answers the prompt's query using joins and filtering, which is efficient in a columnar data warehouse.

The mistakes people make

Designing a highly normalized, OLTP-style schema (e.g., a simple users, features, user_feature_link set of tables). This model is inefficient for analytical queries, requires complex joins, and doesn't capture the event-based nature of usage (like time or frequency). Another red flag is proposing one giant, denormalized table, which is not scalable. Forgetting a timestamp column is a critical omission, as almost all product analytics are time-bound. Finally, failing to discuss scalability solutions like partitioning for a high-volume fact_events table is a missed opportunity for a senior candidate.

What usually comes next

Expect questions about scalability and evolving schemas. For instance, "The fact_events table could have billions of rows. How do you keep queries fast?" (Answer: Partition the table by date, use a columnar database like BigQuery/Redshift, create materialized views for common queries). Or, "How do you handle changes to user attributes in dim_users, like a customer upgrading their plan?" (Answer: This tests knowledge of Slowly Changing Dimensions, or SCDs; Type 2 is a common approach).

A concrete example

To find users who used 'Feature A' but not 'Feature B', you could run a query like this: SELECT DISTINCT t1.user_id FROM fact_events t1 WHERE t1.feature_id = 'feature_A_id' AND t1.user_id NOT IN (SELECT t2.user_id FROM fact_events t2 WHERE t2.feature_id = 'feature_B_id');. With 10 million users generating 20 events per day, the fact_events table grows by 200 million rows daily. This volume makes partitioning the fact table by timestamp (e.g., daily partitions) essential for performance.