Deciding between a full reload and an incremental load

Full loads copy an entire dataset each run, ensuring simplicity and consistency but at higher cost and time as data grows. Incremental loads move only new or changed data since the last run, improving performance and reducing resource use, but require careful design to track changes and avoid data loss or duplication.

1 · Learn the must-know

• 1Incremental loads typically rely on a watermark column (e.g., LastModifiedDate or an incrementing ID) to identify new or changed rows since the last successful load.
• 2In Fabric Data Factory pipelines, you can use parameters and variables combined with a Lookup activity to dynamically retrieve and update the watermark value between pipeline runs.
• 3Fabric Dataflows Gen2 and pipelines support incremental refresh policies, but source systems must expose reliable change-tracking mechanisms (timestamps, CDC, or change tracking) for incremental logic to work correctly.
• 4A common pattern is a metadata-driven pipeline: a control table stores the last watermark per source table, which is read before extraction and updated after a successful load.
• 5Full loads are often used for smaller dimension tables or when source systems lack reliable change tracking, while incremental loads are preferred for large, frequently updated fact tables to reduce pipeline duration and compute cost.
• 6Failure handling matters: if an incremental load partially fails, the watermark should only be updated after data is successfully written to the destination to prevent gaps or duplicate processing on retry.

2 · Check your understanding