Snowflake's cloud-based architecture provides flexibility and scalability, but it's essential to monitor and optimize your usage to control costs effectively. Snowflake Cost Insights is a powerful tool that helps you identify potential areas where you can reduce unnecessary spending.

What are Snowflake Cost Insights?

Cost Insights are actionable recommendations generated by Snowflake based on analysis of your usage patterns. They pinpoint specific areas in your Snowflake account where you might be able to save on compute and storage costs.

Key Types of Cost Insights

Snowflake Cost Insights cover a wide range of optimization opportunities, including:

• Large Tables Never Queried: Identifies large tables that haven't been used in a while, potentially indicating redundant data storage.

• Tables with Data Written but Not Read: Highlights tables where data is regularly inserted or updated but rarely queried, suggesting potential storage optimization.

• Rarely Used Materialized Views: Pinpoints materialized views that are not frequently utilized, which could be candidates for removal or optimization.

• Short-Lived Permanent Tables: Detects temporary tables that are converted to permanent but have a short lifespan, potentially indicating inefficient data management practices.

• Rarely Used Search Optimization Paths: Finds search optimization paths that are not frequently used, suggesting opportunities for consolidation or removal.

• Rarely Used Tables with Automatic Clustering: Identifies tables with automatic clustering enabled that are rarely queried, where clustering might not be necessary.

A Script to Setup the Environment: Creating Tables and Views for Cost Insights

First, let's create a dedicated database and populate it with tables and views that simulate scenarios commonly flagged by Snowflake Cost Insights:

SQL

-- Create a dedicated database for cost insights demo
CREATE OR REPLACE DATABASE cost_insights_demo;
USE DATABASE cost_insights_demo;

-- Create a large table that will never be queried
CREATE OR REPLACE TABLE large_unused_table (
    id INT,
    random_data VARCHAR(1000)
);

-- Insert 1 million rows of sample data into the large unused table
INSERT INTO large_unused_table
SELECT 
    seq4() as id,
    randstr(5, random()) as random_data
FROM TABLE(GENERATOR(ROWCOUNT => 1000000));

-- Create a table that will have data written but not read
CREATE OR REPLACE TABLE write_only_table (
    timestamp TIMESTAMP_NTZ,
    data VARIANT
);

-- Create a task to continuously insert data into the write-only table
CREATE OR REPLACE TASK insert_into_write_only_table
    WAREHOUSE = 'DEMO_WH'
    SCHEDULE = '60 MINUTE'  -- Runs every 60 minutes
AS
INSERT INTO write_only_table (timestamp, data)
SELECT CURRENT_TIMESTAMP(), OBJECT_CONSTRUCT('key', UNIFORM(1, 100, RANDOM()))
FROM TABLE(GENERATOR(ROWCOUNT => 1000));

-- Resume the task to start data insertion
ALTER TASK insert_into_write_only_table RESUME;

-- Uncomment the following line to suspend the task if needed
--ALTER TASK insert_into_write_only_table SUSPEND;

-- Create a base table for a rarely used materialized view
CREATE OR REPLACE TABLE sales_summary_base (
    date DATE,
    product_category VARCHAR,
    total_sales NUMBER
);

-- Insert sample sales data into the base table
INSERT INTO sales_summary_base (date, product_category, total_sales)
SELECT 
    DATEADD(day, SEQ4(), '2023-01-01'),       -- Generate dates over a year
    CASE
        WHEN UNIFORM(1, 10, RANDOM()) <= 3 THEN 'Electronics'
        WHEN UNIFORM(1, 10, RANDOM()) <= 6 THEN 'Clothing'
        ELSE 'Home Goods'
    END AS product_category,                    -- Randomly assign categories
    ROUND(UNIFORM(5000, 30000, RANDOM()), 2) AS total_sales  -- Random sales values between $5,000 and $30,000
FROM TABLE(GENERATOR(ROWCOUNT => 365));         -- Generate 365 rows (one year of data)

