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

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.

[4]

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.
LanguageWhere you write itCan it change data?
T-SQLWarehouse, SQL analytics endpointYes in a Warehouse. No on the endpoint, which is read-only.
PySpark, Spark SQLNotebookYes, Lakehouse tables and files.
KQLKQL queryset, EventhouseA 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.

[53][54][55][56][64]

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:

  • SELECT lists the columns to return, including calculated ones.
  • FROM ... JOIN ... ON names the tables and how their rows match.
  • WHERE keeps only the rows that pass a test, before grouping.
  • GROUP BY puts rows with the same values into one group.
  • HAVING keeps only the groups that pass a test, after grouping.
  • ORDER BY sorts 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.

sql
SELECT s.station_name,
CAST(t.start_time AS DATE) AS trip_date,
COUNT(*) AS trips
FROM dbo.trips AS t
JOIN dbo.stations AS s
ON t.station_id = s.station_id
WHERE t.duration_min > 0
GROUP BY s.station_name, CAST(t.start_time AS DATE)
ORDER BY trip_date, trips DESC;
Output: one row per station per day, such as Riverside | 2026-11-17 | 412. Test rides are gone. Within each date, the busiest station comes first.

[53]

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 twin where(), keeps matching rows.
  • join(other, on=..., how=...) matches rows from two DataFrames.
  • groupBy(...) forms groups, and agg(...) computes one value per group.
  • select() picks columns, orderBy() sorts, and limit(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.

[61]

python
from pyspark.sql import functions as F
trips = 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")
Output: the same rows as the T-SQL query. The last line saves them as a Delta table named daily_station_trips in the notebook's Lakehouse.

[61][62]

[61]

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

[54][62]

sql
%%sql
SELECT station_name,
to_date(start_time) AS trip_date,
COUNT(*) AS trips
FROM trips
JOIN stations USING (station_id)
WHERE duration_min > 0
GROUP BY station_name, to_date(start_time)
Output: the same trips per station per day, computed by Spark in a Notebook cell. Spark SQL accepts JOIN ... USING when both tables share the column name.

[54]

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.

ModeIf 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.

[33][62][63][66]

python
daily = spark.read.table("trips_clean").groupBy("station_id").count()
daily.write.saveAsTable("daily_station_trips")
Snippet for the next question. It worked the first time it ran.

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.

[33][66]

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.

  • where keeps matching rows. Equality is ==, and ago(1d) means one day before now.
  • project picks columns, and take 100 returns 100 rows.
  • join kind=inner (OtherTable) on Key matches rows from two tables.
  • summarize Agg = count() by Col1, Col2 groups and aggregates in one step. It returns one row per distinct combination of the by columns.
  • bin(StartTime, 1d) groups times into 1-day buckets. Use 1h for hours.
  • order by, or its twin sort 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.

[56][57][58][60]

kusto
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
Output: the same rows again, one per station per day. TripDate shows the start of each day, such as 2026-11-17T00:00:00Z.

[57][58]

A KQL queryset in Fabric. A four-line query summarizes bike counts by street and renders a column chart. The Run button and the Done status with the run time are highlighted.
Microsoft Learn, CC BY 4.0

[55]

[58][59]

KQL is a conveyor belt. Once summarize has boxed the rows, the next step can't see inside. Picture: Isometric diagram cartoon: a factory conveyor belt runs from top left to bottom right through three machines.

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.

[56][57]

Match

Match each task to its spelling in T-SQL / PySpark / KQL

Pick an item on the left, then its match on the right.

[60][61]

Three robots, one question. The answer is still 412 trips at Riverside. Picture: Three-panel comic strip, bright flat colors.

Exam-style practice

python
trips = spark.read.table("trips")
trips.filter(trips.duration_min > 0)
trips.write.mode("overwrite").saveAsTable("trips_clean")
Snippet for the next question.

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.

[61]

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.

[55][56]

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.

[57][60]

FlashcardsIndex cardsFlashcards ⚡

Card 1 of 15

[53][54][56][57][59][61][64][66]

Worth keeping

  1. T-SQL runs in a Warehouse or SQL analytics endpoint; PySpark and Spark SQL in a Notebook; KQL in a KQL queryset.
  2. WHERE filters rows before grouping; HAVING, or a KQL where after summarize, filters groups.
  3. PySpark methods return a new DataFrame, so assign every step.
  4. spark.read.table reads; saveAsTable writes Delta by default, and needs a mode if the table exists.
  5. 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.

  1. [4]Microsoft Learn: Study Guide for Exam DP-700
  2. [33]Microsoft Learn: Lakehouse and Delta Lake tables
  3. [53]Microsoft Learn: Query the warehouse or SQL analytics endpoint
  4. [54]Microsoft Learn: Develop, execute, and manage Microsoft Fabric notebooks
  5. [55]Microsoft Learn: Query data in a KQL queryset
  6. [56]Microsoft Learn: Kusto Query Language overview
  7. [57]Microsoft Learn: summarize operator
  8. [58]Microsoft Learn: join operator
  9. [59]Microsoft Learn: innerunique join
  10. [60]Microsoft Learn: SQL to Kusto Query Language cheat sheet
  11. [61]Microsoft Learn: Work with data in a Spark dataframe
  12. [62]Microsoft Learn: How to read and write data with Pandas in Microsoft Fabric
  13. [63]Microsoft Learn: Use a notebook to load data into your lakehouse
  14. [64]Microsoft Learn: Microsoft Fabric decision guide: Choose between Warehouse and Lakehouse
  15. [66]Apache Spark documentation: Generic Load/Save Functions