A star schema is a fact table surrounded by multiple dimension tables. When visualized, the shape the table makes resembles that of a star, something similar to Figure 2.16.
FIGURE 2.16 A star schema example
Do you remember what table distribution type you use for a fact table and what type you use for a dimension table? You might consider reading back over that section to confirm this. It might start to make a bit more sense now that you see this diagram. The reason you might use a HASH distribution for the fact table is because that is the one you will query most. When you configure the HASH you also provide the argument used as the key to distribute data across the nodes, known as the distribution key or distribution column. The dimension tables, which rarely if ever change, have a REPLICATED distribution, meaning the content of the tables is copied to each node. You might also notice in Figure 2.16 that there are no database layer–enforced primary or foreign key relationships between the fact and the dimension tables. This is the case when you’re running on SQL pools in Azure Synapse Analytics but is not the case when you’re running a database in Azure SQL. See Figure 2.2 for a reminder of how primary and foreign keys are displayed visually.
Snowflake Schema
A snowflake schema is what typically exists before a normalization of dimension tables into the state you see in Figure 2.16. With some imagination, Figure 2.16 looks like a star, but consider a scenario where there were additional dimension tables, aka subdimension tables, which needed to be transformed—in other words, normalized into the dimension tables that are directly linking to the fact table. Figure 2.17 illustrates what a simple snowflake schema might look like.
FIGURE 2.17 A snowflake schema example
When you compare this to a star schema, you can see many similar tables. Those extra tables moving from the snowflake schema to the star schema took place because the data was transformed and moved into other dimension tables. That transformation is also referred to as normalization, which results in a star schema.
