Attributing cost¶

Creating the tags¶

As part of designing the strategy, decide on the database and schema where you plan to create the tags.

• You can create a dedicated database and schema for the tags.

• If you want to tag resources in different accounts across your organization, you can create the tags in a key account in your organization (for example, in your organization account).

The following example creates a database named cost_management and a schema named tags for the tags that you plan to use:

USE ROLE tag_admin;

CREATE DATABASE cost_management;
CREATE SCHEMA tags;

With cost_management and tags selected as the current database and schema, create a tag named cost_center and set the values allowed for the tag to the names of cost centers:

CREATE TAG cost_center
  ALLOWED_VALUES 'finance', 'marketing', 'engineering', 'product';

Replicating the tag database¶

If you have an organization with multiple accounts and you want to make the tags available in these other accounts, set up your accounts for replication, and create a replication group in a main account (for example, in the organization account). Set up this replication group to replicate the database containing the tags.

For example, to replicate the tags to the accounts named my_org.my_account and my_org.my_account_2, execute this statement in your organization account:

CREATE REPLICATION GROUP cost_management_repl_group
  OBJECT_TYPES = DATABASES
  ALLOWED_DATABASES = cost_management
  ALLOWED_ACCOUNTS = my_org.my_account_1, my_org.my_account_2
  REPLICATION_SCHEDULE = '10 MINUTE';

Then, in each account in which you want to make the tags available, create a secondary replication group, and refresh this group from the primary group:

CREATE REPLICATION GROUP cost_management_repl_group
  AS REPLICA OF my_org.my_org_account.cost_management_repl_group;

ALTER REPLICATION GROUP cost_management_repl_group REFRESH;

Tagging the resources and users¶

After creating and replicating the tags, you can use these tags to identify the warehouses and users belonging to each department. For example, because the sales department uses both warehouse1 and warehouse2, you can set the cost_center tag to 'SALES' for both warehouses.

USE ROLE tag_admin;

ALTER WAREHOUSE warehouse1 SET TAG cost_management.tags.cost_center='SALES';
ALTER WAREHOUSE warehouse2 SET TAG cost_management.tags.cost_center='SALES';
ALTER WAREHOUSE warehouse3 SET TAG cost_management.tags.cost_center='FINANCE';

ALTER USER finance_user SET TAG cost_management.tags.cost_center='FINANCE';
ALTER USER sales_user SET TAG cost_management.tags.cost_center='SALES';

Resources not shared by departments¶

Suppose that you want to attribute costs by department and that each department uses a set of dedicated warehouses.

If you tag warehouses with a cost_center tag to identify the department that owns the warehouse, you can join the ACCOUNT_USAGE TAG_REFERENCES view with the WAREHOUSE_METERING_HISTORY view on the object_id and warehouse_id columns to get usage information by warehouse, and you can use the tag_value column to identify the departments that own those warehouses.

The following SQL statement performs this join:

SELECT
    TAG_REFERENCES.tag_name,
    COALESCE(TAG_REFERENCES.tag_value, 'untagged') AS tag_value,
    SUM(WAREHOUSE_METERING_HISTORY.credits_used_compute) AS total_credits
  FROM
    SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
      LEFT JOIN SNOWFLAKE.ACCOUNT_USAGE.TAG_REFERENCES
        ON WAREHOUSE_METERING_HISTORY.warehouse_id = TAG_REFERENCES.object_id
          AND TAG_REFERENCES.domain = 'WAREHOUSE'
  WHERE
    WAREHOUSE_METERING_HISTORY.start_time >= DATE_TRUNC('MONTH', DATEADD(MONTH, -1, CURRENT_DATE))
      AND WAREHOUSE_METERING_HISTORY.start_time < DATE_TRUNC('MONTH',  CURRENT_DATE)
  GROUP BY TAG_REFERENCES.tag_name, COALESCE(TAG_REFERENCES.tag_value, 'untagged')
  ORDER BY total_credits DESC;

+-------------+-------------+-----------------+
| TAG_NAME    | TAG_VALUE   |   TOTAL_CREDITS |
|-------------+-------------+-----------------|
| NULL        | untagged    |    20.360277159 |
| COST_CENTER | Sales       |    17.173333333 |
| COST_CENTER | Finance     |      8.14444444 |
+-------------+-------------+-----------------+

You can run a similar query to perform the same attribution for all the accounts in your organization using views in the ORGANIZATION_USAGE schema from the organization account. The rest of the query does not change.

