Exploring compute cost¶

Compute for warehouses¶

Query: Average hour-by-hour Snowflake spend (across all warehouses) over the past m days

This query shows the total credit consumption on an hourly basis to help understand consumption trends (peaks, valleys) over the past m days. This helps identify times of day when there are spikes in consumption.

SELECT start_time,
  warehouse_name,
  credits_used_compute
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time >= DATEADD(day, -m, CURRENT_TIMESTAMP())
  AND warehouse_id > 0  -- Skip pseudo-VWs such as "CLOUD_SERVICES_ONLY"
ORDER BY 1 DESC, 2;

-- by hour
SELECT DATE_PART('HOUR', start_time) AS start_hour,
  warehouse_name,
  AVG(credits_used_compute) AS credits_used_compute_avg
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time >= DATEADD(day, -m, CURRENT_TIMESTAMP())
  AND warehouse_id > 0  -- Skip pseudo-VWs such as "CLOUD_SERVICES_ONLY"
GROUP BY 1, 2
ORDER BY 1, 2;

Copy

Query: Credit consumption by warehouse over specific time period

This query shows the total credit consumption for each warehouse over a specific time period. This helps identify warehouses that are consuming more credits than others and specific warehouses that are consuming more credits than anticipated.

-- Credits used (all time = past year)
SELECT warehouse_name,
  SUM(credits_used_compute) AS credits_used_compute_sum
FROM snowflake.account_usage.warehouse_metering_history
GROUP BY 1
ORDER BY 2 DESC;

-- Credits used (past N days/weeks/months)
SELECT warehouse_name,
  SUM(credits_used_compute) AS credits_used_compute_sum
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time >= DATEADD(day, -m, CURRENT_TIMESTAMP())
GROUP BY 1
ORDER BY 2 DESC;

Copy

Query: Warehouse usage over m-day average

This query returns the daily average credit consumption grouped by week and warehouse. It can be used to identify anomalies in credit consumption for warehouses across weeks from the past year.

WITH cte_date_wh AS (
  SELECT TO_DATE(start_time) AS start_date,
    warehouse_name,
    SUM(credits_used) AS credits_used_date_wh
  FROM snowflake.account_usage.warehouse_metering_history
  GROUP BY start_date, warehouse_name
)

SELECT start_date,
  warehouse_name,
  credits_used_date_wh,
  AVG(credits_used_date_wh) OVER (PARTITION BY warehouse_name ORDER BY start_date ROWS m PRECEDING) AS credits_used_m_day_avg,
  100.0*((credits_used_date_wh / credits_used_m_day_avg) - 1) AS pct_over_to_m_day_average
FROM cte_date_wh
  QUALIFY credits_used_date_wh > 100  -- Minimum N=100 credits
    AND pct_over_to_m_day_average >= 0.5  -- Minimum 50% increase over past m day average
ORDER BY pct_over_to_m_day_average DESC;

Copy

Compute for cloud services¶

Query: Billed cloud services

Usage for cloud services is billed only if the daily consumption of cloud services exceeds 10% of the daily usage of virtual warehouses. This query returns how much of cloud services consumption was actually billed for a particular day, ordered by the highest billed amount.

SELECT
    usage_date,
    credits_used_cloud_services,
    credits_adjustment_cloud_services,
    credits_used_cloud_services + credits_adjustment_cloud_services AS billed_cloud_services
FROM snowflake.account_usage.metering_daily_history
WHERE usage_date >= DATEADD(month,-1,CURRENT_TIMESTAMP())
    AND credits_used_cloud_services > 0
ORDER BY 4 DESC;

Copy

Query: Total cloud services cost by type of query

This query returns the total credits consumed for cloud services by a particular type of query.

SELECT query_type,
  SUM(credits_used_cloud_services) AS cs_credits,
  COUNT(1) num_queries
FROM snowflake.account_usage.query_history
WHERE true
  AND start_time >= TIMESTAMPADD(day, -1, CURRENT_TIMESTAMP)
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10;

Copy

Query: Cloud services cost for queries of a given type

This query returns the total credits consumed for cloud services by all queries of a specifc type. Replace 'COPY' if you want to focus on a different type of query and day if you want to explore a longer or shorter period of time.

