Visualize cost information in Coda

[ian-r-rose]

Once we have created useful data views for our cost information (#430, #247), let's experiment with visualizing them in Coda. This would require creating a new service account for Coda and learning how to use its Snowflake pack.

[jkarpen]

@jkarpen reminder to add the Coda integration dependencies here

[jkarpen]

Instructions on adding the Snowflake pack to Coda here: https://help.coda.io/en/articles/8549795-set-up-the-snowflake-pack

Note that there are some steps in Snowflake that require ACCOUNTADMIN role to complete, specifically to enable OAuth security integration.

Also on the Coda side this will require a Coda admin so we will need to loop in ODI IT once we have the Snowflake prerequisites in place.

[jkarpen]

Moved this into Sprint 2025-04 so we remember to discuss it in next sprint planning. We may be able to have the call with IT sooner than expected as the Snowflake prereqs are almost done.

[ram-kishore-odi]

Hi @jkarpen, cc: @ian-r-rose

Please see the information related to the snowflake costing information below. I am also copying Ian just to see if he has any comments.

For a general overview of Snowflake's cost management features and how to explore your spending, please refer to this link: https://docs.snowflake.com/en/user-guide/cost-exploring-overall

You can get more granular cost data by querying specific views within the ACCOUNT_USAGE (for a single account) or ORGANIZATION_USAGE (for all accounts in your organization) schemas. These schemas are accessible via SQL.

Following is a quick summary of the key information and the tables to refer to: (based/from on snowflake official documentation).

ACCOUNT_USAGE Schema (for single account cost information):

USAGE_IN_CURRENCY_DAILY: Daily cost information in your organization's currency.
DATABASE_STORAGE_USAGE_HISTORY: Daily storage usage for databases
STORAGE_DAILY_HISTORY: Average daily storage usage for database and stage storage.

ORGANIZATION_USAGE Schema (for cost information across all accounts):

DATABASE_STORAGE_USAGE_HISTORY: Daily storage usage for databases across all accounts.
STORAGE_DAILY_HISTORY: Average daily storage usage for database and stage storage across all accounts.

I think you need access to appropriate roles (e.g., USAGE_VIEWER, GOVERNANCE_VIEWER, ORGANIZATION_BILLING_VIEWER) to access this information. I did not check your account yet but I will be able to grant these as needed. It also looks like there can be a delay of up to 72 hours for cost data to appear (so it may not be the latest always, just a heads-up).

Attached a screenshot below for locating these schemas below.

Please let me know if you have any questions after reviewing the documentation.

For calculating compute costs:

Key View: USAGE_IN_CURRENCY_DAILY

Relevant Columns:
CREDITS_USED (or similar): Represents the compute credits consumed by your virtual warehouses. This is the primary metric for compute cost.

WAREHOUSE_ID / WAREHOUSE_NAME: Identifies the warehouse consuming the credits.

START_TIME / END_TIME: Shows the time period of compute usage.

Cost Calculation: Compute costs are directly related to the CREDITS_USED. We need to multiply CREDITS_USED and Snowflake credit price to get the actual cost. I am not sure if COST column directly available in this view.

Here are some sample queries (for an account. We can run similar queries using ORGANIZATION_USAGE for the whole organization)

-- Compute cost
SELECT
DATE(START_TIME) AS usage_date,
WAREHOUSE_NAME,
SUM(CREDITS_USED) AS total_credits_used
FROM
ACCOUNT_USAGE.USAGE_IN_CURRENCY_DAILY
WHERE /* Add your date range filter here */
GROUP BY
usage_date, WAREHOUSE_NAME;

-- Storage Cost (Database)
SELECT
DATE(USAGE_DATE) as usage_date,
SUM(STORAGE_BYTES) AS total_database_storage_bytes
FROM
ACCOUNT_USAGE.DATABASE_STORAGE_USAGE_HISTORY
WHERE /* Add your date range filter here */
GROUP BY
usage_date;

[ram-kishore-odi]

hi @jkarpen, Your access request for the main ODI account has been submitted a short time ago. I expect to have an update shortly. Will keep you posted.