Attributing Cost¶

Overview¶

Snowflake offers a flexible set of cost attribution features that enable organizations to attribute cost at any level of the Snowflake object hierarchy. These features can attribute credit consumption to groupings like cost centers, environments, and projects. Snowflake provides the following attribution strategies:

• Object tagging can provide granular attribution that allows you to assign the cost of using individual resources like a warehouse or database to a specific unit within the organization.

• Executing queries can attribute warehouse usage by role, user, or query, which is particularly helpful when multiple cost centers share the same warehouse.

Attribute Cost of Individual Resources¶

The most direct, powerful way to attribute cost to different groupings like departments, environments, or projects is to tag specific resources using the object tagging feature. In this strategy, an administrator creates a tag (e.g. cost_center), then defines a list of possible values of the tag (e.g. sales, finance). In this example, each cost center gets a unique tag value. The tag/value combination is then assigned to resources used by a cost center. As these resources consume credits, you can run reports grouped by the tag value. Because this tag value corresponds directly to a particular grouping within the organization, costs can be attributed accurately.

Object tagging allows you to attribute cost based on something as general as an account or as specific as a table. Note that not all taggable objects accumulate cost. From a cost management perspective, tagging an object only makes sense if there is a schema view that contains usage or cost data related to that object. For a list of Snowflake usage and cost views, see Exploring Overall Cost.

Attribution Workflow¶

Using object tags to attribute cost consists of three basic tasks:

1. Design a tagging strategy and create the appropriate tags.

2. Tag resources with the correct tag/value pair.

3. Run reports based on usage and tag data.

To help illustrate this workflow, consider a Snowflake account that:

• Has two cost centers: Sales and finance.

• Has three warehouses. Sales uses Warehouse1 and Warehouse2, while the finance department uses Warehouse3.

In this example, the goal is to attribute warehouse costs associated with each warehouse to the appropriate department.

Note

The following example assumes that appropriate privileges have been granted to the role tag_admin. An organization can use the granular tagging permissions to develop a secure tagging strategy.

Task 1: Design tagging strategy and create the appropriate tags

For this example, an administrator with the proper privileges creates a single tag called cost_center.

use role tag_admin;
use schema my_db.my_schema;
create tag cost_center;

Task 2: Tag objects with the correct tag/value pair

The tag administrator applies the appropriate tag/value pair to the warehouses. For example, since the sales department uses both warehouse1 and warehouse2, the same tag/value combination is applied to these two resources.

use role tag_admin;
alter warehouse warehouse1 set tag cost_center='SALES';
alter warehouse warehouse2 set tag cost_center='SALES';
alter warehouse warehouse3 set tag cost_center='FINANCE';

Note

If administrators want to control which values can be set on the object, they can use the optional allowed_values parameter when creating the tag. For details, see CREATE TAG.

Task 3: Run reports based on usage and tag data

You can attribute costs by reporting on the use of resources that have the cost_center tag. This data is available in Snowsight, the Snowflake web interface, or by querying the SNOWFLAKE.ACCOUNT_USAGE schema.

Snowsight

1. Switch to a role that has access to the ACCOUNT_USAGE schema.

2. Navigate to Admin » Usage.

3. From the Tags drop-down, select the cost_center tag.

4. To focus on a specific cost center, select a value from the list of the tag’s values.

5. Click Apply.

For more details about filtering in Snowsight, see Filter by Tag.

SQL

The Account Usage schema contains data about both resource usage and the tags in the schema. Executing a query against views in this schema attributes credit consumption by the warehouses to the correct cost center.

use snowflake.account_usage;
select tag_value as cost_center,
  sum(credits_used)
from warehouse_metering_history, tag_references
where warehouse_name=object_name
  and tag_name='COST_CENTER'
group by 1
order by 2 desc;

The query returns a report like:

COST_CENTER	CREDITS_USED
SALES	17.173333333
FINANCE	8.14444444

For complete details about using object tags, see Object Tagging.

Attribute Cost for a Shared Warehouse¶

In certain scenarios, a warehouse is shared between multiple teams, and it is necessary to split warehouse cost amongst the teams. There are three ways to separate the cost: by user, by role, or by a custom query tag. Each of these is briefly discussed, followed by the query that is used to generate a report for each approach.

By User

Every query that is executed in a virtual warehouse is associated with the user that ran the query. This data is available in the USER_NAME column of the QUERY_HISTORY View. You can join this data with usage data in the WAREHOUSE_METERING_HISTORY view to break down warehouse costs by user.

