Back to interview questions

100+ Data Modeling Interview Questions and Answers

100+ Data Modeling Interview Questions and Answers

Explore 120 data modeling interview questions and answers on keys, normalization, dimensional models, history, schema evolution and design trade-offs.

Explore 120 data modeling interview questions and answers on keys, normalization, dimensional models, history, schema evolution and design trade-offs.

By

Team Sherlock.sh

Add Sherlock as a preferred source on Google

Data modeling interviews reveal whether a candidate can translate a business rule into a structure that remains meaningful when records change, systems disagree and reports combine data. An attractive entity-relationship diagram is only a starting point. Recruiters should listen for clear statements of identity, grain, cardinality and time: what one row represents, which relationships are permitted, and whether a value describes the present or a historical state.

These 100+ Data Modeling Interview Questions and Answers cover 120 questions across beginner, intermediate and experienced levels. The guide moves from entities, keys and normalization to dimensional models, historical changes, document boundaries and the consequences of distributed storage. Use a business scenario relevant to the role, such as orders with partial shipments or customers whose territories change, so candidates can explain the choices behind their schemas.

How can recruiters tell whether a candidate understands a data model or has simply memorized a familiar schema?

Ask the candidate to trace one real-world event through the proposed tables and calculate a small result. Then introduce a change: an order has two shipments, a customer record arrives late, or a metric must preserve its original classification. Strong candidates identify the assumption that changed, show which keys or relationships need attention and explain how existing consumers would be protected.

Sherlock AI helps hiring teams assess signals of potential AI cheating, deepfakes, impersonation and unauthorized assistance during remote interviews. Pair those signals with schema walkthroughs and practical follow-up questions about grain, constraints and historical meaning. The goal is a fair assessment of independent reasoning; a signal alone is not conclusive proof of misconduct.

Data Modeling Interview Questions

Select a sequence that fits the role and give candidates the business assumptions they need. A transactional application model and a reporting model can describe the same business differently. The complete question bank comes first; identically numbered answers follow.

Data Modeling Interview Questions for Beginners

Start with identity, relationships and basic integrity. Ask candidates to explain a diagram in ordinary business language and illustrate a rule with a few valid and invalid records.

  1. What is data modeling, and what problem does it solve?

  2. How do conceptual, logical and physical data models differ?

  3. What is the difference between an entity and an entity instance?

  4. How do you decide whether something should be an attribute or a separate entity?

  5. What is a domain in a data model?

  6. What does a primary key guarantee?

  7. How do candidate keys, superkeys and alternate keys differ?

  8. When would you choose a natural key or a surrogate key?

  9. What is a composite key, and when is it appropriate?

  10. What does a foreign key enforce?

  11. How are relationship cardinality and optionality different?

  12. How do you represent a one-to-many relationship?

  13. How do you represent a many-to-many relationship?

  14. How can a relational schema enforce a one-to-one relationship?

  15. What is a recursive relationship?

  16. What is a weak entity, and how is it identified?

  17. What is normalization trying to achieve?

  18. What are insertion, update and deletion anomalies?

  19. What does first normal form mean in a practical design?

  20. What is a functional dependency?

  21. How does second normal form remove partial dependencies?

  22. How does third normal form address transitive dependencies?

  23. Why does adding a generated ID not normalize a table?

  24. What does a lossless decomposition mean?

  25. When is denormalization a reasonable choice?

  26. What does NULL mean, and why is it different from zero or an empty string?

  27. How do NOT NULL, CHECK and DEFAULT serve different purposes?

  28. How should a model choose deletion behavior for relationships?

  29. How do you choose appropriate data types for attributes?

  30. How should monetary amounts and currencies be modeled?

  31. How do dates, local times and instants differ in a data model?

  32. What is the difference between stored and derived attributes?

  33. What belongs in a useful data dictionary?

  34. How do you read and validate an entity-relationship diagram?

  35. What is grain, and why should it be stated before adding columns?

  36. How do operational and analytical models serve different workloads?

  37. What are facts and dimensions in a basic dimensional model?

  38. Why is a star schema useful for introductory reporting models?

  39. How would you model an order header and its lines?

  40. How do you validate a small data model before implementation?

Data Modeling Interview Questions for Intermediate

Explore models that support useful analysis without losing meaning. Change the reporting grain, introduce a historical update or add a many-to-many relationship to test how the candidate preserves correct results.

  1. How would you declare the grain of an order-line fact table?

  2. When would you use transaction, periodic snapshot or accumulating snapshot facts?

  3. Why can an inventory balance be summed across products but not across dates?

  4. How should a model aggregate percentages and average prices?

  5. How would you keep order-level charges from inflating line-level revenue reports?

  6. What makes a dimension conformed across different fact tables?

  7. How do role-playing dimensions affect order-date and shipment-date analysis?

  8. When should an invoice number remain in a fact table as a degenerate dimension?

  9. What problem does a junk dimension solve, and when does it become unsuitable?

  10. How can a factless fact table reveal events that did not occur?

  11. How would you model sales credited to multiple representatives?

  12. How do you choose between Type 1 and Type 2 handling for an attribute?

  13. How would you resolve a fact to the correct Type 2 dimension version?

  14. What checks would you apply to a Type 2 customer dimension?

  15. How do you handle a fact that arrives before its dimension attributes?

  16. What changes when a late fact belongs to an already closed reporting period?

  17. How should a dimension represent unknown, not applicable and not yet available?

  18. When is a snowflaked dimension preferable to a flattened star dimension?

  19. How can a relation satisfy third normal form but violate Boyce-Codd normal form?

  20. How would you model a calendar with fiscal periods and multiple date roles?

  21. How do you model a recurring event without losing its original time-zone meaning?

  22. How would you model returns without corrupting original sales history?

  23. When should an aggregate table be added to an analytical model?

  24. How can a materialized view change performance without changing the logical model?

  25. How should index design relate to logical keys and access patterns?

  26. How can denormalization be introduced without creating competing sources of truth?

  27. When should related data be embedded in a document rather than referenced?

  28. What makes an embedded array dangerous as a model grows?

  29. How would you model product attributes that differ by product category?

  30. How would you model a hierarchy with variable depth?

  31. How do you model a relationship that carries its own business attributes?

  32. How would you enforce that each active subscription has one current plan?

  33. How would you model a rule that total allocations cannot exceed a budget?

  34. What model-level tests can detect a join that silently drops facts?

  35. How can you test that a table still satisfies its declared grain?

  36. How would you detect a many-to-many join before it inflates a dashboard?

  37. How should a model distinguish soft deletion from historical inactivity?

  38. How do you model a measure that is valid only for some event types?

  39. How would you model multiple currencies in a sales fact?

  40. When does a three-way relationship need a ternary table instead of separate pairwise tables?

Data Modeling Interview Questions for Experienced

Use these scenarios to assess architecture and judgment under change. Ask what evidence would expose a flawed model, which guarantees must hold during migration and how teams will own the model after launch.

  1. A report doubles revenue after a new join. How would you diagnose the model?

  2. How would you combine facts recorded at different grains without multiplying measures?

  3. When does a system need both valid time and recorded time?

  4. How would you apply a retroactive correction without silently rewriting historical reports?

  5. How do event time and processing time affect an analytical model?

  6. How would you model deduplication when messages can be delivered more than once?

  7. What changes when events, rather than current rows, are the system of record?

  8. How would you model deletes in a change-data-capture pipeline?

  9. How would you make a backfill safe while live updates continue?

  10. How would you rename a widely used field without breaking consumers?

  11. How do you decide whether a schema change is backward compatible?

  12. What does field identity contribute to schema evolution in a lakehouse table?

  13. How would you choose a partition key for a large fact table?

  14. Why can a high-cardinality shard key still produce a hot shard?

  15. How should a model represent an invariant that spans shards or services?

  16. How would you prevent cross-tenant references in a shared relational schema?

  17. What must a reviewer check before relying on row-level security for tenant isolation?

  18. How would you separate sensitive identity data from analytical facts?

  19. How would you design deletion propagation across derived datasets?

  20. How do retention requirements change the physical and logical model?

  21. Two departments define an active customer differently. How would you resolve the model?

  22. What belongs in a data contract beyond column names and types?

  23. How would you use lineage to assess the impact of a model change?

  24. How should model ownership work when several domains share customer data?

  25. How would you combine customer identities after a merger?

  26. When is a graph model justified over relational relationships?

  27. How would you model entities with several subtypes?

  28. When does an entity-attribute-value model become a maintenance problem?

  29. How would you decide which constraints to enforce and which to monitor?

  30. What evidence would you require before denormalizing a production model?

  31. How would you diagnose a model that is slow only for the largest customer?

  32. How would you model a business process whose state can move backward?

  33. How would you model corrections to a financial or operational ledger?

  34. Why can daily distinct-customer counts not be added to obtain a monthly distinct count?

  35. How would you support both current and historically reproducible product hierarchies?

  36. What makes a model migration rollback plan credible?

  37. How would you validate a migrated model when row counts legitimately change?

  38. How would you balance a canonical enterprise model with domain-specific models?

  39. When does a fourth-normal-form decomposition remove redundant combinations without losing a real relationship?

  40. How would you explain a modeling decision when the available evidence is incomplete?

Data Modeling Interview Questions and Answers

The answers connect each modeling choice to the questions the data must answer and the rules it must preserve. SQL examples state platform-specific assumptions where they matter; common relational sketches emphasize schema meaning rather than a particular deployment tool. Treat sample domains and metrics as explicit examples, and confirm their definitions with the business before applying them to another system.

Data Modeling Interview Questions and Answers for Beginners

These answers establish the vocabulary of data modeling through concrete entities and relationships. Notice the difference between a business requirement, its logical representation and the physical mechanism used to enforce it.

1. What is data modeling, and what problem does it solve?

A data model describes the things a system records, how they relate and which rules make the records meaningful. It turns statements such as “an order belongs to one customer and contains several lines” into explicit identities, relationships and constraints. The aim is reliable meaning across writes, reports and integrations, rather than simply drawing tables.

For an online shop, a model distinguishes a product from an order line: the product describes something for sale, while the line records a particular purchase at an agreed price. Combining them without that distinction can make a catalog price change rewrite the apparent value of old orders. A useful model therefore starts with business behavior and preserves the distinctions users depend on.

2. How do conceptual, logical and physical data models differ?

A conceptual model identifies business concepts and relationships. A logical model specifies attributes, identifiers and rules without committing to every storage detail. A physical model translates those decisions into a particular database, including concrete types, constraints and access structures.

Level

Order example

Main decision

Conceptual

Customers place orders

What concepts and relationships exist?

Logical

Order has an identifier, customer and order date

What does each record mean and what must be true?

Physical

Tables, foreign keys and a chosen timestamp type

How does the selected platform enforce and store it?

These are views of the same design, not three unrelated documents. If the physical model permits several customers per order while the logical model permits one, the difference needs a business explanation or correction. An index change may leave the conceptual model untouched; a new shared-order feature may change all three.

3. What is the difference between an entity and an entity instance?

An entity type represents a category of things with a shared meaning, such as Customer or Order. An entity instance is one particular customer or order. Its identifier tells us which instance it is even when descriptive attributes change.

For example, customer 742 can change their display name without becoming a different customer. Conversely, two customers can have the same display name without being one instance. I would first define what counts as the same customer in the business process, then choose how to represent that identity. A table is one implementation of an entity type; not every table must correspond directly to a business entity.

4. How do you decide whether something should be an attribute or a separate entity?

I treat a value as an attribute when it describes its owner and has no independent identity or lifecycle in the required model. A separate entity is useful when it has its own attributes, relationships, multiple occurrences or independent management.

A simple delivery instruction can be an order attribute. A delivery address may deserve a separate representation if orders can have multiple destinations or addresses require validation and change tracking. That does not mean every reusable string needs a lookup table. The decision depends on the questions and rules the system must support, and an address captured on a completed order may be a historical snapshot rather than a pointer to a customer’s current address.

5. What is a domain in a data model?

A domain defines the permitted values and meaning of an attribute. It is more specific than a storage type: an integer can store many values, but a quantity domain might permit only whole numbers greater than zero, expressed in individual items.

