In data warehousing design, what does the term “snowflake schema” refer to, and which type of tables are being normalized in that schema?

Introduction / Context:
A snowflake schema is a common dimensional modeling pattern used in data warehouses. Understanding exactly which tables are normalized (split into multiple related tables) helps data engineers choose between star and snowflake topologies for performance, storage, and governance.

Given Data / Assumptions:

• We are discussing dimensional models that contain fact tables and dimension tables.
• Snowflake schema is contrasted with star schema.
• We assume standard OLAP/BI query workloads.

Concept / Approach:
In a star schema, each dimension is denormalized into a single wide table linked to a central fact table via foreign keys. In a snowflake schema, the dimension tables are partially or fully normalized into multiple related tables (e.g., Geography split into Country, State, City). The fact table remains a central table of keys and additive or semi-additive measures.

Step-by-Step Solution:

Verification / Alternative check:
Modeling references define snowflaking as decomposing dimension attributes into multiple related tables to reduce redundancy and improve data quality control. Query optimizers can still join these tables efficiently, though performance may be slower than a star due to extra joins.

Why Other Options Are Wrong:
Fact: Fact tables are not “snowflaked”; they already store keys and measures.
Helper: Helper (bridge/junk) tables exist but are not what “snowflake” names.
All of the above: Incorrect because snowflaking is specifically about dimensions.

Common Pitfalls:
Confusing star (denormalized dimensions) with snowflake (normalized dimensions), and assuming snowflake always improves performance. It often improves maintainability but may add join overhead.

Final Answer:
Dimension