This is part of a series on my preparation for the DP-900 exam. This is the Microsoft Azure Data Fundamentals, part of a number of certification paths. You can read various posts I’ve created as part of this learning experience.
There are types of schemas the exist in data warehouses. This topic is definitely on the exam.
OLTP/Relational
The type of schema that many of us work with is the standard OLTP or relational model. We have lots of transaction tables, most should have a PK, some of which have PKs. The schema expands to meet different needs and can have lots of entities.
As an example, here is a view of the AdventureWorks database.
This isn’t fundamentally different from the schema types below, but there isn’t a central, or two central, tables here. Instead, we have a lot of different groups of tables. The structure is designed for normalization, and usually has lots of tables compared to a data warehouse.
Star
The star schema is often used in data warehouses. The name comes from the fact that the table arrangement looks similar to a star. There is a central fact table, which has some details of the main data, often something like sales, and a lot of foreign keys (FK). The fact_sales_order is the fact table below.
Then there are supporting tables around the fact table, linked by the FKs. These are the dimension tables, and contain details about a specific dimension or area. In the image below, we have date, employee, store, and other dimensions tables.
This is a somewhat de-normalized structure, as the primary purpose is to report on a set of facts.
Snowflake
This schema builds on the star schema. Here there is still one fact table (Sales below), but the dimension tables have their own dimension tables, providing more details. Essentially, the dimension tables are normalized. An example is the Employee dimension, which has a linked Department dimension.
Galaxy
The galaxy schema expands with a second fact table. In the image below, we have the sales and purchase as fact tables. There are dimension tables, which can be linked to one or more fact tables.
It isn’t that important you know how to build these schemas or design the entities for the DP-900 exam, but you do need to recognize the structures.