Star schema vs snowflake schema

Both schemas place a central fact table beside dimension tables. The difference is what happens inside the dimensions. In a star schema, each dimension is a single table with all its descriptive columns. In a snowflake schema, dimensions are normalised into smaller related tables.

A product dimension in a star might hold category and supplier names directly. In a snowflake, category and supplier become separate tables joined to the product table. The fact table is unchanged; the difference is the number of joins needed to reach a label.

A star schema is easy to explain. One fact table joins to a handful of dimensions, and each dimension is a list of labels. A business user can follow the model, and a BI tool can generate a query without deep knowledge of the source system.

Flat dimensions also make queries shorter and often faster, because there are fewer joins. Columnar databases compress repeated text well, so the redundancy costs less than it would in a transactional system.

Normalisation removes repeated values. If a supplier name changes, a snowflake updates one row in the supplier table instead of thousands of rows in the product dimension. That can matter when the source system is normalised and the warehouse mirrors it.

Snowflakes also enforce consistency. A category table with one row per category prevents two spellings of the same category from appearing in different product rows. The cost is more joins and a model that is harder for non-technical users to navigate.

The right choice depends on how the model will be used. A reporting layer read by BI tools and analysts usually benefits from the simplicity of a star. A large conformed warehouse with strict master data may benefit from some normalisation.

If the main consumer is a dashboard, a star schema is usually the better default. Flatten the dimensions to the level people filter by, and keep the descriptive columns they recognise. Do not normalise a dimension just because the source system does.

If several subject areas share large reference tables, or if a dimension changes frequently and consistency is critical, a snowflake or a hybrid may be justified. Measure the cost in joins, documentation, and user confusion, not only in storage.

Many warehouses combine both shapes. The presentation layer for BI is a star, while the staging and integration layers stay normalised. The star is built from those sources and refreshed on a schedule, so the reporting model stays simple without duplicating the operational design.

A common compromise is to snowflake only large or volatile reference tables, such as a customer hierarchy, and flatten everything else. That keeps the common queries simple while avoiding a huge repeated hierarchy in the main dimension.

Whichever shape you choose, define the grain, the keys, and the refresh process. A star with an unclear grain is no better than a snowflake with too many joins. The schema is a means to a reliable answer, not an end in itself.