What is the best possible solution for incremental Load from On-Prem Server to Fabric Lake?

[Anusha66]

Hi. 

So we have lakehouse as a gold layer to which we want to push incremental load. But can see that Fabric Pipleline donot have 'upsert'.   We are instead running merge statement on Notebooks. 

While can also see that, if we chose Warehouse- Notebooks wont work and there are many T-SQL limitations like 'Merge', Temp tables etc. 

I want to arrive at a best approach for incremental data push to Fabric Lakehouse.

[v-achippa]

Hi @Anusha66,

Thank you for reaching out to Microsoft Fabric Community.

Since fabric pipelines do not support UPSERT, the best way is using Notebooks with Delta merge for incremental updates.

• Extract the data from on-prem to staging table, use a fabric data pipeline to copy incremental data from an on-prem into a staging delta table in lakehouse.
• Create staging table(Bronze layer), this table temporarily holds incremental records before merging.
• Create gold table, this table stores the final merged dataset.
• Once the staging table has incremental data, use a Notebook in fabric to merge into the gold table.

If this post helps, then please consider Accepting as the solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[BIByte]

Like with most things in Fabric "It Depends" on your particular scenario and preference - Are you looking for a code/no code experience, is it easier to idenify deletes from your source system, do you have to process any semi or unstructured data from source etc..

The general preference is

For a lakehouse use a notebook with MERGE to process incremental loads and if you'd prefer a low code way to process incremental loads then try the COPY JOB - https://learn.microsoft.com/en-us/fabric/data-factory/what-is-copy-job this supports incremental loads if you were to supply a watermark column.

MERGE isn't supported in warehouses at the moment but based on the roadmap -https://learn.microsoft.com/en-us/fabric/release-plan/data-warehouse#merge-(t-sql) it should be available in Q1 2025

[jhayes64]

Yes MERGE supported in DW. MERGE (Transact-SQL) - SQL Server | Microsoft Learn 

I like the idea of letting COPYJOB do it's thing for pulling incremental updates from the source. And to continue on the lowcode thing, I am thinking of creating a view (or views) for each table that has  MAX date insert/update for each table. I would then create a materialzied view for each table that has an update date >= MAX date and use that as the source for the MERGE. I woud then only have WHEN MATCHED and WHEN NOT MATCHED BY TARGET statements for performance reasons. This would only work when the source table only has soft deletes. I am going to test that out and will let you know!

[Anusha66]

Yeah, while chosing a lakehouse, we are bound to use notebooks for Merge Statements, but for too many Stored Procedures, replacing with notebooks don't seem to be a good idea, especially when we can't seem to run multiple notebooks at a time for Low Capacities. 

Thank you for your responses!

[v-achippa · Accepted solution]

Hi @Anusha66,

Below is the best approach to optimize performance by still enabling MERGE:

• Use fabric pipelines to load incremental data into a staging delta table in the lakehouse.
• Use a single notebook, instead of running multiple notebooks, create one master notebook that handles all MERGE operations sequentially to reduce compute load and ensure stability in low-capacity environments. After the merge operation, optimize the gold tables so that it improves the query performance.
• Use Warehouse for reporting and Stored Procedures by creating shortcuts from the lakehouse (gold layer) to warehouse.

By following this approach, it ensures a balance between MERGE capabilities and performance optimization.

If this post helps, then please consider Accepting as the solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @Anusha66,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @Anusha66,

We wanted to kindly follow up to check if the solution I have provided for the issue worked.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @Anu66,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa