Launch Deal · 50% off 1st month ·

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.

See plans

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.