Skip to main content

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.

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

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.

   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.

  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.

 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.

✓ Answer checked by Claude Opus 5.5 (high effort)

Related questions

All Data Mining chapter 1 questions →