2.1 — Design and Implement Full and Incremental Loads
DP-700 Domain 2 · Skill 2.1
What You Need to Know
A full load copies everything every time. An incremental load copies only what changed since the last successful run. Incremental loading is cheaper and faster, and it puts less load on the source. Full loads still make sense for small tables, or when the source can't tell you what changed.
Ways to detect changes
| Technique | How it works | Captures deletes? |
|---|---|---|
| High watermark | Store the highest value of a reliably increasing column (ModifiedDate, an identity column, ROWVERSION) and next time read only rows above it |
No |
| Change data capture (CDC) | The source records every insert, update and delete in change tables | Yes |
| Change tracking | The source (SQL Server or Azure SQL) records which rows changed | Yes (keys only) |
| Delta change data feed | A Delta table records row-level changes between versions | Yes |
| File-based | Process only files that arrived since the last run (by folder date, or by last-modified time) | Only if a delete file arrives |
Implementation options in Fabric
- Copy job: a no-code way to do it. The first run copies everything; later runs copy only changes, using a watermark column or CDC. The job tracks its own state, resumes from the last successful run after a failure, and can append, overwrite, merge (upsert) or keep SCD Type 2 history in the destination.
- Pipeline watermark pattern:
- A Lookup activity reads the old watermark from a control table.
- A second Lookup reads the new maximum value from the source.
- A Copy activity copies the rows between the two values.
- A Stored procedure or Script activity saves the new watermark.
- Notebook: read new rows, then MERGE them into a Delta table, as below.
- Mirroring: continuous, managed replication when you want a near real-time replica rather than a scheduled load (lesson 2.4).
from delta.tables import DeltaTable
target = DeltaTable.forName(spark, "silver.customer")
(target.alias("t")
.merge(updates.alias("s"), "t.CustomerID = s.CustomerID")
.whenMatchedUpdateAll()
.whenNotMatchedInsertAll()
.execute())
In a warehouse the same upsert is a T-SQL MERGE, or an UPDATE followed by an INSERT ... WHERE NOT EXISTS.
Design rules
- Save the watermark only after the load succeeds. A failed run then retries the same window, so nothing is lost.
- Make loads idempotent (MERGE on a business key) so that a rerun doesn't create duplicates.
- Fact tables should load incrementally whenever possible. Truncating and reloading a large fact table is a last resort.
- Dimension tables with generated surrogate keys must never be truncated and reloaded, because the fact rows that reference those keys would break.
Key Concept: To pick an incremental method, look at what the source offers. A trustworthy increasing column means watermark. Deletes that must flow through mean CDC. Wanting it no-code and stateful means Copy job.
Exam Traps
- Using a watermark when deleted source rows must also disappear from the target (watermarks don't see deletes)
- Updating the watermark before the copy succeeds
- Appending incremental rows that include updates, which creates duplicates (use merge or upsert)
- Choosing a full reload of a large fact table as the easy option
Check your understanding
Practice Scenario
🔒 This practice scenario is part of Skillmarked Pro
The lessons stay free. Pro unlocks the other 16 DP-700 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.
Launch Deal: first month €9.95, then €19.90/month. Enter code LAUNCH50 at checkout. Ends 1 December.
Cancel anytime. Or €199 a year, which saves 17%.
Not sure yet? Try 10 free mock exam questions.
Already Pro? Log in.
Sources
- What is Copy job in Data Factory, Microsoft Learn
- Incrementally load data from a warehouse to a lakehouse, Microsoft Learn
- Dimensional modeling in Fabric Warehouse: load tables, Microsoft Learn
- Upsert into a Delta Lake table using merge, Delta Lake documentation