Black Friday50% off your first month on any monthly plan with EARLY50 See plans
All 33 lessons
  1. Start here: cloud and data 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. The three languages: SQL, PySpark and KQL side by side
    6. 6. Getting data in: the map of Fabric tools
  2. Store, ingest and transform batch data (exam area 2, 30-35%)

    1. 7. Choosing a data store and a transformation tool
    2. 8. Shortcuts and mirroring: use data without copying it
    3. 9. Ingest data with pipelines and Copy job
    4. 10. Transform with PySpark and SQL: join, denormalize, aggregate
    5. 11. Messy data: duplicates, missing values and late arrivals
    6. 12. Full and incremental loads, and loading a star schema
  3. Orchestrate and stream (exam areas 1 and 2)

    1. 13. Pipeline or notebook? Choosing how to orchestrate
    2. 14. Schedules and event-based triggers
    3. 15. Parameters and dynamic expressions
    4. 16. Streaming in Fabric: Eventstreams, Spark streaming and KQL
    5. 17. KQL and windowing functions for real-time data
  4. Set up and secure a Fabric solution (exam area 1, 30-35%)

    1. 18. Workspace settings: Spark, domains, OneLake and data workflows
    2. 19. Version control with Git and database projects
    3. 20. Deployment pipelines: development, test and production
    4. 21. Who can see what: workspace roles and item permissions
    5. 22. Fine-grained security: rows, columns, objects, files and masking
    6. 23. Governance: sensitivity labels and endorsement
  5. Monitor, fix and optimize (exam area 3, 30-35%)

    1. 24. Monitoring hub, alerts and Activator
    2. 25. Fixing errors in pipelines, dataflows and notebooks
    3. 26. Fixing errors in Eventhouse, Eventstream and T-SQL
    4. 27. Optimize lakehouse tables: V-Order, OPTIMIZE, VACUUM and partitions
    5. 28. Optimize Spark, pipelines and warehouses
    6. 29. Optimize eventstreams, eventhouses and queries
  6. Exam practice and final review

    1. 30. Exam day: question types, timing and how to think
    2. 31. Mock exam 1
    3. 32. Mock exam 2
    4. 33. Final review: every decision on one page
See the leaderboard

25 min

Transform with PySpark and SQL: join, denormalize, aggregate

Shape raw tables into analytics tables with joins, grouping and aggregations.

By the end of this lesson you can

  • Join dimension tables into one wide table and save it as Delta
  • Group and aggregate in PySpark and in T-SQL
  • Use ROW_NUMBER to keep one row per group
  • Choose CTAS, INSERT...SELECT or a view in a Warehouse

From earlier lessons · 1 of 2

Your nightly copy brings only new and changed customer rows. Changed rows must replace their old versions. Which Lakehouse table action fits?

Pick one answer.

From earlier lessons · 2 of 2

You only need to copy a table hourly, incrementally, with the least build effort. What do you use?

Pick one answer.

The raw tables are in Northwind Rides' Lakehouse. The ops manager opens one and sees a column of numbers called start_station_id. "I don't know which station 4172 is," she says. "I need revenue per city zone per day, with names, by Monday." That's three moves: join, group and save.

[4]

Denormalize: join dimensions into one wide table

Exam depth: know it cold.

A fact table stores events you measure, such as trips with a fare. A dimension table describes the things around those events, such as stations or customers. Facts hold the dimension's key, like start_station_id.

Denormalizing means joining dimension columns into the table people query. You store some data more than once, such as a zone name on every trip. In return, reports need fewer joins and are easier to read.

Dimensions are usually small next to facts, so the extra storage is almost always worth it. Do it in your gold layer, the tables reports read.

[35][36]

Rendering diagram…
Two dimension tables join onto the trips fact table by their keys. The result is one wide table that a report can read with no joins.

[35]

Join in PySpark

python
from pyspark.sql import functions as F
trips = spark.read.table("silver_trips")
stations = spark.read.table("dim_station")
customers = spark.read.table("dim_customer")
trips_wide = (
trips
.join(stations, trips.start_station_id == stations.station_id, "left")
.join(customers, on="customer_id", how="left")
.select(
"trip_id", "start_time", "duration_min", "fare",
"customer_id", "plan_type",
"station_name", "city_zone",
)
)
display(trips_wide)
Output: one row per trip, now with station_name, city_zone and plan_type. Trips whose station isn't in dim_station keep null station columns, because the joins are left joins.

[87][88]

Exam depth: know it cold.

Read a PySpark join in three parts: .join(other_table, condition, how).

  • Condition: trips.start_station_id == stations.station_id matches columns with different names. on="customer_id" works when both tables use the same name, and keeps one copy of it.
  • How: "inner" keeps only rows that match on both sides. "left" keeps every row from the left table and fills nulls when there's no match.
  • .select(...) picks the columns you want in the result.

