Design a data model for feature adoption tracking

WHAT THIS TESTS: This question evaluates your ability to design a dimensional data warehouse schema for high-volume, append-only event data while optimizing for analyst and PM accessibility. The interviewer cares about your understanding of fact and dimension tables, handling time-series cardinality, and making set-difference queries performant and intuitive without requiring complex nested SQL.

A GOOD ANSWER COVERS: First, an event fact table with columns like user_id, feature_key, event_timestamp, session_id, device_id, and event_properties. Second, dimension tables for users and features to normalize slowly changing attributes like plan_type or feature_category. Third, a materialized user_feature_summary table derived from the fact table containing user_id, feature_key, first_used_date, last_used_date, and total_uses so a PM can write a simple LEFT JOIN or NOT EXISTS query to find Feature A adopters who never used Feature B. Fourth, physical layout details such as partitioning the fact table by event_date and clustering on user_id plus feature_key to keep scan costs low at billions of rows. Fifth, a clear data lineage showing how raw events flow into the summary via an ETL or dbt model scheduled daily or hourly.

COMMON WRONG ANSWERS: Proposing a single users table with boolean columns like used_feature_a and used_feature_b. This creates a sparse schema that requires DDL migrations on every new feature and loses temporal granularity. Another red flag is storing one row per user per day with every feature as a column, which explodes width and complicates version control. Suggesting the PM should just write a self-join against the raw event log without any aggregation layer also signals poor empathy for the end user and ignores query cost.

LIKELY FOLLOW-UPS: How would you handle feature renaming or key changes without breaking historical queries? What is the retention policy and how do you handle GDPR deletes across partitioned tables? How would you extend this model to track not just binary adoption but depth of engagement, such as five-minute usage versus a single click? At what scale would you move from a daily materialized view to a streaming aggregation, and what latency would PMs tolerate?

ONE CONCRETE EXAMPLE: A user clicks the new dashboard widget. The SDK sends an event with feature_key set to dashboard_widget_v2, user_id 98765, and event_timestamp 2025-03-15T09:23:11Z. The event lands in the fact table. The nightly dbt job upserts the user_feature_summary row for user 98765 and dashboard_widget_v2, setting first_used_date to 2025-03-15 if null and incrementing total_uses. The PM then runs SELECT u.user_id FROM user_feature_summary u WHERE u.feature_key = 'dashboard_widget_v2' AND NOT EXISTS (SELECT 1 FROM user_feature_summary x WHERE x.user_id = u.user_id AND x.feature_key = 'legacy_dashboard') to get the exact cohort.

Source: countly.com