Top 30 Data Modeling Interview Questions and Answers
30 multiple-choice questions on Data Modeling, drawn from 30 bites out of the 30 tagged Data Modeling on Tezvyn. Answer them here or read straight down. Every question carries the correct option, why it is correct, and a link to the bite it came from.
30 questions. Pick an answer, or open “Show the answer” to read it.
Answers are graded in your browser. Nothing is saved, and no XP or streak is earned here. The app keeps score.
Question 1 of 30
What is the main reason to place a unique constraint on a non-primary column such as email?
Show the answer
Answer: c · To enforce business-level uniqueness while keeping a stable primary key for joins
The card gives email as an alternate key that stops duplicate signups while the auto-incrementing primary key stays the stable join target. The distractor that it serves as the main row identifier conflates a unique constraint with a primary key, a red flag the card explicitly warns against.
Read the full bite: What is the difference between primary, foreign, and unique keys?
Question 2 of 30
What primary challenge did the relational model address compared to earlier database systems?
Show the answer
Answer: c · The need for complex procedural code to navigate specific data storage paths
The card states that before the relational model, "Retrieving data required writing complex procedural code to navigate specific data paths," which was inflexible. The relational model aimed to separate the logical data structure from its physical implementation. Option A is a distractor because while hierarchical models were predecessors, the core problem was the procedural navigation within them, not just the representation of hierarchies.
Read the full bite: The Relational Model: Data as Simple Tables
Question 3 of 30
A social app adds a like_count column directly on the posts table instead of counting rows in a likes table on every read. According to the card, what new problem does this denormalization introduce?
Show the answer
Answer: a · The count can drift out of sync if updates aren't handled atomically, requiring periodic reconciliation
The card's example warns that a missed update or race condition can leave the count wrong, requiring periodic reconciliation, that is the core cost of denormalizing. The tempting wrong answer has it backwards: the point of adding the column is that reads become a fast single column fetch, not a join.
Read the full bite: When to intentionally denormalize a schema
Question 4 of 30
In the normalized design, CustomerName and CustomerAddress are moved out of the Orders table primarily to eliminate which kind of dependency?
Show the answer
Answer: b · A transitive dependency through CustomerID
Customer attributes depend on CustomerID, a non-key column in Orders, so the dependency is transitive and removing it achieves 3NF. A partial dependency would involve only part of a composite primary key, which is a 2NF concern.
Question 5 of 30
Why can't a many-to-many relationship between Students and Courses be modeled with just a single foreign key column on one of those tables?
Show the answer
Answer: c · A single foreign-key column can hold only one related value per row
One foreign-key column stores a single reference per row, so it can only express one side relating to many, not both sides being many. A junction table with two foreign keys is required to represent the full set of pairings.
Read the full bite: Modeling one-to-many versus many-to-many relationships
Question 6 of 30
When calculating weekly cohort retention, why is computing week_offset from each user's signup_date preferable to grouping activity by calendar week?
Show the answer
Answer: d · It ensures retention measures each cohort's return behavior relative to its own signup origin
Computing week_offset anchors every activity to the user's specific signup cohort, which is the defining requirement of cohort retention; option C describes grouping all users into calendar activity weeks, which is the classic global active users trap that fails to measure cohort-specific return behavior.
Read the full bite: Describe the SQL and data model for weekly cohort retention
Question 7 of 30
A category tree is read constantly to render menus but almost never restructured. Which model best fits, and why?
Show the answer
Answer: a · Nested set, because subtree reads are a single range query
Nested set encodes descendants as a left/right range, so a whole subtree is one indexed query, ideal for read-heavy trees. Its weakness is expensive writes, which barely matters here since the tree is almost never restructured.
Read the full bite: Adjacency List versus Nested Set for hierarchies
Question 8 of 30
What is the primary advantage of employing a surrogate key as the primary key for a table containing core business entities?
Show the answer
Answer: a · It ensures that relationships between tables remain stable even if the real-world data they represent changes.
The card emphasizes that surrogate keys solve the stability problem by decoupling internal linking from volatile real-world data, preventing painful cascading updates. Option C is incorrect because surrogate keys are meaningless and exposing them externally is a 'footgun'.
Read the full bite: Surrogate Keys: Stable IDs for Unstable Data
Question 9 of 30
Why model workflow status as a constrained enum or lookup table rather than a free-text column?
Show the answer
Answer: b · It prevents inconsistent values and enables reliable filtering and transitions
A constrained status set guarantees consistent values, so filters and workflow transitions are reliable; free text invites typos that fragment queries. Storage size and the authors relationship are unrelated to this modeling choice.
Question 10 of 30
In the two-table versioning design, what is the main benefit of storing current_version_id in the articles table?
Show the answer
Answer: c · It lets the application fetch the latest article state without scanning the full version history
The correct answer is C because the pointer provides immediate access to the current snapshot, avoiding costly history scans. Distractor B is wrong since the design intentionally stores full snapshots rather than relying on diff reconstruction for lookups.
Read the full bite: Design a content versioning system with history and revert
Question 11 of 30
What specific type of data redundancy does Fourth Normal Form (4NF) primarily address that BCNF does not?
Show the answer
Answer: c · Redundancy from combining multiple independent lists of facts about a single entity.
Correct answer C directly describes the core problem 4NF solves: the redundancy arising when a table combines two or more independent, multi-valued lists of facts about a single entity. Option A describes the type of redundancy addressed by BCNF, which focuses on functional dependencies where a determinant is not a superkey, not on independent multi-valued relationships.
Read the full bite: Fourth Normal Form (4NF): Isolating Independent Facts
Question 12 of 30
For which scenario are nested Pydantic models most appropriate?
Show the answer
Answer: c · To accurately represent and validate hierarchical data, such as JSON with nested objects.
Nested Pydantic models are specifically designed to handle and validate complex, hierarchical data structures like JSON objects containing other objects. Using them for flat data is explicitly advised against, as it adds unnecessary complexity.
Read the full bite: Nested Pydantic Models: Composing Complex Data
Question 13 of 30
What is the fundamental reason querying NoSQL databases requires model-specific approaches rather than a universal language like SQL?
Show the answer
Answer: c · The query method in NoSQL is inherently dictated by the database's specific data model, such as key-value, document, or graph.
The card emphasizes that "The query method is tied to the data's shape" and "Querying in NoSQL is specific to the database's data model." This means the underlying data structure (key-value, document, graph) directly determines how data can be accessed. While NoSQL handles unstructured data, it also handles semi-structured data, and the core reason for model-specific querying is the data model itself, not solely the unstructured nature of the data.
Read the full bite: Querying NoSQL: It Depends on the Data Model
Question 14 of 30
When implementing a last-touch attribution model in your warehouse, which approach correctly assigns credit for a signup?
Show the answer
Answer: a · Select touches falling within 30 days before the signup, then use a window function to keep only the latest touch per user.
Option A is correct because it applies the 30-day lookback window and uses a window function to isolate the final eligible touch per user. Option D is tempting but wrong because ignoring the lookback window could credit a touch from months before the signup, which violates the model's intent.
Read the full bite: How would you implement a last-touch attribution model for user signups?
Question 15 of 30
A product catalog has items with very different attributes per type, is read mostly by key, and has high write volume. Per the card's example, which storage model fits best and why?
Show the answer
Answer: b · A document model, since each product can be fetched as a self-contained record without needing joins across varying attributes
The card's example says this exact scenario fits a document model because each product fetches as a self-contained document without joins. The tempting wrong answer, that document databases are always faster at scale, is explicitly flagged in the card as a common wrong answer.
Read the full bite: How do you choose between relational and NoSQL databases?
Question 16 of 30
Why is embedding the full list of liking user IDs inside each post document a poor choice for a popular social platform?
Show the answer
Answer: b · The liker array is unbounded, so it can exceed the document size limit and forces rewriting the whole document on each like
Unbounded growth collides with the document size cap and causes costly full-document rewrites and contention per like. Document stores do support arrays and offer lookups, so the other options are false.
Read the full bite: Embed or reference likes in a document database?
Question 17 of 30
To query a DynamoDB table by a non-key attribute without scanning, what is the correct approach and its main tradeoff?
Show the answer
Answer: a · Create a global secondary index on the attribute; it adds storage and write capacity cost and is eventually consistent
A GSI turns the lookup into a targeted query but duplicates data, consumes write capacity per base write, and is eventually consistent. A filtered Scan still reads the whole table, so it is not a real solution.
Read the full bite: Add a second access pattern to a key-value store
Question 18 of 30
After defining productSchema, which line gives you the constructor used to create and query Product documents?
Show the answer
Answer: c · mongoose.model('Product', productSchema)
mongoose.model compiles a schema into a model, the queryable constructor. new mongoose.Schema defines structure only, and mongoose.connect opens a database connection.
Question 19 of 30
Which architecture best insulates a cross-platform ad system's core campaign model from external API volatility and schema drift?
Show the answer
Answer: c · Maintain a platform-agnostic canonical model, use provider-specific adapters to transform into native schemas, and deploy asynchronously with validation
A canonical model captures marketing intent without referencing any provider schema, while adapters isolate platform-specific mappings and constraints, and an async pipeline handles partial failure gracefully. Option D is wrong because a single sparse table tightly couples the system to every platform's fields and synchronous calls block users and eliminate failure isolation.
Read the full bite: How would you model cross-platform ad campaign data and adaptation logic?
Question 20 of 30
A user adds two identical t-shirts, priced at $25 each, to their cart. Which payload best follows standard analytics practices for an `add_to_cart` event?
Show the answer
Answer: d · { "currency": "USD", "value": 50.00, "items": [{ "item_id": "TSHIRT01", "price": 25.00, "quantity": 2 }] }
The standard schema uses an `items` array where each object represents a unique product type. The `quantity` property correctly specifies how many units were added. Duplicating items in the array is inefficient and non-standard.
Read the full bite: Describe the client-side event for an 'Add to Cart' button
Question 21 of 30
A product manager asks you to build a weekly active user dashboard. What is the most important first step you should take?
Show the answer
Answer: d · Ask the product manager to clarify the specific user action that defines 'active'.
The correct answer is B because clarifying business requirements, like the definition of 'active', is the essential first step. Jumping straight to designing a schema (C) or writing a query (B) is premature and can lead to building the wrong dashboard.
Read the full bite: How would you build a weekly active user dashboard?
Question 22 of 30
An analyst is asked to build a WAU dashboard. Which action should come first to prevent metric errors?
Show the answer
Answer: b · Define the specific feature event, user identifier, and timestamp for active usage
Defining the atomic event, user ID, and timestamp must come first because you cannot accurately model a user-period table or configure BI measures without knowing what counts as active. Option C is tempting because building a user-period table is essential, but creating it before defining the active event leads to incorrect or incomplete data modeling.
Read the full bite: What data do you need and what steps build a WAU dashboard?
Question 23 of 30
Which combination most reliably prevents double-crediting when two concurrent requests try to finalize the same referral?
Show the answer
Answer: d · A single atomic transaction with a conditional state update plus an idempotency key
An atomic transaction with a conditional transition lets only one request move the referral to credited, and an idempotency key absorbs retries, guaranteeing exactly-once payout. Later reconciliation, scaling, or caching do not prevent the concurrent double-write.
Read the full bite: Design a referral feature's lifecycle and races
Question 24 of 30
What property of graph databases makes deep multi-hop traversals scale with the explored subgraph rather than the total number of nodes?
Show the answer
Answer: c · Index-free adjacency via direct node-to-node pointers
Index-free adjacency means each node holds direct references to its neighbors, so traversing an edge is a pointer hop and cost tracks the subgraph. B-tree foreign-key indexes are exactly what relational joins rely on and what makes deep traversals expensive there.
Read the full bite: When a graph database beats relational or document stores
Question 25 of 30
Why is encoding a value into the event name, like 'video_played_30s', considered an anti-pattern?
Show the answer
Answer: b · It explodes event cardinality and should instead be a typed property like duration_seconds
Values belong in typed properties so the event name stays stable and low-cardinality; baking values into names creates an unbounded set of event types. It is not a casing or size issue.
Question 26 of 30
You store a precomputed review_count on each product row to speed up reads. What is the primary cost of this denormalization?
Show the answer
Answer: b · Every review write must also update and keep the count consistent
Denormalization trades read speed for write complexity and consistency risk: the duplicated count must be maintained on every review change or it drifts from the source data. Reads get faster, not slower, and the table remains fully indexable.
Read the full bite: Denormalization: trading write cost for read speed
Question 27 of 30
What is the best practice for storing event timestamps to support consistent daily reporting across global timezones?
Show the answer
Answer: b · Store UTC plus the source IANA timezone, and convert to a single reporting zone at query time
UTC storage plus an IANA zone keeps data unambiguous and DST-correct while letting any reporting zone be applied at query time. Naive local times, fixed offsets, and per-session bucketing all produce inconsistent day boundaries.
Read the full bite: Define a consistent day across timezones
Question 28 of 30
Using a 30-minute inactivity timeout, how should sessionization treat a user who views pages, idles 40 minutes, then views more pages?
Show the answer
Answer: c · As two separate sessions split at the 40-minute gap
A gap exceeding the inactivity timeout closes the current session and starts a new one, so the 40-minute idle splits the activity into two sessions. Same user and day does not keep events in one session once the gap is exceeded.
Read the full bite: Sessionizing clickstream events into sessions
Question 29 of 30
A user on the Pro plan downgrades to Free in the middle of their billing period. According to the recommended entitlements design, what happens to their access?
Show the answer
Answer: d · They keep Pro-level access until the current paid period ends, and the entitlement service only starts returning Free-tier limits at renewal.
Downgrades take effect at the end of the current paid period so users keep the access they already paid for, with new limits starting only at renewal. Applying it immediately, as in A, is exactly the clawback mistake the card warns against.
Question 30 of 30
In a time-series database, why is storing a unique per-reading request ID as a tag a serious modeling mistake?
Show the answer
Answer: c · It explodes series cardinality, bloating the index and memory usage
Each unique tag combination creates a new series, so a per-reading unique value makes cardinality unbounded and exhausts memory. Tags are indexed strings meant for low-cardinality, queryable metadata.
Could you explain these out loud?
That is what an interview actually tests. Tezvyn gives you questions like these with 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.