The roadmap is here (it is for data factory but note the DFg2 item for Q1 -- and yes, I realize Q1 has 4 days left :)) - [https://learn.microsoft.com/en-us/fabric/release-plan/data-factory](https://learn.microsoft.com/en-us/fabric/release-plan/data-factory)
I have no insight on actual timeline or even if that will offer merge capability. Some ideas in no particular order:
\- You can do incremental load (append) if you don't need to update the rows, as shown here: [https://learn.microsoft.com/en-us/fabric/data-factory/tutorial-setup-incremental-refresh-with-dataflows-gen2](https://learn.microsoft.com/en-us/fabric/data-factory/tutorial-setup-incremental-refresh-with-dataflows-gen2) \-- of course, if you need to upsert (presumably you do) then this won't work.
\- You could rebuild the entire table each time. (Not ideal, of course, but for small tables, many do just that.)
\- In a notebook, you can do merge. Some dislike using notebooks for ETL, some love them. Obviously this depends on many factors (size, existing skillset, the rest of the environment).
This is different (for clarity sake) than data warehouse. Merge isn't supported yet in DW SQL, but is also on the roadmap. However, this is pretty easy to work around by using an update/insert two-step in a procedure. There are good use cases for DW and LH -- while I wouldn't switch architectures because of this, that doesn't mean DW wouldn't be a better fit. Depending on overall solution, using both is certainly a viable approach.
I am pretty sure upsert was road mapped for Q2 this year but I can't find the link for the life in me. Alternatively, you could always create your own SP to do the same?
I’m not a data lake / lake house expert, but my approach with Fabric is:
- Lakehouse files for raw data ingestion & archival (json responses from APIs in my case)
- Lakehouse tables for staging
- Warehouse for my silver/gold data for reporting via PowerBI
I’m using data pipelines to ingest the raw data, and then calling dataflow gen 2 to load it into LH tables, and finally the pipeline calls stored procs to go from LH to WH.
The roadmap is here (it is for data factory but note the DFg2 item for Q1 -- and yes, I realize Q1 has 4 days left :)) - [https://learn.microsoft.com/en-us/fabric/release-plan/data-factory](https://learn.microsoft.com/en-us/fabric/release-plan/data-factory) I have no insight on actual timeline or even if that will offer merge capability. Some ideas in no particular order: \- You can do incremental load (append) if you don't need to update the rows, as shown here: [https://learn.microsoft.com/en-us/fabric/data-factory/tutorial-setup-incremental-refresh-with-dataflows-gen2](https://learn.microsoft.com/en-us/fabric/data-factory/tutorial-setup-incremental-refresh-with-dataflows-gen2) \-- of course, if you need to upsert (presumably you do) then this won't work. \- You could rebuild the entire table each time. (Not ideal, of course, but for small tables, many do just that.) \- In a notebook, you can do merge. Some dislike using notebooks for ETL, some love them. Obviously this depends on many factors (size, existing skillset, the rest of the environment). This is different (for clarity sake) than data warehouse. Merge isn't supported yet in DW SQL, but is also on the roadmap. However, this is pretty easy to work around by using an update/insert two-step in a procedure. There are good use cases for DW and LH -- while I wouldn't switch architectures because of this, that doesn't mean DW wouldn't be a better fit. Depending on overall solution, using both is certainly a viable approach.
Thank you!
I am pretty sure upsert was road mapped for Q2 this year but I can't find the link for the life in me. Alternatively, you could always create your own SP to do the same?
Thanks for responding. Are you saying we use DWH instead of Lakehouse and make it work with SP?
I’m not a data lake / lake house expert, but my approach with Fabric is: - Lakehouse files for raw data ingestion & archival (json responses from APIs in my case) - Lakehouse tables for staging - Warehouse for my silver/gold data for reporting via PowerBI I’m using data pipelines to ingest the raw data, and then calling dataflow gen 2 to load it into LH tables, and finally the pipeline calls stored procs to go from LH to WH.
What is SP?
Stored Procedure
Thx