Incremental taking longer time than normal refresh

[yogita_2025]

Below mentioned is the sample query I am using in tables and views to apply filter based on RangeStart and Range End:

let

source = Databricks.Query(ServerHostname, HTTPPath, [Catalog=null, Database=null, EnableAutomaticProxyDiscovery=null]),

part1 = "select cast(invDte as TIMESTAMP) as invDte,trxTyp,yy,mm,invDte as `invDte Modelling`,invPD,invWhs,invItm,invExp,invOpnQty,invOpnQtyCar,invOpnAmt,invOHQty from " & CatalogName & ".dl_datamart_hkdb.vbi_inventory where `invDte` >= '" & DateTime.ToText(RangeStart,"yyyy-MM-dd HH:mm:ss") & "' and `invDte` <= '" & DateTime.ToText(RangeEnd,"yyyy-MM-dd HH:mm:ss") & "'",

myquery=source(part1)

in

myquery

Native query is enabled in this process and then I am implementing Incremental refresh for 2 months but the time used for normal refresh is lesser than the time used for incremental refresh.

[v-karpurapud]

Hi @yogita_2025 

Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thank you @Akash_Varuna for responding on this topic.

The issue you are experiencing, where incremental refresh takes longer than a full refresh, is usually due to query folding being disrupted by the use of a native SQL query constructed as a text string within Power Query.

Even though you are applying RangeStart and RangeEnd parameters in your SQL query, Power BI treats fully constructed SQL strings as opaque. This prevents the service from analyzing or optimizing the query, resulting in partition filters not being pushed to the source. Consequently, this breaks the incremental refresh optimization, leading to slower performance compared to a full refresh.

To address this, we recommend avoiding raw SQL string construction within Power Query when implementing incremental refresh. Instead, use navigational queries that allow Power BI to apply RangeStart and RangeEnd filters via query folding, enabling efficient partition-based refresh behavior.

If using SQL is necessary in your scenario, consider moving the logic to Databricks by creating a view that accepts invDte as a filter parameter. This allows filter logic to remain at the source while maintaining performance benefits.

Additionally, ensure that the invDte column in your source is properly partitioned and, where applicable, apply ZORDER BY invDte to further optimize query performance. Avoiding string-concatenated SQL within Power BI is essential to ensure folding and performance remain intact.

For reference: Understanding query evaluation and query folding in Power Query - Power Query | Microsoft Learn
Incremental refresh for semantic models in Power BI - Power BI | Microsoft Learn
Optimization recommendations on Azure Databricks - Azure Databricks | Microsoft Learn

If this response resolves your query, kindly mark it as Accepted Solution to help other community members. A Kudos is also appreciated if you found the response helpful.

Thank you!

[yogita_2025]

Hi, Thanks for the clarification. I have some more issues

Power Bi report is connected with Databricks.

Databricks does not support view native query which does not ensure query folding.

If I setup SQL query myself in advanced editor that is also not working as required as it is first loading the whole data and then applying incremental which leads to increased refresh time.

Can I apply Incremental refresh on Power BI in this case.

[v-karpurapud · Accepted solution]

Hi @yogita_2025

The Power BI report is connected to Databricks, and incremental refresh has been configured using native SQL queries with RangeStart and RangeEnd parameters. However, during the refresh process, it is observed that the incremental refresh is taking significantly longer than a full refresh, which defeats the purpose of implementing incremental loading.

The performance issue arises because Databricks views do not support native query folding, and manually constructing SQL queries in Power BI breaks folding as well. As a result, Power BI cannot push partition filters directly to the source, causing the entire dataset to load first before filters are applied. This leads to inefficient refresh behavior and significantly increased processing time.

To enable effective incremental refresh, it is recommended to implement a parameterized table function in Databricks that accepts date filters (startDate, endDate) as arguments. This approach allows Power BI to maintain query folding and push filters directly at the source, enabling efficient partition pruning and faster refresh performance. Alternatively, if Microsoft Fabric is being used, leveraging Direct Lake mode or composite models with pre-partitioned data can further optimize refresh strategies and report performance.

If this post helps, kindly mark it as Accepted Solution. 

Thank You!

[Akash_Varuna]

Hi @yogita_2025  Ensure the invDte column has appropriate indexes to optimize filtering and check that query folding is applied to push filters to the source. Monitor queries using SQL Profiler or Power BI Query Diagnostics to identify bottlenecks in the native query. Review the data range being refreshed to ensure partitions are efficient and avoid processing unnecessary data. If incremental refresh still lags, consult your database administrator for performance tuning.