Data Modeling interview questions

What a strong answer covers

• Define an ERD as a visual representation of entities, attributes, and relationships in a database.
• Identify entities as real-world objects or concepts (e.g., Customer, Product).
• Describe attributes as properties or characteristics of entities (e.g., Customer Name, Product Price).
• Explain relationships as associations between entities, including cardinality (one-to-one, one-to-many, many-to-many).

Where people lose the point

• ×Confusing entities with tables or attributes with columns without explaining the conceptual difference.
• ×Omitting cardinality or misrepresenting relationship types.
• ×Failing to mention the purpose of an ERD as a communication tool.

What a strong answer covers

• Define a primary key as a column or set of columns that uniquely identifies each record in a table.
• State that primary keys enforce entity integrity and cannot contain NULL values.
• Define a foreign key as a column or set of columns in one table that refers to the primary key in another table.
• Explain that foreign keys establish relationships between tables and enforce referential integrity.

Where people lose the point

• ×Confusing the roles, e.g., saying a foreign key uniquely identifies a record.
• ×Not mentioning the integrity constraints (entity integrity for PK, referential integrity for FK).
• ×Failing to explain how they link tables together.

What a strong answer covers

• Define normalization as a systematic process of structuring a relational database to reduce data redundancy.
• Explain its primary goal is to improve data integrity and consistency.
• Discuss how it helps eliminate data anomalies (insertion, update, deletion anomalies).
• Mention that it makes the database more flexible and easier to maintain.

Where people lose the point

• ×Only stating it reduces redundancy without explaining *why* that's important (integrity, anomalies).
• ×Confusing normalization with denormalization.
• ×Not mentioning the concept of normal forms.

What a strong answer covers

• Describe Star Schema: a central fact table surrounded by denormalized dimension tables, simple joins.
• Describe Snowflake Schema: a central fact table with normalized dimension tables (dimensions linked to other dimensions), more complex joins.
• Discuss advantages of Star: simpler queries, better performance for most analytical queries, easier to understand.
• Discuss advantages of Snowflake: reduced data redundancy in dimensions, potentially less storage, better for complex hierarchies.
• Explain the trade-off: Star for performance/simplicity, Snowflake for storage/data integrity in dimensions.

Where people lose the point

• ×Incorrectly describing the structure of either schema (e.g., saying Star has normalized dimensions).
• ×Failing to articulate the core trade-off between query performance/simplicity and storage/redundancy.
• ×Not mentioning their primary use in data warehousing/analytics.

What a strong answer covers

• Explain normalization's benefits: reduced redundancy, improved data integrity, easier maintenance, fewer update anomalies.
• Explain normalization's drawbacks: more complex queries (joins), potentially slower read performance.
• Explain denormalization's benefits: faster read performance (fewer joins), simpler queries for specific access patterns.
• Explain denormalization's drawbacks: increased data redundancy, potential for update anomalies, higher storage costs, more complex write operations.
• Conclude that the choice depends on the application's specific requirements (OLTP vs. OLAP, read vs. write heavy).

Where people lose the point

• ×Presenting one as universally superior without discussing context.
• ×Not clearly linking the benefits/drawbacks to specific database operations (reads/writes).
• ×Failing to mention the impact on data integrity for denormalization.

What a strong answer covers

• Relational: fixed schema, tables, rows, columns, normalization, joins, ACID properties.
• Document: flexible schema, JSON-like documents, embedding/denormalization, no joins (or limited), eventual consistency.
• Discuss the 'schema-on-write' vs. 'schema-on-read' concept.
• Explain how embedding related data in documents (denormalization) is common in document databases to optimize for specific read patterns, contrasting with relational normalization.
• Highlight the impact on data integrity and consistency (ACID vs. BASE).

Where people lose the point

• ×Only listing features without explaining the *modeling* implications.
• ×Not discussing the concept of embedding/denormalization in document databases.
• ×Failing to mention the schema flexibility of document databases.

What a strong answer covers

• Define a many-to-many relationship (e.g., Students and Courses).
• Explain that it cannot be directly represented with foreign keys in the two main tables.
• Describe the need for an intermediary (junction/associative) table.
• Illustrate with an example: `Students` table, `Courses` table, and `Enrollments` table linking them with foreign keys.
• Mention that the junction table typically has a composite primary key made of the foreign keys from the two related tables.

Where people lose the point

• ×Attempting to solve it by adding a foreign key directly to one of the main tables.
• ×Not explaining the purpose of the junction table clearly.
• ×Failing to provide a concrete example.

What a strong answer covers

• Define OLTP: focuses on high-volume, concurrent, short transactions (inserts, updates, deletes), emphasizes data integrity and consistency.
• Define OLAP: focuses on complex queries, aggregations, historical data analysis, emphasizes read performance and data availability.
• Modeling for OLTP: highly normalized relational models, optimized for writes, ACID compliance, small transactions.
• Modeling for OLAP: dimensional models (star/snowflake), denormalization, optimized for reads, historical data, often uses NoSQL or columnar stores.
• Discuss the trade-offs in schema design, indexing, and data consistency between the two paradigms.