SELECT
    TAG_REFERENCES.tag_name,
    COALESCE(TAG_REFERENCES.tag_value, 'untagged') AS tag_value,
    SUM(WAREHOUSE_METERING_HISTORY.credits_used_compute) AS total_credits
  FROM
    SNOWFLAKE.ORGANIZATION_USAGE.WAREHOUSE_METERING_HISTORY
      LEFT JOIN SNOWFLAKE.ORGANIZATION_USAGE.TAG_REFERENCES
        ON WAREHOUSE_METERING_HISTORY.warehouse_id = TAG_REFERENCES.object_id
          AND TAG_REFERENCES.domain = 'WAREHOUSE'
          AND tag_database = 'COST_MANAGEMENT' AND tag_schema = 'TAGS'
  WHERE
    WAREHOUSE_METERING_HISTORY.start_time >= DATE_TRUNC('MONTH', DATEADD(MONTH, -1, CURRENT_DATE))
      AND WAREHOUSE_METERING_HISTORY.start_time < DATE_TRUNC('MONTH',  CURRENT_DATE)
  GROUP BY TAG_REFERENCES.tag_name, COALESCE(TAG_REFERENCES.tag_value, 'untagged')
  ORDER BY total_credits DESC;

Resources shared by users from different departments¶

Suppose that users in different departments share the same warehouses and you want to break down the credits used by each department. You can tag the users with a cost_center tag to identify the department that they belong to, and you can join the TAG_REFERENCES view with the QUERY_ATTRIBUTION_HISTORY view.

The next sections provide examples of SQL statements for attributing costs for shared resources.

• Calculating the cost of user queries for the last month

• Calculating the cost of user queries by department without idle time

• Calculating the cost of queries by users without idle time

• Calculating the cost of queries by users without tags

WITH
  wh_bill AS (
    SELECT SUM(credits_used_compute) AS compute_credits
      FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
      WHERE start_time >= DATE_TRUNC('MONTH', CURRENT_DATE)
        AND start_time < CURRENT_DATE
  ),
  user_credits AS (
    SELECT user_name, SUM(credits_attributed_compute) AS credits
      FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ATTRIBUTION_HISTORY
      WHERE start_time >= DATE_TRUNC('MONTH', CURRENT_DATE)
        AND start_time < CURRENT_DATE
      GROUP BY user_name
  ),
  total_credit AS (
    SELECT SUM(credits) AS sum_all_credits
    FROM user_credits
  )
SELECT
    u.user_name,
    u.credits / t.sum_all_credits * w.compute_credits AS attributed_credits
  FROM user_credits u, total_credit t, wh_bill w
  ORDER BY attributed_credits DESC;

+-----------+--------------------+
| USER_NAME | ATTRIBUTED_CREDITS |
|-----------+--------------------+
| FINUSER   | 6.603575468        |
| SALESUSER | 4.321378049        |
| ENGUSER   | 0.6217131392       |
|-----------+--------------------+

WITH joined_data AS (
  SELECT
      tr.tag_name,
      tr.tag_value,
      qah.credits_attributed_compute,
      qah.start_time
    FROM SNOWFLAKE.ACCOUNT_USAGE.TAG_REFERENCES tr
      JOIN SNOWFLAKE.ACCOUNT_USAGE.QUERY_ATTRIBUTION_HISTORY qah
        ON tr.domain = 'USER' AND tr.object_name = qah.user_name
)
SELECT
    tag_name,
    tag_value,
    SUM(credits_attributed_compute) AS total_credits
  FROM joined_data
  WHERE start_time >= DATEADD(MONTH, -1, CURRENT_DATE)
    AND start_time < CURRENT_DATE
  GROUP BY tag_name, tag_value
  ORDER BY tag_name, tag_value;

+-------------+-------------+-----------------+
| TAG_NAME    | TAG_VALUE   |   TOTAL_CREDITS |
|-------------+-------------+-----------------|
| COST_CENTER | engineering |   0.02493688426 |
| COST_CENTER | finance     |    0.2281084988 |
| COST_CENTER | marketing   |    0.3686840545 |
|-------------+-------------+-----------------|

SELECT user_name, SUM(credits_attributed_compute) AS credits
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ATTRIBUTION_HISTORY
  WHERE
    start_time >= DATEADD(MONTH, -1, CURRENT_DATE)
    AND start_time < CURRENT_DATE
  GROUP BY user_name;

+-----------+--------------------+
| USER_NAME | ATTRIBUTED_CREDITS |
|-----------+--------------------|
| JSMITH    |       17.173333333 |
| MJONES    |         8.14444444 |
| SYSTEM    |         5.33985393 |
+-----------+--------------------+

SELECT qah.user_name, SUM(qah.credits_attributed_compute) as total_credits
  FROM
    SNOWFLAKE.ACCOUNT_USAGE.QUERY_ATTRIBUTION_HISTORY qah
    LEFT JOIN snowflake.account_usage.tag_references tr
    ON qah.user_name = tr.object_name AND tr.DOMAIN = 'USER'
  WHERE
    start_time >= dateadd(month, -1, current_date)
    AND qah.user_name IS NULL OR tr.object_name IS NULL
  GROUP BY qah.user_name
  ORDER BY total_credits DESC;

