Black Friday50% off your first month on any monthly plan with EARLY50 See plans
All 35 lessons
  1. Start here: cloud, data, Fabric and Power BI basics

    1. 1. Start here: the exam, the plan and what you already know
    2. 2. Cloud in plain words: SaaS, PaaS, IaaS and where Fabric fits
    3. 3. Data basics: files, tables, formats and the lakehouse idea
    4. 4. A tour of Microsoft Fabric: OneLake, workspaces and items
    5. 5. Power BI in plain words: semantic models, reports and DAX
  2. Prepare data: get data in (exam area 2, 45-50%)

    1. 6. Connect to data and find it: connections, gateways, OneLake catalog and Real-Time hub
    2. 7. Choosing a data store: lakehouse, warehouse or eventhouse
    3. 8. Use data without copying: shortcuts, mirroring and OneLake integration
    4. 9. Ingest data: Dataflow Gen2, pipelines, Copy job and notebooks
    5. 10. Power Query in Dataflow Gen2: filter, data types, merge and new columns
    6. 11. Messy data: duplicates, missing values and nulls
  3. Prepare data: transform and query (exam area 2)

    1. 12. Views, functions and stored procedures in T-SQL
    2. 13. Build a star schema in a lakehouse or warehouse
    3. 14. Aggregate, join and enrich: SQL and PySpark side by side
    4. 15. Query with the Visual query editor and SQL
    5. 16. Query with KQL
    6. 17. Query with DAX: DAX query view and EVALUATE
  4. Design and build semantic models (exam area 3, 25-30%)

    1. 18. Choose a storage mode: Import, DirectQuery, Direct Lake and Dual
    2. 19. Relationships: star schemas, bridge tables and many-to-many
    3. 20. DAX foundations: measures, filter context, CALCULATE and variables
    4. 21. DAX functions the exam tests: iterators, table filters, windows and information functions
    5. 22. Calculation groups, dynamic format strings and field parameters
    6. 23. Composite models and large semantic model storage format
  5. Optimize models, then secure them (exam areas 3 and 1)

    1. 24. Direct Lake in depth: fallback, refresh and the two flavors
    2. 25. Incremental refresh for semantic models
    3. 26. Faster reports and DAX: finding and fixing slow queries
    4. 27. Who can see what: workspace roles and item permissions
    5. 28. Fine-grained security: rows, columns, objects and files
    6. 29. Governance: sensitivity labels and endorsement
  6. Lifecycle, then exam practice (exam area 1)

    1. 30. Version control: Git integration and Power BI projects (.pbip)
    2. 31. Deployment pipelines and impact analysis
    3. 32. XMLA endpoint and reusable assets: .pbit, .pbids and shared semantic models
    4. 33. Mock exam 1
    5. 34. Final review and exam day: every decision on one page
    6. 35. Mock exam 2
See the leaderboard

22 min

Build a star schema in a lakehouse or warehouse

Turn source tables into fact and dimension tables with surrogate keys and slowly changing dimensions.

By the end of this lesson you can

  • Choose the grain of a fact table and sort columns into facts and dimensions
  • Explain surrogate keys and how IDENTITY columns behave in Fabric Warehouse
  • Pick SCD Type 1 or Type 2 for a column, and recognize Types 0 and 3
  • Denormalize lookups and build the star with T-SQL or a Notebook

From earlier lessons · 1 of 2

You need T-SQL logic that takes a region as a parameter and can be joined in FROM. Which object fits?

Pick one answer.

From earlier lessons · 2 of 2

Which statement fails on a lakehouse's SQL analytics endpoint?

Pick one answer.

Juniper Coffee's till data lands as one wide table: 30 columns per receipt line, with store and product names typed by hand. The Power BI model built on it is slow, and report authors can't find anything. Then finance asks a hard question. After July's reorg moved Quay Rd from East to North, did last year's East revenue move too?

[6]

Facts, dimensions and the grain

Exam depth: know it cold.

