Forum Discussion
Need help regarding fabric
We currently have a transactional EMR database where operational data such as patients, visits, inventory, invoices, and users is generated. Earlier, this data was incrementally synced using Azure Data Factory into an Azure SQL database that acted as our data warehouse, and Power BI imported data from this warehouse for reporting, which led to refresh failures, data duplication, and scalability issues as the data grew to around 200 GB. We are now replacing the Azure SQL data warehouse with a Microsoft Fabric Warehouse, while keeping the same change-tracking-based incremental sync logic through Fabric pipelines. The Fabric Warehouse will serve as the single shared analytics layer where staging and final reporting tables are maintained using SQL MERGE operations, and Power BI will connect directly to the warehouse using DirectQuery, eliminating dataset duplication, refresh dependency, and improving reliability, performance, and scalability.
Hi Nikita29
If you're planning to use Microsoft Fabric Data Warehouse, the best method would be DirectLake, and not DirectQuery.
Data warehouses (as well as lakehouses and other Fabric items) persist their data in Delta Parquet format inside OneLake — meaning your warehouse tables physically reside there. Because your warehouse lives in OneLake, Power BI’s Direct Lake connectivity mode can query the Delta tables directly — combining the fresh, low-latency benefits of Import mode with the convenience of DirectQuery, all without moving data around.It is one that Microsoft would recommend because:
- It avoids duplication (no import).
- It delivers fast performance like Import.
- It keeps data fresh like DirectQuery.
- It leverages Fabric’s unified storage (OneLake).
Here's a Microsoft Fabric link that explains it.
Direct Lake overview - Microsoft Fabric | Microsoft Learn
Hope this helps - please appreciate leaving a Kudos or accepting as a Solution!
6 Replies
KevinChantSuper User
Hi there, any particular reason why you have opted to use DirectQuery instead of Direct lake mode?
Direct Lake overview - Microsoft Fabric | Microsoft Learn- suparnababu8
Hi Nikita29
As per your note, I understood that, you're planning to migrate from Azure(ASQL+ADF) to MS Fabric(Fabric Warehouse + Fabric Pipeliens). If you migarte this and you are going to use Direct Query to avoid large data sets into power BI, then this DirectQuery leverages MSFabric compute power instead of relying on Power BI refresh cycles and also Fabric warehouse serving multiple reporting needs, reducing complexity.
But May I know what information need from community?
Thank you!
Did I answer your question? Mark my post as a solution!
Proud to be a Super User! - svenchio
Hi Nikita29 you missed to include and ask, so, I'm assuming you want an opinion from the community on the approach, here's mine:
Plan seems resonable and aligns well with best practices for scalability and reliability, I thinking moving from Azure SQL DW to Microsoft Fabric Warehouse to keep your SQL-ecosystem that you otherwise would have to refactor with a Lakehouse, right? (believe me, been there😅) ; using DirectQuery for Power BI is a smart choice (and neccesary in this case) because:
- It eliminates dataset duplication and refresh dependency, reducing failures.
- SQL MERGE for incremental sync is efficient for maintaining staging and reporting tables.
- Fabric Warehouse provides better performance and elasticity for growing data volumes (200 GB+).
- DirectQuery ensures real-time reporting without heavy refresh cycles.
Just ensure:
- Proper indexing and partitioning in the Warehouse for query performance.
- Monitor capacity usage to avoid CU bottlenecks.
- Validate incremental logic for consistency during sync.
- Keep in mind current limitations in warehouses https://learn.microsoft.com/en-us/fabric/data-warehouse/limitations (e.g. Unsupported types, Identity columns are supported but limited, Primary, foreign, and unique keys are supported only as nonclustered and NOT ENFORCED, etc.)
Yes, I missed to include and ask for an opinion on the best approach to implement it.
1. I was confused with 2 options Fabric Warehouse or Lakehouse, but now am planning to use Fabric Lakehouse.
2. So, while I use lakehouse, I can eliminate SQL merge on lakehouse side and use upsert logic right?
3. Are performance & elasticity features available for Lakehouse too like you mentioned for warehouse?
- deborshi_nag
Hi Nikita29
If you're planning to use Microsoft Fabric Data Warehouse, the best method would be DirectLake, and not DirectQuery.
Data warehouses (as well as lakehouses and other Fabric items) persist their data in Delta Parquet format inside OneLake — meaning your warehouse tables physically reside there. Because your warehouse lives in OneLake, Power BI’s Direct Lake connectivity mode can query the Delta tables directly — combining the fresh, low-latency benefits of Import mode with the convenience of DirectQuery, all without moving data around.It is one that Microsoft would recommend because:
- It avoids duplication (no import).
- It delivers fast performance like Import.
- It keeps data fresh like DirectQuery.
- It leverages Fabric’s unified storage (OneLake).
Here's a Microsoft Fabric link that explains it.
Direct Lake overview - Microsoft Fabric | Microsoft Learn
Hope this helps - please appreciate leaving a Kudos or accepting as a Solution!
I am planning to use MS Fabric Lakehouse. Thanks for your opinion.
