Interview Questions for Data Modeling

Here is a comprehensive list of technical data modeling interview questions, categorized by skill level. These questions focus on relational (OLTP) and dimensional (OLAP/Data Warehousing) modeling, with a touch of NoSQL for the advanced tier.

I have also included “Expected Answer Bullets” so you know exactly what to listen for.

---

Beginner Level (Conceptual & Logical Understanding)

Goal: Ensure they understand the basics of keys, normalization, and relationship types.

1. The “Employee Manager” Problem (Relationships)
Question: In an Employees table, how would you model the relationship where one employee manages another? What is this type of relationship called?

• Expected Answer: It is a Recursive (or Self-Referencing) Foreign Key. The table would have an EmployeeID (Primary Key) and a ManagerID (Foreign Key that references EmployeeID). This represents a one-to-many relationship (one manager has many employees).

2. Normalization Explainers
Question: Briefly explain 1st, 2nd, and 3rd Normal Form (1NF, 2NF, 3NF) in plain English.

• Expected Answer:
  • 1NF: Atomic values (no repeating groups or arrays in a single column).
  • 2NF: Must be in 1NF, and every non-key column must depend on the entire primary key (relevant for composite keys).
  • 3NF: Must be in 2NF, and no non-key column depends on another non-key column (no transitive dependencies).

3. Natural vs. Surrogate Keys
Question: What is the difference between a natural key and a surrogate key? Give an example of when you would use each.

• Expected Answer:
  • Natural Key: A column that already exists in the real world (e.g., SSN, ISBN, VIN). Risky because they can change or be reused.
  • Surrogate Key: An artificially generated unique identifier (e.g., an INT IDENTITY or UUID). Preferred in data warehouses because it isolates the fact table from changes in the source system.

---

Intermediate Level (Physical Design & Dimensional Modeling)

Goal: Assess their ability to handle performance, historical data, and warehousing structures.

4. Indexing Strategy
Question: You have a Sales table with 1 billion rows. Users frequently query by Order_Date and Customer_ID. How would you index this table, and what is the danger of creating too many indexes?

• Expected Answer:
  • Create a Composite Non-Clustered Index on (Customer_ID, Order_Date) because Customer_ID is likely highly selective.
  • Danger: Too many indexes drastically slow down INSERT, UPDATE, and DELETE operations (write amplification) because the index must be updated alongside the table.

5. Star Schema vs. Snowflake Schema
Question: When building a data warehouse, when would you choose a Star Schema over a Snowflake Schema?

• Expected Answer:
  • Star Schema: Denormalized dimensions. Preferred for simplicity, faster query performance (fewer joins), and easier use by BI tools.
  • Snowflake Schema: Normalized dimensions. Used to save storage space or when dimensions are extremely complex. Downside: More joins = slower queries.
  • Key insight to listen for: “Always start with Star and only snowflake if storage is a crisis.”

6. Handling SCD Type 2
Question: A customer changes their address. In a data warehouse, we need to keep the historical address for old orders. How do you model this? (Hint: SCD)

• Expected Answer: This is a Slowly Changing Dimension (SCD) Type 2.
  • Add columns: Start_Date, End_Date (or NULL for current), and Is_Current_Flag.
  • Instead of updating the old record, you expire the old row (set End_Date = yesterday) and insert a new row with the new address, a new surrogate key, and Start_Date = today. This ensures historical fact tables still point to the old surrogate key.

---

Advanced Level (Scalability, NoSQL, & Complex Optimization)

Goal: Stress-test their critical thinking on distributed systems, anti-patterns, and enterprise architecture.

7. The Polymorphic Association (Anti-Pattern)
Question: You have an Orders table and a Returns table. You are asked to create a single Notes table that can attach comments to either an Order or a Return. The junior developer proposes a table with columns: NoteID, Note_Text, Parent_Type (‘Order’ or ‘Return’), and Parent_ID. Why is this a bad practice, and how would you fix it?

• Expected Answer:
  • Why it’s bad: You cannot enforce a Foreign Key constraint because the Parent_ID references two different tables. This breaks referential integrity.
  • Fix: Create two separate junction tables: Order_Notes and Return_Notes. Or, use concrete super-types (create a generic Event table that both Orders and Returns inherit from, then link Notes to Event_ID).

