When would you choose a Snowflake schema over a Star schema?

Star: One fact, denormalized dimensions—simple, fewer joins, fast. Snowflake: Normalized dimensions (e.g., dim_product → dim_category → dim_category_group)—more joins, less redundancy. Why Snowflake: When dimension tables are large and shared—a single dim_category serves many products; denormalizing would duplicate millions of rows. Avoids inconsistent attributes across copies (e.g., category name updated in one place). Storage: Snowflake saves space when hierarchy is deep and wide. Trade-off: More joins = more complexity and potential perf hit in some engines. When Star wins: Query latency paramount, dimensions small enough to broadcast, denormalization acceptable. Cost: Snowflake reduces storage; Star reduces compute (fewer joins). Best practice: Start Star; normalize only dimensions that are large or change independently.