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
The three languages: SQL, PySpark and KQL side by side
Read and write the basic queries the exam shows in SQL, PySpark and KQL.
By the end of this lesson you can
- Read a short T-SQL, PySpark or KQL query and say what it returns
- Say where each language runs in Fabric
- Read a Delta table into a DataFrame and write it back
- Spot the missing or wrong line in an exam snippet
From earlier lessons · 1 of 2
A Lakehouse comes with a SQL analytics endpoint. What can you do through that endpoint?
Pick one answer.
From earlier lessons · 2 of 2
Which Fabric item stores streaming, time-stamped events that you query with KQL?
Pick one answer.
Northwind Rides' ops lead asks a plain question: how many trips started at each station each day? The finance analyst answers in T-SQL from the Warehouse. The data engineer answers in PySpark from a Notebook. The real-time team answers in KQL from the Eventhouse. Three answers, three languages, and on the exam you must read all of them.
Three languages and where each one runs
Exam depth: know it cold.
- T-SQL (Transact-SQL) is Microsoft's dialect of SQL, the standard language for tables. You write it in the SQL query editor of a Warehouse or of a Lakehouse's SQL analytics endpoint.
- PySpark is Python code that drives Spark, the engine that splits big jobs across many machines. You write it in a Notebook.
- Spark SQL is SQL that runs on Spark. You also write it in a Notebook. Notebooks support four Spark languages: PySpark, Scala, Spark SQL and SparkR.
- KQL (Kusto Query Language) is a query language for time-stamped events. You write it in a KQL queryset, a Fabric item for writing and saving KQL queries, connected to a KQL database in an Eventhouse.
| Language | Where you write it | Can it change data? |
|---|---|---|
| T-SQL | Warehouse, SQL analytics endpoint | Yes in a Warehouse. No on the endpoint, which is read-only. |
| PySpark, Spark SQL | Notebook | Yes, Lakehouse tables and files. |
| KQL | KQL queryset, Eventhouse | A query only reads. Loading data uses separate commands. |
You'll ask all three the same question: how many trips started at each station on each day? Table trips has one row per ride, with station_id, start_time and duration_min. Table stations has station_id and station_name. Rides of 0 minutes are tests, so every query leaves them out. In KQL the columns use KQL's usual capitalized style: StationId, StartTime, DurationMin.
T-SQL: SELECT, WHERE, GROUP BY, JOIN
Exam depth: know it cold.
A T-SQL query is a set of clauses, each with one job:
SELECTlists the columns to return, including calculated ones.FROM ... JOIN ... ONnames the tables and how their rows match.WHEREkeeps only the rows that pass a test, before grouping.GROUP BYputs rows with the same values into one group.HAVINGkeeps only the groups that pass a test, after grouping.ORDER BYsorts the result.
An aggregate such as COUNT(*) or SUM() turns each group into one number. Every column in SELECT that isn't aggregated must also appear in GROUP BY.
Trap: a column alias, the name after AS such as trip_date, works in ORDER BY only. In WHERE, GROUP BY and HAVING you repeat the expression, which is why CAST(t.start_time AS DATE) appears twice below.
SELECT s.station_name,CAST(t.start_time AS DATE) AS trip_date,COUNT(*) AS tripsFROM dbo.trips AS tJOIN dbo.stations AS sON t.station_id = s.station_idWHERE t.duration_min > 0GROUP BY s.station_name, CAST(t.start_time AS DATE)ORDER BY trip_date, trips DESC;
You are a data engineer at Northwind Rides. A query selects station_id, CAST(start_time AS DATE) AS trip_date and COUNT(*) AS trips from dbo.trips, then ends with GROUP BY station_id, CAST(start_time AS DATE). It must return only station-days with more than 500 trips. Which line should you add at the end?
Pick one answer.
PySpark: DataFrames in a Notebook
Exam depth: know it cold.
In PySpark you work with a DataFrame: a table of rows and named columns that Spark spreads across many machines. You chain methods on it, one step per line:
filter(), or its twinwhere(), keeps matching rows.join(other, on=..., how=...)matches rows from two DataFrames.groupBy(...)forms groups, andagg(...)computes one value per group.select()picks columns,orderBy()sorts, andlimit(100)keeps the first 100 rows.
Helpers such as F.col, F.count and F.to_date come from pyspark.sql.functions, imported as F. Each method returns a new DataFrame. The original never changes.
from pyspark.sql import functions as Ftrips = spark.read.table("trips")stations = spark.read.table("stations")daily = (trips.filter(F.col("duration_min") > 0).join(stations, on="station_id", how="inner").groupBy("station_name", F.to_date("start_time").alias("trip_date")).agg(F.count("*").alias("trips")).orderBy("trip_date", F.desc("trips")))daily.write.mode("overwrite").saveAsTable("daily_station_trips")
Spark SQL: SQL inside a Notebook
Exam depth: recognize it.
Spark SQL lets you write SQL in a Notebook. Start a cell with the %%sql magic command, a first line that switches the cell's language, or pass a SQL string to spark.sql() from PySpark. It runs on Spark against Lakehouse tables, not on the Warehouse engine. It reads almost like T-SQL, with some functions of its own, such as to_date().
%%sqlSELECT station_name,to_date(start_time) AS trip_date,COUNT(*) AS tripsFROM tripsJOIN stations USING (station_id)WHERE duration_min > 0GROUP BY station_name, to_date(start_time)
Read and write Delta tables from a Notebook
Exam depth: know it cold.
Two lines do most of the work. Both use the notebook's default Lakehouse, the one attached to it.
spark.read.table("trips")reads a table into a DataFrame.df.write.mode("overwrite").saveAsTable("trips_clean")saves a DataFrame as a table in the Tables section.
You don't need .format("delta"). In Fabric, Spark's default table format is Delta, so saveAsTable writes Delta anyway.
The mode says what happens when the table already exists.
| Mode | If the table exists |
|---|---|
"overwrite" | Replace its contents. |
"append" | Add the new rows to it. |
"ignore" | Write nothing. Keep the old data. |
no mode ("errorifexists") | Fail with an error. |
daily = spark.read.table("trips_clean").groupBy("station_id").count()daily.write.saveAsTable("daily_station_trips")
You are a data engineer at Northwind Rides. The nightly notebook above ran fine on its first night. On the second night it fails because daily_station_trips already exists. Each run must replace the table. What should you add to the write?
Pick one answer.
KQL: the pipe, where and summarize
Exam depth: know it cold.
A KQL query starts with a table name. Each next line begins with a pipe, |, which hands the result of one step to the next. Picture a funnel: each step filters, reshapes or sums up what reaches it.
wherekeeps matching rows. Equality is==, andago(1d)means one day before now.projectpicks columns, andtake 100returns 100 rows.join kind=inner (OtherTable) on Keymatches rows from two tables.summarize Agg = count() by Col1, Col2groups and aggregates in one step. It returns one row per distinct combination of thebycolumns.bin(StartTime, 1d)groups times into 1-day buckets. Use1hfor hours.order by, or its twinsort by, sorts.
Steps run in order, so moving a line changes the result. To filter groups, as HAVING does in SQL, put a where after summarize. KQL is also case-sensitive: trips and Trips are different tables.
Trips| where DurationMin > 0| join kind=inner (Stations) on StationId| summarize Trips = count() by StationName, TripDate = bin(StartTime, 1d)| order by TripDate asc, Trips desc
You are a data engineer at Northwind Rides. This KQL query should count real trips per station, but it fails: Trips | summarize Trips = count() by StationName | where DurationMin > 0. You need to fix it with the smallest change. What should you do?
Pick one answer.
Match
Match each task to its spelling in T-SQL / PySpark / KQL
Pick an item on the left, then its match on the right.
Exam-style practice
trips = spark.read.table("trips")trips.filter(trips.duration_min > 0)trips.write.mode("overwrite").saveAsTable("trips_clean")
You are a data engineer at Northwind Rides. The notebook above runs without errors, but trips_clean still contains test rides with a duration of 0. You need to explain the cause. What is it?
Pick one answer.
You are a data engineer at Northwind Rides. Scooter battery readings stream into a KQL database in an Eventhouse. The ops team needs the average battery level per scooter for the last 15 minutes. You need to write this query. Where and in which language?
Pick one answer.
You are a data engineer at Northwind Rides. You need trips per station per hour for the last 24 hours from the Trips table. Which KQL query is correct?
Pick one answer.
Worth keeping
- T-SQL runs in a Warehouse or SQL analytics endpoint; PySpark and Spark SQL in a Notebook; KQL in a KQL queryset.
- WHERE filters rows before grouping; HAVING, or a KQL where after summarize, filters groups.
- PySpark methods return a new DataFrame, so assign every step.
- spark.read.table reads; saveAsTable writes Delta by default, and needs a mode if the table exists.
- KQL runs top to bottom through pipes, so line order changes the result.
You can now read what each engine says. But all three queries assumed the trips were already in Fabric. Next lesson: every way data gets in, and the one question that picks each tool.
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 (15)
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
- [33]Microsoft Learn: Lakehouse and Delta Lake tables
- [53]Microsoft Learn: Query the warehouse or SQL analytics endpoint
- [54]Microsoft Learn: Develop, execute, and manage Microsoft Fabric notebooks
- [55]Microsoft Learn: Query data in a KQL queryset
- [56]Microsoft Learn: Kusto Query Language overview
- [57]Microsoft Learn: summarize operator
- [58]Microsoft Learn: join operator
- [59]Microsoft Learn: innerunique join
- [60]Microsoft Learn: SQL to Kusto Query Language cheat sheet
- [61]Microsoft Learn: Work with data in a Spark dataframe
- [62]Microsoft Learn: How to read and write data with Pandas in Microsoft Fabric
- [63]Microsoft Learn: Use a notebook to load data into your lakehouse
- [64]Microsoft Learn: Microsoft Fabric decision guide: Choose between Warehouse and Lakehouse
- [66]Apache Spark documentation: Generic Load/Save Functions