For example, temperature is incomplete without its unit and measurement convention. The numeric value 30 means something different in Celsius and Fahrenheit. I would document the domain, represent units where they can vary, and enforce feasible rules in the database or validated boundary. Domain constraints cannot prove a measurement was collected correctly, but they can reject values that are invalid by definition.

6. What does a primary key guarantee?

A primary key identifies each row uniquely and does not allow null key components. It may be one column or a combination. Its purpose is stable row identity for references and updates, rather than describing the row to a person.

An order table can use order_id as its primary key while keeping a separately unique customer-facing order number. Choosing a primary key does not automatically prevent every business duplicate: two rows with different generated IDs could still represent the same imported order. That requires an additional uniqueness rule, such as a unique pair of source system and source order ID.

7. How do candidate keys, superkeys and alternate keys differ?

A superkey is a set of attributes that uniquely identifies a row. A candidate key is a minimal superkey: removing any attribute would lose that guarantee. The chosen candidate key becomes the primary key; the others are alternate keys.

If both employee_id and a mandatory, unique payroll number independently identify an employee, each is a candidate key. The pair of employee ID and employee name is a superkey, but is not minimal because the ID already identifies the row. “Minimal” concerns unnecessary attributes, not the fewest characters or the fastest index. Business assumptions about uniqueness must hold across all valid data, not just the current sample.

8. When would you choose a natural key or a surrogate key?

A natural key comes from the business domain, while a surrogate key is assigned primarily to identify a record. A natural key can make identity transparent; a surrogate can keep references stable when business codes change or several source systems use incompatible identifiers.

For example, a product can have an internal product_id and a separately unique SKU. Changing the SKU then does not require changing every foreign key. The surrogate does not replace the SKU uniqueness rule, however. I would evaluate stability, scope and reuse of the business identifier rather than assume generated IDs are always necessary or that a readable code is always permanent.

9. What is a composite key, and when is it appropriate?

A composite key identifies a row using more than one attribute. It is appropriate when the business identity is inherently scoped, such as a line number within an order. Line 1 alone is not unique, but (order_id, line_number) can be.

CREATE TABLE order_line (
    order_id INTEGER NOT NULL,
    line_number INTEGER NOT NULL CHECK (line_number > 0),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    PRIMARY KEY (order_id, line_number)
)

CREATE TABLE order_line (
    order_id INTEGER NOT NULL,
    line_number INTEGER NOT NULL CHECK (line_number > 0),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    PRIMARY KEY (order_id, line_number)
)

CREATE TABLE order_line (
    order_id INTEGER NOT NULL,
    line_number INTEGER NOT NULL CHECK (line_number > 0),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    PRIMARY KEY (order_id, line_number)
)

This isolated example establishes line identity; a complete schema would also reference the order table. A surrogate line ID is another option, particularly when other records reference lines frequently, but the unique order-and-line-number rule would still be needed. Joining on only line_number would mix unrelated orders.

10. What does a foreign key enforce?

A foreign key requires a referencing value to match an eligible key in another table, subject to the database’s null and matching rules. It prevents an order from naming a nonexistent customer, but it does not prove that the customer is active or authorized to place that order.

For a mandatory relationship, combine the foreign key with NOT NULL. In a simple single-column foreign key, null normally represents no reference and is not rejected by the foreign-key check itself. I also choose update and deletion behavior deliberately: removing a customer should not silently erase financial history merely because cascading deletion is convenient.

11. How are relationship cardinality and optionality different?

Cardinality describes how many instances can participate in a relationship; optionality describes whether participation is required. Together they express minimum and maximum participation, such as zero-to-many orders per customer and exactly one customer per order.

A foreign key from Order to Customer implements the direction of the reference. Making that column non-null makes a customer mandatory for each order. It does not require every customer to have at least one order. I would read both ends of an ER diagram aloud because a single “one-to-many” label can hide an important minimum-count rule.

12. How do you represent a one-to-many relationship?

Place the key of the “one” side as a foreign key on the “many” side. Each child then names its parent, while many child rows can use the same parent key. A customer can therefore have many orders without storing a list of order IDs inside the customer row.

CREATE TABLE customer (
    customer_id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);
CREATE TABLE sales_order (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customer(customer_id)
)

CREATE TABLE customer (
    customer_id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);
CREATE TABLE sales_order (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customer(customer_id)
)

CREATE TABLE customer (
    customer_id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);
CREATE TABLE sales_order (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customer(customer_id)
)

Here each order must identify an existing customer. A customer can exist without an order, which supports registering a customer before their first purchase. Adding UNIQUE(customer_id) to the order table would change the relationship by allowing at most one order per customer.

13. How do you represent a many-to-many relationship?

Use an associative or junction table whose rows represent the relationship itself. For students and courses, an Enrollment row connects one student to one course. This supports any number of courses per student and any number of students per course without repeating numbered columns.

CREATE TABLE enrollment (
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    enrolled_on DATE NOT NULL,
    PRIMARY KEY (student_id, course_id)
)

CREATE TABLE enrollment (
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    enrolled_on DATE NOT NULL,
    PRIMARY KEY (student_id, course_id)
)

CREATE TABLE enrollment (
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    enrolled_on DATE NOT NULL,
    PRIMARY KEY (student_id, course_id)
)

The parent foreign keys would be added against the Student and Course tables in the complete schema. The key shown means a student can enroll in a course only once. If retakes are allowed, the identity must include an offering or attempt; simply removing the uniqueness rule would lose the distinction between a legitimate retake and an accidental duplicate.

14. How can a relational schema enforce a one-to-one relationship?

A foreign key with a uniqueness constraint on the referencing column enforces at most one related row per parent. A shared primary key is a common implementation: the child’s primary key is also its foreign key to the parent.

For example, employee_private(employee_id PRIMARY KEY REFERENCES employee(employee_id), ...) permits at most one private-details row per employee and prevents orphan private-details rows. It still allows an employee with no such row. If both sides must exist together, additional transaction or application rules may be required. Splitting a table should have a reason, such as access boundaries or an optional lifecycle, rather than merely producing a more elaborate diagram.

15. What is a recursive relationship?

A recursive relationship relates instances of the same entity type. An employee’s manager can be another employee, so manager_id can reference employee_id in the same table. Null can represent a top-level employee when that is the chosen rule.

The foreign key checks that a named manager exists. It does not by itself prevent self-management or longer cycles such as A reporting to B while B reports to A. A row-level check can reject self-management, but validating the whole hierarchy needs a broader mechanism. I would also ask whether multiple simultaneous managers are allowed; that would require a relationship table rather than one manager column.

16. What is a weak entity, and how is it identified?

A weak entity depends on an owner for its identification in the chosen model. A room numbered 201 is not globally identifiable if each building can contain room 201; the combination of building ID and room number identifies it.

The room’s partial identifier is unique only within its building. A composite primary key makes that scope explicit, while a surrogate room ID can simplify references if the business uniqueness rule remains enforced separately. Existence dependency and deletion behavior should be discussed explicitly: being modeled as dependent does not automatically mean records should be physically deleted whenever the owner is retired.

17. What is normalization trying to achieve?

Normalization organizes relational data around dependencies so that facts are not repeated in ways that cause inconsistent updates. It separates facts with different determinants while retaining relationships through keys. The benefit is clearer ownership of each fact, not simply a larger table count.

If every order repeats the customer’s current contact phone, changing that phone requires updating many rows and can leave conflicting values. A Customer relation can own the current phone. But a phone deliberately captured for a particular delivery is a different fact and may belong with that delivery. Normalization depends on meaning: identical-looking values are not necessarily redundant facts.

18. What are insertion, update and deletion anomalies?

An anomaly occurs when the way facts are combined makes a legitimate operation lose information or produce contradictions. Insertion anomalies prevent recording one fact without an unrelated one; update anomalies require coordinated changes to repeated facts; deletion anomalies remove a fact that should survive.

Anomaly

Course-and-enrollment table example

Insertion

A new course cannot be stored until someone enrolls.

Update

Changing the course title requires editing every enrollment row.

Deletion

Deleting the last enrollment also removes the only record of the course.

Separating Course from Enrollment gives each fact its own lifecycle. The relationships still matter, so the redesigned schema must preserve the link through a course key. Splitting data without correct keys can exchange update anomalies for incorrect joins.

19. What does first normal form mean in a practical design?

First normal form requires attributes to hold single values from their defined domains, rather than repeating groups masquerading as columns. In practice, an order with product_1, product_2 and product_3 is a warning that order lines should be separate rows.

With a line table, a fourth product becomes another row rather than a schema change. Each line can carry its own quantity and price, and constraints can apply consistently. Atomicity is relative to the domain and required operations: a date need not be split into day, month and year merely because it has components. The important question is whether the value is one meaningful value for this attribute, or a hidden collection the application must parse to use correctly.

20. What is a functional dependency?

A functional dependency X → Y means that any two valid rows agreeing on X must also agree on Y. X determines Y according to the business rules. It is a statement about all permitted data, not a correlation discovered in a small sample.

If each product code identifies exactly one current product description, product_code → description holds in that model. The reverse usually does not: several product codes can share a description. Time and scope matter too. If codes are only unique within a supplier, the determinant is (supplier_id, product_code); omitting the supplier would create a false dependency and an incorrect key.

21. How does second normal form remove partial dependencies?

A relation is in second normal form when it is in first normal form and each non-prime attribute depends on the whole of every candidate key, rather than a proper subset. A non-prime attribute is one that belongs to no candidate key. Partial dependencies commonly appear with composite keys.

Consider Enrollment keyed by (student_id, course_id), with student_name and grade. The student name depends on student ID alone; the grade belongs to that student-course enrollment. Moving student name to Student removes the partial dependency. Adding a surrogate enrollment ID does not automatically repair the design: the original composite candidate key and its dependencies still matter.

22. How does third normal form address transitive dependencies?

Third normal form constrains functional dependencies so that a non-key fact is not improperly determined through another non-key fact. Formally, for each nontrivial dependency X → A, X must be a superkey or A must be prime. The common beginner case is a non-key attribute determining another non-key attribute.

In Employee keyed by employee ID, suppose department ID determines department name. Repeating department name in every employee row gives employee_id → department_id → department_name. A Department table can own the name, while Employee references the department. This assumes one current name per department; a historical department name captured at a specific event may require different semantics rather than blindly replacing it with today’s value.

23. Why does adding a generated ID not normalize a table?

A generated ID supplies row identity, but it does not remove dependencies among the other attributes. If each row repeats the current supplier name alongside a supplier code, those repeated names can still diverge even when every row has a unique numeric ID.

I would identify all candidate keys and business dependencies, then separate facts at the appropriate level. The ID can remain useful for references, but it is not evidence that the rest of the row is well modeled. A schema can have a primary key on every table and still contain duplicate business records, partial dependencies and contradictory descriptions.

24. What does a lossless decomposition mean?

A decomposition is lossless when joining the resulting relations reconstructs exactly the original valid relation without inventing or losing combinations. This is essential when normalization splits a table: the new tables must retain enough shared identity to reconnect the facts correctly.

If Order stores customer ID and Customer stores one row per customer ID, joining each order to its customer preserves the order’s identity. Splitting customer name and city into unrelated tables and joining on a non-unique name can instead invent combinations for people who share a name. I would verify the relevant keys and dependencies, not assume that any shared column makes a decomposition safe.

25. When is denormalization a reasonable choice?

Denormalization deliberately duplicates or precomputes information to serve a measured access need. It can simplify frequent analytical reads or reduce expensive joins, but it creates a responsibility to keep the copies consistent or explain their refresh delay.

For example, a reporting table may include product category beside each daily sales total to make common grouping straightforward. I would name the authoritative source, define how category changes affect history and specify how the derived table is rebuilt. Denormalization without those rules can make two reports disagree. A performance problem should be measured first, because missing indexes or a poorly written query may not require changing the logical model.

26. What does NULL mean, and why is it different from zero or an empty string?

NULL represents missing information in SQL; zero is a known numeric value and an empty string is a known string value in systems that distinguish it from null. Replacing missing measurements with zero can therefore change the business meaning and bias calculations.

