[Wranglers] QUERY_INSIGHTS view now available in Snowflake.

[Ali, Saqib]

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/20250704/ca6ee0c6/attachment.htm>