For example, if you modified the query below to include USER_NAME, the report might look like:

USER_NAME	WAREHOUSE_NAME	APPROXIMATE_CREDITS_USED
JOHN	WAREHOUSE1	11.255295833
SALLY	WAREHOUSE1	3.79444444
JOHN	WAREHOUSE2	1.636703054

By Role

Every query that is executed in a virtual warehouse is associated with the role that runs the query. This data is available in the ROLE_NAME column of the QUERY_HISTORY View. Every Snowflake user has at least one role.

By joining the query’s role from the QUERY_HISTORY view with the usage data in the WAREHOUSE_METERING_HISTORY view, the cost administrator can break down warehouse costs by role.

For example, if you modified the query below to include ROLE_NAME, the report might look like:

ROLE_NAME	WAREHOUSE_NAME	APPROXIMATE_CREDITS_USED
SALES_ADMIN	WAREHOUSE1	17.20239833
DEV_ADMIN	WAREHOUSE1	5.7990244
SALES_ADMIN	WAREHOUSE2	1.49765448

By Query Tag

When multiple applications share the same username and warehouse, the QUERY_TAG session parameter can help attribute the cost amongst the applications. This session parameter applies a tag to queries and other SQL statements executed within a session.

To use this attribution strategy, first set the QUERY_TAG for the session. For example:

ALTER SESSION SET QUERY_TAG = 'APP1'

Once this parameter is set for the session, every query that runs during the session is stored in the QUERY_HISTORY View along with the APP1 query tag.

To run a report on costs associated with particular query tags, modify the query below to include QUERY_TAG. The result might look like:

QUERY_TAG	WAREHOUSE_NAME	APPROXIMATE_CREDITS_USED
SALES_APP	WAREHOUSE1	14.3027235
DEV_APP	WAREHOUSE1	11.2938568
SALES_APP	WAREHOUSE2	9.235620

Query: Split warehouse usage by user, role, or query tag

You can use a query to generate a report that splits shared warehouse costs by user, by role, or by query tag. This query is basically the same regardless of how the costs are separated. It joins query metadata from the QUERY_HISTORY View with usage data in the WAREHOUSE_METERING_HISTORY View to break down warehouse costs in the last month.

To generate a report, adjust the following query by modifying the SELECT statements to project the appropriate column of the QUERY_HISTORY view. For example, if you are attributing warehouse costs to various roles, specify the ROLE_NAME column.

-- Replace <query_metadata> with USER_NAME, ROLE_NAME, or QUERY_TAG

WITH HOUR_EXECUTION_TIME_CTE AS (
    SELECT <query_metadata>
      ,WAREHOUSE_NAME
      ,DATE_TRUNC('hour',START_TIME) as START_TIME_HOUR
      ,SUM(EXECUTION_TIME)  as HOUR_EXECUTION_TIME
    FROM "SNOWFLAKE"."ACCOUNT_USAGE"."QUERY_HISTORY"
    WHERE WAREHOUSE_NAME IS NOT NULL
    AND EXECUTION_TIME > 0

--Change the below filter if you want to look at a longer range than the last 1 month
    AND START_TIME > DATEADD(Month,-1,CURRENT_TIMESTAMP())
    group by 1,2,3
    )
, HOUR_EXECUTION_TOTAL_CTE AS (
    SELECT START_TIME_HOUR
      ,WAREHOUSE_NAME
      ,SUM(HOUR_EXECUTION_TIME) AS HOUR_EXECUTION_TOTAL
    FROM HOUR_EXECUTION_TIME_CTE
    group by 1,2
)
, HOUR_APPROXIMATE_CREDITS_USED AS (
    SELECT
      A.<query_metadata>
      ,C.WAREHOUSE_NAME
      ,(A.HOUR_EXECUTION_TIME/B.HOUR_EXECUTION_TOTAL)*C.CREDITS_USED AS HOUR_APPROXIMATE_CREDITS_USED

    FROM HOUR_EXECUTION_TIME_CTE A
    JOIN HOUR_EXECUTION_TOTAL_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
  <query_metadata>
  , WAREHOUSE_NAME
  ,SUM(HOUR_APPROXIMATE_CREDITS_USED) AS APPROXIMATE_CREDITS_USED
FROM HOUR_APPROXIMATE_CREDITS_USED
GROUP BY 1, 2
ORDER BY 3 DESC
;