Forum Discussion
Super UserHow to properly do incremental load in Microsoft Fabric Lakehouse notebooks?
Hello,
We are using Microsoft Fabric Lakehouse notebooks and want to understand the proper way to do incremental load.
We are not sure about the correct behavior when:
- appending data to tables that already exist
- updating rows that are already in the target table
- handling cases where some rows are new and some rows already exist
- keeping the specific order for tables
Does append also merge existing rows, or is a different approach needed for updates?
What is the proper notebook-based pattern for this in Fabric Lakehouse?
Best,
Jacek
Hi jaryszek , Thank you for reaching out to the Microsoft Community Forum.
In Fabric Lakehouse, you’re working with Delta tables, so the behaviour is straightforward once you separate append from upsert. Append only inserts new rows, it does not check for existing records, does not update anything and will create duplicates if the same keys already exist. So no, append does not merge or update rows.
For incremental loads where some rows are new and some already exist, the correct notebook pattern is to use MERGE INTO (upsert). You first read only the incremental data (using a watermark, last-updated column or CDC), then merge it into the target table using a business key. This single operation handles both scenarios cleanly, updates matching rows and inserts new ones in one step.
There’s also no concept of maintaining row order during load, since Delta tables are distributed storage and don’t preserve insertion order. If order matters, apply it at query time using ORDER BY. So, the proper approach is to extract incrementally -> use MERGE for upsert -> avoid append unless the data is strictly insert-only (like logs).
2 Replies
v-hashadapuCommunity Support
Hi jaryszek , Thank you for reaching out to the Microsoft Community Forum.
In Fabric Lakehouse, you’re working with Delta tables, so the behaviour is straightforward once you separate append from upsert. Append only inserts new rows, it does not check for existing records, does not update anything and will create duplicates if the same keys already exist. So no, append does not merge or update rows.
For incremental loads where some rows are new and some already exist, the correct notebook pattern is to use MERGE INTO (upsert). You first read only the incremental data (using a watermark, last-updated column or CDC), then merge it into the target table using a business key. This single operation handles both scenarios cleanly, updates matching rows and inserts new ones in one step.
There’s also no concept of maintaining row order during load, since Delta tables are distributed storage and don’t preserve insertion order. If order matters, apply it at query time using ORDER BY. So, the proper approach is to extract incrementally -> use MERGE for upsert -> avoid append unless the data is strictly insert-only (like logs).
- jaryszek
thank you
