3.6 — Optimize a Pipeline and a Data Warehouse
DP-700 Domain 3 · Skill 3.6
What You Need to Know
Pipeline tuning is mostly about parallelism and how data moves. Warehouse tuning is mostly about data types, file layout and how data is loaded.
Optimize a pipeline
| Lever | Effect |
|---|---|
| Run independent activities in parallel | Activities with no dependency between them run at the same time. Chain only what truly depends on something else |
| ForEach: Sequential off, plus Batch count | Runs iterations in parallel (for example 20 tables at once) instead of one after another |
| Copy activity: Intelligent throughput optimization | Assigns more compute power to one copy. Leave it on Auto, or raise it for large copies |
| Copy activity: Degree of copy parallelism | Number of parallel threads reading the source and writing the destination (for example, partitioned reads from a database) |
| Partitioned source reads | Read a large table in ranges (dynamic range or physical partitions) instead of one big query |
| Copy only what changed | Incremental loads (watermark, CDC, Copy job) instead of full reloads |
| Staging | For warehouse destinations, staging lets the copy bulk-load with COPY INTO |
| Avoid unnecessary hops | A shortcut or mirroring avoids copying at all; one Copy into Delta beats CSV → Parquet → Delta |
| Use the right activity | Don't run a notebook just to copy a file; don't run a Copy activity just to transform data |
Optimize a data warehouse
| Area | Best practice |
|---|---|
| Data types | Use the smallest type that fits: int or bigint keys rather than strings or hashes, and varchar(100) rather than varchar(8000) or max. Use matching types on both sides of a join |
| Loading | Load in bulk: COPY INTO, INSERT ... SELECT or CTAS. Avoid trickle single-row INSERT, UPDATE and DELETE, which create many small files. Aim for source files of at least 100 MB |
| Statistics | Created and updated automatically. Run UPDATE STATISTICS or CREATE STATISTICS after big loads, before users query |
| Data clustering | CLUSTER BY columns in CREATE TABLE or CTAS, so that filtered queries skip files on large tables |
| V-Order | On by default for warehouses. You can turn it off for a write-heavy staging warehouse, but you can't turn it back on. A common pattern is a staging warehouse without V-Order, then a V-Order warehouse for serving |
| Zero-copy clones | CREATE TABLE ... AS CLONE OF copies only metadata, which is useful for test copies and snapshots |
| Caching and cold starts | The first run of a query can be slower while data loads into the cache. Check data_scanned_remote_storage_mb in Query insights before you judge performance |
| Find problem queries | queryinsights.long_running_queries and frequently_run_queries |
Capacity is part of performance
- If a well-designed workload is still slow because the capacity is throttled (shown in the Capacity Metrics app), the fix is to scale the capacity SKU or move workloads between capacities, not to tune the code.
- Fix the design first: data types, file sizes and incremental loads. Then scale.
Key Concept: Pipelines get faster with parallelism and less movement. Warehouses get faster with small data types, bulk loads, statistics and clustering.
Exam Traps
- Running ForEach iterations sequentially when the tables don't depend on each other
- Loading a warehouse with thousands of single-row INSERTs
- Turning off V-Order on a warehouse that serves reports (it can't be turned back on)
- Judging a query by its first, cold run
- Using
varchar(max)or GUID or hash strings as join keys
Check your understanding
Practice Scenario
🔒 This practice scenario is part of Skillmarked Pro
The lessons stay free. Pro unlocks the other 16 DP-700 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.
Launch Deal: first month €9.95, then €19.90/month. Enter code LAUNCH50 at checkout. Ends 1 December.
Cancel anytime. Or €199 a year, which saves 17%.
Not sure yet? Try 10 free mock exam questions.
Already Pro? Log in.
Sources
- Copy activity performance and scalability guide, Microsoft Learn
- ForEach activity, Microsoft Learn
- Performance guidelines in Fabric Data Warehouse, Microsoft Learn
- Data clustering in Fabric Data Warehouse, Microsoft Learn
- Disable V-Order on a warehouse, Microsoft Learn