Design a data model for feature adoption tracking
Tests dimensional modeling for high-volume events so PMs can query Feature A not B without complex SQL. A strong answer uses an event fact table plus a materialized user-feature summary. Red flag: a wide user table with boolean columns per feature.
What's really being asked
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.
The full answer
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.
The mistakes people make
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.
What usually comes next
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?
A 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.
Interview question
Which schema design best enables PMs to efficiently find users who adopted Feature A but never used Feature B without writing complex nested SQL against billions of events?
- a.A single event fact table that PMs query directly using self-joins and nested aggregations
- b.An event fact table paired with a derived user_feature_summary table for adoption metricsCorrect
- c.A daily user snapshot table with one row per user and wide feature columns per day
- d.A single users table with boolean columns like used_feature_a and used_feature_b
Why? this is the answer
The materialized user_feature_summary lets PMs write simple LEFT JOIN or NOT EXISTS queries for set differences, whereas querying the raw fact table directly forces complex nested SQL and high scan costs. The boolean user table is also a red flag because it creates a sparse schema that requires DDL migrations for every new feature.
Just read this? Test yourself on what you have been reading.
Read the original → countly.com
- #data modeling
- #analytics engineering
- #data warehouse
- #product analytics
- #sql
You just looked this up. Could you explain it out loud?
That is the part interviews actually test. Tezvyn takes questions like this one and gives you what the interviewer is really checking, the answer that lands, and the mistake that ends the conversation, in the four minutes before your next meeting.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Open roles that interview on data modeling — each one lists the topics its interview covers.
See open roles