If a satisfaction score has not yet been collected, zero might incorrectly imply the lowest possible rating. I would define whether missing means unknown, not applicable or not yet available, and model separate reasons when the distinction affects decisions. SQL’s three-valued logic also matters: comparisons with null usually produce unknown, so missing-value tests use IS NULL rather than = NULL.

27. How do NOT NULL, CHECK and DEFAULT serve different purposes?

NOT NULL requires a value; CHECK restricts permitted values or relationships within a row; DEFAULT supplies a value when an insert omits the column. A default is not a validation rule and does not justify inventing a business value.

CREATE TABLE stock_movement (
    movement_id INTEGER PRIMARY KEY,
    quantity INTEGER NOT NULL CHECK (quantity <> 0),
    status VARCHAR(20) NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending', 'posted'))
)

CREATE TABLE stock_movement (
    movement_id INTEGER PRIMARY KEY,
    quantity INTEGER NOT NULL CHECK (quantity <> 0),
    status VARCHAR(20) NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending', 'posted'))
)

CREATE TABLE stock_movement (
    movement_id INTEGER PRIMARY KEY,
    quantity INTEGER NOT NULL CHECK (quantity <> 0),
    status VARCHAR(20) NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending', 'posted'))
)

In this example an omitted status becomes pending, while an explicit invalid status is rejected. The quantity requires both a non-null value and a nonzero value; a check alone commonly permits an unknown result from null. Before choosing a default, I would confirm that the default state is truthful for every operation that relies on it.

28. How should a model choose deletion behavior for relationships?

Deletion behavior should reflect ownership, retention and audit requirements. Restricting deletion protects referenced records; cascading deletion removes dependent rows; setting a foreign key to null preserves a child while removing an optional association. These choices encode different business outcomes.

A temporary cart may own its cart lines, making cascade a sensible choice when the cart is removed. A completed invoice usually has a different retention obligation, so deleting a customer must not casually cascade through invoices. I would distinguish physical deletion from deactivation and define what happens to required references. A nullable foreign key is not enough if the remaining record becomes uninterpretable without its parent.

29. How do you choose appropriate data types for attributes?

Choose a type that matches the value’s semantics, valid range and required operations. An identifier that happens to contain digits may still be text if leading zeros matter or arithmetic is meaningless. A measurement needs both numeric precision and a known unit.

A postal code such as 02108 should not become integer 2108. A quantity of indivisible items can be an integer, while measured weight may need a decimal scale. I would also check platform behavior for overflow, string comparison and time handling. Type choices can enforce part of a domain, but they do not replace business constraints or clear attribute definitions.

30. How should monetary amounts and currencies be modeled?

Model the amount with an exact representation suitable for the domain, and associate it with a currency. A fixed-scale decimal is common; integer minor units can work when the scale is explicitly controlled. Binary floating-point is generally a poor default for amounts that require exact decimal accounting.

For instance, amount = 125.50 and currency = USD describe an amount, while 125.50 alone is ambiguous. Do not sum different currencies without a defined conversion basis. The precision needed for prices, rates and final settled amounts can differ, and currencies do not all share the same minor-unit convention. I would document rounding rules at the business operation where they apply, rather than round every intermediate value indiscriminately.

31. How do dates, local times and instants differ in a data model?

A date represents a calendar day, a local date-time represents wall-clock time in some locality, and an instant identifies a point on the global timeline. They answer different questions and should not be interchanged merely because a database offers a timestamp type.

A birthday is usually a date; a scheduled store opening can be a local time plus location rules; an observed payment event is usually an instant. For a future appointment, retaining the intended time zone can matter because an offset alone does not describe future daylight-saving rules. I would document which meaning each attribute carries and use a type and conversion policy appropriate to the chosen database.

32. What is the difference between stored and derived attributes?

A stored attribute is persisted directly; a derived attribute is calculated from other facts under a defined rule. Deriving avoids contradictory copies when the source values change, while storing a derived result can be useful for performance or to preserve the result of a historical decision.

Current age is usually derived from birth date and an as-of date, rather than updated in a column every birthday. An approved invoice total may need to preserve the amount agreed at issuance, even if current pricing rules later change. I would distinguish a recomputable convenience value from a historical business fact, then specify the calculation version and refresh behavior if a derived value is stored.

33. What belongs in a useful data dictionary?

A data dictionary records what entities and attributes mean, not only their SQL types. It should identify business definitions, grain, keys, units, allowed values, null meaning, ownership and relevant derivation rules. These details help different teams use the same field consistently.

An attribute named revenue is insufficient if one team interprets it before refunds and another after refunds. A useful entry defines the included transactions, recognition basis, currency and time boundary, with an owner for unresolved questions. Documentation should accompany the model and change with it; a beautifully formatted dictionary that disagrees with the enforced schema can be more misleading than a short accurate one.

34. How do you read and validate an entity-relationship diagram?

I read each relationship in both directions, including its minimum and maximum participation, and connect it to explicit business examples. I also inspect identifiers and attribute ownership rather than judging the design by how tidy the diagram looks.

For Customer and Order, I might say: “A customer can have zero or many orders; every order belongs to exactly one customer.” Then I test guest checkout, shared orders and imported orders against that statement. If a legitimate case does not fit, the model or its scope needs revision. Notation varies, so the diagram should include a legend and the database implementation should be checked against the rules the symbols imply.

35. What is grain, and why should it be stated before adding columns?

Grain states exactly what one row represents. It may be one order, one order line, one product per warehouse per day or one observed event. Once the grain is explicit, every attribute and measure must be meaningful at that level.

If a row represents an order line, the order’s shipping charge does not naturally become a line-level measure merely because it is repeated on each line. An order with three lines and a $9 shipping charge would appear to have $27 shipping if those repeated values were summed. I would keep the charge at order grain or apply a documented allocation, rather than rely on report authors to remember which columns cannot be summed.

36. How do operational and analytical models serve different workloads?

An operational model supports the transactions and invariants of a business process, such as creating orders and reserving stock. An analytical model supports consistent filtering, grouping and comparison across many observations. Their structures can differ while describing compatible business concepts.

Order entry may use separate order, line and payment relations with transaction rules. Sales analysis may expose a fact table at line grain with descriptive product and customer dimensions. Copying the operational schema directly into a dashboard can make common measures difficult or error-prone, while using a reporting aggregate as the operational source can lose detail needed to process corrections. The transformation between the models must preserve identifiers and declared meanings.

37. What are facts and dimensions in a basic dimensional model?

Facts record observations or events at a declared grain; dimensions provide descriptive context for filtering and grouping those observations. A sales fact might contain quantity and amount for one order line, linked to product, customer and date dimensions.

A dimension is not merely any table with text, and a fact is not merely any table with numbers. A product’s numeric package size can be descriptive context, while an attendance fact may record an event without a numeric measure. I would first define the business process and grain, then decide which attributes describe the context and which values quantify the observation. That distinction keeps aggregation behavior explainable.

38. Why is a star schema useful for introductory reporting models?

A star schema places fact tables alongside descriptive dimension tables with clear relationships. It gives report authors a recognizable path from filters such as product category or month to measures such as units sold, while making the grain of the observations explicit.

For a line-sales fact, a single product dimension row per referenced product key prevents product attributes from multiplying the fact rows during a join. If a supposed dimension contains several matching rows for that key, totals can inflate. The star layout alone does not guarantee correctness: relationship uniqueness, time interpretation and measure definitions still need validation. Its value is a usable semantic structure, not a universal promise of faster queries.

39. How would you model an order header and its lines?

I would put order-level facts on Order and item-level facts on OrderLine, with each line referencing its order. The header can hold the customer and order time; a line can hold product, quantity and the price agreed for that sale. Each row then has one clear grain.

A line key such as (order_id, line_number) permits the same product to appear twice when the business allows distinct configurations or prices. A unique (order_id, product_id) rule would prohibit that, so it must not be added without checking requirements. I would also avoid deriving historical line prices from the current catalog: changing today’s price must not silently change the meaning of yesterday’s sale.

40. How do you validate a small data model before implementation?

I walk representative and awkward business cases through the model, then check whether identifiers, constraints and queries express the intended rules. Typical cases show that the model can store normal activity; counterexamples reveal what it accidentally permits or prevents.

For a course-enrollment model, test a student with no enrollments, two students sharing a name, a retake, a canceled offering and an attempted duplicate registration. Verify that a proposed key distinguishes valid instances and rejects only genuine duplicates. I would also calculate one expected report by hand and compare it with the modeled joins. Agreement on these examples is stronger evidence than a diagram approved without testing its consequences.

Data Modeling Interview Questions and Answers for Intermediate

The intermediate answers show how facts, dimensions, temporal records and document boundaries behave with actual data. Worked examples make aggregation rules and historical lookup choices explicit, including cases where an apparently reasonable join changes the result.

41. How would you declare the grain of an order-line fact table?

Declare the grain as a business event before choosing columns: for example, one accepted order line at the time the order is placed. Then identify what uniquely distinguishes that event, such as the source system, order ID and line number. A database-generated row ID alone does not explain what the row means or prevent a duplicate business event.

For an order with two lines, the table has two rows. Line quantity and line net amount fit that grain; the full order shipping charge does not belong on both rows unless it is explicitly allocated. If partial shipments must be analyzed independently, create a shipment-line fact at its own grain rather than letting some order lines expand into several indistinguishable rows.

Test the grain statement against returns, amendments, canceled lines and repeated deliveries. These cases establish whether a row represents original intent, current state or an event history. That choice determines both the uniqueness rule and which totals are meaningful.

42. When would you use transaction, periodic snapshot or accumulating snapshot facts?

Choose the fact pattern according to the business question: individual events, state at regular intervals, or progress through a process. They can coexist because they answer different questions; one table need not serve all three roles.

Pattern

Example grain

Typical use

Transaction

One inventory movement

Explain which movements changed stock

Periodic snapshot

One product-location-day

Compare end-of-day stock over time

Accumulating snapshot

One fulfillment line

Measure time between order, pick and delivery milestones

A movement table can be summed to derive changes, but reconstructing every historical balance may be expensive and requires a trustworthy starting balance. A daily snapshot makes trend reporting simpler but loses intraday movement detail. An accumulating snapshot makes process timing convenient, while an event log is still useful when stages repeat or a case is reopened. Choose the authoritative source and reconciliation rules for each representation.

43. Why can an inventory balance be summed across products but not across dates?

An inventory balance is a state measured at a point in time. At a common timestamp and compatible unit, balances across distinct locations or products can be additive. Adding successive daily balances counts the same remaining stock repeatedly and therefore does not represent inventory available at month-end.

If a location holds 10 units on Monday and 12 on Tuesday, the two-day closing balance is 12, not 22. The average is 11 only if the two daily observations should receive equal weight. If the intended measure is average inventory over irregular observation intervals, a time-weighted calculation may be needed.

Document allowable aggregation by dimension rather than labeling the column simply “numeric.” Product totals also require compatible units: adding kilograms and individual items creates a number with no coherent unit. A semantic measure should enforce the intended time selection and unit conversion.

44. How should a model aggregate percentages and average prices?

Store the additive components and calculate the ratio after aggregation. A percentage or average is generally non-additive, and averaging precomputed row ratios gives each row equal weight even when their denominators differ.

For example, one campaign has 1 conversion from 10 visits and another has 90 from 100 visits. Their rates are 10% and 90%; the combined conversion rate is 91/110, about 82.73%, rather than 50%. The same principle applies to average selling price: total revenue divided by total units.

SELECT SUM(conversions) * 1.0 / NULLIF(SUM(visits), 0) AS conversion_rate
FROM

SELECT SUM(conversions) * 1.0 / NULLIF(SUM(visits), 0) AS conversion_rate
FROM

SELECT SUM(conversions) * 1.0 / NULLIF(SUM(visits), 0) AS conversion_rate
FROM

The expression returns NULL when there are no visits, leaving the presentation layer to explain “not applicable.” Decide how invalid records, refunds and negative quantities affect the metric; guarding division by zero does not establish the business definition.

45. How would you keep order-level charges from inflating line-level revenue reports?

