Microsoft DP-700 Practice Test 38
Question 370
An administrator proposes VACUUM with a few minutes of retention while consumers rely on Delta time travel. What is the safer choice?
- Consider Z-order optimization on the frequently filtered column.
- Schedule supported table-maintenance work using a notebook or pipeline activity after successful loads.
- Retain a supported safety window that protects time travel and concurrent readers.
- Consider V-order when optimizing that serving Delta table.
Explanation
Correct Answer
C:Aggressive vacuuming can remove files still required for recovery or readers; default retention is typically seven days. Inspect retention policy and time-travel requirements.
Incorrect Answers
A:Z-order colocates values to improve file pruning for selective predicates when workload characteristics justify it.
B:Maintenance should occur at a controlled cadence and after writes, not during arbitrary concurrent file operations.
D:V-order can improve cross-engine scanning and compression for read-heavy Power BI/SQL workloads at some write cost.
Question 371
A lakehouse uses raw CSV files rather than managed Delta tables. A colleague attempts Delta OPTIMIZE on the file folder. What should change?
- Convert or load the dataset into a supported Delta table before applying Delta maintenance.
- Run VACUUM with a safe retention period compatible with readers and time travel.
- Retain a supported safety window that protects time travel and concurrent readers.
- Consider V-order when optimizing that serving Delta table.
Explanation
Correct Answer
A:Delta table maintenance operates on Delta transaction-log tables, not arbitrary raw file directories. Verify the table has a valid Delta log.
Incorrect Answers
B:VACUUM removes old unreferenced files, unlike OPTIMIZE, which compacts active data files.
C:Aggressive vacuuming can remove files still required for recovery or readers; default retention is typically seven days.
D:V-order can improve cross-engine scanning and compression for read-heavy Power BI/SQL workloads at some write cost.
Question 372
An ingestion Copy activity spends most of its time waiting on a source API that throttles concurrent requests. How should concurrency be tuned?
- Parameterize incremental source selection using changed partitions or a durable watermark.
- Remove unnecessary dependency edges and place only true prerequisites on the critical path.
- Reduce copy parallelism to a sustainable level and use bounded retries if appropriate.
- Consolidate compatible small tasks into fewer notebook executions where business isolation permits.
Explanation
Correct Answer
C:Too much source concurrency can increase throttling and total duration, so bottleneck-aware tuning is required. Compare bytes per second and throttle counts.
Incorrect Answers
A:Reading only changed inputs avoids redundant network and storage work for stable history.
B:An artificial serial dependency increases elapsed time even when downstream work does not use the lookup output.
D:Batching tiny workloads amortizes orchestration and Spark startup cost without altering processing semantics.
Question 373
A pipeline processes 20 independent tables sequentially despite sufficient capacity and no dependencies. What optimization should be considered?
- Parameterize incremental source selection using changed partitions or a durable watermark.
- Run independent activities in parallel within an approved concurrency limit.
- Break down end-to-end duration into queueing, startup, transfer, and downstream processing.
- Consolidate compatible small tasks into fewer notebook executions where business isolation permits.
Explanation
Correct Answer
B:Parallelism can reduce elapsed time when operations are independent and external systems can sustain load. Benchmark total time and capacity impact.
Incorrect Answers
A:Reading only changed inputs avoids redundant network and storage work for stable history.
C:Total runtime may be dominated by non-copy stages; optimizing a fast activity gives little benefit.
D:Batching tiny workloads amortizes orchestration and Spark startup cost without altering processing semantics.
Question 374
A copy job retransfers every archived file daily though only new partitions change. Which change improves transfer efficiency?
- Parameterize incremental source selection using changed partitions or a durable watermark.
- Consolidate compatible small tasks into fewer notebook executions where business isolation permits.
- Remove unnecessary dependency edges and place only true prerequisites on the critical path.
- Run independent activities in parallel within an approved concurrency limit.
Explanation
Correct Answer
A:Reading only changed inputs avoids redundant network and storage work for stable history. Compare selected file counts over two runs.
Incorrect Answers
B:Batching tiny workloads amortizes orchestration and Spark startup cost without altering processing semantics.
C:An artificial serial dependency increases elapsed time even when downstream work does not use the lookup output.
D:Parallelism can reduce elapsed time when operations are independent and external systems can sustain load.
Question 375
A notebook activity is launched repeatedly for tiny partitions, and Spark session startup dominates runtime. What may reduce overhead?
- Checkpoint validated partitions and resume safely using idempotent writes.
- Reduce copy parallelism to a sustainable level and use bounded retries if appropriate.
- Consolidate compatible small tasks into fewer notebook executions where business isolation permits.
- Run independent activities in parallel within an approved concurrency limit.
Explanation
Correct Answer
C:Batching tiny workloads amortizes orchestration and Spark startup cost without altering processing semantics. Measure total startup time before and after grouping.
Incorrect Answers
A:Partition-level resume avoids repeating successful work while maintaining correct exactly-once logical output.
B:Too much source concurrency can increase throttling and total duration, so bottleneck-aware tuning is required.
D:Parallelism can reduce elapsed time when operations are independent and external systems can sustain load.
Question 376
A pipeline reexecutes a costly failed full load from the beginning even though earlier partitions committed. Which design improves recovery?
- Checkpoint validated partitions and resume safely using idempotent writes.
- Consolidate compatible small tasks into fewer notebook executions where business isolation permits.
- Remove unnecessary dependency edges and place only true prerequisites on the critical path.
- Run independent activities in parallel within an approved concurrency limit.
Explanation
Correct Answer
A:Partition-level resume avoids repeating successful work while maintaining correct exactly-once logical output. Force a mid-run failure and inspect resumed partitions.
Incorrect Answers
B:Batching tiny workloads amortizes orchestration and Spark startup cost without altering processing semantics.
C:An artificial serial dependency increases elapsed time even when downstream work does not use the lookup output.
D:Parallelism can reduce elapsed time when operations are independent and external systems can sustain load.
Question 377
A pipeline waits for a slow lookup call before several independent copies can begin. What should the designer review?
- Checkpoint validated partitions and resume safely using idempotent writes.
- Parameterize incremental source selection using changed partitions or a durable watermark.
- Remove unnecessary dependency edges and place only true prerequisites on the critical path.
- Consolidate compatible small tasks into fewer notebook executions where business isolation permits.
Explanation
Correct Answer
C:An artificial serial dependency increases elapsed time even when downstream work does not use the lookup output. Compare the dependency graph and wall-clock time.
Incorrect Answers
A:Partition-level resume avoids repeating successful work while maintaining correct exactly-once logical output.
B:Reading only changed inputs avoids redundant network and storage work for stable history.
D:Batching tiny workloads amortizes orchestration and Spark startup cost without altering processing semantics.
Question 378
An ingestion pipeline is slow, but its copy throughput appears healthy. What other bottleneck should be measured?
- Remove unnecessary dependency edges and place only true prerequisites on the critical path.
- Run independent activities in parallel within an approved concurrency limit.
- Parameterize incremental source selection using changed partitions or a durable watermark.
- Break down end-to-end duration into queueing, startup, transfer, and downstream processing.
Explanation
Correct Answer
D:Total runtime may be dominated by non-copy stages; optimizing a fast activity gives little benefit. Compare activity timing breakdowns against the SLA.
Incorrect Answers
A:An artificial serial dependency increases elapsed time even when downstream work does not use the lookup output.
B:Parallelism can reduce elapsed time when operations are independent and external systems can sustain load.
C:Reading only changed inputs avoids redundant network and storage work for stable history.
Question 379
A warehouse query recently became much slower. Which built-in evidence should the engineer inspect?
- Inspect table and column statistics relevant to the filter and join.
- Use Warehouse Query Insights and execution diagnostics to identify the slow query shape and resource pressure.
- Apply a selective sargable date predicate before expensive joins and aggregations.
- Normalize the join keys ahead of time and join on compatible native columns.
Explanation
Correct Answer
B:Query Insights surfaces historical durations, frequent patterns, and expensive queries for evidence-led tuning. Compare the query's recent executions before changing code.
Incorrect Answers
A:The optimizer relies on statistics to estimate cardinality and choose plans; stale or unrepresentative data can distort its estimates.
C:Early selective filtering reduces data scanned when the optimizer can use the predicate effectively.
D:Complex per-row expressions can hinder optimization and produce avoidable compute work.
Question 380
A reporting query scans all transaction history despite needing one quarter. Which first rewrite can reduce work?
- Repair dimension uniqueness and join grain before attempting performance-only tuning.
- Maintain a validated serving aggregate at the report's stable monthly grain.
- Apply a selective sargable date predicate before expensive joins and aggregations.
- Normalize the join keys ahead of time and join on compatible native columns.
Explanation
Correct Answer
C:Early selective filtering reduces data scanned when the optimizer can use the predicate effectively. Compare execution duration and rows scanned.
Incorrect Answers
A:A logically incorrect join can multiply work and amounts; faster execution is not a substitute for accurate cardinality.
B:Preaggregated serving data can reduce repeated heavy group-by scans for predictable reporting patterns.
D:Complex per-row expressions can hinder optimization and produce avoidable compute work.