8. Eventual Consistency vs. Strong Consistency
Question: You are modeling a microservices architecture. You have a Product Catalog service and an Inventory service. How do you model the data relationship between these two services if they use separate databases, and what consistency model do you choose?

• Expected Answer:
  • Modeling: There is no foreign key across databases. You store the Product_ID (as a value) in the Inventory service, but do not enforce a physical relationship.
  • Consistency: You must use Eventual Consistency via asynchronous messaging (e.g., Kafka, SQS). If a product is deleted in Catalog, an event is published to update Inventory. You cannot use a distributed transaction (2PC) because it creates tight coupling and availability issues.

9. Query Optimization using Execution Plans
Question: You run a query that joins a 10-million-row Fact_Sales table to a 10,000-row Dim_Product table. The query takes 45 seconds. You look at the execution plan and see a “Hash Match” join and a “Key Lookup” (Clustered) taking 80% of the cost. How do you fix this?

• Expected Answer:
  • Problem: The “Key Lookup” means the index used does not “cover” the query. The database is finding the rows in the index, then jumping back to the clustered table to get the remaining columns.
  • Fix 1: Create a Covering Non-Clustered Index on Dim_Product that includes all the columns requested in the SELECT and WHERE clauses (or use INCLUDE for the heavy text columns).
  • Fix 2: If filtering heavily, change the join order using FORCE ORDER or rewrite the query to push filters into a subquery before the join (reduce the Hash Match bucket size).

10. Designing for Time-Series Data (Partitioning)
Question: You are modeling a table for IoT sensor data that will ingest 1 million rows per hour. Retention policy requires keeping data online for 3 years. What physical database design technique is mandatory for this scenario, and how does it impact your key design?

• Expected Answer:
  • Technique: Range Partitioning (usually by date). You should partition the table by the Event_Time column (e.g., daily or monthly partitions).
  • Key Design: The Primary Key must include the partition key (e.g., PRIMARY KEY (Sensor_ID, Event_Time)). This creates a composite key that ensures uniqueness within a partition.
  • Bonus Insight: This allows for Partition Switching (fast loading of new data) and Partition Truncating (instant deletion of old data without locking the table).

11. The Polymorphic Contact Manager (Anti-Pattern Identification)

Question: You are reviewing a legacy database design. The team has a Person table with a PersonID. They also have a ContactManager table with the following columns: ContactManagerID (PK), PersonID (FK to Person), ContactID (INT), and ContactType (VARCHAR check constraint: ‘Email’, ‘Phone’, ‘Address’).

The logic is: if ContactType = ‘Email’, the ContactID references the Email table; if ‘Phone’, it references the Phone table; if ‘Address’, it references the Address table.

What is this design pattern called? Is this an ideal approach for a relational database? If not, explain the specific technical flaws and provide two alternative solutions to fix this design.

Expected Answer:

• Beginner Level (Awareness):
  • Pattern Name: “Polymorphic Associations” or “Generic Foreign Keys.”
  • Initial Assessment: “I don’t think this is a good idea, because I can’t draw a proper ER diagram for it. It feels like the database can’t enforce rules properly.”
• Intermediate Level (Flaw Identification):
  • Is it ideal? No, this is considered an anti-pattern in relational modeling.
  • Specific Technical Flaws:
    1. No Referential Integrity: You cannot define a Foreign Key constraint on ContactID because it points to three different tables based on the ContactType. The database cannot guarantee that ContactID 123 actually exists in the Email table when ContactType = ‘Email’.
    2. Complex Querying: To retrieve a person’s contact info, you must write complex CASE statements, UNION queries, or join all three tables and COALESCE the results. You cannot use simple INNER JOINs.
    3. Indexing Inefficiency: A single index on ContactID is inefficient because the ID values across the three parent tables are semantically different and may overlap.
    4. Constraint Limitations: You cannot enforce type-specific rules (e.g., requiring an Email_Address to be NOT NULL or a Phone_Number to have a Country_Code) because the ContactManager table only holds generic IDs.