Keep an order-level charge at order grain, or allocate it to lines under an explicit rule. Repeating a 12-dollar shipping charge on each of three lines and summing it produces 36 dollars. The problem is mixed grain, not a missing DISTINCT keyword.

If users need shipping by product, allocation can be proportional to line value, weight or another agreed driver. With line values of 20, 30 and 50, a 10-dollar charge allocates as 2, 3 and 5 under value weighting. Preserve the original charge and allocation method so a changed rule can be explained and reproduced.

Handle zero-valued orders and rounding explicitly. Rounding every line independently can lose a cent, so assign the residual deterministically and test that allocated charges sum to the source charge. Use separate measures for source totals and allocated totals to avoid double counting both.

46. What makes a dimension conformed across different fact tables?

A conformed dimension gives facts compatible business identities, attribute meanings and hierarchies so they can be analyzed together. Matching column names is insufficient: “customer” might mean bill-to account in one process and end user in another.

For example, sales and support facts can share a customer dimension if both resolve the same customer identity and agree on how mergers, regional classifications and historical changes are represented. Aggregate each fact to the intended customer-period grain before combining results; joining raw sales rows directly to raw support rows can multiply both.

Conformance can involve an agreed subset rather than identical copies of every attribute. Record which hierarchy levels and versions are compatible, and expose unresolved identities visibly instead of forcing false matches to make a cross-process dashboard balance.

47. How do role-playing dimensions affect order-date and shipment-date analysis?

A role-playing dimension is reused in different semantic roles. An order fact might contain order_date_key and ship_date_key, both referring to the same calendar but describing different events. The relationship used determines the meaning of a date filter.

SELECT od.calendar_month AS order_month,
       sd.calendar_month AS ship_month,
       COUNT(*) AS line_count
FROM order_line AS f
JOIN dim_date AS od ON od.date_key = f.order_date_key
JOIN dim_date AS sd ON sd.date_key = f.ship_date_key
GROUP BY od.calendar_month,

SELECT od.calendar_month AS order_month,
       sd.calendar_month AS ship_month,
       COUNT(*) AS line_count
FROM order_line AS f
JOIN dim_date AS od ON od.date_key = f.order_date_key
JOIN dim_date AS sd ON sd.date_key = f.ship_date_key
GROUP BY od.calendar_month,

SELECT od.calendar_month AS order_month,
       sd.calendar_month AS ship_month,
       COUNT(*) AS line_count
FROM order_line AS f
JOIN dim_date AS od ON od.date_key = f.order_date_key
JOIN dim_date AS sd ON sd.date_key = f.ship_date_key
GROUP BY od.calendar_month,

This groups lines by both roles instead of silently applying one date to all measures. The example assumes both dates are present or mapped to explicit dimension members; otherwise an inner join removes unshipped lines. BI tools differ in how active relationships work, so name fields such as “Order Month” and “Ship Month” and validate the filter behavior users actually see.

48. When should an invoice number remain in a fact table as a degenerate dimension?

Keep an invoice number in the fact when it supplies a useful grouping or drill-through identifier but has no independent descriptive attributes requiring a dimension. This is a degenerate dimension: it behaves as a business label even though it is stored alongside measures.

An invoice-line fact can retain invoice number and line number to reconstruct all lines of an invoice. Invoice number alone is not unique at line grain, and it may also need a source-system or legal-entity qualifier to avoid collisions.

If an invoice gains a substantial independent lifecycle, owner or set of shared attributes, a separate entity or dimension may become justified. Do not create a one-column dimension automatically, but do not use the degenerate label as an excuse to mix unrelated header attributes into every line without an ownership and history policy.

49. What problem does a junk dimension solve, and when does it become unsuitable?

A junk dimension combines several low-cardinality descriptive flags into one keyed row, reducing scattered tiny dimensions and keeping flags available for slicing. For example, order channel, gift-wrap choice and expedited-delivery flag can describe a small number of observed combinations.

With 3 channels and two binary flags, the complete combination space has 12 rows. A fact references the combination rather than three separate dimension keys. This is convenient only while the combinations remain manageable and the attributes share a sensible context.

Adding a unique order reference would make the combination count grow toward the fact count and defeat the design. Decide whether to prepopulate all permitted combinations or create observed combinations, enforce uniqueness of the flag tuple, and preserve stable keys during reloads.

50. How can a factless fact table reveal events that did not occur?

A factless fact records an occurrence or eligible relationship using keys rather than numeric measures. To find missing events, compare the expected population with the observed population at the same grain.

For training attendance, one table can record scheduled employee-session pairs and another attended pairs. An anti-join finds expected attendees with no matching attendance, provided canceled sessions and excused employees are treated according to the business rule.

SELECT e.employee_id, e.session_id
FROM expected_attendance AS e
WHERE NOT EXISTS (
    SELECT 1
    FROM attendance AS a
    WHERE a.employee_id = e.employee_id
      AND a.session_id = e.session_id
)

SELECT e.employee_id, e.session_id
FROM expected_attendance AS e
WHERE NOT EXISTS (
    SELECT 1
    FROM attendance AS a
    WHERE a.employee_id = e.employee_id
      AND a.session_id = e.session_id
)

SELECT e.employee_id, e.session_id
FROM expected_attendance AS e
WHERE NOT EXISTS (
    SELECT 1
    FROM attendance AS a
    WHERE a.employee_id = e.employee_id
      AND a.session_id = e.session_id
)

Enforce uniqueness of each pair or explicitly model repeated scans as separate events. A missing row means “not observed”; call it an absence only when the attendance feed is complete and the expected population is trustworthy.

51. How would you model sales credited to multiple representatives?

Use a bridge between the sale and its representatives, with an allocation weight when the measure must remain additive. The bridge expresses the many-to-many relationship; the weights express the attribution rule, which is a separate business decision.

If a 100-dollar sale is credited 60% to one representative and 40% to another, allocated revenue is 60 and 40. Joining both representatives and summing the unweighted sale amount produces 200. Full-credit reporting may intentionally give each representative 100, but its grand total must not be presented as company revenue.

Validate that weights for each sale and allocation version sum to one when that is the contract, and define treatment of unassigned representatives. Historical attribution changes require an effective-date or version policy; overwriting the bridge can otherwise restate previous performance reports unexpectedly.

52. How do you choose between Type 1 and Type 2 handling for an attribute?

Choose based on whether analysis must retain the previous value. Type 1 replaces a value; Type 2 creates a new dimension version. The choice belongs to the attribute and reporting requirement, not automatically to every column in a customer table.

A spelling correction may be Type 1 because the old spelling was erroneous. A customer moving between sales territories may need Type 2 so a past sale remains associated with the territory responsible at the time. One dimension can therefore mix correction fields with historically meaningful attributes.

Ask whether a report should show “as it was then” or “using today’s classification.” If both are required, expose separate historical and current paths with clear labels. Merely retaining old rows does not guarantee correct history: facts must point to the appropriate version.

53. How would you resolve a fact to the correct Type 2 dimension version?

Look up the business identity using the fact’s relevant event time within a nonoverlapping validity interval. Use the resolved version’s surrogate key in the fact. A load-time lookup to the current dimension row misclassifies late facts.

SELECT f.sale_id, d.customer_key
FROM staged_sale AS f
JOIN dim_customer AS d
  ON d.customer_id = f.customer_id
 AND f.sold_at >= d.valid_from
 AND (f.sold_at < d.valid_to OR d.valid_to IS NULL)

SELECT f.sale_id, d.customer_key
FROM staged_sale AS f
JOIN dim_customer AS d
  ON d.customer_id = f.customer_id
 AND f.sold_at >= d.valid_from
 AND (f.sold_at < d.valid_to OR d.valid_to IS NULL)

SELECT f.sale_id, d.customer_key
FROM staged_sale AS f
JOIN dim_customer AS d
  ON d.customer_id = f.customer_id
 AND f.sold_at >= d.valid_from
 AND (f.sold_at < d.valid_to OR d.valid_to IS NULL)

Here intervals are half-open: a change at noon belongs to the new version at exactly noon. The match must be exactly one row per sale; zero matches indicate missing history or identity, while two matches indicate overlapping versions. Normalize timestamp interpretation before the lookup, and define whether business-effective time or source-recorded time governs the report.

54. What checks would you apply to a Type 2 customer dimension?

Check the version structure as well as ordinary key uniqueness. Each surrogate key must be unique, each validity interval must be valid, and versions for a business identity must not overlap. If the design promises one current row, test that rule separately.

SELECT customer_id
FROM dim_customer
GROUP BY customer_id
HAVING SUM(CASE WHEN valid_to IS NULL THEN 1 ELSE 0 END) <> 1

SELECT customer_id
FROM dim_customer
GROUP BY customer_id
HAVING SUM(CASE WHEN valid_to IS NULL THEN 1 ELSE 0 END) <> 1

SELECT customer_id
FROM dim_customer
GROUP BY customer_id
HAVING SUM(CASE WHEN valid_to IS NULL THEN 1 ELSE 0 END) <> 1

This detects identities with zero or multiple open-ended versions under that convention; it does not detect historical overlaps. Compare each closed interval with the next start, and decide whether gaps are allowed. A customer who ceased to exist may legitimately have no current row, so scope the check to the identity lifecycle promised by the model.

Also test facts around exact change boundaries. Valid intervals can still yield wrong reports if the loader assigns the current surrogate key to every old sale.

55. How do you handle a fact that arrives before its dimension attributes?

Preserve the fact and its unresolved identity using a defined late-dimension strategy rather than dropping revenue or inventing descriptive values. Common approaches are an inferred dimension member for the known business key or a designated unknown member plus a repair queue.

An inferred customer row might contain customer ID C17 and placeholder attributes, allowing incoming sales to reference a stable surrogate key. When C17’s attributes arrive, enrich that row according to the historical policy; if the data describes a genuine later change, a new version may be needed instead.

Keep “unknown identity,” “known identity with missing attributes,” and “not applicable” distinguishable when users need different remediation. Track unresolved age and count. Otherwise a technically valid foreign key can conceal a permanently incomplete dimension.

56. What changes when a late fact belongs to an already closed reporting period?

The fact should retain its original business event time, while the model also records when it became available. Whether it changes the published period is a reporting policy: some reports restate history, while others retain a closed result and expose an adjustment.

For a sale that occurred in March but arrived in April, resolve the customer’s March dimension version rather than April’s current version. Then refresh affected aggregates and reconcile the correction under the agreed period policy. Assigning April as the event date only to avoid reprocessing destroys the meaning of monthly sales.

Keep source event identity and ingestion metadata so repeated delivery does not create duplicate sales. Users should be able to distinguish a genuine change in business activity from a historical total changing because delayed data became available.

57. How should a dimension represent unknown, not applicable and not yet available?

Represent these states explicitly when they have different meanings, usually with designated members or a reason field. Unknown means the value cannot currently be determined; not applicable means the attribute does not belong to the event; not yet available describes an expected future value.

An order not yet shipped should not look like an order with a corrupted shipment date. A digital download may never require physical delivery, while a physical order may simply be awaiting dispatch. Distinguishing these cases improves both reporting and data-quality triage.

Do not reuse a plausible real date such as January 1 as an unexplained placeholder. If a date dimension uses special keys, provide clear labels and keep real-date calculations from treating them as actual timestamps. Document the mapping so every loader applies the same semantics.

58. When is a snowflaked dimension preferable to a flattened star dimension?

Snowflake a dimension when separately managed relationships or reusable hierarchical entities justify the additional joins. Flattening a product’s category and department into a product dimension is often simpler for analytics, but a shared category entity can be useful when its own identity and management matter.

Choice

Potential benefit

Modeling cost

Flattened dimension

Simple filtering and fewer joins

Repeated hierarchy labels and coordinated updates

Snowflaked dimension

Explicit reusable hierarchy entities

More relationship paths and join complexity

The choice must preserve historical semantics. If a product changes category, pointing all historical product versions at one mutable category relationship can restate history even though the diagram is normalized. Benchmark the actual engine and BI tool; fewer joins are not automatically faster in every platform, and reduced duplication is not automatically easier for users.

59. How can a relation satisfy third normal form but violate Boyce-Codd normal form?

