Talk:Snowflake schema

	This article is within the scope of WikiProject Databases, a project which is currently considered to be inactive.DatabasesWikipedia:WikiProject DatabasesTemplate:WikiProject DatabasesDatabases articles
B	This article has been rated as B-class on Wikipedia's content assessment scale.

Citation for Examples[edit]

The Citation needed template for the Examples should be removed. It refers to the code and data model in the Star Schema article, which is valid. If prove beyond the model and code is needed, star and snowflage examples should be compared side by side from a data point of view, with actual example data roy<sac> ^Talk! .oOo. 21:12, 14 September 2020 (UTC)[reply]

Old Comments[edit]

the first and fourth paragraphs were directly stolen from this oracle documentation ("snowflake schemas section"): http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96520/schemas.htm#12915

so I'm going to remove them

This copyvio was the initial start of the page, so is in all older versions of the page. Maybe the page should be deleted and rewritten from scratch? In particular since without the original copyvio information, the current version of the page is not really understandable to a non-expert. -- S.K. 17:14, 2 March 2006 (UTC)[reply]

It's been rewritten since these comments. Just wanted to get these comments out of the talk page heading area. SqlPac (talk) 07:20, 5 December 2009 (UTC)[reply]

First and last paragraphs deleted on January 13,2006 be brought back[edit]

Hello!

I am the one who wrote the first and the last paragraphs of the article on Snowflake schemas which have been deleted. You have my assurance that the resembelence of the first and the last paragraphs is purely coincidental. I work on SQL Server data warehouses day in and day out. Those lines were a result of my own understanding on the same. Just because a certain resembelence was found doesn't mean that those were copied. Also, you cannot say something is "Copied" or that someone is a "thief" without any proof.

I demand that those lines be brought back into the article, else I will do that myself.

Also, tell me, in case you find something similar to other articles on the web (say for eg. A typical "Hello World!" program), do you keep deleting the stuff? That way, everything should be scrapped off, isn't it?

-Nakul

Mate, it's not a matter of a certain `resembelence' (whatever that is) between your supposed writing and something else. Those two paragraphs are word-by-word copies of the Oracle documentation (19: Schema Modelling Techniques). What more evidence is needed 118.92.75.85 (talk)

Some cleanup and context as requested[edit]

Some sources regard snowflake and star schema a separate ideas, others that snowflake is a variation on star. I've allowed for either interpretation
Added a quick definition of multidimensionality
Put the nub of the difference between star and snowflake schemas towards the top of the article
Replaced "Relational databases consisting of a single fact table with a compound candidate key, with segments for each "dimension" and with additional columns of additive, numeric facts." with the term star schema, since this is the definition of a star schema and the technical terms "compound key" and "candidate key" are not otherwise required.
removed the paragraph containing the PoV statement "few business queries that management likes to run"
Added reasons why snowflaking might be a good idea
reordered some of the paragraphs so that the explanation of a term was before or immediately after its first usage
made the point that users might use star or snowflake schemas to form a query, regardless of the physical storeage model

Allandean 09:14, 6 September 2006 (UTC)[reply]

Have made changes in the introductory paragraphs for better readability. Comments are invited for further improvements.

Seeming contradiction (to non-expert) in snowflake and star articles[edit]

If I read this correctly, the snowflake schema article says there will always be one fact table, while the star schema article says that there will often be multiple fact tables, and that when there is a single fact that this makes the star a star. I don't know which is accurate, but to a lay reader this seems a contradiction and is confusing. I'd encourage an expert on the subject to compare these two statements (near the top of each article) and amend for consistency. -Jeff Jebbo (talk) 15:37, 14 May 2008 (UTC)[reply]

I think the number of fact tables is irrelevant. The difference between the star schema and the snowflake schema is whether or not you normalize your dimensions. Consider a Date (time) dimension. In a star schema you might have one denormalized table with the following columns: (Id, Date, Day, Month, MonthName, Year, Quarter). In a snowflake schema you might have a more normalized version, with the main dimension table holding the following: (Id, Date, Day, Month_ID, Year, Quarter_ID). The Month_ID and Quarter_ID columns would act as foreign keys referencing Month and Quarter dimension tables separately. In this way you have a more normalized dimension that "snowflakes" out. SqlPac (talk) 18:47, 27 June 2008 (UTC)[reply]

