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
Aggregate, join and enrich: SQL and PySpark side by side
Join, aggregate and add new columns or tables with T-SQL and PySpark in Fabric.
By the end of this lesson you can
- Predict the rows each join type returns, and spot a join that doubles totals
- Aggregate with GROUP BY, HAVING and COUNT DISTINCT, filtering in the right clause
- Rank and total rows with ROW_NUMBER, RANK, DENSE_RANK and SUM OVER
- Write the same joins, new columns and aggregates in PySpark
From earlier lessons · 1 of 3
What does the grain of a fact table describe?
Pick one answer.
From earlier lessons · 2 of 3
A store's region changes, and past sales must stay in the old region. Which SCD type fits?
Pick one answer.
From earlier lessons · 3 of 3
A fact load joins to an SCD Type 2 dimension by business key. Which filter stops new facts from doubling?
Pick one answer.
Juniper Coffee's ops lead wants four numbers by noon: revenue per region, only the regions above target, the top store in each region, and a High or Low ticket band. Yesterday two reports disagreed. The new Dock St café vanished from one, and Harbour St doubled in the other. Both were join mistakes.
The data in this lesson. Dock St (store 4) opened this week and isn't in Stores yet. Station Rd (store 3) has no sales today.
Sales
| ReceiptID | StoreID | SoldAt | Amount | Product. |
|---|---|---|---|---|
| R1 | 1 | 2026-11-03 08:05 | 4.50 | Latte. |
| R2 | 1 | 2026-11-03 08:05 | 2.50 | Scone. |
| R3 | 2 | 2026-11-03 12:40 | 4.50 | Latte. |
| R4 | 4 | 2026-11-03 17:10 | 4.80 | Mocha. |
Stores
| StoreID | Region | StoreName. |
|---|---|---|
| 1 | North | Harbour St. |
| 2 | South | Mill Lane. |
| 3 | North | Station Rd. |
Join types: whose rows survive
Exam depth: know it cold.
A join combines rows from two tables where a condition matches, usually a key: ON s.StoreID = st.StoreID.
| Join | Keeps. |
|---|---|
INNER JOIN | Only rows that match on both sides. |
LEFT JOIN | Every row from the left table, with NULLs where the right has no match. |
RIGHT JOIN | Every row from the right table, with NULLs where the left has no match. |
FULL JOIN | Every row from both sides. |
CROSS JOIN | Every left row paired with every right row. It has no ON. |
LEFT JOIN and LEFT OUTER JOIN are the same; OUTER is optional. A plain JOIN is an inner join.
Decision rule: from a fact to a dimension, use LEFT JOIN with the fact on the left, so no sale is ever dropped.
Predict, then reveal
Predict first: with Sales on the left and Stores on the right, joined on StoreID, how many rows does each join return?
You are a data analyst at Juniper Coffee. Your query uses FROM Sales AS s INNER JOIN Stores AS st ON s.StoreID = st.StoreID. The Dock St café isn't in Stores yet, and its sales are missing from the company total. Finance needs every sale counted. What should you change?
Pick one answer.
GROUP BY, HAVING and COUNT DISTINCT
Exam depth: know it cold.
GROUP BY puts rows with the same values into one group. Aggregate functions such as SUM, COUNT, AVG, MIN and MAX then turn each group into one row.
WHEREfilters rows before grouping, so it can't use an aggregate.HAVINGfilters groups after grouping. Conditions such asSUM(Amount) > 5go here.- Every column in
SELECTthat isn't inside an aggregate must also be inGROUP BY.
Counting comes in three forms. COUNT(*) counts rows. COUNT(column) counts values that aren't NULL. COUNT(DISTINCT column) counts different values.
SELECTst.Region,COUNT(*) AS Receipts,COUNT(DISTINCT s.Product) AS Products,SUM(s.Amount) AS RevenueFROM Sales AS sJOIN Stores AS st ON s.StoreID = st.StoreIDGROUP BY st.RegionHAVING SUM(s.Amount) > 5.00;
You are a data analyst at Juniper Coffee. You need every region whose latte revenue is over 500, from Sales joined to Stores. Which query shape returns exactly that?
Pick one answer.
Window functions: ROW_NUMBER, RANK, DENSE_RANK and SUM OVER
Exam depth: know it cold.
A window function calculates a value for each row from a set of related rows, and keeps every row. GROUP BY collapses rows; a window doesn't.
The OVER clause sets the window. PARTITION BY splits rows into sets, such as one per region. Leave it out and all rows form one set. ORDER BY inside OVER sets the order, and ranking functions require it.
The three ranking functions differ only on ties.
ROW_NUMBERgives 1, 2, 3 with no ties. Which tied row gets 1 isn't guaranteed, so add a tie-breaker column.RANKgives ties the same number, then skips: 1, 1, 3.DENSE_RANKgives ties the same number, with no gap: 1, 1, 2.
SUM(Revenue) OVER (PARTITION BY Region) puts the region total on every row. Divide by it for a store's share of its region.
SELECT Region, Store, Revenue,ROW_NUMBER() OVER (PARTITION BY Region ORDER BY Revenue DESC) AS RowNum,RANK() OVER (PARTITION BY Region ORDER BY Revenue DESC) AS Rnk,DENSE_RANK() OVER (PARTITION BY Region ORDER BY Revenue DESC) AS DenseRnk,SUM(Revenue) OVER (PARTITION BY Region) AS RegionTotalFROM (VALUES ('North', 'Harbour St', 700), ('North', 'Station Rd', 700),('North', 'Quay Rd', 500), ('South', 'Mill Lane', 650))AS d(Region, Store, Revenue);
Result. The two 700 stores tie in North.
| Store | Revenue | RowNum | Rnk | DenseRnk | RegionTotal. |
|---|---|---|---|---|---|
| Harbour St | 700 | 1 or 2 | 1 | 1 | 1900. |
| Station Rd | 700 | 2 or 1 | 1 | 1 | 1900. |
| Quay Rd | 500 | 3 | 3 | 2 | 1900. |
| Mill Lane | 650 | 1 | 1 | 1 | 650. |
North's total is 700 + 700 + 500 = 1900, repeated on each North row. Mill Lane is alone in South, so it ranks 1 everywhere.
You are an analytics engineer at Juniper Coffee. A bonus goes to the top stores in each region, and tied stores share a place. After a two-way tie for first, the next store must be second, not third. Which ranking should the query use?
Pick one answer.
Enrich: new columns and new tables
Exam depth: know it cold.
To enrich data is to add information the source didn't hold. Three moves cover most needs.
- A computed column with
CASE: a label from a rule, such as a ticket band. - Date parts:
YEAR(SoldAt),MONTH(SoldAt)orDATEPART(hour, SoldAt), for sales by month or by hour. - A lookup: a join that brings in a column from another table, such as region.
To keep the result, write it to a new table: CTAS in a Warehouse, or saveAsTable in a Lakehouse Notebook. If it must always show the latest data, use a view instead, as in lesson 12.
CREATE TABLE dbo.SalesEnrichedASSELECTs.ReceiptID,s.Amount,CASE WHEN s.Amount >= 4.00 THEN 'High' ELSE 'Low' END AS TicketBand,DATEPART(hour, s.SoldAt) AS SaleHour,st.RegionFROM dbo.Sales AS sLEFT JOIN dbo.Stores AS st ON s.StoreID = st.StoreID;
The same work in PySpark
Exam depth: know it cold.
In a Lakehouse Notebook you do the same work on DataFrames, tables held in Spark's memory. The Lakehouse tables here use snake_case column names.
| Task | T-SQL | PySpark. |
|---|---|---|
| Read a table | FROM Sales | spark.read.table("sales"). |
| Join | LEFT JOIN ... ON | .join(stores, on="store_id", how="left"). |
| New column | CASE WHEN ... END AS band | .withColumn("band", F.when(...).otherwise(...)). |
| Filter rows | WHERE | .filter(...). |
| Group and aggregate | GROUP BY with SUM | .groupBy("region").agg(F.sum("amount")). |
| Filter groups | HAVING | .filter(...) after .agg(...). |
| Count different values | COUNT(DISTINCT x) | F.count_distinct("x"). |
| Save a new table | CTAS | .write.mode("overwrite").saveAsTable("t"). |
F is the usual short name for pyspark.sql.functions. Without .otherwise(...), rows that match no when get NULL.
from pyspark.sql import functions as Fsales = spark.read.table("sales")stores = spark.read.table("stores")enriched = (sales.join(stores, on="store_id", how="left").withColumn("band", F.when(F.col("amount") >= 4, "High").otherwise("Low")))enriched.write.mode("overwrite").saveAsTable("sales_enriched")
by_region = (enriched.groupBy("region").agg(F.count("receipt_id").alias("receipts"),F.sum("amount").alias("revenue"),F.count_distinct("product").alias("products"),).filter(F.col("revenue") > 5))
You are a data analyst at Juniper Coffee. In a Notebook, you need a new column size that is 'Large' when qty is 3 or more, and 'Small' otherwise. Every row must stay. Which line completes df = df.____?
Pick one answer.
Exam-style practice
You are a data analyst at Juniper Coffee. This query fails: SELECT st.Region, st.StoreName, SUM(s.Amount) FROM Sales AS s JOIN Stores AS st ON s.StoreID = st.StoreID GROUP BY st.Region; You need revenue per store within each region. What should you change?
Pick one answer.
You are an analytics engineer at Juniper Coffee. After someone reloaded DimStore, revenue per store exactly doubled in every report. FactSales didn't change. The report query joins FactSales to DimStore on StoreID. What is the most likely cause?
Pick one answer.
You are a data analyst at Juniper Coffee. You need stores with more than 100 receipts yesterday. The query SELECT StoreID, COUNT(*) FROM Sales WHERE COUNT(*) > 100 GROUP BY StoreID; fails. What should you do?
Pick one answer.
Worth keeping
- LEFT JOIN from the fact keeps every sale; duplicate dimension keys multiply totals.
- WHERE filters rows, HAVING filters groups, and GROUP BY lists every plain SELECT column.
- RANK skips after ties, DENSE_RANK doesn't, ROW_NUMBER never ties.
- Enrich with CASE, date parts and lookups; keep results with CTAS or saveAsTable.
- PySpark mirrors SQL: join, withColumn with when/otherwise, groupBy().agg(), filter.
You've typed every query by hand. Next lesson, the Visual query editor builds the same select, filter and aggregate with clicks, and shows you the SQL it writes.
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 (13)
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
- [34]Microsoft Learn: Dimensional modeling in Microsoft Fabric Warehouse: Dimension tables
- [96]Microsoft Learn: Ingest data into your Warehouse using Transact-SQL
- [123]Microsoft Learn: Analytical and aggregate functions in Fabric Data Warehouse
- [158]Microsoft Learn: Load data into your lakehouse with a notebook
- [160]Microsoft Learn: Joins (SQL Server)
- [161]Microsoft Learn: FROM clause plus JOIN, APPLY, PIVOT (Transact-SQL)
- [162]Microsoft Learn: SELECT - HAVING clause (Transact-SQL)
- [163]Microsoft Learn: SELECT - QUALIFY clause (Transact-SQL)
- [164]Microsoft Learn: DATEPART (Transact-SQL)
- [165]Microsoft Learn: PySpark basics
- [166]Microsoft Learn: PySpark functions
- [167]Microsoft Learn: when (PySpark)