Where people lose the point

• ×Confusing the goals or characteristics of OLTP and OLAP.
• ×Not linking specific modeling techniques (normalization, denormalization, dimensional) to the respective system types.
• ×Failing to discuss the impact on performance and data integrity for each.

What a strong answer covers

• Propose a relational model: `Users` table (id, username, profile_info), `Posts` table (id, user_id, content, timestamp), `Likes` table (post_id, user_id, timestamp).
• Discuss how to handle a user's feed: joining `Posts` with `Users` and potentially `Likes` for display.
• Address scalability challenges: high read/write volume for posts and likes, 'fan-out' problem for feed generation.
• Suggest alternative approaches for scalability: denormalization (e.g., pre-calculating feeds), using NoSQL (e.g., document for posts, graph for followers, column-family for feeds).
• Mention considerations for real-time updates and eventual consistency in a distributed system.

Where people lose the point

• ×Only providing a basic relational model without discussing its limitations for social media scale.
• ×Not addressing the 'feed generation' challenge specifically.
• ×Failing to suggest alternative modeling paradigms or optimizations for scalability.

What a strong answer covers

• Explain that graph databases excel when data relationships are as important as the data itself.
• Describe their structure: nodes (entities) and edges (relationships) with properties.
• Highlight their strength in traversing complex, multi-hop relationships efficiently.
• Provide use cases: social networks (friend-of-friend queries), recommendation engines (users who bought X also bought Y), fraud detection (unusual connection patterns).
• Contrast with relational (joins become complex/slow for deep relationships) and document (relationships are often embedded or loosely linked, not first-class citizens).

Where people lose the point

• ×Only listing features without explaining *why* they are superior for certain use cases.
• ×Not providing concrete examples where graph traversal is key.
• ×Failing to explain the fundamental difference in how relationships are handled.

What a strong answer covers

• Define Slowly Changing Dimensions (SCDs) as dimensions whose attribute values change over time, and how to manage these changes in a data warehouse.
• Explain the challenge: historical analysis requires preserving past attribute values, while current analysis needs the latest values.
• Describe Type 2 SCD: creates a new row in the dimension table for each change, preserving the full history of the dimension attribute.
• Detail the implementation of Type 2 SCD: adding start_date, end_date, and a current_flag to the dimension table.
• Discuss the benefits (full history) and drawbacks (increased dimension table size, more complex queries for 'current' view).

Where people lose the point

• ×Confusing SCDs with regular dimension updates.
• ×Incorrectly describing the mechanism of Type 2 SCD (e.g., just updating the existing row).
• ×Not explaining the purpose of the additional columns (start_date, end_date, current_flag).

What a strong answer covers

• Define schema evolution as the process of modifying the structure of a database over time to accommodate new requirements.
• Challenges in relational databases: rigid schema, ALTER TABLE operations can be slow/disruptive for large tables, requires careful planning and downtime.
• Challenges in NoSQL (e.g., document databases): flexible schema allows easier addition of new fields, but 'schema-on-read' can lead to inconsistent data or application-level complexity.
• Discuss strategies: versioning schemas, using migration scripts, backward/forward compatibility, 'big bang' vs. incremental changes.
• Highlight the trade-off between flexibility (NoSQL) and strong consistency/guarantees (relational) in managing schema changes.

Where people lose the point

• ×Only focusing on one database type without contrasting.
• ×Not discussing the operational impact (downtime, performance) of schema changes.
• ×Failing to mention strategies or best practices for managing evolution.

What a strong answer covers

• Define data integrity as the overall completeness, accuracy, and consistency of data throughout its lifecycle.
• Explain its importance: reliable data for decision-making, preventing errors, maintaining trust.
• Discuss Entity Integrity: ensured by primary keys (unique, non-null).
• Discuss Referential Integrity: ensured by foreign keys (links to valid primary keys, prevents orphaned records).
• Discuss Domain Integrity: ensured by data types, constraints (CHECK, NOT NULL), and business rules.

Where people lose the point

• ×Only mentioning one type of integrity (e.g., just referential) without a broader definition.
• ×Not linking specific database mechanisms (PK, FK, constraints) to the types of integrity they enforce.
• ×Failing to explain *why* data integrity is important.

What a strong answer covers

• Understand business requirements: identify key performance indicators (KPIs), analytical needs, and data sources.
• Identify facts and dimensions: determine what measures need to be analyzed (sales, profit) and what contexts describe them (product, customer, time, store).
• Choose a schema type: typically Star or Snowflake schema, justifying the choice based on query patterns and data complexity.
• Design dimension tables: include attributes for filtering and grouping, consider Slowly Changing Dimensions (SCDs).
• Design fact tables: include measures and foreign keys to dimension tables, consider granularity and additive properties of measures.

Where people lose the point

• ×Confusing data warehouse modeling with OLTP modeling (e.g., focusing on high normalization).
• ×Not explicitly mentioning facts and dimensions as core components.
• ×Failing to discuss the importance of business requirements and analytical needs.