A star schema shapes tables for reporting. One fact table sits in the middle, and dimension tables sit around it like the points of a star.

  • Fact table: one row per event you measure, such as one line on a till receipt. It holds measures, numbers you add up such as quantity and amount, plus keys to the dimensions.
  • Dimension table: one row per thing, such as a store, a product or a date. Its attributes, such as region or category, are what you filter and group by.

The grain says exactly what one fact row means. Write it as one sentence before anything else: one row per receipt line. Every measure in the table must be true at that grain.

Choose the lowest grain the source gives you. You can always add detail up into totals, but you can't split a total back into detail.

[11][33][35]

Rendering diagram…
Juniper's sales star at receipt-line grain. Budgets are per store per month, a coarser grain, so they get their own fact table that shares DimDate and DimStore.

[11][34]

Match

Match each part of Juniper's model to its role

Pick an item on the left, then its match on the right.

[33][34]

You are an analytics engineer at Juniper Coffee. FactSales has one row per receipt line. Finance now sends monthly budgets per store and wants budget against actual in one report. How should you store the budgets?

Pick one answer.

[33][35]

Star or snowflake, and why models want stars

Exam depth: know it cold.

Every Power BI visual filters, groups and then summarizes. A star fits that: dimensions filter and group, and facts get summed.

A snowflake splits one dimension into a chain of tables, such as Product, then Subcategory, then Category. It's normalized: each value is stored only once.

Star (flat dimension)Snowflake (chain of tables).
Tables per dimensionOneSeveral.
Joins per queryFewerMore.
Hierarchy in a semantic modelYes, in one tableCan't span tables.
StorageSome repeated textLess repetition.

To denormalize is to flatten: join the chain into one wide dimension, so Category repeats on every product row. Dimensions are small next to facts, so the extra storage is almost always worth it.

Decision rule: for reporting, flatten each snowflake into one dimension. Keep the chain only for a huge dimension, or when another fact table sits at the higher level.

[33][34]

sql
CREATE TABLE dbo.DimProduct
AS
SELECT
CAST(ROW_NUMBER() OVER (ORDER BY p.ProductID) AS int) AS ProductKey,
p.ProductID,
p.ProductName,
c.CategoryName
FROM stg.Product AS p
LEFT JOIN stg.Category AS c
ON p.CategoryID = c.CategoryID;
Staging: P1 Latte (C1), P2 Scone (C2), P3 Chai (C9); categories C1 Coffee, C2 Bakery. Result: 1 P1 Latte Coffee, 2 P2 Scone Bakery, 3 P3 Chai NULL. The LEFT JOIN keeps Chai although C9 is missing.

[96][123]

You are a BI developer at Juniper Coffee. The Warehouse holds Product, Subcategory and Category tables linked by keys. Report authors need a Category to Product drill-down hierarchy and fewer tables in the Data pane. What should you do?

Pick one answer.

[33]

Surrogate keys and IDENTITY columns

Exam depth: know it cold.

A business key, also called a natural key, is the ID from the source system, such as the till's store code JC-017. A surrogate key is a new integer the warehouse assigns to each dimension row. Facts store the surrogate key.

Why bother? A surrogate key does three jobs.

  • It lets one store have several history rows (SCD Type 2, below).
  • It merges sources whose IDs clash.
  • It keeps the fact table narrow, with one small number per dimension.

Use one even when the business key looks fine. The date dimension is the exception: its key is the date as an int, such as 20261103.

Two ways to make them in Fabric:

  • IDENTITY column (Warehouse only; recognize it): StoreKey BIGINT IDENTITY numbers new rows for you.
  • ROW_NUMBER(): number the rows in the query, as in the CTAS above. It works in a Warehouse and in Spark.

[34][139][156]

sql
CREATE TABLE dbo.DimStore (
StoreKey BIGINT IDENTITY,
StoreID varchar(10) NOT NULL,
StoreName varchar(50) NOT NULL,
Region varchar(10) NOT NULL,
IsCurrent bit NOT NULL
);
INSERT INTO dbo.DimStore (StoreID, StoreName, Region, IsCurrent)
SELECT StoreID, StoreName, Region, 1 FROM stg.Stores;
Leave StoreKey out of the INSERT and the warehouse fills it. The keys are unique positive numbers, but not 1, 2, 3 in order. A full Type 2 table also has ValidFrom and ValidTo.