SELECT *
FROM snowflake.account_usage.query_history
WHERE true
  AND start_time >= TIMESTAMPADD(day, -1, CURRENT_TIMESTAMP)
  AND query_type = 'COPY'
ORDER BY credits_used_cloud_services DESC
LIMIT 10;

Copy

Query: Warehouses with high cloud services usage

This query shows the warehouses that are not using enough warehouse time to cover the cloud services portion of compute. This provides a launching point for additional investigation by isolating warehouses with a high ratio of cloud service use (>10% of overall credits). Investigation candidates include issues around cloning, listing files in S3, partner tools, setting session parameters, etc.

SELECT
  warehouse_name,
  SUM(credits_used) AS credits_used,
  SUM(credits_used_cloud_services) AS credits_used_cloud_services,
  SUM(credits_used_cloud_services)/SUM(credits_used) AS percent_cloud_services
FROM snowflake.account_usage.warehouse_metering_history
WHERE TO_DATE(start_time) >= DATEADD(month,-1,CURRENT_TIMESTAMP())
    AND credits_used_cloud_services > 0
GROUP BY 1
ORDER BY 4 DESC;

Copy

Query: Cloud services usage sorted by portion of query time

This query returns all queries run within the last minute and sorts them by parts of total query execution time (e.g. compilation time vs. queue time).

SELECT *
FROM snowflake.account_usage.query_history
WHERE true
  AND start_time >= TIMESTAMPADD(minute, -60, CURRENT_TIMESTAMP)
ORDER BY compilation_time DESC,
  execution_time DESC,
  list_external_files_time DESC,
  queued_overload_time DESC,
  credits_used_cloud_services DESC
LIMIT 10;

Copy

Compute for Automatic Clustering¶

Query: Automatic Clustering cost history (by day, by object)

This query provides a list of tables with Automatic Clustering and the volume of credits consumed via the service over the last 30 days, broken out by day. Any irregularities in the credit consumption or consistently high consumption are flags for additional investigation.

SELECT TO_DATE(start_time) AS date,
  database_name,
  schema_name,
  table_name,
  SUM(credits_used) AS credits_used
FROM snowflake.account_usage.automatic_clustering_history
WHERE start_time >= DATEADD(month,-1,CURRENT_TIMESTAMP())
GROUP BY 1,2,3,4
ORDER BY 5 DESC;

Copy

Query: Automatic Clustering History & m-day average

This query shows the average daily credits consumed by Automatic Clustering grouped by week over the last year. It can help identify anomalies in daily averages over the year so you can investigate spikes or unexpected changes in consumption.

WITH credits_by_day AS (
  SELECT TO_DATE(start_time) AS date,
    SUM(credits_used) AS credits_used
  FROM snowflake.account_usage.automatic_clustering_history
  WHERE start_time >= DATEADD(year,-1,CURRENT_TIMESTAMP())
  GROUP BY 1
  ORDER BY 2 DESC
)

SELECT DATE_TRUNC('week',date),
      AVG(credits_used) AS avg_daily_credits
FROM credits_by_day
GROUP BY 1
ORDER BY 1;

Copy

Compute for Search Optimization¶

Query: Search Optimization cost history (by day, by object)

This query provides a full list of tables with Search Optimization and the volume of credits consumed via the service over the last 30 days, broken out by day. Any irregularities in the credit consumption or consistently high consumption are flags for additional investigation.

SELECT TO_DATE(start_time) AS date,
  database_name,
  schema_name,
  table_name,
  SUM(credits_used) AS credits_used
FROM snowflake.account_usage.search_optimization_history
WHERE start_time >= DATEADD(month,-1,CURRENT_TIMESTAMP())
GROUP BY 1,2,3,4
ORDER BY 5 DESC;

Copy

Query: Search Optimization History & m-day average

This query shows the average daily credits consumed by Search Optimization grouped by week over the last year. It can help identify anomalies in daily averages over the year so you can investigate spikes or unexpected changes in consumption.

WITH credits_by_day AS (
  SELECT TO_DATE(start_time) AS date,
    SUM(credits_used) AS credits_used
  FROM snowflake.account_usage.search_optimization_history
  WHERE start_time >= DATEADD(year,-1,CURRENT_TIMESTAMP())
  GROUP BY 1
  ORDER BY 2 DESC
)

