Exploring compute cost

Total compute cost consists of the overall use of:

  • Virtual warehouses (user-managed compute resources)

  • Serverless features such as Automatic Clustering and Snowpipe that use Snowflake-managed compute resources

  • Cloud services layer of the Snowflake architecture

This topic describes how to gain insight into historical compute costs using Snowsight, or by writing queries against views in the ACCOUNT_USAGE and ORGANIZATION_USAGE schemas. Snowsight allows you to quickly and easily obtain information about cost from a visual dashboard. Queries against the usage views allow you to drill down into cost data and can help generate custom reports and dashboards.

If you need more information about how compute costs are incurred, refer to Understanding compute cost.

Note

The cloud services layer consumes credits, but not all of those credits are actually billed. Usage for cloud services is charged only if the daily consumption of cloud services exceeds 10% of the daily usage of virtual warehouses. Snowsight and a majority of views show the total number of credits consumed by warehouses, serverless features, and cloud services without accounting for this daily adjustment to cloud services. To determine how many credits were actually billed for compute costs, run queries against the METERING_DAILY_HISTORY view.

Viewing credit usage

All compute resources (virtual warehouses, serverless, cloud services) consume Snowflake credits. Users can use Snowsight to view the overall cost of compute usage for any given day, week, or month.

To explore compute cost:

  1. Sign in to Snowsight.

  2. Switch to the ACCOUNTADMIN role. If you are not the account administrator, switch to a role with access to cost and usage data.

  3. Navigate to Admin » Cost Management.

  4. Select a warehouse to use to view the usage data. Snowflake recommends using an XS warehouse for this purpose.

  5. Select Consumption.

  6. Select Compute from the Usage Type drop-down.

Note

If a user has the ACCOUNTADMIN role, but does not have the ORGADMIN role, they can only view costs for the current account. The Account filter that would allow them to switch to a different account does not appear.

Filter by tag

You can use tags to attribute the cost of using resources to a logical unit within your organization. A tag is a Snowflake object that can have one or more values associated with it. A user with the appropriate privileges applies a tag/value pair to each resource that is used by a cost center or other logical unit (e.g. the Development environment, a business unit, or business line). Once resources have been tagged, you can isolate costs based on a specific tag/value pair, allowing you to attribute this cost to a specific logical unit.

To filter the Consumption dashboard to show costs associated with a specific tag/value combination:

  1. In Snowsight, open the Consumption dashboard.

  2. From the Tags drop-down, select the tag.

  3. Select the value from the list of the tag’s values.

  4. Select Apply.

For example, you can use the drop-down to select the COST_CENTER tag and the SALES value to show usage associated with resources tagged with COST_CENTER = SALES while excluding all other usage from the dashboard.

You can also display all resources with a tag regardless of their tag value. Use the drop down to select a tag, then choose All instead of a specific value.

View consumption by type, service, or resource

When viewing the bar graph that displays compute history, you can filter the data By Type, By Service or By Resource.

By Type:

Separates resource consumption into compute (virtual warehouses and serverless resources) and cloud services. For the purpose of this filter, cloud services is separated out from the other types of compute resources.

By Service:

Separates resource consumption into warehouse consumption and consumption by each serverless feature. For example, WAREHOUSE_METERING represents credits consumed by warehouses while PIPE represents credits consumed by the serverless Snowpipe feature. Cloud services compute is included in warehouse consumption.

By Resource:

Separates resource consumption by the Snowflake object that consumed credits. For example, each warehouse is represented, as is every table that incurred serverless costs.

Querying data for compute cost

Snowflake provides two schemas, ORGANIZATION_USAGE and ACCOUNT_USAGE, that contain data related to usage and cost. The ORGANIZATION_USAGE schema provides cost information for all of the accounts in the organization while the ACCOUNT_USAGE schema provides similar information for a single account. Views in these schemas provide granular, analytics-ready usage data to build custom reports or dashboards.

Most views in the ORGANIZATION_USAGE and ACCOUNT_USAGE schemas contain the cost of compute resources in terms of credits consumed. To explore compute cost in currency, rather than credits, write queries against the USAGE_IN_CURRENCY_DAILY View. This view converts credits consumed into cost in currency using the daily price of a credit.

The following views provide usage and cost information related to compute cost.

View

Compute Resource

Description

Schema

AUTOMATIC_CLUSTERING_HISTORY

