Microsoft DP-700 Practice Test 24

Question 230

A warehouse transform uses SQL `ROW_NUMBER()` over the wrong partition key and keeps duplicate records. What explains the unexpected result?

The warehouse window function is not preceded by a `DISTINCT` on every output column.
The target needs a larger Fabric capacity to select exactly one row per logical entity.
The window partition/order specification does not match the intended business-entity grain, so the row-selection logic is incorrect.
A lakehouse Files shortcut was not created for the warehouse query plan.

Explanation

Correct Answer

Window functions preserve explicit partition semantics; a wrong partition allows duplicates or discards legitimate rows.

Incorrect Answers

An incorrect ROW_NUMBER partition cannot be repaired reliably by a blanket DISTINCT, which can collapse valid rows.

Compute capacity does not correct the partition-key logic determining which rows are peers.

A shortcut cannot fix the wrong PARTITION BY columns in a relational deduplication query.

Question 231

A warehouse ELT job claims to build correct relational features. Select THREE SQL-side validations that should be checked.

Check only that the SQL statement contains a ROW_NUMBER function somewhere.
Compare report-level counts without checking which duplicate version was retained.
Validate the expected join cardinality at the business keys.
Validate window partitioning and deterministic ORDER BY tie-breaking for representative records.
Execute the query in a notebook without validating SQL key-level ranking results.
Reconcile destination row counts and selected aggregates to source fixtures for the intended output grain.

Explanation

Correct Answers

An unintended many-to-many join can inflate rows even when the SQL statement succeeds.

Ranking or deduplication can select different rows if the partition/order contract is ambiguous.

Correctness requires verifying transformed data rather than only checking SQL compilation.

Incorrect Answers

The presence of the syntax does not establish partition keys, ordering or winner selection are correct.

Total counts can match even while stale or wrong version rows survive a bad partition/order rule.

A successful execution does not prove the relational ROW_NUMBER grouping, tie-break and join cardinalities.

Question 232

A data analyst designs Power Query transformations that merge several SaaS connector datasets into a curated lakehouse table. What tool should execute them?

Use a Dataflow Gen2 with configured connector credentials, merge steps, refresh and a supported lakehouse destination.
Create a PySpark environment to run the Power Query M transformations without converting them.
Use a Copy activity alone and assume it performs all required SaaS merges and column shaping.
Build only a Dataflow Gen2 preview and omit its configured lakehouse data destination.

Explanation

Correct Answer

Power Query transformations and connectors are native Dataflow Gen2 strengths, and destination configuration materializes the curated result.

Incorrect Answers

Fabric Spark environments do not directly execute Dataflow Gen2's Power Query pipeline syntax and connectors.

Basic copying does not automatically implement the specified Power Query merge/transform logic.

Preview results are not a persisted curated table until destination mapping and refresh are published.

Question 233

A Dataflow Gen2 preview shows merged rows but scheduled lakehouse results remain unchanged. Select TWO likely missing steps.

The Dataflow preview lacked sufficient sample rows to display all merged input records.
The Dataflow query steps are stored as files rather than as warehouse stored procedures.
The lakehouse folder contains too many Parquet files to show the refresh status.
The dataflow must define and save a supported destination mapping for the transformed query.
A successful dataflow refresh must execute with working source connections and write the mapped destination.

Explanation

Correct Answers

Preview results alone do not guarantee an output table is bound as a refresh destination.

Saving a Power Query design does not automatically mean the destination has received an updated snapshot.

Incorrect Answers

Complete preview results do not imply outputs have been configured or refreshed; the problem is at destinations.

The data destination for each Dataflow query is independent of SQL stored-procedure representation.

File count is not the reason only one of several destination mappings took effect.

Question 234

A Dataflow Gen2 merge is deployed. Which check establishes correct multi-source output?

Inspect the editor preview for merged rows but never check destination tables.
Validate only the data destination of the first output query that was already succeeding.
Check the Dataflow icon and its workspace label after publishing.
Inspect the refresh result and destination table for representative joined source keys, data types and expected row cardinality.

Explanation

Correct Answer

A row-level fixture proves the Power Query merge and destination write rather than merely confirming the dataflow item exists.

Incorrect Answers

An in-memory preview is not proof of a completed write for each configured output.

One succeeding destination does not establish the other two mappings and outputs are correct.

Item labels cannot establish that all three output tables contain refreshed expected schemas and data.

Question 235

A data engineering team must ingest millions of rows from a supported relational source to a Fabric lakehouse with mappings but no custom algorithm. What Fabric approach fits?

Write a Python for-loop that inserts each of the millions of source rows individually.
Use a supported Data Factory Copy activity or Copy job with configured connectors, write mode and monitored throughput.
Use Eventstream event ingestion solely because the source has millions of historical rows.
Use the SQL analytics endpoint to INSERT directly into a lakehouse table for the initial load.

Explanation

Correct Answer

Copy operators are designed for scalable data movement without requiring a custom PySpark job for basic transfer.

Incorrect Answers

Row-at-a-time movement adds unnecessary driver and request overhead when a supported bulk Copy activity is available.

Eventstream is a streaming route, not the most direct relational bulk Copy with schema mappings.

That endpoint is read-only for data DML; supported Copy can write to the configured lakehouse destination.

Question 236

A pipeline processes a large relational source through a single-driver Python loop and performs poorly. It has no parallel Copy operation. What design limitation is likely?

