Forum Discussion
Incremental loading from multiple F&O tables using Fabric Link
For example:
However, I would like to avoid reprocessing the full dataset every time and instead load only the delta since the last execution.
One possible approach would be to store the SinkModifiedOn watermark for each table in a configuration table, and then apply an OR condition in the query, such as:
Is there a simpler or more elegant approach?
For example, would it make sense to store only a single maximum SinkModifiedOn value (across all tables) in the configuration table, and then compare each table’s SinkModifiedOn against this generic maximum date, like this:
Hi diegofosso ,
When working with Fabric Link for F&O, there are a few important aspects to consider when designing incremental loads.
Each table includes the SinkModifiedOn field, which should be used as the basis for incremental logic. However, it is also important to take into account the IsDelete field. Fabric Link does not physically delete records; instead, deletions are tracked logically using this flag, so your process must handle inserts, updates, and deletes accordingly.
From an architectural perspective, a good approach is to treat the data coming from Fabric Link as a landing layer (Bronze). Fabric Link already delivers data incrementally and in Delta format, so it is better to avoid embedding complex transformation logic at this stage.
From there, the recommended pattern would be:
- Keep the landing layer as-is from Fabric Link
- Apply incremental loading per table using SinkModifiedOn
- Handle deletions using IsDelete
- Consolidate data into a Silver layer using MERGE/UPSERT
- Perform joins and modeling in a Gold layer
This aligns naturally with a medallion architecture, which works especially well in this type of scenario.
Additionally, you can leverage Delta Change Data Feed (CDF) to process row-level changes between table versions, which allows you to build a more robust and decoupled incremental process.
It is also worth considering the use of materialized views(now in GA) to simplify consumption and expose curated datasets.
In summary, rather than trying to solve incremental loading with a single query across multiple tables, the key is to:
- Think in terms of incremental per table
- Use both SinkModifiedOn and IsDelete correctly
- Separate ingestion, transformation, and consumption layers
- Leverage native Fabric capabilities such as Delta and Change Data Feed
This approach is much more robust and aligned with how Fabric Link actually works.
If my comment helped solve your question, it would be great if you could mark it as the accepted solution. It helps others with the same issue and it also motivates me to keep contributing.
Thanks a lot. I really appreciate it
4 Replies
Hi diegofosso ,
Thank you for reaching out to Microsoft Community.Maintaining separate SinkModifiedOn watermarks for each table is the correct and recommended approach. This aligns with how Fabric Link writes data into OneLake, where each table is processed independently and may experience different ingestion latencies. Using a single global watermark can lead to missed records, especially when changes in slower tables arrive later than others.
From a design perspective, instead of combining everything into a single SQL query with OR conditions, it is better to perform incremental ingestion per table, storing and updating a dedicated watermark for each table in a control/config table
Load the incremental data into your Lakehouse using these table specific filters. Apply joins downstream after all tables have been incrementally updatedIf there is a strict requirement to use a single query, then applying separate watermark filters within subqueries per table is a safer alternative than using a shared watermark. However, this can still result in partial or inconsistent joins when only one table has new data.
Hope this helps.
Thank you.Hi diegofosso ,
When working with Fabric Link for F&O, there are a few important aspects to consider when designing incremental loads.
Each table includes the SinkModifiedOn field, which should be used as the basis for incremental logic. However, it is also important to take into account the IsDelete field. Fabric Link does not physically delete records; instead, deletions are tracked logically using this flag, so your process must handle inserts, updates, and deletes accordingly.
From an architectural perspective, a good approach is to treat the data coming from Fabric Link as a landing layer (Bronze). Fabric Link already delivers data incrementally and in Delta format, so it is better to avoid embedding complex transformation logic at this stage.
From there, the recommended pattern would be:
- Keep the landing layer as-is from Fabric Link
- Apply incremental loading per table using SinkModifiedOn
- Handle deletions using IsDelete
- Consolidate data into a Silver layer using MERGE/UPSERT
- Perform joins and modeling in a Gold layer
This aligns naturally with a medallion architecture, which works especially well in this type of scenario.
Additionally, you can leverage Delta Change Data Feed (CDF) to process row-level changes between table versions, which allows you to build a more robust and decoupled incremental process.
It is also worth considering the use of materialized views(now in GA) to simplify consumption and expose curated datasets.
In summary, rather than trying to solve incremental loading with a single query across multiple tables, the key is to:
- Think in terms of incremental per table
- Use both SinkModifiedOn and IsDelete correctly
- Separate ingestion, transformation, and consumption layers
- Leverage native Fabric capabilities such as Delta and Change Data Feed
This approach is much more robust and aligned with how Fabric Link actually works.
If my comment helped solve your question, it would be great if you could mark it as the accepted solution. It helps others with the same issue and it also motivates me to keep contributing.
Thanks a lot. I really appreciate it
Hi diegofosso ,
Thank you arabalca for your inputs.We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.
Best Regards,
Chaithra E.Hi diegofosso ,
May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.
Thank you.
