---
title: "Compare star, snowflake, fact constellation"
subject: "Data Mining"
chapter: 1
key: "semester_7/data_mining:star-snowflake-constellation"
source: https://www.bctengineer.com/q/semester_7/data_mining/star-snowflake-constellation
site: BCT Engineering
---

# Compare star, snowflake, fact constellation

Data Mining · Chapter 1: Introduction

- Asked in: year not given
- Video: [#7 Data Warehouse Schema - Definition & Types - Star, Snowflake, Fact Constellation Schema |DWDM|](https://www.youtube.com/watch?v=g5l9E3A7FwE) — Trouble- Free
- Answer checked by Claude Opus 5.5 (high effort) on 2026-10-14
- Page: https://www.bctengineer.com/q/semester_7/data_mining/star-snowflake-constellation
- Whole chapter: https://www.bctengineer.com/md/semester_7/data_mining/chapter-1

## Question

Compare Star, Snowflake and Fact Constellation schemas with examples.

## Answer

| Point | Star schema | Snowflake schema | Fact constellation |
|---|---|---|---|
| Structure | One fact table, one table per dimension | Star with normalised (split) dimensions | Multiple fact tables sharing dimensions |
| Dimension tables | Denormalised | Normalised into sub-tables | Shared between facts |
| Redundancy | High | Low | Depends on dimensions |
| Joins | Few | More | Many, across facts |
| Query speed | Fastest | Slower | Slowest, most complex |
| Storage | More | Less | More |
| Complexity | Simple | Moderate | Complex |
| Use | Data marts, simple models | Large dimension hierarchies | Enterprise warehouses |

**Star example**
```text
 Time    Item
   \    /
  SALES FACT -- Branch
```
`Item(item_key, name, brand, type, supplier_type)` is a single table.

**Snowflake example**
```text
 SALES FACT -- Item -> Supplier
          \
           Location -> City
```
`Item` is split into `Item(item_key, name, brand, type, supplier_key)` and `Supplier(supplier_key, supplier_type)`.

**Fact constellation example**
```text
 Sales Fact      Shipping Fact
     \            /
   Time  Item  Location  (shared)
```
Sales and Shipping facts both use the Time, Item and Location dimensions.

## Check yourself

The answer's key points as multiple-choice questions, with the correct option and why.

1. What characterises a star schema?
   A. Multiple fact tables sharing dimensions
   B. One fact table with one denormalised table per dimension
   C. Normalised split dimensions
   D. No fact table
   Answer: B. Star has a central fact table and single denormalised dimension tables. (Key point: Star structure)

2. Compared with star, a snowflake schema has:
   A. More redundancy and fewer joins
   B. Less redundancy and storage but more joins
   C. Same joins and same redundancy
   D. No dimension tables
   Answer: B. Normalising dimensions reduces redundancy but needs more joins and slows queries. (Key point: Snowflake trade-off)

3. What defines a fact constellation?
   A. A single fact table
   B. Multiple fact tables sharing dimension tables
   C. Only normalised dimensions
   D. A schema with no dimensions
   Answer: B. e.g. Sales and Shipping facts share Time, Item and Location. (Key point: Fact constellation)

4. In the snowflake example, how is Item modelled?
   A. Item alone, with supplier_type inside
   B. Item split into Item and Supplier tables linked by supplier_key
   C. Item merged with Time
   D. Item removed
   Answer: B. supplier_type moves into a Supplier table referenced by supplier_key. (Key point: Snowflake example)

## Related in Data Mining

- [OLAP vs OLTP; schema modeling; star DMQL](https://www.bctengineer.com/question.md?key=semester_7%2Fdata_mining%3Aolap-oltp-schema-star-dmql) — Data Mining, chapter 2
- [OLAP operations and star schema in DMQL](https://www.bctengineer.com/question.md?key=semester_7%2Fdata_mining%3Aolap-operations-star-dmql) — Data Mining, chapter 2

## Related in other subjects

- [Compare star and delta connection](https://www.bctengineer.com/question.md?key=semester_1%2Fbasic_electrical_engineering%3Acompare-star-delta-three-phase) — Basic Electrical Engineering, chapter 8
- [Constellation diagram significance; E1 vs T1](https://www.bctengineer.com/question.md?key=semester_6%2Fcommunication_systems%3Aconstellation-significance-e1-t1) — Communication Systems, chapter 5