Fri. Dec 4th, 2020
snowflake-best-practices-performance-optimization

In this blog, we will discover few tips that snowflake uses to optimize queries.

Changing the query SQL Text ( capital letter to lower case ) does trigger an execution on the raw data and not from the result cache.

Example 1 :

-- create benchmarking query
select sum(distinct A.YEAR*b.YEAR)
from sample_ext a
inner join sample_ext b
on A.YEAR = b.YEAR and A.UNITS=b.UNITS;

This is the results :

Now, let’s try to convert a to A , and b to B and vice versa, technically the query will be doing exactly the same, however the SQL text will be detected as changed from snowflake and the query will not from result cache. it will go again and run on data in the source , which means data transfer from S3, thus the same query takes 6.67 secs instead of 93 mls.

Now, if we run the query again, it will return data from cache result, and obviously run much quicker :

Conclusion 1: changing capital letters to the lower case may trigger a re-run of the query, however going to the line “/n” doesn’t change the SQL Text, and it’s not considered by snowflake as a change.

By Amine Hallam

Author & blogger on the latest data analytics technologies: Hadoop, Snowflake, Cloud ( AWS ), ETL .. etc

Leave a Reply

Your email address will not be published. Required fields are marked *