The star schema is the core concept behind a common DP-900 question: what shape should analytical data take when you store it? Fact tables and dimension tables, the difference between star and snowflake designs, and the analytical data model (semantic model) built on top of them span two items in Domain 4 of the official skills outline: "Describe options for analytical data stores" and "Describe features of data models in Power BI." Learn them once and you can answer questions from both areas.
This post is a supplementary entry in our DP-900 study series. Building on the OLTP versus OLAP and data lake versus data warehouse concepts covered earlier, it walks through how analytical data is actually organized into tables and how it becomes a model in Power BI.
---
Why Does Analytical Data Need a Different Shape?
Think about an online store's order system. Every order is recorded across separate tables: orders, order lines, customers, addresses, products, and payments. Normalization removes duplication and keeps writes fast, but answering "revenue by region and category last quarter" means joining five or six tables.
It is the difference between a shoebox of receipts and a well-kept household ledger. Receipts are quick to toss in one at a time, but to learn what you spent on groceries last month you have to dig through all of them. The ledger already sorts date, category, and amount into fixed columns, so the total is right there.
That is why analytical stores do not simply copy transactional data. They reorganize it into a shape that is easy to aggregate.
| Aspect | Transactional schema (OLTP) | Analytical schema (OLAP) | |------|------|------| | Purpose | Record orders and payments quickly and accurately | Aggregate and compare large volumes of data | | Design principle | Normalize to minimize duplication | Split into facts and dimensions for easy aggregation | | Typical query | Read or update a single order | Monthly or regional totals, trends | | Table structure | Many small tables | A central fact table plus dimension tables | | Typical example | Online store order database | Data warehouse, Power BI model |
Exam tip: "Transform transactional data into a schema optimized for analytical queries" → data warehouse. The most common table design used for this is the star schema.
---
Fact Tables and Dimension Tables
A star schema is built from two kinds of tables: fact tables that hold the numbers, and dimension tables that hold the attributes you use to group and filter those numbers.
Fact Tables: What Happened, in Numbers
Each row in a fact table is one recorded event. One sale, one shipment, or one sensor reading each becomes a single row. A row holds the numeric values you want to analyze (quantity, revenue, cost) and the keys that point to the dimensions the event belongs to (date key, product key, customer key).
Dimension Tables: The "By What" of Your Analysis
A dimension table represents an entity you aggregate by. A product dimension holds the product name, category, and brand; a customer dimension holds the name, city, and tier. Each row has a unique key value, and the fact table references dimension rows through that key.
Almost every analytical model includes a date (time) dimension. With a date dimension that already has date, month, quarter, and year columns, questions such as "revenue by month" or "compared with the same quarter last year" become easy to answer.
| Aspect | Fact table | Dimension table | |------|------|------| | What it holds | Measures (numbers) plus dimension keys | Descriptive attributes (names, categories, locations) | | What one row means | One event (one sale) | One entity (one product) | | Row count | Very large and always growing | Relatively small | | Example columns | Quantity, revenue, date key, product key | Product name, category, brand | | Role in a query | The values you sum or average | What you group and filter by |
In an analytical model, these two ideas are called measures and dimensions. "Total revenue by customer" and "monthly units sold by product" are both measures aggregated across one or more dimensions.
Exam tip: "Numeric values to aggregate, such as quantity or revenue" → fact table. "Attributes to group or filter by, such as product name, category, or city" → dimension table.
---
Star Schema vs Snowflake Schema
A single fact table in the middle with dimension tables around it looks like a star, hence the name star schema. Dimension tables relate to the fact table in a one-to-many (1:N) relationship: one product appears in many sales records, but each sale points to exactly one product.
A snowflake schema takes the dimension tables of a star schema and splits them further. Instead of repeating the category name inside the product dimension, for example, you create a separate category table and link it to the product table. The branches reaching outward look like a snowflake, which is where the name comes from.
!Star schema vs snowflake schema
| Aspect | Star schema | Snowflake schema | |------|------|------| | Dimension structure | One table per dimension | Dimension normalized into several tables | | How hierarchies are stored | Columns in the same table (product → category) | Separate tables (product table → category table) | | Number of joins | Few | More | | Queries and model | Simple and fast | More complex | | Duplication | Some duplication allowed inside dimensions | Less duplication |
Microsoft's Power BI guidance recommends designing models as star schemas. Simple relationships make filters flow the way you expect and keep DAX formulas easier to write. When the source is shaped like a snowflake, the dimension tables are often merged into one so the model ends up as a star.
Exam tip: "A dimension table links to yet another table (for example, product → category) to represent a hierarchy" → snowflake schema. "Dimension tables connect directly to one fact table" → star schema.
---
From Cubes to Semantic Models
Analysts can query warehouse tables directly, but to make reports and dashboards easier to build, a separate analytical data model usually sits on top. It is a blueprint that defines in advance which tables connect to which, what counts as a measure, and which hierarchies you can drill through.
The Old Way: OLAP Cubes
In the past, cubes were built with technologies such as SQL Server Analysis Services (SSAS) Multidimensional. Numbers were pre-aggregated and stored for every combination of dimensions, such as revenue by product and region. Lookups were fast, but any new aggregation meant reprocessing the cube.
The Current Way: Semantic Models
Power BI and Microsoft Fabric use a semantic model, also called a tabular model. It defines tables, relationships, hierarchies, and measures written in DAX, and aggregations are calculated at query time instead of being stored in advance. In Import mode, data is loaded into the VertiPaq engine's in-memory columnar store for fast calculation. In Fabric, Direct Lake mode loads Delta tables from OneLake straight into the model's memory without a separate copy.
| Aspect | OLAP cube (multidimensional) | Semantic model (tabular) | |------|------|------| | Typical technology | SSAS Multidimensional | Power BI, Microsoft Fabric | | When aggregation happens | Precomputed and stored | Calculated at query time | | How calculations are defined | MDX and similar | DAX measures | | Where it stands today | Legacy approach | Currently recommended approach |
Attribute Hierarchies: Drill Up and Drill Down
Hierarchies let you move up and down between levels within a dimension. Viewing revenue by year and clicking into one year to see it by month is drilling down; going back up to the year view is drilling up.
Date dimension: day → month → year Product dimension: product → category Customer dimension: customer → city
In Power BI Desktop's Model view, you create relationships between fact and dimension tables, define hierarchies, and set data types and display formats. For the difference between measures and calculated columns, and for data connectivity modes, continue with Power BI Essentials.
Exam tip: "A multidimensional structure that stores numbers pre-aggregated by dimension" → cube. "Start at yearly values and move down to monthly values" → drill down through a hierarchy. "Load Fabric Delta tables into a model without copying them" → Direct Lake.
---
Data Warehouse, Data Lake, and Lakehouse
If the star schema is about how to shape tables, analytical data stores are about where the data lives and in what form. DP-900 expects you to tell three options apart.
| Aspect | Data warehouse | Data lake | Data lakehouse | |------|------|------|------| | Storage form | Relational tables | Files on a distributed file system | Lake files plus Delta tables | | When schema is applied | On write (star schema, etc.) | On read | Delta Lake tables enforce the schema | | Data it holds | Structured | Structured, semi-structured, unstructured | Mainly structured and semi-structured | | Main query method | SQL | Apache Spark and similar engines | Both SQL and Spark | | Azure examples | Fabric Warehouse | Azure Data Lake Storage Gen2, OneLake | Fabric Lakehouse, Azure Databricks |
What makes the lakehouse possible is Delta Lake. It is an open-source format that adds a transaction log on top of Parquet files, giving you schema enforcement, transactional consistency, batch and streaming loads, and SQL querying. Both Microsoft Fabric and Azure Databricks use it as their default table format.
Fabric Lakehouse vs Fabric Warehouse
In Microsoft Fabric, both stores keep their data in OneLake. The difference is how you work with the data.
Fabric Lakehouse: stores data in Delta Lake format and is worked with through notebooks and Spark. A SQL analytics endpoint is created automatically, so you can also query it with SQL. It suits semi-structured or mixed data and machin