For a fact-to-dimension join, use "left" so a missing dimension row never drops a fact.

[87][88]

You are a data engineer at Northwind Rides. Your notebook builds the wide table with trips.join(stations, trips.start_station_id == stations.station_id, "inner"). Finance says 300 trips from a new station are missing, since the station isn't in dim_station yet. What should you change?

Pick one answer.

Save the result as a Delta table

python
(
trips_wide.write
.format("delta")
.mode("overwrite")
.saveAsTable("gold_trips_wide")
)
Output: a Delta table named gold_trips_wide in the Lakehouse Tables folder. Each run replaces it. The SQL analytics endpoint and Power BI can read it right away.

[63]

[63][66][87]

Group and aggregate in PySpark

Exam depth: know it cold.

groupBy puts rows with the same values into groups. agg then runs one or more aggregate functions on each group. An aggregate function turns many values into one, such as count, sum, avg, min or max.

The result has one row per group. Give each result a name with .alias(...), or Spark names it something like sum(fare).

[87]

python
daily_zone = (
trips_wide
.groupBy(F.to_date("start_time").alias("trip_date"), "city_zone")
.agg(
F.count("trip_id").alias("trips"),
F.sum("fare").alias("revenue"),
F.avg("duration_min").alias("avg_minutes"),
)
)
daily_zone.write.format("delta").mode("overwrite").saveAsTable("gold_daily_zone")
Output: one row per date and city zone, with columns trip_date, city_zone, trips, revenue and avg_minutes. It's saved as the Delta table gold_daily_zone.

[63][87]

You are a data engineer at Northwind Rides. Ops needs total fare per zone for each day from trips_wide, which has trip_date, city_zone and fare. Which line gives exactly that?

Pick one answer.

[87]

Window functions: ROW_NUMBER

Exam depth: know it cold.

A window function calculates a value for each row from a set of related rows. Unlike groupBy, it keeps every row.

ROW_NUMBER numbers the rows in each set 1, 2, 3 and so on.

  • PARTITION BY says which rows form a set, such as one customer's trips.
  • ORDER BY says which row gets number 1. It's required for ROW_NUMBER.

Keep rows where the number is 1 and you get one row per set: the latest trip, the top zone. If two rows tie on the order, which one gets 1 isn't fixed. Add a tie-breaker column to the order.

[90][91][94]

python
from pyspark.sql import Window
w = Window.partitionBy("customer_id").orderBy(F.col("start_time").desc())
last_trip = (
trips_wide
.withColumn("rn", F.row_number().over(w))
.filter(F.col("rn") == 1)
.drop("rn")
)
Output: one row per customer, holding their most recent trip. The descending order gives the newest trip number 1, and the filter keeps only that row.

[90][91]

ORDER BY start_time ASC, keep row 1. Congratulations, you found everyone's first ride. Picture: Retro pixel art, 16-bit video game style.

The same work in T-SQL: CTAS, INSERT...SELECT and views

Exam depth: know it cold.

In a Warehouse, three statements cover most transforms.

  • CTAS (CREATE TABLE AS SELECT) creates a new table and fills it from a query, in one step.
  • INSERT...SELECT adds a query's rows to a table that already exists.
  • A view saves the query itself, with a name. It stores no rows. The query runs each time someone reads the view.

A Warehouse can read a Lakehouse table in the same workspace by its three-part name: lakehouse.schema.table.

[92][93]

sql
CREATE TABLE dbo.daily_zone_revenue
AS
SELECT
CAST(t.start_time AS date) AS trip_date,
s.city_zone,
COUNT(*) AS trips,
SUM(t.fare) AS revenue
FROM rides_lakehouse.dbo.silver_trips AS t
JOIN dbo.dim_station AS s
ON t.start_station_id = s.station_id
GROUP BY CAST(t.start_time AS date), s.city_zone;
Output: a new Warehouse table daily_zone_revenue with one row per date and zone. It reads trips from the Lakehouse by three-part name and joins the station dimension.

[92]

sql
INSERT INTO dbo.daily_zone_revenue (trip_date, city_zone, trips, revenue)
SELECT
CAST(t.start_time AS date),
s.city_zone,
COUNT(*),
SUM(t.fare)
FROM rides_lakehouse.dbo.silver_trips AS t
JOIN dbo.dim_station AS s
ON t.start_station_id = s.station_id
WHERE CAST(t.start_time AS date) = CAST(DATEADD(day, -1, GETDATE()) AS date)
GROUP BY CAST(t.start_time AS date), s.city_zone;
Output: yesterday's rows are added to the existing daily_zone_revenue table. Earlier days stay as they were. Run it once a day after the trips load.

[92]

sql
CREATE VIEW dbo.vw_zone_rank
AS
SELECT
trip_date,
city_zone,
revenue,
ROW_NUMBER() OVER (
PARTITION BY trip_date
ORDER BY revenue DESC
) AS zone_rank
FROM dbo.daily_zone_revenue;
Output: every row of daily_zone_revenue plus zone_rank. The top-earning zone each day gets 1. The view stores nothing; it runs this query whenever it's read.

[93][94]

CTASINSERT...SELECTView
Stores rows?Yes, in a new table.Yes, in an existing table.No, only the query.
Data is current as ofWhen it ran.When it ran.The moment you read it.
Read speedFast. Results are ready.Fast.Runs the query each time.
Use it forBuilding a table from a query.Adding each new batch.Always-current logic, or one name for a join.

Decision rule: results read often and refreshed on a schedule, use a table (CTAS to create, INSERT...SELECT to add). Logic that must always show current data, use a view.

The Lakehouse SQL analytics endpoint can hold views, but it can't create tables or insert rows. CTAS needs a Warehouse.

[92][93]

You are a data engineer at Northwind Rides. A dashboard reads daily zone totals 500 times each morning. The totals come from a slow join over a billion trips. Totals refreshed once a night are fine. You need the fastest reads. What should you create in the Warehouse?

Pick one answer.

[92][93]

The KQL equivalents

Exam depth: recognize it.

KQL (Kusto Query Language) is the language of the Eventhouse. A query starts with a table, and each | passes rows to the next step. A later lesson covers it in full.

TaskPySparkT-SQLKQL
Filter rows.filter(...)WHEREwhere.
Pick columns.select(...)SELECTproject.
Join a dimension.join(..., "left")LEFT JOINjoin kind=leftouter or lookup.
Group and aggregate.groupBy().agg()GROUP BYsummarize ... by.
Sort.orderBy(...)ORDER BYsort by.

Example: Trips | summarize revenue = sum(fare) by city_zone totals fares per zone.

[95]

Exam-style practice

You are a data engineer at Northwind Rides. Your query keeps each customer's latest trip with ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY start_time ASC) AS rn and WHERE rn = 1. Ops says it shows rides from years ago. What should you fix?

Pick one answer.

[91][94]

You are a data engineer at Northwind Rides. A scheduled notebook ends with daily_zone.write.format("delta").saveAsTable("gold_daily_zone"). It worked on Monday. On Tuesday it fails because the table already exists. Each run holds the full data. What should you add?

Pick one answer.

[63][66]

You are a data engineer at Northwind Rides. An analyst runs a CTAS statement in the Lakehouse SQL analytics endpoint to build a summary table. It fails. The team only writes T-SQL. What should you do?

Pick one answer.

[92][93]

FlashcardsIndex cardsFlashcards ⚡

Card 1 of 16

[36][66][88][92][93][94][95]

Worth keeping

  1. Denormalize with left joins from facts to dimensions, so no fact is dropped.
  2. groupBy plus agg in PySpark, GROUP BY in T-SQL, summarize in KQL.
  3. ROW_NUMBER with PARTITION BY and ORDER BY, then rn = 1, keeps one row per key.
  4. Always set .mode() when writing; overwrite for full loads, append for new rows.
  5. CTAS builds a table, INSERT...SELECT adds to it, a view stays current; CTAS needs a Warehouse.

Monday's report goes out, and ops loves it. On Tuesday, one zone's revenue has doubled overnight. The scooters didn't get busier. Somebody's data arrived twice. Next lesson you hunt down duplicates, gaps and late arrivals.

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. [4]Microsoft Learn: Study Guide for Exam DP-700
  2. [35]Microsoft Learn: Dimensional modeling in Fabric Data Warehouse
  3. [36]Microsoft Learn: Dimensional modeling in Microsoft Fabric Warehouse: Dimension tables
  4. [63]Microsoft Learn: Use a notebook to load data into your lakehouse
  5. [66]Apache Spark documentation: Generic Load/Save Functions
  6. [87]Microsoft Learn: Lakehouse tutorial: Prepare and transform data in the lakehouse
  7. [88]Microsoft Learn: PySpark basics
  8. [90]Microsoft Learn: Window class
  9. [91]Microsoft Learn: row_number
  10. [92]Microsoft Learn: Ingest data into your Warehouse using Transact-SQL
  11. [93]Microsoft Learn: T-SQL surface area in Fabric Data Warehouse
  12. [94]Microsoft Learn: Analytical and aggregate functions in Fabric Data Warehouse
  13. [95]Microsoft Learn: KQL quick reference