Top 50 Data Modelling Interview Questions & Answers

1. What is Data Modelling?

Data Modelling is the process of creating a data model to visually represent data structures and how data is stored, connected, accessed, and updated in a database. It helps in designing the database at the conceptual, logical, and physical levels. Data models ensure consistency and quality in data usage across applications.

2. What are the types of Data Models?

The main types of data models include:

Conceptual Data Model
Logical Data Model
Physical Data Model

Each model has its purpose and level of detail, starting from high-level business requirements to actual database design.

3. What is the difference between Logical and Physical Data Model?

Logical Data Model represents the structure of the data elements and their relationships without considering how they will be implemented physically. Physical Data Model, on the other hand, includes the database-specific implementations like tables, columns, data types, indexes, and constraints.

Logical models are platform-independent, while physical models are platform-specific.

4. What is normalization?

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing large tables into smaller ones and defining relationships between them. The goal is to eliminate data anomalies and ensure consistent data.

5. What are the types of normalization?

The different normal forms include:

1NF (First Normal Form)
2NF (Second Normal Form)
3NF (Third Normal Form)
BCNF (Boyce-Codd Normal Form)

Each higher normal form addresses specific kinds of redundancy and anomalies present in the lower forms.

6. What is Denormalization?

Denormalization is the process of combining normalized tables into larger tables to improve read performance. It’s the reverse of normalization. This may lead to redundancy but helps in optimizing complex queries and reducing joins in a relational database.

7. What are Entities?

Entities represent real-world objects or concepts that can have data stored about them. For example, a customer or product can be considered an entity. In data modeling, entities become tables, and attributes become fields in those tables.

8. What is an Attribute?

An attribute is a property or characteristic of an entity. For example, for an entity “Employee,” attributes can be EmployeeID, Name, Department, etc. Attributes define the data that can be collected and stored for each entity.

9. What is a Primary Key?

A primary key is a unique identifier for a record in a table. It ensures that each record in the table can be uniquely identified and cannot contain NULL values. It plays a critical role in maintaining entity integrity.

10. What is a Foreign Key?

A foreign key is a field in one table that uniquely identifies a row of another table. It creates a link between the two tables and enforces referential integrity. Foreign keys help maintain consistent data across related tables.

11. What is a Composite Key?

A composite key consists of two or more columns that together uniquely identify a record. It is used when no single column is sufficient to uniquely identify rows in a table. Composite keys are common in many-to-many relationships.

12. What is a Candidate Key?

Candidate keys are fields that are eligible to be chosen as the primary key. Each candidate key can uniquely identify a record in the table. One of the candidate keys is selected as the primary key, while others can serve as alternate keys.

13. What is an Alternate Key?

An alternate key is any candidate key that is not chosen as the primary key. It can still uniquely identify a record and is often used for enforcing additional constraints on data.

14. What is an Entity-Relationship Diagram (ERD)?

An ERD is a visual representation of entities and their relationships. It helps in designing databases by illustrating how entities interact with each other. ERDs are commonly used in conceptual and logical data modeling phases.

15. What is a Relationship in Data Modeling?

A relationship defines how entities are related to each other. There are three types of relationships: one-to-one, one-to-many, and many-to-many. Relationships are represented using foreign keys and are shown in ER diagrams.

16. What is Cardinality?

Cardinality refers to the number of instances of one entity that can be associated with the number of instances of another entity. Common cardinalities include one-to-one, one-to-many, and many-to-many. It plays a critical role in defining data relationships.

17. What is an Associative Entity?

An associative entity is used to represent a many-to-many relationship between two entities. It contains foreign keys referencing the primary keys of related entities. It may also include additional attributes relevant to the relationship.

18. What is Data Integrity?

Data integrity ensures the accuracy and consistency of data within a database. It is enforced using constraints like primary keys, foreign keys, and check constraints. Maintaining integrity prevents anomalies and ensures reliable data processing.

19. What is a Surrogate Key?

A surrogate key is a system-generated unique identifier used as a primary key. It has no business meaning and is often used in place of natural or composite keys. Surrogate keys improve performance and simplify data management.

20. What is the difference between OLTP and OLAP?

OLTP (Online Transaction Processing) systems are used for day-to-day transactional data and support insert, update, delete operations. OLAP (Online Analytical Processing) systems are used for complex queries and data analysis. OLTP focuses on speed and efficiency, whereas OLAP is optimized for read-heavy operations.

21. What is Dimensional Data Modeling?

Dimensional Data Modeling is used in data warehouses and focuses on easy retrieval of data. It consists of fact tables and dimension tables. This model supports analytical processing by organizing data into measurable facts and descriptive dimensions.

22. What is a Fact Table?

A fact table contains the measurable, quantitative data for analysis. It typically includes foreign keys to dimension tables and numeric metrics. Fact tables are central in a star or snowflake schema in a data warehouse.

23. What is a Dimension Table?

A dimension table contains descriptive attributes related to dimensions of the fact data. Examples include time, location, product, etc. These tables help in slicing and dicing the data for reports and analysis.

24. What is a Star Schema?

Star Schema is a type of database schema that consists of a central fact table connected to multiple dimension tables. It is simple, intuitive, and improves query performance. Star schemas are widely used in data warehousing systems.

25. What is a Snowflake Schema?

Snowflake Schema is a normalized version of the star schema where dimension tables are broken into sub-dimensions. It reduces data redundancy but can lead to complex queries. It is useful in large data warehouses with complex hierarchies.

26. What is Slowly Changing Dimension (SCD)?

Slowly Changing Dimensions (SCD) manage changes in dimension data over time. Common types include Type 1 (overwrite), Type 2 (create new row), and Type 3 (track limited history). It is essential in data warehouses to preserve historical accuracy. SCDs help in tracking the evolution of business entities like customers or products.

