cert
cert-1
cert-2

Pass Microsoft DP-700 Exam in First Attempt Guaranteed!

Get 100% Latest Exam Questions, Accurate & Verified Answers to Pass the Actual Exam!
30 Days Free Updates, Instant Download!

cert-5
cert-6
DP-700 Exam - Verified By Experts
DP-700 Premium Bundle
$14.99

DP-700 Premium Bundle

$74.99
$89.98
  • Premium File 160 Questions & Answers. Last update: Nov 10, 2026
  • Training Course 160 Video Lectures
 
$89.98
$74.99
accept 159 downloads in last 7 days
block-screenshots
Purchase Microsoft DP-700 Exam Training Products Individually
Microsoft DP-700 Premium File
Premium File 160 Q&A
$76.99$69.99
Microsoft DP-700 Video Training Course
Training Course 160 Lectures
$21.99 $19.99
Microsoft DP-700 Practice Test 19

Question 180

A tiny reference table is joined to a huge fact DataFrame. Which Spark join strategy may avoid a full fact shuffle?

  1. Broadcast the validated small lookup side when it fits executor memory.
  2. Inspect the Spark execution plan and shuffle stages before changing partition or join strategy.
  3. Use a window partitioned by customer and ordered by revision time, then retain rank one.
  4. Review write partition sizing and subsequent Delta compaction strategy.

Explanation

Correct Answer

A:

Broadcast joins distribute the smaller side to workers and can avoid repartitioning the large relation. Check that the optimized plan uses a broadcast join.

Incorrect Answers

B:

Join optimization depends on size, keys, skew, and data distribution; blindly increasing executors may not fix shuffle design.

C:

A deterministic row-number window chooses the newest record per business key rather than arbitrary duplicate removal.

D:

Output file layout affects read efficiency; controlling partitions and compacting small files can reduce metadata overhead.

Question 181

A Spark job filters on partition date after reading a Delta table. What read pattern reduces unnecessary scanning?

  1. Broadcast the validated small lookup side when it fits executor memory.
  2. Use equivalent built-in Spark SQL functions when available.
  3. Push the selective date predicate into the DataFrame read/filter plan where partition pruning applies.
  4. Keep the operation in distributed Spark DataFrame transformations instead of collecting all rows to the driver.

Explanation

Correct Answer

C:

Filtering on the partition column allows skipping irrelevant partitions when the source layout supports it. Inspect files scanned for a one-day query.

Incorrect Answers

A:

Broadcast joins distribute the smaller side to workers and can avoid repartitioning the large relation.

B:

Native expressions are easier for Spark to optimize and avoid much Python execution overhead.

D:

Collect transfers data to one process, defeating distributed execution and exceeding driver memory.

Question 182

A notebook needs to preserve only the newest revision per customer from duplicate event rows. Which DataFrame technique fits?

  1. Run a Fabric Spark notebook using DataFrame column transformations.
  2. Inspect the Spark execution plan and shuffle stages before changing partition or join strategy.
  3. Use a window partitioned by customer and ordered by revision time, then retain rank one.
  4. Broadcast the validated small lookup side when it fits executor memory.

Explanation

Correct Answer

C:

A deterministic row-number window chooses the newest record per business key rather than arbitrary duplicate removal. Verify a customer with several revisions keeps the latest.

Incorrect Answers

A:

Spark distributes supported DataFrame operations across partitions, making it appropriate for large data volumes.

B:

Join optimization depends on size, keys, skew, and data distribution; blindly increasing executors may not fix shuffle design.

D:

Broadcast joins distribute the smaller side to workers and can avoid repartitioning the large relation.

Question 183

A PySpark transformation is slow because a Python UDF handles simple arithmetic on every row. What should be considered?

  1. Review write partition sizing and subsequent Delta compaction strategy.
  2. Push the selective date predicate into the DataFrame read/filter plan where partition pruning applies.
  3. Use equivalent built-in Spark SQL functions when available.
  4. Broadcast the validated small lookup side when it fits executor memory.

Explanation

Correct Answer

C:

Native expressions are easier for Spark to optimize and avoid much Python execution overhead. Compare execution plans and timing for the same output.

Incorrect Answers

A:

Output file layout affects read efficiency; controlling partitions and compacting small files can reduce metadata overhead.

B:

Filtering on the partition column allows skipping irrelevant partitions when the source layout supports it.

D:

Broadcast joins distribute the smaller side to workers and can avoid repartitioning the large relation.

Question 184

A Spark write leaves hundreds of tiny output files after partitioned ingestion. What should be adjusted?

  1. Use equivalent built-in Spark SQL functions when available.
  2. Review write partition sizing and subsequent Delta compaction strategy.
  3. Keep the operation in distributed Spark DataFrame transformations instead of collecting all rows to the driver.
  4. Broadcast the validated small lookup side when it fits executor memory.

Explanation

Correct Answer

B:

Output file layout affects read efficiency; controlling partitions and compacting small files can reduce metadata overhead. Inspect file size distribution after the change.

Incorrect Answers

A:

Native expressions are easier for Spark to optimize and avoid much Python execution overhead.

C:

Collect transfers data to one process, defeating distributed execution and exceeding driver memory.

D:

Broadcast joins distribute the smaller side to workers and can avoid repartitioning the large relation.

Question 185

