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

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.

[6]

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

ReceiptIDStoreIDSoldAtAmountProduct.
R112026-11-03 08:054.50Latte.
R212026-11-03 08:052.50Scone.
R322026-11-03 12:404.50Latte.
R442026-11-03 17:104.80Mocha.

Stores

StoreIDRegionStoreName.
1NorthHarbour St.
2SouthMill Lane.
3NorthStation 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.

JoinKeeps.
INNER JOINOnly rows that match on both sides.
LEFT JOINEvery row from the left table, with NULLs where the right has no match.
RIGHT JOINEvery row from the right table, with NULLs where the left has no match.
FULL JOINEvery row from both sides.
CROSS JOINEvery 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.

[160][161]

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?

[160]

[34][160]

[161]

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.

[160]

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.

  • WHERE filters rows before grouping, so it can't use an aggregate.
  • HAVING filters groups after grouping. Conditions such as SUM(Amount) > 5 go here.
  • Every column in SELECT that isn't inside an aggregate must also be in GROUP BY.

Counting comes in three forms. COUNT(*) counts rows. COUNT(column) counts values that aren't NULL. COUNT(DISTINCT column) counts different values.

[123][162]

sql
SELECT
st.Region,
COUNT(*) AS Receipts,
COUNT(DISTINCT s.Product) AS Products,
SUM(s.Amount) AS Revenue
FROM Sales AS s
JOIN Stores AS st ON s.StoreID = st.StoreID
GROUP BY st.Region
HAVING SUM(s.Amount) > 5.00;
Result: one row, North | 2 | 2 | 7.00. South is grouped too (1 receipt, 4.50), then HAVING drops it. Dock St's R4 is gone because the join is inner.

[123]

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.

[123]

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_NUMBER gives 1, 2, 3 with no ties. Which tied row gets 1 isn't guaranteed, so add a tie-breaker column.
  • RANK gives ties the same number, then skips: 1, 1, 3.
  • DENSE_RANK gives 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.

[123]

sql
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 RegionTotal
FROM (VALUES ('North', 'Harbour St', 700), ('North', 'Station Rd', 700),
('North', 'Quay Rd', 500), ('South', 'Mill Lane', 650))
AS d(Region, Store, Revenue);
Daily revenue for four stores, typed inline with VALUES so you can run it anywhere. The result is in the next block.

[123]

Result. The two 700 stores tie in North.

StoreRevenueRowNumRnkDenseRnkRegionTotal.
Harbour St7001 or 2111900.
Station Rd7002 or 1111900.
Quay Rd5003321900.
Mill Lane650111650.

North's total is 700 + 700 + 500 = 1900, repeated on each North row. Mill Lane is alone in South, so it ranks 1 everywhere.

[123]

[123][163]

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.

[123]

INNER JOIN loses the new café. LEFT JOIN keeps it. Duplicate keys double everyone's revenue. Picture: Three-panel comic strip, cartoon style.

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) or DATEPART(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.

[96][160][164]

sql
CREATE TABLE dbo.SalesEnriched
AS
SELECT
s.ReceiptID,
s.Amount,
CASE WHEN s.Amount >= 4.00 THEN 'High' ELSE 'Low' END AS TicketBand,
DATEPART(hour, s.SoldAt) AS SaleHour,
st.Region
FROM dbo.Sales AS s
LEFT JOIN dbo.Stores AS st ON s.StoreID = st.StoreID;
Result: R1 4.50 High 8 North; R2 2.50 Low 8 North; R3 4.50 High 12 South; R4 4.80 High 17 NULL. The LEFT JOIN keeps Dock St's R4.

[96][164]

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.

TaskT-SQLPySpark.
Read a tableFROM Salesspark.read.table("sales").
JoinLEFT JOIN ... ON.join(stores, on="store_id", how="left").
New columnCASE WHEN ... END AS band.withColumn("band", F.when(...).otherwise(...)).
Filter rowsWHERE.filter(...).
Group and aggregateGROUP BY with SUM.groupBy("region").agg(F.sum("amount")).
Filter groupsHAVING.filter(...) after .agg(...).
Count different valuesCOUNT(DISTINCT x)F.count_distinct("x").
Save a new tableCTAS.write.mode("overwrite").saveAsTable("t").

F is the usual short name for pyspark.sql.functions. Without .otherwise(...), rows that match no when get NULL.

[158][165][166][167]

python
from pyspark.sql import functions as F
sales = 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")
Output: sales_enriched, a Delta table with 4 rows. The left join keeps R4 with a NULL region. band is High for 4.50 and 4.80, and Low for 2.50.

[158][165][167]

python
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)
)
Before the filter there are three groups: North (2, 7.00, 2), South (1, 4.50, 1) and NULL for R4 (1, 4.80, 1). The filter after agg works like HAVING, so only North remains.

[165][166]

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.

[167]

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.

[123]

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.

[160]

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.

[123]

FlashcardsIndex cardsFlashcards ⚡

Card 1 of 15

[123][158][160][166][167]

Worth keeping

  1. LEFT JOIN from the fact keeps every sale; duplicate dimension keys multiply totals.
  2. WHERE filters rows, HAVING filters groups, and GROUP BY lists every plain SELECT column.
  3. RANK skips after ties, DENSE_RANK doesn't, ROW_NUMBER never ties.
  4. Enrich with CASE, date parts and lookups; keep results with CTAS or saveAsTable.
  5. 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.

  1. [6]Microsoft Learn: Study guide for Exam DP-600: Implementing Analytics Solutions Using Microsoft Fabric
  2. [34]Microsoft Learn: Dimensional modeling in Microsoft Fabric Warehouse: Dimension tables
  3. [96]Microsoft Learn: Ingest data into your Warehouse using Transact-SQL
  4. [123]Microsoft Learn: Analytical and aggregate functions in Fabric Data Warehouse
  5. [158]Microsoft Learn: Load data into your lakehouse with a notebook
  6. [160]Microsoft Learn: Joins (SQL Server)
  7. [161]Microsoft Learn: FROM clause plus JOIN, APPLY, PIVOT (Transact-SQL)
  8. [162]Microsoft Learn: SELECT - HAVING clause (Transact-SQL)
  9. [163]Microsoft Learn: SELECT - QUALIFY clause (Transact-SQL)
  10. [164]Microsoft Learn: DATEPART (Transact-SQL)
  11. [165]Microsoft Learn: PySpark basics
  12. [166]Microsoft Learn: PySpark functions
  13. [167]Microsoft Learn: when (PySpark)