Help Center> Data Warehouse Service> Best Practices> Tutorial: Tuning Table Design> Table Schemas
View PDF

Table Schemas

The most common types of data warehouse schemas are star and snowflake schemas. Consider service and performance requirements when you choose a schema for your tables.

  • In the star schema, a central fact table contains the core data for the database and several dimension tables provide descriptive attribute information for the fact table. The primary key of a dimension table associates a foreign key in a fact table, as shown in Figure 1.
    • All facts must have the same granularity.
    • Different dimensions are not associated.
    Figure 1 Star schema
  • The snowflake schema is developed based on the star schema. In this schema, each dimension can be associated with multiple dimensions and split into tables of different granularities based on the dimension level, as shown in Figure 2.
    • This schema reduces the amount of data in dimension tables and facilitates join queries.
    • This schema has more dimension tables that need to be maintained than the star schema does.
    Figure 2 Snowflake schema

This tutorial verifies performance using the Store Sales (SS) model of TPC-DS. The model uses the snowflake schema. Figure 3 illustrates its structure.

Figure 3 TPC-DS Store Sales ER-Diagram

For details about the store_sales fact table and dimension tables in the model, see the official document of TPC-DS at http://www.tpc.org/tpc_documents_current_versions/current_specifications5.asp.