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 18

Question 170

A source column contains dates formatted as text, causing downstream schema mismatches. What should the dataflow do?

  1. Apply an explicit Power Query type-conversion step with suitable locale handling.
  2. Use a column split by a validated delimiter or parsing rule.
  3. Check join keys and relationship cardinality in the Merge step.
  4. Add the currency join and calculation in a curated Dataflow Gen2 query output.

Explanation

Correct Answer

A:

Explicit typing prevents text dates from being written to a destination with incompatible semantics. Test both ambiguous date strings and normal values.

Incorrect Answers

B:

An explicit split can normalize a compound field while preserving the original transformation definition for future refreshes.

C:

A many-to-many or repeated-key join can multiply rows; removing duplicates afterward can conceal the modeling error.

D:

Curated query steps express transformation without rewriting the raw source data.

Question 171

A dataflow merges two queries but produces unexpectedly duplicated rows. Which Power Query operation should be reviewed?

  1. Choose and validate a supported append update method for the configured data destination.
  2. Use Dataflow Gen2 Power Query merges and load the resulting table to an approved destination.
  3. Remove the unwanted top rows and promote the correct header row in Power Query.
  4. Check join keys and relationship cardinality in the Merge step.

Explanation

Correct Answer

D:

A many-to-many or repeated-key join can multiply rows; removing duplicates afterward can conceal the modeling error. Inspect input key frequencies and merged row counts.

Incorrect Answers

A:

Destination write behavior determines whether previous records remain; query transformations alone do not select append semantics.

B:

Power Query handles visual join and shaping operations without writing distributed Spark code.

C:

Shaping the import once in a dataflow prevents manual spreadsheet editing each refresh.

Question 172

A file has header rows before the actual dataset and requires a repeatable cleanup. Which low-code steps are suitable?

  1. Remove the unwanted top rows and promote the correct header row in Power Query.
  2. Use a column split by a validated delimiter or parsing rule.
  3. Filter null and blank account identifiers before loading the curated output.
  4. Check join keys and relationship cardinality in the Merge step.

Explanation

Correct Answer

A:

Shaping the import once in a dataflow prevents manual spreadsheet editing each refresh. Inspect column names after a new file is loaded.

Incorrect Answers

B:

An explicit split can normalize a compound field while preserving the original transformation definition for future refreshes.

C:

A deterministic filter at transformation time prevents invalid business keys from reaching aggregates.

D:

A many-to-many or repeated-key join can multiply rows; removing duplicates afterward can conceal the modeling error.

Question 173

A Dataflow Gen2 refresh must append each month's invoices instead of replacing the existing destination. What configuration should be checked?

  1. Remove the unwanted top rows and promote the correct header row in Power Query.
  2. Check join keys and relationship cardinality in the Merge step.
  3. Use a column split by a validated delimiter or parsing rule.
  4. Choose and validate a supported append update method for the configured data destination.

Explanation

Correct Answer

D:

Destination write behavior determines whether previous records remain; query transformations alone do not select append semantics. Refresh twice with distinct month files and confirm both remain.

Incorrect Answers

A:

Shaping the import once in a dataflow prevents manual spreadsheet editing each refresh.

B:

A many-to-many or repeated-key join can multiply rows; removing duplicates afterward can conceal the modeling error.

C:

An explicit split can normalize a compound field while preserving the original transformation definition for future refreshes.

Question 174

An input feed sometimes contains blank account IDs that must be excluded from billing aggregates. Which dataflow step is appropriate?

  1. Use Dataflow Gen2 Power Query merges and load the resulting table to an approved destination.
  2. Apply an explicit Power Query type-conversion step with suitable locale handling.
  3. Filter null and blank account identifiers before loading the curated output.
  4. Remove the unwanted top rows and promote the correct header row in Power Query.

Explanation

Correct Answer

C:

A deterministic filter at transformation time prevents invalid business keys from reaching aggregates. Count filtered rows during test refresh.

Incorrect Answers

A:

Power Query handles visual join and shaping operations without writing distributed Spark code.

B:

Explicit typing prevents text dates from being written to a destination with incompatible semantics.

D:

Shaping the import once in a dataflow prevents manual spreadsheet editing each refresh.

Question 175

A dataflow query must split a combined address field into city and state. Which Power Query transformation is appropriate?

  1. Use a column split by a validated delimiter or parsing rule.
  2. Remove the unwanted top rows and promote the correct header row in Power Query.
  3. Use Dataflow Gen2 Power Query merges and load the resulting table to an approved destination.
  4. Choose and validate a supported append update method for the configured data destination.

Explanation

Correct Answer

A:

An explicit split can normalize a compound field while preserving the original transformation definition for future refreshes. Test records with missing or extra delimiters.

Incorrect Answers

B:

Shaping the import once in a dataflow prevents manual spreadsheet editing each refresh.

C:

Power Query handles visual join and shaping operations without writing distributed Spark code.

D:

Destination write behavior determines whether previous records remain; query transformations alone do not select append semantics.

Question 176

A finance dataflow should join two currency lookup columns but not change the raw ingestion table. Where should the conversion logic live?

  1. Use a column split by a validated delimiter or parsing rule.
  2. Check join keys and relationship cardinality in the Merge step.
  3. Choose and validate a supported append update method for the configured data destination.
  4. Add the currency join and calculation in a curated Dataflow Gen2 query output.

Explanation

Correct Answer

D:

Curated query steps express transformation without rewriting the raw source data. Compare curated values against the original source amounts.

Incorrect Answers

A:

An explicit split can normalize a compound field while preserving the original transformation definition for future refreshes.

B:

A many-to-many or repeated-key join can multiply rows; removing duplicates afterward can conceal the modeling error.

C:

Destination write behavior determines whether previous records remain; query transformations alone do not select append semantics.

Question 177

A multi-terabyte Delta dataset needs a distributed column derivation using Python expressions. Which processing tool fits?

  1. Run a Fabric Spark notebook using DataFrame column transformations.
  2. Review write partition sizing and subsequent Delta compaction strategy.
  3. Broadcast the validated small lookup side when it fits executor memory.
  4. Use a window partitioned by customer and ordered by revision time, then retain rank one.

Explanation

Correct Answer

A:

Spark distributes supported DataFrame operations across partitions, making it appropriate for large data volumes. Check output schema and partitioned write performance.

Incorrect Answers

B:

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

C:

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

D:

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

Question 178

A notebook performs an accidental driver collect of a massive dataset and runs out of memory. What should replace it?

  1. Keep the operation in distributed Spark DataFrame transformations instead of collecting all rows to the driver.
  2. Use a window partitioned by customer and ordered by revision time, then retain rank one.
  3. Push the selective date predicate into the DataFrame read/filter plan where partition pruning applies.
  4. Broadcast the validated small lookup side when it fits executor memory.

Explanation

Correct Answer

A:

Collect transfers data to one process, defeating distributed execution and exceeding driver memory. Review the physical plan and driver memory after correction.

Incorrect Answers

B:

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

C:

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 179

A join between two large Spark dataframes causes expensive wide shuffles. Which first diagnostic should guide a repair?

  1. Inspect the Spark execution plan and shuffle stages before changing partition or join strategy.
  2. Run a Fabric Spark notebook using DataFrame column transformations.
  3. Broadcast the validated small lookup side when it fits executor memory.
  4. Review write partition sizing and subsequent Delta compaction strategy.

Explanation

Correct Answer

A:

Join optimization depends on size, keys, skew, and data distribution; blindly increasing executors may not fix shuffle design. Compare stage metrics and the revised plan.

Incorrect Answers

B:

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

C:

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

D:

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

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.

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