• Advanced Level (Solutions & Trade-offs):
  • “To fix this, I would propose two solutions depending on the use case:”
  • Solution A: Supertype/Subtype (Best for Transactional/OLTP systems)
    • Create a master Contacts table with a ContactID (PK) and ContactType.
    • Change the Person table to have an FK directly referencing this new Contacts.ContactID.
    • Change the Email, Phone, and Address tables so their Primary Key is not auto-generated. Instead, their ContactID acts as both the Primary Key and a Foreign Key back to the master Contacts table. This enforces that every subtype record has a valid master record, and maintains a hard FK between Person and Contact.
  • Solution B: Multiple Explicit Foreign Keys (Simpler, but less extensible)
    • Remove the ContactManager table entirely.
    • Add three nullable columns directly to the Person table: EmailID (FK to Email), PhoneID (FK to Phone), and AddressID (FK to Address).
    • This allows the database to enforce strict referential integrity for each type individually.
    • Drawback: Requires schema changes (new columns) every time a new contact type (e.g., SocialMediaID) is introduced.
  • Expert Level (Contextual Awareness):
    • “Before choosing a fix, I need to clarify the business requirement. If this is a Data Warehouse (OLAP), I wouldn’t use either of those relational solutions. I would likely denormalize the data and store the actual Email, Phone, and Address values directly in a wide Dim_Person table (with NULLs), because we don’t enforce Foreign Keys in dimensional models, and we prioritize query performance over storage normalization.”
• Is solution A still polymorphic?
• Follow-up Question (Optional):
  • “In Solution A, we introduced a master Contacts table. Is this still considered a polymorphic association? Why or why not?”
    Expected Answer:
    Beginner: “I think it is, because we still have a ContactType column.”
    Intermediate: “No, it is not polymorphic anymore. The ContactID in the Person table now references only one table (Contacts), so we can enforce a proper Foreign Key constraint. The ContactType is just an attribute now.”
    Advanced (The “Aha!” insight):
    “It is no longer polymorphic because we have removed the ambiguity. In the original design, the ContactID meant three different things depending on the row, that was the polymorphism. In Solution A, the ContactID always means ‘a row in the Contacts table.’ The type column simply tells you which child table to join to for the details. This is standard relational inheritance modeling, often called Single Table Inheritance at the physical level, but strictly speaking, it is a Supertyping pattern, not a polymorphic association.”
• Pro Tip for your interview: The best senior candidates won’t just stop at fixing the FK, they will ask you “What queries run against this?” because if you rarely query contacts across all types, Solution B (3 nullable FKs) might actually be faster and simpler than Solution A (which requires an extra join to the master table for every contact lookup). The expert knows there is no “perfect” solution, only trade-offs based on access patterns.

12. Handling Shared Contacts (Many-to-Many Scenario)

Question: Building on the previous Supertype/Subtype model (Solution A), we now have a new business requirement: An Address can be shared by multiple People (e.g., family members living at the same residence, or a business address used by many employees).

However, an Email and a Phone number are still considered unique to a single Person (cannot be shared).

Given this mixed requirement, how would the Supertype/Subtype model need to be altered? Walk through the specific structural changes and explain why this is now a different relationship pattern.

---

Expected Answer:

• Beginner Level (Awareness):
  • “I would just put the same ContactID into multiple Person records. So, if two people share an address, they both have the same AddressID in their Person row.”
  • Interviewer note: This shows they understand the concept but haven’t yet considered the data integrity or modeling implications. It also breaks the uniqueness assumption of the Contacts table if we use the original foreign key approach.
• Intermediate Level (Structural Understanding):
  • The Problem with the Original Model:
    • In Solution A, Person has a one-to-many relationship with Contacts (one Person, many Contacts).
    • If an Address is shared, we now have a many-to-many relationship between Person and Contacts for that specific ContactType.
    • We cannot simply put the same ContactID in multiple Person rows unless we change the relationship and remove the FK constraint from Person, which breaks referential integrity.
  • The Fix:
    • Remove the PersonID column from the Contacts master table.
    • Create a junction (bridge) table called PersonContact with a composite key of (PersonID, ContactID).
    • PersonID is an FK to Person.
    • ContactID is an FK to Contacts.
    • This allows any Person to link to any Contact, and any Contact to link to multiple People.
