Query hints and native query folding for incremental refresh

[Devale1]

We have a native sql query as source, with queryfolding and with a second step to filter a date column for incremental refresh.

We also want to add query hints, such as OPTION (LABEL='small') which is a classifier for sql pools to assign less resources to the query.

The problem is: the query hint should be at the END of the query, and do to queryfolding, the hint gets wrapped as a subquery and therefore the hint is not at the end, thus throwing an error. 

Any ideas? Why would sql.nativequery not support extra parameters for these query options? 

https://learn.microsoft.com/en-us/sql/t-sql/queries/option-clause-transact-sql?view=sql-server-ver16

[Poojara_D12]

Hi @Devale1 

To resolve the issue of placing SQL query hints at the end when using query folding with incremental refresh:

Key Points:

1. Query Folding Behavior:

   • Power Query applies query folding to transform steps, which can wrap your SQL query with additional subqueries, causing the query hint to appear in the wrong place.

2. Issue:

   • The SQL query hint (OPTION (LABEL='small')) needs to be at the end of the query, but query folding places it incorrectly, causing errors.

Solution Ideas:

1. Disable Query Folding for Incremental Refresh:

   • You can disable query folding after applying the date filter in the second step. This way, the query hint will be correctly placed in the native SQL query.
   • To disable folding, go to Power Query Editor > Right-click on the step after the date filter, and select "Disable Query Folding".

2. Manually Add the Query Hint:

   • After disabling query folding, manually append the query hint (OPTION (LABEL='small')) to the end of the native SQL query in the Advanced Editor.

3. Alternative Approach:

   • If disabling folding is not ideal, consider using a staging query where the filtering is done in SQL directly. You can use a view or a pre-defined query in SQL that applies the filter and the query hint before Power Query loads the data.

Summary:

• Disable Query Folding after applying the filter step, then manually add the query hint at the end.
• Alternatively, use a pre-filtered SQL query with the hint in SQL to bypass query folding issues.

This should allow you to properly use the query hint in your SQL query. Let me know if you need further clarification!

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[lbendlin]

Include the RangeStart and RangeEnd filter as part of the native query.