[156]

[34][156]

Slowly changing dimensions

Exam depth: know it cold.

Store details change now and then. A slowly changing dimension (SCD) is your rule for what the dimension does when one changes.

  • Type 1: overwrite. Update the row in place and keep no history. Use it for corrections and for columns nobody tracks over time, such as a phone number.
  • Type 2: add a version. Expire the current row and insert a new row with a new surrogate key. Old facts keep pointing at the old version. Type 2 adds ValidFrom, ValidTo and an IsCurrent flag.
  • Type 0 (recognize it): keep the original value forever, such as a store's opening date.
  • Type 3 (recognize it): add a previous value column, which keeps only one step of history.

One dimension can mix types, column by column.

Decision rule: if reports must show the past as it was, Type 2. Otherwise, Type 1.

[33][34][157]

Worked example: Quay Rd moves from East to North on 2026-07-01. Finance wants last year's revenue to stay in East.

Before, DimStore has one row for store JC-017:

StoreKeyStoreIDValidFromValidToIsCurrentRegion.
31JC-0172024-03-019999-12-311East.

The load does two things. It expires row 31: ValidTo becomes 2026-07-01 and IsCurrent becomes 0. Then it inserts a new version with a new key, here 88.

StoreKeyStoreIDValidFromValidToIsCurrentRegion.
31JC-0172024-03-012026-07-010East.
88JC-0172026-07-019999-12-311North.

Sales before July point at key 31 and stay in East. New sales get key 88 and count in North. With Type 1, row 31 would simply say North, and all of Quay Rd's history would move with it.

[34]

You are an analytics engineer at Juniper Coffee. DimStore has a Region column and a ManagerName column. Regional bonuses are paid on history, so a region change must not move past sales. Manager name changes need no history. How should you load DimStore?

Pick one answer.

[34]

SCD Type 1 repaints the sign. SCD Type 2 keeps a dated photo of the old one. Picture: Isometric diagram cartoon in soft pastel colors: a tiny coffee shop rides a conveyor belt from a district tile labeled "EAST" to one labeled

Date, role-playing, degenerate and junk dimensions

Exam depth: know it cold.

  • Date dimension: one row per calendar date, with columns such as Year, Quarter, Month, MonthNumber, Weekday and IsHoliday. Its key is the date as an int in YYYYMMDD form. Sources rarely have one, so you generate it, including future dates for budgets. Time of day goes in a separate time dimension.
  • Role-playing dimension: one dimension used in several roles by the same fact. A supplier-delivery fact has OrderDateKey and DeliveryDateKey, and both point at DimDate.
  • Conformed dimension: one dimension shared by several fact tables, such as DimDate for sales and budgets.
  • Degenerate dimension (recognize it): an ID that stays in the fact table with no table of its own, such as ReceiptNo.
  • Junk dimension (recognize it): many small flags combined into one table, such as IsTakeaway and PaymentType, with one key in the fact.

[34]

You are an analytics engineer at Juniper Coffee. FactDeliveries has an order date and a delivery date for every supplier delivery. Analysts must filter by either date, and you want one date table to maintain in the Warehouse. How should you model it?

Pick one answer.

[34]

Build it: Warehouse T-SQL or Lakehouse Notebook

Exam depth: know it cold.

Load dimensions first, then facts, so every fact row can find its keys. Then use the engine your team writes.

WarehouseLakehouse.
LanguageT-SQLPySpark or Spark SQL in a Notebook.
Create a table from a queryCREATE TABLE ... AS SELECT (CTAS).write.mode("overwrite").saveAsTable(...).
Add rowsINSERT ... SELECT.write.mode("append").
Stored asDelta tables in OneLakeDelta tables in OneLake.

A fact load joins each source row to the current dimension row by business key, and stores the surrogate key. A sale whose store isn't in the dimension yet gets the -1 Unknown key, so it isn't dropped.

The SQL analytics endpoint can't create tables. To build the star in T-SQL, use a Warehouse.

