Hi @GQ00 ,
Thanks for reaching out to the Microsoft fabric community forum.
The behavior you are experiencing with incremental refresh is expected, particularly when moving from DirectQuery to Import mode. Incremental refresh only provides benefits after the initial refresh completes. When the semantic model is first published, Fabric must perform a full historical load for the entire archive window you have configured. In your case, this means loading about 120 million rows from Orders and 720 million rows from OrderLines over six years. The PBIX file size in Desktop around 7 MB does not reflect the actual resources needed for the refresh. The initial refresh requires Fabric to allocate additional memory and compute resources to process all source data, create partitions, compress data, build dictionaries, and manage relationships, which can temporarily exhaust your F8 capacity.
While your policy of storing six years and refreshing two days is suitable for ongoing refreshes, the initial load requires a different approach. It is recommended to bootstrap the historical load in smaller batches using the XMLA endpoint rather than loading all six years at once. Fabric supports XMLA read/write operations for semantic models, enabling you to process partitions incrementally with tools like SQL Server Management Studio or Tabular Editor. Many users load data month by month or year by year until all historical partitions are created. Afterward, scheduled refreshes will only process recent data and require significantly less capacity.
Alternatively, you may consider temporarily reducing the archive period, publishing and refreshing, and then gradually extending the retention window. Scaling up Fabric capacity for the initial load and scaling down afterward is also a viable option.
Regarding your second point, filtering a single Order ID in Power Query does not generate incremental refresh partitions. Partitioning only occurs when the fact table is filtered using the RangeStart and RangeEnd parameters on the date column, which must be set in Power Query before publishing. If these parameters are not properly configured, Fabric will treat the table as a standard import.
Even low cardinality values can use significant memory in large fact tables. It is best practice to replace these text columns with integer keys and store the text in smaller dimension tables, which improves compression and reduces memory usage without affecting business logic.
I recommend focusing on correctly applying RangeStart and RangeEnd filters and using XMLA bootstrapping for the initial load to minimize impact on your F8 capacity.
For further guidance, please refer to the following Microsoft documentation:
https://learn.microsoft.com/power-bi/connect-data/incremental-refresh-configure
https://learn.microsoft.com/power-bi/connect-data/incremental-refresh-xmla
https://learn.microsoft.com/power-bi/enterprise/service-premium-connect-tools
https://learn.microsoft.com/power-bi/guidance/import-modeling-data-reduction.
Thank you.
