Sno wfllake

SOL FUinctions
Snowflake Query
Optimisation: Cost&
Speed Improvemnents
By Naveen Tadipatri

Learn how to make your Snowflake queries run faster and cost less, with 8
proven techniques used by data engineering teams worldwide.

By Naveen Tadipatr
1920 +8ON (19201X68
 TECHNIQUE 1

Optimise Warehouse Size

Right-size your virtual warehouse for the workload to strike the perfect balance between performance and cost. An oversized
warehouse consumes more credits than necessary, while an undersized one can lead to query queues and slow execution
times. Snowflake allocates compute clusters based on a range of sizes (XS to 6XL), each determining the level of parallelism for
query execution.

2 3

Why It Matters How It Works Actionable Tip

Optimal performance-to-cost ratio. Snowflake scales compute based on Start small, monitor,and scale as
size. needed to avoid slowdowns.

ALTER WAREHOUSE my_wh SET WAREHOUSE_SIZE='LARGE';

 TECHNIQUE 2

Implement Auto-Suspend & Auto-Resume

Automatically pause and resume compute resources to prevent paying for idle time, particularly beneficial for workloads that
are not active 24/7. Snowflake suspends warehouses after a specified period of inactivity and instantly resumes them when a
new query arrives.

Cost Savings
Eliminates charges for idle compute, especially in non
continuous workloads.
Snowilake SQL Funcitlons
Efficiency
Instant query resumption ensures no user experience Cost savings
-15.10o

degradation.

By Naveen Tadiporr

Tip:
For development and test environments, set AUTO_SUSPEND to 120 seconds or less to maximise savings.

ALTER WAREHOUSE my_wh SET AUTO_SUSPEND = 60;

ALTER WAREHOUSE my_wh SET AUTO RESUME = TRUE;
 TECHNIQUE 3

Filter Data Early

Applying filters as early as possible in your queries significantly reduces the volume of data processed, especially before
complex operations like joins. This method leverages Snowflake's micro-partition pruning capabilities, ensuring that only the
most relevant data is scanned.

Reduced Processing Micro-Partition Pruning

Minimises the number of rows involved in joins, leading to Snowflake's architecture allows it to skip entire micro
lower compute costs and faster query execution. partitions that do not contain relevant data, optimising
data scans.

Tip:
Always push filters like date, region, or statusto the earliest possible stage inyour query,ideally before any join
operations.

SELECT *
FROM big_table bt
JOIN small table st ON [Link] =[Link]
WHERE [Link] >= '2025-01-01':
 TECHNIQUE 4

Leverage Clustering Keys

For large tables, implementing clustering keys can dramatically enhance query performance by physically organising data to
facilitate faster filtering. Snowflake stores data in micro-partitions, and clustering keys enable it to efficiently skip irrelevant
ones, thus avoiding costly full table scans.

Improved Pruning Faster Queries When to Use

Clustering organises data to Directly reduces the data Best suited for tables exceeding 1
improve how Snowflake prunes Scanned for common filter TB that frequently undergo filtered
micro-partitions, significantly patterns, leading to quicker queries on the same columns.
speeding up filtered queries. results and lower compute
usage.

ALTER TABLE sales CLUSTER BY (region, sale_date);

 TECHNIQUE 5

Avoid SELECT *
One of the simplest yet most impactfuloptimisation techniques is to specify only the columns you need, rather than using
SELECT *. Snowflake charges based on the volume of datascanned, so selecting fewer columns directly translates to fewer bytes
read, reducing I/0 and compute costs.

Reduced Data Scans

Minimising columns drastically cuts down the data

snoowflake volume Snowflake needs to process.
SQL Functions
Cost Efficiency
Lower data scans directly reduce compute costs,
especially for high-frequency queries.

Performance Boost

Faster queryexecution due to less data transfer and

By Naveen Tadipatri
processing.

A Critical Habit:
Cultivating this smallhabit across allyour queries can lead to significant cost savings and performance improvements
over time.

SELECT id, name, sales FROM customers;

 TECHNIQUE 6

Leverage Result Cache

Snowflake's result cache is a powerful feature that allows you to reuse the results of previous queries without recomputing
them. This means cached queries return almost instantly and incur zero credit costs, making it ideal for repetitive reporting and
dashboard queries.

24-Hour Window
Zero Cost
Results are stored for 24 hours,
Instant Returns
No compute credits are consumed provided the underlying data hasn't
Queries that hit the cache complete for cached queries, offering changed, ensuring fresh data when
almost instantaneously, significantly substantial cost savings. needed.
boosting response times.

Simply re-running the same SELECT statement within the 24-hour window will utilise the cache automatically.
 TECHNIQUE 7

Optimise Joins
Choosing the right join type andpre-filtering data before joining are crucialsteps in optimising query performance and cost.
Unnecessary LEFT JOIN operations can increase processing overhead, while filtering datasets beforehand ensures Snowflake
processes smaller, more manageable data volumes.

INNER JOIN LEFT JOIN RIGHT JOIN FULL OUTER JOIN

Using INNER JOIN when you only need matching rows reduces the dataset Snowflake needs to process, leading to faster
execution and lower costs. Conversely, LEFT JOIN can be more expensive if not all rows from the left table are needed.

SELECT...
FROM a

INNER JOIN b ON [Link] = [Link];

 TECHNIQUE 8

Materialized Views for Aggregations

For frequently queried heavy aggregations, using materialized views can significantly reduce query times and costs. Materialised
views store pre-computed results, eliminating the need to re-process large datasets repeatedly. Snowflake automatically
refreshes these views when the underlying data changes, ensuring data freshness with minimal effort.

Cost Savings
Reduces compute resource usage for
analytical workloads.

Pre-computed Results
Stores aggregated data, avoiding re
computation for common queries. Automatic Refresh
Snowflake handles updates, ensuring
the view always reflects the latest
data.

O IdealUse Case:
Perfect for dashboards and reporting tools that frequently query the same aggregated metrics, ensuring rapid data
delivery.

CREATE MATERIALIZED VIEW mv_sales AS

SELECT region, SUM(sales) AS total_sales
FROM sales GROUP BY region;
 Key Takeaways
Every Second Saved = N Boost Performance Lower Your Bill
Cost Saved
Implement these techniques to Strategic optimisation ensures
Optimising query execution achieve faster data retrieval and you get the most out of your
directly translates to reduced enhanced analytical capabilities. Snowflake investment without
credit consumption and overspending.
improved budget efficiency.
Follow Naveen Tadipatrifor more insights on Snowflake, SQL, and Data Engineering.