The first design decision is whether each run reloads everything or only what changed. A full load is simple and self-correcting; an incremental load is faster and cheaper but depends on a reliable way to detect change.
Choosing between full and incremental
A full load copies the entire source on every run; an incremental load copies everything once and then only new or changed rows. Microsoft’s dimensional modelling guidance says fact tables should be loaded incrementally whenever possible, and truncating and reloading a large fact table should be a last resort because of the time, compute and disruption to source systems.
| Aspect | Full load | Incremental load |
|---|---|---|
| What each run moves | All rows | Only rows inserted or changed since the last run (deletes only with CDC or change data feed) |
| State to keep | None | Last watermark, version or CDC position |
| Typical write method | Overwrite, or truncate then insert | Append, upsert/merge, or SCD Type 2 |
| Handles source deletes | Yes, implicitly (destination is rebuilt) | Only with CDC, change data feed or a soft-delete flag |
| Cost on large tables | High | Low |
| Self-correcting after errors | Yes | Needs a reset or reload to fix drift |
| Good fit | Small lookups, no change column, periodic rebuilds | Large facts, frequent schedules, near real-time sync |
Ways to detect change
Fabric gives several mechanisms for finding the rows that changed. Each has different prerequisites and different blind spots. Figure 7.2 summarises which changes each approach can see.
| Mechanism | Where it lives | Detects inserts | Detects updates | Detects deletes | Prerequisite |
|---|---|---|---|---|---|
| Watermark column (pipeline pattern) | Pipeline: Lookup + Copy + Stored procedure | Yes | Only if the column changes on update | No | Monotonically increasing column; a control table you maintain |
| Copy job watermark incremental copy | Copy job (state managed for you) | Yes | Yes, when the watermark changes | No | ROWVERSION, datetime, date, integer or datetime-like string column |
| Copy job CDC incremental copy | Copy job | Yes | Yes | Yes | CDC enabled on the source and supported by the connector |
| Delta change data feed (CDF) | Lakehouse Delta table property | Yes | Yes (pre- and post-image) | Yes | delta.enableChangeDataFeed = true set before the changes happen |
| Mirroring | Mirrored database item | Yes | Yes | Yes | Supported source; continuous replication into read-only Delta |
| Dataflow Gen2 incremental refresh | Dataflow query setting | Bucket-level | Bucket-level (whole bucket replaced) | Within a refreshed bucket | DateTime filter column, a change-detection column, foldable query |
Common trap: Assuming a watermark-based incremental load keeps the destination exactly in sync with the source - watermark-based copying only picks up rows whose watermark value is greater than the last one recorded, so rows deleted from the source stay in the destination. Use CDC (or change data feed) when deletes must be replicated.