Star schema and snowflake schema are two ways to organise data in a data warehouse. Both have a central fact table (numbers you measure, such as sales) surrounded by dimension tables (descriptions, such as product, customer and date). The difference is how the dimensions are stored.
In short: a star schema keeps each dimension in one table — simpler and faster for reporting. A snowflake schema splits dimensions into several related tables — less duplication, but more joins.
The same idea, but dimensions are normalised into sub-tables. For example, DimProduct links to DimCategory, which links to DimDepartment. Drawn out, the branches look like a snowflake.
Choose a star schema for most reporting and BI. Power BI and Microsoft Fabric semantic models work best with star schemas.
Choose a snowflake schema when dimensions are very large, have deep hierarchies or are shared across many fact tables and duplication becomes a real problem.
Many teams use a mostly-star design and snowflake only a few dimensions.
You can build either design in Azure Synapse, Azure SQL Database, Databricks or a Microsoft Fabric warehouse/lakehouse. Data engineers usually load raw data into a lake, then build star-schema “gold” tables for reporting. Learn more in data modeling in Azure.
Frequently Asked Questions
What is the main difference between star and snowflake schemas?
A star schema stores each dimension in one denormalised table. A snowflake schema splits dimensions into normalised sub-tables.
Which schema is faster?
Usually the star schema, because queries need fewer joins.
When is a snowflake schema better?
When dimensions are very large or have deep hierarchies, and reducing duplicate data matters more than query simplicity.
Which schema does Power BI recommend?
Microsoft recommends a star schema for Power BI semantic models.
What is a fact table?
A table of measurable events, such as sales amounts or quantities, with keys that link to dimension tables.
What is a dimension table?
A table that describes the facts — for example product, customer, store or date details.
var ekit_config = {"ajaxurl":"https://azuretrainings.in/wp-admin/admin-ajax.php","nonce":"61699873bb","i18n":{"video_frame":"Video player","close":"Close","slider_prev":"Previous slide","slider_next":"Next slide","slider_first":"This is the first slide","slider_last":"This is the last slide","slider_bullet":"Go to slide {{index}}"}};
//# sourceURL=ekit-core-js-extra
var localize = {"ajaxurl":"https://azuretrainings.in/wp-admin/admin-ajax.php","nonce":"c12d6e06b9","i18n":{"added":"Added ","compare":"Compare","loading":"Loading..."},"eael_translate_text":{"required_text":"is a required field","invalid_text":"Invalid","billing_text":"Billing","shipping_text":"Shipping","fg_mfp_counter_text":"of"},"page_permalink":"https://azuretrainings.in/snowflake-schema-vs-star-schema/","cart_redirectition":"no","cart_page_url":"","el_breakpoints":{"mobile":{"label":"Mobile Portrait","value":767,"default_value":767,"direction":"max","is_enabled":true},"mobile_extra":{"label":"Mobile Landscape","value":880,"default_value":880,"direction":"max","is_enabled":false},"tablet":{"label":"Tablet Portrait","value":1024,"default_value":1024,"direction":"max","is_enabled":true},"tablet_extra":{"label":"Tablet Landscape","value":1200,"default_value":1200,"direction":"max","is_enabled":false},"laptop":{"label":"Laptop","value":1366,"default_value":1366,"direction":"max","is_enabled":false},"widescreen":{"label":"Widescreen","value":2400,"default_value":2400,"direction":"min","is_enabled":false}}};
//# sourceURL=eael-general-js-extra
