Structure: Denormalised Dimensions vs Normalised Hierarchies
In a star schema, each dimension table contains all descriptive attributes in a single flat table. A product dimension, for example, holds product name, category, subcategory, brand, and supplier in one row. The fact table references each dimension through a single foreign key. The result is a schema diagram that looks like a star: one fact table in the centre, dimension tables radiating outward.
In a snowflake schema, hierarchical attributes within a dimension are broken out into their own tables. The product dimension becomes a product table linked to a category table, which links to a department table. Each level of the hierarchy gets its own table with its own primary key. The schema diagram branches outward like a snowflake.
Both approaches model the same underlying data. The difference is where you place the normalisation boundary. Star schemas denormalise for query simplicity; snowflake schemas normalise for storage efficiency and update consistency.
A third pattern, the starflake, selectively normalises only the largest or most frequently updated dimensions while keeping smaller dimensions flat. This is common in practice even when teams do not name it explicitly.
Query Performance and Join Complexity
Star schemas produce fewer joins per query. A typical analytical query joins the fact table to two or three dimension tables, each on a single surrogate key. Query optimisers on columnar databases recognise this pattern and apply star-join optimisations (hash joins, broadcast of small dimension tables) efficiently.
Snowflake schemas add joins. A query that needs product department information must join fact to product, then product to category, then category to department. Each additional join adds a hash or merge step. On small datasets the difference is negligible. On fact tables with hundreds of millions of rows and dimension hierarchies with multiple levels, the extra joins increase execution time measurably.
BI tools and reporting platforms typically generate SQL automatically. Star schemas are friendlier to these generators because each attribute lives in one table with a direct relationship to the fact. Snowflake schemas sometimes require intermediate views or pre-joined tables to present a flat attribute list to BI tools that expect star-like navigation.
If your primary workload is ad-hoc analyst SQL, star schemas reduce the chance of incorrect joins and make queries easier to read and debug. If your workload is mostly pre-built dashboards with cached aggregations, join complexity matters less because queries run on schedules rather than interactively.
Storage, Maintenance and Data Integrity
Snowflake schemas use less storage for dimension data because repeating text (like a department name) is stored once in a normalised table rather than duplicated across every product row. On row-store databases, this saving was significant. On modern columnar databases with dictionary encoding and run-length compression, repeated text values compress extremely well, shrinking the storage gap substantially.
Maintenance differs more meaningfully. In a star schema, renaming a department means updating every product row that belongs to that department. In a snowflake schema, you update one row in the department table. For dimensions with millions of rows and frequent hierarchy changes, snowflake schemas reduce update volume and lock contention.
Data integrity is stronger in snowflake schemas because each hierarchy level has its own primary key and foreign-key constraint. Invalid combinations (a subcategory pointing to the wrong category) are structurally impossible if constraints are enforced. Star schemas rely on ETL logic to prevent inconsistent denormalised rows, which is effective but depends entirely on pipeline quality.
Slowly changing dimensions (SCDs) work in both schemas. Type 2 SCD (adding new rows for historical tracking) is slightly more complex in snowflake schemas because the change might occur at any hierarchy level, and you must decide whether to version the sub-dimension table, the main dimension, or both. Star schemas localise version rows to a single dimension table, simplifying the SCD logic.
When to Choose Each Schema
Star schemas are the default recommendation for most analytical data warehouses. They are simpler to build, simpler to query, well supported by every BI platform, and the storage penalty on columnar systems is usually trivial. Start with a star schema unless you have a specific reason to normalise dimensions.
Snowflake schemas earn their place when dimension tables are very large (tens of millions of rows), hierarchy attributes change frequently, or data-integrity enforcement at the database level is a compliance requirement. A customer dimension with deep geographic hierarchies (country, region, state, city, postal code) that changes often benefits from normalisation because updates are localised.
Consider a starflake approach if only one or two dimensions justify normalisation. Keep small, stable dimensions (date, currency, status) denormalised as star arms. Normalise only the large, volatile dimensions. This gives you the query simplicity of a star for most joins and the maintenance benefit of snowflaking for the dimensions that need it.
Document your choice in the warehouse data dictionary with a rationale so that future team members understand why certain dimensions are flat and others are normalised. Inconsistent patterns without documentation create confusion when the team grows.
This content is general information about dimensional modelling patterns and does not constitute professional or financial advice.
Practical Migration Between Schemas
Converting a star schema to a snowflake schema requires extracting hierarchy columns from the dimension table into new normalised tables, generating surrogate keys for each level, and updating the original dimension to reference those keys. ETL pipelines that load the dimension must be refactored to load sub-dimension tables first (respecting foreign-key order) and then the main dimension table.
Converting a snowflake schema to a star schema is a denormalisation exercise. You join the hierarchy tables into a single flat dimension view, generate a new surrogate key sequence, and replace fact-table references. This direction is simpler because it reduces tables rather than adding them.
Both conversions are disruptive. Downstream reports, saved queries, and BI tool metadata all reference specific table and column names. Plan for a transition period where both the old and new schemas exist, with a view layer providing backward compatibility. Run parallel validation queries to confirm that aggregated metrics match between old and new schemas before decommissioning the original structure.
If you are on a cloud platform that supports zero-copy cloning (such as Snowflake the product, distinct from the schema pattern), clone your production schema, perform the conversion on the clone, validate, then swap. This approach minimises downtime and provides an instant rollback path.
This content is general information about dimensional modelling patterns and does not constitute professional or financial advice.