DP-700 - Ingest and Transform Data (33% of the exam) - Section 2.1

Design and implement full and incremental data loads, including watermarking and change detection.

Distinguish full loads from incremental loads, and implement watermark columns and high-water mark logic to identify new or changed rows. Apply change data capture, MERGE into Delta tables for upserts, and idempotent reload patterns to keep target datasets consistent.

full versus incremental load patternswatermark columns and high-water markchange data capture and change trackingupsert and MERGE into Delta tablesidempotent reloads

Practice question for this objective

Free sampleIngest and Transform Datahard

An Azure SQL Database source feeds a silver Delta table in Microsoft Fabric. The source customer table receives inserts, updates, and physical deletes, and the silver layer must reflect deletes as well as changes. The source already has a feature enabled that exposes which rows changed since a given version through a system function, without storing the full before-and-after column values. Which source change-detection mechanism does this describe, and is it sufficient to drive the load?

AIt describes a high-water-mark watermark on a LastModified column, which is sufficient because the maximum timestamp identifies every insert, update, and delete to apply.
BIt describes change data capture, which is sufficient because it retains the full before-and-after column images of every changed row in dedicated capture tables.
CIt describes a OneLake shortcut to the source, which is sufficient because the shortcut surfaces source deletes automatically without any change-detection query.
DIt describes change tracking, which is sufficient because it returns the keys and change type of rows changed since a version, including deletes, so the load can apply each change. Correct

Change tracking reports changed keys and the change type including deletes by version without storing full column history, unlike change data capture. The defining clue is that the feature returns which rows changed and the operation type by version but does not retain before-and-after column values; that is change tracking, not change data capture, and because it surfaces delete operations it can drive a load that applies inserts, updates, and deletes to the silver table.

Why A is wrong: A LastModified watermark catches inserts and updates but cannot reveal physically deleted rows, since a deleted row leaves no timestamp to compare; it is tempting but misses the delete requirement entirely.

Why B is wrong: Change data capture does retain full before-and-after column images, but the scenario states the feature does not store full column values; that detail rules out change data capture even though it would also handle deletes.

Why C is wrong: A shortcut exposes existing data in place and is not a row-level change-detection mechanism; it does not tell the load which rows changed or were deleted since a version, so it cannot drive an incremental upsert-and-delete.

Why D is correct: Change tracking records which rows changed and the operation type, including deletes, by version without retaining column history; the changed keys plus change type let the load upsert and remove rows, which is exactly what is needed.

See more DP-700 practice questions, answers explained.

Exam traps in Ingest and Transform Data

Answers that look right on this material and are not. Each one is a distractor from a different question in the DP-700 bank for this domain.

Fully reload the orders fact by overwriting it each run, and incrementally load the product dimension on a watermark column.
Why it is wrong: This inverts the sensible choice: it rereads the large fact nightly while trying to incrementally track a tiny table whose changes are scattered and have no reliable change column, which is both slow and fragile.
Filter the source on a LastModifiedUtc watermark, then MERGE only the rows above the stored mark into silver, updating matched rows and inserting new ones each run.
Why it is wrong: A watermark plus upsert captures inserts and updates, but a physically deleted source row leaves no LastModifiedUtc to exceed the mark, so the deleted row is never seen and stays in silver, which breaks the delete requirement.
Append the daily extract to the silver Delta table so every changed customer is added as an additional row alongside the earlier version.
Why it is wrong: Appending preserves every version and creates duplicate CustomerId rows, so the silver table no longer holds one current record per customer; the requirement asks for in-place updates, not retained history.

Examworthy is not affiliated with or endorsed by Microsoft. Original, blueprint-aligned practice material only.