--- description: Snowflake Schema in data warehouse is a logical arrangement of tables in a multidimensional database such that the ER diagram resembles a snowflake shape. title: Snowflake Schema in Data Warehouse Model image: https://www.guru99.com/images/snowflake-schema-in-data-warehouse.png --- [Skip to content](#main) **โšก Smart Summary** Snowflake Schema in Data Warehouse modeling arranges normalized dimension tables that branch from a central fact table, resembling a snowflake. It extends the star schema, reduces data redundancy, and organizes hierarchies across multiple related lookup tables. * ๐Ÿงฉ **Core Structure:** A central fact table connects to dimension tables that are normalized into further sub-dimension and lookup tables. * โ„๏ธ **Normalization:** Splitting each dimension into related tables removes repeating attributes and pushes hierarchies toward third normal form. * ๐ŸŒŸ **Relation to Star Schema:** The snowflake schema extends the star schema by normalizing its flat, denormalized dimension tables. * ๐Ÿ’พ **Storage Benefit:** Smaller normalized lookup tables lower disk usage and eliminate redundant data, which eases maintenance. * ๐Ÿ”— **Query Trade-off:** More tables mean more joins, which can slow query performance and complicate reporting. * ๐Ÿงญ **When to Use:** Choose it for large dimensions with deep hierarchies where storage savings and data integrity matter most. * ๐Ÿช **Related Schemas:** Galaxy and star cluster designs build on star and snowflake concepts for more complex models. [ Read More ](javascript:void%280%29;) ![Snowflake Schema in Data Warehouse with normalized dimension tables branching from a central fact table](https://www.guru99.com/images/snowflake-schema-in-data-warehouse.png) ## What is a Snowflake Schema? A **Snowflake Schema** in a data warehouse is a logical arrangement of tables in a multidimensional database whose [entity relationship (ER) diagram](https://www.guru99.com/er-diagram-tutorial-dbms.html) resembles the shape of a snowflake. It is a [dimensional model](https://www.guru99.com/dimensional-model-data-warehouse.html) in which a central fact table links to dimension tables, and those dimension tables are further divided into related sub-dimension tables. The snowflake schema is an extension of the star schema. While a star schema keeps each dimension in a single flat table, the snowflake schema normalizes those dimensions, splitting repeating groups of data into additional lookup tables. This normalization removes redundancy and creates the branching, hierarchical structure that gives the schema its name. ## Snowflake Schema Example In the following snowflake schema example, a Sales fact table sits at the center, surrounded by dimensions such as Product, Date, and Store. Instead of storing every attribute inside one dimension table, the geography information is normalized so that Country is moved into its own separate table. [![Example of a snowflake schema with a central fact table and normalized Country dimension table](https://www.guru99.com/images/1/022218_0758_StarandSnow2.png)](https://www.guru99.com/images/1/022218%5F0758%5FStarandSnow2.png) Example of Snowflake Schema Here, the Store dimension references a City table, the City table references a State table, and the State table references a Country table. Each value is stored only once and linked by a foreign key, so a country name is never repeated across millions of rows. This layered normalization is what distinguishes a snowflake schema from a flat star schema. ## Characteristics of Snowflake Schema The snowflake schema has several defining characteristics: * It uses smaller disk space, because normalized dimension tables avoid storing repeated values. * New dimensions can be added to the schema with relatively little effort. * Query performance can drop, because retrieving data requires joining many tables. * It needs more maintenance effort, since a larger number of lookup tables must be managed. ## How to Design a Snowflake Schema Designing a snowflake schema starts the same way as any dimensional model and then adds a normalization step. The aim is to identify the business process you want to analyze, model it first as a star schema, and then normalize the dimensions that contain deep hierarchies. Work through the following steps: 1. **Identify the business process and grain.** Decide what a single row of the fact table represents, such as one sales transaction, and define the numeric measures, or facts, that you need to report on. 2. **Build the central fact table.** Add the numeric measures along with the foreign keys that point to each dimension; together those foreign keys usually form the composite primary key. 3. **Define the dimension tables.** Create one table for each descriptive dimension, such as Product, Customer, Date, and Store, and assign each a surrogate primary key. 4. **Normalize the hierarchies.** Split every dimension that holds repeating attributes into sub-dimension tables, for example moving Category out of Product, or City, State, and Country out of a Store dimension. 5. **Connect the tables with foreign keys.** Link each sub-dimension back to its parent table so the branches form clear one-to-many hierarchies that resemble a snowflake. 6. **Validate and test with queries.** Run representative reporting queries to confirm that the joins return correct results and that overall performance stays acceptable. Because the design normalizes data toward third normal form, document the join paths clearly so that analysts understand how to navigate each branch. With the structure defined, it is worth weighing the schemaโ€™s benefits against its costs. ### RELATED ARTICLES * [What is Data Mart in Data Warehouse? Types & Example ](https://www.guru99.com/data-mart-tutorial.html "What is Data Mart in Data Warehouse? Types & Example") * [12 BEST ETL Tools in 2026 ](https://www.guru99.com/best-etl-tools.html "12 BEST ETL Tools in 2026") * [What is Star Schema in Data Warehouse modeling? ](https://www.guru99.com/star-schema-in-data-warehouse-modeling.html "What is Star Schema in Data Warehouse modeling?") * [Top 50 DB2 Interview Questions and Answers (2026) ](https://www.guru99.com/db2-interview-questions.html "Top 50 DB2 Interview Questions and Answers (2026)") ## Advantages of Snowflake Schema The snowflake schema offers a number of benefits: * Its primary advantage is reduced disk storage, because joining smaller normalized lookup tables avoids duplicating dimension data. * It provides greater scalability in the relationships between components and dimension levels. * It removes redundancy, which improves data integrity and makes the model easier to maintain. * A descriptive attribute is updated in only one place, which lowers the risk of inconsistent data. ## Disadvantages of Snowflake Schema The design also comes with trade-offs to consider: * The normalized structure increases the maintenance required to manage many related tables. * Complex queries that span multiple joins can be difficult to write and understand. * A larger number of tables means more joins, which lengthens query execution time. * Business users often find the branching model harder to navigate than a simple star schema. ## Snowflake Schema vs Star Schema The snowflake schema and the [star schema](https://www.guru99.com/star-schema-in-data-warehouse-modeling.html) are the two most common multidimensional designs in data warehousing, and the key difference between them is normalization. A star schema keeps each dimension in a single flat, denormalized table for maximum query speed, whereas a snowflake schema normalizes those dimensions into several related tables to save storage and protect data integrity. Because of this, the two schemas suit different priorities. | Aspect | Star Schema | Snowflake Schema | | ----------------- | ------------------------------------- | ------------------------------------ | | Dimension tables | Denormalized, one table per dimension | Normalized into sub-dimension tables | | Storage | Uses more space due to redundancy | Uses less space, no redundancy | | Query performance | Faster, fewer joins | Slower, more joins | | Query complexity | Simple to write | More complex | | Best suited for | Fast reporting and BI | Large, hierarchical dimensions | In short, choose a star schema when query speed and reporting simplicity matter most, and choose a snowflake schema when storage efficiency, clean hierarchies, and low data redundancy are the priority. Many real warehouses combine both patterns depending on the size and depth of each dimension. ## When to Use a Snowflake Schema A snowflake schema is not always the right choice, so it helps to match the design to the workload and reporting needs. It tends to work best in the following situations: * Dimensions are very large and contain many repeating attributes that waste storage space when they are denormalized. * Dimensions have deep, well-defined hierarchies, such as Region to Country to State to City, that map naturally onto separate tables. * Data integrity and consistency are more important for the project than raw query speed. * Storage costs are a real concern and the disk savings across huge dimension tables are meaningful. * The model feeds [OLAP](https://www.guru99.com/online-analytical-processing.html) tools that can navigate normalized hierarchies efficiently. Conversely, when fast and simple reporting for business analysts is the priority, a star schema or a hybrid star cluster design is usually the better fit. Many [data warehouse architectures](https://www.guru99.com/data-warehouse-architecture.html) deliberately blend both approaches to balance speed and storage. ## What is a Galaxy Schema? A **Galaxy Schema** contains two or more fact tables that share dimension tables between them. It is also called a Fact Constellation Schema, and because it can be viewed as a collection of stars, it earns the name galaxy schema. [![Example of a galaxy schema with two fact tables sharing conformed dimension tables](https://www.guru99.com/images/1/022218_0758_StarandSnow3.png)](https://www.guru99.com/images/1/022218%5F0758%5FStarandSnow3.png) Example of Galaxy Schema As you can see in the example above, there are two fact tables: 1. Revenue 2. Product In a galaxy schema, the dimensions that are shared between the fact tables are called conformed dimensions. ## Characteristics of Galaxy Schema The galaxy schema has the following characteristics: * The dimensions are separated into distinct dimensions based on the various levels of the hierarchy. * For example, if geography has four levels of hierarchy โ€” region, country, state, and city โ€” then the galaxy schema should have four dimensions. * It is possible to build this type of schema by splitting a single star schema into more star schemas. * The dimensions in this schema are large and must be built according to the levels of the hierarchy. * The schema is helpful for aggregating fact tables to support better analysis and understanding. ## What is Star Cluster Schema? A snowflake schema contains fully expanded hierarchies, which can add complexity and require extra joins. A star schema, on the other hand, contains fully collapsed hierarchies, which may lead to redundancy. The best solution is often a balance between these two designs, known as a Star Cluster Schema. [![Example of a star cluster schema that balances star and snowflake designs](https://www.guru99.com/images/1/022218_0758_StarandSnow4.png)](https://www.guru99.com/images/1/022218%5F0758%5FStarandSnow4.png) Example of Star Cluster Schema Overlapping dimensions appear as forks in the hierarchies. A fork happens when an entity acts as a parent in two different dimensional hierarchies. These fork entities are then identified as classifications with one-to-many relationships, which limits the number of extra tables the design creates. ## FAQs โ„๏ธ Why is it called a snowflake schema? The schema earns its name because its entity relationship diagram branches outward like a snowflake. Normalizing each dimension into sub-dimension and lookup tables creates multiple connected levels radiating from the central fact table, forming a shape that resembles a snowflake crystal. ๐Ÿงฌ What is normalization in a snowflake schema? Normalization splits a dimension table into smaller related tables to remove repeating data. In a snowflake schema, attributes such as category or country move into their own tables, typically reaching third normal form, which reduces redundancy and keeps each value stored only once. ๐Ÿ“Š What is the difference between a fact table and a dimension table? A fact table stores measurable, numeric business events, such as sales amounts, plus foreign keys to dimensions. A dimension table stores descriptive attributes, such as product name or region, that give those facts context. Fact tables are usually far larger than dimension tables. ๐ŸŒฟ What is a sub-dimension or outrigger table in a snowflake schema? A sub-dimension, sometimes called an outrigger table, is a normalized table that branches off a main dimension. For example, a Product dimension may link to a separate Category table. These extra tables create the snowflakeโ€™s characteristic multi-level hierarchy. ๐Ÿ”€ Can a data warehouse use both star and snowflake schemas together? Yes. Many warehouses mix both patterns, normalizing only the large dimensions that benefit from it while keeping smaller dimensions flat. This hybrid, sometimes called a star cluster schema, balances the query speed of a star schema with the storage savings of a snowflake schema. ๐Ÿ“ˆ Does a snowflake schema support OLAP analysis? Yes. A snowflake schema feeds [OLAP](https://www.guru99.com/online-analytical-processing.html) systems well because its normalized hierarchies map cleanly onto drill-down levels such as country, state, and city. However, the extra joins can slow cube processing, so very query-heavy OLAP workloads sometimes favor a star schema. ๐Ÿค– How can AI help design and optimize a snowflake schema? AI assistants can suggest which dimensions to normalize, generate table structures from a business description, and recommend indexes or join paths that improve performance. They can also detect redundancy and inconsistent keys, though a data engineer should review every recommendation before applying it. ๐Ÿ› ๏ธ Can ChatGPT or GitHub Copilot generate snowflake schema SQL? Yes. [ChatGPT](https://chatgpt.com) and [GitHub Copilot](https://github.com/features/copilot) can draft CREATE TABLE statements and join queries for a snowflake schema from a short prompt. Always review the generated keys, data types, and relationships before running them in production. #### Summarize this post with: ChatGPT Perplexity Grok Google AI **Stay Updated on AI** **Get Weekly AI Skills, Trends, Actionable Advice.** ##### Sign up for the newsletter Subscribe for Free You have successfully subscribed. Please check your inbox. ![AI-Newsletter](https://www.guru99.com/images/footer-email-avatar-imges-1.png) Chosen by over **350,000+** professionals [Scroll to top ](#wrapper)Scroll to top ร— Toggle Menu Close Search for: Search ```json {"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://www.guru99.com/#organization","name":"Guru99","sameAs":["https://www.facebook.com/Guru99Official","https://twitter.com/guru99com"],"logo":{"@type":"ImageObject","@id":"https://www.guru99.com/#logo","url":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","contentUrl":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","caption":"Guru99","inLanguage":"en-US"}},{"@type":"WebSite","@id":"https://www.guru99.com/#website","url":"https://www.guru99.com","name":"Guru99","publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US"},{"@type":"ImageObject","@id":"https://www.guru99.com/images/snowflake-schema-in-data-warehouse.png","url":"https://www.guru99.com/images/snowflake-schema-in-data-warehouse.png","width":"700","height":"250","caption":"Snowflake Schema in Data Warehouse","inLanguage":"en-US"},{"@type":"BreadcrumbList","@id":"https://www.guru99.com/snowflake-schema-in-data-warehouse-model.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":"1","item":{"@id":"https://www.guru99.com","name":"Home"}},{"@type":"ListItem","position":"2","item":{"@id":"https://www.guru99.com/data-warehousing","name":"Data Warehousing"}},{"@type":"ListItem","position":"3","item":{"@id":"https://www.guru99.com/snowflake-schema-in-data-warehouse-model.html","name":"Snowflake Schema in Data Warehouse Model"}}]},{"@type":"WebPage","@id":"https://www.guru99.com/snowflake-schema-in-data-warehouse-model.html#webpage","url":"https://www.guru99.com/snowflake-schema-in-data-warehouse-model.html","name":"Snowflake Schema in Data Warehouse Model","dateModified":"2026-07-27T10:32:29+05:30","isPartOf":{"@id":"https://www.guru99.com/#website"},"primaryImageOfPage":{"@id":"https://www.guru99.com/images/snowflake-schema-in-data-warehouse.png"},"inLanguage":"en-US","breadcrumb":{"@id":"https://www.guru99.com/snowflake-schema-in-data-warehouse-model.html#breadcrumb"}},{"@type":"Person","@id":"https://www.guru99.com/author/davidcarter","name":"David Carter","description":"I'm David Carter, a Data Scientist specializing in data analytics and machine learning, solving complex problems with innovative data models.","url":"https://www.guru99.com/author/davidcarter","image":{"@type":"ImageObject","@id":"https://www.guru99.com/images/david-carter-author-120x120.png","url":"https://www.guru99.com/images/david-carter-author-120x120.png","caption":"David Carter","inLanguage":"en-US"},"worksFor":{"@id":"https://www.guru99.com/#organization"}},{"articleSection":"Data Warehousing","headline":"Snowflake Schema in Data Warehouse Model","description":"Snowflake Schema in data warehouse is a logical arrangement of tables in a multidimensional database such that the ER diagram resembles a snowflake shape.","keywords":"data warehouse","speakable":{"@type":"SpeakableSpecification","cssSelector":[".entry-title",".summary"]},"@type":"Article","author":{"@id":"https://www.guru99.com/author/davidcarter","name":"David Carter"},"dateModified":"2026-07-27T10:32:29+05:30","image":{"@id":"https://www.guru99.com/images/snowflake-schema-in-data-warehouse.png"},"copyrightYear":"2026","name":"Snowflake Schema in Data Warehouse Model","subjectOf":[{"@type":"FAQPage","mainEntity":[{"@type":"Question","name":"Why is it called a snowflake schema?","acceptedAnswer":{"@type":"Answer","text":"The schema earns its name because its entity relationship diagram branches outward like a snowflake. Normalizing each dimension into sub-dimension and lookup tables creates multiple connected levels radiating from the central fact table, forming a shape that resembles a snowflake crystal."}},{"@type":"Question","name":"What is normalization in a snowflake schema?","acceptedAnswer":{"@type":"Answer","text":"Normalization splits a dimension table into smaller related tables to remove repeating data. In a snowflake schema, attributes such as category or country move into their own tables, typically reaching third normal form, which reduces redundancy and keeps each value stored only once."}},{"@type":"Question","name":"What is the difference between a fact table and a dimension table?","acceptedAnswer":{"@type":"Answer","text":"A fact table stores measurable, numeric business events, such as sales amounts, plus foreign keys to dimensions. A dimension table stores descriptive attributes, such as product name or region, that give those facts context. Fact tables are usually far larger than dimension tables."}},{"@type":"Question","name":"What is a sub-dimension or outrigger table in a snowflake schema?","acceptedAnswer":{"@type":"Answer","text":"A sub-dimension, sometimes called an outrigger table, is a normalized table that branches off a main dimension. For example, a Product dimension may link to a separate Category table. These extra tables create the snowflake's characteristic multi-level hierarchy."}},{"@type":"Question","name":"Can a data warehouse use both star and snowflake schemas together?","acceptedAnswer":{"@type":"Answer","text":"Yes. Many warehouses mix both patterns, normalizing only the large dimensions that benefit from it while keeping smaller dimensions flat. This hybrid, sometimes called a star cluster schema, balances the query speed of a star schema with the storage savings of a snowflake schema."}},{"@type":"Question","name":"Does a snowflake schema support OLAP analysis?","acceptedAnswer":{"@type":"Answer","text":"Yes. A snowflake schema feeds OLAP systems well because its normalized hierarchies map cleanly onto drill-down levels such as country, state, and city. However, the extra joins can slow cube processing, so very query-heavy OLAP workloads sometimes favor a star schema."}},{"@type":"Question","name":"How can AI help design and optimize a snowflake schema?","acceptedAnswer":{"@type":"Answer","text":"AI assistants can suggest which dimensions to normalize, generate table structures from a business description, and recommend indexes or join paths that improve performance. They can also detect redundancy and inconsistent keys, though a data engineer should review every recommendation before applying it."}},{"@type":"Question","name":"Can ChatGPT or GitHub Copilot generate snowflake schema SQL?","acceptedAnswer":{"@type":"Answer","text":"Yes. ChatGPT and GitHub Copilot can draft CREATE TABLE statements and join queries for a snowflake schema from a short prompt. Always review the generated keys, data types, and relationships before running them in production."}}]}],"@id":"https://www.guru99.com/snowflake-schema-in-data-warehouse-model.html#schema-1151572","isPartOf":{"@id":"https://www.guru99.com/snowflake-schema-in-data-warehouse-model.html#webpage"},"publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US","mainEntityOfPage":{"@id":"https://www.guru99.com/snowflake-schema-in-data-warehouse-model.html#webpage"}}]} ```