Designing a warehouse model for feature adoption
Dimensional modeling skill.
Star schema with a feature-usage fact table at a defined grain, surrounded by user, feature, date, and device dimensions.
One giant wide table or modeling without defining the grain.
WHAT THIS TESTS Whether you understand dimensional modeling and can translate a business question, feature adoption, into a performant, queryable schema.
A GOOD ANSWER COVERS Start by declaring the grain, the most important modeling decision: one row per feature-interaction event, capturing who used which feature, when, and on what device. Build a star schema. The central fact table, fact_feature_usage, stores foreign keys to dimensions plus measures and degenerate attributes like session id. Surround it with conformed dimension tables: dim_user with attributes such as signup date, plan, and cohort; dim_feature with feature name, category, and release date; dim_date with calendar attributes for easy period rollups; and dim_device with platform, OS, and app version. Keep dimensions denormalized enough to avoid excessive joins, favoring a star over a snowflake for analytic speed. Use surrogate keys and handle slowly changing dimensions so historical attributes like a user's plan at event time are preserved with type 2 tracking.
HOW METRICS FALL OUT Adoption KPIs such as percent of active users who used a feature, or week-over-week adoption, become straightforward aggregations of the fact joined to dimensions, often materialized into a summary or aggregate table for dashboard latency.
COMMON WRONG ANSWERS One enormous wide table that is impossible to maintain and bloats storage. Skipping grain, which makes counts ambiguous. Over-normalizing into a deep snowflake that requires many joins and slows the dashboard. Storing metrics without the dimensions needed to slice them.
LIKELY FOLLOW-UPS How do you handle late-arriving events? Type 1 versus type 2 SCD trade-offs? When do you pre-aggregate?
ONE CONCRETE EXAMPLE To show adoption of a new export feature by plan, you join fact_feature_usage to dim_feature filtered to export, to dim_user for plan, and to dim_date for the week, then compute distinct users per plan over distinct active users, all from a clean star that the BI tool queries in seconds rather than scanning a sprawling raw events table.
Interview question
When modeling feature-adoption events in a warehouse star schema, which decision should you make first and why?
- a.Choose surrogate versus natural keys to optimize joins
- b.Declare the grain of the fact table so counts are unambiguousCorrect
- c.Decide between a star and snowflake to minimize storage
- d.Pick the BI tool so the schema matches its query syntax
Why? this is the answer
Declaring grain is the foundational step; every measure and dimension depends on what one fact row represents. Key choice, schema shape, and tooling are secondary decisions made after the grain is fixed.
Just read this? Test yourself on what you have been reading.
Read the original → en.wikipedia.org
- #dimensional-modeling
- #star-schema
- #data-warehouse
- #analytics
- #etl
Put your scrolling time to good use
Learn one idea, try a quiz and save useful cards for revision. Tezvyn makes it easy to learn and stay current in your tech field, a few minutes at a time.
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. Every open role lists the topics its interview covers, so you can prepare for the real thing rather than guessing.
See open roles