The workflow is bypassing a supported scalable copy engine and serializing records through driver-level code.
The low throughput is caused by the table not being registered as a SQL view.
The pipeline should cache every Python row object in the driver memory.
The source lacks an Eventhouse KQL query acceleration policy.

Explanation

Correct Answer

Per-row Python processing incurs unnecessary round trips compared with source/sink optimized Copy operations for straightforward movement.

Incorrect Answers

The bottleneck is row-at-a-time driver-side movement, not catalog metadata for a downstream view.

Caching source rows in a single driver reinforces the serial bottleneck instead of parallel bulk transfer.

Eventhouse acceleration is unrelated to this relational Copy-vs-loop movement path.

Question 237

A Copy job replaces custom per-row movement. What validation directly tests volume and data integrity?

Check only the count of Python loop iterations without comparing the Copy activity's transferred bytes.
Compare source and destination key counts, representative mappings, activity throughput and failure metrics over a representative high-volume load.
Compare the Copy activity run title to the original Python script name.
Run the Copy activity under an unrestricted admin identity and ignore the intended production credentials.

Explanation

Correct Answer

A large-scale load test identifies truncation, schema mapping failures and performance limitations before production use.

Incorrect Answers

Iteration count does not establish effective throughput or source-to-sink integrity under bulk Copy.

Matching names provide no performance or correctness evidence.

Privileged access can bypass real production authorization constraints and is not a safe acceptance test.

Question 238

A CSV feed has `quantity` values such as `12`, an empty string and `unknown`. It must load into a validated numeric Delta column. Which transformation design is appropriate?

Cast every quantity directly to INT and turn off error capture for invalid text.
Replace all nonnumeric quantities with zero silently before checking business rules.
Apply explicit numeric conversion with a documented invalid-value rule, separating nulls or rejected records from successfully parsed quantities.
Preserve the `quantity` column as unvalidated text and claim the numeric Delta schema is satisfied.

Explanation

Correct Answer

Unvalidated implicit casting can turn bad input into unexpected nulls or fail a write; an explicit schema contract preserves data quality.

Incorrect Answers

Empty/unknown fields may fail conversion or be lost without an approved reject/null policy.

Zero may be a valid quantity and must not be used as an unapproved substitution for missing/invalid records.

Text-only values violate the required numeric target contract and prevent trustworthy arithmetic.

Question 239

A pipeline fails converting a CSV `quantity` field into an integer target. Select TWO technical conditions that can explain this failure.

The source CSV lacks a SQL table index for the `quantity` field.
The Copy activity hasn't enabled OneLake V-Order on the target.
Some source values such as `unknown` are not valid integer representations.
The default notebook lakehouse is different from the CSV source folder.
The transformation passes inferred string values into a strict numeric sink without a validated conversion.

Explanation

Correct Answers

An incompatible token cannot be cast to an integer without an explicit error or null/quarantine policy.

Source schema inference and target type requirements differ; conversion must be deliberate and testable.

Incorrect Answers

CSV parses text fields; an absent SQL index does not explain integer casting of malformed values.

Delta file layout does not change whether source strings can be converted into integer values.

A notebook default mount does not repair invalid or out-of-range numeric strings at the mapping boundary.

Question 240

The CSV parser is updated for malformed numeric values. What is the most useful acceptance test?

Test only a CSV row with a valid number, without malformed or out-of-range samples.
Check only total row counts, ignoring the rejected-row quarantine and original raw values.
Change target integers to strings silently and assume numeric business calculations still work.
Load a fixture with valid integers, blanks, negative values and an invalid token; reconcile parsed rows, null handling and quarantined errors.

Explanation

Correct Answer

Mixed-input testing distinguishes successful numeric conversion from silent data loss or undesired type coercion.

Incorrect Answers

Happy-path examples won't reveal unresolved invalid-token or overflow handling defects.

Counts can mask dropped or coerced bad rows and fail to validate the intended error policy.

Avoiding conversion by changing types can violate the expected schema and arithmetic semantics.

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

Microsoft DP-700 Practice Test 22 - 10 Questions (Last Reviewed 13-Oct-2026)
Microsoft DP-700 Practice Test 23 - 10 Questions (Last Reviewed 12-Oct-2026)
Microsoft DP-700 Practice Test 24 - 10 Questions (Last Reviewed 11-Oct-2026)
Microsoft DP-700 Practice Test 25 - 10 Questions (Last Reviewed 10-Oct-2026)
Microsoft DP-700 Practice Test 26 - 10 Questions (Last Reviewed 09-Oct-2026)

Pass Your Microsoft DP-700 Exam Easy!

Question 230

Explanation

Correct Answer

Incorrect Answers

Question 231

Explanation

Correct Answers

Incorrect Answers

Question 232

Explanation

Correct Answer

Incorrect Answers

Question 233

Explanation

Correct Answers

Incorrect Answers

Question 234

Explanation

Correct Answer

Incorrect Answers

Question 235

Explanation

Correct Answer

Incorrect Answers

Question 236

Explanation

Correct Answer

Incorrect Answers

Question 237

Explanation

Correct Answer

Incorrect Answers

Question 238

Explanation

Correct Answer

Incorrect Answers

Question 239

Explanation

Correct Answers

Incorrect Answers

Question 240

Explanation

Correct Answer

Incorrect Answers

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