Data Modeling Interview Questions and Answers
Ques 26. What is the difference between a snowflake schema and a star schema?
While a star schema has a centralized fact table connected to dimension tables, a snowflake schema takes normalization further by breaking down dimension tables into sub-dimensions, forming a more normalized structure.
Example:
Example: In a snowflake schema, the 'Location' dimension may be normalized into 'City,' 'State,' and 'Country' tables.
Ques 27. How do you handle historical data in a data warehouse?
Handling historical data involves using slowly changing dimensions (SCDs), where changes to data over time are tracked. SCD types include Type 1 (overwrite), Type 2 (add new row), and Type 3 (add columns).
Example:
Example: Using Type 2 SCD to add a new row for an employee when their department changes over time.
Ques 28. What is a fact table, and how is it different from a dimension table?
A fact table contains quantitative data, such as sales or revenue, and is surrounded by dimension tables that provide context to the data. Dimension tables describe the who, what, where, when aspects of the facts.
Example:
Example: In a retail data warehouse, a 'Sales' fact table might include 'ProductID,' 'CustomerID,' 'DateID,' and 'SalesAmount.'
Ques 29. What is data governance, and why is it important in data modeling?
Data governance involves managing the availability, usability, integrity, and security of data within an organization. It is crucial in data modeling to ensure consistency, quality, and compliance with standards.
Example:
Example: Establishing policies and procedures for data quality checks and access control within a data model.
Ques 30. Explain the concept of database normalization.
Database normalization is the process of organizing data to minimize redundancy and dependency by dividing tables into smaller, related tables. Normal forms (1NF, 2NF, 3NF, BCNF) guide this process.
Example:
Example: Breaking down a 'Customer' table into 'Customer' and 'Address' tables to eliminate duplicate address information.
Most helpful rated by users: