All 33 lessons
Start here: cloud and data 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. The three languages: SQL, PySpark and KQL side by side
- 6. Getting data in: the map of Fabric tools
Store, ingest and transform batch data (exam area 2, 30-35%)
- 7. Choosing a data store and a transformation tool
- 8. Shortcuts and mirroring: use data without copying it
- 9. Ingest data with pipelines and Copy job
- 10. Transform with PySpark and SQL: join, denormalize, aggregate
- 11. Messy data: duplicates, missing values and late arrivals
- 12. Full and incremental loads, and loading a star schema
Orchestrate and stream (exam areas 1 and 2)
Set up and secure a Fabric solution (exam area 1, 30-35%)
- 18. Workspace settings: Spark, domains, OneLake and data workflows
- 19. Version control with Git and database projects
- 20. Deployment pipelines: development, test and production
- 21. Who can see what: workspace roles and item permissions
- 22. Fine-grained security: rows, columns, objects, files and masking
- 23. Governance: sensitivity labels and endorsement
Monitor, fix and optimize (exam area 3, 30-35%)
- 24. Monitoring hub, alerts and Activator
- 25. Fixing errors in pipelines, dataflows and notebooks
- 26. Fixing errors in Eventhouse, Eventstream and T-SQL
- 27. Optimize lakehouse tables: V-Order, OPTIMIZE, VACUUM and partitions
- 28. Optimize Spark, pipelines and warehouses
- 29. Optimize eventstreams, eventhouses and queries
Exam practice and final review
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.
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.
Join in PySpark
from pyspark.sql import functions as Ftrips = 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)
Exam depth: know it cold.
Read a PySpark join in three parts: .join(other_table, condition, how).
- Condition:
trips.start_station_id == stations.station_idmatches 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.
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
(trips_wide.write.format("delta").mode("overwrite").saveAsTable("gold_trips_wide"))
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).
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")
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.
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.
from pyspark.sql import Windoww = 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"))
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.
CREATE TABLE dbo.daily_zone_revenueASSELECTCAST(t.start_time AS date) AS trip_date,s.city_zone,COUNT(*) AS trips,SUM(t.fare) AS revenueFROM rides_lakehouse.dbo.silver_trips AS tJOIN dbo.dim_station AS sON t.start_station_id = s.station_idGROUP BY CAST(t.start_time AS date), s.city_zone;
INSERT INTO dbo.daily_zone_revenue (trip_date, city_zone, trips, revenue)SELECTCAST(t.start_time AS date),s.city_zone,COUNT(*),SUM(t.fare)FROM rides_lakehouse.dbo.silver_trips AS tJOIN dbo.dim_station AS sON t.start_station_id = s.station_idWHERE CAST(t.start_time AS date) = CAST(DATEADD(day, -1, GETDATE()) AS date)GROUP BY CAST(t.start_time AS date), s.city_zone;
CREATE VIEW dbo.vw_zone_rankASSELECTtrip_date,city_zone,revenue,ROW_NUMBER() OVER (PARTITION BY trip_dateORDER BY revenue DESC) AS zone_rankFROM dbo.daily_zone_revenue;
| CTAS | INSERT...SELECT | View | |
|---|---|---|---|
| Stores rows? | Yes, in a new table. | Yes, in an existing table. | No, only the query. |
| Data is current as of | When it ran. | When it ran. | The moment you read it. |
| Read speed | Fast. Results are ready. | Fast. | Runs the query each time. |
| Use it for | Building 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.
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.
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.
| Task | PySpark | T-SQL | KQL |
|---|---|---|---|
| Filter rows | .filter(...) | WHERE | where. |
| Pick columns | .select(...) | SELECT | project. |
| Join a dimension | .join(..., "left") | LEFT JOIN | join kind=leftouter or lookup. |
| Group and aggregate | .groupBy().agg() | GROUP BY | summarize ... by. |
| Sort | .orderBy(...) | ORDER BY | sort by. |
Example: Trips | summarize revenue = sum(fare) by city_zone totals fares per zone.
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.
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.
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.
Worth keeping
- Denormalize with left joins from facts to dimensions, so no fact is dropped.
- groupBy plus agg in PySpark, GROUP BY in T-SQL, summarize in KQL.
- ROW_NUMBER with PARTITION BY and ORDER BY, then rn = 1, keeps one row per key.
- Always set .mode() when writing; overwrite for full loads, append for new rows.
- 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.
- [4]Microsoft Learn: Study Guide for Exam DP-700
- [35]Microsoft Learn: Dimensional modeling in Fabric Data Warehouse
- [36]Microsoft Learn: Dimensional modeling in Microsoft Fabric Warehouse: Dimension tables
- [63]Microsoft Learn: Use a notebook to load data into your lakehouse
- [66]Apache Spark documentation: Generic Load/Save Functions
- [87]Microsoft Learn: Lakehouse tutorial: Prepare and transform data in the lakehouse
- [88]Microsoft Learn: PySpark basics
- [90]Microsoft Learn: Window class
- [91]Microsoft Learn: row_number
- [92]Microsoft Learn: Ingest data into your Warehouse using Transact-SQL
- [93]Microsoft Learn: T-SQL surface area in Fabric Data Warehouse
- [94]Microsoft Learn: Analytical and aggregate functions in Fabric Data Warehouse
- [95]Microsoft Learn: KQL quick reference