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

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.

[4]

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.

[73][280][281]

The Retention and caching policies pane of a KQL database. Retention period is 3650 days, with a note of min 1 and max 36500 days. The Caching section is highlighted, with a caching period of 3650 days and the note that caching must be lower or equal to retention.
Microsoft Learn, CC BY 4.0

[280]

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.

[280][281]

[281]

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.

[280][281]

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.

[150][284]

kusto
.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)
Create an hourly trips-per-station view, then read only its materialized part.

[150][284]

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.

[73][285]

An Eventhouse database overview page. On the right, the Database details pane has a OneLake section where Availability is set to Disabled, with a toggle highlighted to turn it on.
Microsoft Learn, CC BY 4.0

[285]

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.

[285]

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.

  1. Put where right after the table name. Filter before you extend, summarize or join.
  2. 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.
  3. Then filter on string terms, then numbers.
  4. Search one column, never search * across all columns.
  5. Prefer case-sensitive operators: == over =~, in over in~.
  6. Try new queries with take or count first, so you don't pull gigabytes by accident.

[282]

kusto
// Slow: calculates first, filters late, substring search
Trips
| 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 calculate
Trips
| where StartTime > ago(1d)
| where StationName has "harbor"
| summarize trips = count() by Hour = bin(StartTime, 1h)
Same answer, very different cost.

[282][283]

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.

hascontains
Matchesa whole termany substring.
Uses the term indexyes, so it's fastno, it scans the column.
"North Harbor Dock" with "harbor"truetrue.
"KustoExplorerQueryRun" with "Explorer"falsetrue.

Decision rule: use has when you look for a whole word. Use contains only when you truly need part of a word.

[283]

[283]

Joins in KQL follow the same idea: shrink both sides first.

  • Filter and summarize each side before the join, 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 lookup instead of join.
  • Filtering by one column only? where StationId in (...) beats a join.

[282]

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.

[282][283]

contains reads every station name. has just checks the index and goes home early. Picture: Retro pixel art, 16-bit video game style, two side-by-side scenes.

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:

LevelIncoming and outgoing traffic
Lowunder 10 MB/s.
Medium10 to 100 MB/s.
Highover 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.

[48][132][286][287]

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.

EngineFirst things to check
Warehouse or Lakehouse SQL analytics endpointresult 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.

[264][267][279][281][282]

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.

[150]

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.

[48][132]

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.

[280][281]

FlashcardsIndex cardsFlashcards ⚡

Card 1 of 15

[150][280][281][282][283][285][286]

Worth keeping

  1. Caching sets how much data is fast; retention sets how long data exists. Caching can't exceed retention.
  2. A materialized view stores a common summary; materialized_view() reads it fastest.
  3. OneLake availability shares Eventhouse tables as Delta with other engines, with no copy.
  4. In KQL, filter on time first, right after the table, and use has instead of contains for whole words.
  5. 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.

  1. [4]Microsoft Learn: Study Guide for Exam DP-700
  2. [48]Microsoft Learn: Overview of Microsoft Fabric eventstreams
  3. [73]Microsoft Learn: Microsoft Fabric decision guide: choose a data store
  4. [132]Microsoft Learn: Add an Eventhouse destination to an eventstream
  5. [150]Microsoft Learn: Materialized views
  6. [264]Microsoft Learn: Performance guidelines in Fabric Data Warehouse
  7. [267]Microsoft Learn: Run Delta table maintenance in lakehouse
  8. [279]Microsoft Learn: Result set caching
  9. [280]Microsoft Learn: Change data policies
  10. [281]Microsoft Learn: Caching policy (hot and cold cache)
  11. [282]Microsoft Learn: Best practices for Kusto Query Language queries
  12. [283]Microsoft Learn: String operators
  13. [284]Microsoft Learn: Create and edit materialized views
  14. [285]Microsoft Learn: Turn on OneLake availability for an eventhouse
  15. [286]Microsoft Learn: Configure settings for a Fabric eventstream
  16. [287]Microsoft Learn: Capacity consumption for Microsoft Fabric eventstreams