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
22 min
KQL and windowing functions for real-time data
Query streaming data with KQL and group events into time windows.
By the end of this lesson you can
- Read and complete KQL queries that use where, project, extend, summarize and join
- Count events per time bucket with summarize and bin()
- Tell tumbling, hopping, sliding, session and snapshot windows apart
- Pick where to window: Eventstream, Spark or KQL
From earlier lessons · 1 of 3
Which Fabric item captures live events and routes them to destinations such as an Eventhouse, with no code?
Pick one answer.
From earlier lessons · 2 of 3
In Spark structured streaming, what lets a restarted query carry on where it stopped?
Pick one answer.
From earlier lessons · 3 of 3
Streaming scooter events must be queryable within seconds, mostly by time. Where should they land?
Pick one answer.
Scooter trip events now flow into Northwind Rides' Eventhouse, about 200 a minute. The ops lead wants a chart of trip starts per station, in 5-minute buckets. She also wants to know how long each rider stays active before going quiet. Both questions are about time windows. You need to write the query, and decide where the windowing should happen.
KQL reads like a pipeline
Exam depth: know it cold.
KQL (Kusto Query Language) is the query language of KQL databases. A query starts with a table name. Each pipe | passes the rows to the next operator.
| Operator | What it does |
|---|---|
where | Keeps rows that match a condition. |
project | Keeps only the columns you list, and can compute new ones. |
extend | Adds a calculated column and keeps all the others. |
summarize | Groups rows and computes counts, sums or averages per group. |
top, sort | Orders rows, or takes the first n by a column. |
ago(1h) means one hour before the query runs. Operators run in order, so top before where gives a different answer than where before top.
Trap: project drops every column you don't name. A later line that uses a dropped column fails.
TripEvents| where Timestamp > ago(1h)| where EventType == "TripStart"| extend BatteryBand = iff(BatteryPct < 20, "low", "ok")| project Timestamp, ScooterId, StationId, BatteryBand| take 10
Count events per 5 minutes: summarize and bin()
Exam depth: know it cold.
summarize returns one row per distinct combination of its by columns. bin(value, size) rounds a value down to a multiple of the size. So bin(Timestamp, 5m) turns 10:03 and 10:04 into 10:00.
Put them together and you get a count per 5-minute bucket: summarize count() by bin(Timestamp, 5m). That's a tumbling window over stored data.
Two details the exam likes. First, you must bin a datetime yourself; automatic hourly bins aren't supported. Second, a bucket with no rows doesn't appear at all, so a chart can show gaps.
TripEvents| where Timestamp > ago(6h) and EventType == "TripStart"| summarize Trips = count() by bin(Timestamp, 5m), StationId| render timechart
You are a data engineer at Northwind Rides. This query returns one row per event, not one per 5 minutes:
TripEvents | summarize count() by Timestamp, StationId
You need one row per station per 5 minutes. What should you change?
Pick one answer.
Joining tables in KQL
Exam depth: know it cold.
join matches rows from two tables on a key column. Write on StationId when both columns share a name. When names differ, write on $left.StationId == $right.Id.
The trap is the default. With no kind, KQL uses innerunique. It removes duplicate keys from the left table first, then does an inner join. For a standard inner join, write kind=inner. Other kinds include leftouter and leftanti.
For speed, put the smaller table on the left side of a join.
TripEvents| where Timestamp > ago(1h)| summarize Trips = count() by bin(Timestamp, 5m), StationId| join kind=leftouter (Stations | project StationId, StationName) on StationId| project Timestamp, StationName, Trips
The five window types
Exam depth: know it cold.
A window groups a stream of events by time so you can aggregate them. Every window emits its result at the end of the window.
| Window | Shape | Each event belongs to |
|---|---|---|
| Tumbling | Fixed size, back to back, no overlap. | Exactly one window. |
| Hopping | Fixed size, moves forward by a hop smaller than its size. | One or more windows. |
| Sliding | Fixed size; output only when an event enters or leaves. | One or more windows. |
| Session | Starts at an event, closes after a timeout with no events. | One session. |
| Snapshot | Groups events with the exact same timestamp. | One snapshot. |
A hopping window whose hop equals its size is a tumbling window. A session window also takes a maximum duration and an optional partition key, such as ScooterId.
Match
Match each Northwind Rides requirement to its window type
Pick an item on the left, then its match on the right.
Where to window: Eventstream, Spark or KQL
Exam depth: know it cold.
You can window in three places.
| Where | How | Pick it when |
|---|---|---|
| Eventstream | Group by transformation with a time window, or the SQL operator. | You want results computed in flight, with no code. |
| Spark structured streaming | groupBy(window(...)) or session_window(...), plus withWatermark. | The team writes PySpark and lands results in a Lakehouse. |
| KQL | summarize ... by bin(Timestamp, 5m) on stored events. | The raw events already sit in an Eventhouse. |
In Spark, a watermark tells Spark how late an event may arrive and still count. Decision rule: window in flight with Eventstream, in code with Spark, or after landing with KQL.
from pyspark.sql import functions as Ftrip_counts = (trips.withWatermark("event_time", "10 minutes").groupBy(F.window("event_time", "5 minutes"), F.col("station_id")).count())# 10-minute windows that start every minute:# F.window("event_time", "10 minutes", "1 minute")
SELECT StationId, System.Timestamp() AS WindowEnd, COUNT(*) AS TripsFROM TripStream TIMESTAMP BY EventTimeGROUP BY StationId, HoppingWindow(minute, 10, 1)-- Others: TumblingWindow(minute, 5)-- SlidingWindow(minute, 5)-- SessionWindow(minute, 10, 60)
You are a data engineer at Northwind Rides. Every minute, a dashboard tile must show trip starts over the previous 10 minutes. The team uses the Eventstream Group by transformation. You need to choose the window. What should you use?
Pick one answer.
Materialized views and update policies
Exam depth: recognize it.
A materialized view stores the result of one summarize over a source table. A background process keeps it up to date. A query combines the stored part with new rows not yet processed, so results are always fresh and faster than aggregating raw data.
An update policy runs a query each time data lands in a source table. It writes the transformed rows to a target table in the same database.
Decision rule: aggregations go in a materialized view; per-row cleanup or enrichment at ingestion goes in an update policy.
.create materialized-view TripsPer5Min on table TripEvents{TripEvents| summarize Trips = count() by StationId, bin(Timestamp, 5m)}.alter table CleanTrips policy update@'[{"IsEnabled": true, "Source": "TripEvents", "Query": "CleanTripEvents()", "IsTransactional": false}]'
Try it in Fabric
Use an eventstream with the Bicycles sample data, on a free trial.
- Open the eventstream and select Edit on the ribbon.
- Hover over the line between the stream and the destination. Select +, then Group by.
- Select the edit icon. In the Aggregations section, choose Count and select Add.
- Set Group aggregation by to Street.
- Set Time window to Tumbling and Duration to 1 Minute. Select Save.
- Select Refresh on the lower pane to see the test result.
- Select Publish to save the logic and return to Live view.
Exam-style practice
You are a data engineer at Northwind Rides. A query runs Faults | join Repairs on ScooterId. Some scooters have many faults, but the result shows only one fault row each. You need every matching fault row. What should you do?
Pick one answer.
You are a data engineer at Northwind Rides. The ops team writes no code. They need trip counts per station in 1-minute windows stored in the Eventhouse, instead of every raw event. You need the least effort. What should you use?
Pick one answer.
You are a data engineer at Northwind Rides. Dashboards keep running the same hourly trip count over billions of raw events, and queries are slow. Results must still include the newest events. You need faster queries with little upkeep. What should you create?
Pick one answer.
Worth keeping
- KQL runs operators in order: where filters, project picks columns, extend adds them, summarize groups.
- summarize count() by bin(Timestamp, 5m) is the classic per-bucket count.
- Tumbling never overlaps, hopping overlaps on a schedule, sliding fires on events, session ends after quiet.
- Window in flight with Eventstream, in code with Spark, or after landing with KQL.
- Materialized views hold aggregations; update policies transform rows at ingestion.
On the job
At Northwind Rides, the raw trip events land in an Eventhouse table untouched. An update policy parses each new batch into a clean table with typed columns.
A materialized view on the clean table keeps trips per station per 5 minutes. The ops dashboard reads that view, so tiles load fast even after months of data.
A second path in the Eventstream uses Group by with a 1-minute tumbling window. It sends one small count per station to Activator, which alerts when a busy station suddenly reports zero trips. Sending counts instead of every event keeps the alert path cheap.
Your Spark windowing notebook works, but each run waits three minutes for a session to start, and a teammate's job keeps failing with a capacity error. Next lesson: the workspace settings that decide both.
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 (19)
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
- [48]Microsoft Learn: Overview of Microsoft Fabric eventstreams
- [57]Microsoft Learn: summarize operator
- [58]Microsoft Learn: join operator
- [65]Microsoft Learn: bin()
- [95]Microsoft Learn: KQL quick reference
- [102]Microsoft Learn: Stateful processing with Structured Streaming
- [133]Microsoft Learn: Process event data by using the event processing editor
- [140]Microsoft Learn: Update policy overview
- [142]Microsoft Learn: Tutorial: Learn common operators
- [143]Microsoft Learn: Tutorial: Use aggregation functions
- [144]Microsoft Learn: Tutorial: Join data from multiple tables
- [145]Microsoft Learn: Introduction to Stream Analytics windowing functions
- [146]Microsoft Learn: Windowing functions (Azure Stream Analytics and Fabric Eventstream)
- [147]Microsoft Learn: Microsoft Fabric Eventstream for Azure Event Hubs and Stream Analytics users
- [148]Microsoft Learn: Tutorial: Explore and transform sample bike-sharing data with Eventstream for KQL Database integration
- [149]Microsoft Learn: Monitor eventstream for stopped events
- [150]Microsoft Learn: Materialized views
- [151]Microsoft Learn: Materialized views use cases