SELECT DATE_TRUNC('week', date),
  AVG(credits_used) as avg_daily_credits
FROM credits_by_day
GROUP BY 1
ORDER BY 1;

Copy

Compute for Materialized Views¶

Query: Materialized Views cost history (by day, by object)

This query provides a full list of materialized views and the volume of credits consumed via the service over the last 30 days, broken out by day. Any irregularities in the credit consumption or consistently high consumption are flags for additional investigation.

SELECT TO_DATE(start_time) AS date,
  database_name,
  schema_name,
  table_name,
  SUM(credits_used) AS credits_used
FROM snowflake.account_usage.materialized_view_refresh_history
WHERE start_time >= DATEADD(month,-1,CURRENT_TIMESTAMP())
GROUP BY 1,2,3,4
ORDER BY 5 DESC;

Copy

Query: Materialized Views History & m-day average

This query shows the average daily credits consumed by materialized views grouped by week over the last year. It can help identify anomalies in daily averages over the year so you can investigate spikes or unexpected changes in consumption.

WITH credits_by_day AS (
  SELECT TO_DATE(start_time) AS date,
    SUM(credits_used) AS credits_used
  FROM snowflake.account_usage.materialized_view_refresh_history
  WHERE start_time >= DATEADD(year,-1,CURRENT_TIMESTAMP())
  GROUP BY 1
  ORDER BY 2 DESC
)

SELECT DATE_TRUNC('week',date),
  AVG(credits_used) AS avg_daily_credits
FROM credits_by_day
GROUP BY 1
ORDER BY 1;

Copy

Compute for Query Acceleration Service¶

Query: Query Acceleration Service cost by warehouse

This query returns the total number of credits used by each warehouse in your account for the query acceleration service (month-to-date):

SELECT warehouse_name,
       SUM(credits_used) AS total_credits_used
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_HISTORY
  WHERE start_time >= DATE_TRUNC(month, CURRENT_DATE)
  GROUP BY 1
  ORDER BY 2 DESC;

Copy

Compute for Snowpipe and Snowpipe Streaming¶

Query: Cumulative usage of data ingest (Snowpipe and “Copy”)

This query returns an aggregated daily summary of all loads for each table in Snowflake showing average file size, total rows, total volume and the ingest method (copy or Snowpipe). If file sizes are too small or big for optimal ingest, additional investigation/optimization may be required. By mapping the volume to credit consumption, it is possible to determine which tables are consuming more credits per TB loaded.

SELECT TO_DATE(last_load_time) AS load_date,
  status,
  table_catalog_name AS database_name,
  table_schema_name AS schema_name,
  table_name,
  CASE
    WHEN pipe_name IS NULL THEN 'COPY'
    ELSE 'SNOWPIPE'
  END AS ingest_method,
  SUM(row_count) AS row_count,
  SUM(row_parsed) AS rows_parsed,
  AVG(file_size) AS avg_file_size_bytes,
  SUM(file_size) AS total_file_size_bytes,
  SUM(file_size)/POWER(1024,1) AS total_file_size_kb,
  SUM(file_size)/POWER(1024,2) AS total_file_size_mb,
  SUM(file_size)/POWER(1024,3) AS total_file_size_gb,
  SUM(file_size)/POWER(1024,4) AS total_file_size_tb
FROM snowflake.account_usage.copy_history
GROUP BY 1,2,3,4,5,6
ORDER BY 3,4,5,1,2;

Copy

Query: Snowpipe cost history (by day, by object)

This query provides a full list of pipes and the volume of credits consumed via the service over the last 30 days, broken out by day. Any irregularities in the credit consumption or consistently high consumption are flags for additional investigation.

SELECT TO_DATE(start_time) AS date,
  pipe_name,
  SUM(credits_used) AS credits_used
FROM snowflake.account_usage.pipe_usage_history
WHERE start_time >= DATEADD(month,-1,CURRENT_TIMESTAMP())
GROUP BY 1,2
ORDER BY 3 DESC;

Copy

Query: Snowpipe History & m-day average

This query shows the average daily credits consumed by Snowpipe grouped by week over the last year. It can help identify anomalies in daily averages over the year so you can investigate spikes or unexpected changes in consumption.