BCNF requires every determinant of a nontrivial functional dependency to be a superkey. Third normal form also permits a dependency whose determined attribute is prime, meaning it belongs to a candidate key. That exception means a relation can satisfy 3NF while retaining a dependency that BCNF rejects.

Consider Assignment(student, course, instructor). Suppose each student has one instructor for a course, and each instructor teaches exactly one course, although a course can have several instructors. Then (student, course) → instructor and instructor → course. The candidate keys are (student, course) and (student, instructor). Course is prime, so the second dependency is permitted by 3NF; instructor alone is not a superkey, so it violates BCNF.

A lossless decomposition into InstructorCourse(instructor, course) and StudentInstructor(student, instructor) removes that repetition. However, enforcing one instructor per student-course pair now requires checking across the two relations. BCNF can trade local dependency enforcement for less redundancy. I would make that trade-off explicit rather than assume that a higher normal form is automatically easier to enforce.

60. How would you model a calendar with fiscal periods and multiple date roles?

Create a governed calendar dimension that maps each date to the required fiscal attributes, while keeping the fact’s date roles explicit. Fiscal week or quarter is a business classification, not something every query should derive independently.

For a 4-4-5 retail calendar, maintain the approved year boundaries and any extra week explicitly. Keep calendar year and fiscal year as distinct fields. An order-date key and a return-date key can both reference the calendar without implying that their periods mean the same business event.

If different legal entities use different fiscal calendars, include calendar identity in the mapping or expose separate governed calendar relationships. A date alone no longer identifies one fiscal period. Test boundary days around year-end and explain how historical calendar corrections affect existing reports.

61. How do you model a recurring event without losing its original time-zone meaning?

Separate the recurring local schedule from concrete event instants. A class scheduled for 09:00 in a named time zone is not equivalent to a permanently fixed UTC offset, because daylight-saving rules can change the corresponding instant.

Store the local recurrence rule and time-zone identifier for the schedule, then store the resolved instant for each actual occurrence. For historical analysis, retain which occurrence was canceled or moved rather than inferring all history from the current recurrence rule.

Define behavior for nonexistent and ambiguous local times, such as clock-change transitions. A database timestamp type alone does not answer whether a missed local time should be skipped or shifted. This modeling decision belongs with the scheduling rules and should be validated with boundary examples.

62. How would you model returns without corrupting original sales history?

Model the return as its own business event and link it to the original sale line when that relationship is known. The original sale and subsequent return occur at different times and may have different locations, reasons and quantities.

A return of one unit from a three-unit sale should not erase the fact that three units were originally sold. Define whether a net-sales measure includes signed return amounts, and ensure both facts use compatible currency and valuation rules. Partial returns and returns without receipts need explicit handling.

Keep gross sales, returns and net sales distinguishable. Updating the original line’s quantity from three to two may suit a current operational state, but it prevents a warehouse from explaining the initial transaction and the later reversal.

63. When should an aggregate table be added to an analytical model?

Add an aggregate when measured query cost justifies a maintained summary and its grain supports the required analysis. Keep the atomic source authoritative, and record how the summary is rebuilt or incrementally corrected.

A product-day summary can accelerate daily revenue by product. It cannot answer order-level basket questions or distinguish individual customers if those identities were removed. Include only dimensions that survive the aggregation and prevent users from joining the summary to detail in a way that repeats its measures.

Test aggregate totals against atomic facts for representative periods, including late corrections and deletions. A fast table that silently omits revised historical days is a correctness regression. Decide whether distinct counts can be recomputed exactly or require a separately documented approximation.

64. How can a materialized view change performance without changing the logical model?

A materialized view stores a derived result, so it can reduce repeated computation while the logical business relationships remain unchanged. Its refresh behavior becomes part of the data contract because readers may see an older result than the base tables.

For example, a daily customer-spend projection can precompute a costly join and aggregation. Before using it, establish how often it refreshes, whether refresh is incremental, and how users learn its freshness. A normal view usually stores the query definition instead, so it does not inherently avoid recomputing the result.

Choose a refresh strategy supported by the database and test concurrent reads, failures and late updates. Do not promise transactional freshness merely because the derived table is inside the same database; refresh semantics differ by engine and configuration.

65. How should index design relate to logical keys and access patterns?

Use logical keys to express identity and constraints, and choose indexes to support the actual predicates, joins and ordering. A surrogate primary key may be useful for references but may not accelerate a frequent customer-and-date query.

For an events table commonly filtered by customer and a time range, an index beginning with customer ID and then event time may be appropriate. Its value depends on selectivity, query shape and the engine’s optimizer; adding every possible index increases storage and write maintenance.

Keep an index’s performance role separate from a uniqueness contract. Removing a nonunique index should not change valid business states, whereas removing a unique constraint can. Measure with representative data and plans, and verify the database’s rules for automatically created indexes rather than assuming every foreign key is indexed.

66. How can denormalization be introduced without creating competing sources of truth?

Define the duplicated value as a maintained projection of an authoritative source, with an owner, update path and tolerated lag. Denormalization is a deliberate performance or usability trade-off, not permission for independent writers to disagree.

An order-search document may copy the customer’s current display name to avoid an extra lookup. If the order must retain the name as shown on the original invoice, however, that is a historical snapshot with different semantics rather than merely a stale cache. Name these fields accordingly.

Test reconciliation and rebuildability. A projection should either be reproducible from retained sources or have a documented reason why it is authoritative itself. When source changes fail to propagate, users need a detectable inconsistency and recovery path, not silent divergence.

67. When should related data be embedded in a document rather than referenced?

Embed related values when they are read and changed together, have a bounded size, and share a lifecycle. Reference them when they grow independently, are reused widely, or require separate access and ownership. A document model still needs explicit relationships and consistency rules.

An order can embed the delivery address captured at checkout because that snapshot belongs to the order. Embedding every customer’s lifetime of orders inside one customer document creates unbounded growth and makes unrelated order updates contend on the same aggregate.

Check the deployed database’s document-size and update constraints. A reference also adds application or query work and may require multiple operations. Choose from observed access patterns and correctness requirements rather than treating “no joins” as a universal objective.

68. What makes an embedded array dangerous as a model grows?

An array becomes risky when its size or update rate has no practical bound. Large arrays increase transfer and document-update costs, can reach database limits, and make pagination or selective access awkward.

For example, keeping the most recent five status changes with an order is bounded. Keeping every audit event ever generated for a busy account in one array is not. A separate event collection keyed by account and time supports independent growth; a small embedded recent-events projection can remain useful.

Estimate cardinality and document size using realistic retention, not only today’s sample data. Explain how old entries are archived or removed and how concurrent writers behave. A schema that fits a demo may fail years later without any change in its declared field types.

69. How would you model product attributes that differ by product category?

Separate shared product identity and attributes from category-specific details, then choose typed subtype tables, validated documents or a controlled attribute-value design according to the queries and change rate. A single wide table full of unexplained NULLs is not the only option.

For laptops, memory size should be numeric with a unit; for shirts, size may use a defined size system. Storing both as an unrestricted string called “value” makes validation and comparison difficult. A document can preserve typed category-specific fields, while relational subtype tables can enforce category-specific constraints.

If an attribute-value approach is necessary, model allowed attributes, types, units and cardinalities explicitly. It trades schema flexibility for more complex validation and querying. Keep frequently filtered business attributes accessible and typed rather than burying every important field in an opaque extension payload.

70. How would you model a hierarchy with variable depth?

Choose a representation based on required traversals and update behavior. An adjacency list stores each node’s parent and is simple to maintain; a closure table stores ancestor-descendant paths and makes descendant or ancestor lookup more direct.

Representation

Useful property

Cost to consider

Adjacency list

Compact parent relationship

Recursive traversal and cycle checks

Closure table

Direct ancestor-descendant lookup

More stored paths and maintenance on moves

Materialized path

Convenient subtree prefix representation

Rewriting descendant paths when a node moves

For an organizational tree, decide whether each employee has one manager or multiple reporting relationships. A tree representation is wrong if the domain is actually a graph. Also define whether reports use the current hierarchy or the hierarchy effective at the event date; moving a node should not silently rewrite historical responsibility.

71. How do you model a relationship that carries its own business attributes?

Promote the relationship to an associative entity when it has properties such as dates, roles, quantities or status. Those attributes describe the association, not either endpoint independently.

For employees assigned to projects, allocation percentage and assignment dates belong to an assignment record. A unique employee-project pair works only if the model permits one assignment for the entire lifecycle. Rejoining the same project later may require a version or assignment identifier.

Validate rules at the relationship’s grain. Preventing overlapping assignments may require more than a simple pair key, while limiting an employee’s total allocation across projects requires a cross-row rule. Do not place a project-specific role on the employee table, where it would incorrectly apply to every project.

72. How would you enforce that each active subscription has one current plan?

First define whether plan history is stored as intervals or whether a separate current-plan reference is authoritative. Then enforce uniqueness of the current relationship and validate that its referenced plan exists. A row-level flag without a constraint permits multiple “current” plans.

In PostgreSQL, a partial unique index can enforce at most one open-ended plan interval per subscription:

CREATE UNIQUE INDEX one_open_plan
ON subscription_plan (subscription_id)
WHERE valid_to IS NULL

CREATE UNIQUE INDEX one_open_plan
ON subscription_plan (subscription_id)
WHERE valid_to IS NULL

CREATE UNIQUE INDEX one_open_plan
ON subscription_plan (subscription_id)
WHERE valid_to IS NULL

This does not enforce at least one current plan, prevent historical overlaps, or prove that the open interval has already begun. Those rules need additional constraints or transaction logic matched to the domain. Apply the plan switch atomically so intermediate states are not mistaken for a valid completed change.

73. How would you model a rule that total allocations cannot exceed a budget?

Treat it as a cross-row invariant over a defined budget scope, not merely a validation on one allocation row. Checking that each allocation is positive cannot stop several individually valid allocations from exceeding the total.

For a budget of 100, two concurrent requests for 60 may each see no existing spend and both pass an application-side precheck. A safe implementation must serialize the relevant decision, use an appropriate isolation strategy with retries, or atomically reserve against a maintained balance under a documented contract.

Model the budget owner, currency, period and allocation lifecycle so the sum is unambiguous. Decide whether pending allocations count and how cancellations release capacity. The conceptual rule should remain stable even if the physical enforcement differs between databases.

74. What model-level tests can detect a join that silently drops facts?

Test referential coverage and reconcile measures before and after the join. An inner join can silently discard facts with missing dimension keys, while a nonunique dimension join can multiply them.

SELECT f.customer_key, COUNT(*) AS orphan_count
FROM sales_fact AS f
LEFT JOIN dim_customer AS d
  ON d.customer_key = f.customer_key
WHERE d.customer_key IS NULL
GROUP BY

SELECT f.customer_key, COUNT(*) AS orphan_count
FROM sales_fact AS f
LEFT JOIN dim_customer AS d
  ON d.customer_key = f.customer_key
WHERE d.customer_key IS NULL
GROUP BY

SELECT f.customer_key, COUNT(*) AS orphan_count
FROM sales_fact AS f
LEFT JOIN dim_customer AS d
  ON d.customer_key = f.customer_key
WHERE d.customer_key IS NULL
GROUP BY

If the dimension key is guaranteed unique, this identifies unmatched facts. Compare row counts and relevant additive totals as well; a zero orphan count alone does not prove that a nonunique join avoided duplication. Include unknown-member handling in the expectation rather than treating every placeholder as an ordinary successful match.

75. How can you test that a table still satisfies its declared grain?

Translate the grain into a business-key uniqueness test and pair it with domain-specific reconciliation. A surrogate key can remain unique while the same source event is loaded twice.

SELECT source_system, order_id, line_number, COUNT(*) AS copies
FROM order_line_fact
GROUP BY source_system, order_id, line_number
HAVING COUNT(*) > 1

SELECT source_system, order_id, line_number, COUNT(*) AS copies
FROM order_line_fact
GROUP BY source_system, order_id, line_number
HAVING COUNT(*) > 1

SELECT source_system, order_id, line_number, COUNT(*) AS copies
FROM order_line_fact
GROUP BY source_system, order_id, line_number
HAVING COUNT(*) > 1