• Advanced Level (Constraints & Business Logic Enforcement):
  • “The structural fix is a junction table, but that alone isn’t enough—I need to enforce the business rules at the database level to prevent emails and phones from being shared, while allowing addresses to be shared.”
  • How to enforce this:
    1. Option A (Database Constraints): Add a Is_Shared boolean column to the Contacts master table. Then create a unique constraint on (ContactID, PersonID) in the junction table—but that doesn’t solve the sharing restriction.
    2. Option B (Trigger or Application Logic – Practical approach): The junction table itself doesn’t prevent sharing. To enforce the rule, you would:
       • Allow multiple PersonContact rows for the same ContactID.
       • But, add a Max_Shared or Is_Shareable column to the ContactType lookup table (or directly on Contacts).
       • Then, use a trigger (or application-level validation) that checks: “If ContactType = ‘Email’ or ‘Phone’, count the number of rows in PersonContact for this ContactID and throw an error if > 1.”
       • Alternative: If using a modern RDBMS (like PostgreSQL), use a partial unique index:sqlCREATE UNIQUE INDEX idx_unique_person_contact ON PersonContact (ContactID) WHERE ContactType IN (‘Email’, ‘Phone’);(Note: This requires the ContactType to be denormalized into PersonContact or a join in the index, which may not be supported by all databases.)
    3. Option C (Schema Separation – Cleanest but more tables):
       • Keep the many-to-many junction for Addresses only: PersonAddress (PersonID, AddressID).
       • Keep the one-to-many FKs for Email and Phone directly on the Person table (or via a non-shared junction).
       • This physically enforces the rule without triggers, because the junction table allows sharing, while the direct FK does not.
• Expert Level (Contextual Trade-offs):
  • “Before implementing Option B or C, I need to ask: How often do addresses change? If addresses are frequently updated and shared by hundreds of employees, I might consider a normalized approach (Option C) to save storage and maintain single points of update. However, if this is a Data Warehouse, I’d ignore the normalization entirely and denormalize the shared address into each Person’s row to avoid complex joins in BI reports. The ‘ideal’ model depends entirely on whether this is an OLTP system with strict write integrity or an OLAP system with read performance priorities.”
  • “Additionally, I would ask: If an address is shared, and one person updates it, should it update for everyone? That dictates whether we use a ‘shared reference’ (junction table) or copy the address value directly into each person’s row.”

---

Intermediate Level (Relationship Evolution & Role-Based Modeling)

1B. The Employee Manager Problem – Extended (Multiple Manager Types)

Question:
Building on the previous “Employee Manager” model, the business has introduced a new requirement:

“Each employee now has three types of managers:

1. Direct Manager (the person they report to daily – the original requirement).
2. Dotted-Line Manager (a secondary manager from a different department who provides project oversight).
3. Location Manager (the manager responsible for the physical office/branch where the employee sits).”

How would the data model need to change to accommodate these three distinct management relationships? Walk me through the trade-offs of at least two different modeling approaches.

---

Expected Answer:

• Beginner Level (Awareness):
  • “I would just add three columns to the Employees table: ManagerID, DottedLineManagerID, and LocationManagerID. All three would be Foreign Keys that reference EmployeeID.”
  • Interviewer note: This works functionally but shows a lack of foresight. It hard-codes the relationship types into the schema, meaning any new manager type (e.g., “Project Manager,” “Regional Manager,” “HR Partner”) requires an ALTER TABLE to add a new column.
• Intermediate Level (Normalization & Flexibility):
  • Problem with the 3-column approach: It’s not extensible. If we add a 4th or 5th type of manager, we have to change the table structure, which requires downtime, migration scripts, and code changes.
  • Better Approach – Junction Table with Roles:
    • Remove ManagerID columns from the Employees table entirely.
    • Create a junction table called EmployeeManager or ManagementStructure with the following columns:
      • EmployeeID (FK to Employees) – The subordinate.
      • ManagerID (FK to Employees) – The supervisor.
      • RelationshipType (VARCHAR or INT FK to a lookup table) – Values: ‘Direct’, ‘DottedLine’, ‘Location’.
    • This creates a many-to-many relationship between Employees and themselves, with a role/type discriminator.
    • Why this is better: Adding a new manager type (e.g., “Project Manager”) simply requires inserting a new row into a RelationshipType lookup table. No schema changes required.
  • Constraint Consideration:
    • If every employee must have exactly one Direct Manager, you can enforce this with a unique constraint on (EmployeeID, RelationshipType) where RelationshipType = ‘Direct’, or use a filtered/partial index. Alternatively, enforce it at the application level or via a trigger.
