[Wranglers] QUERY_INSIGHTS view now available in Snowflake.

Ali, Saqib docbook.xml at gmail.com
Sat Jul 5 12:46:53 UTC 2025


I have been monitoring the QUERY_INSIGHTS table for my Snowflake instance.
Here are some of the insights (messages + suggestions) that the
QUERY_INSIGHT view currently offers:

1) Filter Not Applicable— The provided filter condition on the query did
not filter any or no records present for the given condition. This also
leads to full table scan. This is represented with the value of
QUERY_INSIGHT_INAPPLICABLE_FILTER_ON_TABLE_SCAN in the INSIGHT_TYPE_ID
column.
2) Filter is not selective enough— In this case, the filters are present
however they did not filter expected number of records when compared with
full data. This is represented with the value of
QUERY_INSIGHT_UNSELECTIVE_FILTER in the INSIGHT_TYPE_ID column.
3) Filters using clustering columns— The executed query applied filter
conditions on columns that have been clustered. This is represented with
the value of QUERY_INSIGHT_FILTER_WITH_CLUSTERING_KEY  in the
INSIGHT_TYPE_ID column. This is a good thing and as such the IS_OPPORTUNITY
is set to FALSE in the QUERY_INSIGHTS view.
4) Joining conditions not defined— when one or more tables are joined in
the query but the join conditions are not defined. This leads to the cross
join. This is represented with the values of
QUERY_INSIGHT_JOIN_WITH_NO_JOIN_CONDITION in the INSIGHT_TYPE_ID column.
5) Query Spillage— The warehouse used to execute the does not have enough
storage to store the data for the query, which results in spilling to
Remote Storage. In this scenario the query becomes much more expensive. It
is better to execute the said query on a larger Warehouse. This is
represented with the value of QUERY_INSIGHT_REMOTE_SPILLAGE in the
INSIGHT_TYPE_ID column.



On Fri, Jul 4, 2025 at 7:15 PM Ali, Saqib <docbook.xml at gmail.com> wrote:

> Love this feature. I had a bad query where the JOIN condition was missing.
> This showed up in the QUERY_INSIGHTS with the following message:
>
> {
>   "join_id": 1,
>   "message": "A JOIN in this query has no join condition (at node [1]),
> which produces many more rows than the total number of rows that went in."
> }
>
> And the suggestion was:
>
>  ["Adding at least one condition to define the relationship between
> columns in the joined data sets could speed up this query by reducing the
> number of rows that it produces."]
>
> select *
> from snowflake.account_usage.QUERY_INSIGHTS
> where is_opportunity
> ;
>
>
> On Fri, Jul 4, 2025 at 6:39 AM Ali, Saqib <docbook.xml at gmail.com> wrote:
>
>> QUERY_INSIGHTS view is now available in Snowflake:
>>
>> select *
>> from snowflake.account_usage.QUERY_INSIGHTS;
>>
>> This VIEW provides information about the conditions that affect the Query
>> Performance, where applicable. The conditions can be any one of the
>> following:
>>
>> 1) Filter is not applicable i.e. the WHERE clause doesn’t filter out any
>> rows, which means that the query might scan more data than intended.
>> 2) Filter is not selective i.e. the WHERE clause doesn’t significantly
>> reduce the number of rows, which means that the query might scan more data
>> than intended.
>> 3) Filter uses clustering key i.e. the Query benefitted with the
>> Clustering Key.
>> 4) Join with no join condition. The join is missing the join condition.
>> The result is a cross join, which returns every possible combination of
>> rows.
>> 5) Remote spillage. This query scanned more data than the warehouse had
>> capacity to store. As a result, the warehouse spilled data to storage,
>> which slowed down the processing of the query.
>>
>> INSIGHT_TYPE_ID, MESSAGE and SUGGESTIONS are the key fields in the
>> QUERY_INSIGHTS view.
>>
>> The best thing is that this VIEW has the QUERY_PARAMTERIZED_HASH column,
>> so you can track the improvements as you optimize the queries.
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://analyticsengineering.net/pipermail/wranglers/attachments/20250705/854aeda2/attachment.htm>


More information about the Wranglers mailing list