This test fits a current order-line representation. If the table intentionally stores revisions, include the revision identity in the grain and test current-version rules separately. Also test whether required grain fields are NULL, because grouping can expose duplicates without establishing valid identity.

Reconcile row counts and amounts to a source partition that is complete enough to compare. A uniqueness check can pass even when half the rows are missing, so it is only one part of validating the model’s contract.

76. How would you detect a many-to-many join before it inflates a dashboard?

Profile uniqueness on both join sides at the actual join keys, then use a small example to trace the resulting row pairs. A diagram marked one-to-many is not evidence that the stored data obeys that cardinality.

If a customer has two address rows and three purchase rows, joining only on customer ID creates six pairs. Choosing one current address, joining to the event-time address version, or explicitly allocating across addresses produces different meanings. The correct repair depends on the question, not on which query gives the expected total.

Add assertions for the chosen cardinality and measure totals before and after the relationship. Avoid using DISTINCT as a generic repair: it can remove legitimate repeated measures or fail to remove duplicated rows that differ in an address field.

77. How should a model distinguish soft deletion from historical inactivity?

Soft deletion is an operational lifecycle state, while historical inactivity describes when an entity stopped participating in a business process. They may coincide, but they should not be conflated automatically.

A customer account closed today may still be required to explain last year’s transactions. Filtering every join to is_deleted = false can erase those historical sales from reports. Instead, preserve the required identity and history while defining which views show currently available entities.

Also decide how deletion propagates to projections, cached dimensions and audit history. A model should record lifecycle meaning and retention behavior explicitly; a boolean flag alone cannot express all obligations or distinguish closure, merging and erroneous creation.

78. How do you model a measure that is valid only for some event types?

Keep applicability explicit through separate fact types, a discriminating event type, or a documented nullable measure. Do not fill an inapplicable value with zero if users would interpret zero as a real observation.

A delivery event may have a distance measurement, while a cancellation event has no traveled distance. A combined event table can distinguish these cases with event type and applicability rules, but separate facts may be clearer when the grains and dimensions differ substantially.

Test conditional rules such as “delivery events require nonnegative distance” and decide how unknown measurement differs from not applicable. Aggregation must follow the intended denominator: averaging distance across all events with fabricated zeros understates the average delivery distance.

79. How would you model multiple currencies in a sales fact?

Retain the amount in its original currency and make converted amounts explicit about target currency, rate type and effective date. Numeric amounts in different currencies cannot be summed meaningfully without a conversion policy.

For a sale of 100 EUR, storing 110 USD also requires the applicable exchange-rate convention and rate date if the result must be reproducible. Booking-date, settlement-date and period-average conversions can all produce valid but different measures. Name them so users cannot confuse them.

Use suitable exact numeric precision and define rounding at the required accounting or reporting stage. If a converted aggregate is rebuilt after rates change, preserve the original policy or version the result. A currency code column is necessary context, but it does not by itself make cross-currency totals correct.

80. When does a three-way relationship need a ternary table instead of separate pairwise tables?

Use a ternary relationship when a fact depends on all three participants together and cannot be reconstructed from independent pairs without inventing combinations. The key and constraints should describe the full business statement, such as which supplier supplies a particular component to a particular project.

Supplier

Component

Project

S1

C1

P1

S1

C2

P2

S2

C1

P2

In this example, replacing the three-column table with supplier-component, supplier-project and component-project pairs loses information. Joining those pairwise projections admits S1-C1-P2: S1 supplies C1 somewhere, S1 serves P2 somehow, and C1 is used by P2 through another supplier. Yet the original table never recorded S1 supplying C1 to P2. Every pair is valid while the triple is false.

Keep the triple as the relationship grain, with quantity or agreed price attached there when it depends on that exact combination. The primary key might be all three identities, or include an agreement version if repeated contracts are meaningful. Pairwise tables can still describe separate eligibility rules, but they do not replace the actual assignment unless business dependencies prove that decomposition is lossless.

Data Modeling Interview Questions and Answers for Experienced

Experienced answers examine failure modes across system boundaries: ambiguous identity, delayed events, unsafe migrations, skewed access patterns and conflicting metric definitions. A strong solution identifies its guarantees, operational costs and unresolved business decisions instead of offering one universal schema.

81. A report doubles revenue after a new join. How would you diagnose the model?

I would check the grain and cardinality on both sides before changing the aggregation. A sales line joined to two matching customer-history rows appears twice; summing its amount then doubles revenue even though the sales fact itself is correct. The immediate diagnostic is to compare row counts and amounts before and after the join, then identify fact keys with more than one match.

SELECT f.sale_line_id, COUNT(*) AS dimension_matches
FROM sale_line AS f
JOIN customer_history AS d
  ON d.customer_id = f.customer_id
 AND f.sold_at >= d.valid_from
 AND (f.sold_at < d.valid_to OR d.valid_to IS NULL)
GROUP BY f.sale_line_id
HAVING COUNT(*) > 1

SELECT f.sale_line_id, COUNT(*) AS dimension_matches
FROM sale_line AS f
JOIN customer_history AS d
  ON d.customer_id = f.customer_id
 AND f.sold_at >= d.valid_from
 AND (f.sold_at < d.valid_to OR d.valid_to IS NULL)
GROUP BY f.sale_line_id
HAVING COUNT(*) > 1

SELECT f.sale_line_id, COUNT(*) AS dimension_matches
FROM sale_line AS f
JOIN customer_history AS d
  ON d.customer_id = f.customer_id
 AND f.sold_at >= d.valid_from
 AND (f.sold_at < d.valid_to OR d.valid_to IS NULL)
GROUP BY f.sale_line_id
HAVING COUNT(*) > 1

If sale line 1 falls inside two customer-history intervals while line 2 matches one, the query returns only (1, 2). That is evidence of duplicate dimension matches, not proof that the source sale was duplicated. Also check unmatched facts separately: an inner join can hide revenue by dropping rows as well as inflate it through fan-out. The query treats NULL valid_to as an open-ended interval and uses an inclusive start with an exclusive end.

For an as-of relationship, overlapping dimension intervals are a model-integrity defect; repair them and prevent recurrence. For a legitimate multivalued relationship, choose allocation or distinct-entity reporting explicitly. SUM(DISTINCT amount) is not a safe fix: two different sales can have the same amount, so it can hide duplication while discarding valid revenue.

82. How would you combine facts recorded at different grains without multiplying measures?

I would aggregate each fact to the common comparison grain before joining. Orders and support tickets are independent one-to-many relationships with a customer. Joining their raw rows creates every order-ticket combination, so neither order revenue nor ticket counts remain safe to sum.

WITH revenue AS (
  SELECT customer_id, SUM(amount) AS revenue
  FROM orders GROUP BY customer_id
), tickets AS (
  SELECT customer_id, COUNT(*) AS ticket_count
  FROM support_ticket GROUP BY customer_id
)
SELECT c.customer_id, COALESCE(r.revenue, 0) AS revenue,
       COALESCE(t.ticket_count, 0) AS ticket_count
FROM customer AS c
LEFT JOIN revenue AS r ON r.customer_id = c.customer_id
LEFT JOIN tickets AS t ON

WITH revenue AS (
  SELECT customer_id, SUM(amount) AS revenue
  FROM orders GROUP BY customer_id
), tickets AS (
  SELECT customer_id, COUNT(*) AS ticket_count
  FROM support_ticket GROUP BY customer_id
)
SELECT c.customer_id, COALESCE(r.revenue, 0) AS revenue,
       COALESCE(t.ticket_count, 0) AS ticket_count
FROM customer AS c
LEFT JOIN revenue AS r ON r.customer_id = c.customer_id
LEFT JOIN tickets AS t ON

WITH revenue AS (
  SELECT customer_id, SUM(amount) AS revenue
  FROM orders GROUP BY customer_id
), tickets AS (
  SELECT customer_id, COUNT(*) AS ticket_count
  FROM support_ticket GROUP BY customer_id
)
SELECT c.customer_id, COALESCE(r.revenue, 0) AS revenue,
       COALESCE(t.ticket_count, 0) AS ticket_count
FROM customer AS c
LEFT JOIN revenue AS r ON r.customer_id = c.customer_id
LEFT JOIN tickets AS t ON

With two orders worth 20 and 30 and three tickets for customer 1, the result is revenue 50 and ticket count 3. A direct raw join would produce six rows and revenue 150. A customer with neither orders nor tickets remains present with two zeros under this report’s explicit policy.

This example defines the output as one row per customer and intentionally treats missing aggregates as zero. A time-based report must align date boundaries and distinguish order date from ticket date. If the business asks which ticket caused an order, a shared customer is not evidence of that relationship; the model needs an explicit attribution rule.

83. When does a system need both valid time and recorded time?

It needs both when users must distinguish when a fact applied in the business from when the system knew it. A contract rate may be effective on January 1 but entered on January 10, then corrected on February 3. One timeline cannot answer both “What rate actually applied on January 5?” and “What rate did the January 20 report use?”

Time axis

Meaning

Example question

Valid time

Business-effective interval

Which rate applied to this shipment?

Recorded time

Interval during which this version was recorded as current knowledge

What did we believe when the invoice was issued?

A bitemporal model records both intervals and makes the query contract explicit. Corrections preserve earlier knowledge instead of overwriting it. This adds storage and query complexity, so I would require a concrete audit or retrospective-analysis need. SQL Server system-versioned temporal tables track system history; business-effective time still needs separate modeling when those dates differ.

84. How would you apply a retroactive correction without silently rewriting historical reports?

I would preserve the original recorded version, store the correction with its effective period and recorded timestamp, and define which reports are restated. A corrected customer region effective before a sale may change a current reconstruction of regional revenue, but a previously issued report may need to retain the classification known at issuance.

For example, retain an immutable report-run identifier linked to input versions, transformation version and cutoff time. Publish a restated run separately and explain the delta. In the warehouse, close or supersede the affected knowledge version and insert corrected intervals under a transaction or equivalent atomic publication boundary. Merely updating the current region loses the evidence needed to explain why yesterday’s totals differ.

85. How do event time and processing time affect an analytical model?

Event time says when an activity happened; processing time says when a pipeline observed or handled it. They serve different purposes and should not share an ambiguous timestamp column. A purchase made at 23:58 and received at 00:03 belongs to the earlier event-time day, while its ingestion latency spans the day boundary.

I would retain the source event timestamp, ingestion timestamp, time-zone interpretation and stable event identity. Daily metrics need an explicit lateness policy: an open period can be revised, while a closed period may receive a documented adjustment. Arrival order alone is unreliable when devices buffer events or retries overtake earlier messages. If event time is untrusted, record that quality limitation instead of presenting processing time as the original occurrence time.

86. How would you model deduplication when messages can be delivered more than once?

I would separate message-delivery identity from the identity of the business event. Retries of one payment should map to the same event key, while two legitimate payments with equal amounts must remain distinct. Prefer a producer-assigned event identifier with a documented uniqueness scope, such as (source_system, event_id).

A consumer can atomically insert that key into a processed-event table and apply the state change in the same transaction. A uniqueness conflict then indicates that this event has already been applied. If the producer can resend the same key with a changed payload, compare a payload fingerprint and quarantine the conflict rather than treating it as a harmless retry. Retention of deduplication keys must cover the possible replay window; deleting them early can make old events count again.

87. What changes when events, rather than current rows, are the system of record?

With event sourcing, current state is a projection of an ordered history of domain changes. The model must describe durable event meanings, per-aggregate ordering, concurrency checks and how old events remain interpretable. Storing an append-only audit log beside a mutable table does not automatically make that log the authoritative event model.

For an account, FundsDeposited and FundsWithdrawn explain state changes more clearly than an unexplained replacement balance. A projection can reconstruct a balance, but replay must not resend notifications or charge a payment provider. Versioned handlers, deterministic reconstruction and projection checkpoints add operational cost. I would use this model when historical reconstruction and domain traceability justify that cost, not simply because the application publishes messages.

88. How would you model deletes in a change-data-capture pipeline?

I would distinguish removal of source state from a transport tombstone and from a business cancellation. A source delete may need to remove a warehouse current-state row, close a history interval, or preserve a permitted analytical record. The correct action depends on the target’s declared semantics and retention rules.

