All 35 lessons
Start here: cloud, data, Fabric and Power BI basics
- 1. Start here: the exam, the plan and what you already know
- 2. Cloud in plain words: SaaS, PaaS, IaaS and where Fabric fits
- 3. Data basics: files, tables, formats and the lakehouse idea
- 4. A tour of Microsoft Fabric: OneLake, workspaces and items
- 5. Power BI in plain words: semantic models, reports and DAX
Prepare data: get data in (exam area 2, 45-50%)
- 6. Connect to data and find it: connections, gateways, OneLake catalog and Real-Time hub
- 7. Choosing a data store: lakehouse, warehouse or eventhouse
- 8. Use data without copying: shortcuts, mirroring and OneLake integration
- 9. Ingest data: Dataflow Gen2, pipelines, Copy job and notebooks
- 10. Power Query in Dataflow Gen2: filter, data types, merge and new columns
- 11. Messy data: duplicates, missing values and nulls
Prepare data: transform and query (exam area 2)
Design and build semantic models (exam area 3, 25-30%)
- 18. Choose a storage mode: Import, DirectQuery, Direct Lake and Dual
- 19. Relationships: star schemas, bridge tables and many-to-many
- 20. DAX foundations: measures, filter context, CALCULATE and variables
- 21. DAX functions the exam tests: iterators, table filters, windows and information functions
- 22. Calculation groups, dynamic format strings and field parameters
- 23. Composite models and large semantic model storage format
Optimize models, then secure them (exam areas 3 and 1)
- 24. Direct Lake in depth: fallback, refresh and the two flavors
- 25. Incremental refresh for semantic models
- 26. Faster reports and DAX: finding and fixing slow queries
- 27. Who can see what: workspace roles and item permissions
- 28. Fine-grained security: rows, columns, objects and files
- 29. Governance: sensitivity labels and endorsement
Lifecycle, then exam practice (exam area 1)
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?
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.
Match
Match each part of Juniper's model to its role
Pick an item on the left, then its match on the right.
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.
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 dimension | One | Several. |
| Joins per query | Fewer | More. |
| Hierarchy in a semantic model | Yes, in one table | Can't span tables. |
| Storage | Some repeated text | Less 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.
CREATE TABLE dbo.DimProductASSELECTCAST(ROW_NUMBER() OVER (ORDER BY p.ProductID) AS int) AS ProductKey,p.ProductID,p.ProductName,c.CategoryNameFROM stg.Product AS pLEFT JOIN stg.Category AS cON p.CategoryID = c.CategoryID;
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.
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 IDENTITYnumbers 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.
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;
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.
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:
| StoreKey | StoreID | ValidFrom | ValidTo | IsCurrent | Region. |
|---|---|---|---|---|---|
| 31 | JC-017 | 2024-03-01 | 9999-12-31 | 1 | East. |
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.
| StoreKey | StoreID | ValidFrom | ValidTo | IsCurrent | Region. |
|---|---|---|---|---|---|
| 31 | JC-017 | 2024-03-01 | 2026-07-01 | 0 | East. |
| 88 | JC-017 | 2026-07-01 | 9999-12-31 | 1 | North. |
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.
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.
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
intinYYYYMMDDform. 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.
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.
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.
| Warehouse | Lakehouse. | |
|---|---|---|
| Language | T-SQL | PySpark or Spark SQL in a Notebook. |
| Create a table from a query | CREATE TABLE ... AS SELECT (CTAS) | .write.mode("overwrite").saveAsTable(...). |
| Add rows | INSERT ... SELECT | .write.mode("append"). |
| Stored as | Delta tables in OneLake | Delta 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.
INSERT INTO dbo.FactSales (DateKey, StoreKey, ProductKey, ReceiptNo, Qty, Amount)SELECTYEAR(s.SaleDate) * 10000 + MONTH(s.SaleDate) * 100 + DAY(s.SaleDate),COALESCE(d.StoreKey, -1),COALESCE(p.ProductKey, -1),s.ReceiptNo, s.Qty, s.AmountFROM stg.Sales AS sLEFT JOIN dbo.DimStore AS dON d.StoreID = s.StoreID AND d.IsCurrent = 1LEFT JOIN dbo.DimProduct AS pON p.ProductID = s.ProductID;
from pyspark.sql import Window, functions as Fstores = 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")
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.
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.
Worth keeping
- Declare the grain first; a different grain gets its own fact table.
- Facts hold measures and surrogate keys; dimensions hold attributes.
- Flatten snowflaked lookups into one dimension for reporting.
- SCD Type 2 when the past must stay as it was, Type 1 when it needn't.
- 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.
- [6]Microsoft Learn: Study guide for Exam DP-600: Implementing Analytics Solutions Using Microsoft Fabric
- [11]Microsoft Learn: Dimensional modeling in Fabric Data Warehouse
- [33]Microsoft Learn: Understand star schema and the importance for Power BI
- [34]Microsoft Learn: Dimensional modeling in Microsoft Fabric Warehouse: Dimension tables
- [35]Microsoft Learn: Dimensional modeling in Microsoft Fabric Warehouse: Fact tables
- [96]Microsoft Learn: Ingest data into your Warehouse using Transact-SQL
- [123]Microsoft Learn: Analytical and aggregate functions in Fabric Data Warehouse
- [139]Microsoft Learn: T-SQL surface area in Fabric Data Warehouse
- [156]Microsoft Learn: IDENTITY columns in Fabric Data Warehouse
- [157]Microsoft Learn: Temporal table usage scenarios
- [158]Microsoft Learn: Load data into your lakehouse with a notebook
- [159]Microsoft Learn: row_number (PySpark)