Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Errors enabling Native Query / Query Folding for Snowflake queries

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 ...
Need Help
  • ppm1's avatar
    ppm1
    3 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Anonymous ,

 

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.