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
20 min
Optimize Spark, pipelines and warehouses
Speed up Spark jobs, pipelines and warehouse queries with the settings the exam names.
By the end of this lesson you can
- Choose the Spark setting that makes a notebook start or run faster
- Tune a Copy activity and a ForEach loop for throughput
- Speed up Warehouse queries with statistics, data types and caching
- Map a performance symptom to its fix
From earlier lessons · 1 of 2
A Delta table has thousands of small files and reads are slow. Which command fixes the file count?
Pick one answer.
From earlier lessons · 2 of 2
Which reader gains the most from V-Order on a Spark-written Lakehouse table?
Pick one answer.
Northwind Rides' nightly run should finish by 6 a.m. It finishes at 8. Each of 12 notebooks waits for its own Spark session. The payments copy from SQL Server takes two hours on default settings. And the ops dashboard's main Warehouse query scans every column. Nothing fails. Everything is just slow.
Spark: start sessions faster
Exam depth: know it cold.
A Spark session is the cluster your notebook runs on. Starting one can take seconds or minutes.
- Starter pool: prewarmed Medium nodes kept ready by Fabric. With no extra libraries, a session starts in 5-10 seconds. Custom libraries add 30 seconds to 5 minutes. A custom node size, Private Links or a managed virtual network means on-demand start: 2-5 minutes.
- Autoscale and dynamic allocation of executors (the worker processes that run tasks): on by default in starter pools. The pool grows and shrinks with the job.
- High concurrency mode: several notebooks share one running session, so only the first one waits. They must have the same user, default Lakehouse and Spark compute settings. Up to 5 notebooks share a session by default. For pipelines, turn on For pipeline running multiple notebooks in Workspace settings > Data Engineering/Science > Spark settings > High concurrency. A session tag on the Notebook activity groups which notebooks share.
Spark: run jobs faster
Exam depth: know it cold.
- Native execution engine: runs Spark queries in a faster C++ engine with no code changes and no extra cost. It works best on Parquet and Delta. Unsupported parts, such as Python UDFs (your own row-by-row functions), fall back to normal Spark automatically.
- Intelligent cache: on by default. It keeps file data on each node's disk, so repeated reads skip remote storage.
- Broadcast join: copies a small lookup table to every node. The big table then needs no shuffle, which is moving data between nodes over the network.
- Avoid
collect()andtoPandas()on big data: they pull every row into the driver, the one node that coordinates the job. It runs out of memory. Write the result instead. - Cache only what you reuse:
cache()a DataFrame (a table held in Spark) used more than once, thenunpersist()it.
from pyspark.sql.functions import broadcasttrips = spark.read.table("trips") # large fact tablestations = spark.read.table("stations") # small lookup table# Broadcast the small table: no shuffle of tripsjoined = trips.join(broadcast(stations), "station_id")# Avoid on large data: every row lands in driver memory# rows = joined.collect()# Better: write the result, or look at a few rowsjoined.write.mode("overwrite").saveAsTable("trips_enriched")joined.limit(20).show()
Try it in Fabric
Turn on the native execution engine for every notebook that uses an environment. An environment is a Fabric item that holds Spark settings and libraries.
- Open the workspace and select your environment.
- Under Spark compute, select Acceleration.
- Check Enable native execution engine.
- Select Save, then Publish.
For one notebook only, put %%configure with "spark.native.enabled": "true" in its first cell.
You are a data engineer at Northwind Rides. A pipeline runs 12 notebooks in a ForEach loop. They all use the same environment and default Lakehouse. Most of the run time is spent starting a Spark session for each notebook. You need to cut that start time. What should you do?
Pick one answer.
Pipelines: copy faster
Exam depth: know it cold.
The Copy activity's Settings tab holds the speed controls.
- Intelligent throughput optimization: how much CPU, memory and network the copy may use. Choose Auto, Standard, Balanced, Maximum, or a value from 4 to 256.
- Degree of copy parallelism (parallel copy): the maximum number of threads reading and writing at once. Auto by default. Set a limit if the source database gets overloaded.
- Partition option on a SQL source: Physical partitions of table or Dynamic range split the read into parallel queries.
- Staging: an interim store the data passes through. A Copy into a Warehouse needs it when the source isn't in a format the Warehouse's
COPYcommand reads directly.
For many tables, use a ForEach loop around one parameterized Copy. Clear Sequential, then set Batch count: default 20, maximum 50 at once.
You are a data engineer at Northwind Rides. A Copy activity loads 1.5 billion payment rows from an Azure SQL Database into a Lakehouse. With default settings it reads through one connection and takes two hours. You need it much faster without overloading the source. What should you do?
Pick one answer.
Warehouse: faster queries
Exam depth: know it cold.
- Statistics: summaries of a column's values that the query optimizer uses to pick a plan. The Warehouse creates and updates them automatically. After a big load, run
UPDATE STATISTICSin a quiet window, so queries don't wait for it. Only single-column statistics can be created by hand. - Data types: use the smallest type that fits. Prefer
varchar(100)overvarchar(max),intoverdecimal(18,0), anddateover text dates. Give join columns the same type on both sides. UseNOT NULLwhere you can. - Return less data:
SELECT *can be costly. Name the columns you need and filter early, before joins. - Don't judge the first run: the first run of a query can be slower while data is cached. A nonzero
data_scanned_remote_storage_mbin query insights shows this cold start.
Result set caching saves the final result of a SELECT. When the same query runs again and the data hasn't changed, the stored result comes back without recomputing. It's on by default for Warehouses and SQL analytics endpoints. It suits dashboards that repeat the same heavy query.
Some queries don't qualify. Examples: a result over 10,000 rows, GETDATE(), or a query inside an explicit transaction. Any change to the tables also invalidates the cache.
To check, look at result_cache_hit in queryinsights.exec_requests_history: 2 means the cache was used, 1 means created, 0 means not eligible.
-- One-time: single-column statistics on a join columnCREATE STATISTICS stats_station_id ON dbo.FactTrips (station_id);-- After the nightly load, in a quiet windowUPDATE STATISTICS dbo.FactTrips (stats_station_id);-- Did repeat runs use result set caching? (2 = hit)SELECT TOP 20 result_cache_hit, commandFROM queryinsights.exec_requests_historyORDER BY submit_time DESC;
You are a data engineer at Northwind Rides. A Warehouse query joins FactTrips to DimStation on station_id. FactTrips stores it as varchar(8000) and DimStation as int. Estimates are poor and the join is slow. You need a lasting fix. What should you do?
Pick one answer.
Symptom-to-fix table
Decision rule: name the symptom, then pick the one fix that targets it.
| Symptom | Fix |
|---|---|
| Notebook waits minutes to start. | Starter pool with default settings, no extra libraries. |
| Many notebooks each wait to start. | High concurrency mode (session sharing). |
| CPU-heavy Spark query on Delta is slow. | Native execution engine. |
| Join of big and small table shuffles a lot. | Broadcast join on the small table. |
| Driver out of memory. | Remove collect() or toPandas(); write results. |
| Copy from SQL reads on one thread. | Partition option plus degree of copy parallelism. |
| Source database overloaded by a copy. | Lower degree of copy parallelism. |
| ForEach copies run one at a time. | Clear Sequential; raise Batch count (max 50). |
| Warehouse plan estimates are poor. | Update statistics; match data types. |
| Same dashboard query runs repeatedly. | Result set caching (on by default). |
| Lakehouse table has many small files. | OPTIMIZE (lesson 27). |
Match
Match each Northwind symptom to its fix
Pick an item on the left, then its match on the right.
Exam-style practice
You are a data engineer at Northwind Rides. A notebook cleans 300 million trip rows, then runs df.toPandas() to save a CSV. The driver fails with an out-of-memory error. You need the job to finish with the least cost. What should you do?
Pick one answer.
You are a data engineer at Northwind Rides. A dashboard query on the Warehouse runs every minute and always takes 12 seconds. result_cache_hit is always 0. The query filters with WHERE trip_date = CAST(GETDATE() AS date). You need repeat runs to use the cache. What should you do?
Pick one answer.
Worth keeping
- Slow starts: starter pools and high concurrency. Slow queries: native engine and broadcast joins.
- Keep data off the driver: write results instead of collect() or toPandas().
- Copies: partition the source read and set copy parallelism; loop with ForEach batch count.
- Warehouse: fresh statistics, small matching data types, named columns, result set caching.
- Name the symptom first, then pick the one setting that targets it.
On the job
Here's how Northwind's engineer wins back the two hours, one change at a time, measuring after each.
First, the 12 notebooks. High concurrency for pipelines plus a session tag means one session start instead of 12.
Next, the payments copy. The source has a clustered key, so the engineer picks Dynamic range on payment_id and caps parallelism at 50. The database team confirms CPU stays healthy.
Last, the dashboard. Query insights shows the slowest query is a SELECT * over 60 columns. Naming the 8 columns the visual needs, and matching the station_id types, fixes it.
The run now ends at 5:40 a.m. Measure, change one thing, measure again.
Batch is fast again. But the live scooter map lags by four minutes, and one KQL query scans a whole month to show the last hour. Next lesson tunes the real-time side: Eventhouse caching, retention and faster KQL.
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 (22)
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
- [79]Microsoft Learn: How to copy data using copy activity
- [93]Microsoft Learn: T-SQL surface area in Fabric Data Warehouse
- [108]Microsoft Learn: Transform data with a ForEach activity
- [153]Microsoft Learn: Configure starter pools in Microsoft Fabric
- [155]Microsoft Learn: Overview of Apache Spark compute in Microsoft Fabric
- [160]Microsoft Learn: High concurrency mode in Apache Spark for Fabric
- [161]Microsoft Learn: Configure high concurrency mode for Fabric notebooks in pipelines
- [257]Microsoft Learn: Query Insights in Fabric Data Warehouse
- [259]Microsoft Learn: Compacting Delta tables
- [262]Microsoft Learn: Spark monitoring and performance optimization best practices
- [264]Microsoft Learn: Performance guidelines in Fabric Data Warehouse
- [271]Microsoft Learn: Configure high concurrency mode for Fabric notebooks
- [272]Microsoft Learn: Development and Monitoring (Spark best practices)
- [273]Microsoft Learn: Native execution engine for Fabric Data Engineering
- [274]Microsoft Learn: Intelligent cache in Microsoft Fabric
- [275]Microsoft Learn: Troubleshoot Spark memory and performance issues
- [276]Microsoft Learn: Copy activity performance and scalability guide
- [277]Microsoft Learn: Copy activity performance with SQL databases
- [278]Microsoft Learn: Data Factory limitations overview
- [279]Microsoft Learn: Result set caching
- [304]Microsoft Learn: Configure a warehouse in a copy activity