Implementing Snowflake’s Cost Optimization techniques

Thank you for reading my earlier blogs on the Snowflake cost series. If you haven't read the earlier blogs in the series, you can refer to them here —

1. Cost optimization series — https://medium.com/@poojakelgaonkar/snowflake-cost-optimization-series-7aac34697608
2. Understanding Snowflake Cost — https://medium.com/snowflake/understanding-snowflake-cost-10165aea767f
3. Understanding Warehouse Cost and Optimization — https://medium.com/snowflake/understanding-warehouse-cost-and-optimization-8fdffe2f68e6
4. Understanding Optimization Services — https://medium.com/snowflake/understanding-snowflake-optimization-services-b8c8dbad1f52

You can also subscribe and read my earlier blogs on Snowflake and Data topics if not done so already.

This is the fourth blog in the series that focuses more on implementing the optimization techniques which will help to optimize the performance and cost as well as help in implementing the controls to monitor and report the utilization.

You can start with small steps and standards while creating or defining the Snowflake components. You can consider various scenarios and examples from below to implement optimization techniques.

1. Consider you have created a warehouse for your development team to perform some of the ad hoc work and run proof of concepts(POCs). The team may use it on an ad-hoc basis and you decide to create a warehouse with XSMALL size and set it suspended as it's created.

/* create warehouse */
CREATE WAREHOUSE DEV_POC_WH WAREHOUSE_SIZE='X-SMALL' 
INITIALLY_SUSPENDED=TRUE;

Now, you have a warehouse setup in a suspended state and you want to change the state to active for your team. You can use ALTER command to set it active

/*resume the warehouse*/

ALTER WAREHOUSE DEV_POC_WH RESUME ;

You know that DEV is going to use it for ad-hoc and they might not have workloads running for long on the warehouse and you may not need to maintain the warehouse state or cache for performance benefits. You can save the warehouse cost by using Auto Suspend and Auto Resume. You can wait and check if the warehouse is idle for 5 minutes, you can suspend it.

/* Alter warehouse to set the auto suspend and auto resume */

ALTER WAREHOUSE DEV_POC_WH SET 
  AUTO_SUSPEND = 300
  AUTO_RESUME = TRUE
;

This is one of the recommended properties and features to save the warehouse cost when it's idle.

2. Consider a scenario where your development team has been developing and running queries to develop a new data pipeline and workloads. As it is in the development and testing phase, this might need to undergo various testing rounds and need time to review the query profile and implement SQL optimization to improve the query performances. In such cases or any scenario where you want to ensure to stop long-running or long waited or long queued queries, it keeps the warehouse up and running until it gets executed. You can use the session parameters or warehouse parameters or properties to avoid any unexpected usage. In this case, being a development warehouse — would prefer putting it as a warehouse parameter.

/* Alter warehouse to set the query timeout to 5 minutes */

ALTER WAREHOUSE DEV_POC_WH 
SET STATEMENT_TIMEOUT_IN_SECONDS = 300
SET STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = 300;

You can also use it as a session parameter and set it along with the context or initial parameters of a session

/* set context and session parameters */
Use database DEV_POC_DB;
Use schema POC;
SET STATEMENT_TIMEOUT_IN_SECONDS = 300;
SET STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = 300;

3. Imagine you have multiple warehouses running in PROD environment and you need to monitor the usage. You know the daily credit usage of the PROD warehouses and want to monitor if the usage is within the defined range or if it's going above the defined threshold. You can use resource monitors to define the threshold and action to be taken if it reaches the threshold. In this scenario, this is for production workloads then you may want to get a notification in case it goes beyond the defined threshold.

/* create a resource monitor for PROD warehouse */

create resource monitor PROD_WH_MONITOR with credit_quota=500
frequency = daily
start_timestamp = IMMEDIATELY 
notify_users = (POOJAKELGAONKAR, "POOJAKELGAONKAR")
triggers on 90 percent do notify;

/* assign resource monitor to the warehouse  */

ALTER WAREHOUSE PROD_WH SET RESOURCE_MONITOR = PROD_WH_MONITOR;

4. Consider you have set the ad-hoc workloads on an ad-hoc warehouse and you might expect some additional usage because of the nature of ad-hoc workloads or queries being run on the warehouse. You have set the resource monitor however you also want to get the list of queries that are running longer on a given warehouse on a weekly basis to monitor the warehouse performance, query performance and consider resizing depending on the workloads. In this scenario, you can create a task that will run on a weekly basis to check the long-run queries on the given warehouse.

/*create a task to check the long-running queries*/

CREATE OR REPLACE TASK long_queries_logging
  USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL'
  SCHEDULE = 'USING CRON  0 18 * * 1 America/Los_Angeles'
AS
BEGIN
  SELECT query_id from snowflake.account_usage.query_history where total_elapsed_time >= 3600000 and DATEDIFF(DAY,start_time,current_timestamp) = 7;
END;

/* check the status of the task */

show tasks; --task is always in suspended state when created 

/* change the state of the task */

ALTER TASK long_queries_logging RESUME;

/* Execute task */

EXECUTE TASK long_queries_logging;

You can also consider creating a stored procedure to list down the long-running queries and invoke the stored procedure in the task. You can also send the data to external monitoring services like cloudwatch if needed.

You can view the task_history and executions using the Snowsight task_history tab under activity. This has up to 45 minutes of latency to reflect the executed task. You can come back after some time to this window to check the task status and details.

5. The another important component is cloud services cost. Consider you are running ETL or ELT jobs on Snowflake and your development team is using lot of USE commands to set the context before running the ETL or ELT SQLs on Snowflake. How these commands are treated? How these are considered in usage? — You need to know that, these all statements are considered as metadata operations and part of cloud services usage. The more statements you run, the more usage of the cloud services layer. You know that, cloud services is free when it’s up to 10% of overall compute. you will have cloud services cost if it crosses 10% of compute usage. You need to consider this in design in the early phase or you can also consider it by using cloud services review, and usage monitoring options. You can use the task to run set of queries to identify the higher usage of cloud services. You can add necessary filters, cloud services usage filter to get to the query_id as well as overall usage of cloud services. Below is sample query on query_history view —

/* cloud services usage per query */
select 
query_id,
credits_used_cloud_services
from
snowflake.account_usage.query_history
where credits_used_cloud_services > 0

You can also get the cloud services usage at warehouse level using warehouse metering history view. you can add specific filters to get to a warehouse or usage for given duration —

/*cloud services usage per warehouse*/ 
select 
warehouse_name,
sum(credits_used_cloud_services)
from
snowflake.account_usage.warehouse_metering_history
where credits_used_cloud_services > 0
group by 1

Hope this blog helps you to get started with implementing these techniques to optimize the usage. You can also implement various metrics and monitoring, alerting on top of usage views. You will learn more about this in an upcoming blog.

Please follow my blog series on Snowflake topics to understand various aspects of data design, engineering, data governance, cost optimizations, etc.

About Me :

I am one of the Snowflake Data Superheroes 2023. I am also one of the Snowflake SnowPro Core SME- Certification Program. I am a DWBI and Cloud Architect! I am currently working as Senior Data Architect — GCP, Snowflake. I have been working with various Legacy data warehouses, Bigdata Implementations, and Cloud platforms/Migrations. I am SnowPro Core certified Data Architect as well as Google certified Google Professional Cloud Architect. You can reach out to me LinkedIn if you need any further help on certification, Data Solutions, and Implementations!