For Debezium’s PostgreSQL connector, delete events and optional tombstones are separate messages; a tombstone supports log compaction and is not a complete business record. The consumer needs the key, operation and source ordering information to apply changes correctly. Test a delete followed by a delayed old update, a primary-key change and a snapshot/replay boundary. Otherwise an old message can resurrect a record that the source has already removed.

89. How would you make a backfill safe while live updates continue?

I would define a consistent source boundary and prevent older backfill data from overwriting newer changes. A backfill that copies rows for hours while CDC updates the same target can otherwise replace a current address with the snapshot’s old address. The design needs source ordering, conflict resolution and a clear cutover point.

One approach loads a consistent snapshot into a separate target, applies changes from the snapshot boundary forward, reconciles keys and measures, then switches readers. Another applies version-guarded updates to a shared target, provided source versions are comparable and deletes are represented. Wall-clock arrival time is rarely an adequate ordering guarantee. Record progress by stable ranges or checkpoints so retries are repeatable and the migration can be audited.

90. How would you rename a widely used field without breaking consumers?

I would use an expand-and-contract migration with a compatibility period. First introduce the new field or interface while retaining the old contract, update producers and consumers, verify usage, and remove the obsolete field only after the supported clients have migrated. A physical rename can be easy while its downstream effects are extensive.

For example, replacing customer_name with legal_name may also change meaning, not just spelling. If the old field included display names, a simple alias would conceal a semantic incompatibility. Document mapping and null behavior, backfill only where the new meaning is known, and test mixed application versions. Rollback must account for new writes that the old schema cannot represent; restoring an old deployment is not enough.

91. How do you decide whether a schema change is backward compatible?

I evaluate compatibility for actual readers and writers, not only whether the storage engine accepts the DDL. Adding an optional field may be safe for tolerant readers but break a positional SELECT * export or a client that rejects unknown properties. Changing an integer’s unit from seconds to milliseconds is incompatible even when its type stays unchanged.

Change

Potential failure

Useful evidence

Add nullable field

Strict serializers reject new properties

Old-reader contract tests

Narrow a type

Existing or future values no longer fit

Full-range profiling and write tests

Change meaning

Reports remain syntactically valid but become wrong

Versioned definition and business reconciliation

I would define supported producer-consumer version combinations and test them before rollout. Compatibility is a contract property across interfaces, transformations and semantics; storage-level schema evolution solves only part of it.

92. What does field identity contribute to schema evolution in a lakehouse table?

Stable field identity lets a table format distinguish a renamed column from a different column that happens to reuse its name or position. Apache Iceberg tracks fields using identifiers, which helps readers associate stored data with the correct logical field during supported schema changes.

This does not make every change semantically safe. Renaming gross_revenue to net_revenue without changing the data still misleads consumers. I would check the table-format specification, deployed engine support, downstream readers and transformation contract together. A metadata change may avoid rewriting files, but consumers can still require migration, and a data-type change outside supported promotions can require a planned rewrite.

93. How would you choose a partition key for a large fact table?

I would choose it from access patterns, retention operations and the distribution of writes. Time partitioning can help prune date-bounded scans and expire old periods, but its usefulness depends on queries supplying relevant predicates and on keeping the partition count manageable. Partitioning is a physical choice; it does not change the fact grain.

For a clickstream, daily event-date partitions might suit daily reports, while late events must still be routed into older partitions. An ingestion-date partition simplifies arrival management but can require scanning many arrival dates to answer an event-time question. Measure data skew, partition sizes and planning overhead. Also verify engine-specific uniqueness constraints: for PostgreSQL partitioned tables, a unique or primary-key constraint must include all partition-key columns under the documented restrictions.

94. Why can a high-cardinality shard key still produce a hot shard?

High cardinality provides many possible values, but it does not guarantee balanced traffic. A monotonically increasing key can concentrate new writes in one range, and a popular tenant can dominate requests even when there are millions of tenants. I would evaluate frequency, monotonicity and query targeting as well as the count of distinct values.

For a tenant workload, adding a bucket to the key can spread one tenant’s writes, but a tenant-wide query then needs to combine buckets. Hashing can improve distribution while reducing locality for range scans. Choose the trade-off from measured hot-key and query behavior, and plan resharding or key refinement using the database’s supported mechanisms. A diagram with evenly sized shards is not evidence of an evenly loaded production system.

95. How should a model represent an invariant that spans shards or services?

I would identify which component owns the invariant and what consistency it requires. “A username is globally unique” and “inventory never becomes negative” cannot be guaranteed by independent local checks when concurrent requests act on different owners. The model needs an authoritative reservation, coordinated transaction, or a deliberately weaker contract.

For inventory, partitioning ownership by SKU and location can place the stock balance and reservations under one transaction boundary. If an order spans several owners, model reservation states, expiry and compensation explicitly. Eventual reconciliation can repair discrepancies but is not equivalent to preventing overselling. The chosen design should state whether temporary violations are acceptable and which business action is blocked until confirmation.

96. How would you prevent cross-tenant references in a shared relational schema?

I would make tenant ownership part of the relationship, rather than trusting every application join to remember a filter. If order identifiers are unique only inside a tenant, the order key and its child foreign keys should include tenant_id. This prevents a line in one tenant from referencing an order in another.

CREATE TABLE tenant_order (
  tenant_id BIGINT NOT NULL,
  order_id BIGINT NOT NULL,
  PRIMARY KEY (tenant_id, order_id)
);
CREATE TABLE tenant_order_line (
  tenant_id BIGINT NOT NULL,
  order_id BIGINT NOT NULL,
  line_no INTEGER NOT NULL,
  PRIMARY KEY (tenant_id, order_id, line_no),
  FOREIGN KEY (tenant_id, order_id)
    REFERENCES tenant_order (tenant_id, order_id)
)

CREATE TABLE tenant_order (
  tenant_id BIGINT NOT NULL,
  order_id BIGINT NOT NULL,
  PRIMARY KEY (tenant_id, order_id)
);
CREATE TABLE tenant_order_line (
  tenant_id BIGINT NOT NULL,
  order_id BIGINT NOT NULL,
  line_no INTEGER NOT NULL,
  PRIMARY KEY (tenant_id, order_id, line_no),
  FOREIGN KEY (tenant_id, order_id)
    REFERENCES tenant_order (tenant_id, order_id)
)

CREATE TABLE tenant_order (
  tenant_id BIGINT NOT NULL,
  order_id BIGINT NOT NULL,
  PRIMARY KEY (tenant_id, order_id)
);
CREATE TABLE tenant_order_line (
  tenant_id BIGINT NOT NULL,
  order_id BIGINT NOT NULL,
  line_no INTEGER NOT NULL,
  PRIMARY KEY (tenant_id, order_id, line_no),
  FOREIGN KEY (tenant_id, order_id)
    REFERENCES tenant_order (tenant_id, order_id)
)

If the parent table contains only (tenant_id=1, order_id=7), a child (1,7,1) is valid and (2,7,1) must be rejected. The latter cannot borrow tenant 1’s order merely because the order number matches. Make foreign-key enforcement part of the deployed database configuration and verification.

The composite constraint protects referential integrity, not complete authorization. Tenant-aware access policies, trusted tenant context, exports, caches and background jobs still need separate controls. Test with identical order IDs in two tenants so accidental assumptions about global uniqueness become visible.

97. What must a reviewer check before relying on row-level security for tenant isolation?

I would verify which roles are subject to the policy, how tenant context is established and whether every access path uses those roles. In PostgreSQL, table owners normally bypass row security, and superusers and roles with BYPASSRLS do so as well. Tests run only as the owner can therefore give misleading results.

Use a representative restricted application role and test reads, inserts, updates that change ownership, joins and background work. Keep tenant context outside arbitrary client control. Row-level security also does not cover every operation, such as PostgreSQL TRUNCATE; privileges must prevent inappropriate access. The data model should make ownership unambiguous, while policy tests verify that the enforcement matches that ownership.

98. How would you separate sensitive identity data from analytical facts?

I would identify which analyses genuinely need direct identifiers and separate those attributes from broadly used facts. A restricted identity mapping can associate a durable subject key with contact information, while facts reference that key and expose only the necessary analytical attributes. This reduces the number of places holding direct identity data.

It is not automatically anonymization: a stable key or a rare combination of attributes can still enable re-identification. Model access, retention, deletion propagation and approved linkage together. For example, an analyst may need cohort and purchase value but not email or street address. Keep the mapping’s ownership and permitted joins explicit, and verify that exports and derived datasets do not quietly rebuild the sensitive profile.

99. How would you design deletion propagation across derived datasets?

I would treat deletion as a lineage and lifecycle problem, not a single-table statement. Identify the authoritative subject key, where it is copied or transformed, which stores retain versions, and which derived outputs remain linkable to the subject. Then define the required action and completion evidence for each destination.

A deletion record with a stable request identifier can drive repeatable propagation and prevent a later replay from restoring removed data. Backups, snapshots and log retention need separate policies that account for the applicable obligations; a deleted current row does not prove every retained copy is gone. Keep legal interpretation with the responsible specialists, while the model supplies explicit ownership, lineage and technical execution states rather than assuming all data shares one retention rule.

100. How do retention requirements change the physical and logical model?

Retention introduces lifecycle states and boundaries that the model must support deliberately. Different attributes or record classes may have different permitted lifetimes, so embedding all customer data into every event can make selective retention expensive. Time-based partitions can simplify disposal, but only when the partition boundary matches the retention policy.

I would model retention classification, relevant dates and any authorized hold independently from ordinary business status. An inactive customer is not necessarily eligible for deletion, and a cancelled order may still be needed for operational reconciliation. Historical tables, replicas and derived aggregates must be included in the design. Validate that expiry does not break required referential relationships or accidentally cascade into records with a different retention obligation.

101. Two departments define an active customer differently. How would you resolve the model?

I would preserve the distinct business definitions until stakeholders agree they are equivalent. Sales may mean a purchase in the last 90 days, while product may mean a login in the last 30 days. Giving both calculations the same field name produces conflicting reports with no obvious technical error.

Model each metric with its event basis, eligibility rules, time window, timezone and treatment of deleted or test accounts. A semantic layer can expose clearly named measures and an approved shared measure if the organization chooses one. Reconciliation should show the customers included by one rule but excluded by the other. The goal is an explicit decision about meaning, not forcing different operational questions into one convenient Boolean.

102. What belongs in a data contract beyond column names and types?

A useful contract includes grain, key scope, null meanings, units, time semantics, allowed state transitions, ownership and change policy. It should also describe freshness and completeness expectations and which constraints are enforced versus monitored. A structurally valid row can still be unusable when its currency or timestamp meaning is unknown.

For an orders dataset, specify whether it contains one row per order or revision, whether cancelled orders remain present, how refunds appear and how corrections arrive. Add examples of valid and invalid records plus a consumer-visible compatibility policy. Keep the contract versioned with the producing transformation so documentation cannot silently drift away from behavior. A contract must have an owner who can resolve ambiguity and respond to violations.

103. How would you use lineage to assess the impact of a model change?

I would trace both structural dependencies and semantic dependencies. A column rename can affect views, transformations and dashboards that reference it directly, but a changed definition can also affect downstream models through derived columns with different names. Table-level lineage alone often misses that second class.

For a currency conversion change, inspect the source rate, effective-date join, rounding rule and all measures derived from converted amounts. Combine automated dependency discovery with owner review for exports, spreadsheets and dynamic queries. The resulting impact list should identify required migrations and reconciliation tests. Lineage is useful evidence, but its coverage and freshness need to be stated; an empty dependency graph is not proof that no consumer exists.

104. How should model ownership work when several domains share customer data?

I would distinguish ownership of identity, ownership of domain-specific attributes and stewardship of shared definitions. A billing team can own payment preferences without owning the meaning of product engagement. Requiring every change to pass through one universal customer table often creates both coupling and unclear responsibility.

Use stable integration identifiers and explicit contracts between domains, with a documented process for conflicting claims and merges. Shared dimensions can publish conformed attributes where agreement exists, while domain models retain their own valid concepts. For example, a billing account, a person and a product workspace are not interchangeable customers. Their relationships should be modeled rather than collapsed into one record merely because they share an email address.

