Best Practice for bringing data from Lakehouse to Warehouse

[JulianaMacedo]

Hi!

Have a client that is implementing Fabric in the lowest capacity possible due to budget constraints. To try to maintain costs under control we are trying to find the best practices or best way to bring data from Lakehouse into the Warehouse that will minimize CU usage, can be scheduled, needs to have incremental refresh and uses SQL to get data.
We've tried a bit with a Copy Activity and SQL queries inside this activity, which achieves most of the requirements but it seems that CU usage is still high. 

Any recommendations? We are thinking about trying stored procedures and schedule it through a pipeline...any other ideas?
We have not considered mirroring because most of the tables have no column fro date / time from source systems. 

[v-prasare]

Hi @sam51,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[JulianaMacedo]

Hi yes it has helped.

[v-prasare]

Hi @JulianaMacedo,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[apturlov · Accepted solution]

@JulianaMacedo If your goal is to load data into a Warehouse from a Lakehouse incrementally and create a separate materialized dataset as opposed to what @KevinChant suggested as a dynamic view reading data directly from the Lakehouse, then you need to use a SQL MERGE query. That would be, to my best knowledge, the most efficient way of incremental load into a Warehouse. To run such a query the easiest way is to use a Factory data pipeline with a Script activity and code SQL right in the activity. I bit more involved way is to create a stored procedure in the Warehouse and use a Stored Procedure activity instead. You can trigger such a pipeline using OneLake data event trigger that will occur when any table in the Lakehouse is modified which is determined by a new file created in the delta log folder for that table.

Hope that helps.

[KevinChant]

Are they in the same workspace? if so, instead of copyng the data over and duplicating the data create a view in the Warehouse that points to the lakehouse table.

[JulianaMacedo]

Yes they are in the same workspace. 
But is it possible to schedule and do incremental load with a View? My knowledge in that is probably limited, but how whould one do that with a View?