Docs

Estimate your savings

Baselit's Agents monitor and optimize Snowflake warehouses in real-time. Our customers save anywhere between 10% to 50% of their Snowflake spend just by switching on the agents. If you'd like to try it out, book a demo here and get access to a 14-day free trial.

These agents optimize Snowflake warehouses in two ways.

  1. Cache optimization i.e. when to suspend a warehouse vs letting it run idle.

  2. Auto-scaling of multi-cluster warehouses with optimal spin up and spin down of clusters.

Run this SQL query to estimate the savings potential for your Snowflake account.

Note: You will need ACCOUNTADMIN role to run the query.

-- startDate and endDate represents period for which potential savings are being calculated
SET startDate = (SELECT current_date - 7);
SET endDate = (SELECT current_date);

WITH warehouse_credits_map AS (
    SELECT * FROM (
        VALUES
        ('X-Small', 'STANDARD', 1),
        ('Small', 'STANDARD', 2),
        ('Medium', 'STANDARD', 4),
        ('Large', 'STANDARD', 8),
        ('X-Large', 'STANDARD', 16),
        ('2X-Large', 'STANDARD', 32),
        ('3X-Large', 'STANDARD', 64),
        ('4X-Large', 'STANDARD', 128),
        ('5X-Large', 'STANDARD', 256),
        ('6X-Large', 'STANDARD', 512),
        ('Medium', 'SNOWPARK-OPTIMIZED', 6),
        ('Large', 'SNOWPARK-OPTIMIZED', 12),
        ('X-Large', 'SNOWPARK-OPTIMIZED', 24),
        ('2X-Large', 'SNOWPARK-OPTIMIZED', 48),
        ('3X-Large', 'SNOWPARK-OPTIMIZED', 96),
        ('4X-Large', 'SNOWPARK-OPTIMIZED', 192),
        ('5X-Large', 'SNOWPARK-OPTIMIZED', 384),
        ('6X-Large', 'SNOWPARK-OPTIMIZED', 768)
    ) AS t (warehouse_size, warehouse_type, credits_per_hour)
),
stop_threshold AS (
    SELECT MAX(timestamp) AS timestamp
    FROM snowflake.account_usage.warehouse_events_history
),
-- warehouse_cluster_status stores warehouse events timeline 
warehouse_cluster_status_base AS (
    SELECT
        warehouse_id,
        warehouse_name,
        cluster_number,
        timestamp AS valid_from,
        LEAD(timestamp) OVER (PARTITION BY warehouse_id, cluster_number ORDER BY timestamp ASC) AS valid_to,
        event_name = 'RESUME_CLUSTER' AS is_active,
        event_reason,
        user_name,
        role_name,
        query_id
    FROM snowflake.account_usage.warehouse_events_history
    WHERE
        event_name IN ('RESUME_CLUSTER', 'SUSPEND_CLUSTER')
        AND event_state = 'COMPLETED'
        AND valid_from::date BETWEEN $startDate AND $endDate
),
warehouse_cluster_status AS (
    SELECT
        warehouse_cluster_status_base.warehouse_id,
        warehouse_cluster_status_base.warehouse_name,
        warehouse_cluster_status_base.cluster_number,
        warehouse_cluster_status_base.is_active,
        warehouse_cluster_status_base.valid_from,
        COALESCE(warehouse_cluster_status_base.valid_to, stop_threshold.timestamp) AS valid_to,
        warehouse_cluster_status_base.event_reason,
        warehouse_cluster_status_base.user_name,
        warehouse_cluster_status_base.role_name,
        warehouse_cluster_status_base.query_id
    FROM warehouse_cluster_status_base
    CROSS JOIN stop_threshold
),
warehouse_cluster_status_enriched AS (
    SELECT *, 
    LAG(valid_from, 1) OVER (PARTITION BY warehouse_id, cluster_number ORDER BY valid_from) AS previous_valid_from
    FROM warehouse_cluster_status
    WHERE valid_from::date BETWEEN $startDate AND $endDate
),
query_history_enriched AS (
    SELECT
        query_id, 
        TIMEADD('milliseconds', queued_overload_time + compilation_time + queued_provisioning_time + queued_repair_time + list_external_files_time, start_time) AS act_start_time,
        execution_time,
        TIMEADD('milliseconds', execution_time, act_start_time) AS act_end_time,
        warehouse_id,
        warehouse_name,
        cluster_number,
        warehouse_type,
        warehouse_size
    FROM snowflake.account_usage.query_history
    WHERE
        warehouse_size IS NOT NULL AND warehouse_name IS NOT NULL
        AND 
        act_end_time::date BETWEEN $startDate AND $endDate
),
ranked_queries AS (
    SELECT
        query_id,
        act_start_time,
        act_end_time,
        warehouse_id,
        cluster_number,
        warehouse_size,
        warehouse_type,
        MIN(act_start_time) OVER (PARTITION BY warehouse_id, cluster_number ORDER BY act_end_time ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS latest_start_time_after_end
    FROM query_history_enriched
),
savings_info AS (
    SELECT
        ranked_queries.query_id,
        act_start_time,
        act_end_time,
        ranked_queries.warehouse_id,
        ranked_queries.cluster_number,
        latest_start_time_after_end,
        warehouse_size,
        warehouse_type,
        is_active,
        valid_from,
        previous_valid_from,
        valid_to,
        event_reason,
        user_name,
        role_name
    FROM ranked_queries
    JOIN warehouse_cluster_status_enriched ON warehouse_cluster_status_enriched.warehouse_id = ranked_queries.warehouse_id
        AND warehouse_cluster_status_enriched.cluster_number = ranked_queries.cluster_number
        AND ranked_queries.act_end_time::date = warehouse_cluster_status_enriched.valid_from::date
        AND (warehouse_cluster_status_enriched.valid_from BETWEEN ranked_queries.act_end_time AND ranked_queries.latest_start_time_after_end)
    WHERE
        latest_start_time_after_end > act_end_time
        AND is_active = FALSE
        AND valid_from::date BETWEEN $startDate AND $endDate
),
enriched_savings_info AS (
    SELECT
        savings_info.*,
        warehouse_credits_map.credits_per_hour / 3600 AS credits_per_second,
    FROM savings_info
    INNER JOIN warehouse_credits_map ON savings_info.warehouse_size = warehouse_credits_map.warehouse_size
        AND savings_info.warehouse_type = warehouse_credits_map.warehouse_type
),
savings_data AS (
    SELECT
        valid_from::date AS DATE,
        sum(credits_per_second * greatest((TIMEDIFF('seconds', greatest(previous_valid_from, act_end_time), valid_from) - greatest((60 - TIMEDIFF('seconds', previous_valid_from, greatest(previous_valid_from, act_end_time)) ), 0) ), 0)) AS potential_credit_savings
	FROM enriched_savings_info
    GROUP BY 1
)
SELECT SUM(potential_credit_savings) AS potential_credit_savings_weekly,
potential_credit_savings_weekly* 365/(DATEDIFF('day', $startDate, $endDate)+1) AS potential_credit_savings_annually, potential_credit_savings_annually * 3.30 AS dollar_savings_annually FROM

-- startDate and endDate represents period for which potential savings are being calculated
SET startDate = (SELECT current_date - 7);
SET endDate = (SELECT current_date);

WITH warehouse_credits_map AS (
    SELECT * FROM (
        VALUES
        ('X-Small', 'STANDARD', 1),
        ('Small', 'STANDARD', 2),
        ('Medium', 'STANDARD', 4),
        ('Large', 'STANDARD', 8),
        ('X-Large', 'STANDARD', 16),
        ('2X-Large', 'STANDARD', 32),
        ('3X-Large', 'STANDARD', 64),
        ('4X-Large', 'STANDARD', 128),
        ('5X-Large', 'STANDARD', 256),
        ('6X-Large', 'STANDARD', 512),
        ('Medium', 'SNOWPARK-OPTIMIZED', 6),
        ('Large', 'SNOWPARK-OPTIMIZED', 12),
        ('X-Large', 'SNOWPARK-OPTIMIZED', 24),
        ('2X-Large', 'SNOWPARK-OPTIMIZED', 48),
        ('3X-Large', 'SNOWPARK-OPTIMIZED', 96),
        ('4X-Large', 'SNOWPARK-OPTIMIZED', 192),
        ('5X-Large', 'SNOWPARK-OPTIMIZED', 384),
        ('6X-Large', 'SNOWPARK-OPTIMIZED', 768)
    ) AS t (warehouse_size, warehouse_type, credits_per_hour)
),
stop_threshold AS (
    SELECT MAX(timestamp) AS timestamp
    FROM snowflake.account_usage.warehouse_events_history
),
-- warehouse_cluster_status stores warehouse events timeline 
warehouse_cluster_status_base AS (
    SELECT
        warehouse_id,
        warehouse_name,
        cluster_number,
        timestamp AS valid_from,
        LEAD(timestamp) OVER (PARTITION BY warehouse_id, cluster_number ORDER BY timestamp ASC) AS valid_to,
        event_name = 'RESUME_CLUSTER' AS is_active,
        event_reason,
        user_name,
        role_name,
        query_id
    FROM snowflake.account_usage.warehouse_events_history
    WHERE
        event_name IN ('RESUME_CLUSTER', 'SUSPEND_CLUSTER')
        AND event_state = 'COMPLETED'
        AND valid_from::date BETWEEN $startDate AND $endDate
),
warehouse_cluster_status AS (
    SELECT
        warehouse_cluster_status_base.warehouse_id,
        warehouse_cluster_status_base.warehouse_name,
        warehouse_cluster_status_base.cluster_number,
        warehouse_cluster_status_base.is_active,
        warehouse_cluster_status_base.valid_from,
        COALESCE(warehouse_cluster_status_base.valid_to, stop_threshold.timestamp) AS valid_to,
        warehouse_cluster_status_base.event_reason,
        warehouse_cluster_status_base.user_name,
        warehouse_cluster_status_base.role_name,
        warehouse_cluster_status_base.query_id
    FROM warehouse_cluster_status_base
    CROSS JOIN stop_threshold
),
warehouse_cluster_status_enriched AS (
    SELECT *, 
    LAG(valid_from, 1) OVER (PARTITION BY warehouse_id, cluster_number ORDER BY valid_from) AS previous_valid_from
    FROM warehouse_cluster_status
    WHERE valid_from::date BETWEEN $startDate AND $endDate
),
query_history_enriched AS (
    SELECT
        query_id, 
        TIMEADD('milliseconds', queued_overload_time + compilation_time + queued_provisioning_time + queued_repair_time + list_external_files_time, start_time) AS act_start_time,
        execution_time,
        TIMEADD('milliseconds', execution_time, act_start_time) AS act_end_time,
        warehouse_id,
        warehouse_name,
        cluster_number,
        warehouse_type,
        warehouse_size
    FROM snowflake.account_usage.query_history
    WHERE
        warehouse_size IS NOT NULL AND warehouse_name IS NOT NULL
        AND 
        act_end_time::date BETWEEN $startDate AND $endDate
),
ranked_queries AS (
    SELECT
        query_id,
        act_start_time,
        act_end_time,
        warehouse_id,
        cluster_number,
        warehouse_size,
        warehouse_type,
        MIN(act_start_time) OVER (PARTITION BY warehouse_id, cluster_number ORDER BY act_end_time ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS latest_start_time_after_end
    FROM query_history_enriched
),
savings_info AS (
    SELECT
        ranked_queries.query_id,
        act_start_time,
        act_end_time,
        ranked_queries.warehouse_id,
        ranked_queries.cluster_number,
        latest_start_time_after_end,
        warehouse_size,
        warehouse_type,
        is_active,
        valid_from,
        previous_valid_from,
        valid_to,
        event_reason,
        user_name,
        role_name
    FROM ranked_queries
    JOIN warehouse_cluster_status_enriched ON warehouse_cluster_status_enriched.warehouse_id = ranked_queries.warehouse_id
        AND warehouse_cluster_status_enriched.cluster_number = ranked_queries.cluster_number
        AND ranked_queries.act_end_time::date = warehouse_cluster_status_enriched.valid_from::date
        AND (warehouse_cluster_status_enriched.valid_from BETWEEN ranked_queries.act_end_time AND ranked_queries.latest_start_time_after_end)
    WHERE
        latest_start_time_after_end > act_end_time
        AND is_active = FALSE
        AND valid_from::date BETWEEN $startDate AND $endDate
),
enriched_savings_info AS (
    SELECT
        savings_info.*,
        warehouse_credits_map.credits_per_hour / 3600 AS credits_per_second,
    FROM savings_info
    INNER JOIN warehouse_credits_map ON savings_info.warehouse_size = warehouse_credits_map.warehouse_size
        AND savings_info.warehouse_type = warehouse_credits_map.warehouse_type
),
savings_data AS (
    SELECT
        valid_from::date AS DATE,
        sum(credits_per_second * greatest((TIMEDIFF('seconds', greatest(previous_valid_from, act_end_time), valid_from) - greatest((60 - TIMEDIFF('seconds', previous_valid_from, greatest(previous_valid_from, act_end_time)) ), 0) ), 0)) AS potential_credit_savings
	FROM enriched_savings_info
    GROUP BY 1
)
SELECT SUM(potential_credit_savings) AS potential_credit_savings_weekly,
potential_credit_savings_weekly* 365/(DATEDIFF('day', $startDate, $endDate)+1) AS potential_credit_savings_annually, potential_credit_savings_annually * 3.30 AS dollar_savings_annually FROM

-- startDate and endDate represents period for which potential savings are being calculated
SET startDate = (SELECT current_date - 7);
SET endDate = (SELECT current_date);

WITH warehouse_credits_map AS (
    SELECT * FROM (
        VALUES
        ('X-Small', 'STANDARD', 1),
        ('Small', 'STANDARD', 2),
        ('Medium', 'STANDARD', 4),
        ('Large', 'STANDARD', 8),
        ('X-Large', 'STANDARD', 16),
        ('2X-Large', 'STANDARD', 32),
        ('3X-Large', 'STANDARD', 64),
        ('4X-Large', 'STANDARD', 128),
        ('5X-Large', 'STANDARD', 256),
        ('6X-Large', 'STANDARD', 512),
        ('Medium', 'SNOWPARK-OPTIMIZED', 6),
        ('Large', 'SNOWPARK-OPTIMIZED', 12),
        ('X-Large', 'SNOWPARK-OPTIMIZED', 24),
        ('2X-Large', 'SNOWPARK-OPTIMIZED', 48),
        ('3X-Large', 'SNOWPARK-OPTIMIZED', 96),
        ('4X-Large', 'SNOWPARK-OPTIMIZED', 192),
        ('5X-Large', 'SNOWPARK-OPTIMIZED', 384),
        ('6X-Large', 'SNOWPARK-OPTIMIZED', 768)
    ) AS t (warehouse_size, warehouse_type, credits_per_hour)
),
stop_threshold AS (
    SELECT MAX(timestamp) AS timestamp
    FROM snowflake.account_usage.warehouse_events_history
),
-- warehouse_cluster_status stores warehouse events timeline 
warehouse_cluster_status_base AS (
    SELECT
        warehouse_id,
        warehouse_name,
        cluster_number,
        timestamp AS valid_from,
        LEAD(timestamp) OVER (PARTITION BY warehouse_id, cluster_number ORDER BY timestamp ASC) AS valid_to,
        event_name = 'RESUME_CLUSTER' AS is_active,
        event_reason,
        user_name,
        role_name,
        query_id
    FROM snowflake.account_usage.warehouse_events_history
    WHERE
        event_name IN ('RESUME_CLUSTER', 'SUSPEND_CLUSTER')
        AND event_state = 'COMPLETED'
        AND valid_from::date BETWEEN $startDate AND $endDate
),
warehouse_cluster_status AS (
    SELECT
        warehouse_cluster_status_base.warehouse_id,
        warehouse_cluster_status_base.warehouse_name,
        warehouse_cluster_status_base.cluster_number,
        warehouse_cluster_status_base.is_active,
        warehouse_cluster_status_base.valid_from,
        COALESCE(warehouse_cluster_status_base.valid_to, stop_threshold.timestamp) AS valid_to,
        warehouse_cluster_status_base.event_reason,
        warehouse_cluster_status_base.user_name,
        warehouse_cluster_status_base.role_name,
        warehouse_cluster_status_base.query_id
    FROM warehouse_cluster_status_base
    CROSS JOIN stop_threshold
),
warehouse_cluster_status_enriched AS (
    SELECT *, 
    LAG(valid_from, 1) OVER (PARTITION BY warehouse_id, cluster_number ORDER BY valid_from) AS previous_valid_from
    FROM warehouse_cluster_status
    WHERE valid_from::date BETWEEN $startDate AND $endDate
),
query_history_enriched AS (
    SELECT
        query_id, 
        TIMEADD('milliseconds', queued_overload_time + compilation_time + queued_provisioning_time + queued_repair_time + list_external_files_time, start_time) AS act_start_time,
        execution_time,
        TIMEADD('milliseconds', execution_time, act_start_time) AS act_end_time,
        warehouse_id,
        warehouse_name,
        cluster_number,
        warehouse_type,
        warehouse_size
    FROM snowflake.account_usage.query_history
    WHERE
        warehouse_size IS NOT NULL AND warehouse_name IS NOT NULL
        AND 
        act_end_time::date BETWEEN $startDate AND $endDate
),
ranked_queries AS (
    SELECT
        query_id,
        act_start_time,
        act_end_time,
        warehouse_id,
        cluster_number,
        warehouse_size,
        warehouse_type,
        MIN(act_start_time) OVER (PARTITION BY warehouse_id, cluster_number ORDER BY act_end_time ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS latest_start_time_after_end
    FROM query_history_enriched
),
savings_info AS (
    SELECT
        ranked_queries.query_id,
        act_start_time,
        act_end_time,
        ranked_queries.warehouse_id,
        ranked_queries.cluster_number,
        latest_start_time_after_end,
        warehouse_size,
        warehouse_type,
        is_active,
        valid_from,
        previous_valid_from,
        valid_to,
        event_reason,
        user_name,
        role_name
    FROM ranked_queries
    JOIN warehouse_cluster_status_enriched ON warehouse_cluster_status_enriched.warehouse_id = ranked_queries.warehouse_id
        AND warehouse_cluster_status_enriched.cluster_number = ranked_queries.cluster_number
        AND ranked_queries.act_end_time::date = warehouse_cluster_status_enriched.valid_from::date
        AND (warehouse_cluster_status_enriched.valid_from BETWEEN ranked_queries.act_end_time AND ranked_queries.latest_start_time_after_end)
    WHERE
        latest_start_time_after_end > act_end_time
        AND is_active = FALSE
        AND valid_from::date BETWEEN $startDate AND $endDate
),
enriched_savings_info AS (
    SELECT
        savings_info.*,
        warehouse_credits_map.credits_per_hour / 3600 AS credits_per_second,
    FROM savings_info
    INNER JOIN warehouse_credits_map ON savings_info.warehouse_size = warehouse_credits_map.warehouse_size
        AND savings_info.warehouse_type = warehouse_credits_map.warehouse_type
),
savings_data AS (
    SELECT
        valid_from::date AS DATE,
        sum(credits_per_second * greatest((TIMEDIFF('seconds', greatest(previous_valid_from, act_end_time), valid_from) - greatest((60 - TIMEDIFF('seconds', previous_valid_from, greatest(previous_valid_from, act_end_time)) ), 0) ), 0)) AS potential_credit_savings
	FROM enriched_savings_info
    GROUP BY 1
)
SELECT SUM(potential_credit_savings) AS potential_credit_savings_weekly,
potential_credit_savings_weekly* 365/(DATEDIFF('day', $startDate, $endDate)+1) AS potential_credit_savings_annually, potential_credit_savings_annually * 3.30 AS dollar_savings_annually FROM

Start saving $ on Snowflake today.

Get started with Baselit for free and automate Snowflake optimization.

Start saving $ on Snowflake today.

Get started with Baselit for free and automate Snowflake optimization.

Start saving $ on Snowflake today.

Get started with Baselit for free and automate Snowflake optimization.

Stay updated with strategies to optimize Snowflake.

Backed by

© Merse, Inc. dba Baselit. All rights reserved.

Stay updated with strategies to optimize Snowflake.

Backed by

© Merse, Inc. dba Baselit. All rights reserved.