Incremental loading reduces the amount of data a pipeline has to read and write, but a production design has to handle more than “rows newer than the last timestamp.” For Microsoft DP-700, the durable engineering problem includes watermarks, change data capture, retries, duplicate prevention, deletes, late-arriving facts, late-arriving dimensions, and safe backfills. A strong DP-700 incremental-loading practice scenario tests what happens to watermarks and duplicate handling after a partial ingestion failure instead of assuming “newer timestamp” is a complete strategy.
Fabric supports several ways to implement incremental movement, including pipeline patterns and Copy job capabilities that track watermarks or CDC state for supported sources. The important architecture principle is independent of the tool: advance state only after successful durable processing, make reruns safe, and keep a deliberate path for records that arrive outside the normal processing window.
A watermark defines where the last successful load ended
A watermark is a value that increases as new or changed source records appear, such as a modification timestamp, sequence number, row version, or integer ID. Fabric Copy job can manage supported incremental state automatically, while custom pipelines can store it in a control table. In either case, the team should know where the checkpoint lives and how it can be inspected or reset. An incremental process stores the last successful watermark and reads only records beyond that point on the next run.
The watermark column must reflect every change the pipeline is expected to capture. Prefer a monotonically increasing value that changes on both insert and update. If the source can update a row without touching the watermark, the pipeline can silently miss that change forever. Profile source behavior before committing the design and document any operations that fall outside the incremental rule. A created-date column misses later updates. A timestamp written inconsistently by applications can create gaps. Select the watermark based on source behavior, not convenience.
Advance the watermark only after the destination is committed
A safe pipeline reads the old watermark, determines an upper bound for the current run, processes records within that window, validates the destination write, and only then stores the new watermark. Capturing the upper bound before extraction prevents new source changes from continuously extending a running window and gives the run a reproducible start and end. If state advances before the data is durable, a failure can permanently skip records.
Treat the watermark update as part of the transaction boundary even when the source and destination cannot participate in one database transaction. Store run state with a status such as started, loaded, validated, and committed so operators can distinguish an unfinished window from a completed one. If validation fails, the next run should replay the same bounded window instead of skipping ahead. The recovery design should make it clear which window must be replayed after a partial failure.
CDC is stronger when inserts, updates, and deletes all matter
Watermark-based extraction works well when a reliable column changes on every insert or update. Fabric Copy job currently supports several watermark-oriented types and can use CDC when a supported database exposes it. Engineers should still understand the semantics because automated state tracking does not decide whether destination writes need append, merge, or history preservation. It usually cannot detect rows deleted from the source unless deletion is represented explicitly. Change data capture can provide insert, update, and delete events when the source and connector support it.
Use CDC when source truth includes deletions or when application-managed timestamps are unreliable. Decide how downstream analytics represent deletes: physical deletion, soft-delete flags, end-dating a dimension row, or retaining history for audit. CDC supplies the change event, but business requirements determine the destination semantics. CDC still needs checkpoint management and downstream merge logic. Capturing a delete event is useful only if the destination knows how that delete should affect analytical history.
Idempotent writes make retries safe
An incremental pipeline should be restartable without producing duplicate facts or corrupting state. Design a stable merge key and define whether the source is authoritative for all columns or only selected attributes. When updates arrive more than once, applying them repeatedly should converge on the same destination state rather than creating another record. Use stable business keys or event identifiers and write patterns such as merge, upsert, replace-partition, or deterministic overwrite where they fit the destination model. That property is idempotency: repeating the same load after a timeout or partial failure should converge on the intended state instead of duplicating facts or applying the same business change twice.
Append-only loads need special care. They work naturally for immutable event streams with stable unique identifiers, but they are dangerous when the source can resend or correct records. If append is retained, add a downstream deduplication rule or partition-replacement strategy so reruns do not inflate facts. Fabric Copy job documentation warns that resetting incremental state and then appending previously loaded rows can create duplicates. If a reset causes a full reread, the destination must be truncated, merged, or otherwise reconciled deliberately.
Late-arriving facts require a lookback or replay strategy
A record can describe an event from yesterday but arrive today because a source system was offline, a file was delayed, or an upstream process retried. Separate event time, source modification time, and ingestion time in the destination when they carry different meanings. That makes lateness measurable and gives analysts a way to explain why historical totals changed after the original event date. If the pipeline filters only on event date and permanently closes yesterday’s partition, that fact can be missed.
Use an ingestion or modification watermark separate from event time, or reprocess a bounded lookback window whose writes are idempotent. Measure the distribution of arrival delay and choose a lookback that covers the business tolerance. A seven-day window may be too expensive for one source and too short for another, so lateness policy should be source-specific. The correct lookback depends on observed lateness. Measure arrival delay so the window is based on evidence rather than an arbitrary number of days.
Late-arriving dimensions can break dimensional joins
A fact row may arrive before the corresponding customer, product, account, or other dimension member exists in the analytical model. This is common when systems publish events independently. Dimensional-loading logic should decide whether the analytical relationship is required immediately or can be repaired later without dropping the fact. A strict join can drop the fact, while a null foreign key can make reports inconsistent.
Common patterns include an inferred or unknown dimension member followed by a later update, or holding facts temporarily until the dimension arrives. If an inferred member is used, preserve the business key so the placeholder can be updated deterministically when descriptive attributes appear. Do not create a second dimension row merely because the real record arrives later. Choose based on business requirements and source reliability. The pipeline should preserve the fact even when descriptive context is incomplete.
Missing and duplicate records need explicit reconciliation
Incremental state can be logically correct while the destination is still wrong because of source corrections, out-of-order files, failed partial writes, or duplicate delivery. Keep row counts, checksums, key uniqueness tests, and source-to-destination reconciliation for critical datasets. Reconciliation should be partition-aware so a late correction in an old month is visible even when the current incremental window succeeds. Store rejected rows and data-quality failures separately rather than quietly discarding them from the analytical history.
Schedule periodic wider-window or full comparisons where the business risk justifies them. Compare not only counts but sums, key coverage, and important business totals. Two datasets can contain the same number of rows while disagreeing on values because an update was missed or applied to the wrong key. Incremental processing optimizes normal operation; reconciliation detects assumptions that stopped being true.
Backfills should not corrupt normal incremental state
Historical corrections, new business rules, or newly available source history may require a backfill. Treat backfills as controlled deployments with explicit start and end boundaries, expected row counts, and rollback or reprocessing plans. If the backfill rewrites partitions, coordinate with downstream semantic models and reports so consumers know when historical results may change. Run backfills through a controlled path that identifies the target range and writes deterministically without moving the everyday watermark forward incorrectly.
Version transformation logic and record which code produced each backfill. Backfills may also need to suppress normal scheduled ingestion for overlapping partitions or coordinate through locks so two jobs do not rewrite the same data concurrently. Treat historical repair as production write activity, not an ad-hoc notebook operation. If the historical job uses a newer rule than current incremental processing, the destination can contain inconsistent definitions across time unless both paths are coordinated.
Operate incremental loading as a stateful system
For DP-700 Fabric data-engineering, full and incremental data loads require explicit handling of duplicate, missing, and late-arriving records. The Microsoft DP-700 study guide publishes versioned skills lists, so candidates should check which objectives are effective on their exam date rather than treating a future-dated revision as current. A watermark is one state variable inside a larger reliability design.
Track source window, destination counts, watermark or checkpoint, rejected rows, late-arrival metrics, retry attempts, and schema version for each run. Add the transformation or pipeline version so operators can reproduce how a row was produced. Stateful ingestion is maintainable only when data lineage includes both the source window and the code that processed it. An incremental pipeline is trustworthy when operators can explain exactly what it has processed, safely rerun a window, and repair historical gaps without guessing.
Operational metrics should expose the state directly: current watermark, rows read and written, inserts versus updates, duplicates rejected, late records replayed, and the last successful commit time. Those measures make it possible to distinguish a slow source from a stuck watermark or a non-idempotent retry. A pipeline that only reports “success” can still be silently losing or duplicating data.