Incremental Refresh - 'Unable to confirm if the M query can be folded'

[mestee]

Hello,

Background:
My semantic model has a fact table (source: Oracle). In the connection to Oracle, there is a SQL query in the SQL statement box. There were NO other transformations on this fact before I started this exercise (i.e., only 'Source' shows under Applied Steps). The query has 3 CTE's with a final select statement and returns data for 3 time periods based on a date column: current month, prior month, and prior year, with (currently) approximately 750K rows per each time period. However, that row count may increase as finer granularity is added (by day...).

Goal:
Implement incremental refresh such that only the current month data set is refreshed (the prior time periods should not be refreshed after initial load).

What I've done:
1. Added parameters RangeStart and RangeEnd with default dates.
2. Filtered the fact date column to use the parameters: Edit query > click on Source > on the date column, add custom filter : Keep rows where date is after or equal to RangeStart And is before RangeEnd.
2a. Note: as mentioned above, after the above step is added, I right click Filtered Rows and 'View Native Query' is NOT enabled.
3. Set up incremental refresh on the fact: right clicked the fact > Incremental Refresh > enabled and added configurations. 
3a. At the top of the box for this step, it says 'Unable to confirm if the M query can be folded. It is not recommended to use incremental refresh with non-foldable queries.'

Questions:
1. Are there any workarounds to enable query folding? I've tried updating the Power Query M code to several examples I found online but I either get the message 'Expression.Error: 'EnableFolding' isn't a valid option for this function. Valid options are: (none).' and/or View Native Query never gets enabled in the Power Query steps.
2. If no workaround exists, are there any benefits to implementing incremental refresh anyway? 
Thanks All!

[Stachu · Accepted solution]

Hello

Did you add the filter with the UI? Can you share the M code of the whole query? 
In general Power Query cannot verify folding it doesn't necesserily mean it's not working, you can verify that with Query Diagnostics (article from Chriss Webb where he uses Query Diagnostics to check OData folding).

If the only action is filtering on a range I would expect it to work actually.

As for the incremental refresh - if the folding is not working it's a bad idea, engine will create partitions as per policy, but as the folding will not be working, for each partition it will load full table and filter in memory - extremely inefficient, basically removes the whole benefit of having incremental refresh

[mestee]

I made updates to the M code, specifically, I had this "[EnableFolding=true]", as a last step and I removed that. At that point, the last step did show View Native Query enabled. I deployed to the Service and partitions were created as expected. What I'm not sure of without running the Query Diagnostics as mentioned is whether or not the folding was actually happening. From what I've read, if 'View Native Query' is enabled, it's likely that it is folding back to the source. In the end, I decided to revert the change as we are starting to move the fact tables to the Lakehouse. I will mark this as the solution because there was enough information here, specifically the reference to article on Query Diagnostics, to assist me. For completeness, in the M code, I had 3 steps: 
1. Source = Oracle.Database(....),
2. SqlText = " [the SQL query] ",
3. Data =
Value.NativeQuery(
Source,
SqlText,
[RangeStart = RangeStart, RangeEnd = RangeEnd]
)
in
Data
Step 3 is where I had [EnableFolding=true] as a last step and where the error was coming from. Once that was removed, the error went away and the step showed 'View Native Query' enabled.
What I would suggest to anyone coming across this post is do what @Stachu suggested and ensure query folding is actually happening by running the Query Diagnostics. If it's not folding back to the source, incremental refresh is not a good solution.

[v-pnaroju-msft · Accepted solution]

Thankyou, @Stachu for your response.

Hi mestee,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

We would like to inquire whether have you got the chance to check the solution provided by @Stachu to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Thank you.

[Stachu · Accepted solution]

Hello

Did you add the filter with the UI? Can you share the M code of the whole query? 
In general Power Query cannot verify folding it doesn't necesserily mean it's not working, you can verify that with Query Diagnostics (article from Chriss Webb where he uses Query Diagnostics to check OData folding).

If the only action is filtering on a range I would expect it to work actually.

As for the incremental refresh - if the folding is not working it's a bad idea, engine will create partitions as per policy, but as the folding will not be working, for each partition it will load full table and filter in memory - extremely inefficient, basically removes the whole benefit of having incremental refresh