In enterprise BI design, are data warehouse datasets usually normalized to higher normal forms, or are they typically denormalized (e.g., star schemas) for faster analytics?

Introduction / Context:
Data warehouses aim to speed up analytical queries. Denormalized schemas (star/snowflake) reduce complex joins and align with how analysts think about facts and dimensions. This contrasts with OLTP normalization, which prioritizes update integrity and minimal redundancy for transactions.

Given Data / Assumptions:

• Analytical queries scan large volumes and aggregate across dimensions.
• Columnar storage and bitmap/star-join optimizations are common.
• Dimensional modeling emphasizes usability and performance.

Concept / Approach:
Fact tables store measurements (e.g., sales_amount), while dimension tables store descriptive attributes (e.g., product_category). Denormalization minimizes join chains and improves query performance. Snowflaking may partially normalize dimensions but overall remains analysis-oriented.

Step-by-Step Solution:
Design conformed dimensions shared across facts.Model facts at appropriate granularity.Apply surrogate keys for stability and history (SCDs).Leverage columnstore indexes/partitions for speed.

Verification / Alternative check:
Compare query plans on a star schema vs. fully normalized 3NF; the star schema usually yields simpler and faster execution for analytics.

Why Other Options Are Wrong:
Strict 3NF hinders analytical speed (option b). Databases do not store spreadsheets as native warehouse tables (option c). Normalization by datatype (option d) is nonsensical. Temporary normalization during ETL (option e) misunderstands that denormalization is a design choice, not just a query-time trick.

Common Pitfalls:
Over-normalizing warehouses; mixing OLTP and OLAP workloads; ignoring conformed dimensions leading to inconsistent metrics.

Final Answer:
Typically denormalized (star/snowflake) for analytics