Explain the situations where snowflake is better than star schema
A snowflake schema is a way to handle problems that do not fit within the star schema. It consists of outrigger tables which relate to dimensions rather than to the fact table.
The amount of space taken up by dimensions is so small compared to the space required for a fact table as to be insignificant. Therefore, tablespace or disk space is not a considered a reason to create a snowflake schema.
The main reason for creating a snowflake is to make it simpler and faster for a report writer to create drop down boxes. Rather than having to write a select distinct statement, they can simply select * from the code table.
Junk dimensions and mini dimensions are another reason to create add outriggers. The junk dimensions contain data from a normal dimension that you wish to separate out, such as fields that change quickly. Updates are so slow that they can add hours to the load process. With a junk dimension, it is possible to drop and add records rather than update.
Mini dimensions contain data that is so dissimilar between two or more source systems that would cause a very sparse main dimension. The conformed data that can be obtained from all source systems is contained in the parent dimension and the data from each source system that does not match is contained in the child dimension.
Finally, if you are unlucky enough to have end users actually adding or updating data to the data warehouse rather than just batch loads, it may be necessary to add these outriggers to maintain referential integrity in the data being loaded.