Microsoft DP-700 Practice Test 40
Question 390
A KQL dashboard needs approximate unique device counts over massive telemetry volumes and an exact count is not required. Which query technique can reduce aggregation cost?
- Use KQL dcount() for an approximate cardinality estimate grouped by the required time bucket.
- Build an exact set of every device identifier with make_set() for each bucket.
- Use count() to count all telemetry rows in each time bucket.
- Project just the device column but still calculate count() over the rows.
Explanation
Correct Answer
A:Approximate distinct counting reduces resource use when an estimation error is acceptable, unlike exact counting of all unique identifiers. Compare the approximate result with a bounded exact sample.
Incorrect Answers
B:Materializing an exact device-ID set uses memory and may hit size limits; the requirement permits a scalable approximate unique count. The target requirement is different: a KQL dashboard needs approximate unique device counts over massive telemetry volumes and an exact count is not required.
C:KQL count() counts events, not distinct device identities, so a busy device is counted repeatedly.
D:Projecting away unused fields reduces columns but count() still measures event rows rather than distinct device values.
Question 391
A live Eventstream sends every event to a destination though only warnings are relevant. What transformation reduces downstream load?
- Use KQL dcount() for an approximate cardinality estimate grouped by the required time bucket.
- Apply an eventstream Filter operation before the warning-only destination.
- Tune appropriate retention and caching policies to the business access window.
- Consider a supported materialized view for the repeated native-table aggregation.
Explanation
Correct Answer
B:Filtering early reduces unnecessary output traffic while leaving raw events available for other consumers. Check filtered output rate for normal and warning events.
Incorrect Answers
A:Approximate distinct counting reduces resource use when an estimation error is acceptable, unlike exact counting of all unique identifiers.
C:Retention and hot-cache choices affect storage cost and query performance; policy should match historical access needs.
D:Materialized views can precompute recurring results from eligible native data rather than repeating full aggregation work.
Question 392
An Eventhouse query is slow because it performs an expensive join before narrowing a small time range. What rewrite should be tested?
- Filter both KQL inputs to the relevant time horizon before the join.
- Tune appropriate retention and caching policies to the business access window.
- Adjust the supported Eventstream throughput level after measuring source and sink capacity.
- Use KQL dcount() for an approximate cardinality estimate grouped by the required time bucket.
Explanation
Correct Answer
A:Early filtering reduces join input volume and unnecessary time-series scan work. Inspect the query plan and execution time.
Incorrect Answers
B:Retention and hot-cache choices affect storage cost and query performance; policy should match historical access needs.
C:Throughput configuration can constrain processing, but increasing it helps only when downstream destinations can keep pace.
D:Approximate distinct counting reduces resource use when an estimation error is acceptable, unlike exact counting of all unique identifiers.
Question 393
The same small product lookup DataFrame is reused in four expensive transformations within one Spark job. What optimization may avoid repeated recomputation?
- Perform the aggregation with distributed Spark functions and collect only the small final result if needed.
- Cache the validated reusable lookup DataFrame when memory budget and reuse frequency justify it.
- Examine key distribution and use skew-aware repartitioning or salting when justified.
- Apply V-order selectively to read-heavy cross-engine tables rather than every Spark write.
Explanation
Correct Answer
B:Caching shared intermediate data can avoid redundant computation across repeated downstream actions when the cached data fits. Measure job stages and cache hit behavior across the four transformations.
Incorrect Answers
A:Driver collection of raw data creates a memory and network bottleneck; aggregating first reduces transferred rows.
C:Hot keys create uneven partitions that dominate overall stage time despite idle executors.
D:V-order can benefit Direct Lake or SQL readers but adds write overhead for Spark-centric workloads.
Question 394
A notebook repeatedly collects the full output to the driver for a simple group aggregate. What should change?
- Perform the aggregation with distributed Spark functions and collect only the small final result if needed.
- Cache the validated reusable lookup DataFrame when memory budget and reuse frequency justify it.
- Filter directly on the stored partition column with an equivalent boundary predicate.
- Examine key distribution and use skew-aware repartitioning or salting when justified.
Explanation
Correct Answer
A:Driver collection of raw data creates a memory and network bottleneck; aggregating first reduces transferred rows. Compare driver memory and result equivalence.
Incorrect Answers
B:Caching shared intermediate data can avoid redundant computation across repeated downstream actions when the cached data fits.
C:A direct predicate may let Spark skip irrelevant partitions instead of scanning and transforming all partition values.
D:Hot keys create uneven partitions that dominate overall stage time despite idle executors.
Question 395
A skewed Spark aggregation has a few tasks running far longer than others. What should the engineer analyze?
- Apply V-order selectively to read-heavy cross-engine tables rather than every Spark write.
- Examine key distribution and use skew-aware repartitioning or salting when justified.
- Cache the validated reusable lookup DataFrame when memory budget and reuse frequency justify it.
- Replace the UDF with supported native Spark SQL expressions.
Explanation
Correct Answer
B:Hot keys create uneven partitions that dominate overall stage time despite idle executors. Compare maximum task duration after changes.
Incorrect Answers
A:V-order can benefit Direct Lake or SQL readers but adds write overhead for Spark-centric workloads.
C:Caching shared intermediate data can avoid redundant computation across repeated downstream actions when the cached data fits.
D:Built-in functions are generally easier to optimize and avoid per-row Python boundary overhead.
Question 396
A Delta read ignores partition pruning because the filter wraps the partition date in a function. Which rewrite may help?
- Cache the validated reusable lookup DataFrame when memory budget and reuse frequency justify it.
- Filter directly on the stored partition column with an equivalent boundary predicate.
- Examine key distribution and use skew-aware repartitioning or salting when justified.
- Inspect shuffle partition sizing, join strategy, skew, and memory pressure in Spark stage metrics.
Explanation
Correct Answer
B:A direct predicate may let Spark skip irrelevant partitions instead of scanning and transforming all partition values. Compare input file counts in the execution plan.
Incorrect Answers
A:Caching shared intermediate data can avoid redundant computation across repeated downstream actions when the cached data fits.
C:Hot keys create uneven partitions that dominate overall stage time despite idle executors.
D:Spill indicates work exceeds in-memory execution capacity, often because of partition design or join shape.
Question 397
A Spark pipeline incurs major Python UDF overhead for string normalization available as a built-in function. What should be preferred?
- Replace the UDF with supported native Spark SQL expressions.
- Inspect shuffle partition sizing, join strategy, skew, and memory pressure in Spark stage metrics.
- Cache the validated reusable lookup DataFrame when memory budget and reuse frequency justify it.
- Examine key distribution and use skew-aware repartitioning or salting when justified.
Explanation
Correct Answer
A:Built-in functions are generally easier to optimize and avoid per-row Python boundary overhead. Benchmark identical normalized output.
Incorrect Answers
B:Spill indicates work exceeds in-memory execution capacity, often because of partition design or join shape.
C:Caching shared intermediate data can avoid redundant computation across repeated downstream actions when the cached data fits.
D:Hot keys create uneven partitions that dominate overall stage time despite idle executors.
Question 398
A shuffle-heavy join shows large spill to disk and high runtime. What should be examined before merely adding nodes?
- Inspect shuffle partition sizing, join strategy, skew, and memory pressure in Spark stage metrics.
- Perform the aggregation with distributed Spark functions and collect only the small final result if needed.
- Replace the UDF with supported native Spark SQL expressions.
- Cache the validated reusable lookup DataFrame when memory budget and reuse frequency justify it.
Explanation
Correct Answer
A:Spill indicates work exceeds in-memory execution capacity, often because of partition design or join shape. Compare spill bytes and task times after tuning.
Incorrect Answers
B:Driver collection of raw data creates a memory and network bottleneck; aggregating first reduces transferred rows.
C:Built-in functions are generally easier to optimize and avoid per-row Python boundary overhead.
D:Caching shared intermediate data can avoid redundant computation across repeated downstream actions when the cached data fits.
Question 399
A serving Delta table is read heavily by Direct Lake and Spark writes slow after V-order was enabled everywhere. What should be adjusted?
- Cache the validated reusable lookup DataFrame when memory budget and reuse frequency justify it.
- Increase the appropriate shuffle partition count to distribute work across available executor slots.
- Perform the aggregation with distributed Spark functions and collect only the small final result if needed.
- Apply V-order selectively to read-heavy cross-engine tables rather than every Spark write.
Explanation
Correct Answer
D:V-order can benefit Direct Lake or SQL readers but adds write overhead for Spark-centric workloads. Compare write throughput and report query latency per table.
Incorrect Answers
A:Caching shared intermediate data can avoid redundant computation across repeated downstream actions when the cached data fits.
B:Too few large partitions can underutilize executors and increase spill; measured partition tuning can improve parallelism.
C:Driver collection of raw data creates a memory and network bottleneck; aggregating first reduces transferred rows.
Question 400
A Spark stage has two huge shuffle partitions even though many executors are idle and the join is already appropriate. What tuning should be tested?
- Decrease the shuffle partition count so each remaining task performs even more work.
- Increase executor count but keep the existing two huge shuffle partitions unchanged.
- Increase the appropriate shuffle partition count to distribute work across available executor slots.
- Cache the small postshuffle summary in the driver's local memory.
Explanation
Correct Answer
C:Too few large partitions can underutilize executors and increase spill; measured partition tuning can improve parallelism. Compare task counts, spill bytes, and duration after the change.
Incorrect Answers
A:Fewer partitions would further reduce available task parallelism and make the large tasks heavier.
B:Additional executor slots remain mostly idle when the stage has only two independent shuffle tasks.
D:Driver-side caching is not a remedy for underpartitioned executor shuffle stages and can create driver-memory pressure. In the current incident, a Spark stage has two huge shuffle partitions even though many executors are idle and the join is already appropriate.
