In dimensional modeling, what is the difference between a star schema and a snowflake schema in terms of structure and normalization?

Introduction / Context:
Star and snowflake schemas are the two most common schema patterns used in dimensional data warehouses. Understanding their differences is important for designing performant and maintainable BI solutions. Interviewers like this question because it reveals how you think about normalization, query simplicity, and trade offs between storage efficiency and ease of use.

Given Data / Assumptions:

• We are working with a dimensional model that includes fact and dimension tables.
• Fact tables contain business measures and foreign keys to dimensions.
• Dimensions can be designed in more or less normalized forms.
• End users query the data warehouse for reporting and analysis.

Concept / Approach:
In a star schema, each dimension is stored in a single, denormalized table that directly joins to the fact table. This results in a simple, star shaped diagram with one central fact and several surrounding dimensions. In a snowflake schema, one or more dimensions are normalized into multiple related tables. For example, a Product dimension might be split into Product, Brand, and Category tables. This saves storage and can enforce more consistent data, but it increases join complexity for queries.

Step-by-Step Solution:
Step 1: Describe a star schema as one where the fact table sits at the center and each dimension is a single table connected directly by foreign keys. Step 2: Highlight that star schemas usually denormalize dimension attributes into one table, trading some redundancy for simpler queries. Step 3: Describe a snowflake schema as an extension where dimensions are normalized into multiple related tables, such as splitting geography into City, State, and Country tables. Step 4: Explain that this normalization reduces redundancy and may slightly improve data maintenance but adds more joins for each query. Step 5: Summarize that star schemas favor query performance and simplicity, while snowflake schemas favor storage efficiency and normalization.

Verification / Alternative check:
If you compare actual data warehouse diagrams, a star schema for sales might show Fact_Sales joined to Dim_Date, Dim_Product, Dim_Customer, and Dim_Store. A snowflake version of the same model would break Dim_Product into additional tables like Dim_Brand or Dim_Category and break Dim_Store into Dim_City and Dim_Region. Query plans for a star schema typically show fewer joins than the equivalent snowflake schema, illustrating the practical trade offs.

Why Other Options Are Wrong:
Option B incorrectly ties star and snowflake schemas to storage technologies like flat files or NoSQL databases, which is unrelated. Option C mislabels star schemas as OLTP designs and snowflake schemas as backup structures, which is false. Option D claims there is no structural difference, ignoring the clear normalization and join pattern differences between the two approaches.

Common Pitfalls:
A common mistake is to over normalize dimensions into complex snowflakes, making queries and BI tools harder to manage. Another pitfall is using only star schemas without considering that some limited snowflaking might simplify maintenance for shared hierarchies. Good designers choose a hybrid that keeps user facing models simple while avoiding extreme redundancy in dimensions.

Final Answer:
A star schema uses single, denormalized dimension tables directly linked to a central fact table, while a snowflake schema normalizes some dimensions into multiple related tables, creating a more branched structure with extra joins.