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

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.

[4]

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.

OperatorWhat it does
whereKeeps rows that match a condition.
projectKeeps only the columns you list, and can compute new ones.
extendAdds a calculated column and keeps all the others.
summarizeGroups rows and computes counts, sums or averages per group.
top, sortOrders 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.

[95][142]

kusto
TripEvents
| where Timestamp > ago(1h)
| where EventType == "TripStart"
| extend BatteryBand = iff(BatteryPct < 20, "low", "ok")
| project Timestamp, ScooterId, StationId, BatteryBand
| take 10
Last hour of trip starts. iff() picks a value by condition, and extend adds it as a column.

[142]

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.

[57][65][143]

kusto
TripEvents
| where Timestamp > ago(6h) and EventType == "TripStart"
| summarize Trips = count() by bin(Timestamp, 5m), StationId
| render timechart
Trip starts per station in 5-minute buckets over the last 6 hours, drawn as a line chart.

[143]

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.

[57][65]

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.

[58][144]

kusto
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
Add each station's name to the 5-minute counts. kind=leftouter keeps counts with no matching station.

[144]

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.

WindowShapeEach event belongs to
TumblingFixed size, back to back, no overlap.Exactly one window.
HoppingFixed size, moves forward by a hop smaller than its size.One or more windows.
SlidingFixed size; output only when an event enters or leaves.One or more windows.
SessionStarts at an event, closes after a timeout with no events.One session.
SnapshotGroups 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.

[145][146]

Rendering diagram…
One 20-minute timeline. Tumbling bars never overlap; hopping and sliding bars do; sessions stretch to fit bursts of events.

[145]

Match

Match each Northwind Rides requirement to its window type

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

Tumbling windows: never overlap. Hopping windows: overlap on purpose. Session windows: leave when the room goes quiet. Picture: Retro pixel art, three side-by-side 16-bit panels on a dark blue background.

Where to window: Eventstream, Spark or KQL

Exam depth: know it cold.

You can window in three places.

WhereHowPick it when
EventstreamGroup by transformation with a time window, or the SQL operator.You want results computed in flight, with no code.
Spark structured streaminggroupBy(window(...)) or session_window(...), plus withWatermark.The team writes PySpark and lands results in a Lakehouse.
KQLsummarize ... 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.

[48][65][102][146][147]

python
from pyspark.sql import functions as F
trip_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")
Spark: 5-minute tumbling counts per station. Adding a third argument, a slide, makes overlapping windows.

[102]

[102][145]

sql
SELECT StationId, System.Timestamp() AS WindowEnd, COUNT(*) AS Trips
FROM TripStream TIMESTAMP BY EventTime
GROUP BY StationId, HoppingWindow(minute, 10, 1)
-- Others: TumblingWindow(minute, 5)
-- SlidingWindow(minute, 5)
-- SessionWindow(minute, 10, 60)
Eventstream SQL operator syntax: the window goes in GROUP BY, and System.Timestamp() returns each window's end time.

[145][146]

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.

[145][147]

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.

[140][150][151]

kusto
.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}]'
A materialized view of trips per station per 5 minutes, and an update policy that runs a stored function, CleanTripEvents(), on each new batch.

[140][151]

Try it in Fabric

Use an eventstream with the Bicycles sample data, on a free trial.

  1. Open the eventstream and select Edit on the ribbon.
  2. Hover over the line between the stream and the destination. Select +, then Group by.
  3. Select the edit icon. In the Aggregations section, choose Count and select Add.
  4. Set Group aggregation by to Street.
  5. Set Time window to Tumbling and Duration to 1 Minute. Select Save.
  6. Select Refresh on the lower pane to see the test result.
  7. Select Publish to save the logic and return to Live view.

[133][148][149]

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.

[58]

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.

[48][147]

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.

[150][151]

FlashcardsIndex cardsFlashcards ⚡

Card 1 of 15

[48][58][65][102][142][145][151]

Worth keeping

  1. KQL runs operators in order: where filters, project picks columns, extend adds them, summarize groups.
  2. summarize count() by bin(Timestamp, 5m) is the classic per-bucket count.
  3. Tumbling never overlaps, hopping overlaps on a schedule, sliding fires on events, session ends after quiet.
  4. Window in flight with Eventstream, in code with Spark, or after landing with KQL.
  5. 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.

[48][140][149][150]

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.

  1. [4]Microsoft Learn: Study Guide for Exam DP-700
  2. [48]Microsoft Learn: Overview of Microsoft Fabric eventstreams
  3. [57]Microsoft Learn: summarize operator
  4. [58]Microsoft Learn: join operator
  5. [65]Microsoft Learn: bin()
  6. [95]Microsoft Learn: KQL quick reference
  7. [102]Microsoft Learn: Stateful processing with Structured Streaming
  8. [133]Microsoft Learn: Process event data by using the event processing editor
  9. [140]Microsoft Learn: Update policy overview
  10. [142]Microsoft Learn: Tutorial: Learn common operators
  11. [143]Microsoft Learn: Tutorial: Use aggregation functions
  12. [144]Microsoft Learn: Tutorial: Join data from multiple tables
  13. [145]Microsoft Learn: Introduction to Stream Analytics windowing functions
  14. [146]Microsoft Learn: Windowing functions (Azure Stream Analytics and Fabric Eventstream)
  15. [147]Microsoft Learn: Microsoft Fabric Eventstream for Azure Event Hubs and Stream Analytics users
  16. [148]Microsoft Learn: Tutorial: Explore and transform sample bike-sharing data with Eventstream for KQL Database integration
  17. [149]Microsoft Learn: Monitor eventstream for stopped events
  18. [150]Microsoft Learn: Materialized views
  19. [151]Microsoft Learn: Materialized views use cases