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

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.

[4]

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.

[153][155][160][161][271][272]

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() and toPandas() 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, then unpersist() it.

[262][273][274][275]

python
from pyspark.sql.functions import broadcast
trips = spark.read.table("trips") # large fact table
stations = spark.read.table("stations") # small lookup table
# Broadcast the small table: no shuffle of trips
joined = 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 rows
joined.write.mode("overwrite").saveAsTable("trips_enriched")
joined.limit(20).show()
PySpark in a Fabric notebook. Spot the commented-out line the exam would flag.

[262][275]

[275]

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.

  1. Open the workspace and select your environment.
  2. Under Spark compute, select Acceleration.
  3. Check Enable native execution engine.
  4. Select Save, then Publish.

For one notebook only, put %%configure with "spark.native.enabled": "true" in its first cell.

[273]

An environment item open in Fabric. Under Spark compute, Acceleration is selected, and the Native execution engine section has the Enable native execution engine box checked. Save and Publish buttons are highlighted at the top right.
Microsoft Learn, CC BY 4.0

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.

[160][272]

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 COPY command 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.

[79][108][276][277][278][304]

A pipeline with one Copy data activity. Its Settings tab shows Intelligent throughput optimization set to Auto, Degree of copy parallelism set to Auto, and Enable staging checked with a staging account connection field.
Microsoft Learn, CC BY 4.0

[79]

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.

[277]

One slow copy, fifty parallel threads, and a source database that would like a word. Picture: Movie-poster parody, dramatic action-film style: a giant cartoon pipe splits into many smaller pipes all pouring data cubes at once, while a

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 STATISTICS in 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) over varchar(max), int over decimal(18,0), and date over text dates. Give join columns the same type on both sides. Use NOT NULL where 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_mb in query insights shows this cold start.

[93][264]

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.

[279]

sql
-- One-time: single-column statistics on a join column
CREATE STATISTICS stats_station_id ON dbo.FactTrips (station_id);
-- After the nightly load, in a quiet window
UPDATE STATISTICS dbo.FactTrips (stats_station_id);
-- Did repeat runs use result set caching? (2 = hit)
SELECT TOP 20 result_cache_hit, command
FROM queryinsights.exec_requests_history
ORDER BY submit_time DESC;
T-SQL in the Warehouse. Refresh statistics after the load, then check the dashboard query hit the cache.

[264][279]

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.

[264]

Symptom-to-fix table

Decision rule: name the symptom, then pick the one fix that targets it.

SymptomFix
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).

[155][160][259][264][275][277][279]

Match

Match each Northwind symptom to its fix

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

[160][275][278][279]

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.

[275]

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.

[279]

FlashcardsIndex cardsFlashcards ⚡

Card 1 of 14

[155][160][273][274][275][276][278][279]

Worth keeping

  1. Slow starts: starter pools and high concurrency. Slow queries: native engine and broadcast joins.
  2. Keep data off the driver: write results instead of collect() or toPandas().
  3. Copies: partition the source read and set copy parallelism; loop with ForEach batch count.
  4. Warehouse: fresh statistics, small matching data types, named columns, result set caching.
  5. 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.

[160][257][277]

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.

  1. [4]Microsoft Learn: Study Guide for Exam DP-700
  2. [79]Microsoft Learn: How to copy data using copy activity
  3. [93]Microsoft Learn: T-SQL surface area in Fabric Data Warehouse
  4. [108]Microsoft Learn: Transform data with a ForEach activity
  5. [153]Microsoft Learn: Configure starter pools in Microsoft Fabric
  6. [155]Microsoft Learn: Overview of Apache Spark compute in Microsoft Fabric
  7. [160]Microsoft Learn: High concurrency mode in Apache Spark for Fabric
  8. [161]Microsoft Learn: Configure high concurrency mode for Fabric notebooks in pipelines
  9. [257]Microsoft Learn: Query Insights in Fabric Data Warehouse
  10. [259]Microsoft Learn: Compacting Delta tables
  11. [262]Microsoft Learn: Spark monitoring and performance optimization best practices
  12. [264]Microsoft Learn: Performance guidelines in Fabric Data Warehouse
  13. [271]Microsoft Learn: Configure high concurrency mode for Fabric notebooks
  14. [272]Microsoft Learn: Development and Monitoring (Spark best practices)
  15. [273]Microsoft Learn: Native execution engine for Fabric Data Engineering
  16. [274]Microsoft Learn: Intelligent cache in Microsoft Fabric
  17. [275]Microsoft Learn: Troubleshoot Spark memory and performance issues
  18. [276]Microsoft Learn: Copy activity performance and scalability guide
  19. [277]Microsoft Learn: Copy activity performance with SQL databases
  20. [278]Microsoft Learn: Data Factory limitations overview
  21. [279]Microsoft Learn: Result set caching
  22. [304]Microsoft Learn: Configure a warehouse in a copy activity