---
title: "Data warehouse schemas"
subject: "Data Mining"
chapter: 1
key: "semester_7/data_mining:dw-schema-types"
source: https://www.bctengineer.com/q/semester_7/data_mining/dw-schema-types
site: BCT Engineering
---

# Data warehouse schemas

Data Mining · Chapter 1: Introduction

- Asked in: 2065 Baisakh; 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/dw-schema-types
- Whole chapter: https://www.bctengineer.com/md/semester_7/data_mining/chapter-1

## Question

What do you mean by "schema"? Briefly describe different Data Warehouse schemas.

## Short answer

A schema is the logical structure of database tables and relationships. In a data warehouse, the star schema has one fact table linked to denormalized dimension tables; the snowflake schema normalizes dimensions; and the fact constellation schema uses multiple fact tables sharing dimensions, suitable for complex enterprise needs.

## Answer

A **schema** is the logical description of the structure of a database: its tables, attributes, keys and relationships. In a data warehouse, the schema is a **multidimensional model** consisting of a central **fact table** (numeric measures and foreign keys) and surrounding **dimension tables** (descriptive attributes).

#### 1. Star schema
One fact table connected directly to one denormalised table per dimension. The diagram looks like a star.
```text
   Time        Item
     \         /
      \       /
       SALES FACT
      /        \
   Branch     Location
```
Simple, fast queries, but with data redundancy in dimensions.

#### 2. Snowflake schema
A variant of the star schema where dimension tables are normalised into more tables.
```text
  Item -> Supplier
    \
     SALES FACT -- Location -> City
```
Saves space and reduces redundancy, but needs more joins and so is slower.

#### 3. Fact constellation (galaxy) schema
Multiple fact tables share dimension tables.
```text
 Sales Fact      Shipping Fact
     \            /
   Time, Item, Location (shared)
```
Used for complex enterprise warehouses where several business processes are modelled. It is the most complex of the three.

## Check yourself

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

1. What describes a star schema?
   A. Normalised dimensions with many joins
   B. Multiple fact tables sharing dimensions
   C. One fact table directly linked to one denormalised table per dimension
   D. No fact table
   Answer: C. A star has a central fact table and denormalised dimension tables. (Key point: Star schema)

2. What is the trade-off of a snowflake schema versus a star?
   A. Faster but uses more space
   B. Uses a single table
   C. Saves space but needs more joins and is slower
   D. Has no dimensions
   Answer: C. Normalising dimensions reduces redundancy but adds joins. (Key point: Snowflake trade-off)

3. What characterises a fact constellation schema?
   A. One fact table only
   B. Fact table without measures
   C. Multiple fact tables sharing dimension tables
   D. Dimensions with no keys
   Answer: C. Several fact tables share dimensions, modelling multiple business processes; it is the most complex. (Key point: Fact constellation)

## 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
- [Data warehouse, data mart and need](https://www.bctengineer.com/question.md?key=semester_7%2Fdata_mining%3Awarehouse-datamart-need) — Data Mining, chapter 1
- [Data warehouse architecture and components](https://www.bctengineer.com/question.md?key=semester_7%2Fdata_mining%3Adw-architecture-components) — Data Mining, chapter 1
- [Short note: Noise in data warehouse](https://www.bctengineer.com/question.md?key=semester_7%2Fdata_mining%3Anoise-in-dw-note) — Data Mining, chapter 2
- [Data warehouse and its characteristics](https://www.bctengineer.com/question.md?key=semester_7%2Fdata_mining%3Adw-define-characteristics) — Data Mining, chapter 1
- [National data warehouse process architecture](https://www.bctengineer.com/question.md?key=semester_7%2Fdata_mining%3Anepal-national-data-warehouse) — Data Mining, chapter 1

## Related in other subjects

- [Data warehouse and data mining](https://www.bctengineer.com/question.md?key=semester_8%2Finformation_system%3Adata-warehouse-data-mining) — Information System, chapter 4, asked 2×
- [Hybrid data in big data warehouse](https://www.bctengineer.com/question.md?key=semester_8%2Fbig_data_technologies%3Ahybrid-data-warehouse) — Big Data Technologies, chapter 1
- [Data mining and business knowledge](https://www.bctengineer.com/question.md?key=semester_8%2Finformation_system%3Adata-mining-business-knowledge) — Information System, chapter 4