WITH credits_by_day AS (
  SELECT TO_DATE(start_time) AS date,
    SUM(credits_used) AS credits_used
  FROM snowflake.account_usage.pipe_usage_history
  WHERE start_time >= DATEADD(year,-1,CURRENT_TIMESTAMP())
  GROUP BY 1
  ORDER BY 2 DESC
)

SELECT DATE_TRUNC('week',date),
  AVG(credits_used) AS avg_daily_credits
FROM credits_by_day
GROUP BY 1
ORDER BY 1;

Copy

Query: Total Snowpipe Streaming cost

This query lists the current credit usage for Snowpipe Streaming, including both Snowpipe Streaming compute and client costs.

SELECT start_time,
  end_time,
  SUM(credits_used) AS total_credits,
  name,
  IFF(CONTAINS(name,':'),'streaming client cost', 'streaming compute cost') AS streaming_cost_type
FROM SNOWFLAKE.ACCOUNT_USAGE.METERING_HISTORY
WHERE service_type ='SNOWPIPE_STREAMING'
GROUP BY ALL;

Copy

Compute for serverless alerts¶

Query: Total serverless alert cost

This query lists the current credit usage for all serverless alerts:

SELECT
    start_time,
    end_time,
    alert_id,
    alert_name,
    credits_used,
    schema_id,
    schema_name,
    database_id,
    database_name
  FROM SNOWFLAKE.ACCOUNT_USAGE.serverless_alert_history
  ORDER BY start_time, alert_id;

Copy

Compute for serverless tasks¶

Query: Total serverless task cost

This query lists the current credit usage for all serverless tasks:

SELECT start_time,
  end_time,
  task_id,
  task_name,
  credits_used,
  schema_id,
  schema_name,
  database_id,
  database_name
FROM snowflake.account_usage.serverless_task_history
ORDER BY start_time, task_id;

Copy

Compute for replication¶

Query: Account replication cost

This query lists the credits used by a replication or failover group for account replication in the current month:

SELECT start_time, 
  end_time, 
  replication_group_name, 
  credits_used, 
  bytes_transferred
FROM snowflake.account_usage.replication_group_usage_history
WHERE start_time >= DATE_TRUNC('month', CURRENT_DATE());

Copy

Query: Database replication cost history (by day, by object)

This query provides a full list of replicated databases and the volume of credits consumed via the replication service over the last 30 days, broken out by day. Any irregularities in the credit consumption or consistently high consumption are flags for additional investigation.

SELECT TO_DATE(start_time) AS date,
  database_name,
  SUM(credits_used) AS credits_used
FROM snowflake.account_usage.database_replication_usage_history
WHERE start_time >= DATEADD(month,-1,CURRENT_TIMESTAMP())
GROUP BY 1,2
ORDER BY 3 DESC;

Copy

Query: Database replication History & m-day average

This query shows the average daily credits consumed by Replication grouped by week over the last year. This helps identify any anomalies in the daily average so you can investigate any spikes or changes in consumption.

WITH credits_by_day AS (
  SELECT TO_DATE(start_time) AS date,
    SUM(credits_used) AS credits_used
  FROM snowflake.account_usage.database_replication_usage_history
  WHERE start_time >= DATEADD(year,-1,CURRENT_TIMESTAMP())
  GROUP BY 1
  ORDER BY 2 DESC
)

SELECT DATE_TRUNC('week',date),
  AVG(credits_used) AS avg_daily_credits
FROM credits_by_day
GROUP BY 1
ORDER BY 1;

Copy

Compute for partner tools¶

Query: Credit consumption by partner tools

This query identifies which of Snowflake’s partner tools/solutions (e.g. BI, ETL, etc.) are consuming the most credits. This can help identify partner solutions that are consuming more credits than anticipated, which can be a starting point for additional investigation.