• Advanced Level (Temporal Modeling, Hierarchy Depth, & Edge Cases):
  • “The junction table is the right structural move, but I have several follow-up questions and enhancements:”
  • 1. Temporal Tracking (Historical Auditing):
    • “Does the business need to track when a management relationship changed? For example, if John had a different dotted-line manager last quarter, we might need to preserve that history for performance reviews or organizational audits.”
    • Solution: Add Start_Date and End_Date (or Is_Current_Flag) to the EmployeeManager junction table. This makes it an SCD Type 2 structure for management relationships.
  • 2. Circular Reference Prevention:
    • “We must prevent circular reporting structures (e.g., A manages B, B manages C, and C manages A), which can cause infinite loops in queries and reporting.”
    • Solution: This cannot be fully enforced by standard SQL constraints. It requires either:
      • Application-level validation before insert/update.
      • A trigger that checks for cycles using a recursive CTE and rolls back the transaction if a cycle is detected.
      • Database-specific features: Some databases (like PostgreSQL with WITH RECURSIVE) can enforce this via exclusion constraints, but typically it’s handled in the business logic layer.
  • 3. Depth & Hierarchy Reporting:
    • “How deep does the management chain go? If we need to report on the entire organizational tree (e.g., ‘show me all employees under VP of Engineering’), we need to consider recursive query performance.”
    • Solution:
      • For transactional systems, use recursive CTEs to traverse the hierarchy.
      • For reporting/OLAP systems, consider denormalizing with a closure table (precomputing all ancestor-descendant paths) or materializing the hierarchy path (e.g., storing a Path string like /CEO/VP/Director/Employee).
  • 4. Edge Cases:
    • “Can an employee be their own manager? (Should be prevented.)”
    • “Can an employee have no manager (e.g., the CEO)? How do we represent NULL values in the junction table?”
    • “What happens when a manager leaves the company? Do we cascade delete, or do we require the manager to be reassigned first?” (This shows they think about data integrity and business processes.)
• Expert Level (Organizational Modeling & Graph Databases):
  • “If management relationships are a core domain concept with multiple types, changing over time, and we frequently query the entire organizational tree, I would consider using a Graph Database (like Neo4j) instead of a relational model.”
  • “In a graph model, Employees are nodes, and management relationships are edges with properties (Type, Start_Date, End_Date). This makes recursive queries (‘find all subordinates of X at any depth’) O(1) in traversal complexity and avoids expensive recursive CTEs in SQL.”
  • “However, if we must stay in SQL, I’d evaluate whether a Closure Table is worth the storage overhead for faster reads, or if we can tolerate recursive CTEs for occasional queries.”

Question	Correct Answer
Is this design polymorphic?	NO. Polymorphism requires a single FK pointing to multiple tables. Here, it points to only one.
What is this pattern called?	Supertype/Subtype (or Table Inheritance).
What is the discriminator column for?	Descriptive metadata—it tells you which subtype table contains the details, but it does not route the FK.
Can you enforce referential integrity?	YES. A hard FK can be placed from PersonContact.ContactID to Contacts.ContactID.
Is this a good design?	YES. It is the industry-standard relational solution for this problem.

---

Bonus: “Whiteboard” Scenario (For all levels)

Give them a piece of paper and ask:

“Draw a logical data model for a University Registration System. We have Students, Professors, Courses, and Rooms. A Student can enroll in many courses; a Course is taught by one Professor; a Course meets in one Room.”

• Beginner check: Do they draw the 4 tables and establish Foreign Keys?
• Intermediate check: Do they realize a Student_Course junction table is needed for the many-to-many relationship?
• Advanced check: Do they ask clarifying questions? (e.g., “Can a room be used by multiple courses at different times? If so, we need a Time slot dimension.”). The advanced candidate does not just draw; they define the grain.