All 35 lessons
Start here: cloud, data, Fabric and Power BI 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. Power BI in plain words: semantic models, reports and DAX
Prepare data: get data in (exam area 2, 45-50%)
- 6. Connect to data and find it: connections, gateways, OneLake catalog and Real-Time hub
- 7. Choosing a data store: lakehouse, warehouse or eventhouse
- 8. Use data without copying: shortcuts, mirroring and OneLake integration
- 9. Ingest data: Dataflow Gen2, pipelines, Copy job and notebooks
- 10. Power Query in Dataflow Gen2: filter, data types, merge and new columns
- 11. Messy data: duplicates, missing values and nulls
Prepare data: transform and query (exam area 2)
Design and build semantic models (exam area 3, 25-30%)
- 18. Choose a storage mode: Import, DirectQuery, Direct Lake and Dual
- 19. Relationships: star schemas, bridge tables and many-to-many
- 20. DAX foundations: measures, filter context, CALCULATE and variables
- 21. DAX functions the exam tests: iterators, table filters, windows and information functions
- 22. Calculation groups, dynamic format strings and field parameters
- 23. Composite models and large semantic model storage format
Optimize models, then secure them (exam areas 3 and 1)
- 24. Direct Lake in depth: fallback, refresh and the two flavors
- 25. Incremental refresh for semantic models
- 26. Faster reports and DAX: finding and fixing slow queries
- 27. Who can see what: workspace roles and item permissions
- 28. Fine-grained security: rows, columns, objects and files
- 29. Governance: sensitivity labels and endorsement
Lifecycle, then exam practice (exam area 1)
20 min
Query with KQL
Select, filter and aggregate data in an eventhouse with KQL.
By the end of this lesson you can
- Name where KQL runs in Fabric: KQL queryset, KQL database and Real-Time Dashboard
- Read and complete KQL that filters with where, ago() and between
- Aggregate with summarize ... by and bin(), and join a lookup table
- Translate a SQL query into KQL and spot operator-order bugs
From earlier lessons · 1 of 2
Which SQL clause filters groups after GROUP BY?
Pick one answer.
From earlier lessons · 2 of 2
Revenue is 700, 700 and 500 in one region. What does RANK() give, ordered by revenue descending?
Pick one answer.
Loyalty-app events now stream into Juniper Coffee's Eventhouse, about 3,000 an hour from 120 cafés. Marketing wants scans per store per hour this morning, unique customers per store, and redemptions by region. The data sits in a KQL database, and your SQL habits only half work there.
The data in this lesson. A tiny slice of the LoyaltyEvents table, all on 2026-11-19 (times are UTC).
| Timestamp | StoreId | CustomerId | Points | EventType. |
|---|---|---|---|---|
| 08:02 | 12 | C1 | 10 | Scan. |
| 08:07 | 12 | C2 | 10 | Scan. |
| 08:14 | 31 | C1 | -50 | Redeem. |
| 08:21 | 12 | C1 | 20 | Scan. |
| 09:03 | 31 | C3 | 10 | Scan. |
A small Stores table maps StoreId 12 to Harbour St in North, and 31 to Mill Lane in South.
Where you run KQL in Fabric
Exam depth: recognize it.
KQL (Kusto Query Language) is the query language of a KQL database, the database inside an Eventhouse. You run it in three places.
- KQL queryset: an item for writing, running and saving queries. It has tabs, and each tab can point at a different database. It also accepts many SQL functions.
- The KQL database itself: every KQL database comes with a built-in query environment for quick exploration.
- Real-Time Dashboard: a page of tiles, and each tile runs one KQL query. From a queryset, Save to dashboard turns a query into a tile.
Decision rule: explore and save queries in a queryset; share live visuals on a Real-Time Dashboard.
The pipe: a table, then operators in order
Exam depth: know it cold.
A KQL query starts with a table name. Each pipe | passes the rows on to the next operator, and operators run top to bottom.
| Operator | What it does. |
|---|---|
where | Keeps rows that match a condition. Equals is ==. |
project | Keeps only the columns you list, and can compute new ones. |
extend | Adds a calculated column and keeps all the others. |
sort by | Sorts rows. order by is the same operator. |
top 5 by x | The first 5 rows by x. Same as sort, then take. |
take 5 | Any 5 rows, with no order. For a quick look only. |
Two defaults surprise SQL users. sort and top are descending unless you write asc. And take returns arbitrary rows, so it's never "the latest".
LoyaltyEvents| where Timestamp between (datetime(2026-11-19 08:00:00) .. datetime(2026-11-19 08:59:59))| where EventType == "Scan"| extend Bonus = Points * 2| project Timestamp, StoreId, CustomerId, Bonus| sort by Timestamp asc
You are a data analyst at Juniper Coffee. Marketing wants the 3 most recent redemptions from LoyaltyEvents. Which query returns them?
Pick one answer.
Time filters: ago(), between and datetime()
Exam depth: know it cold.
Event data is filtered by time in almost every query.
ago(1h)is the current UTC time minus one hour.where Timestamp > ago(1h)keeps the last hour. Other units:30m,1d,7d.datetime(2026-11-19)ordatetime(2026-11-19 08:00:00)is a fixed point in time, in UTC.between (a .. b)keeps values fromatob, and both ends are included. The right side can also be a length of time:between (datetime(2026-11-19) .. 1d).
Put the time filter first. It cuts the most rows before the other operators run.
You are a data analyst at Juniper Coffee. A tile must show loyalty scans from the last 2 hours only. Which filter is right?
Pick one answer.
summarize ... by, and bin() for time buckets
Exam depth: know it cold.
summarize is KQL's GROUP BY. It returns one row per distinct combination of the by columns. With no by, you get one row in total.
Common aggregations are count(), sum(x), avg(x), min(x), max(x) and dcount(x). dcount returns an estimate of the number of distinct values, fast even on huge tables.
Name each result: Scans = count(). Without a name you get one made from the expression, such as count_ or sum_Points.
bin(Timestamp, 1h) rounds each time down to the start of its hour, so 08:02 and 08:21 both become 08:00. Group by it for a count per hour. You must bin a datetime yourself; KQL no longer bins by the hour for you. An hour with no rows simply doesn't appear in the result.
LoyaltyEvents| where EventType == "Scan"| summarize Scans = count(), Customers = dcount(CustomerId)by StoreId, Hour = bin(Timestamp, 1h)| sort by StoreId asc, Hour asc
You are a data analyst at Juniper Coffee. This query should return scans per store per hour, but it returns one row per store: LoyaltyEvents | where EventType == "Scan" | summarize Scans = count() by StoreId. What should you change?
Pick one answer.
Join a lookup table, then render a chart
Exam depth: know it cold.
join matches rows from two tables on a key: on StoreId when both columns share a name, or on $left.StoreId == $right.Id when they don't.
Mind the default. With no kind, KQL uses innerunique: it removes duplicate keys from the left table, then does an inner join. Write kind=inner for a standard inner join, or kind=leftouter to keep every left row.
render draws the result as a chart, such as timechart or columnchart. It must be the last operator. A timechart needs a datetime column for its x-axis.
LoyaltyEvents| where EventType == "Redeem"| summarize Redemptions = count() by StoreId| join kind=leftouter (Stores | project StoreId, Region) on StoreId| summarize Redemptions = sum(Redemptions) by Region| render columnchart
SQL to KQL in one table
Exam depth: know it cold.
| SQL | KQL. |
|---|---|
SELECT TOP 10 * | take 10. |
SELECT a, b | project a, b. |
WHERE x = 'A' | where x == "A". |
| A computed column | extend name = .... |
GROUP BY with aggregates | summarize agg() by col. |
HAVING | where after summarize. |
COUNT(DISTINCT x) | dcount(x), an estimate. |
ORDER BY x ASC | sort by x asc. |
TOP 10 ... ORDER BY x DESC | top 10 by x. |
LEFT OUTER JOIN | join kind=leftouter. |
The explain keyword (recognize it): in a KQL queryset, type -- on one line, explain on the next, then a SQL SELECT. The output is the same query written in KQL.
Materialized views, update policies and windows
Exam depth: recognize it. One line each.
- Materialized view: stores the result of one
summarizeover a table and keeps it current in the background, so repeated aggregations run fast and still include new rows. - Update policy: runs a query each time data lands in a source table, and writes the transformed rows to another table.
- Time windows:
summarize ... by bin(Timestamp, 5m)gives back-to-back buckets that never overlap. Streaming tools call that a tumbling window; the other window types belong to Eventstream and the data engineer exam.
Try it in Fabric
On a free trial, with any KQL database that holds data:
- Open a KQL queryset in your workspace.
- In Explorer, open the database switcher and select Add data source > Eventhouse / KQL Database. Pick a database in the OneLake catalog window and select Connect.
- Type a query in the top pane, such as a table name followed by
| take 10. - Select Run, or press Shift+Enter. The results appear in the grid below.
- Select Save to dashboard to turn the query into a Real-Time Dashboard tile.
Exam-style practice
You are a data analyst at Juniper Coffee. Marketing wants scans per store per hour for the last day. Your query ends with | summarize Scans = count() by StoreId, bin(Timestamp, 1d) and returns one row per store. What should you change?
Pick one answer.
You are a data analyst at Juniper Coffee. You need stores with more than 100 scans in the last 24 hours. Every option starts with LoyaltyEvents | where EventType == "Scan". Which ending is right?
Pick one answer.
You are a data analyst at Juniper Coffee. Your KQL query shows scans per store per hour. Store managers want it as a live visual next to other tiles, with no semantic model to build. What should you do?
Pick one answer.
Worth keeping
- Write and save KQL in a queryset; share live tiles on a Real-Time Dashboard.
- Operators run in order: filter first, and remember sort and top default to descending.
- where Timestamp > ago(1h) for recent data; between includes both ends.
- summarize ... by bin(Timestamp, 1h) counts per hour; where after summarize is HAVING.
- Join with an explicit kind, and put render last.
SQL and KQL are done. The last query language on the exam runs against a semantic model, not a table. Next lesson: DAX query view and EVALUATE.
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 (20)
Each fact in the lesson is marked with the number of the page it comes from.
- [6]Microsoft Learn: Study guide for Exam DP-600: Implementing Analytics Solutions Using Microsoft Fabric
- [39]Microsoft Learn: Eventhouse overview
- [173]Microsoft Learn: Query data in a KQL queryset
- [174]Microsoft Learn: Create a Real-Time Dashboard
- [175]Microsoft Learn: KQL quick reference
- [176]Microsoft Learn: Tutorial: Learn common operators
- [177]Microsoft Learn: take operator
- [178]Microsoft Learn: sort operator
- [179]Microsoft Learn: top operator
- [180]Microsoft Learn: ago()
- [181]Microsoft Learn: between operator
- [182]Microsoft Learn: The datetime data type
- [183]Microsoft Learn: summarize operator
- [184]Microsoft Learn: bin()
- [185]Microsoft Learn: Tutorial: Use aggregation functions
- [186]Microsoft Learn: join operator
- [187]Microsoft Learn: render operator
- [188]Microsoft Learn: SQL to Kusto Query Language cheat sheet
- [189]Microsoft Learn: Materialized views
- [190]Microsoft Learn: Update policy overview