An analyst needs total revenue by customer and fiscal quarter from a warehouse fact table. Which SQL construct fits?

  1. Use a deterministic CTE or window ranking by transaction ID and keep the selected source version.
  2. Keep NULL handling explicit and apply COALESCE only when the business rule treats missing as zero.
  3. Use a supported warehouse MERGE or equivalent transactional upsert keyed to the business identifier.
  4. Use GROUP BY over customer and fiscal-quarter keys with SUM of the revenue measure.

Explanation

Correct Answer

D:

Grouped aggregate SQL reduces fact rows to the requested reporting grain. Verify totals for a customer spanning multiple quarters.

Incorrect Answers

A:

Explicit ranking chooses the correct record; DISTINCT over all columns may not resolve conflicting versions of one key.

B:

Replacing null with zero changes meaning and can mask incomplete source records.

C:

An upsert maintains one current logical row per key and handles both changed and new records.

Question 186

A target warehouse table must update matching keys and insert unseen rows from a staging table. Which DML pattern is appropriate?

  1. Use a supported warehouse MERGE or equivalent transactional upsert keyed to the business identifier.
  2. Use GROUP BY over customer and fiscal-quarter keys with SUM of the revenue measure.
  3. Cast the operands to a suitable decimal type before division.
  4. Aggregate or constrain the tag side to the intended grain before joining facts.

Explanation

Correct Answer

A:

An upsert maintains one current logical row per key and handles both changed and new records. Compare matching and nonmatching source keys after the operation.

Incorrect Answers

B:

Grouped aggregate SQL reduces fact rows to the requested reporting grain.

C:

Numeric type semantics affect precision; explicit decimal conversion preserves fractions where integer arithmetic would not.

D:

One-to-many joins multiply fact rows and can inflate additive measures; the join grain must be fixed first.

Question 187

A dimension join returns null for fact rows whose customer key is absent. Which join type preserves those facts for investigation?

  1. Use a deterministic CTE or window ranking by transaction ID and keep the selected source version.
  2. Use a LEFT JOIN from facts to dimensions and inspect unmatched dimension keys.
  3. Use ROW_NUMBER or RANK with PARTITION BY store and ORDER BY revenue descending.
  4. Use a supported warehouse MERGE or equivalent transactional upsert keyed to the business identifier.

Explanation

Correct Answer

B:

Left joins retain rows from the fact side while exposing missing matches, unlike an INNER JOIN that drops them. Count missing dimension matches before choosing remediation.

Incorrect Answers

A:

Explicit ranking chooses the correct record; DISTINCT over all columns may not resolve conflicting versions of one key.

C:

Window functions calculate per-group order without collapsing detailed transaction rows.

D:

An upsert maintains one current logical row per key and handles both changed and new records.

Question 188

A report requires the three largest revenue transactions per store. Which SQL pattern can rank rows per store?

  1. Use ROW_NUMBER or RANK with PARTITION BY store and ORDER BY revenue descending.
  2. Aggregate or constrain the tag side to the intended grain before joining facts.
  3. Cast the operands to a suitable decimal type before division.
  4. Use a LEFT JOIN from facts to dimensions and inspect unmatched dimension keys.

Explanation

Correct Answer

A:

Window functions calculate per-group order without collapsing detailed transaction rows. Test ties and choose the intended ranking behavior.

Incorrect Answers

B:

One-to-many joins multiply fact rows and can inflate additive measures; the join grain must be fixed first.

C:

Numeric type semantics affect precision; explicit decimal conversion preserves fractions where integer arithmetic would not.

D:

Left joins retain rows from the fact side while exposing missing matches, unlike an INNER JOIN that drops them.

Question 189

A query calculates a ratio of two integer measures and unexpectedly truncates the decimal. How should the expression change?

  1. Cast the operands to a suitable decimal type before division.
  2. Use a LEFT JOIN from facts to dimensions and inspect unmatched dimension keys.
  3. Use ROW_NUMBER or RANK with PARTITION BY store and ORDER BY revenue descending.
  4. Use GROUP BY over customer and fiscal-quarter keys with SUM of the revenue measure.

Explanation

Correct Answer

A:

Numeric type semantics affect precision; explicit decimal conversion preserves fractions where integer arithmetic would not. Test an example with a noninteger result.

Incorrect Answers

B:

Left joins retain rows from the fact side while exposing missing matches, unlike an INNER JOIN that drops them.

C:

Window functions calculate per-group order without collapsing detailed transaction rows.

D:

Grouped aggregate SQL reduces fact rows to the requested reporting grain.

Question 190

A warehouse query returns doubled sales totals after joining a one-to-many product tags table. What should be corrected?

  1. Aggregate or constrain the tag side to the intended grain before joining facts.
  2. Use a deterministic CTE or window ranking by transaction ID and keep the selected source version.
  3. Cast the operands to a suitable decimal type before division.
  4. Use a supported warehouse MERGE or equivalent transactional upsert keyed to the business identifier.

Explanation

Correct Answer

A:

One-to-many joins multiply fact rows and can inflate additive measures; the join grain must be fixed first. Compare prejoin and postjoin fact row counts.

Incorrect Answers

B:

Explicit ranking chooses the correct record; DISTINCT over all columns may not resolve conflicting versions of one key.

C:

Numeric type semantics affect precision; explicit decimal conversion preserves fractions where integer arithmetic would not.

D:

An upsert maintains one current logical row per key and handles both changed and new records.

Implementing Data Engineering Solutions Using Microsoft Fabric (DP-700) Practice Tests