Data Modeling
30 bites tagged Data Modeling — interview questions with model answers, and 60-second explainers.
Designing a tiered entitlements backend
Plans map to features and limits, subscriptions link account to plan, an entitlement check enforces access; handle upgrade/downgrade with effective dates and proration. Whether you model entitlements decoupled from plans.
Define a Mongoose schema and model
New mongoose.Schema with field options (type, required, default), then mongoose.model('Product', schema) to get a model. schema syntax and the schema-to-model step.
Modeling IoT data with tags and fields
Tags are indexed identifying metadata, fields are unindexed measured values, and tag cardinality drives memory. time-series schema design. putting high-cardinality unique IDs in tags and exploding the index.
Sessionizing clickstream events into sessions
Order events per user, split on inactivity gap, assign session ids, pick event or session grain. grouping events into sessions and grain choice.
Denormalization: trading write cost for read speed
Duplicate or precompute data to avoid joins, accept harder writes and consistency risk, justify by read-heavy access. deliberate redundancy for read performance.
When a graph database beats relational or document stores
Deeply connected data, variable-depth traversals, fraud or recommendation paths, index-free adjacency. fit between graph data and traversal queries.
Embed or reference likes in a document database?
Embedding is fast for small bounded lists but unbounded likes hit document size limits; referencing scales for high-cardinality, write-heavy likes. document modeling driven by access patterns and growth.
How do you choose between relational and NoSQL databases?
Relational gives schema, joins, and ACID for structured related data; document gives flexible schema and horizontal scale for varied or denormalized data. ability to match a data model to requirements.
Adjacency List versus Nested Set for hierarchies
Adjacency list is simple writes but recursive reads; nested set is fast subtree reads but costly writes. read-versus-write trade-offs in tree storage. ignoring recursive CTEs or the wide updates nested sets need.
Modeling one-to-many versus many-to-many relationships
One-to-many uses a foreign key on the many side; many-to-many needs a junction table with two foreign keys. cardinality and junction-table modeling. storing comma-separated IDs instead of a junction table.
Normalizing a flat orders table to 3NF
Split repeating data, remove partial dependencies, remove transitive dependencies, define keys. applying 1NF, 2NF, 3NF rules concretely. jumping to tables without naming which dependency each step removes.
When to intentionally denormalize a schema
Identify read-heavy join cost, duplicate or precompute data, and own the consistency burden. trading read speed for write complexity deliberately. denormalizing prematurely or ignoring how duplicates drift.
Data model for an editorial calendar
A content entity with publish_date and a status enum, a foreign key to authors, indexes on date and status. relational modeling for a CMS workflow. storing author as free text or status as an arbitrary string.
Add a second access pattern to a key-value store
Add a global secondary index on EmailAddress, weighing extra storage, write amplification, and eventual consistency. secondary indexing in NoSQL. a full scan with a filter, or assuming indexes are free.
Define a consistent day across timezones
Store events in UTC, capture the local/source timezone, then convert to a single reporting timezone at query time. consistent date bucketing across global timezones.
Design an analytics event schema
Consistent object-action naming, snake_case, typed properties with units, and shared context like user, session, timestamp. discipline in analytics taxonomy.
Design a referral feature's lifecycle and races
A referral entity with explicit states, a unique constraint on the invited user, and atomic transactions plus idempotency to prevent double credits. modeling a stateful flow with idempotency and concurrency safety.
How would you implement a last-touch attribution model for user signups?
Tests your ability to translate marketing concepts into warehouse SQL. A strong answer covers UTM/pageview events, sessionized tables, and a windowed join for the last touch within 30 days of signup.
Describe the SQL and data model for weekly cohort retention
Join users and activity, compute week offset per user, group by cohort and offset for retention. Anchoring activity to signup cohort. Counting active users globally without cohort anchoring.
What is the difference between primary, foreign, and unique keys?
This tests relational integrity basics. Answer: primary keys identify rows, foreign keys reference tables, and unique keys are alternate candidates. Red flag: saying unique keys are just for indexing or omitting a non-PK example like email.
How would you model cross-platform ad campaign data and adaptation logic?
Propose a canonical model, platform adapters mapping copy to each schema, and an async pipeline with validation. Separating campaign intent from platform execution.
Design a content versioning system with history and revert
Versions table with article_id, version_num, content_snapshot, timestamp; APIs for createVersion, getHistory, restoreVersion. Can you model immutable history simply? Diff-only storage without latest lookup or mutating rows in place.
What data do you need and what steps build a WAU dashboard?
Tests defining a metric, modeling events, and wiring them into a BI tool. A strong answer names the feature event, sets a rolling 7-day window, counts distinct users by period, and configures the BI layer. Red flag: jumping to charts before defining active.
How would you build a weekly active user dashboard?
This tests translating a business need into a technical spec. First, clarify the definition of "active." Then, identify the event data needed (user_id, timestamp). Finally, outline the ETL and BI tool steps. A red flag is not clarifying the metric first.
Get Data Modeling bites daily.
Five a day, five minutes, offline. With quizzes so it sticks.
Open testing — you’ll join as an early tester.