Errors enabling Native Query / Query Folding for Snowflake queries

[StaceyLGriffeth]

I am trying to enable Incremental Refresh for a data model.  The model has numerous queries to our Snowflake instance.  I have followed the steps from numerous websites to modify the query to enable Native Query so that I can enable Query Folding, but have not been successful.

Here is an example of a query.  The first Applied Step is called Source: 

= Snowflake.Databases("xxxx.snowflakecomputing.com","POWERBI_WH")

The second Applied Step is called Navigation:  

= OPS_Schema{[Name="VW_DAILY_TRX_CONSOLIDATED",Kind="View"]}[Data]

From what I’ve read, I believe it is at this point that I would select the Function (fx) icon and create a custom step to enable Native Query.  This is where I think I’m getting lost.  I have tried numerous iterations of the following, all of which are met with errors.  This is my current verbiage:

= Value.NativeQuery( VW_DAILY_TRX_CONSOLIDATED_View, "select * from ANALYTICS.OPS.VW_DAILY_TRX_CONSOLIDATED_View", null, [EnableFolding = true])

And the associated error:

Expression.Error:  Native queries aren't supported by this value.  Details: [Table]

I appreciate any suggestions for how to get this working.

Thanks!

Stacey

[v-stephen-msft]

Hi @StaceyLGriffeth ,

Here's an example of how you can modify your query to use native SQL syntax and enable query folding:

let
    Source = Snowflake.Databases("xxxx.snowflakecomputing.com","POWERBI_WH"),
    Navigation = Source{[Name="OPS_Schema"]}[Data]{[Name="VW_DAILY_TRX_CONSOLIDATED",Kind="View"]}[Data],
    SqlStatement = "select * from ANALYTICS.OPS.VW_DAILY_TRX_CONSOLIDATED_View",
    NativeQuery = Value.NativeQuery(Navigation, SqlStatement, null, [EnableFolding=true])
in
    NativeQuery

In this modified query, we first define the Source and Navigation steps as before. Then, we define a new variable SqlStatement that contains the native SQL query you want to execute. Finally, we use the Value.NativeQuery function to execute the native SQL query on the Navigation step, with the EnableFolding option set to true to enable query folding.

Note that you need to replace xxxx.snowflakecomputing.com with the actual hostname of your Snowflake instance, and ANALYTICS.OPS.VW_DAILY_TRX_CONSOLIDATED_View with the actual name of your view.

Also, make sure that your Snowflake user has the necessary privileges to execute native SQL queries. You can check the Snowflake documentation for more information on how to grant these privileges.

I hope this helps!

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[ppm1]

Why are you using Value.NativeQuery? You should be able to just choose that view, perform any filtering/column selection steps, etc. and it should fold back to your source.

Pat 

[StaceyLGriffeth]

Hi @ppm1 I am trying to set up Incremental Refresh.  From what I've read, in order to do so, this option "View Native Query" has to be active.  In order to do that, I need to insert the Value.NativeQuery step earlier in the Applied Steps.

[ppm1 · Accepted solution]

Just because View Native Query is greyed out, it doesn't mean your query isn't folding. You can confirm by looking at the query hitting Snowflake, or just do a simple experiment. Try refreshing your preview or loading a small amount of data with a narrow filter (e.g., one day, one dimension value) and confirm it loads quickly. With Snowflake, if your query is simple filters, select columns, etc., it should fold (do those steps first if you also need to do more complex transforms that may not fold.

Pat