Incremental loading is one of the most important techniques for keeping analytical data current without moving the entire source dataset on every run. Instead of repeatedly copying all historical rows, a pipeline identifies what is new or changed since the previous successful load and processes only that slice.<\/p>\n
Microsoft Fabric supports this pattern through Data Factory pipelines, lakehouses, warehouses, Copy jobs, notebooks, and other data-engineering components. Microsoft\u2019s own Fabric tutorial demonstrates a classic watermark design in which a pipeline reads the previous watermark, calculates a new one, copies rows between the two values, and saves the new watermark only after the copy succeeds.<\/p>\n
The technique appears simple, but production correctness depends on details that are easy to miss: the watermark must be reliable, updates and deletes need an explicit strategy, retries must not skip data, and state must advance only after the intended data is durable. These are practical concerns for data engineers working toward DP-700<\/a> as well as for teams modernizing existing batch pipelines.<\/p>\n A watermark is only one incremental pattern. The best choice depends on what the source can tell you. A monotonically increasing timestamp or sequence can work well when every insert or update changes that value. Change data capture is stronger when the source can expose inserts, updates, and deletes as an ordered change stream. File-based systems may use arrival manifests, partition folders, or object metadata. APIs may provide continuation tokens, \u201cupdated since\u201d filters, or event subscriptions.<\/p>\n Do not choose a watermark merely because it is easy to implement. Ask what changes the source can make after initial insertion. If rows can be updated without changing the selected timestamp, a timestamp watermark will silently miss those updates. If records can be deleted, a query that only selects rows greater than the last watermark cannot represent the deletion at all.<\/p>\n Document the source behavior before building the pipeline. Incremental loading is fundamentally a state-management problem; the extraction mechanism must be able to represent the source state changes that matter to the analytical system.<\/p>\n In the common Fabric pipeline pattern, the old watermark records the point reached by the last successful load. The pipeline then queries the source for the current maximum watermark, which becomes the upper boundary for the present run. Rows greater than the old value and less than or equal to the new value are selected.<\/p>\n Capturing the new upper boundary before the copy begins is important. It gives the run a stable window even if more source rows arrive while the pipeline is executing. Newer rows will be picked up by the next run rather than creating an open-ended extraction that changes underneath the current copy.<\/p>\n The watermark state should advance only after the data has been successfully written. If the state is updated first and the copy later fails, the next run may start after records that were never delivered. If the copy succeeds but the state update fails, the same window may be processed again, so the destination logic should be idempotent or capable of deduplicating a replay.<\/p>\n Retries and reruns are normal operational events. An incremental pipeline that cannot safely replay a batch will eventually create duplicates or force manual cleanup. Design the destination write so that repeating the same source window produces the same final state.<\/p>\n For append-only event data, a stable event identifier can support deduplication. For mutable entities, a merge or upsert can use the business key and a source change timestamp. For file landing, a deterministic batch path or run manifest can prevent the same file from being treated as new twice. The exact implementation depends on the storage layer, but the principle is constant: state should be recoverable without relying on a human to remember which records were already loaded.<\/p>\n Fabric\u2019s Delta-based lakehouse storage is useful here because it supports transactional table operations and table history. The broader data storage and processing<\/a> design still matters, though. Bronze, silver, and gold layers often need different replay behavior, and a raw landing zone should preserve enough source evidence to rebuild curated tables when necessary.<\/p>\n A high watermark assumes that records arrive in an order compatible with the watermark column. Real systems often violate that assumption. A transaction might be created today with an event time from yesterday, a mobile client might sync after being offline, or an upstream batch might arrive late. If the pipeline uses event time as the only filter, late records can fall behind the stored watermark and never be selected.<\/p>\n One solution is a controlled overlap window: each run looks back a defined period and relies on destination deduplication to absorb repeated records. Another is to watermark on a source-system modification field rather than business event time. Change data capture can be better still when it provides ordered change positions independent of event timestamps.<\/p>\n The look-back period should come from measured source behavior, not guesswork. Monitor lateness distribution and choose a window that captures realistic delays without forcing excessive reprocessing.<\/p>\n Incremental copy is easiest for append-only data. Mutable source tables need more thought. If a record changes, the pipeline must know that it changed and decide how the lakehouse should represent the new state. A silver table might keep only the latest version, maintain effective-dated history, or append changes to an audit-style table.<\/p>\n Deletes are frequently forgotten. Options include source-provided tombstones, CDC delete events, soft-delete flags, periodic reconciliation, or an occasional full comparison for datasets where deletion matters. The right choice depends on whether downstream consumers require current-state accuracy, historical reconstruction, or both.<\/p>\n A useful rule is that the incremental mechanism should preserve the semantics required by the destination. If the downstream model expects a current customer list, deleted customers cannot simply remain forever because the extraction process has no delete path.<\/p>\n Fabric pipelines can be parameterized so the same orchestration pattern processes multiple tables. A configuration table might store source table, destination table, watermark column, extraction query, and processing mode. This can dramatically reduce duplicated pipeline logic.<\/p>\n However, metadata-driven ingestion works only when the datasets truly share the same semantics. One table may be append-only, another may require upserts, and a third may need hard-delete handling. Do not force these into one generic pattern if doing so makes the behavior opaque. Reuse the common orchestration and keep the exceptional business logic explicit.<\/p>\n For readers stepping into this area from a broader platform perspective, the existing discussion of Microsoft Fabric and Power BI<\/a> helps distinguish the engineering layer from the reporting layer. Incremental loading is an engineering concern whose success is eventually experienced by report users as fresher data and more predictable refresh behavior.<\/p>\n An incremental pipeline can report \u201cSucceeded\u201d and still be wrong. Monitor the state of the data, not only the activity result. At minimum, record the old watermark, new watermark, rows read, rows written, duration, and run identifier. Alert when the watermark stops advancing unexpectedly, when row volume deviates sharply from normal, or when freshness exceeds the service target.<\/p>\n Reconciliation is especially valuable for important datasets. Compare source counts or aggregates against the destination for the processed window. For mutable sources, periodically validate that the current-state representation still matches the source. These checks catch silent failures such as an incorrect filter condition, a timezone conversion problem, or a watermark column that stopped updating.<\/p>\n Operational logs should also make replay decisions easier. If an operator can see the exact window associated with a failed run, recovery becomes a controlled action rather than an improvised query.<\/p>\n Incremental ingestion is not an isolated optimization. It affects partitioning, table design, transformation logic, data quality, monitoring, and downstream refresh. A lakehouse may ingest incrementally into bronze but rebuild selected silver aggregates. A gold table may refresh only partitions affected by the new data. Materialized lake views can also choose an incremental refresh strategy when Fabric can determine that only new or changed source data needs processing.<\/p>\nChoose an incremental pattern that matches the source<\/h3>\n
Understand the old-watermark and new-watermark boundary<\/h3>\n
Make the destination safe for replay<\/h3>\n
Account for late-arriving and out-of-order data<\/h3>\n
Handle updates and deletes deliberately<\/h3>\n
Parameterize the pattern without hiding exceptions<\/h3>\n
Monitor freshness, volume, and watermark progression<\/h3>\n
Use incremental loading as part of a wider data-engineering design<\/h3>\n