27. What is a Junk Dimension?

A junk dimension combines unrelated attributes into a single dimension table. These attributes are usually small flags or indicators. It helps in reducing clutter in the data model and avoids creating many small dimension tables. Junk dimensions are common in dimensional modeling for handling miscellaneous data.

28. What is a Conformed Dimension?

Conformed dimensions are shared across multiple fact tables or data marts. They ensure consistency in reporting and analysis across different business processes. A common example is a shared “Date” or “Customer” dimension. They play a crucial role in enterprise data warehousing.

29. What is a Degenerate Dimension?

A degenerate dimension is a dimension key stored in the fact table without a corresponding dimension table. It often contains invoice numbers, order IDs, or transaction codes. These are useful when the attribute doesn’t have descriptive information. Degenerate dimensions are used for operational reporting.

30. What is Data Mart?

A data mart is a subset of a data warehouse, focused on a specific business area like sales or finance. It allows faster access to data for analysis by department-specific users. Data marts can be dependent, independent, or hybrid. They improve performance and reduce complexity for end-users.

31. What is ER Modeling?

ER (Entity-Relationship) Modeling is a conceptual data modeling technique that visually describes entities and their relationships. It uses ER diagrams that include entities, attributes, and cardinalities. It is widely used in designing relational databases. ER models are the foundation for logical and physical models.

32. What is the difference between Star and Snowflake Schema?

Star schema has denormalized dimensions connected directly to the fact table. Snowflake schema normalizes dimensions into sub-dimensions. Star schema offers faster query performance, while snowflake reduces redundancy. The choice depends on complexity and storage needs.

33. What is Data Vault Modeling?

Data Vault is a data modeling methodology focused on long-term historical storage of data. It consists of Hubs, Links, and Satellites. It supports agility, auditability, and scalability in modern data warehouses. Data Vault is preferred in big data and agile environments.

34. What is Anchor Modeling?

Anchor Modeling is a modeling technique suited for agile data warehouses. It is highly normalized and flexible for evolving requirements. Entities (anchors) are split into attributes and relationships (ties). Anchor modeling provides historical tracking and schema evolution support.

35. What is Normal Form?

Normal forms are rules for structuring relational databases to reduce redundancy and anomalies. There are several normal forms: 1NF to 5NF and BCNF. Each successive form addresses deeper normalization. Applying normal forms ensures data integrity and efficient updates.

36. What is Functional Dependency?

Functional dependency describes a relationship where one attribute uniquely determines another. Denoted as A → B, meaning B is functionally dependent on A. It is fundamental in normalization. Identifying functional dependencies is key to eliminating redundancy.

37. What is Referential Integrity?

Referential integrity ensures that foreign keys correctly reference primary keys in related tables. It prevents orphan records and maintains consistency across relationships. Database systems enforce it using constraints. It is critical in maintaining relational database accuracy.

38. What is Indexing in Data Modeling?

Indexing improves data retrieval speed by creating a quick lookup structure. Indexes are built on one or more columns. They can be clustered or non-clustered. Proper indexing is crucial for performance optimization in large datasets.

39. What is a Lookup Table?

A lookup table stores a set of predefined values used to validate or reference other table data. It improves consistency and reduces errors. Often used for dropdown values like status or category. Lookup tables can be considered mini dimension tables.

40. What is a Hierarchical Relationship?

A hierarchical relationship organizes data in a tree-like structure, where each child has one parent. Common in organization charts or product categories. These are modeled using parent-child relationships in tables. Hierarchies are useful for drill-down analysis.

41. What is a Recursive Relationship?

A recursive relationship is when an entity is related to itself. For example, an employee managing another employee. It requires self-referencing foreign keys. Recursive relationships are used in organizational or hierarchical data modeling.

42. What is a Data Dictionary?

A data dictionary contains metadata about data elements in a database. It includes details like data type, length, constraints, and relationships. It serves as a reference for developers and analysts. Data dictionaries improve documentation and data governance.

43. What is a Domain in Data Modeling?

A domain defines the valid set of values for a data element. For example, gender can be restricted to ‘M’ or ‘F’. Domains ensure data consistency and validation. They are often implemented using constraints or reference tables.

44. What is a Subtype and Supertype?

Supertype is a generic entity that has common attributes. Subtypes inherit attributes from the supertype but add their own. Used to model inheritance or category hierarchies. They help in reducing duplication and improving clarity in complex models.

45. What is an Index in a Table?

An index is a database object that speeds up data retrieval operations. It creates a data structure that maps key values to row locations. Proper indexing improves query performance but may slow down inserts/updates. Indexes are critical for read-heavy systems.

46. What is a Metadata?

Metadata is data about data. It describes properties, structure, and constraints of data elements. Examples include table names, column data types, and descriptions. Metadata is essential for data management and lineage tracking.

47. What is Master Data?

Master Data refers to the core entities used across business processes like Customers, Products, and Employees. It is consistent and rarely changes. Maintaining high-quality master data is crucial for analytics and operations. It forms the backbone of business transactions.

48. What is Transactional Data?

Transactional data records day-to-day business operations such as sales, orders, and payments. It is dynamic and time-variant. Used in OLTP systems and stored in fact tables. Transactional data supports business process tracking and reporting.

49. What is a Semantic Layer?

A semantic layer provides a business-friendly view of data for end users. It hides technical complexities and offers easy-to-understand terms. Used in BI tools to enable self-service reporting. It improves usability and data accessibility.

50. What is Data Lineage?

Data lineage tracks the origin, movement, and transformation of data across systems. It helps in auditing, debugging, and impact analysis. Visual tools are used to represent data flow from source to destination. Data lineage ensures trust and transparency in analytics.

Request more information

Top 50 Data Modelling Interview Questions & Answers