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
18 min
Optimize eventstreams, eventhouses and queries
Keep real-time pipelines and queries fast with the right Eventhouse policies, views and KQL query choices.
By the end of this lesson you can
- Set the caching and retention policies that balance query speed and cost in an Eventhouse
- Choose a materialized view or OneLake availability for a real-time scenario
- Spot the KQL query that runs faster: early time filters, has instead of contains
- Cut load on an Eventstream with early filters and the right throughput level
From earlier lessons · 1 of 3
A Lakehouse table gets thousands of tiny Parquet files from frequent small writes, and reads keep slowing down. Which maintenance command fixes the file layout?
Pick one answer.
From earlier lessons · 2 of 3
A Warehouse feeds a dashboard that runs the same heavy SELECT every few minutes, and the data changes only once a night. Which setting helps most?
Pick one answer.
From earlier lessons · 3 of 3
A Spark notebook joins a 2 TB trips table to a 5 MB stations table. Which join strategy avoids shuffling the large table?
Pick one answer.
Northwind Rides' batch jobs are fast again, but the live scooter map isn't. Its Eventhouse query scans a whole month of trips to show the last hour. Two years of data sit in the hot cache, so the capacity bill keeps climbing. And every dashboard tile searches station names with contains. You have one afternoon to fix it.
Caching and retention: two different clocks
Exam depth: know it cold.
An Eventhouse stores streaming events in one or more KQL databases, which you query with KQL (Kusto Query Language). Each database has two policies, and each one sets a clock.
- Caching policy: how long data stays in the hot cache, the fast local disk (SSD) next to the compute. Queries on hot data are fast. Data older than that becomes cold: still there, still queryable, but slower to read and cheaper to keep.
- Retention policy: how long data stays at all. After that, the Eventhouse removes it automatically.
Both default to 3,650 days. Retention can be 1 to 36,500 days. The caching period must be lower than or equal to retention.
Example: retention 56 days, hot cache 28 days. The last 28 days are fast. You can still query all 56.
Where to set them: open the KQL database and select Manage > Data policies. You can also set both policies on a single table or materialized view with a command, such as .alter table Trips policy caching hot = 7d.
You are a data engineer at Northwind Rides. The ops team queries only the last 7 days of trip events. Auditors need a full year kept and queryable, even if slowly. You need to cut cost without slowing the ops queries. What should you set?
Pick one answer.
Materialized views: pay once for a common summary
Exam depth: know it cold.
A materialized view is one summarize query over a table that the Eventhouse keeps computed in the background. Dashboards read the stored result instead of re-aggregating millions of raw events.
It has two parts. The materialized part holds results already computed. The delta holds new rows not yet processed. Query the view by name and you get both, so results are always fresh.
Want more speed and can accept slight lag? Use materialized_view('ViewName'). It reads only the materialized part.
If you only ever need the summary, you can shorten the source table's retention. That lowers hot cache cost too.
.create materialized-view TripsPerStationHourly on table Trips{Trips| summarize trips = count() by StationId, bin(StartTime, 1h)}// Fastest read: materialized part only (may lag a little)materialized_view('TripsPerStationHourly')| where StartTime > ago(1d)
OneLake availability: let other engines read it
Exam depth: know it cold.
Eventhouse data isn't in OneLake as Delta tables by default. OneLake availability turns that on. It makes a logical copy of KQL database data in Delta Lake format, at no extra storage cost.
Then Spark notebooks, a Lakehouse shortcut, the Warehouse and Power BI in Direct Lake mode can all read it. Nobody builds a copy pipeline.
You turn it on per database or per table. The database's retention policy also applies to the OneLake copy.
While it's on, you can't rename tables, change a column's type, apply row-level security, or delete and purge data.
The Eventhouse waits so it can write big Parquet files, the column-based files under every Delta table. The default delay is up to 3 hours, and you can set 5 minutes to 3 hours.
You are a data engineer at Northwind Rides. Trip events land in an Eventhouse. The data science team wants to read the same trips from Spark notebooks in Delta format. You need to give them access without building or running a copy pipeline. What should you do?
Pick one answer.
KQL queries: filter early, scan less
Exam depth: know it cold.
A KQL query is as fast as the amount of data it reads. Every rule below reads less.
- Put
whereright after the table name. Filter before youextend,summarizeorjoin. - Filter on time first. Datetime columns have an efficient index. A filter like
where StartTime > ago(1h)can skip whole chunks of data without reading them. - Then filter on string terms, then numbers.
- Search one column, never
search *across all columns. - Prefer case-sensitive operators:
==over=~,inoverin~. - Try new queries with
takeorcountfirst, so you don't pull gigabytes by accident.
// Slow: calculates first, filters late, substring searchTrips| extend Hour = bin(StartTime, 1h)| where StationName contains "harbor"| where StartTime > ago(1d)| summarize trips = count() by Hour// Fast: time filter first, term search, then calculateTrips| where StartTime > ago(1d)| where StationName has "harbor"| summarize trips = count() by Hour = bin(StartTime, 1h)
has vs contains is the most tested string rule.
KQL breaks each string into terms: runs of letters and digits. It indexes every term of three or more characters.
has | contains | |
|---|---|---|
| Matches | a whole term | any substring. |
| Uses the term index | yes, so it's fast | no, it scans the column. |
"North Harbor Dock" with "harbor" | true | true. |
"KustoExplorerQueryRun" with "Explorer" | false | true. |
Decision rule: use has when you look for a whole word. Use contains only when you truly need part of a word.
Joins in KQL follow the same idea: shrink both sides first.
- Filter and
summarizeeach side before thejoin, so fewer rows meet. - Put the smaller table on the left of the join.
- Left side small (up to about 100 MB), right side large: add
hint.strategy=broadcast, which copies the small side to every node. - Right side small, left side large: use
lookupinstead ofjoin. - Filtering by one column only?
where StationId in (...)beats a join.
You are a data engineer at Northwind Rides. A KQL query must count today's trips at stations whose name includes the whole word "Harbor". It runs on billions of rows. Which query runs fastest?
Pick one answer.
Eventstream: send less, process less
Exam depth: recognize it.
The cheapest event is the one you never store. In the Eventstream editor, add a Filter to drop events you don't need. Add Manage fields to drop columns. Do this before the destination, with the Eventhouse destination in Event processing before ingestion mode.
The event throughput setting sizes the Eventstream for its traffic:
| Level | Incoming and outgoing traffic |
|---|---|
| Low | under 10 MB/s. |
| Medium | 10 to 100 MB/s. |
| High | over 100 MB/s. |
A higher level adds partitions, which are parallel lanes for events. It also raises the processor's starting cost, so pick the level the traffic needs.
An Eventstream keeps events for 1 day by default, up to 90 days.
Slow query? The fix depends on the engine
Exam depth: know it cold.
The exam mixes engines in one question. Match the fix to the engine that runs the query.
| Engine | First things to check |
|---|---|
| Warehouse or Lakehouse SQL analytics endpoint | result set caching (on by default), statistics, matching data types in joins, a slow first run on a cold cache. |
| Eventhouse (KQL) | hot cache covers the queried period, time filter first, has not contains, a materialized view for repeated summaries. |
| Lakehouse Delta tables (Spark or SQL) | small files fixed by OPTIMIZE, V-Order for read-heavy tables. |
Decision rule: name the engine first, then pick that engine's fix.
Exam-style practice
You are a data engineer at Northwind Rides. Forty ops staff watch a Real-Time Dashboard tile that counts trips per station per hour. Each refresh aggregates millions of raw events. Results a few minutes old are fine. You need to cut query cost. What should you do?
Pick one answer.
You are a data engineer at Northwind Rides. Scooters send 200 event types to an Eventstream. The Eventhouse needs only battery-fault events, and ingestion cost keeps rising. You need to reduce what gets ingested, with no code. What should you do?
Pick one answer.
You are a data engineer at Northwind Rides. A KQL database keeps 2 years of trips, with a 30-day hot cache. A monthly report on trips from 18 months ago runs slowly. Everything else is fast. What explains it?
Pick one answer.
Worth keeping
- Caching sets how much data is fast; retention sets how long data exists. Caching can't exceed retention.
- A materialized view stores a common summary; materialized_view() reads it fastest.
- OneLake availability shares Eventhouse tables as Delta with other engines, with no copy.
- In KQL, filter on time first, right after the table, and use has instead of contains for whole words.
- Cut Eventstream load with a Filter before the destination and the throughput level the traffic needs.
That's the last of the content: every skill area is now covered. Next lesson is about the exam itself. How many minutes per question, what to do with a yes/no series, and when opening Microsoft Learn mid-exam costs you points.
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 (16)
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
- [73]Microsoft Learn: Microsoft Fabric decision guide: choose a data store
- [132]Microsoft Learn: Add an Eventhouse destination to an eventstream
- [150]Microsoft Learn: Materialized views
- [264]Microsoft Learn: Performance guidelines in Fabric Data Warehouse
- [267]Microsoft Learn: Run Delta table maintenance in lakehouse
- [279]Microsoft Learn: Result set caching
- [280]Microsoft Learn: Change data policies
- [281]Microsoft Learn: Caching policy (hot and cold cache)
- [282]Microsoft Learn: Best practices for Kusto Query Language queries
- [283]Microsoft Learn: String operators
- [284]Microsoft Learn: Create and edit materialized views
- [285]Microsoft Learn: Turn on OneLake availability for an eventhouse
- [286]Microsoft Learn: Configure settings for a Fabric eventstream
- [287]Microsoft Learn: Capacity consumption for Microsoft Fabric eventstreams