+------------+---------------+
| USER_NAME  | TOTAL_CREDITS |
|------------+---------------|
| RSMITH     |  0.1830555556 |
+------------+---------------+

Resources used by applications that need to attribute costs to different departments¶

The examples in this section calculate the costs for one or more applications that are powered by Snowflake.

The examples assume that these applications set query tags that identify the application for all queries executed. To set the query tag for queries in a session, execute the ALTER SESSION command. For example:

ALTER SESSION SET QUERY_TAG = 'COST_CENTER=finance';

This associates the COST_CENTER=finance tag with all subsequent queries executed during the session.

You can then use the query tag to trace back the cost incurred by these queries to the appropriate departments.

The next sections provide examples of using this approach.

• Calculating the cost of queries by department

• Calculating the cost of queries (excluding idle time) by query tag

• Calculating the cost of queries (including idle time) by query tag

SELECT
    query_tag,
    SUM(credits_attributed_compute) AS compute_credits,
    SUM(credits_used_query_acceleration) AS qas
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ATTRIBUTION_HISTORY
  WHERE query_tag = 'COST_CENTER=finance'
  GROUP BY query_tag;

+---------------------+-----------------+------+
| QUERY_TAG           | COMPUTE_CREDITS | QAS  |
|---------------------+-----------------|------|
| COST_CENTER=finance |      0.00576115 | null |
+---------------------+-----------------+------+

SELECT
    COALESCE(NULLIF(query_tag, ''), 'untagged') AS tag,
    SUM(credits_attributed_compute) AS compute_credits,
    SUM(credits_used_query_acceleration) AS qas
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ATTRIBUTION_HISTORY
  WHERE start_time >= DATEADD(MONTH, -1, CURRENT_DATE)
  GROUP BY tag
  ORDER BY compute_credits DESC;

+-------------------------+-----------------+------+
| TAG                     | COMPUTE_CREDITS | QAS  |
|-------------------------+-----------------+------+
| untagged                | 3.623173449     | null |
| COST_CENTER=engineering | 0.531431948     | null |
|-------------------------+-----------------+------+

WITH
  wh_bill AS (
    SELECT SUM(credits_used_compute) AS compute_credits
      FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
      WHERE start_time >= DATE_TRUNC('MONTH', CURRENT_DATE)
      AND start_time < CURRENT_DATE
  ),
  tag_credits AS (
    SELECT
        COALESCE(NULLIF(query_tag, ''), 'untagged') AS tag,
        SUM(credits_attributed_compute) AS credits
      FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ATTRIBUTION_HISTORY
      WHERE start_time >= DATEADD(MONTH, -1, CURRENT_DATE)
      GROUP BY tag
  ),
  total_credit AS (
    SELECT SUM(credits) AS sum_all_credits
      FROM tag_credits
  )
SELECT
    tc.tag,
    tc.credits / t.sum_all_credits * w.compute_credits AS attributed_credits
  FROM tag_credits tc, total_credit t, wh_bill w
  ORDER BY attributed_credits DESC;

+-------------------------+--------------------+
| TAG                     | ATTRIBUTED_CREDITS |
+-------------------------+--------------------|
| untagged                |        9.020031304 |
| COST_CENTER=finance     |        1.027742521 |
| COST_CENTER=engineering |        1.018755812 |
| COST_CENTER=marketing   |       0.4801370376 |
+-------------------------+--------------------+

Grouping similar queries¶

For recurrent or similar queries, use the query_hash or query_parameterized_hash to group costs by query.

To find the most expensive recurrent queries for the current month, execute the following statement:

SELECT query_parameterized_hash,
       COUNT(*) AS query_count,
       SUM(credits_attributed_compute) AS total_credits
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ATTRIBUTION_HISTORY
  WHERE start_time >= DATE_TRUNC('MONTH', CURRENT_DATE)
  AND start_time < CURRENT_DATE
  GROUP BY query_parameterized_hash
  ORDER BY total_credits DESC
  LIMIT 20;

For an additional query based on query ID, see Examples.

Attributing costs of hierarchical queries¶

For stored procedures that issue multiple hierarchical queries, you can compute the attributed query costs for the procedure by using the root query ID for the procedure.

1. To find the root query ID for a stored procedure, use the ACCESS_HISTORY view. For example, to find the root query ID for a stored procedure, set the query_id and execute the following statements:

   SET query_id = '<query_id>';
   
   SELECT query_id,
          parent_query_id,
          root_query_id,
          direct_objects_accessed
     FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY
     WHERE query_id = $query_id;
   

   Copy

   For more information, see Example: Ancestor queries with stored procedures.

2. To sum the query cost for the entire procedure, replace <root_query_id> and execute the following statements:

   SET query_id = '<root_query_id>';
   
   SELECT SUM(credits_attributed_compute) AS total_attributed_credits
     FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ATTRIBUTION_HISTORY
     WHERE (root_query_id = $query_id OR query_id = $query_id);
   

   Copy