[34][96][139][158]

sql
INSERT INTO dbo.FactSales (DateKey, StoreKey, ProductKey, ReceiptNo, Qty, Amount)
SELECT
YEAR(s.SaleDate) * 10000 + MONTH(s.SaleDate) * 100 + DAY(s.SaleDate),
COALESCE(d.StoreKey, -1),
COALESCE(p.ProductKey, -1),
s.ReceiptNo, s.Qty, s.Amount
FROM stg.Sales AS s
LEFT JOIN dbo.DimStore AS d
ON d.StoreID = s.StoreID AND d.IsCurrent = 1
LEFT JOIN dbo.DimProduct AS p
ON p.ProductID = s.ProductID;
A sale on 2026-11-03 gets DateKey 20261103. The IsCurrent filter matches one version per store, so no sale is doubled. A store or product missing from its dimension gets -1.

[34][96]

python
from pyspark.sql import Window, functions as F
stores = spark.read.table("silver_stores")
w = Window.orderBy("store_id")
dim_store = (
stores
.withColumn("store_key", F.row_number().over(w))
.select("store_key", "store_id", "store_name", "region")
)
dim_store.write.mode("overwrite").saveAsTable("dim_store")
In a Lakehouse Notebook: row_number() numbers the stores 1, 2, 3 by store_id, and dim_store is saved as a Delta table. Overwrite renumbers every key, so use it only before facts store the keys.

[158][159]

[6]

Exam-style practice

You are an analytics engineer at Juniper Coffee. Cleaned sales sit as Delta tables in a Lakehouse. The team writes PySpark, not T-SQL. You need to build DimStore and FactSales as Delta tables for a Power BI model. What should you use?

Pick one answer.

[158]

You are an analytics engineer at Juniper Coffee. After July's reorg, new FactSales rows for Quay Rd appear twice. The fact load joins stg.Sales to DimStore with ON d.StoreID = s.StoreID only. DimStore is SCD Type 2. What should you fix?

Pick one answer.

[34]

FlashcardsIndex cardsFlashcards ⚡

Card 1 of 15

[11][33][34][35][156][157]

Worth keeping

  1. Declare the grain first; a different grain gets its own fact table.
  2. Facts hold measures and surrogate keys; dimensions hold attributes.
  3. Flatten snowflaked lookups into one dimension for reporting.
  4. SCD Type 2 when the past must stay as it was, Type 1 when it needn't.
  5. Fabric IDENTITY: BIGINT, unique but not sequential. ROW_NUMBER works in both engines.

The star is built. Now the daily work inside it: a join that silently drops the new café, a total that doubles, a rank that skips a place. Next lesson you join, aggregate and enrich in SQL and PySpark, side by side.

Leaderboard

Loading…

50 XP for each lesson you finish, 10 for each right answer. Names show only for learners who turn on Show me on leaderboards.

Sources for this lesson (12)

Each fact in the lesson is marked with the number of the page it comes from.

  1. [6]Microsoft Learn: Study guide for Exam DP-600: Implementing Analytics Solutions Using Microsoft Fabric
  2. [11]Microsoft Learn: Dimensional modeling in Fabric Data Warehouse
  3. [33]Microsoft Learn: Understand star schema and the importance for Power BI
  4. [34]Microsoft Learn: Dimensional modeling in Microsoft Fabric Warehouse: Dimension tables
  5. [35]Microsoft Learn: Dimensional modeling in Microsoft Fabric Warehouse: Fact tables
  6. [96]Microsoft Learn: Ingest data into your Warehouse using Transact-SQL
  7. [123]Microsoft Learn: Analytical and aggregate functions in Fabric Data Warehouse
  8. [139]Microsoft Learn: T-SQL surface area in Fabric Data Warehouse
  9. [156]Microsoft Learn: IDENTITY columns in Fabric Data Warehouse
  10. [157]Microsoft Learn: Temporal table usage scenarios
  11. [158]Microsoft Learn: Load data into your lakehouse with a notebook
  12. [159]Microsoft Learn: row_number (PySpark)