-- Create a rarely used materialized view
CREATE OR REPLACE MATERIALIZED VIEW rarely_used_mv 
AS
SELECT date, product_category, SUM(total_sales) AS total_sales
FROM sales_summary_base
GROUP BY date, product_category;

-- Create a short-lived temporary table
CREATE or Replace TEMPORARY TABLE short_lived_temp_table (
    id INT,
    data VARCHAR
);

-- Insert sample data into the temporary table 
INSERT INTO short_lived_temp_table (id, data)
SELECT seq4() as id,
'Data ' || seq4() AS data
FROM TABLE(GENERATOR(ROWCOUNT => 100000));

-- Drop the temporary table to simulate short-lived behavior
DROP TABLE short_lived_temp_table;

-- Create a table with rarely used search optimization paths
CREATE OR REPLACE TABLE rarely_searched_table (
    id INT,
    name VARCHAR,
    description VARCHAR
);

-- Add search optimization to the table
ALTER TABLE rarely_searched_table ADD SEARCH OPTIMIZATION;

-- Insert sample data into the rarely searched table
INSERT INTO rarely_searched_table (id, name, description)
SELECT
    seq4() as id,       -- Generate unique IDs
    'Name ' || seq4() AS name,    -- Generate sample names
    'Description ' || seq4() AS description -- Generate sample descriptions
FROM TABLE(GENERATOR(ROWCOUNT => 100000)); -- Adjust row count as needed

-- Create a table with automatic clustering that will be rarely used
CREATE OR REPLACE TABLE rarely_clustered_table (
    id INT,
    name VARCHAR,
    category VARCHAR
)
CLUSTER BY (category);

-- Insert sample data into the rarely clustered table
INSERT INTO rarely_clustered_table (id, name, category)
SELECT
    seq4() as id,
    'Name ' || seq4() AS name,
    'Category ' || uniform(1, 5, random()) AS category -- Assign random categories (1-5)
FROM TABLE(GENERATOR(ROWCOUNT => 100000))

Replace DEMO_WH with your actual warehouse.

Triggering Cost Insights

To trigger Cost Insights for these scenarios, you'll need to allow some time (typically a week or more) for Snowflake to gather usage statistics. During this time, avoid querying the tables and views we just created.

Accessing Snowflake Cost Insights

You can access Cost Insights through Snowsight, Snowflake's web-based user interface:

1. Log in to Snowsight: Ensure you have the ACCOUNTADMIN role or a role with the necessary permissions to view cost and usage data.

2. Navigate to Cost Management: Go to Admin > Cost Management.

3. View Cost Insights: The Cost Insights tile on the Account Overview tab presents a summary of potential savings opportunities.

Taking Action on Cost Insights

Each Cost Insight provides detailed information about the issue, estimated potential savings, and recommended actions to resolve it. Depending on the insight, you can:

• Drop or Archive Tables: For unused or rarely used tables, you might choose to drop them entirely or archive the data to a cheaper storage tier.

• Reconsider Materialized Views: If a materialized view is not providing significant query performance benefits, consider removing it or adjusting its refresh schedule.

• Optimize Temporary Table Usage: For short-lived permanent tables, evaluate if they could be replaced with temporary tables to reduce storage costs.

• Consolidate Search Optimization Paths: If multiple search optimization paths are rarely used, you might be able to consolidate them into a single path.

• Disable Automatic Clustering: For infrequently queried tables, assess if automatic clustering is necessary or if it can be disabled to save on compute costs.

Proactive Cost Management with Snowflake

Snowflake offers various tools and features to help you manage costs proactively:

• Resource Monitors: Set up resource monitors to track warehouse usage and receive alerts when consumption exceeds predefined thresholds.

• Cost Allocation: Use tags to attribute costs to different departments, projects, or users.

• Budgeting: Create budgets to monitor spending and receive notifications when you approach or exceed your budget.

Conclusion

By actively monitoring Snowflake Cost Insights and applying the recommended actions, you can optimize your resource utilization, reduce unnecessary spending, and get the most value out of your Snowflake investment.

Resources: https://docs.snowflake.com/en/user-guide/cost-optimize#label-cost-optimize-cost-insights