In dimensional modeling, how are fact tables typically structured with respect to normalization in a data warehouse?

Difficulty: Easy

Correct Answer: Completely normalized

Explanation:


Introduction / Context:
Fact tables are the core of dimensional models, storing numeric measures and foreign keys to dimensions. Knowing their normalization level clarifies how they support fast aggregation and consistent joins.



Given Data / Assumptions:

  • A fact table contains foreign keys to dimensions and numeric facts (measures like sales_amount, quantity).
  • Dimensions may be denormalized (star) or normalized (snowflake).
  • We consider standard data warehouse practices.


Concept / Approach:
A fact table typically follows normalization rules: it stores keys and measures without repeating dimension attributes. Each row corresponds to a grain (e.g., one line item per order-day-product). There is no duplication of descriptive attributes; those live in dimensions. Thus, fact tables are effectively in a fully normalized structure relative to descriptive data.



Step-by-Step Solution:

Check for descriptive attributes inside fact: best practice avoids them.Confirm all descriptive text lives in dimension tables to prevent anomalies.Therefore, fact tables are “completely normalized” regarding descriptive data.


Verification / Alternative check:
Kimball-style design describes facts as narrow, key-and-measure driven tables. Any denormalization tends to occur in dimensions to minimize joins, not in facts.



Why Other Options Are Wrong:
Completely/Partially denormalized: Denormalization applies mostly to dimensions.
Partially normalized: Suggests mixed practice, not standard for facts whose design is straightforward.



Common Pitfalls:
Placing descriptive columns (e.g., product_name) directly into the fact table; this inflates size and introduces update anomalies.



Final Answer:
Completely normalized

Discussion & Comments

No comments yet. Be the first to comment!
Join Discussion