Question

    In a snowflake schema of a data warehouse, which of the

    following consequences is most likely as a result of normalization of dimension tables?
    A Increased query performance due to flat dimension structure Correct Answer Incorrect Answer
    B Decreased storage space usage and reduced data redundancy Correct Answer Incorrect Answer
    C Elimination of need for joins between fact and dimension tables Correct Answer Incorrect Answer
    D Ability to use only one dimension table per fact table Correct Answer Incorrect Answer
    E Dimension tables become denormalized for faster data access Correct Answer Incorrect Answer

    Solution

    A snowflake schema is a variation of a star schema in which the dimension tables are normalized (broken down into related tables). This: • Reduces redundancy (e.g., separating country, state, and city into different tables). • Leads to better storage efficiency. • However, it requires more joins in queries, which can reduce query performance compared to a star schema. • Option A is incorrect because normalization typically increases the number of joins, decreasing performance. • Option C is false; more joins are needed in snowflake schemas. • Option D is incorrect; multiple dimension tables can be used. • Option E is the opposite of what a snowflake schema does (it normalizes, not denormalizes, dimension tables).

    Practice Next