Added image[edit]

Added a simple image of a snowflake schema. SqlPac (talk) 17:47, 27 June 2008 (UTC)[reply]

Code/Table sample[edit]

Perhaps we could add a SQL code sample and a simple ERD to demonstrate a snowflake schema? I think it would be a good idea to coordinate the example with the star schema article, to show a snowflake schema version of the same example given over there. SqlPac (talk) 20:33, 27 June 2008 (UTC)[reply]

Is the join on Store_ID required in the sample given? It looks redundant to me. Nimmirag (talk) 08:31, 26 July 2008 (UTC)[reply]

It is required to get to the Geography dimension, from which Country is retrieved, so no I don't believe it's redundant. SqlPac (talk) 15:48, 30 July 2008 (UTC)[reply]

NB This is my first attempt at contributing on wikipedia - apologies in advance for any mistakes or discourtesies. In the star schema example only Dim_Geography illustrates normalisation (decomposition of functionally related attributes into a separate entity). The other points on the star illustrate substituting text values with numeric values, which is not illustrative of normalisation (despite parts of this article suggesting that normalisation is a process undertaken to reduce disk footprint). I appreciate that this is a working example expanding upon the star schema on another page therefore your hands are tied somewhat CorneliusWiki (talk) 14:22, 25 July 2019 (UTC)[reply]

Counterexample needed[edit]

According to the OLAP article, there is some performance benefit to OLAP using star schemas or snowflake schemas, over other relational schemas. However, I see only examples of star schemas and snowflake schemas, but no contrasting "non-snowflake" schema. It seems to me that just about any garden-variety 3NF database schema is already going to be a snowflake schema. Is it possible to design a relational schema that's not a snowflake? What are we trying to improve upon? I can see that not all schemas are star schemas (and the performance benefits of star schemas), but the snowflake schema seems to be so general as to be meaningless. — Preceding unsigned comment added by Qseep (talk • contribs) 23:34, 9 February 2011 (UTC)[reply]

Absolutely concur. I came here to learn whats new in star/snowflake that is not already in our regular OLTP/Normalized schemas, and learned nothing. All it says is that snowflke is a normalized star. Please compare and contrast this with a standard or "naive" relational schema, to show how its superior. This comment also applies to the star schema article.76.100.60.110 (talk) 23:12, 9 November 2012 (UTC)[reply]

Normalization in 2nd paragraph[edit]

In the 2nd paragraph,:

"whereas the star schema's dimensions are normalized"

not read

"whereas the star schema's dimensions are denormalized"

? Jimmyjudas (talk) 16:38, 3 January 2013 (UTC)[reply]

Denormalization in the Disadvantages section[edit]

After all the explanations in the rest of the articles of how the snowflake schema is more normalized than the star schema, there is a sudden mention of the snowflake's denormalization:

When compared to a highly normalized transactional schema, the snowflake schema's denormalization removes the data integrity assurances provided by normalized schemas.

What denormalization is this talking about? — Preceding unsigned comment added by 76.109.182.7 (talk) 23:38, 27 February 2014 (UTC)[reply]

It's just wrong. Remove it. 80.254.147.116 (talk) 11:46, 29 August 2014 (UTC)[reply]

I agree, remove it roy<sac> ^Talk! .oOo. 21:09, 14 September 2020 (UTC)[reply]

snowflake schema comply to 3rd normal form[edit]

Under "Disadvantage" section, the wikipage states that "When compared to a highly normalized transactional schema, the snowflake schema's denormalization removes the data integrity assurances provided by normalized schemas."

On the contrary, snowflake schema comply to 3rd normal form (i.e. not demoralized), as stated in: http://pic.dhe.ibm.com/infocenter/idm/docv3/index.jsp?topic=%2Fcom.ibm.datatools.dimensional.ui.doc%2Ftopics%2Fc_dm_snowflake_schemas.html — Preceding unsigned comment added by 202.40.171.233 (talk) 07:56, 12 August 2014 (UTC)[reply]