0

Why is the snowflake schema normalized, even though the data warehouse has the property of being denormalized? I am learning about data warehouse technologies, can anyone elaborate on this for me? I apologize if I am missing something here, I am open to suggestions. :)

Thanks!

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
Zuffido
  • 104
  • 1
  • 11
  • Why do you think data warehouse schemas are denormalized? That’s not really a constructive way to think about it. – David Browne - Microsoft Apr 19 '22 at 00:27
  • 1
    When you build a database schema, you go through a data modelling exercise, and during that exercise the data modeller may decide that an appropriate design is to implement a snowflake schema instead of a star schema (... or a standard OLTP schema .... or a data vault schema.... or a EAV schema). Data is modelled to fit requirements and constraints. A snowflake schema might fit those. This gentleman has some interesting and amusing reads about data modelling and the mistakes that can be made https://www.red-gate.com/simple-talk/blogs/when-the-fever-is-over-and-ones-work-is-done/ – Nick.Mc Apr 19 '22 at 04:34
  • Thanks, @David Browne - Microsoft for the response. I am going through some literature online and mostly it is written that data warehouses are de-normalized to enhance the query processing. – Zuffido Apr 19 '22 at 16:37
  • Thanks, @Nick.McDermaid for your response. I will surely check the link you have provided. – Zuffido Apr 19 '22 at 16:38

1 Answers1

0

Data warehouses may be denormalised but that’s not mandatory, only some kinds of data warehouse modelling use denormalisation as part of its design principles.

For a dimension, a snowflake model describes when a dimension is modelled using normalisation. It could still be part of a data warehouse though.

The kimball approach advises to avoid snowflakes in a data warehouse because it prefers denormalised dimensions.

Rich
  • 2,207
  • 1
  • 23
  • 27