Data Warehouse Schemas Explained: Star, Snowflake, Galaxy

In the world of SQL and data warehousing, organizing and structuring data for efficient querying and reporting is paramount. Three of the most common schema designs used for this purpose are the Star, Snowflake, and Galaxy schemas. As each of these schemas has its pros and cons, it is crucial to have a good understanding of their main characteristics in order to design an optimal and robust data warehouse that meets business requirements. Before we discuss their main differences and use cases, let’s introduce the concepts of Fact and Dimension tables, which are essential for understanding how these schemas work.

💡 Not a Medium subscriber? No worries — I’ve got you covered.
👉 You can read this article for free right here: SpeakingData

Fact and Dimension Tables

As mentioned in one of the previous articles, OLTP and OLAP are two different types of database systems optimized for different purposes and workloads. OLTP is designed to handle and process transactions, while OLAP is optimized for analytical purposes (check this article to learn more about OLTP and OLAP systems). Another crucial difference between the two is the way data is organized in the tables. In OLTP systems, tables might contain both quantifiable fields (e.g., quantities, prices) and…