Serverless

Credits consumed by automatic clustering.

ORGANIZATION_USAGE ACCOUNT_USAGE

DATABASE_REPLICATION_USAGE_ HISTORY

Serverless

Credits consumed for database replication.

ACCOUNT_USAGE

HYBRID_TABLE_USAGE_HISTORY

Serverless

Credits consumed for Hybrid Table Requests resources.

ACCOUNT_USAGE

LISTING_AUTO_FULFILLMENT_ REFRESH_DAILY

Warehouses

Credits used to refresh data fulfilled to other regions by Cross-Cloud Auto-Fulfillment.

DATA_SHARING_USAGE

LISTING_AUTO_FULFILLMENT_ USAGE_HISTORY

Warehouses

Estimated usage associated with fulfilling data products to other regions by using Cross-Cloud Auto-Fulfillment. Refer to the SERVICE_TYPE of REPLICATION.

ORGANIZATION_USAGE

MATERIALIZED_VIEW_REFRESH_ HISTORY

Serverless

Credits consumed the refreshing of materialized views.

ORGANIZATION_USAGE ACCOUNT_USAGE

METERING_DAILY_HISTORY

Warehouses

Serverless

Cloud Services

Credits consumed by all compute resources (warehouses, serverless, and cloud services) in a given day.

Can be used to determine whether cloud services compute costs were actually billed for a specific day (i.e. cloud services credit consumption exceeded 10% of warehouse consumption).

ORGANIZATION_USAGE ACCOUNT_USAGE

METERING_HISTORY

Warehouses

Serverless

Cloud Services

Credits consumed by warehouses and cloud services on an hourly basis. To see how many credits an individual warehouse is consuming, query the WAREHOUSE_METERING_HISTORY view.

ACCOUNT_USAGE

PIPE_USAGE_HISTORY

Serverless

Credits consumed by Snowpipe.

ORGANIZATION_USAGE ACCOUNT_USAGE

QUERY_ACCELERATION_HISTORY

Serverless

Credits consumed by the query acceleration service.

ACCOUNT_USAGE

REPLICATION_USAGE_HISTORY

Serverless

Credits consumed and number of bytes transferred during database replication. If possible, use the DATABASE_REPLICATION_USAGE_HISTORY view instead.

ORGANIZATION_USAGE ACCOUNT_USAGE

REPLICATION_GROUP_USAGE_ HISTORY

Serverless

Credits consumed and number of bytes transferred during replication for a specific replication group.

ACCOUNT_USAGE

SEARCH_OPTIMIZATION_HISTORY

Serverless

Credits consumed by the search optimization service.

ACCOUNT_USAGE

SERVERLESS_TASK_HISTORY

Serverless

Credits consumed by tasks.

ACCOUNT_USAGE

SNOWPIPE_STREAMING_FILE_ MIGRATION_HISTORY

Serverless

Credits consumed by Snowpipe Streaming compute (does not include client costs).

ACCOUNT_USAGE

USAGE_IN_CURRENCY_DAILY

Warehouses

Serverless

Cloud Services

Daily credit consumption by all compute resources along with the cost of that usage in the organization’s currency.

ORGANIZATION_USAGE

WAREHOUSE_METERING_HISTORY

Warehouses

Cloud Services

Hourly credit usage of each warehouse, including the cloud services cost associated with using the warehouse.

ORGANIZATION_USAGE ACCOUNT_USAGE

Note

The views and table functions of the Snowflake Information Schema also provide usage data related to cost. Though the ACCOUNT_USAGE schema is preferred, the Information Schema can be faster in some circumstances.

Example queries

The following queries drill-down into data in ACCOUNT_USAGE views to gain insight into compute costs.

Note

Queries executed against views in the Account Usage schema can be modified to gain insight into cost for the entire organization by using the corresponding view in the Organization Usage schema. For example, both schemas include a WAREHOUSE_METERING_HISTORY view.

Click the name of a query below to see the full SQL example.

Compute for Warehouses:
Compute for Cloud Services:
Compute for Automatic Clustering:
Compute for Search Optimization:
Compute for Materialized Views:
Compute for Query Acceleration Service:
Compute for Snowpipe:
Compute and client costs for Snowpipe Streaming:
Compute for Tasks:
Compute for Replication:
Compute for Partner Tools:
Compute for Hybrid Tables:

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 tasks

Query: Total 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