105. How would you combine customer identities after a merger?

I would retain source identifiers with their namespaces and introduce a reversible mapping to an enterprise identity. Two source systems can both contain customer 123, and matching names or email addresses alone may merge different people or organizations. Record match evidence, confidence, decisions and effective periods where required.

Keep source facts linked to their original identities so a mistaken merge can be undone without rewriting all history. A bridge can map source identities to the enterprise entity, with rules for unresolved and split identities. Test one-to-many and many-to-one cases explicitly. The model should support review and correction; it should not disguise uncertain entity resolution as a database-generated certainty.

106. When is a graph model justified over relational relationships?

I would choose a graph-oriented representation when variable-length relationship traversal and relationship-rich queries are central to the workload. Examples include dependency impact, multi-hop ownership and path constraints. A simple many-to-many relationship does not by itself require a graph database; a junction table may be entirely adequate.

Compare representative queries, update patterns, relationship properties and integrity requirements. A graph can make traversal natural but still needs identity, direction, temporal validity and duplicate-edge rules. For example, “supplier A depends on supplier B” differs from “A transacted with B,” even if both connect the same nodes. Prototype the difficult paths and expected scale before paying the operational and integration cost of another storage model.

107. How would you model entities with several subtypes?

I would select a representation based on subtype invariants and query patterns. A shared parent with subtype tables can keep common identity and enforce required subtype attributes, while a single table with a discriminator simplifies some reads but may create many nullable columns. Separate independent tables can work when shared identity is not a genuine requirement.

Representation

Useful when

Main review concern

Parent plus subtype tables

Subtypes share identity and have substantial distinct attributes

Enforcing allowed subtype combinations

Single table with discriminator

Subtypes are small and queried together often

Conditional constraints and sparse fields

Separate tables

Types have independent lifecycles

Cross-type references and duplicated logic

For employees and contractors, ask whether one person can be both simultaneously and whether the relationship changes over time. The answer determines exclusivity and temporal constraints. An inheritance diagram copied from application classes is not sufficient evidence for the data model.

108. When does an entity-attribute-value model become a maintenance problem?

It becomes problematic when important business attributes lose their types, constraints and discoverability inside generic name-value rows. A product catalog may need extensible attributes, but storing price, currency and inventory as arbitrary strings makes validation and correct aggregation harder. Every query must reconstruct meaning that a typed schema could express directly.

I would keep stable, operationally significant fields typed and model genuinely variable attributes with a governed definition catalog, allowed types and validation rules. JSON or EAV can be appropriate for sparse, evolving properties, but neither removes schema responsibility. Review indexing, multi-attribute predicates, units and duplicate attribute rules. Flexibility is valuable only when consumers can still determine what a value means and whether it is valid.

109. How would you decide which constraints to enforce and which to monitor?

I would enforce invariants that must hold at the write boundary when the platform can do so reliably, and monitor broader expectations that require delayed or external evidence. A unique order-line key is a strong database constraint; an expectation that daily volume remains near last week’s level is an operational quality check.

For each rule, define consequence, owner, detection delay and remediation. Rejecting a malformed transaction can protect correctness, while rejecting an entire source batch because one optional description is missing may damage availability unnecessarily. Monitoring is not a substitute for enforcement when concurrent writes can violate a critical invariant. Conversely, a strict constraint based on an unstable business assumption can turn a valid new case into an outage.

110. What evidence would you require before denormalizing a production model?

I would require a measured bottleneck, a representative workload and a plan to preserve or reconcile duplicated facts. Denormalization can reduce joins or remote reads, but it moves work into updates, backfills and repair. Faster reads are not sufficient if stale copies produce inconsistent decisions.

For example, copying a product label into a sales fact may intentionally preserve the label at sale time; copying the current credit limit into several services has different correctness risks. Declare whether the copy is historical, a cache or another authoritative fact. Benchmark the proposed design with realistic update rates and skew, then measure freshness, write amplification and recovery cost alongside query latency.

111. How would you diagnose a model that is slow only for the largest customer?

I would investigate skew and cardinality assumptions before adding hardware or indexes indiscriminately. A model that assumes a few child records per customer may perform well for most tenants and fail for one with millions. The largest customer may also create a hot partition or an oversized embedded document.

Compare row counts, relationship fan-out, access patterns and execution plans for typical and extreme tenants. A targeted design might separate high-volume child collections, bucket time-series records, preaggregate a specific read or isolate an exceptional tenant. Each option changes operational complexity. Keep the logical contract stable where possible and test the long tail explicitly; average cardinality hides the users most likely to expose the model’s limits.

112. How would you model a business process whose state can move backward?

I would distinguish the current state from the history of transitions and avoid assuming that a status sequence is always monotonic. An order can move from shipped to returned, a case can reopen, and an approval can be revoked. A single set of milestone dates may not represent repeated cycles correctly.

Use a transition record with event identity, previous and new state, occurrence time and reason where these are meaningful. Derive the current state through an explicit ordering rule, while retaining cycles for analysis. If a process permits only certain transitions, enforce or validate that rule at the authoritative boundary. Do not infer duration from the first and last timestamp without defining whether reopened intervals count toward the metric.

113. How would you model corrections to a financial or operational ledger?

I would preserve the original entry and represent corrections through linked reversal or adjustment entries when the ledger’s contract requires an auditable history. Overwriting the amount destroys the trail connecting the original event, its correction and the reported balance. The exact accounting treatment belongs to the applicable domain rules.

For a simple operational example, a mistaken quantity of +10 can be reversed with -10 and replaced with +8, with all three entries linked to the correction case. The net quantity is +8, while the history explains why. Define idempotency, authorization, period boundaries and whether reports use occurrence or posting time. An append-only table still needs integrity controls; immutability alone does not make entries correct or balanced.

114. Why can daily distinct-customer counts not be added to obtain a monthly distinct count?

Distinct counts depend on overlap between sets, which a scalar daily count does not retain. A customer active on two days contributes once to the monthly distinct count but once to each daily count. Summing daily counts therefore measures customer-days, not distinct monthly customers.

Day

Observed customer IDs

Daily distinct count

Day 1

C1, C2

2

Day 2

C2, C3

2

Both days

C1, C2, C3

3

The two daily counts sum to 4, while their union contains 3 customers. A model supporting exact monthly counts must retain enough identity information to calculate the union, for example customer-day membership or atomic events. With complete event data, the calculation can be:

SELECT COUNT(DISTINCT customer_id) AS active_customers
FROM activity_event
WHERE event_date >= '2026-01-01'
  AND event_date < '2026-02-01'

SELECT COUNT(DISTINCT customer_id) AS active_customers
FROM activity_event
WHERE event_date >= '2026-01-01'
  AND event_date < '2026-02-01'

SELECT COUNT(DISTINCT customer_id) AS active_customers
FROM activity_event
WHERE event_date >= '2026-01-01'
  AND event_date < '2026-02-01'

This assumes a governed event-date calendar and a consistent customer identity; NULL customer IDs are excluded by the aggregate. At large scale, mergeable approximate distinct-count sketches can reduce storage, but the model must retain compatible sketches and disclose their error characteristics. Adding already-computed approximate counts is no more valid than adding exact daily counts.

115. How would you support both current and historically reproducible product hierarchies?

I would separate the hierarchy version used for a historical report from the current classification used for restated analysis. A product moving from one category to another should not silently change every past category total unless that is the intended reporting contract. Effective-dated relationships or versioned hierarchy snapshots can make the choice explicit.

For example, a sale can retain its historical product-version key while a separate mapping supports analysis under today’s category tree. Validate cycles, multiple parents and date gaps according to the hierarchy’s rules. If multiple parents are allowed, define whether totals duplicate, allocate or deduplicate contributions. A tree-shaped visualization must not hide a graph-shaped business relationship.

116. What makes a model migration rollback plan credible?

A credible plan explains how to restore correct service behavior after new writes have occurred, not just how to restore the old table definition. If the new model permits multiple addresses but the old application supports one, rolling back the binary cannot represent all new data. The migration must define a reversible mapping or a bounded point beyond which rollback becomes a forward repair.

I would preserve source versions, checkpoints and reconciliation evidence, test mixed versions, and define measurable cutover criteria. Keep readers switchable until the new model is validated. For destructive transformations, a backup is useful only with a tested restore and replay process that meets recovery needs. State the expected data loss or service interruption explicitly rather than labeling an untested restore command a rollback strategy.

117. How would you validate a migrated model when row counts legitimately change?

I would compare business invariants and reconciliation mappings rather than require identical row counts. Normalizing a customer table into customer and address tables or splitting order headers from lines changes counts by design. The validation must show that each source fact has a justified destination and that required meaning is preserved.

Check key coverage, orphan counts, duplicate mappings, totals at defined grains and representative edge cases. For a currency migration, reconcile both original-currency totals and converted outputs under the agreed rounding rule. Maintain a reject or exception ledger with explanations. Matching grand totals alone can hide offsetting errors, so compare by meaningful partitions such as tenant, date and source system as well as selected individual records.

118. How would you balance a canonical enterprise model with domain-specific models?

I would standardize the concepts that genuinely require interoperability and let domains model their own behavior behind explicit contracts. A universal model can reduce translation, but it can also become a bottleneck that forces different meanings into vague entities. Domain models improve local clarity but need deliberate integration boundaries.

For instance, a shared legal-entity identifier may be valuable across billing and procurement, while a product workspace and a billing account should retain separate identities and lifecycles. Publish the relationships between them, including cardinality and effective dates. Evaluate the cost of translation against the cost of shared change coordination. The right scope of standardization follows actual cross-domain decisions, not a goal of having one diagram for everything.

119. When does a fourth-normal-form decomposition remove redundant combinations without losing a real relationship?

It is appropriate when two multivalued attributes are independent for the same entity. Fourth normal form requires the determinant of each nontrivial multivalued dependency to be a superkey. The key business question is whether the combinations carry information or merely repeat two independent sets.

Suppose a product is offered in red and blue and is stocked by warehouses W1 and W2, with every listed color available at every listed warehouse. A combined relation needs four rows:

Product

Color

Warehouse

P1

Red

W1

P1

Red

W2

P1

Blue

W1

P1

Blue

W2

Separate ProductColor(product_id, color) and ProductWarehouse(product_id, warehouse_id) store two rows each; joining them reconstructs the four permitted combinations. Adding a third warehouse requires one new warehouse relationship rather than repeating it for every color.

The decomposition is wrong if W1 stocks only red and W2 only blue: joining the two projections would invent unavailable combinations. In that case color availability depends on the product-warehouse combination, so the three-way relationship must remain. Validate independence with domain owners and counterexamples; visual repetition alone is not evidence of a multivalued dependency.

120. How would you explain a modeling decision when the available evidence is incomplete?

I would separate established requirements from assumptions, choose the simplest design that meets the known needs and document what evidence would trigger reconsideration. For example, embedding a bounded set of delivery instructions may be reasonable today, while an unbounded history of delivery events should remain separately addressable. The decision should identify the expected size and lifecycle rather than claim one storage pattern is universally best.

Use a small prototype or representative data profile to reduce the most consequential uncertainty. Record the alternatives, migration path and signs of failure, such as excessive fan-out or repeated cross-domain updates. A strong senior answer is explicit about confidence and reversibility: it makes a defensible choice now while preserving the ability to correct it when the workload or business meaning changes.

Data Modeling Documentation and Further Reading

These primary references support relational constraints, dimensional design, document modeling and schema evolution. PostgreSQL-specific behavior refers to version 18; other engines may enforce nulls, uniqueness, temporal periods or partitioned constraints differently. Product documentation describes implementation capabilities, while business definitions and historical policies must be agreed for the model being built.

Team Sherlock.sh

Building fraud intelligence for live high-stake video interactions (financial discussions, hiring interviews, board meeting etc).

Catch candidate fraud before it costs you.

Sherlock AI helps you detect AI-assisted, proxy, and deepfake candidates, so every hiring decision is backed by evidence.

Ask AI about Sherlock.sh

Similar Posts