-- This Is Approximate Credit Consumption By Client Application
WITH
  client_hour_execution_cte AS (
    SELECT
      CASE
        WHEN client_application_id LIKE 'Go %' THEN 'Go'
        WHEN client_application_id LIKE 'Snowflake UI %' THEN 'Snowflake UI'
        WHEN client_application_id LIKE 'SnowSQL %' THEN 'SnowSQL'
        WHEN client_application_id LIKE 'JDBC %' THEN 'JDBC'
        WHEN client_application_id LIKE 'PythonConnector %' THEN 'Python'
        WHEN client_application_id LIKE 'ODBC %' THEN 'ODBC'
        ELSE 'NOT YET MAPPED: ' || CLIENT_APPLICATION_ID
      END AS client_application_name,
      warehouse_name,
      DATE_TRUNC('hour',start_time) AS start_time_hour,
      SUM(execution_time)  AS client_hour_execution_time
    FROM snowflake.account_usage.query_history qh
      JOIN snowflake.account_usage.sessions se
        ON se.session_id = qh.session_id
    WHERE warehouse_name IS NOT NULL
      AND execution_time > 0
      AND start_time > DATEADD(month,-1,CURRENT_TIMESTAMP())
    GROUP BY 1,2,3
  ),
  hour_execution_cte AS (
    SELECT start_time_hour,
      warehouse_name,
      SUM(client_hour_execution_time) AS hour_execution_time
    FROM client_hour_execution_cte
    GROUP BY 1,2
  ),
  approximate_credits AS (
    SELECT A.client_application_name,
      C.warehouse_name,
      (A.client_hour_execution_time/B.hour_execution_time)*C.credits_used AS approximate_credits_used
    FROM client_hour_execution_cte A
      JOIN hour_execution_cte B
        ON A.start_time_hour = B.start_time_hour and B.warehouse_name = A.warehouse_name
      JOIN snowflake.account_usage.warehouse_metering_history C
        ON C.warehouse_name = A.warehouse_name AND C.start_time = A.start_time_hour
  )

SELECT client_application_name,
  warehouse_name,
  SUM(approximate_credits_used) AS approximate_credits_used
FROM approximate_credits
GROUP BY 1,2
ORDER BY 3 DESC;

Copy

Compute for hybrid tables¶

Query: Credit consumption by hybrid table over a specific period of time

This query shows the total credit consumption for each hybrid table over a specific period of time. This helps identify hybrid tables that are consuming more credits than others and specific hybrid tables that are consuming more credits than anticipated.

-- Credits used (all time = past year)
SELECT object_name,
  SUM(credits_used) AS total_credits
FROM snowflake.account_usage.hybrid_table_usage_history
GROUP BY 1
ORDER BY 2 DESC;

-- Credits used (past N days/weeks/months)
SELECT object_name,
  SUM(credits_used) AS total_credits
FROM snowflake.account_usage.hybrid_table_usage_history
WHERE start_time >= DATEADD(day, -m, CURRENT_TIMESTAMP()) 
GROUP BY 1
ORDER BY 2 DESC;

Copy

Compute for Cortex Fine-tuning¶

Query: Credit consumption by Cortex Fine-tuning.

This query shows the training credit consumption for each Cortex Fine-tuning, aggregated in one hour increments.

SELECT *
  FROM SNOWFLAKE.ACCOUNT_USAGE.CORTEX_FINE_TUNING_USAGE_HISTORY;

Copy

Compute for Cortex functions¶

Query: Credit consumption by Cortex functions.

This query shows the credit consumption for each Cortex function call, aggregated in one hour increments based on function and model.

SELECT *
  FROM SNOWFLAKE.ACCOUNT_USAGE.CORTEX_FUNCTIONS_USAGE_HISTORY;

Copy

Query: Credit consumption by Cortex function called with the mistral-large model.

This query shows the credit consumption for each Cortex function called with the mistral-large model, aggregated in one hour increments based on function and model.

SELECT *
  FROM SNOWFLAKE.ACCOUNT_USAGE.CORTEX_FUNCTIONS_USAGE_HISTORY
  WHERE model_name = 'mistral-large';

Copy

Compute for Cortex Search serving¶

Query: Credit consumption by Cortex Search serving.

This query shows the serving credit consumption for each Cortex Search Service, aggregated in one hour increments.

SELECT *
  FROM SNOWFLAKE.ACCOUNT_USAGE.CORTEX_SEARCH_SERVING_USAGE_HISTORY;

Copy

Compute for Document AI¶

Query: Credit consumption by Document AI.

This query shows the credit consumption for Document AI.

SELECT *
  FROM SNOWFLAKE.ACCOUNT_USAGE.DOCUMENT_AI_USAGE_HISTORY;

Copy